Performance Tuning - Professional SQL Server 2005 Performance Tuning - page 11

These reports are a new feature that shipped after SQL Server 2005. The reports provide a wealth of
performance information with no work required other than installing them. All you need to know
is that they are there, where to find them, and how to read them. Chapter 13 covers the SQL Server
Performance Dashboard.

DynamicManagementViews
The Dynamic Management Views (DMVs) in SQL Server 2005 are the source of a wealth of information
about what is going on inside SQL Server. In earlier versions of SQL Server, some of this information was
made available in system tables. In SQL Server 2005, the amount of information about what SQL Server
is doing internally has increased dramatically.

Anyone looking at SQL Server performance should have a good understanding of the key DMVs.

Many of the chapters in this topic discuss in detail the DMVs relevant for each chapter's topic. Another
great source of information on using these DMVs is the SQL Server Best Practices website at:

http://technet.microsoft.com/en-gb/sqlserver/bb331794.aspx

This page includes a link to the SQL Server Best practices toolbox, which contains yet more extremely
useful Scripts for querying the DMVs:

http://www.microsoft.com/technet/scriptcenter/scripts/sql/sql2005/default.mspx ?mfr = true

DirectAdministratorsConnection—DAC
Sometimes a DBA trying to diagnose a busy server needs to find who is using all the resources. Other
times a DBA needs to kill a long-running query. One of the challenges is that it is not always possible to
get a new connection to SQL to start looking at what is going on. This is because the server no longer has
any resources available to create a new connection.

SQL Server 2005 resolves this problem with the Direct Administrators Connection. This is a special
connection that uses considerably fewer resources and is quite strongly restricted in what it can do, but
it does allow a DBA to connect to a system that wouldn't be available otherwise. This is a tremendously
useful feature and one that every SQL Server performance tuner should be aware of.

If your server is in a state where you need to use the DAC to connect, you want to make sure you use
as few resources as possible. Connecting using SQL Server Management Studio can be very resource
intensive as the simple task of clicking different nodes in the server explorer can cause resource intensive
queries to run on the server.

The better option for using the DAC is to connect through SQLCMD, the command line interface to SQL
Server. Using SQLCMD will use much fewer server resources than using SSMS, but it does challenge
the user to know the many useful DMVs needed to identify any resource-hogging queries. Alternatively,
you can keep a suite of useful SQL Scripts accessible and run these through SQLCMD across the DAC to
find and kill the resource hog. The question is where to keep these valuable scripts so they are accessible.
Some useful places include:

AUSBthumbdrive

❑

A team website

❑

A well-known file share

❑