I had a recent consulting engagement where a query had unpredictable performance on an isolated test environment.  I had two initial questions:<\/p>\n

– What were the wait stats associated with the unpredictable query?<\/p>\n

– What did the query execution plan look like in the \u201cgood\u201d versus \u201cbad\u201d condition?<\/p>\n

To address the query wait stats question, I set up an Extended Events session to track the query\u2019s accumulated wait stats for each execution.  The performance issue would only happen a couple of times today, so I scheduled a job to loop the execution – without disconnecting, and keeping the session ID being referenced in the Extended Events session.  I set up a separate table to track each test run (begin\/end time).  This way the long periods could be associated back to the Extended Events session.<\/p>\n

We caught a few instances of the long running query, and the associated wait stats were primarily related to PAGEIOLATCH_SH.  That opened up other considerations which I don\u2019t cover in this post, but I was still interested in seeing the execution plan for the long-running time periods versus the \u201csteady\u201d state.<\/p>\n

For example, let\u2019s say you have the following query:<\/p>\n

EXEC<\/span> sp_executesql    \r\n    N'SELECT     p.ProductLine,\r\n               SUM(f.SalesAmount) TotalSalesAmount\r\n    FROM [dbo].[FactInternetSales] f\r\n    INNER JOIN [dbo].[DimProduct] p ON\r\n         f.ProductKey = p.ProductKey\r\n    GROUP BY p.ProductLine\r\n    ORDER BY p.ProductLine'<\/span>;\r\nGO<\/pre>\n