--- PDF PAGE 635 ---
627
audits, view SQL Server’s Registry keys, find the location of memory dump files, and find 
details of the instance’s services. In the following sections, we discuss how to view the 
Registry keys associated with the instance, expose details of SQL Server’s services, and 
view the contents of the buffer cache.
Exposing Registry Values
The sys.dm_server_registry DMV exposes key registry entries pertaining to the 
instance. The view returns three columns, which are detailed in Table 17-1.
A very useful piece of information that you can find in the sys.dm_server_registry 
DMV is the port number on which SQL Server is currently listening. The query in 
Listing 17-5 uses the sys.dm_server_registry DMV to return the port on which the 
instance is listening, assuming the instance is configured to listen on all IP addresses.
Listing 17-5.  Finding the Port Number
SELECT *
FROM (
        SELECT
        CASE
                WHEN value_name = 'tcport' AND value_data <> ''
                        THEN value_data
                WHEN value_name = 'tcpport' AND value_data = ''
                        THEN (
                                SELECT value_data
                                FROM sys.dm_server_registry
                                WHERE registry_key LIKE '%ipall'
                                        AND value_name = 'tcpdynamicports' )
Table 17-1.  sys.dm_server_registry Columns
Column
Description
Registry_key
The name of the Registry key
Value_name
The name of the key’s value
Value_data
The data contained within the value
Chapter 17  SQL Server Metadata

--- PDF PAGE 636 ---
628
        END PortNumber
        FROM sys.dm_server_registry
        WHERE registry_key LIKE '%IPAll' ) a
WHERE a.PortNumber IS NOT NULL ;
Another useful feature of this DMV is its ability to return the startup parameters 
of the SQL Server service. This is particularly useful if you want to find out if switches 
such as -E have been configured for the instance. The -E switch increases the number 
of extents that are allocated to each file in the round-robin algorithm. The query in 
Listing 17-6 displays the startup parameters configured for the instance.
Listing 17-6.  Finding Startup Parameters
SELECT *
FROM sys.dm_server_registry
WHERE value_name LIKE 'SQLArg%' ;
Exposing Service Details
Another useful DMV within the dm_server category is sys.dm_server_services, which 
exposes details of the services the instance is using. Table 17-2 describes the columns 
returned.
Table 17-2.  sys.dm_server_services Columns
Column
Description
Servicename
The name of the service.
Startup_type
An integer representing the startup type of the service.
Startup_desc
A textual description of the startup type of the service.
Status
An integer representing the current status of the service.
Status_desc
A textual description of the current service state.
Process_id
The process ID of the service.
Last_startup_time
The date and time that the service last started.
Service_account
The account used to run the service.
(continued)
Chapter 17  SQL Server Metadata

--- PDF PAGE 637 ---
629
The query in Listing 17-7 returns the name of each service, its startup type, its 
current status, and the name of the service account that runs the service.
Listing 17-7.  Exposing Service Details
SELECT servicename
        ,startup_type_desc
        ,status_desc
        ,service_account
FROM sys.dm_server_services ;
Analyzing Buffer Cache Usage
The dm_os category of DMV exposes 41 objects that contain information about the 
current status of SQLOS, although only 31 of these are documented. A particularly 
useful DMV in the dm_os category, which exposes the contents of the buffer cache, 
is sys.dm_os_buffer_descriptors. When queried, this object returns the columns 
detailed in Table 17-3.
Column
Description
Filename
The file name of the service, including the full file path.
Is_clustered
1 indicates that the service is clustered; 0 indicates that it is stand-alone.
Clusternodename
If the service is clustered, this column indicates the name of the node on 
which the service is running.
Table 17-2.  (continued)
Table 17-3.  sys.dm_os_buffer_descriptors Columns
Column
Description
Database_id
The ID of the database that the page is from
File_id
The ID of the file that the page is from
Page_id
The ID of the page
Page_level
The index level of the page
(continued)
Chapter 17  SQL Server Metadata

--- PDF PAGE 637 ---
629
The query in Listing 17-7 returns the name of each service, its startup type, its 
current status, and the name of the service account that runs the service.
Listing 17-7.  Exposing Service Details
SELECT servicename
        ,startup_type_desc
        ,status_desc
        ,service_account
FROM sys.dm_server_services ;
Analyzing Buffer Cache Usage
The dm_os category of DMV exposes 41 objects that contain information about the 
current status of SQLOS, although only 31 of these are documented. A particularly 
useful DMV in the dm_os category, which exposes the contents of the buffer cache, 
is sys.dm_os_buffer_descriptors. When queried, this object returns the columns 
detailed in Table 17-3.
Column
Description
Filename
The file name of the service, including the full file path.
Is_clustered
1 indicates that the service is clustered; 0 indicates that it is stand-alone.
Clusternodename
If the service is clustered, this column indicates the name of the node on 
which the service is running.
Table 17-2.  (continued)
Table 17-3.  sys.dm_os_buffer_descriptors Columns
Column
Description
Database_id
The ID of the database that the page is from
File_id
The ID of the file that the page is from
Page_id
The ID of the page
Page_level
The index level of the page
(continued)
Chapter 17  SQL Server Metadata

--- PDF PAGE 638 ---
630
The script in Listing 17-8 demonstrates how we can use the sys.dm_os_buffer_
descriptors DMV to determine the percentage of the buffer cache each database is 
using on the instance. This can help you during performance tuning as well as give you 
valuable insights that you can use during capacity planning or consolidation planning.
Listing 17-8.  Determining Buffer Cache Usage per Database
DECLARE @DB_PageTotals TABLE
(
CachedPages INT,
Database_name NVARCHAR(128),
database_id INT
) ;
INSERT INTO @DB_PageTotals
SELECT COUNT(*) CachedPages
        ,CASE
                WHEN database_id = 32767
                        THEN 'ResourceDb'
                ELSE DB_NAME(database_id)
        END Database_name
        ,database_id
Table 17-3.  (continued)
Column
Description
Allocation_unit_id
The ID of the allocation unit that the page is from
Page_type
The type of page, e.g., DATA_PAGE, INDEX_PAGE, IAM_PAGE, or 
PFS_PAGE
Row_count
The number of rows stored on the page
Free_space_in_bytes
The amount of free space on the page
Is_modified
A flag that indicates if the page is dirty
Numa_node
The NUMA node for the buffer
Read_microset
The amount of time taken to read the page into cache, specified in 
microseconds
Chapter 17  SQL Server Metadata

--- PDF PAGE 639 ---
631
FROM sys.dm_os_buffer_descriptors a
GROUP BY DB_NAME(database_id)
                ,database_id ;
DECLARE @Total FLOAT = (SELECT SUM(CachedPages) FROM @DB_PageTotals) ;
SELECT      Database_name,
            CachedPages,
            SUM(cachedpages) over(partition by database_name)
                    / @total * 100 AS RunningPercentage
FROM        @DB_PageTotals a
ORDER BY    CachedPages DESC ;
Note  More DMVs within the dm_os category are discussed in the “Metadata for 
Troubleshooting and Performance Tuning” section of this chapter.
Metadata for Capacity Planning
One of the most useful ways you can use metadata is during your pursuit of proactive 
capacity management. SQL Server exposes metadata that provides you with information 
about the current size and usage of your database files, and you can use this information 
to plan ahead and arrange additional capacity, before your enterprise monitoring 
software starts generating critical alerts.
Exposing File Stats
The sys.dm_db_file_space_usage DMV returns details of the space used within each 
data file of the database in which it is run. The columns returned by this object are 
detailed in Table 17-4.
Chapter 17  SQL Server Metadata

--- PDF PAGE 640 ---
632
The sys.dm_io_virtual_file_stats DMV returns IO statistics for the database and 
log files of the database. This can help you determine the amount of data being written 
to each file and warn you of high IO stalls. The object accepts database_id and file_id 
as parameters and returns the columns detailed in Table 17-5.
Table 17-4.  sys.dm_db_file_space_usage Columns
Column
Description
database_id
The ID of the database to which the file belongs.
file_id
The ID of the file within the database. These IDs are repeated between 
databases. For example, the primary file always has an ID of 1, and the 
first log file always has an ID of 2.
filegroup_id
The ID of the filegroup in which the file resides.
total_page_count
The total number of pages within the file.
allocated_extent_
page_count
The number of pages within the file that are in extents that have been 
allocated.
unallocated_extent_
page_count
The number of pages within the file that are in extents that have not 
been allocated.
version_store_
reserved_page_count
The number of pages reserved to support transactions using snapshot 
isolation. Only applicable to TempDB.
user_object_
reserved_page_count
The number of pages reserved for user objects. Only applicable to 
TempDB.
internal_object_
reserved_page_count
The number of pages reserved for internal objects. Only applicable to 
TempDB.
mixed_extent_page_
count
The number of extents that have pages allocated to different objects.
Chapter 17  SQL Server Metadata

--- PDF PAGE 640 ---
632
The sys.dm_io_virtual_file_stats DMV returns IO statistics for the database and 
log files of the database. This can help you determine the amount of data being written 
to each file and warn you of high IO stalls. The object accepts database_id and file_id 
as parameters and returns the columns detailed in Table 17-5.
Table 17-4.  sys.dm_db_file_space_usage Columns
Column
Description
database_id
The ID of the database to which the file belongs.
file_id
The ID of the file within the database. These IDs are repeated between 
databases. For example, the primary file always has an ID of 1, and the 
first log file always has an ID of 2.
filegroup_id
The ID of the filegroup in which the file resides.
total_page_count
The total number of pages within the file.
allocated_extent_
page_count
The number of pages within the file that are in extents that have been 
allocated.
unallocated_extent_
page_count
The number of pages within the file that are in extents that have not 
been allocated.
version_store_
reserved_page_count
The number of pages reserved to support transactions using snapshot 
isolation. Only applicable to TempDB.
user_object_
reserved_page_count
The number of pages reserved for user objects. Only applicable to 
TempDB.
internal_object_
reserved_page_count
The number of pages reserved for internal objects. Only applicable to 
TempDB.
mixed_extent_page_
count
The number of extents that have pages allocated to different objects.
Chapter 17  SQL Server Metadata

--- PDF PAGE 641 ---
633
Tip  IO stalls are the amount of time it takes the IO subsystem to respond to SQL 
Server.
Table 17-5.  sys.dm_io_virtual_file_stats Columns
Column
Description
database_id
The ID of the database to which the file belongs.
file_id
The ID of the file within the database. These IDs are repeated 
between databases. For example, the primary file always has an 
ID of 1 and the first log file always has an ID of 2.
sample_ms
The number of milliseconds since the computer started.
num_of_reads
The total number of reads against the file.
num_of_bytes_read
The total number of bytes read from the file.
io_stall_read_ms
The total time waiting for reads to be issued against the file, 
specified in milliseconds.
num_of_writes
The total number of write operations performed against the file.
num_of_bytes_written
The total number of bytes written to the file.
io_stall_write_ms
The total time waiting for writes to complete against the file, 
specified in milliseconds.
io_stall
The total time waiting for all IO requests against the file to be 
completed, specified in milliseconds.
size_on_disk_bytes
The total space used by the file on disk, specified in bytes.
file_handle
The Windows file handle.
io_stall_queued_read_ms
Total IO latency for read operations against the file, caused by 
Resource Governor. Resource Governor is discussed in Chapter 24.
io_stall_queued_write_ms Total IO latency for write operations against the file, caused by 
Resource Governor. Resource Governor is discussed in Chapter 24.
Unlike the previous two DMVs discussed in this section, the sys.master_files catalog 
view is a system-wide view, meaning that it returns a record for every file within every 
database on the instance. The columns returned by this view are described in Table 17-6.
Chapter 17  SQL Server Metadata

--- PDF PAGE 641 ---
633
Tip  IO stalls are the amount of time it takes the IO subsystem to respond to SQL 
Server.
Table 17-5.  sys.dm_io_virtual_file_stats Columns
Column
Description
database_id
The ID of the database to which the file belongs.
file_id
The ID of the file within the database. These IDs are repeated 
between databases. For example, the primary file always has an 
ID of 1 and the first log file always has an ID of 2.
sample_ms
The number of milliseconds since the computer started.
num_of_reads
The total number of reads against the file.
num_of_bytes_read
The total number of bytes read from the file.
io_stall_read_ms
The total time waiting for reads to be issued against the file, 
specified in milliseconds.
num_of_writes
The total number of write operations performed against the file.
num_of_bytes_written
The total number of bytes written to the file.
io_stall_write_ms
The total time waiting for writes to complete against the file, 
specified in milliseconds.
io_stall
The total time waiting for all IO requests against the file to be 
completed, specified in milliseconds.
size_on_disk_bytes
The total space used by the file on disk, specified in bytes.
file_handle
The Windows file handle.
io_stall_queued_read_ms
Total IO latency for read operations against the file, caused by 
Resource Governor. Resource Governor is discussed in Chapter 24.
io_stall_queued_write_ms Total IO latency for write operations against the file, caused by 
Resource Governor. Resource Governor is discussed in Chapter 24.
Unlike the previous two DMVs discussed in this section, the sys.master_files catalog 
view is a system-wide view, meaning that it returns a record for every file within every 
database on the instance. The columns returned by this view are described in Table 17-6.
Chapter 17  SQL Server Metadata

--- PDF PAGE 642 ---
634
Table 17-6.  sys.master_  files Columns
Column
Description
database_id
The ID of the database to which the file belongs.
file_id
The ID of the file within the database. These IDs are repeated 
between databases. For example, the primary file always has an ID 
of 1 and the first log file always has an ID of 2.
file_guid
The GUID of the file.
type
An integer representing the file type.
type_desc
A textual description of the file type.
data_space_id
The ID of the filegroup in which the file resides.
name
The logical name of the file.
physical_name
The physical path and name of the file.
state
An integer indicating the current state of the file.
state_desc
A textual description of the current state of the file.
size
The current size of the file, specified as a count of pages.
max_size
The maximum size of the file, specified as a count of pages.
growth
The growth setting of the file. 0 indicates autogrowth is disabled. 
If is_percent_growth is 0, then the value indicates the growth 
increment as a count of pages. If is_percent_growth is 1, then 
the value indicates a whole number percentage increment.
is_media_read_only
Specifies if the media on which the file resides is read-only.
is_read_only
Specifies if the file is in a read-only filegroup.
is_sparse
Specifies that the file belongs to a database snapshot.
is_percent_growth
Indicates if the growth output is a percentage or a fixed rate.
is_name_reserved
Specifies if the filename is reusable.
create_lsn
The LSN (log sequence number) at which the file was created.
drop_lsn
The LSN at which the file was dropped (if applicable).
read_only_lsn
The most recent LSN at which the filegroup was marked read-only.
(continued)
Chapter 17  SQL Server Metadata

--- PDF PAGE 643 ---
635
Using File Stats for Capacity Analysis
When combined together, you can use the three metadata objects described in the 
previous section to produce powerful reports that can help you with capacity planning 
and diagnosing performance issues. For example, the query in Listing 17-9 provides 
the file size, amount of free space remaining, and IO stalls for each file in the database. 
Because sys.dm_io_virtual_file_stats is a function as opposed to a view, we CROSS 
APPLY the function to the result set, passing in the database_id and the file_id of each 
row as parameters.
Listing 17-9.  File Capacity Details
SELECT m.name
        ,m.physical_name
        ,CAST(fsu.total_page_count / 128. AS NUMERIC(12,4)) [Fie Size (MB)]
        ,CAST(fsu.unallocated_extent_page_count / 128. AS NUMERIC(12,4)) 
[Free Space (MB)]
Table 17-6.  (continued)
Column
Description
read_write_lsn
The most recent LSN at which the filegroup was marked read/write.
differential_base_lsn
The LSN at which changes in the file started being marked in the 
DIFF pages.
differential_base_guid The GUID of the full backup on which differential backups for the file 
are made.
differential_base_time The time of the full backup on which differential backups for the file 
are made.
redo_start_lsn
The LSN at which the next roll forward will start.
redo_start_fork_guid
The GUID of the recovery fork.
redo_target_lsn
The LSN at which an online roll forward for the file can stop.
redo_target_fork_guid
The GUID of the recovery fork.
backup_lsn
The most recent LSN at which a full or differential backup was taken.
Chapter 17  SQL Server Metadata

--- PDF PAGE 647 ---
639
                FROM master.sys.master_files
                WHERE db_name(database_id) NOT IN('master','model','msdb')
                GROUP BY LEFT(physical_name, 2)
        )                mf ON DriveSpace.Drive = mf.drive ;
Metadata for Troubleshooting and Performance 
Tuning
You can use many metadata objects to tune performance and troubleshoot issues within 
SQL Server. In the following sections, we explore how to capture performance counters 
from within SQL Server, how to analyze waits, and how to use DMVs to troubleshoot 
issues with expensive queries.
Retrieving Perfmon Counters
Perfmon is a Windows tool that captures performance counters for the operating system, 
plus many SQL Server–specific counters. DBAs who are trying to diagnose performance 
issues find this very useful. The problem is that many DBAs do not have administrative 
access to the underlying operating system, which makes them reliant on Windows 
administrators to assist with the troubleshooting process. A workaround for this issue 
is the sys_dm_os_performance_counters DMV, which exposes the SQL Server Perfmon 
counters within SQL Server. The columns returned by sys.dm_os_performance_
counters are described in Table 17-7.
Table 17-7.  sys.dm_os_performance_counters Columns
Column
Description
object_name
The category of the counter.
counter_name
The name of the counter.
instance_name
The instance of the counter. For example, database-related counters have an 
instance for each database.
cntr_value
The value of the counter.
cntr_type
The type of counter. Counter types are described in Table 17-8.
Chapter 17  SQL Server Metadata

--- PDF PAGE 647 ---
639
                FROM master.sys.master_files
                WHERE db_name(database_id) NOT IN('master','model','msdb')
                GROUP BY LEFT(physical_name, 2)
        )                mf ON DriveSpace.Drive = mf.drive ;
Metadata for Troubleshooting and Performance 
Tuning
You can use many metadata objects to tune performance and troubleshoot issues within 
SQL Server. In the following sections, we explore how to capture performance counters 
from within SQL Server, how to analyze waits, and how to use DMVs to troubleshoot 
issues with expensive queries.
Retrieving Perfmon Counters
Perfmon is a Windows tool that captures performance counters for the operating system, 
plus many SQL Server–specific counters. DBAs who are trying to diagnose performance 
issues find this very useful. The problem is that many DBAs do not have administrative 
access to the underlying operating system, which makes them reliant on Windows 
administrators to assist with the troubleshooting process. A workaround for this issue 
is the sys_dm_os_performance_counters DMV, which exposes the SQL Server Perfmon 
counters within SQL Server. The columns returned by sys.dm_os_performance_
counters are described in Table 17-7.
Table 17-7.  sys.dm_os_performance_counters Columns
Column
Description
object_name
The category of the counter.
counter_name
The name of the counter.
instance_name
The instance of the counter. For example, database-related counters have an 
instance for each database.
cntr_value
The value of the counter.
cntr_type
The type of counter. Counter types are described in Table 17-8.
Chapter 17  SQL Server Metadata

--- PDF PAGE 648 ---
640
The sys.dm_os_performance_counters DMV exposes different types of counters 
that can be identified by the cntr_type column, which relates to the underlying WMI 
performance counter type. You need to handle different counter types in different ways. 
The counter types exposed are described in Table 17-8.
The query in Listing 17-12 demonstrates how to use sys.dm_os_performance_
counters to capture metrics of the PERF_COUNTER_LARGE_RAWCOUNT type, which is the 
simplest form of counter to capture. The query returns the number of memory grants 
that are currently pending.
Listing 17-12.  Using Counter Type 65792
SELECT *
FROM sys.dm_os_performance_counters
WHERE counter_name = 'Memory Grants Pending' ;
The script in Listing 17-13 demonstrates capturing the number of lock requests that 
are occurring per second over the space of one minute. The lock requests/sec counter 
Table 17-8.  Counter Types
Counter Type Description
1073939712 You will use PERF_LARGE_RAW_BASE as a base value in conjunction with the 
PERF_LARGE_RAW_FRACTION type to calculate a counter percentage or with 
PERF_AVERAGE_BULK to calculate an average.
537003264
Use PERF_LARGE_RAW_FRACTION as a fractional value in conjunction with PERF_
LARGE_RAW_BASE to calculate a counter percentage.
1073874176 PERF_AVERAGE_BULK is a cumulative average that you use in conjunction with 
PERF_LARGE_RAW_BASE to calculate a counter average. The counter, along with 
the base, is sampled twice to calculate the metric over a period of time.
272696320
PERF_COUNTER_COUNTER is a 32-bit cumulative rate counter. The value should be 
sampled twice to calculate the metric over a period of time.
272696576
PERF_COUNTER_BULK_COUNT is a 64-bit cumulative rate counter. The value should 
be sampled twice to calculate the metric over a period of time.
65792
PERF_COUNTER_LARGE_RAWCOUNT returns the last sampled result for the counter.
Chapter 17  SQL Server Metadata