Forum Discussion
Power BI Report Server and SSIS
Where to start...
The thing about SSIS is that when you run it, it will grab a BIG chunk of memory. How much depends on how many packages you run in parallel, each package gets x processes (10 by default). Each process grabs a lump of memory. As each process is passed a SSIS task to execute it will demand more memory as it requires. If a process encounters a dataflow it will start creating data buffers (10 by default again) each buffer is designed to hold around 10k rows of data, so the size of a buffer is mostly dependant on the width (byte size) of the rows of data that are being "moved". Any NTEXT, IMAGE or BLOB columns cause the buffers to explode pretty quickly. All this impacts how much "memory" SSIS will try and claim.
SSIS does not play well with other systems. It wants ALL the memory thank you very much. So running it alongside SQL Server, SSAS or SSRS is obviosuly "interesting" as they ALL want ALL the memory!
There are 2 or 3 things that could have happened.
The first is that PBI-SSRS obviously consumes a lump of memory. Shutting the service down and trying to run the SSIS will probably have released that memory so SSIS could make use of it. So if that's not made an appreciable difference that's unlikley to be your issue. You should be able to tell how crowded your memory space is using task manager, if the free memory is exceptionally low then it's certainly possible SSIS is behaving badly because of a lack of memory.
The second is that your PBI-SSRS has been running some queries on the MSSQL service which may well be consuming a big chunk of memory in the SQL Server Buffer Pool or some such. SQL Server can do this and can hold onto the memory in case it needs it for somehting else. SQL Server is like that (needy and high maintenance) so it might be an idea to restart the SQL Server and try your SSIS then. You can adjust the memory settings for SQL Server to stop it grabbing too much and to make it let go of memory when the underlying windows server is under memory pressure.
The final thing (that springs to my mind) is that the SSISDB (SSSI Catalog) is not terribly efficient if you have lots of versions of packages and lots of data logged. If you deployed a new version of your package that could be the cause (the PBI-SSRS install could just be coincidence), this perf drop off isn't gradual. It's fine, its fine, its fine, then suddenly it's running like a dead sloth!
I'd suggest monitoring CPU usage and memory usage while the package runs. If memory isn't crowded you should see the CPU running nicely as the SSIS package runs. You'll see activity for SSIS and also SQL itself (the SSIS catalog will be logging stuff into its SSISDB and you'll be moving data into a database so SQL will be doing a fair chunk of work)
If the CPU is very stop start with periods where nothing seems to be doing anything, this commonly suggests your SSISDB may be the issue. (it can also mean SQL is struggling to store your data, if the destination DB is runing out of space then you may well see perf probelsm in SSIS) I'd suggest flushing out old data on your SSIS Catalog to see if it has an impact.
if you're interested in seeing how your ssis is perfroming have a read of this
https://technet.microsoft.com/en-us/library/ms141687%28v=sql.90%29.aspx?f=255&MSPPError=-2147217396
Unfortunately there are no "short" answers.
Good luck
Where to start...
The thing about SSIS is that when you run it, it will grab a BIG chunk of memory. How much depends on how many packages you run in parallel, each package gets x processes (10 by default). Each process grabs a lump of memory. As each process is passed a SSIS task to execute it will demand more memory as it requires. If a process encounters a dataflow it will start creating data buffers (10 by default again) each buffer is designed to hold around 10k rows of data, so the size of a buffer is mostly dependant on the width (byte size) of the rows of data that are being "moved". Any NTEXT, IMAGE or BLOB columns cause the buffers to explode pretty quickly. All this impacts how much "memory" SSIS will try and claim.
SSIS does not play well with other systems. It wants ALL the memory thank you very much. So running it alongside SQL Server, SSAS or SSRS is obviosuly "interesting" as they ALL want ALL the memory!
There are 2 or 3 things that could have happened.
The first is that PBI-SSRS obviously consumes a lump of memory. Shutting the service down and trying to run the SSIS will probably have released that memory so SSIS could make use of it. So if that's not made an appreciable difference that's unlikley to be your issue. You should be able to tell how crowded your memory space is using task manager, if the free memory is exceptionally low then it's certainly possible SSIS is behaving badly because of a lack of memory.
The second is that your PBI-SSRS has been running some queries on the MSSQL service which may well be consuming a big chunk of memory in the SQL Server Buffer Pool or some such. SQL Server can do this and can hold onto the memory in case it needs it for somehting else. SQL Server is like that (needy and high maintenance) so it might be an idea to restart the SQL Server and try your SSIS then. You can adjust the memory settings for SQL Server to stop it grabbing too much and to make it let go of memory when the underlying windows server is under memory pressure.
The final thing (that springs to my mind) is that the SSISDB (SSSI Catalog) is not terribly efficient if you have lots of versions of packages and lots of data logged. If you deployed a new version of your package that could be the cause (the PBI-SSRS install could just be coincidence), this perf drop off isn't gradual. It's fine, its fine, its fine, then suddenly it's running like a dead sloth!
I'd suggest monitoring CPU usage and memory usage while the package runs. If memory isn't crowded you should see the CPU running nicely as the SSIS package runs. You'll see activity for SSIS and also SQL itself (the SSIS catalog will be logging stuff into its SSISDB and you'll be moving data into a database so SQL will be doing a fair chunk of work)
If the CPU is very stop start with periods where nothing seems to be doing anything, this commonly suggests your SSISDB may be the issue. (it can also mean SQL is struggling to store your data, if the destination DB is runing out of space then you may well see perf probelsm in SSIS) I'd suggest flushing out old data on your SSIS Catalog to see if it has an impact.
if you're interested in seeing how your ssis is perfroming have a read of this
https://technet.microsoft.com/en-us/library/ms141687%28v=sql.90%29.aspx?f=255&MSPPError=-2147217396
Unfortunately there are no "short" answers.
Good luck
Thanks for all of that great information. It really helped me realize I need to push my IT team more to see what changed on the server as far as resources during the time of the Power BI Report Server install. But thanks for all of that information.