18. SQL Server Metadata

Introducing Metadata Objects

Catalog views reside in the sys schema. There are many catalog views; some of the most useful of which, such as sys.master_files, are explored in this chapter. Listing 18-1 shows an example of how to use a catalog view to produce a list of databases that are in FULL recovery model.

SELECT name

FROM sys.databases

WHERE recovery_model_desc = 'FULL' ;

Listing 18-1

Using Catalog Views

Information schema views reside in the INFORMATION_SCHEMA schema. They return less detail than catalog views but are based on the ISO standards. This means that you can port your queries between RDBMS (relational database management systems). Listing 18-2 shows an example of using information schema views to produce a list of principals that have been granted SELECT access to the Chapter10.dbo.SensitiveData table.

Tip

A script for creating the Chapter10 database can be found in Chapter 10.

USE Chapter10

GO

SELECT GRANTEE, PRIVILEGE_TYPE

FROM INFORMATION_SCHEMA.TABLE_PRIVILEGES

WHERE TABLE_SCHEMA = 'dbo'

AND TABLE_NAME = 'SensitiveData'

AND PRIVILEGE_TYPE = 'SELECT' ;

Listing 18-2

Using Information Schema Views

Many dynamic management views and functions are available in SQL Server. Collectively, they are known as DMVs and they provide information about the current state of the instance, which you can use for troubleshooting and tuning performance. The following categories of DMV are exposed in SQL Server 2022:

AlwaysOn Availability Groups
Big Data Clusters
Change data capture
Change tracking
Common language runtime (CLR)
Database mirroring
Databases
Execution
Extended events
FILESTREAM and FileTable
Full-text search and semantic search
Indexes
I/O
Memory-optimized tables
Objects
Query notifications
Replication
Resource Governor
Security
Server
Service broker
Spatial
SQL Server operating system
Stretch Databases
Transactions

We demonstrate and discuss how to use DMVs many times throughout this chapter. DMVs always begin with a dm_ prefix, followed by two to four characters that describe the category of the object—for example, os_ for operating system, db_ for database, and exec_ for execution. This is followed by the name of the object. In Listing 18-3, you can see three things: an example of how to retrieve a full list of dynamic management views and functions, an example of how to use a dynamic management view to find a list of logins that are currently connected to the Chapter16 database, and a dynamic management function you can use to produce details of the pages that store the data relating to the Chapter17.dbo.Customers table.

Tip

A full list of DMVs and DMFs can be exposed by querying sys.all_objects demonstrated in Listing 18-3. I strongly encourage you to explore the full range of dynamic management objects available.

--Retrieve a list of all DMVs and DMFs

SELECT *

FROM sys.all_objects

WHERE name LIKE 'dm%'

ORDER BY name

USE Chapter17 –This database will exist if you followed the examples in Chapter17 of this book

GO

--Find logins connected to the Chapter16 database

SELECT login_name

FROM sys.dm_exec_sessions

WHERE database_id = DB_ID('Chapter17') ;

--Return details of the data pages storing the Chapter17.dbo.Customers table

SELECT *

FROM sys.dm_db_database_page_allocations(DB_ID('Chapter17'),

OBJECT_ID('dbo.Customers'),

NULL,

'DETAILED') ;

Listing 18-3

Using Dynamic Management Views and Functions

SQL Server also offers many metadata-related system functions, such as DB_ID() and OBJET_ID(), which we used in Listing 18-3. Another example of a metadata-related system function is DATALENGTH, which we used in Listing 18-4 to return the length of each value in the LastName column of the Chapter17.dbo.Customers table.

USE Chapter17

GO

SELECT DATALENGTH(LastName)

FROM dbo.Customers ;

Listing 18-4

Using System Functions

Server-Level and Instance-Level Metadata

Many forms of metadata are available for the server and instance. Server-level metadata can be very useful for DBAs who need to find configuration information or troubleshoot an issue when they do not have access to the underlying operating system. For example, the dm_server category of DMVs offers views that allow you to check the status of server 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 18-1.

Table 18-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

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 18-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.

SELECT *

FROM (

SELECT

CASE

WHEN value_name = 'tcpport' 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' )

END PortNumber

FROM sys.dm_server_registry

WHERE registry_key LIKE '%IPAll' ) a

WHERE a.PortNumber IS NOT NULL ;

Listing 18-5

Finding the Port Number

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 18-6 displays the startup parameters configured for the instance.

SELECT *

FROM sys.dm_server_registry

WHERE value_name LIKE 'SQLArg%' ;

Listing 18-6

Finding Startup Parameters

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 18-2 describes the columns returned.

Table 18-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.
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.

The query in Listing 18-7 returns the name of each service, its startup type, its current status, and the name of the service account that runs the service.

SELECT servicename

,startup_type_desc

,status_desc

,service_account

FROM sys.dm_server_services ;

Listing 18-7

Exposing Service Details

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 18-3.

Table 18-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
Allocation_unit_id	The ID of the allocation unit that the page is from
Page_type	The type of page, for example, 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

The script in Listing 18-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.

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

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 ;

Listing 18-8

Determining Buffer Cache Usage per Database

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 18-4.

Table 18-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.
modified_extend_page_count	The total number of modified pages since the last time a full database backup was taken

The sys.dm_io_virtual_file_stats DMV returns I/O 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 I/O stalls. The object accepts database_id and file_id as parameters and returns the columns detailed in Table 18-5.

Table 18-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 I/O 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 I/O latency for read operations against the file, caused by Resource Governor.
io_stall_queued_write_ms	Total I/O latency for write operations against the file, caused by Resource Governor.

Tip

I/O stalls are the amount of time it takes the I/O subsystem to respond to SQL Server.

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 18-6.

Table 18-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.
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.

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 18-9 provides the file size, amount of free space remaining, and I/O 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 results set, passing in the database_id and the file_id of each row as parameters.

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)]

,vfs.io_stall_read_ms

,vfs.io_stall_write_ms

FROM sys.dm_db_file_space_usage fsu

CROSS APPLY sys.dm_io_virtual_file_stats(fsu.database_id, fsu.file_id) vfs

INNER JOIN sys.master_files m

ON fsu.database_id = m.database_id

AND fsu.file_id = m.file_id ;

Listing 18-9

File Capacity Details

The script in Listing 18-10 demonstrates how you can use sys.master_files to analyze drive capacity for each volume by detailing the current size of each file, the amount each file will grow by the next time it grows, and the current free capacity of the drive. You can obtain the free space on the drive by using the xp_fixeddrives stored procedure.

DECLARE @fixeddrives TABLE

(

Drive CHAR(1),

MBFree BIGINT

) ;

INSERT INTO @fixeddrives

EXEC xp_fixeddrives ;

SELECT

Drive

,SUM([File Space Used (MB)]) TotalSpaceUsed

, SUM([Next Growth Amount (MB)]) TotalNextGrowth

, SpaceLeftOnVolume

FROM (

SELECT Drive

,size * 1.0 / 128 [File Space Used (MB)]

,CASE

WHEN is_percent_growth = 0

THEN growth * 1.0 / 128

WHEN is_percent_growth = 1

THEN (size * 1.0 / 128 * growth / 100)

END [Next Growth Amount (MB)]

,f.MBFree SpaceLeftOnVolume

FROM sys.master_files m

INNER JOIN @fixeddrives f

ON LEFT(m.physical_name, 1) = f.Drive ) a

GROUP BY Drive, SpaceLeftOnVolume

ORDER BY drive ;

Listing 18-10

Analyzing Drive Space with xp_fixeddrives

The issue with xp_fixeddrives is that it cannot see mapped drives. Therefore, as an alternative, you can employ the script in Listing 18-11, which uses PowerShell to return the information.

Caution

The drawback of this approach is that it requires xp_cmdshell to be enabled, which is against security best practice.

USE [master];

DECLARE @t TABLE

(

name varchar(150),

minimum tinyint,

maximum tinyint ,

config_value tinyint ,

run_value tinyint

)

DECLARE @psinfo TABLE(data NVARCHAR(100)) ;

INSERT INTO @psinfo

EXEC xp_cmdshell 'Powershell.exe "Get-WMIObject Win32_LogicalDisk -filter "DriveType=3"| Format-Table DeviceID, FreeSpace, Size"' ;

DELETE FROM @psinfo WHERE data IS NULL OR data LIKE '%DeviceID%' OR data LIKE '%----%';

UPDATE @psinfo SET data = REPLACE(data,' ',',');

;WITH DriveSpace AS

(

SELECT LEFT(data,2) as [Drive],

REPLACE((LEFT((SUBSTRING(data,(PATINDEX('%[0-9]%',data))

, LEN(data))),CHARINDEX(',',

(SUBSTRING(data,(PATINDEX('%[0-9]%',data))

, LEN(data))))-1)),',','') AS FreeSpace

,

REPLACE(RIGHT((SUBSTRING(data,(PATINDEX('%[0-9]%',data))

, LEN(data))),PATINDEX('%,%',

(SUBSTRING(data,(PATINDEX('%[0-9]%',data)) , LEN(data))))) ,',','')

AS [Size]

FROM @psinfo

)

SELECT

mf.Drive

,CAST(sizeMB as numeric(18,2)) as [File Space Used (MB)]

,CAST(growth as numeric(18,2)) as [Next Growth Amount (MB)]

,CAST((CAST(FreeSpace as numeric(18,2))

/(POWER(1024., 3))) as numeric(6,2)) AS FreeSpaceGB

,CAST((CAST(size as numeric(18,2))/(POWER(1024., 3))) as numeric(6,2)) AS TotalSizeGB

,CAST(CAST((CAST(FreeSpace as numeric(18,2))/(POWER(1024., 3))) as numeric(6,2))

/ CAST((CAST(size as numeric(18,2))/(POWER(1024., 3))) as numeric(6,2))

* 100 AS numeric(5,2)) [Percent Remaining]

FROM DriveSpace

JOIN

( SELECT DISTINCT LEFT(physical_name, 2) Drive, SUM(size / 128.0) sizeMB

,SUM(CASE

WHEN is_percent_growth = 0

THEN growth / 128.

WHEN is_percent_growth = 1

THEN (size / 128. * growth / 100)

END) growth

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 ;

Listing 18-11

Analyzing Drive Space with PowerShell

A third way of working with drive information is the sys.dm_os_enumerate_fixed_drives DMV. This DMV returns the path of the drive, for example, C: as well as the free space on the drive, expressed in bytes. Additionally, columns are returned for a drive type ID and description.

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 18-7.

Table 18-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 18-8.

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 18-8.

Table 18-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.

The query in Listing 18-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.

SELECT *

FROM sys.dm_os_performance_counters

WHERE counter_name = 'Memory Grants Pending' ;

Listing 18-12

Using Counter Type 65792

The script in Listing 18-13 demonstrates capturing the number of lock requests that are occurring per second over the space of one minute. The lock requests/sec counter uses the PERF_COUNTER_BULK_COUNT counter type, but the same method applies to capturing counters relating to In-Memory OLTP, which uses the PERF_COUNTER_COUNTER counter type.

DECLARE @cntr_value1 BIGINT = (

SELECT cntr_value

FROM sys.dm_os_performance_counters

WHERE counter_name = 'Lock Requests/sec'

AND instance_name = '_Total') ;

WAITFOR DELAY '00:01:00'

DECLARE @cntr_value2 BIGINT = (

SELECT cntr_value

FROM sys.dm_os_performance_counters

WHERE counter_name = 'Lock Requests/sec'

AND instance_name = '_Total') ;

SELECT (@cntr_value2 - @cntr_value1) / 60 'Lock Requests/sec' ;

Listing 18-13

Using Counter Types 272696576 and 272696320

The script in Listing 18-14 demonstrates capturing the plan cache hit ratio for the instance. The Plan Cache Hit Ratio counter is counter type 537003264. Therefore, we need to multiply the value by 100 and then divide by the base counter to calculate the percentage.

Tip

Before running the script, you should change the instance name to match your own.

SELECT

100 *

(

SELECT cntr_value

FROM sys.dm_os_performance_counters

WHERE object_name = 'MSSQL$PROSQLADMIN:Plan Cache'

AND counter_name = 'Cache hit ratio'

AND instance_name = '_Total')

/

(

SELECT cntr_value

FROM sys.dm_os_performance_counters

WHERE object_name = 'MSSQL$PROSQLADMIN:Plan Cache'

AND counter_name = 'Cache hit ratio base'

AND instance_name = '_Total') [Plan cache hit ratio %] ;

Listing 18-14

Using Counter Type 537003264

The script in Listing 18-15 demonstrates how to capture the Average Latch Wait Time (ms) counter. Because this counter is of type PERF_AVERAGE_BULK, we need to capture the value and its corresponding base counter twice. We then need to deduct the first capture of the counter from the second capture, deduct the first capture of the base counter from the second capture, and then divide the fractional counter value by its base value to calculate the average over the time period. Because it is possible that no latches will be requested within the time period, we have wrapped the SELECT statement in an IF/ELSE block to avoid the possibility of a divide-by-0 error being thrown.

DECLARE @cntr TABLE

(

ID INT IDENTITY,

counter_name NVARCHAR(256),

counter_value BIGINT,

[Time] DATETIME

) ;

INSERT INTO @cntr

SELECT

counter_name

,cntr_value

,GETDATE()

FROM sys.dm_os_performance_counters

WHERE counter_name IN('Average Latch Wait Time (ms)',

'Average Latch Wait Time base') ;

--Adds an artificial delay

WAITFOR DELAY '00:01:00' ;

INSERT INTO @cntr

SELECT

counter_name

,cntr_value

,GETDATE()

FROM sys.dm_os_performance_counters

WHERE counter_name IN('Average Latch Wait Time (ms)',

'Average Latch Wait Time base') ;

IF (SELECT COUNT(DISTINCT counter_value)

FROM @cntr

WHERE counter_name = 'Average Latch Wait Time (ms)') > 2

BEGIN

SELECT

(

SELECT TOP 1 counter_value

FROM @cntr

WHERE counter_name = 'Average Latch Wait Time (ms)'

ORDER BY [Time] DESC

)

-

(

SELECT TOP 1 counter_value

FROM @cntr

WHERE counter_name = 'Average Latch Wait Time (ms)'

ORDER BY [Time] ASC

)

/

(

SELECT TOP 1 counter_value

FROM @cntr

WHERE counter_name = 'Average Latch Wait Time base'

ORDER BY [Time] DESC

)

-

(

SELECT TOP 1 counter_value

FROM @cntr

WHERE counter_name = 'Average Latch Wait Time base'

ORDER BY [Time] ASC

)

) [Average Latch Wait Time (ms)] ;

END

ELSE

BEGIN

SELECT 0 [Average Latch Wait Time (ms)] ;

END

Listing 18-15

Using Counter Type 1073874176

Analyzing Waits

Waits are a natural aspect of any RDBMS, but they can also indicate a performance bottleneck. Analyzing these waits, with a view to reducing them, often improve performance. A full explanation of all wait types can be found at msdn.microsoft.com, but all wait types break down into three categories: resource waits, queue waits, and external waits.

Note

A query in SQL Server is either running, waiting for its turn on the processor (runnable), or waiting for another resource (suspended). If it is waiting for another resource, SQL Server records the reason why it is waiting and the duration of this wait.

Resource waits occur when a thread requires access to an object, but that object is already in use, and therefore, the thread has to wait. This can include the thread waiting to take a lock out on an object or waiting for a disk resource to respond. Queue waits occur when a thread is idle and is waiting for a task to be assigned. This does not necessarily indicate a performance bottleneck, since it is often a background task, such as the Deadlock Monitor or Lazy Writer waiting until it is needed. External waits occur when a thread is waiting for an external resource, such as a linked server. The hidden gotcha here is that an external wait does not always mean that the thread is actually waiting. It could be performing an operation external to SQL Server, such as an extended stored procedure running external code.

Any task that has been issued is in one of three states: running, runnable, or suspended. If a task is in the running state, then it is actually being executed on a processor. When a task is in the runnable state, it sits on the processor queue, awaiting its turn to run. This is known as a signal wait. When a task is suspended, it means that the task is waiting for any reason other than a signal wait. In other words, it is experiencing a resource wait, a queue wait, or an external wait. Each query is likely to alternate between the three states as it progresses.

The sys.dm_os_wait_stats returns details of the cumulative waits for each wait type, since the instance started, or since the statistics exposed by the DMV were reset. This is important, as it gives a holistic view, as to the source of bottlenecks. You can reset the statistics by running the command in Listing 18-16.

DBCC SQLPERF ('sys.dm_os_wait_stats', CLEAR) ;

Listing 18-16

Resetting Wait Stats

The columns returned by sys.dm_os_wait_stats are detailed in Table 18-9.

Table 18-9

sys.dm_os_wait_stats Columns

Column	Description
wait_type	The name of the wait type that has occurred.
waiting_tasks_count	The number of tasks that have occurred on this wait type.
wait_time_ms	The cumulative time of all waits against this wait type, displayed in milliseconds. This includes signal wait times.
max_wait_time_ms	The maximum duration of a single wait against this wait type.
signal_wait_time_ms	The cumulative time for all signal waits against this wait type.

To find the wait types that are responsible for the highest cumulative wait time, run the query in Listing 18-17. This query adds a calculated column to the result set, which deducts the signal wait time from the overall wait time to avoid CPU pressure from skewing the results.

SELECT *

, wait_time_ms - signal_wait_time_ms ResourceWaits

FROM sys.dm_os_wait_stats

ORDER BY wait_time_ms - signal_wait_time_ms DESC ;

Listing 18-17

Finding the Highest Waits

Of course, signal wait time can be a cause for concern in its own right, potentially identifying the processor as a bottleneck, and you should analyze it. Therefore, use the query in Listing 18-18 to calculate the percentage of overall waits, which are due to a task waiting for its turn on the processor. The value is displayed for each wait type and it is followed by a row that displays the overall percentage for all wait types.

SELECT ISNULL(wait_type, 'Overall Percentage:') wait_type

,PercentageSignalWait

FROM (

SELECT wait_type

,CAST(100. * SUM(signal_wait_time_ms)

/ SUM(wait_time_ms) AS NUMERIC(20,2)) PercentageSignalWait

FROM sys.dm_os_wait_stats

WHERE wait_time_ms > 0

GROUP BY wait_type WITH ROLLUP

) a

ORDER BY PercentageSignalWait DESC ;

Listing 18-18

Calculating Signal Waits

To find the highest waits over a defined period, you need to sample the data twice and then deduct the first sample from the second sample. The script in Listing 18-19 samples the data twice with a ten-minute interval and then displays the details of the five highest waits within that interval.

DECLARE @Waits1 TABLE

(

wait_type NVARCHAR(128),

wait_time_ms BIGINT

) ;

DECLARE @Waits2 TABLE

(

wait_type NVARCHAR(128),

wait_time_ms BIGINT

) ;

INSERT INTO @waits1

SELECT wait_type

,wait_time_ms

FROM sys.dm_os_wait_stats ;

WAITFOR DELAY '00:10:00' ;

INSERT INTO @Waits2

SELECT wait_type

,wait_time_ms

FROM sys.dm_os_wait_stats ;

SELECT TOP 5

w2.wait_type

,w2.wait_time_ms - w1.wait_time_ms

FROM @Waits1 w1

INNER JOIN @Waits2 w2

ON w1.wait_type = w2.wait_type

ORDER BY w2.wait_time_ms - w1.wait_time_ms DESC ;

Listing 18-19

Calculating the Highest Waits over a Defined Period

Database Metadata

In previous versions of SQL Server, if DBAs needed to discover information about specific pages within a database, they had no choice but to use the well-known, but undocumented DBCC command, DBCC PAGE. SQL Server addresses this issue, by adding a new dynamic management view, called sys.dm_db_page_info. It is fully documented and supported by Microsoft, and provides the ability to return a page header, in a table-valued format. The function accepts the parameters detailed in Table 18-10.

Table 18-10

Parameters Accepted by sys.dm_db_page_info

Parameter	Description
Database_id	The database_id of the database that you wish to return details for.
File_id	The file_id of the file that you wish to return details for.
Page_id	The page_id of the page that you are interested in.
Mode	Mode can be set to either LIMITED or DETAILED. The only difference between the modes is that when LIMITED is used, the description columns are not populated. This can improve performance against large tables.

In order to populate these parameters, an additional system function has been added, called sys.fn_PageResCracker. This function can be cross applied to a table, passing %%physloc%% as a parameter. Alternatively, if cross applied to the sys.dm_exec_requests DMV, or sys.sysprocesses, a deprecated system view, an additional column has been added, called page_resource, which can be passed as a parameter to the function. This is helpful if you are diagnosing an issue with page waits. When passed a page resource/physical location object, the function will return the database_id, file_id, and page_id of each row in a result set.

Caution

When used with %%physloc%% as opposed to a page_resource object, the sys.fn_PageResCracker function returns a slot_id, as opposed to a database_id. Therefore, when used with %%physloc%%, the DB_ID() function should be used to obtain the database_id and the database_id column returned by the function should be discarded.

Table 18-11 details the columns that are returned by the sys.dm_db_page_info DMF.

Table 18-11

Columns Returned by sys.db_db_page_info

Column	Description
Database_id	The ID of the database
File_id	The ID of the file
Page_id	The ID of the page
page_type	The internal ID associated with the page type description
Page_type_desc page_flag_bits page_flag_bits_desc	The type of page. For example, data page, index page, IAM page, PFS page, etc.
page_type_flag_bits	Hexadecimal value representing the page flags
page_type_flag_bits_desc	A description of the page flags
object_id	The ID of the object that the page is a part of
index_id	The ID of the index that the page is part of
partition_id	The partition ID of the partition that the page is part of
alloc_unit_id	The ID of the allocation unit where the page is stored
page_level	The level of the page within a B-Tree structure
slot_count	The number of slots within the page
ghost_rec_count	The number of records of the page that have been marked for deletion, but have not yet been physically removed
torn_bits	Used to detect data corruption, by storing 1 bit for every torn write detected
is_iam_pg	Indicates if the page is an IAM page
is_mixed_ext	Indicates if the page is part of a mixed extent (an extent allocated to multiple objects)
pfs_file_id	The file ID of the file where the page’s associated PFS (Page Free Space) page is stored
pfs_page_id	The page ID of the PFS page that is associated with the page
pfs_alloc_percent	The amount of free space on the page
pfs_status	The value of the page’s PFS byte
pfs_status_desc	A description of the page’s PFS byte
gam_file_id	The file ID of the file where the page’s associated GAM (global allocation map) page is stored
gam_page_id	The page ID of the GAM page, which is associated with the page
gam_status	Indicates if the page is allocated in GAM
gam_status_desc	Describes the GAM status marker
sgam_file_id	The file ID of the file where the page’s associated SGAM (shared global allocation map) page is stored
sgam_page_id	The page ID of the SGAM page, which is associated with the page
sgam_status	Indicates if the page is allocated in SGAM
sgam_status_desc	Describes the SGAM status marker
diff_map_file_id	The file ID of the file containing the page’s associated differential bitmap page
diff_map_page_id	The page ID of the differential bitmap page associated with the page
diff_status	Indicates if the page has changed since the last differential backup
diff_status_desc	Describes the differential status marker
ml_file_id	The file ID of the file that stores the page’s associated minimally logged bitmap page
ml_page_id	The page ID of the minimally logged bitmap page, associated with the page
ml_status	Indicates if the page is minimally logged
ml_status_desc	Describes the minimally logged status marker
free_bytes	The amount of free space on the page (in bytes)
free_data_offset	The page offset, to the start of the free space on the page
reserved_bytes	If the page is a leaf-level index page, indicates the amount of rows awaiting ghost cleanup. If the page is on a heap, then indicates the number of free bytes reserved by all transactions
reserved_xdes_id	Used for MSFT support for debugging
xdes_id	Used for MSFT support for debugging
prev_page_file_id	The file ID of the previous page in the IAM chain
prev_page_page_id	The page ID of the previous page in the IAM chain
next_page_file_id	The file ID of the next page in the IAM chain
next_page_page_id	The page ID of the next page in the IAM chain
min_len	The length of fixed width rows
Page_lsn	The last LSN (log sequence number) to modify the page
header_version	The version of the page header

The potential occasions where this data may prove invaluable are almost limitless. The script in Listing 18-20 demonstrates how this data could be used to determine the maximum log sequence number in a critical table, in preparation for a restore activity. The DBA can then use the maximum LSN to ensure that a point-in-time restore captures the latest modifications to the critical data.

CREATE DATABASE Chapter18

GO

ALTER DATABASE Chapter18

SET RECOVERY FULL

GO

USE Chapter18

GO

CREATE TABLE dbo.CriticalData (

ID INT IDENTITY PRIMARY KEY NOT NULL,

ImportantData NVARCHAR(128) NOT NULL

)

INSERT INTO dbo.CriticalData(ImportantData)

VALUES('My Very Important Value')

GO

SELECT MAX(page_info.page_lsn)

FROM dbo.CriticalData c

CROSS APPLY sys.fn_PageResCracker(%%physloc%%) AS r

CROSS APPLY sys.dm_db_page_info(DB_ID(), r.file_id, r.page_id, 'DETAILED') AS page_info

Listing 18-20

Find the Most Recent LSN to Modify a Table

Metadata-Driven Automation

You can use metadata to drive intelligent scripts that you can use to automate routine DBA maintenance tasks while at the same time incorporating business logic. In the following sections, you see how you can use metadata to generate rolling database snapshots and also to rebuild only those indexes that are fragmented.

Dynamically Cycling Database Snapshots

As discussed in Chapter 16, we can use database snapshots to create a read-only copy of the database that can reduce contention for read-only reporting. The issue is that the data becomes stale, as data in the source database is modified. For this reason, a useful tool for managing snapshots is a stored procedure, which dynamically creates a new snapshot and drops the oldest existing snapshot. You can then schedule this procedure to run periodically, using SQL Server Agent. (SQL Server Agent is discussed in Chapter 21.) The script in Listing 18-21 creates a stored procedure that, when passed the name of the source database, drops the oldest snapshot and creates a new one.

The procedure accepts two parameters. The first specifies the name of the database that should be used to generate the snapshot. The second parameter specifies how many snapshots you should have at any one time. For example, if you pass in a value of Chapter18 to the @DBName parameter and a value of 2 to the @RequiredSnapshots parameter, the procedure creates a snapshot against the Chapter18 database but only removes the oldest snapshot if at least two snapshots already exist against the Chapter18 database.

The procedure builds up the CREATE DATABASE script in three parts (see Listing 18-21). The first part contains the initial CREATE DATABASE statement. The second part creates the file list, based on the files that are recorded as being part of the database in sys.master_files. The third part contains the AS SNAPSHOT OF statement. The three strings are then concatenated together before being executed. The script appends a sequence number to the name of the snapshot, and the name of each file within the snapshot, to ensure uniqueness.

CREATE PROCEDURE dbo.DynamicSnapshot @DBName NVARCHAR(128), @RequiredSnapshots INT

AS

BEGIN

DECLARE @SQL NVARCHAR(MAX)

DECLARE @SQLStart NVARCHAR(MAX)

DECLARE @SQLEnd NVARCHAR(MAX)

DECLARE @SQLFileList NVARCHAR(MAX)

DECLARE @DBID INT

DECLARE @SS_Seq_No INT

DECLARE @SQLDrop NVARCHAR(MAX)

SET @DBID = (SELECT DB_ID(@DBName)) ;

--Generate sequence number

IF (SELECT COUNT(*) FROM sys.databases WHERE source_database_id = @DBID) > 0

SET @SS_Seq_No = (SELECT TOP 1 CAST(SUBSTRING(name, LEN(Name), 1) AS INT)

FROM sys.databases

WHERE source_database_id = @DBID

ORDER BY create_date DESC) + 1

ELSE

SET @SS_Seq_No = 1

--Generate the first part of the CREATE DATABASE statement

SET @SQLStart = 'CREATE DATABASE '

+ QUOTENAME(@DBName + CAST(CAST(GETDATE() AS DATE) AS NCHAR(10))

+ '_ss' + CAST(@SS_Seq_No AS NVARCHAR(4))) + ' ON ' ;

--Generate the file list for the CREATE DATABASE statement

SELECT @SQLFileList =

(

SELECT

'(NAME = N''' + mf.name + ''', FILENAME = N'''

+ SUBSTRING(mf.physical_name, 1, LEN(mf.physical_name) - 4)

+ CAST(@SS_Seq_No AS NVARCHAR(4)) + '.ss' + '''),' AS [data()]

FROM sys.master_files mf

WHERE mf.database_id = @DBID

AND mf.type = 0

FOR XML PATH ('')

) ;

--Remove the extra comma from the end of the file list

SET @SQLFileList = SUBSTRING(@SQLFileList, 1, LEN(@SQLFileList) - 2) ;

--Generate the final part of the CREATE DATABASE statement

SET @SQLEnd = ') AS SNAPSHOT OF ' + @DBName ;

--Concatenate the strings and run the completed statement

SET @SQL = @SQLStart + @SQLFileList + @SQLEnd ;

EXEC(@SQL) ;

--Check to see if the required number of snapshots exists for the database,

--and if so, delete the oldest

IF (SELECT COUNT(*)

FROM sys.databases

WHERE source_database_id = @DBID) > @RequiredSnapshots

BEGIN

SET @SQLDrop = 'DROP DATABASE ' + (

SELECT TOP 1

QUOTENAME(name)

FROM sys.databases

WHERE source_database_id = @DBID

ORDER BY create_date ASC )

EXEC(@SQLDrop)

END ;

END

Listing 18-21

Dynamically Cycling Database Snapshots

The command in Listing 18-22 runs the DynamicSnapshot procedure against the Chapter18 database specifying that two snapshots should exist at any one time.

EXEC dbo.DynamicSnapshot 'Chapter18', 2 ;

Listing 18-22

Running the DynamicSnapshot Procedure

Rebuilding Only Fragmented Indexes

When you rebuild all indexes with a maintenance plan, which we discuss in Chapter 21, SQL Server supplies no intelligent logic out of the box. Therefore, all indexes are rebuilt, regardless of their fragmentation level, which requires unnecessary time and resource utilization. A workaround for this issue is to write a custom script that rebuilds indexes only if they are fragmented.

The script in Listing 18-23 demonstrates how you can use SQLCMD to identify indexes that have more than 25% fragmentation and then rebuild them dynamically. The reason that the code is in a SQLCMD script, as opposed to a stored procedure, is because sys.dm_db_index_physical_stats must be called from within the database that you wish to run it against. Therefore, when you run it via SQLCMD, you can use a scripting variable to specify the database you require; doing so makes the script reusable for all databases. When you run the script from the command line, you can simply pass in the name of the database as a variable.

USE $(DBName)

GO

DECLARE @SQL NVARCHAR(MAX)

SET @SQL =

(

SELECT 'ALTER INDEX '

+ i.name

+ ' ON ' + s.name

+ '.'

+ OBJECT_NAME(i.object_id)

+ ' REBUILD ; '

FROM sys.dm_db_index_physical_stats(DB_ID('$(DBName)'),NULL,NULL,NULL,'DETAILED') ps

INNER JOIN sys.indexes i

ON ps.object_id = i.object_id

AND ps.index_id = i.index_id

INNER JOIN sys.objects o

ON ps.object_id = o.object_id

INNER JOIN sys.schemas s

ON o.schema_id = s.schema_id

WHERE index_level = 0

AND avg_fragmentation_in_percent > 25

FOR XML PATH('')

) ;

EXEC(@SQL) ;

Listing 18-23

Rebuilding Only Required Indexes

When this script is saved as in the root of C: as RebuildIndexes.sql, it can be run from the command line. The command in Listing 18-24 demonstrates running it against the Chapter18 database.

Sqlcmd -v DBName="Chapter18" -I c:RebuildIndexes.sql -S ./PROSQLADMIN

Listing 18-24

Running RebuildIndexes.sql

Summary

SQL Server exposes a vast array of metadata, which describes the data structures within SQL Server as well as the data itself. Metadata is exposed through a series of catalog views, dynamic management views and functions, system functions, and the INFORMATION_SCHEMA. Normally you only use the INFORMATION_SCHEMA if you need your scripts to be transferable to other RDBMS products. This is because it provides less detail than SQL Server–specific metadata but conforms to ISO standards, and therefore, it works on all major RDBMS.

This chapter also covered much useful information about the underlying operating system, as well as SQLOS. For example, you can use the dm_server category of DMV to find details of the instance’s Registry keys and expose details of the instance’s services. You can use the dm_os category of DMV to expose many internal details regarding the SQLOS, including the current contents of the buffer cache.

SQL Server also exposes metadata that you can use for capacity planning, such as the usage statistics for all files within a database (e.g., I/O stalls) and the amount of free space remaining. You can use this information to proactively plan additional capacity requirements before alerts start being triggered and applications are put at risk.

Metadata can also help in the pursuit of troubleshooting and performance tuning. The sys.dm_os_performance_counters DMV allows DBAs to retrieve Perfmon counters, even if they do not have access to the operating system. This can remove inter-team dependencies. You can use sys.dm_os_wait_stats to identify the most common cause of waits within the instance, which can in turn help diagnose hardware bottlenecks, such as memory or CPU pressure. The dm_exec category of DMV can help identify expensive queries, which may be tuned, to improve performance.

DBAs can also use metadata to create intelligent scripts, which can reduce their workload by adding business rules to common maintenance tasks. For example, a DBA can use metadata for tasks such as dynamically rebuilding only indexes that have become fragmented, or dynamically managing the cycling of database snapshots. I encourage you to explore the possibilities of metadata-driven automation further; the possibilities are endless.

Table of Contents for 18. SQL Server Metadata

Create new playlist

Sign In

Sign Up

18. SQL Server Metadata

Introducing Metadata Objects

Server-Level and Instance-Level Metadata

Exposing Registry Values

Exposing Service Details

Analyzing Buffer Cache Usage

Metadata for Capacity Planning

Exposing File Stats

Using File Stats for Capacity Analysis

Metadata for Troubleshooting and Performance Tuning

Retrieving Perfmon Counters

Analyzing Waits

Database Metadata

Metadata-Driven Automation

Dynamically Cycling Database Snapshots

Rebuilding Only Fragmented Indexes

Summary

Table of Contents for
18. SQL Server Metadata