Metadata is data that describes other data. SQL Server exposes a vast array of metadata including structural metadata, which describes every object, and descriptive metadata, which described the data itself. Metadata is exposed through a series of catalog views; information schema views; dynamic management views; and functions, system functions, and stored procedures.

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 17-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’ ;

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 17-2 shows an example of using information schema views to produce a list of principals that have been granted SELECT access to the Chapter9.dbo.SensitiveData table.

USE Chapter9
GO

SELECT GRANTEE, PRIVILEGE_TYPE
FROM INFORMATION_SCHEMA.TABLE_PRIVILEGES
WHERE TABLE_SCHEMA = ’dbo’
        AND TABLE_NAME = ’SensitiveData’
        AND PRIVILEGE_TYPE = ’SELECT’ ;

Many dynamic management views and functions 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 2014:

• AlwaysOn Availability Groups
• 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
• Service broker
• SQL Server operating system
• 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 17-3, you can see two things: 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 Chapter16.dbo.Customers table.

USE Chapter16
GO

--Find logins connected to the Chapter16 database

SELECT login_name
FROM sys.dm_exec_sessions
WHERE database_id = DB_ID(’Chapter16’) ;

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

SELECT *
FROM sys.dm_db_database_page_allocations(DB_ID(’Chapter16’),
                                         OBJECT_ID(’dbo.Customers’),
                                         NULL,
                                         NULL,
                                         ’DETAILED’) ;

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

USE Chapter16
GO

SELECT DATALENGTH(LastName)
FROM dbo.Customers ;

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

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’ )
        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.

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.

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.

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.

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.

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 ;

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. Before SQL Server 2012, it could only be used against TempDB, but it is now supported against any database. The columns returned by this object are detailed in Table 17-4.

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.

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.

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 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 ;

The script in Listing 17-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 ;

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

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 ;

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.

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.

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 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’ ;

The script in Listing 17-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. 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 %] ;

The script in Listing 17-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’) ;
        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

Analyzing Waits

Waits are a natural aspect of any RDBMS, but they can also indicate a performance bottleneck. 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.

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. You can reset the statistics by running the command in Listing 17-16.

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

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

To find the wait types that are responsible for the highest cumulative wait time, run the query in Listing 17-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 ;

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 17-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, ’Overal 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 ;

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 17-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 ;

Finding and Tuning Expensive Queries

Although poor performance can be the sign of a hardware bottleneck, more often than not, you can achieve larger performance gains from tuning queries than from throwing hardware at the problem. If developers have written queries using cursors or other suboptimal techniques, then you may need to pass the queries back to the development or technical support team; but in other cases, the DBA may be able to resolve the issue—by adding a missing index, for example.

The first step in the query troubleshooting process is to find the most expensive queries, which are frequently being run. You can achieve this by using the sys.dm_exec_query_stats DMV in conjunction with the sys.dm_exec_sql_text and sys.dm_exec_query_plan DMVs. The columns returned by the sys.dm_exec_query_plan dynamic management view are detailed in Table 17-10.

The sys.dm_exec_sql_text dynamic management function accepts a single parameter, which is the sql_handle. This means that the function can be cross-applied to the sys.dm_exec_query_stats results. The function returns the text of the batch or procedure, of which the query is a part. The sys.dm_exec_query_plan accepts a single parameter, which is the plan_handle. This means that it can also be cross-applied to the results of sys.dm_exec_query_stats. It returns the XML representation of the query plan.

Before we demonstrate how to use sys.dm_exec_query_stats, we first create the Chapter17 database, which includes two tables that we populate with data. These tasks are performed in Listing 17-20.

--Create the database

CREATE DATABASE Chapter17;
GO

ALTER DATABASE Chapter17 SET RECOVERY FULL;
GO

USE Chapter17
GO

--Create and populate numbers table

DECLARE @Numbers TABLE
(
        Number        INT
)

;WITH CTE(Number)
AS
(
        SELECT 1 Number
        UNION ALL
        SELECT Number + 1
        FROM CTE
        WHERE Number < 100
)
INSERT INTO @Numbers
SELECT Number FROM CTE;

--Create and populate name pieces

DECLARE @Names TABLE
(
        FirstName        VARCHAR(30),
        LastName        VARCHAR(30)
);

INSERT INTO @Names
VALUES(’Peter’, ’Carter’),
                 (’Michael’, ’Smith’),
                 (’Danielle’, ’Mead’),
                 (’Reuben’, ’Roberts’),
                 (’Iris’, ’Jones’),
                 (’Sylvia’, ’Davies’),
                 (’Finola’, ’Wright’),
                 (’Edward’, ’James’),
                 (’Marie’, ’Andrews’),
                 (’Jennifer’, ’Abraham’);

--Create and populate Customers table

CREATE TABLE dbo.Customers
(
        CustomerID        INT            NOT NULL    IDENTITY    PRIMARY KEY,
        FirstName         VARCHAR(30)    NOT NULL,
        LastName          VARCHAR(30)    NOT NULL,
        BillingAddressID  INT            NOT NULL,
        DeliveryAddressID INT            NOT NULL,
        CreditLimit           MONEY          NOT NULL,
        Balance               MONEY          NOT NULL
);

SELECT * INTO #Customers
FROM
         (SELECT
                 (SELECT TOP 1 FirstName FROM @Names ORDER BY NEWID()) FirstName,
                 (SELECT TOP 1 LastName FROM @Names ORDER BY NEWID()) LastName,
                 (SELECT TOP 1 Number FROM @Numbers ORDER BY NEWID()) BillingAddressID,
                 (SELECT TOP 1 Number FROM @Numbers ORDER BY NEWID()) DeliveryAddressID,
                 (SELECT TOP 1 CAST(RAND() * Number AS INT) * 10000
                           FROM @Numbers ORDER BY NEWID()) CreditLimit,
                 (SELECT TOP 1 CAST(RAND() * Number AS INT) * 9000
                           FROM @Numbers ORDER BY NEWID()) Balance
        FROM @Numbers a
        CROSS JOIN @Numbers b
) a;

INSERT INTO dbo.Customers
SELECT * FROM #Customers;

In Listing 17-21, we tear down the plan cache and run some queries against the Customers table in our Chapter17 database before we use the sys.dm_exec_query_stats object to view details of our longest running queries. Because we have reset the plan cache, we know that our queries against the Customers table will be returned. In a production scenario, you may choose to filter the results by execution count, since you may not be interested in ad-hoc queries that have only run once or twice. You may also choose to filter by last execution time to avoid returning information for old queries that are no longer run.

DBCC FREEPROCCACHE
GO

SELECT *
FROM dbo.Customers ;

SELECT *
FROM dbo.Customers
WHERE CreditLimit > 100000 ;
GO

SELECT SUM(Balance) TotalExposure
FROM dbo.Customers ;
GO

SELECT Duplicates.CustomerID
        ,Customers.FirstName
        ,Customers.LastName
        ,Duplicates.DuplicateMarker
FROM
(
        SELECT CustomerID
               ,ROW_NUMBER() OVER(PARTITION BY FirstName, LastName ORDER BY FirstName, LastName) DuplicateMarker
        FROM dbo.Customers
) Duplicates
INNER JOIN dbo.Customers Customers
        ON Duplicates.CustomerID = Customers.CustomerID
WHERE Duplicates.DuplicateMarker > 1 ;
GO

SELECT TOP 5
        execution_count
        ,total_elapsed_time
        ,total_worker_time
        ,total_physical_reads
        ,total_logical_reads
        ,total_logical_writes
        ,qp.dbid
        ,SUBSTRING(ST.text, (QS.statement_start_offset/2) + 1,
    ((CASE statement_end_offset
        WHEN -1 THEN DATALENGTH(st.text)
        ELSE QS.statement_end_offset END
            - QS.statement_start_offset)/2) + 1) Query
        ,qp.query_plan
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) st
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) qp
ORDER BY total_elapsed_time DESC ;

The results of the query against sys.dm_exec_query_stats are displayed in Figure 17-1. You can see that, unsurprisingly, the query to identify duplicates was the longest running query, as well as the most expensive in terms of CPU time and IO.

The right-most column in the results displays the XML execution plan. Clicking this execution plan in SQL Server Management Studio displays the graphical representation of the execution plan. The execution plan for the most expensive query is shown in Figure 17-2.

The query optimizer is very clever, and the vast majority of the time, it chooses the best plan for a query, unless statistics are out of date or indexes are fragmented. Occasionally, however, when troubleshooting a performance issue with a query, you may notice that the optimizer has chosen a suboptimal plan. In the case of our query to identify duplicate names, the optimizer chose a Merge join, which requires the data to be sorted first. The Nested Loop algorithm is likely to perform better for this query, because although using it involves increasing the reads because the data pages of the table are warm, the elapsed time is likely to still be less. We can resolve this issue by asking the development team to add a query hint, but this is not always possible either because of third-party code or delays caused by change control. Therefore, you can resolve the issue by using plan freezing.

Plan Freezing

Plan freezing involves creating an object called a plan guide, which influences the optimizer by attaching query hints to a specific query or programmable object. When the optimizer is passed a query, it checks to see if there is a plan guide that matches the query pattern stored in the sys.plan_guides table. If there is, it modifies the query to incorporate the hints before compiling it.

You can create a plan guide by using the sp_create_plan_guide stored procedure, which accepts the parameters detailed in Table 17-11.

The command in Listing 17-22 creates a plan guide that forces our duplicate identifying query to use a Loop join, as opposed to a Merge join.

USE Chapter17
GO

EXEC sp_create_plan_guide
        @name = N’[PlanGuide-FindDuplicates]’
        , @stmt = N’SELECT Duplicates.CustomerID
        ,Customers.FirstName
        ,Customers.LastName
        ,Duplicates.DuplicateMarker
FROM
(
        SELECT CustomerID
                ,ROW_NUMBER() OVER(PARTITION BY FirstName, LastName ORDER BY FirstName, LastName) DuplicateMarker
        FROM dbo.Customers
) Duplicates
INNER JOIN dbo.Customers Customers
        ON Duplicates.CustomerID = Customers.CustomerID
WHERE Duplicates.DuplicateMarker > 1’
, @type = N’SQL’
, @hints = N’OPTION(LOOP JOIN)’ ;
GO

After running the duplicates query again, you have two plans in the plan cache. We can run the query in Listing 17-23 to find the details.

SELECT
        execution_count
        ,total_elapsed_time
        ,total_worker_time
        ,total_physical_reads
        ,total_logical_reads
        ,total_logical_writes
        ,qp.dbid
        ,SUBSTRING(ST.text, (QS.statement_start_offset/2) + 1,
    ((CASE statement_end_offset
        WHEN -1 THEN DATALENGTH(st.text)
        ELSE QS.statement_end_offset END
            - QS.statement_start_offset)/2) + 1) Query
        ,qp.query_plan
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) st
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) qp
WHERE SUBSTRING(ST.text, (QS.statement_start_offset/2) + 1,
    ((CASE statement_end_offset
        WHEN -1 THEN DATALENGTH(st.text)
        ELSE QS.statement_end_offset END
            - QS.statement_start_offset)/2) + 1) = ’SELECT Duplicates.CustomerID
        ,Customers.FirstName
        ,Customers.LastName
        ,Duplicates.DuplicateMarker
FROM
(
        SELECT CustomerID
                ,ROW_NUMBER() OVER(PARTITION BY FirstName, LastName ORDER BY FirstName, LastName) DuplicateMarker
        FROM dbo.Customers
) Duplicates
INNER JOIN dbo.Customers Customers
        ON Duplicates.CustomerID = Customers.CustomerID
WHERE Duplicates.DuplicateMarker > 1’
ORDER BY total_elapsed_time DESC ;’

The results of this query are displayed in Figure 17-3. Notice that there are two plans and that each has been executed once. Although the logical reads for the second plan are substantially higher, the elapsed time of the query is shorter.

If we view the graphical execution plan of the faster plan shown in Figure 17-4, we can see that a Nested Loop operator was used to join the results set, as per the instruction in our plan guide.

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 17-24 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 Chapter17 to the @DBName parameter and a value of 2 to the @RequiredSnapshots parameter, the procedure creates a snapshot against the Chapter17 database but only removes the oldest snapshot if at least two snapshots already exist against the Chapter17 database.

The procedure builds up the CREATE DATABASE script in three parts (see Listing 17-24). 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 requird 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

The command in Listing 17-25 runs the DynamicSnapshot procedure against the Chapter17 database specifying that two snapshots should exist at any one time.

EXEC dbo.DynamicSnapshot ’Chapter17’, 2 ;

Rebuilding Only Fragmented Indexes

When you rebuilds 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 17-26 demonstrates how you can use SQLCMD to identify indexes that have more than 25-percent 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) ;

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 17-27 demonstrates running it against the Chapter17 database.

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

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 (for example, IO 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.