Friday, April 15, 2016

TSQL To identify queries which are candidates for Parameterization

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.

;WITH CTE(QueryCount, StatementTextForExample, plan_handle, QueyHash, QueryPlanHash, CacheObjType, 
    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
      , qs.query_plan_hash 
      , qs.plan_handle
      , cp.cacheobjtype as [CacheObjType]
      , cp.objtype as [ObjType]
      , SUBSTRING(qt.[text], qs.statement_start_offset/2, ( 
                       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 
               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
   , CTE.CacheObjType
   , CTE.ObjType
   , CTE.StatementTextForExample
   , tp.query_plan AS StatementPlan
   , CTE.QueyHash
   , CTE.QueryPlanHash 
   OUTER APPLY sys.dm_exec_query_plan(CTE.plan_handle) AS tp 
--ORDER BY [Total IO] DESC; 

When you identify these candidates you then need to look at what is the most appropriate resolution such as.

  1. Rewrite the query at the application layer to ensure it is called with parameterization from the data provider
  2. Rewrite the query as a stored procedure
  3. Enable 'optimize for ad hoc workloads' on your SQL instance
    EXEC sys.sp_configure N'optimize for ad hoc workloads', N'0'
  4. Enable 'Forced Parameterization' on the affected database
    USE [master]
Of cause the appropriate option really depends on a larger view of your environment and applications that only you can determine. If you do have any thoughts on this please feel free to add them to the comments below to help others.

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.



  1. Alta calidad comprar réplicas de relojes de un relojero confiable? aquí puedo ofrecerte el mejor réplica de relojes rolex in high quality, including Cartier, Breitling,Omega, etc.

  2. The AAA Cartier Replica watches sale at are fake watchesbest quality cheap fake watch from China,The high end Cartier replica watches include Ballon Bleu De Cartier, replica cartier watchesCalibre De Cartier,Cartier La Dona,Cartier Love Watch,Cartier Pasha,Cartier Roadster,Cartier Santos,Cartier Tank replica watches,We can shipp to Uk,USA,canada,australia,and almost all country worldwide

  3. The Royal Oak Offshore collection replica watches uk has defied established conventions since 1993,replica royal oak watches giving an ever more sporty, masculine and powerful take on the iconic Royal Oak and its aesthetic codes.

  4. The Audemars Piguet Jules Audemarsfake watches collection commemorates one of the brand’s founders. The line is characterized by its traditional and timeless round cases and overall minimalist design. replica jules audemars watchesHowever, behind the simplicity of these watches is an array of impressive functions, such as tourbillons, moon phases, and chronographs. Designed with both men and women in mind, the Jules Audemars collection fully embodies both the range and restraint of the brand.

  5. The Royal Oak Offshore collection replica watches uk has defied established conventions since 1993,replica royal oak watches giving an ever more sporty, masculine and powerful take on the iconic Royal Oak and its aesthetic codes.

  6. Alexander McQueen serves up a collection Cheap ALEXANDER MCQUEEN Shoes for men and women of elevated everyday styles, cheap ALEXANDER MCQUEEN shirt womenstailoring rich in tradition and singular pieces with a gothic-chic sensibility. Dramatic accessories – think leather and chains and skull-motif embellishments – top off the line that's a hit with all the critics.

  7. cheap Soccer Jerseys is the best soccer jerseys wholesale xavi hernandez Jerseys Buy the cheap soccer jerseys, soccer shirts, soccer kits, training kits with wholesale price.

  8. Short for Monestier-de-Clermont, an Alpine town near Grenoble, France, Moncler was founded in 1952 by René Ramillon as an outdoor-gear company, cheap moncler jackets moncler producing sleeping bags, tents, and other mountain-climbing necessities. Today, the French-Italian label is best known for its lightweight, super-warm quilted down jackets, cheap moncler trainers as well as ready-to-wear and accessories hardy enough to take on harsh winter weather.

  9. Invest in an heirloom to cherish forever with a timepiece from the fake cartier watches collection. A fusion of cutting-edge technology and heritage craftsmanship, replica calibre de cartier carbon diver watchesCartier’s fine watches carry the replica calibre de cartier carbon diver watches prestige of the brand’s illustrious history and exceptional quality.

  10. Since 1848, Omega watches built a reputation of being the ultimate in luxurious elegance and sporty sophistication. In 1931, replica watches salethe brand entered the Geneva Observatory trials, replica omega watches salecompeting in several categories designed to test the rigor and mechanical reliability of the Omega timepiece. The brand won all six categories and thereby adopted the slogan, "Omega, exact time for life." In 1965, NASA selected Omega to be the official watch used in training and space exploration. A true giant in name and reliability, Omega produces elegant watches that are impervious to changes in fashion. At , we offer a variety of Omega watches for sale — at the lowest prices online. When you are ready to buy an Omega watch, browse our selection that includes the: Seamaster, Speedmaster, 1957 Trilogy, DeVille, Planet Ocean, and Constellation.