While discussing the concepts of 'optimize for ad hoc workloads' and 'Forced Parameterization' in SQL Server I decided to modify an existing query I wrote to analyse the query performance from the cache (more on it here) to now analyse the adhoc queries for candidates which could benefit from parameterization.
When you identify these candidates you then need to look at what is the most appropriate resolution such as.
Word of caution too. Before adjusting any sp_configure settings with RECONFIGURE make sure you run the following to check for any settings which are not yet active. It is expected to see 'min server memory (MB)' in the results of this though if you leave the setting default at 0 as SQL must reserve the minimum memory possible which is 16mb.
Legal Stuff: The contents of this blog is provided “as-is”. The information, opinions and views expressed are those of the author and do not necessarily state or reflect those of any other company with affiliation to the products discussed. This includes any URLs or Tools. The author does not accept any responsibility from the use of the information or tools mentioned within this blog, and recommends adequate evaluation against your own requirements to measure suitability.
;WITH CTE(QueryCount, StatementTextForExample, plan_handle, QueyHash, QueryPlanHash, CacheObjType,
ObjType)
AS
(
SELECT
COUNT(query_stats.query_hash) AS QueryCount
, MIN(query_stats.query_text) AS StatementTextForExample
, MIN(query_stats.plan_handle) AS plan_handle
, query_stats.query_hash AS QueryHash
, query_stats.query_plan_hash AS QueryPlanHash
, query_stats.CacheObjType
, query_stats.ObjType
FROM
(
SELECT
qs.query_hash
, qs.query_plan_hash
, qs.plan_handle
, cp.cacheobjtype as [CacheObjType]
, cp.objtype as [ObjType]
, SUBSTRING(qt.[text], qs.statement_start_offset/2, (
CASE
WHEN qs.statement_end_offset = -1 THEN LEN(CONVERT(NVARCHAR(MAX), qt.[text])) * 2
ELSE qs.statement_end_offset
END - qs.statement_start_offset)/2
) AS query_text
FROM
sys.dm_exec_query_stats AS qs
INNER JOIN sys.dm_exec_cached_plans cp ON cp.plan_handle = qs.plan_handle
CROSS APPLY sys.dm_exec_sql_text(qs.[sql_handle]) AS qt
WHERE qt.[text] NOT LIKE '%sys.dm_exec_query_stats%'
AND cp.objtype = 'AdHoc'
--AND qs.last_execution_time BETWEEN DATEADD(hour,-1,GETDATE()) AND GETDATE() --change hour time frame
) AS query_stats
GROUP BY query_stats.query_hash
, query_stats.query_plan_hash
, query_stats.CacheObjType
, query_stats.ObjType
HAVING COUNT(query_stats.query_hash) > 1
)
SELECT
CTE.QueryCount
, CTE.CacheObjType
, CTE.ObjType
, CTE.StatementTextForExample
, tp.query_plan AS StatementPlan
, CTE.QueyHash
, CTE.QueryPlanHash
FROM
CTE
OUTER APPLY sys.dm_exec_query_plan(CTE.plan_handle) AS tp
ORDER BY CTE.QueryCount DESC;
--ORDER BY [Total IO] DESC;
When you identify these candidates you then need to look at what is the most appropriate resolution such as.
- Rewrite the query at the application layer to ensure it is called with parameterization from the data provider
- Rewrite the query as a stored procedure
- Enable 'optimize for ad hoc workloads' on your SQL instance
EXEC sys.sp_configure N'optimize for ad hoc workloads', N'0' GO RECONFIGURE WITH OVERRIDE GO
- Enable 'Forced Parameterization' on the affected database
USE [master] GO ALTER DATABASE [AdventureWorksPTO] SET PARAMETERIZATION SIMPLE WITH NO_WAIT GO
Word of caution too. Before adjusting any sp_configure settings with RECONFIGURE make sure you run the following to check for any settings which are not yet active. It is expected to see 'min server memory (MB)' in the results of this though if you leave the setting default at 0 as SQL must reserve the minimum memory possible which is 16mb.
SELECT * FROM sys.configurations WHERE Value <> value_in_use
Legal Stuff: The contents of this blog is provided “as-is”. The information, opinions and views expressed are those of the author and do not necessarily state or reflect those of any other company with affiliation to the products discussed. This includes any URLs or Tools. The author does not accept any responsibility from the use of the information or tools mentioned within this blog, and recommends adequate evaluation against your own requirements to measure suitability.
