cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
jbarta Frequent Visitor
Frequent Visitor

Power BI Report Server and SSIS

I installed Power BI Report server on a virtual machine running Windows Server 2012 R2. It is a server that I had one SSIS package running to convert data from a production database. Prior to the install the package ran in 15 to 18 minutes. The day after the install the package started to run at 30 to 35 mintues, so almost double. I am not sure Power BI Report server is to blame for the difference in run time for the package. I did some testing over the weekend and turned off the Report Server service and took both of the databases for it offline. That did not improve the run time. The install for Power BI Report server is fresh and there are no SSRS reports or Power BI Desktop reports in it. Because of the testing with the service off and databases offline, I believe that Power BI report server is not to blame, but I wanted to see if anyone on this forum thinks different or has experienced the same thing. Any ideas would be helpful. Thanks!

1 ACCEPTED SOLUTION

Accepted Solutions
stpnet Established Member
Established Member

Re: 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://docs.microsoft.com/en-us/sql/integration-services/performance/monitor-running-packages-and-o...

 

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

View solution in original post

6 REPLIES 6
Moderator v-qiuyu-msft
Moderator

Re: Power BI Report Server and SSIS

Hi @jbarta,

 

This should be SSIS performance issue. You can tuning performance follow: https://technet.microsoft.com/library/Cc966529. If you still have issue, please post a thread in SSIS forum

 

Best Regards,
Qiuyun Yu 

Community Support Team _ Qiuyun Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
jbarta Frequent Visitor
Frequent Visitor

Re: Power BI Report Server and SSIS

Thanks for your response!

 

I have done some tuning on the SSIS package and it has helped a little. But it is still not running like it did the days leading up to the install of Power BI Report Server. I have my IT team looking to see if some of the resources for the server had been alocated somewhere else around the same timeframe. But for it to run in 15 mintues one day and then 30 minutes the very next day after the Power BI Report server install, made me suspect that the install was causing the slow down. As I previously mentioned, I did turn off the Power BI Report Server service and the databases were offline and it didn't improve the package runtime. I asssumed that if I did that and ran the package that would tell me that Power BI Report Server is not the problem. So can you tell me if I turned off the service and took the Power BI Report Server databases offline and it did not improve the runtime of the package, in your opinion that eliminates Power BI Report Server as the issue? I just want to make sure I took the appropriate steps to eliminate to determine that.

Moderator v-qiuyu-msft
Moderator

Re: Power BI Report Server and SSIS

Hi @jbarta,

 

Personally, based on your test, the Power BI report server shouldn't be the issue. 

 

To troubleshot the issue better, I would suggest you get help from SSIS experts as I suggested before you can post a thread in SSIS forum. 

 

Best Regards,
Qiuyun Yu 

Community Support Team _ Qiuyun Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
jbarta Frequent Visitor
Frequent Visitor

Re: Power BI Report Server and SSIS

Thanks for your response. 

stpnet Established Member
Established Member

Re: 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://docs.microsoft.com/en-us/sql/integration-services/performance/monitor-running-packages-and-o...

 

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

View solution in original post

jbarta Frequent Visitor
Frequent Visitor

Re: Power BI Report Server and SSIS

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. 

Helpful resources

Announcements
New Ranks and Rank Icons in 2020

New Ranks and Rank Icons in 2020

Read the announcement for more information!

New Kudos Given Badges Coming

New Kudos Given Badges Coming

We're rolling out new Kudos Given badges. Find out how many Kudos you've given.

November 2019 Community Highlights

November 2019 Community Highlights

Get an overview of the events and great community content from November.

Power Platform World Tour

Power Platform World Tour

Find out where you can attend!

Top Solution Authors
Top Kudoed Authors (Last 30 Days)