Forum Discussion
Incremental Refresh Synapse - Reduce Number of Concurrent Session
Does anyone know how I can reduce the number of connections in a intial load of a incremental refresh Power BI Dataset that connects to Synapse.
I have a number of tables in a start schema that I would like to have incremental refresh set up for a 10 year period, the new data is only going to be a couple of days. So I want as small a partion size as possible.
However is seems that I can only have a total of 512 partions across all the tables, which dpesn't let me go for a fine grain of parition. Or that is roughly when I stop getting this message
'Microsoft SQL: 111219;The maximum concurrent session limit of 512 exceeded. To learn more, please visit: http://aka.ms/dwsoftlimits.. The exception was raised by the IDbCommand interface'
Does anyone know a work around to get the refresh to queue the connections rather try and run them all conccurently? I've looked online and couldn't find anyone talking much about this issue
10 Replies
_sfrostSolution Specialist
It's not clear whether you are using gateway here or not. In case you aren't using gateway, you can try setting up gateway for Synapse since synapse supports gateway.
By using the gateway, there is possibility to control the concurrent connections to the source. We need to change a setting in gateway configuration file to make it work. Please refer to the below doc.
https://docs.microsoft.com/en-us/data-integration/gateway/service-gateway-performance-cpu
You need to change the below mentioned field value to the desired number of concurrent connections.
MashupDefaultPoolContainerMaxCount
Did I answer your questions? Give a thumbs up and accept this post as solution!
Ahh thank you, thats a great idea, I will give that a go.
We are not using the gateway at the moment, I will give that a go this morning and let you know if that solves it.
- _sfrost
Though your source is cloud and doesn't require gateway for refresh, we can still set up gateway to control the number of concurrent sessions. That's the reason I suggested you to use gateway and I am not sure if we can control this if we do not use gateway.
To start with, install a gateway and add your Synapse datasource under that gateway.
Once set up, follow the article I provided and change the gateway configuration file.
Once done, do a refresh and observe the behaviour.
