Need a 360-Degree View of Costly Queries in Your SQL Server Data Pipeline?

There’s a Dynamic Management View for that!

Photo by Christopher Burns on Unsplash

It’s called sys.dm_exec_query_stats , and it returns information about cached queries in SQL Server. It's a great way to identify and diagnose expensive queries running in your SQL Server instance.

I’ll share exactly how to do this with a clean, formatted script, but first, let’s touch on what Dynamic Management Views are in case you’re new to the term.

Understanding Dynamic Management Views (DMVs)

SQL Server provides several out-of-the-box views that you can query to retrieve information about server state, like query performance. These are called Dynamic Management Views (DMVs). You can query them just like regular tables or views. The information returned can help you identify expensive queries, detect blocking, monitor indexes, and much more.

Here are a few commonly used DMVs:

• sys.dm_exec_query_stats — Query execution statistics
• sys.dm_exec_requests — Currently executing requests
• sys.dm_exec_sessions — Active user sessions
• sys.dm_os_wait_stats — Wait statistics for performance tuning