SQLAuthority News – Ahmedabad SQL Server User Group Meeting Review – March 21, 2009

We had fun session with Ahmedabad SQL Server Usre Group last week on March 21, 2009. It was short session but one interesting one. We discussed about how query profiler works and how to find most popular query from SQL Server instance. We had also prepared Trace Template as well query which can ran to identify longest running query along with popular query. I received nearly 10 questions after my session and lots of time was spent answering them. The whole session was very interactive. Here is my short user group meeting review for those who missed it.

To close this user group meeting review, I want to congratulate everybody who attended it, if you need my Profiler Template and Query to identify longest running query as well popular query, please let me know and I will send them to you.

Finding Popular and Slow Queries Today

In 2009, SQL Profiler and a trace template were the standard way to catch the longest running and most popular queries. Profiler is now deprecated for the database engine. Today I use Extended Events for tracing, and Query Store, which arrived in SQL Server 2016, to see query history over time. The tools changed, but the goal is the same. For a quick look, the plan cache DMVs still answer the question we discussed that evening.

SELECT TOP 10
    qs.execution_count,
    qs.total_elapsed_time / qs.execution_count AS avg_elapsed_time,
    st.text AS query_text
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS st
ORDER BY qs.execution_count DESC;

Sort by execution_count to find the popular queries, or by total_elapsed_time to find the ones that cost the most time overall. Keep in mind that these numbers reset when SQL Server restarts or when a plan leaves the cache. That was the heart of this user group meeting review, and the idea still holds up well.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.