Using a ‘User Query Store’ for Exceptions and Timeouts

by Mike Boswell – Data Platform Solution Architect.

In the previous post, I mentioned that as a Data Solution Architect we regularly work with clients to pilot new data platforms and with SQL Server 2016 an increasing number of clients are looking to modernise those SQL Platforms.

We spoke of how you could create a 'User Query Store' to retain test results.

More recently I had to investigate queries which were timing out or had exceptions. These are usually the priority in getting the applications working before we start performance tuning. Within Query Store these are deemed to be "Aborted" or "Exceptions".

-- Exception Queries

SELECT qt.query_sql_text, q.query_id, qt.query_text_id, p.plan_id,

rs.runtime_stats_id, rsi.start_time, rsi.end_time, rs.avg_physical_io_reads,

rs.avg_rowcount, rs.count_executions, rs.execution_type_desc, p.query_plan, so.userobjectname, so.userobjecttype

FROM

    dbo.user_query_store_query_text qt JOIN

    dbo.user_query_store_query q ON qt.query_text_id = q.query_text_id JOIN

    dbo.user_query_store_plan p ON q.query_id = p.query_id JOIN

    dbo.user_query_store_runtime_stats rs ON p.plan_id = rs.plan_id JOIN

    dbo.user_query_store_runtime_stats_interval rsi ON rsi.runtime_stats_interval_id = rs.runtime_stats_interval_id JOIN

    dbo.user_sys_objects so on so.userobjectid = q.object_id

where rs.execution_type=4

order
by count_executions desc

-- Aborted Queries

SELECT qt.query_sql_text, q.query_id, qt.query_text_id, p.plan_id,

rs.runtime_stats_id, rsi.start_time, rsi.end_time, rs.avg_physical_io_reads,

rs.avg_rowcount, rs.count_executions, rs.execution_type_desc, p.query_plan, so.userobjectname, so.userobjecttype

FROM

    dbo.user_query_store_query_text qt JOIN

    dbo.user_query_store_query q ON qt.query_text_id = q.query_text_id JOIN

    dbo.user_query_store_plan p ON q.query_id = p.query_id JOIN

    dbo.user_query_store_runtime_stats rs ON p.plan_id = rs.plan_id JOIN

    dbo.user_query_store_runtime_stats_interval rsi ON rsi.runtime_stats_interval_id = rs.runtime_stats_interval_id JOIN

    dbo.user_sys_objects so on so.userobjectid = q.object_id

where rs.execution_type=3

order
by count_executions desc

The above queries will output the top queries that have exceptions or aborts along with the SHOWPLAN. As we have imported the sys.objects we are able to reference the object which contains the query. An object id of '0' points to an Ad-Hoc query.

The only challenge with these queries is that you are unable to get a full count of executions where the query has timed out. The reason is that the query could have a different runtime_stats_id and therefore has a separate entry in the dbo.user_query_store_runtime_stats.

Therefore, to get a total count of the number of executions, which timed out, you need to group by query_id and run the script below.

-- Exception Queries Grouped by query id.

SELECT qt.query_sql_text, q.query_id,
sum(rs.count_executions) sum_of_executions, so.userobjectname, so.userobjecttype

FROM

    dbo.user_query_store_query_text qt JOIN

    dbo.user_query_store_query q ON qt.query_text_id = q.query_text_id JOIN

    dbo.user_query_store_plan p ON q.query_id = p.query_id JOIN

    dbo.user_query_store_runtime_stats rs ON p.plan_id = rs.plan_id JOIN

    dbo.user_query_store_runtime_stats_interval rsi ON rsi.runtime_stats_interval_id = rs.runtime_stats_interval_id JOIN

    dbo.user_sys_objects so on so.userobjectid = q.object_id

where rs.execution_type=4

group
by q.query_id, qt.query_sql_text, so.userobjectname, so.userobjecttype

order
by
sum(count_executions)
desc

-- Aborted Queries Grouped by query id.

SELECT qt.query_sql_text, q.query_id,
sum(rs.count_executions) sum_of_executions, so.userobjectname, so.userobjecttype

FROM

    dbo.user_query_store_query_text qt JOIN

    dbo.user_query_store_query q ON qt.query_text_id = q.query_text_id JOIN

    dbo.user_query_store_plan p ON q.query_id = p.query_id JOIN

    dbo.user_query_store_runtime_stats rs ON p.plan_id = rs.plan_id JOIN

    dbo.user_query_store_runtime_stats_interval rsi ON rsi.runtime_stats_interval_id = rs.runtime_stats_interval_id JOIN

    dbo.user_sys_objects so on so.userobjectid = q.object_id

where rs.execution_type=3

group
by q.query_id, qt.query_sql_text, so.userobjectname, so.userobjecttype

order
by
sum(count_executions)
desc

The grouped output now allows you to see which queries you should focus on the most:

Conclusion

Query Store or a 'User Query Store' provides a very quick way to fault find timeouts and exceptions in your application. Previously you would have had to set-up a Profiler or Extended Events Trace to fault find. Query Store runs in the background and collects information for you, giving instant insight to your application performance.