SET NOCOUNT ON; USE Northwind; -- use your database name here DBCC FREEPROCCACHE; -- empty the procedure cache GO -- we will use tempdb..OptStats table to capture -- the information from several executions -- of sys.dm_exec_query_optimizer_info IF (OBJECT_ID('tempdb..OptStats') IS NOT NULL) DROP TABLE tempdb..OptStats; GO -- the purpose of this statement is -- to create the temporary table tempdb..OptStats SELECT 0 AS Run, * INTO tempdb..OptStats FROM sys.dm_exec_query_optimizer_info; GO -- this will populate the procedure cache -- with this statement's plan so that it will not -- generate any optimizer events when executed -- next time -- the following GO is intentional to ensure -- the query plan reuse will happen for the following -- INSERT for its next invocation in this script GO INSERT INTO tempdb..OptStats SELECT 1 AS Run, * FROM sys.dm_exec_query_optimizer_info; GO -- same reason as above; observe the "2" replaced "1" -- therefore we will have different plan GO INSERT INTO tempdb..OptStats SELECT 2 AS Run, * FROM sys.dm_exec_query_optimizer_info; GO -- empty the temporary table TRUNCATE TABLE tempdb..OptStats GO -- store the "before run" information -- in the temporary table with the output -- of sys.dm_exec_query_optimizer_info -- with value "1" in the column Run GO INSERT INTO tempdb..OptStats SELECT 1 AS Run, * FROM sys.dm_exec_query_optimizer_info; GO -- your statement or batch is executed here /*** the following is an example SELECT C.CustomerID, COUNT(O.OrderID) AS NumOrders FROM dbo.Customers AS C LEFT OUTER JOIN dbo.Orders AS O ON C.CustomerID = O.CustomerID WHERE C.City = 'London' GROUP BY C.CustomerID HAVING COUNT(O.OrderID) > 5 ORDER BY NumOrders; ***/ GO -- store the "after run" information -- in the temporary table with the output -- of sys.dm_exec_query_optimizer_info -- with value "2" in the column Run GO INSERT INTO tempdb..OptStats SELECT 2 AS Run, * FROM sys.dm_exec_query_optimizer_info; GO -- extract all "events" that changed either -- the Occurrence or Value column value between -- the Runs 1 and 2 from the temporary table. -- Display the values of Occurrence and Value -- for all such events before (Run1Occurrence and -- Run1Value) and after (Run2Occurrence and -- Run2Value) executing your batch or query. -- This is the result set generated by the script. WITH X (Run,Counter, Occurrence, Value) AS ( SELECT * FROM tempdb..OptStats WHERE Run=1 ), Y (Run,Counter, Occurrence, Value) AS ( SELECT * FROM tempdb..OptStats WHERE Run=2 ) SELECT X.Counter, Y.Occurrence-X.Occurrence AS Occurrence, CASE (Y.Occurrence-X.Occurrence) WHEN 0 THEN (Y.Value*Y.Occurrence-X.Value*X.Occurrence) ELSE (Y.Value*Y.Occurrence-X.Value*X.Occurrence)/(Y.Occurrence-X.Occurrence) END AS Value FROM X JOIN Y ON (X.Counter=Y.Counter AND (X.Occurrence<>Y.Occurrence OR X.Value<>Y.Value)); GO -- drop the temporary table DROP TABLE tempdb..OptStats; GO
Aug 27, 2007
Investigate your SQL's compilation
Following script to investigate the compiler events and corresponding counters and values of your statement
Subscribe to:
Post Comments (Atom)
No comments:
Post a Comment