Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Performance Tuning with SQL Server Dynamic Management Views

By Tim Ford,

DBA Handbooks

Performance Tuning with SQL Server Dynamic Management ViewsPerformance Tuning with SQL Server Dynamic Management Views by Tim Ford and Louis Davidson
(1st Edition, September 2010)

Buy Printed Book: $29.99
Download ePub version: Free
Download PDF: Free


This is the book that will de-mystify the process of using Dynamic Management Views to collect the information you need to troubleshoot SQL Server problems. It will highlight the core techniques and "patterns" that you need to master, and will provide a core set of scripts that you can use and adapt for your own requirements.

View Related Books:


Editorial Review

Dynamic Management Views (DMVs) are a significant and valuable addition to the DBA's troubleshooting armory, laying bare previously unavailable information regarding the under-the-covers activity of your database sessions and transactions.

Why, then, aren't all DBAs using them? Why do many DBAs continue to ignore them in favour of "tried and trusted" tools such as sp_who2, DBCC OPENTRAN, and so on, or make do with the "ready made" reports built into SSMS? Why do even those that do use the DMVs speak wistfully about "good old sysprocesses"?

There seem to be two main factors at work. Firstly, some DBAs are simply unaware of the depth and breadth of the information that is available from the DMvs, or how it might help them troubleshoot common issues. This book investigates all of the DMVs that are most frequently useful to the DBA in investigating query execution, index usage, session and transaction activity, disk IO, and how SQL Server is using or abusing the operating system.

Secondly, the DMVs have a reputation of being difficult to use. In the process of exposing as much useful data as possible, sysprocesses has been de-normalized, and many new views and columns have been added. This fact, coupled with the initially-baffling choices of what columns will be exposed where, has lead to some DBAs to liken querying DMVs to "collecting mystic spells".

In fact, however, once you start to write your own scripts, you'll see the same tricks, and similar join patterns, being used time and again. As such, a relatively small core set of scripts can be readily adapted to suit any requirement. This book is here to de-mystify the process of collecting the information you need to troubleshoot SQL Server problems. It will highlight the core techniques and "patterns" that you need to master, and will provide a core set of scripts that you can use and adapt for your own systems, including how to:

  • Root out the queries that are causing memory or CPU pressure on your system
  • Investigate caching, and query plan reuse
  • Identify index usage patterns
  • Track fragmentation in clustered indexes and heaps
  • Get full details on blocking and blocked transactions, including the exact commands being executed, and by whom.
  • Find out where SQL Server is spending time waiting for resources to be released, before proceeding
  • Monitor usage and growth of tempdb

The DMVs don't make existing, built-in, performance tools obsolete. On the contrary, they complement these tools, and offer a flexibility, richness and granularity that are simply not available elsewhere. Furthermore, you don't need to master a new GUI, or a new language in order to use them; it's all done in a language all DBAs know and mostly love: T-SQL.

About the Author

Louis Davidson has been in the IT industry for 15 years as a corporate database developer and data architect. Currently he is the Data Architect for Compass Technology in Chesapeake, Virginia, supporting the Christian Broadcasting Network and NorthStar Studios in Nashville, Tennessee. Louis has been a Microsoft MVP since 2004, and is an active volunteer for the Professional Association for SQL Server working in their Special Interest Groups. He is the author of SQL Server 2005 Database Design and Optimization.

Tim Ford is a senior database administrator with Spectrum Health in Grand Rapids, Michigan. He has been an active volunteer with The Professional Association for SQL Server (PASS) since 2002 and a certified solutions developer (MCSD) since 2001. Currently he is attempting to teach himself web development at www.ford-it.com. In his free time he usually has a camera, game controller, spatula, guitar, handlebars, book, or beer in his hands. His wife and two young sons, Austen and Trevor keep him sane when not driving him completely crazy.


Book Details

Paperback: 200 pages
Publisher: Red Gate Books

Total article views: 7027 | Views in the last 30 days: 70
 
Related Articles
FORUM

ACtive-ACtive Clustering SQL server 2005

ACtive-ACtive Clustering SQL server 2005

FORUM

ACtive-ACtive Clustering SQL server 2005

ACtive-ACtive Clustering SQL server 2005

BLOG

SQL Server: String Pattern Matching

It is common scenario that, we might need to extract the data from the SQL server based on some pat...

FORUM

Database load distribution into a two node active/active cluster?

How to load distribute 45 databases on one server into a two node active/active cluster setup

FORUM

Sql Server Information

Gathering Server information

Tags
books    
management views    
 
Contribute

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones
Editor, SQLServerCentral.com

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones