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