Post: Find query execution timeouts with Query Store

[LitKnd]

Comments entered here will automagically appear at https://www.littlekendra.com/2022/03/11/find-execution-timeouts-query-store/

[m60freeman]

If a query times out, and then runs successfully several times, and then times out again, I think there may be confusion in the results. I add a join to sys.query_store_runtime_stats_interval ON runtime_stats_interval_id and put the start_time and end_time in the results. I also have a subquery in the SELECT (which I'll try to refactor into an OUTER APPLY at some point) on sys.query_store_wait_stats because I've found it helpful to get a clue as to why the timeout occurred.

,Waits  =   (   SELECT  STUFF(  (   SELECT  ', ' + CONVERT(VARCHAR, wait_category_desc) + ': ' + CONVERT(VARCHAR, total_query_wait_time_ms) + 'ms'
                                      FROM  (   SELECT  ws.wait_category_desc, ws.total_query_wait_time_ms
                                                  FROM  sys.query_store_wait_stats ws
                                                 WHERE  ws.runtime_stats_interval_id    = rs.runtime_stats_interval_id
                                                   AND  ws.plan_id                      = rs.plan_id
                                                   AND  rs.execution_type               = ws.execution_type
                                            ) WaitValues
                                     ORDER  BY total_query_wait_time_ms DESC
                                       FOR   XML PATH('')
                                ), 1, 2, ''
                        ) WaitList
            )

If it is due to a lock, I have a blocking situation. If it shows a crazy long CPU wait, that is something else entirely.

[LitKnd]

@m60freeman that is awesome!

[m60freeman]

OK, you finally motivated me to make a blog post for the first time in over 10 years. It references this post, and includes my full (way over the top) script.

[pina-porceddu]

@m60freeman where is your blog post?

[m60freeman]

@pina-porceddu You can find my blog, such as it is, at https://m60freeman.blogspot.com