Error RSPortal: obtaining a connection from the pool

[mrpellepelle]

Hi,

we use Oct. 2020 release  (product version 1.9.7627.11028)  and have the following Internal Server Error:

2021-02-01 10:54:28.3246|ERROR|190|OData exception occurred: 
System.Exception: ReportServerDatabaseUnavailableException --->System.InvalidOperationException: Timeout expired. 
The timeout period elapsed prior to obtaining a connection from the pool.  
This may have occurred because all pooled connections were in use and max pool size was reached.

 I can reproduce this error when i open one of our Power BI Reports ( Live Connection to Tabular) and navigate through the tabs and  filter values. After each click i can see increasing connections on SQL Server DB ReportSever DB  via sys.sysprocesses.
When it reaches 100 connections , the error occurs and the Report Service is unavailable( empty root folder) for some minutes. 

100 fits to the following Log entry in RSPortal after restarting PBIRS:

2021-02-01 12:05:10.4767|INFO|1|Catalog max connection pool size: 100

What is RsPortal used for? Older Power BI report do not show this behaviour.

Is it a bug and how can i resolve or work around the problem?

Is it fixed in Januar 2021 release?

[mrpellepelle]

@Petebro  Any news on this topic? Have you been able to reproduce the issue?

[jbarta_mlchc]

@Petebro Is there an update on this issue we are seeing? Last I saw @mrpellepelle and I both tested reports with the enhanced metadata feature off and it still created multiple connections.

[jbarta_mlchc]

@Petebro thanks for that information. I will recreate and test again. Unfortunately for me I know if deploy this report as is to our staff, 200 connections will be hit in no time at all. So I think my best bet is to recreate and try again. Thanks! 

[Petebro]

Unfortunately enhanced metadata is a one way upgrade so you would have to recreate the report. I am trying now with a DQ report I have adding many more visuals and slicers to it.

As far as config guidance, there really isn't any best practice as  our default (100) should be enough for most cases. This is sort of uncharted waters due to this issue. I would say testing double to see if the number of times you end up out of connections is affected at all.  However, there could be other consequences from doing this with AS model performance so test this locally in a test environment before setting in production.

[jbarta_mlchc]

Thanks @Petebro. The report I have been testing with does have a larger amount of visuals and slicers. But I have similar reports built with prior desktop versions that do not create the same amount of connections. So if it is related to the enhanced model metaddata feature I can try testing that. The report was originally created with that feature on, so I would have to recreate the report with the feature off, right? I can't just turn the feature off and redeploy the original report? 

Additionally, if I increase the max connection in the config file, is there a best practice for where to set that?  If I set it to 200 and the report is creating 10 to 20 connections per filter slice, those will be used up very quickly.

[Petebro]

This is likely NOT a desktop specific issue. The reason you see the change when saving with a specific version of desktop is likely just the state of the "enhanced model metadata" feature. There is likely an issue with cleaning up unused connections in PBIRS itself. Working with server team to try to reproduce this issue now, but we were running into some trouble getting it to break. Have you seen this issue on a simple report with slicers or has it been on more complicated reports with many visuals + slicers?

For now you can increase the number of connections in the config file as one use suggested, or use reports with enhanced metadata disabled. I will update you when we know more.

[ChrisMuthmann · Accepted solution]

I've worked on this issue with the support. Issue number is 121020321001710.

There is a demo dashboard with Adventure Works Tabular Model and some slicers and filters. 

We've fixed the issue by increasing 

<MaxCatalogConnectionPoolSizePerProcess> to 500.

[jbarta_mlchc]

@ChrisMuthmann Thanks for the response. I will increase the pool size to 500. But it seems like that could still be reached quickly with a small to medium-size company. If you have 20 employees and they use a report with 8 to 10 visuals on it. You get to 160 to 200 connections very quickly. With a few filter or slicer changes, you are at 500. Is this a temporary solution until they fix the issue?

[ChrisMuthmann]

This should be a temporary solution. I've heard that the support is collecting the issues to decide how important that bugfix is. At least they will clarify the parameter in the documentation of the next version. 

We have just tested with a few developers our most critical dashboard. One user can reach approx. 130 connections in 2 minutes. Four users could not reach more than 246 connections. So it is not an easy calculation where you can multiply the dashboard, visuals and filters by the number of users. If 500 is not enough, we can improve the value to 1000. I've not seen a real impact on memory or other resources.

[jbarta_mlchc]

@ChrisMuthmann Thanks again for your response! I appreciate the information! I will change the pool size and monitor. I am hoping there will be a permanent fix in the near future! Thanks again!

[mrpellepelle]

@Petebro  I reproduced the issue as following:

1. Create new Power BI report with live connection to tabular model. 
2. Added 4 visual with  existing measures and different dimensions.
3. publish it to PBIRS. 
4. Restart PBIRS to see zero RS Portal connection.
5. Start to drilldown/filter visuals and apply filters via filter sections.
6. I can see about 2 to 6 new connections for each new combination.

btw: I also tried to switch off the "enhanced meta data" feature but still get the issue.

[jbarta_mlchc]

@Petebro I turned off the metadata preview feature, restarted desktop, and created a report with six visuals and one slicer. With each slicer change my connections increased by 6 connections even with the "enhanced meta data" off. So I am seeing the same things as @mrpellepelle.

[jbarta_mlchc]

Thanks for that information @josef78 . I was able to change this in the configuration file, but I don't think this is the answer most of us are looking for. I believe for most of us the concern is the number of connections that are occurring with each filter/slicer change. I am seeing between 10 to 20 connections per change depending on the report, so even if I change the config file to 200 connections, it does not take very long for it to get to 200 connections. So what has changed in the October 2020 version (Still exists in January 2021) to make all these connections appear? Reports created with desktop versions prior to October 2020 do not appear to have this same issue. Is it related to the enhanced metadata format preview feature? Has anyone tested with that feature off? 

[mrpellepelle]

This is my understanding of rsportal and a connection pool:

rsportal is configured to use a maximum of 100 pooled connections. When it has reached 100, no new connection may be established. One of the 100 pooled connections must be used. If all connections are still being used (possibly incorrectly), the portal will no longer respond. When the timeout is reached, server logs the error msg.

After a few minutes you can see that the number of connections are falling and Report Server is responding again.

@Petebro  Did you get any feedback from the Desktop RS guys?

[josef78]

Hi,

it is configurable varialbe, you can set in rsreportserver.config option <MaxCatalogConnectionPoolSizePerProcess>

In my production enviroment, is default value =0=100 not enough, but with different symptoms (users waiting too long in some queue to load model to ssas).

I'm using 200.

j.

[jbarta_mlchc]

I had another question. Shouldn't these connections drop once the user closes the report? I am not seeing the connections decrease after I close a report. 

[Steffe]

Hi,

I think we have the same problem.

I see the same error-message in our "rsportal"-log. We are using oct 2020 version (15.0.1104.239) of the server.

I have tried to reproduce it and for some reports (in my example it was a Live Tabular report) i can increase the number of SQL connections by for example changing a filter/slicer in the report. But in my test it seems like I can increase the number of conection to a certain level (can be below 100) by changing a filter, but then it just stop increasing it. I´ve not been able to "kill" the report-portal while doing my own tests.

I´ve never been able to get a higher number of connections than 100.

I´m also looking in the sys.processes view to count the number of connections to 'RSPORTAL'.

I´m not sure which version of the desktop the user that created this report where using. Can I see it in some way (perhaps in the reportserver database)?

I´ve got calls from users saying the report portal is sometimes not responding and I think it is because of this problem.

[jbarta_mlchc]

Nevermind I figured it out and now I can see the connections increase. I did some testing with the new Edge browser versus IE 11 and one thing I noticed is that there were far less connections per filter or slicer change in the new Edge versus IE 11. I was seeing it increase by ~4 connection in the new Edge and around ~24 connections in IE 11 with each filter/slicer change. It took a lot more changes to reach the 100 before the portal became unresponsive.

[jbarta_mlchc]

I am trying to understand further how you are seeing the connections. I see a view called sys.sysprocesses and I run that against the PBIRS database. What should I be looking for to see the connection and seeing them increase? Thanks for you help in understanding this further.

[jbarta_mlchc]

I have done some further testing and discovered a few other things. Our clinic has not moved on to the new Edge browser yet. I installed it and ran the report from the web portal like I have been and there were no issues. I did not experience the connection pool error and the home screen did not go blank. I am only experiencing that when running Internet Explorer 11. I also tested adding the ?rs:Embed=True to the report URL in the new Edge and that caused the issue with the connection pool and the home screen went blank. I use that to deploy reports from a website. But the report runs with no issues in the new Edge and with out the Embed on the URL.