sys.dm_exec_describe_first_result_set – Day 46 – One DMV a Day

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)

sys.dm_exec_describe_first_result_set

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

   

About Manohar Punna

Manohar Punna is a Microsoft Data Platform Consultant and a Data Platform MVP. In his day to day job he works on building database and BI systems, automation, building monitoring systems, helping customers to make value from their data & explore and learn. By passion Manohar is a blogger, speaker and Vice President of DataPlatformGeeks. He is a community enthusiast and believes strongly in the concept of giving back to the community. Manohar is a speaker at various Data Platform events from SQL Server Day, various user groups, SQLSaturdays, SQLBits, MS Ignite, SSGAS & DPS. His One DMV a Day series is the longest one day series on any topic related to SQL Server available so far.

View all posts by Manohar Punna →

Leave a Reply

Your email address will not be published.