Investigate your SQL's compilation

        Following script to investigate the compiler events and corresponding counters and values of your statement

        <pre data-sub="prettyprint:_">
        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

        </pre>