Lesson Learned #354: Why is Python using a lot of CPU of Azure SQ Database?

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!

Continue reading...