20
Aug
10

Chapter 29 – My favorite DMV’s and why

This chapter is awesome.  Aaron Bertrand (Blog | Twitter) takes the time to explain and provide code examples for 15 of the DMV’s introduced in SQL Server 2005 and SQL Server 2008. 

I’ve been using DMV’s for a while now and the amount of information you can glean from them about your environment is incredible.  Coupled with the prior chapter on DDL and DML auditing, Microsoft is doing an excellent job with providing more information, in a much more intuitive fashion than I can remember since SQL Server 6.5.  This is a very, very welcome change as it allows DBA’s, developers and the like to have visibility into the HOW, the WHAT, the WHERE, the WHY and the WHO is doing what inside of your databases.

One of my personal favorite DMV’s is sys.dm_exec_sql_text.  The reason that I love this one so much is that it’s typically used via a cross join to other DMV’s such as sys.dm_exec_query_stats.  The DMV sys.dm_exec_query_stats provides statistics around query plans.  Specifically, how many times that plan has been executed, the cost of the plan and then you can cross join that plan to actually SEE the t-sql that’s being run.  This is incredibly valuable when looking to see if you have a lot of re-compiling going on or a lot of dynamic t-sql or linq-to-sql that’s not reusing query plans.  Addressing some of the big hitters here (from either a cost perspective or a quantity of re-compiles perspective) can significantly alter your overall database / server performance.

Aaron goes on this chapter to provide a few full-blown examples of his favorite DMV’s in use and why he likes them.  He provides all of the code necessary for an improved sp_who2, how to get statistics for stored procedures, finding unused stored procedures, finding inefficient and unused indexes, finding inefficient queries and finding missing indexes.  

An outstanding chapter Aaron, thank you.

Advertisements

0 Responses to “Chapter 29 – My favorite DMV’s and why”



  1. Leave a Comment

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s


Chapters

Enter your email address to subscribe to this blog and receive notifications of new posts by email.

Join 13 other followers