Databases Reference
In-Depth Information
Server 2005, SQL Server 2008, and pre-SP1 builds of SQL Server 2008 R2; but the older your
version of SQL Server, the fewer of these queries are going to work for you.
In order to run most DMV and DMF queries, you need the VIEW SERVER STATE permission on
your SQL Server instance. You will already have this permission if you have system administrator
rights within a SQL Server instance, but you should probably create a dedicated login and matching
user that has VIEW SERVER STATE permission for use by non-administrators or monitoring appli-
cations. Once you have the rights to run DMV and DMF queries, you are ready to get started.
I strongly recommend that you run each of the queries in the following sections one at a time, after
reading the background and instructions i rst. After you get the results of each query, take a few
moments to peruse them to ensure that they make sense. Check the notes about how to interpret the
results, and consider what you are seeing and whether the results seem to reinforce or contradict
other results and metrics that you may have gathered. It's a good idea to save the results of these
queries in individual, labeled tabs in a spreadsheet so that you have a baseline and a record of the
changing results over time.
For these server- and instance-level queries, it does not really matter to which database on the
instance you are connected. Once you reach the database-specii c queries starting at Listing 15-32,
don't forget to change your database context to the particular database you are interested in. This
may seem obvious, but I have seen many people run an entire set of database-specii c queries while
they are still pointed at the master database. This will give you a wealth of useless information
about the master database!
First, you want to i nd out exactly what version, edition, and build of SQL Server you have running
on your instance of SQL Server. You also want to know whether it is x64 or x86 and what operating
system you are running on. One very simple, non-DMV query, shown in Listing 15-1, gives you all
that information, including the compile date and time of your SQL Server build.
LISTING 15-1:
SQL Server and operating system information
-- SQL and OS Version information for current instance
SELECT @@VERSION AS [SQL Server and OS Version Info];
-- SQL Server 2012 Builds
-- Build Description
-- 11.00.1055 CTP0
-- 11.00.1103 CTP1
-- 11.00.1540 CTP3
-- 11.00.1515 CTP3 plus Test Update
-- 11.00.1750 RC0
-- 11.00.1913 RC1
-- 11.00.2300 RTM
-- 11.00.2316 RTM CU1
-- 11.00.2325 RTM CU2
-- 11.00.2809 SP1 CTP3 (un-supported in production)
A sample of the results you will get from the preceding query is shown in Listing 15-2.
Search WWH ::
Custom Search