Databases Reference
In-Depth Information
This chapter walks you through a set of queries that you can use for your SQL Server health check,
explaining what they mean and how to interpret the results. These queries start at the hardware and
instance level, and then enable you to drill down to a particular database to gather more specii c
information about it.
One roadblock that many database administrators face in collecting this type of information is the
bureaucracy within their company or organization. Quite often, someone else in a different depart-
ment is in charge of provisioning and managing the actual database server hardware and operating
system on the database servers. This person is usually a system administrator or system engineer. In
larger organizations, a SAN administrator is often in charge of the storage subsystems. These other
people, who often have different priorities than you, and usually have relatively little knowledge of
SQL Server, can be big obstacles in your quest to gather important information about your database
servers. They may view your information-gathering efforts as an unwelcome invasion of their terri-
tory and area of expertise, and thus may be reluctant to cooperate with you.
I have often asked other DBAs to tell me about how a particular database server is coni gured and
what type of hardware and storage it is using, only to get vague and nearly useless answers that will
not enable evaluation of a server or solve a performance problem. Many DBAs are not allowed to
actually log on to the desktop of their database servers, and are completely at the mercy of their sys-
tem administrator and SAN administrator for all server and storage coni guration and management.
Because of this, many DBAs are completely in the dark about anything they cannot easily see from
SQL Server Management Studio (SSMS), and they have very little information about the details of
their server hardware and storage subsystems. I think this type of organizational policy is a huge
mistake, as it greatly hinders the overall effectiveness of database administrators. If you are faced
with this type of situation as a DBA, you should make every effort to properly improve the matter
by reaching out to your counterparts in other departments to explain what needs to be done and
why it is important. Policies can often be changed, so don't just accept the situation!
However, regardless of any bureaucratic or organizational obstacles in your way, you can still
use techniques from within SQL Server Management Studio (SSMS) to gather most of what you
need to do a relatively comprehensive SQL Server health check. One of the most useful and easy to
use techniques is to run a standard set of dynamic management view (DMV) and dynamic manage-
ment function (DMF) queries to gather health-check information about your servers, instances,
and databases.
RUNNING DMV AND DMF QUERIES
Since they were i rst introduced in SQL Server 2005, DMVs and DMFs have been useful tools for
easily gathering a wealth of valuable information about your hardware, a SQL Server instance, or
individual databases in an instance. With each new version of SQL Server, enhancements have been
made to these DMVs and DMFs that increase their utility. SQL Server 2012 is no exception, and
offers a number of completely new DMVs and DMFs that you can take advantage of during a SQL
Server health check.
This set of queries is designed to be run on SQL Server 2012. Most of the queries will also work
on SQL Server 2008 R2 Service Pack 1 or later, but some will not because Microsoft made a few
late-breaking changes to some DMVs in SQL Server 2012. Some of the queries will also run on SQL
Search WWH ::
Custom Search