Main Content

DMV's as a Shortcut to Procedure Tuning

A talk by at PASS Summit 2009

About the Talk

November 3, 2009 10:00 AM

Seattle, WA

Seattle, WA

Dynamic Management Views and Functions (DMV/F) expose a wealth of information to the database administrator. However, they also expose information that is vital to the database developer. Most of the time you gather query performance data through server-side traces. This session will show how to gather information from the DMV/Fs for currently executing, and recently executed queries. It will demonstrate combining this information with other DMV/Fs to get the query text. I'll show how to also retrieve execution plans. With the established foundation for what can be retrieved, you can begin to expand out and see what's currently residing in cache and get the execution plan and query text for these objects. I'll show where you can get aggregate information for the queries in cache to determine which queries are being accessed most frequently or which is using the most CPU. I'll demonstrate methods for combining this information with XML queries to retrieve Missing Index recommendations from the query optimizer. I'll show how to determine which indexes are being used in your system and which are not. All of this will be focused, not on the DBA, but on the query writer, the developer or database developer that needs information to tune and troubleshoot data access.

Ratings and Recommendations

This Talk hasn't been rated yet. Sign In to rate Talks.

comments powered by Disqus