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.
Katmai (SQL 2008) CTP5 released. I haven't had a chance to play around with it yet, but I plan to install it soon and check out the new features. My understanding is aside from Clustering setup, this CTP has pretty much everything that will be in the final product. Try it out, let me know what you think!!!
What if you have SQL Server table and would like to use one of the columns as an input to a Table Valued Function (TVF)? Well that depends on the version of SQL Server you have. In SQL 2000, you really couldn't. You would have to take each column value, and run the TVF separately. This is not a practical solution if your result set is of any significant size. BUT, in SQL Server 2005 there is a cool new operator called the APPLY operator. What the APPLY operator allows you to do is essentially join your table to a TVF (Or table, or view, but the TVF is the coolest use IMO).
Example:
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
So what is with the "CROSS"? Well, there are 2 different types of APPLYs. If you think of APPLY like a JOIN, then the CROSS APPLY is like an INNER JOIN. You will only receive results for rows where the TVF returns a row for the column input. An OUTER APPLY is, surprisingly enough, like an OUTER JOIN. You will receive results for all Rows from the Outer table, and NULLS if no row is returned from the TVF.
This example Query is pretty cool itself, but I will get into that in another post.