Hello Geeks and welcome to the Day 46 of the long series of One DMV a day. In this series of blogs I will be exploring and explaining the most useful DMVs in SQL Server. As we go ahead in this series we will also talk about the usage, linking between DMVs and some scenarios where these DMVs will be helpful while you are using SQL Server. For the complete list in the series please click here.
Till yesterday I have covered various aspects of execution. The DMVs included from sessions, plans, stats, queues and memory grants. Today I will cover an interesting DMV. Rather two of them. You will find these useful when you encounter a bad performing statement or procedure. I will be talking about sys.dm_exec_describe_first_result_set and sys.dm_exec_describe_first_result_set_for_object
You may have encountered statements written by doing a SELECT * FROM. Either in application dynamic code or the procedures. While troubleshooting a performance issues, you may not want to run such statements directly. Or you may need some metadata information on complex queries result set. Using sys.dm_exec_describe_first_result_set and sys.dm_exec_describe_first_result_set_for_object you can get the first result set description for individual statements and objects like procedures and triggers.
sys.dm_exec_describe_first_result_set and sys.dm_exec_describe_first_result_set_for_object returns information like the columns details in the first result set. This includes the collation settings, the type description, column properties like identity, updatable, unique, computed, sparse etc., if it is ordered on a column, error details, xml column details etc.
So let’s see a sample output from sys.dm_exec_describe_first_result_set/sys.dm_exec_describe_first_result_set_for_object. The output details are same for both. The second one works for procedures and the first one is for all T-SQL Statements.
SELECT * FROM sys.dm_exec_describe_first_result_set( N'SELECT * FROM HumanResources.vEmployee; SELECT * FROM Person.Address', NULL, 0)
There first parameter in the query is the statement. The second one is the parameters list used in the queries. The third parameter is to specify the FOR BROWSE option. The output is only for the first result set even though it has multiple statements.
The output is neat. It provides a lot more information on the columns in the first result set. This brings me to the end of execution related DMVs. Tomorrow I will talk about another set of DMVs. Stay tuned. Till then
Happy Learning,
Manu
Like us on FaceBook | Join the fastest growing SQL Server group on FaceBook |
Follow me on Twitter | Follow me on FaceBook
