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

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

The query text looked something like the following

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



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)



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

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

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

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


Like us on FaceBook |

Join the fastest growing SQL Server group on FaceBook

Follow me on LinkedIn