How to resolve High Compilations/Second problem in SQL Server

Hi Friends and Geeks!

Sharing an experience on resolution of High Compilations/Second performance counter in SQL Server from one of my consulting assignments.

The threshold for Compilations/Second is mentioned as 10% of Batch Requests/Second by various MVPs and in our case it was around 40 to 50%

Investigation Process

1. Investigate for queries with same query_hash but multiple plan_handles using the below query

   SELECT query_hash, COUNT(plan_handle) FROM sys.dm_exec_query_stats GROUP BY query_hash
   ORDER BY COUNT(plan_handle) DESC

Found multiple queries with almost 4000 to 5000 plan_handles for each query_hash.

The query text looked something like the following

(@salesAmount float OUTPUT)SELECT @salesAmount = ISNULL(SalesAmount,0) FROM Sales WITH(NOLOCK) 
WHERE City = 'Chennai'

(@salesAmount float OUTPUT)SELECT @salesAmount = ISNULL(SalesAmount,0) FROM Sales WITH(NOLOCK) 
WHERE City = 'Hyderabad'

Notice that the only difference between the queries is the WHERE condition in the above queries

The code in the stored procedure was as follows

DECLARE @vCmd nvarchar(200)
DECLARE @city varchar(100)
DECLARE @salesAmount float
DECLARE @tablename1 varchar(100)
SET @city = 'Hyderabad'
SET @tablename1 = 'Sales'
SET @vCmd = 'SELECT @salesAmount = ISNULL(SalesAmount,0)' + ' ' +
'FROM ' + @tablename1 + ' WITH(NOLOCK)' + ' ' +
'WHERE City = ''' + @city + '''' ;
SELECT @vCmd
EXEC sp_executesql @vCmd, N'@salesAmount float OUTPUT',
@salesAmount =@salesAmount OUTPUT

Resolution

Changed the code as a parameterized dynamic SQL query as follows (Notice the @params parameter addition to sp_executesql and @city parameter inside the @vCmd command)

DECLARE @vCmd nvarchar(200)
DECLARE @city varchar(100)
DECLARE @salesAmount float
DECLARE @tablename1 varchar(100)
SET @tablename1 = 'Sales'
SET @vCmd = 'SELECT @salesAmount = ISNULL(SalesAmount,0)' + ' ' +
'FROM ' + @tablename1 + ' WITH(NOLOCK)' + ' ' +
'WHERE City = @city'
SELECT @vCmd
EXEC sp_executesql @vCmd, @params = N'@city varchar(100), @salesAmount float OUTPUT',
@salesAmount =@salesAmount OUTPUT,
@city = 'Hyderabad'

Validation

With the fixes done, we needed to validate that the parameterized queries are being compiled only once

The following query was used to figure out the number of plans for the changed code

SELECT qt.text, *
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS qt
WHERE qt.text LIKE '%Sales%'

Only resulted in one plan for multiple executions of the code for different cities

(@city varchar(100), @salesAmount float OUTPUT)SELECT @salesAmount = ISNULL(SalesAmount,0) FROM Sales WITH(NOLOCK) WHERE City = @city

Run the parameterized query again for a different city and check that another plan is not created for the same

SET @vCmd = 'SELECT @salesAmount = ISNULL(SalesAmount,0)' + ' ' +
'FROM ' + @tablename1 + ' WITH(NOLOCK)' + ' ' +
'WHERE City = @city'
SELECT @vCmd
EXEC sp_executesql @vCmd, @params = N'@city varchar(100), @salesAmount float OUTPUT',
@salesAmount =@salesAmount OUTPUT,
@city = 'Chennai'

See that the execution_count in the results has increased by 1 but the plan remains the same

Simple fixes like these brought down the high value of Compilations/Second counter and reduced the CPU utilization to a great extent

Hope you find this tip useful

Thanks and Regards

Veera

Like us on FaceBook |

Join the fastest growing SQL Server group on FaceBook

Follow me on LinkedIn