When looking into performance issues one area to look into is CPU pressure.  CPU pressure basically means the hardware cannot keep up with the load.  Once the loads are identified there are ways to see if those queries can be better tuned to give better performance, before rushing out to buy new hardware.

For this blog, we will be looking at excessive query compilation and optimization, all of which can be found in Microsoft’s Troubleshooting Performance Problems in SQL Server 2008 (https://msdn.microsoft.com/en-us/library/dd672789(v=sql.100).aspx).

Optimizing and compiling queries is a CPU-intensive operation, and the more complex a query the higher the cost to optimize. To help keep this cost as low as possible, SQL Server will cache and reuse query plans. For each new query SQL Server will search the plan cache, or procedural cache, to look for a previously compiled plan it can use. If there is no current plan SQL will have to create one before the query is run.

During compilation, SQL Server 2008 computes a “signature” of the query, which gets put in the query_hash column for both sys.dm_exec_requests and sys.dm_exec_query_stats, and the QueryHash attribute in Showplan/Statistics XML. Entries with the same query_hash value have a good probability of being the same query text, if it had been written in a query_hash parameterized form (SQL Server like parameters over literal values for this very reason). Queries that vary in their literal values should have the same value, for example the first two queries share the same query hash, while the third query has a different query hash, because it is performing a different operation.

SELECT * FROM sys.objects WHERE object_id = 100

SELECT * FROM sys.objects WHERE object_id = 101

SELECT * FROM sys.objects WHERE [name] = ‘sysobjects‘

When the query hash is computed white spaces are ignored, as are differences in the use of explicit column lists compared to using an asterisk (*) in the SELECT list. Fully qualified names as opposed to just the table name also does not matter. All of the following should produce the same query_hash value.

USEse AdventureWorks

GO

SET showplan_xml on

GO

— Assume this is run by a user whose default schema is Sales

SELECT * FROM SalesOrderHeader h

SELECT * FROM Sales.SalesOrderHeader h

SELECT SalesOrderID,

RevisionNumber,

OrderDate,

DueDate,

ShipDate,

Status,

OnlineOrderFlag,

SalesOrderNumber,

PurchaseOrderNumber,

AccountNumber,

CustomerID,

ContactID,

SalesPersonID,

TerritoryID,

BillToAddressID,

ShipToAddressID,

ShipMethodID,

CreditCardID,

CreditCardApprovalCode,

CurrencyRateID,

SubTotal,

TaxAmt,

Freight,

TotalDue,

Comment,

rowguid,

ModifiedDate

FROM Sales.SalesOrderHeader h

GO

SET showplan_xml OFF

GO

Note that the database portion of the fully qualified name is ignored when the query_hash value is generated. This allows resource usage to be aggregated across all queries in systems that replicate the same schema and queries against many databases on the same instance. An easy way to detect applications that submit lots of ad hoc queries is by grouping on the sys.dm_exec_query_stats.query_hash column as follows.

SELECT

q.query_hash,

q.number_of_entries,

t.text as sample_query,

p.query_plan as sample_plan

FROM
(

SELECT TOP 20 query_hash,

count(*) as number_of_entries,

min(sql_handle) as sample_sql_handle,

min(plan_handle) as sample_plan_handle

FROM sys.dm_exec_query_stats

GROUP BY query_hash

HAVING count(*) > 1

ORDER BY count(*) desc) as q

cross apply sys.dm_exec_sql_text(q.sample_sql_handle) as t

cross apply sys.dm_exec_query_plan(q.sample_plan_handle) as p

GO

Queries that have a number_of_entries value in the hundreds or thousands are perfect candidates for parameterization. If you look at the CompileTime and CompileCPU attributes under the tag of the sample XML query plan and multiply those values times the number_of_entries value for that query, you can get an estimate of how much compile time and CPU you can eliminate by parameterizing the query (which means that the query is compiled once, and then it is cached and reused for subsequent executions). Fixing these queries will have a cascading benefit as there will be a reduction of CPU usage, and more memory to cache other plans, thus more memory for the buffer cache.

SQL produces a query_plan_hash  value that represents the “signature” of the query plan’s access path (that is, what join algorithm is used, the join order, index selection, and so forth).   If an application relies on different query plans based on different parameter’s being evaluated you do not want to parameterize the query.

The values query_hash and query_plan_hash can be combined to determine if a set of ad-hoc queries with the same query_hash value resulted in query plans with the same or different query_plan_hash values, or access path. A small modification is done to our earlier query:

SELECT

q.query_hash,

q.number_of_entries,

q.distinct_plans,

t.text as sample_query,

p.query_plan as sample_plan

FROM
(

select top 20 query_hash,

count(*) as number_of_entries,

count(distinct query_plan_hash) as distinct_plans,

min(sql_handle) as sample_sql_handle,

min(plan_handle) as sample_plan_handle

FROM sys.dm_exec_query_stats

GROUP BY query_hash

HAVING count(*) > 1

ORDER BY count(*) desc) as q

cross apply sys.dm_exec_sql_text(q.sample_sql_handle) as t

cross apply sys.dm_exec_query_plan(q.sample_plan_handle

) as p

GO

Note that this new query returns a count of the number of distinct query plans (query_plan_hash values) for a given query_hash value. Rows that return a large number for number_of_entries and a distinct_plans count of 1 are good candidates for parameterization. Even if the number of distinct plans is more than one, you can use sys.dm_exec_query_plan to retrieve the different query plans and examine them to see whether the difference is important and necessary for achieving optimal performance.

Once queries that can be parameterized have been identified the best place to parameterize them is at the client application, which would vary from application to application of course.

Next post, we will look into unnecessary recompilation