Dynamic Management Views
In my "APPLY Operator" post I used the example query below to illustrate the use of CROSS APPLY. I mentioned that the query itself was pretty cool, and I would post about it in a future post. Well, here we go.
SELECT
TOP 5 *
FROM
sys.dm_exec_query_stats qs
CROSS
APPLY sys.dm_exec_query_plan(qs.plan_handle)
CROSS
APPLY sys.dm_exec_sql_text(qs.sql_handle)
ORDER
BY max_elapsed_time DESC
DMVs! SQL 2005 introduced Dynamic Management Views (And Functions). These are not like normal views or tables, which are persisted on disk, but they are in memory structures that keep various data about your server since the last time it was started. So what does this query give us?
Well, sys.dm_exec_query_stats keeps data on all the queries that have run on your server, so we are looking for the top 5 longest running queries. That by itself is nice, as it is not information you could get from SQL 2000 without having some sort of trace running. BUT that is not all we are getting here. We are also getting the text of each of these queries from sys.dm_exec_sql_text and the query plan that SQL server used to execute the query from sys.dm_exec_query_plan.
Now most developers I know don't really know nor care what query plan their queries use. But for a DBA, or someone who troubleshoots SQL Performance, this is amazing information to have without running profiler, or any other trace utility. There are many other DMVs that are available in SQL 2005. Yes your favorite system tables are still available (sysobjects, sysprocesses, sysindexes etc…) but will soon be removed from the product, and replaced by the DMVs.
The other cool thing you can do with the query plan, if your not to keen on reading XML, is save it as a .sqlplan file (Click the XML Link, File->Save As, change to all files, query_plan1.sqlplan). Then double click the file, and you get the graphical query plan that you are used to seeing.
Wouldn't it be cool if when the Query Optimizer optimizes a query, if it decided there was an index that it could use that didn't exist, it would log that somewhere? Guess what…it does. Look at the Missing index DMVs such as sys.dm_db_missing_index_details. Not only do they log it, they tell you how much better the query would run if it did exist.
If you want to learn more about a specific DMV, or DMVs in general, open Books Online, and type in sys.dm_ (All DMVs start with sys.dm_) and they will all be listed. Learn them, use them.