Investigate your SQL's compilation

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

            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