Today, we encountered an interesting scenario with one of our customers. They reported a rapid increase in the number of workers in their Azure SQL Database, which they attributed to a high volume of logins. This situation presents an opportunity to delve into how logins, query durations, and potential blockings among queries can influence worker utilization.
Let's assume that we have:
- Following Python code
- Our databases has a current limit of 60 requests, 60 logins and 600 sessions.
- We are using Microsoft ODBC Driver 18 for SQL Server with connection pooling enable.
import pyodbc
import threading
server = 'servername.database.windows.net'
database = 'dbname'
username = 'username'
password = 'password!'
driver= '{ODBC Driver 18 for SQL Server}'
'''
1= Resource ID 1 - Request Limit Reached
2= Resource ID 2 - Session Limit Reached
3= Resource ID 3 - Login Limit Reached
'''
ScenarioType=2
def create_connection():
with pyodbc.connect('DRIVER=' + driver + ';SERVER=' + server + ';PORT=1433;DATABASE=' + database + ';UID=' + username + ';PWD=' + password) as conn:
with conn.cursor() as cursor:
if(ScenarioType==2):
cursor.execute("select 1")
row = cursor.fetchone()
while row:
##print(row[0])
row = cursor.fetchone()
if(ScenarioType==1):
cursor.execute("WAITFOR DELAY '00:00:10';select 1")
row = cursor.fetchone()
while row:
##print(row[0])
row = cursor.fetchone()
# Number of connections
num_connections = 5000
# Threads
threads = []
for i in range(num_connections):
thread = threading.Thread(target=create_connection)
threads.append(thread)
thread.start()
# Waiting to finish
for thread in threads:
thread.join()
We're going to test the application basically running 5000 requests executing SELECT 1 for the scenario 1. Let's see the results:
- Checking the following queries: We could see the max_workers are very low but the max_session_percent reached 100% and after several minutes we got the following error: Resource ID : 2. The session limit for the database is 600 and has been reached. See 'https://docs.microsoft.com/azure/azure-sql/database/resource-limits-logical-server' for assistance.
select * from sys.dm_db_resource_stats
SELECT * from sys.dm_db_wait_stats order by signal_wait_time_ms desc
So, in summary, our ODBC connection pooling was able to manage the connections request and we reached the session limit.
Let's play with another example but, in this case, we are going to add a little more time in the execution adding 10 seconds in the execution - cursor.execute("WAITFOR DELAY '00:00:10';select 1").
- In this situation, we started to see: Resource ID : 1. The request limit for the database is 60 and has been reached. See 'https://docs.microsoft.com/azure/azure-sql/database/resource-limits-logical-server' for assistance, mainly the workers were waiting for 10 seconds to complete the task.
But, what are my lessons learned here:
The Correlation Between Logins and Worker Usage:
Our customer's experience highlighted a common misconception in database management. While it's true that an increase in logins can affect the number of workers, it is not the sole factor. The critical element here is the duration of the queries executed within these login sessions. Longer-running queries tie up workers for more extended periods, leading to increased worker usage. This effect is compounded when multiple sessions execute long queries concurrently.
Understanding the Underlying Dynamics:
- Query Duration: The duration of a query is a significant determinant of worker utilization. Workers are engaged as long as a query is running. Therefore, longer queries mean more workers are occupied for extended periods.
- Blocking Among Queries: Another aspect to consider is the potential for blocking between queries. When one query locks a resource needed by another, it creates a blocking scenario. This not only delays the execution of the waiting query but also keeps the associated workers engaged, contributing to higher worker usage.
Mitigating the Impact:
To address these challenges, several strategies can be employed:
- Optimizing Query Performance: By refining query efficiency, the duration of query execution can be reduced, thus freeing up workers more quickly.
- Effective Session Management: Ensuring that sessions are terminated promptly after their tasks are completed can prevent unnecessary worker occupation.
- Monitoring for Blockings: Regularly monitoring the database for potential blockings and addressing them swiftly can mitigate their impact on resource utilization.
Conclusion: Today's incident with our customer serves as a valuable lesson in understanding the dynamics of worker utilization in Azure SQL Database. It's not just the number of logins that matter but also the nature and duration of the activities carried out within these sessions. Effective management and optimization of these elements are crucial for maintaining efficient database performance.