This post has been republished via RSS; it originally appeared at: Microsoft Tech Community - Latest Blogs - .
Today, we got a service request that our customer reported a high CPU usage in Azure SQL Database. Following I would to share with you my lessons learned here.
Our customer has the following Python code that is running a query against Azure SQL Database but every execution is taking too much time and the CPU is getting 100%.
import pyodbc
import time
def ConnectToTheDB():
try:
print('Connecting to the DB')
start_time = time.time()
conn = pyodbc.connect("DRIVER={ODBC Driver 17 for SQL Server};server=tcp:servername.database.windows.net,1433;UID=username;PWD=password;database=DBName");
print("Connected to the Database %s seconds ---" % ((time.time() - start_time)) )
return conn
except BaseException as e:
print("An error occurred connecting to the DB - " + format(e))
return
conn = ConnectToTheDB()
cursor = conn.cursor()
SQL = "select count(*) from Table WHERE TextToSearch = ?"
cursor.setinputsizes([(pyodbc.SQL_WVARCHAR, 200, 0)])
nLoop=1
while nLoop<1000:
nLoop=nLoop+1
start_time = time.time()
cursor.execute(SQL,"Value:" + str(nLoop))
row = cursor.fetchone()
totalODBC=0
while row:
totalODBC=totalODBC+1
print("-(ODBC)--- Loop:%d Rows Returned:%d %s seconds ---" % (nLoop,totalODBC,(time.time() - start_time)) )
row = cursor.fetchone()
conn.close()
Using SQL Server Profiler extension of Azure Data Studio we found that the following query that Python is executing.
declare @p1 int
set @p1=2
exec sp_prepexec @p1 output,N'@P1 nvarchar(200)',N'select count(*) from PerformanceVarcharNVarchar WHERE TextToSearch = @P1',N'Value:3'
select @p1
Checking the query that Python is executing, we saw that the execution plan reported a type conversion in the exclamation mask in the Select operator.
declare @p1 int
set @p1=Null
exec sp_prepexec @p1 output,N'@P1 nvarchar(200)',N'select count(*) from PerformanceVarcharNVarchar WHERE TextToSearch = @P1',N'Value:3'
select @p1
So, comparing the data type that Python is sending to the driver with the column of the table, we saw a data type mismatch, because the column data type is Varchar and the parameter data type using by Python is nVarchar, hence, forcing to Azure SQL Database to run a conversion implicit. The code change was cursor.setinputsizes([(pyodbc.SQL_WVARCHAR, 200, 0)]) by cursor.setinputsizes([(pyodbc.SQL_VARCHAR, 200, 0)]).
Changing the parameter of Python to the same data type that the column of the table has, the performance was high and the CPU usage was very low.
import pyodbc
import time
def ConnectToTheDB():
try:
print('Connecting to the DB')
start_time = time.time()
conn = pyodbc.connect("DRIVER={ODBC Driver 17 for SQL Server};server=tcp:servername.database.windows.net,1433;UID=username;PWD=password;database=DBName");
print("Connected to the Database %s seconds ---" % ((time.time() - start_time)) )
return conn
except BaseException as e:
print("An error occurred connecting to the DB - " + format(e))
return
conn = ConnectToTheDB()
cursor = conn.cursor()
SQL = "select count(*) from Table WHERE TextToSearch = ?"
cursor.setinputsizes([(pyodbc.SQL_VARCHAR, 200, 0)])
nLoop=1
while nLoop<1000:
nLoop=nLoop+1
start_time = time.time()
cursor.execute(SQL,"Value:" + str(nLoop))
row = cursor.fetchone()
totalODBC=0
while row:
totalODBC=totalODBC+1
print("-(ODBC)--- Loop:%d Rows Returned:%d %s seconds ---" % (nLoop,totalODBC,(time.time() - start_time)) )
row = cursor.fetchone()
conn.close()
Enjoy!