Monitoring Synapse serverless SQL open connections

[Liliam_C_Leme]

Scenario: Consider a scenario where you are trying to monitor the connections from other applications to serverless SQL. I hit this need while trying to understand how many connections opened I had coming from an application. Had I hit some kind of limitation on serverless SQL or not. Spoiler: There is no limit for connections on Synapse serverless SQL  as you would find with a dedicated SQL pool (formerly SQL DW).

Here it goes how to find how many open connections you have very simply:

1) Open SSMS or Synapse Studio or Azure Data Studio whatever you prefer.

2) Connect to your Synapse  serverless SQL endpoint. If you are not connecting from the Synapse Studio, you can find the endpoint on the  Azure portal when checking your workspace overview details - Fig 1 Overview (yellow).

Fig 1 - Overview

3) Now let's check the active sessions and requests. Run the following queries and check the results. This query will expose the sessions and current requests from those sessions in a summarized way. 

Select     
    DB_NAME(s.database_id) as DBName, 
    COUNT(s.database_id) as NumberOfConnections,
    nt_user_name as username, 
    login_name as LoginName,
    program_name as ApplicationName 
FROM 
    sys.dm_exec_requests req
    JOIN sys.dm_exec_sessions s ON req.session_id = s.session_id
GROUP BY 
    s.database_id, nt_user_name, login_name, program_name

Another way to monitor is the next query that will summarize the connections per program, login, and database:

select
    DB_NAME(database_id) as DBName, 
    nt_user_name as username, 
    login_name as LoginName,
    program_name as ApplicationName,
	host_name,
	program_name,
	COUNT(*) AS NB_Connections
from sys.dm_exec_sessions
GROUP BY DB_NAME(database_id) , 
    nt_user_name , 
    login_name ,
    program_name ,
	host_name,
	program_name

For example, as a result in my own environment I have the following for the second query - Fig 2 Query Results:

Fig. 2  Query results.

As a result, information such as SQL or AAD user, login, application, and hostname will be exposed. As also you may note the results of internal sessions related to the Medatasync service on Serverless. Medatasync does what the name implies sync the metadata with other components. This is internal and is not something a user has control over.

Summary:  The  dynamic managed views (DMVs) above provide you a way to check out the sessions and active requests from those open sessions open. When I used those DMVs to understand the requests I have coming from my application to serverless I could confirm there was no limitation on the open sessions as I mentioned before.

Following are some examples of possible  scenarios to use this logic:

• Monitor Active connections
• Monitor users, applications, and hosts connected to the environment.
• Troubleshooting for a suspicious leak of connections can be applied in any scenario. 

So for the possible question that you may have up to this point... But...Would that mean is there a hard limit on connections inside serverless?

The short answer is No. What must be considered for serverless is not a limited number of connections but the number of active running queries.

Considerations about the active running queries:

• The number of active sessions and requests would depend on the query complexity and amount of data scanned.  
• I mean...As with any SQL, a serverless SQL pool could handle many different sessions that are executing lightweight queries or complex heavy queries consuming most of the resources while the other queries wait.

And this is documented as follows:

Check if there is a concurrent workload running on the serverless pool because the other queries might take the resources. In that case you might split the workload on multiple workspaces." 

Self-help for serverless SQL pool

Note: Thanks to Sergio Fonseca, Silas Mendes, and Ali Saleh on the CSS teamwork side.

Liliam C Leme

UK Engineer