SQL Server 2017 DMVs and DMFs

SQL Server 2017 ships with a number of new and enhanced Dynamic Management Views (DMVs) and Dynamic Management Functions (DMFs) that will help DBAs monitor the health and performance of SQL Server instances. They are newly built and available only for SQL Server 2017.

sys.dm_db_log_stats

sys.dm_db_log_stats returns summary level attributes and information on transaction log files of databases. The information includes the recovery model of the database. Use this information for monitoring and diagnostics of transaction log health. It requires the database ID number you want to investigate as an input parameter.

SQL Query

SELECT
       dbs.name AS [database_name],
       ls.*
FROM
       sys.databases AS dbs
       CROSS APPLY sys.dm_db_log_Stats(dbs.database_id) ls
WHERE
       dbs.database_id=ls.database_id

Output

sys.dm_db_log_info

sys.dm_db_log_info returns virtual log file (VLF) information of the transaction log. Each row in the output represents a VLF in the transaction log and provides information relevant to that VLF in the log. It requires the database ID number as an input parameter.

Having a large number of VLFs can negatively impact the start-up and recovery time of your database. With this view, we can see how many VLFs your database currently has, along with their size and status.

SQL Query

SELECT
       dbs.name AS [database_name],
       li.*
FROM
       sys.databases AS dbs
       CROSS APPLY sys.dm_db_log_info(dbs.database_id) li
WHERE
       dbs.database_id=li.database_id

Output

sys.dm_db_tuning_recommendations

sys.dm_db_tuning_recommendations returns detailed information about tuning recommendations. The data is stored in JSON. This requires enabling of the automatic tuning option. If the database engine identifies the plan that is causing regression then a suggestion will be made for fixing it using this DMV.

SQL Query

-- See the tuning recommendations
SELECT  reason,
        score,
        script = JSON_VALUE(details, '$.implementationDetails.script'),
        planForceDetails.[query_id],
        planForceDetails.[new plan_id],
        planForceDetails.[recommended plan_id],
        estimated_gain = (regressedPlanExecutionCount+recommendedPlanExecutionCount)*(regressedPlanCpuTimeAverage-recommendedPlanCpuTimeAverage)/1000000,
        error_prone = IIF(regressedPlanErrorCount>recommendedPlanErrorCount, 'YES','NO')
FROM   sys.dm_db_tuning_recommendations
CROSS APPLY OPENJSON (Details, '$.planForceDetails')
                WITH (  [query_id] int '$.queryId',
                        [new plan_id] int '$.regressedPlanId',
                        [recommended plan_id] int '$.recommendedPlanId',
                        regressedPlanErrorCount int,
                        recommendedPlanErrorCount int,
                        regressedPlanExecutionCount int,
                        regressedPlanCpuTimeAverage float,
                        recommendedPlanExecutionCount int,
                        recommendedPlanCpuTimeAverage float ) as planForceDetails;

Output

sys.dm_tran_version_store_space_usage

sys.dm_tran_version_store_space_usage returns a table that displays total space in tempdb used by version store records for each database. This is pretty useful in proactively monitoring the tempdb sizing on the version store usage requirement of each database. It returns the aggregated version store space consumed in tempdb per database. It doesn’t have any arguments that need to be supplied. The results show the Database ID, the count of pages reserved in tempdb for version store records, and the total space in kilobytes.

SQL Query

The following query can be used to determine space consumed in tempdb by version store of each database in a SQL Server instance.

SELECT
  DB_NAME(database_id) AS 'Database Name',
  reserved_page_count,
  reserved_space_kb
FROM sys.dm_tran_version_store_space_usage;

Output

sys.dm_os_enumerate_fixed_drives

In earlier version of SQL Server if a DBA needs to find the free space on the drive then he would be using xp_fixeddrives extended stored procedure.

EXEC xp_fixeddrives

The output is as below on my machine, which has three drives.

In SQL Server 2017, a new DMV sys.dm_os_enumerate_fixed_drives has been introduced which would do the same things. Here is the query.

SELECT fixed_drive_path
    ,free_space_in_bytes / (1024 * 1024) 'Free Space'
    ,drive_type_desc
FROM sys.dm_os_enumerate_fixed_drives

The output is as shown below.

As we can see, the output is same as earlier extended procedure.  Using this DMV we can figure out the number of fixed drives (hard drive) a system has along with free space on each of those drives. Using this DMV we can also detect if the disk space is running low on SQL Server.

sys.dm_os_file_exists

In earlier version of SQL Server if a DBA needs to validate that a file exists or not then he would be using xp_fileexist extended stored procedure.

EXEC xp_fileexist 'C:\Backups\DB16_ST_MASTER_81GA_MBOFF.bak';

The output is as shown below.

In SQL Server 2017, a new DMF sys.dm_os_file_exists has been introduced which would do the same things. Here is the query.

SELECT * FROM sys.dm_os_file_exists('C:\Backups\DB16_ST_MASTER_81GA_MBOFF.bak');

The output is as shown below.

As we can see, the output is same as earlier extended procedure.

References

https://docs.microsoft.com/en-us/sql/relational-databases/system-dynamic-management-views/sys-dm-db-log-stats-transact-sql

https://docs.microsoft.com/en-us/sql/relational-databases/system-dynamic-management-views/sys-dm-db-log-info-transact-sql

https://docs.microsoft.com/en-us/sql/relational-databases/system-dynamic-management-views/sys-dm-db-tuning-recommendations-transact-sql

https://docs.microsoft.com/en-us/sql/relational-databases/automatic-tuning/automatic-tuning

https://docs.microsoft.com/en-us/sql/relational-databases/system-dynamic-management-views/sys-dm-tran-version-store-space-usage

 

 

Automatic Plan Correction

Automatic plan correction is a new automatic tuning feature in SQL Server 2017 that identifies SQL query plans that are worse than previous one, and fix performance issues by applying previous good plan instead of the regressed one.

Background

SQL Server Database Engine may use different SQL plans to execute the Transact-SQL queries. Query plans depend on the statistics, indexes, and other factors. SQL Server will analyze possible plans that can be used to execute a T-SQL query and choose the optimal plan. The plans for most of the successfully executed queries are cached and reused when the same query is executed. The optimal plan that should be used to execute some Transact-SQL query might be changed over time (e.g. when statistics change, index is added or removed, etc.).

In some cases, new plan that is chosen might not be better than the previous plans. In other cases, SQL Server might choose the best plan for the current T-SQL query, but the selected plan might not be optimal if the same T-SQL queries is executed with different parameter values. In that case, SQL Server might reuse the plan that is compiled and cached after first execution even if the plan is not optimal for the other executions.

Sometimes, different parameters can cause SQL to have different desired execution plans. For instance, one parameter may return just a small handful of rows, and the query plan may use an index seek with a key lookup. A different parameter may returns hundreds (or thousands) of rows, and in such a scenario a key lookup would be a prohibitive operation to perform, so SQL will decide to do a clustered index (or table) scan instead.

When you start having this kind of problem (parameter sniffing), the issue is that the first time the query is run and there isn’t an execution plan in the plan cache, one will be created and stored in the plan cache for subsequent reuse. If the first query is not your typical workload, then you can have the majority of the subsequent queries running a sub-optimal query plan.

Prior to SQL Server 2016, the fix for this issue would be to use query hints or recompile it with every execution. SQL Server 2016 introduced the Query Store, where you could force a selected plan to be used. SQL Server 2017 takes this to the next level, with Automatic Plan Correction. 

Automatic Plan Correction

In SQL Server 2017, Automatic Plan Correction has been introduced. SQL Server will monitor the queries being run, and if it detects that the current plan is worse than a previous one then SQL Server will automatically force the good plan to be used.

In order to know about previous plans, SQL Server utilizes the Query Store to compare the captured information. After SQL Server 2017 forces a plan using Automatic Plan Correction, it then continues to monitor the performance of that query to ensure that the change is a good change. If it determines that the forced plan wasn’t a good choice, then it will evict the plan from the plan cache, and the next time the query is run a new plan will be created and put into the plan cache.

Let’s demonstrate the automatic plan correction with a real-time example.

Let’s create a Table and insert some sample data into it.

CREATE TABLE dbo.ObjectCategory (
       Id int,
       [Category] nvarchar(200),
       [Name] nvarchar(200),
       index ncci nonclustered columnstore (Id),
       index Ix_Id(Id)
);
INSERT INTO dbo.ObjectCategory VALUES (1, 'Index', 'Ix_Id');
GO
INSERT INTO dbo.ObjectCategory
SELECT TOP 1000000 2 AS Id, 'Object' AS Category, o.name AS [Name]
FROM   sys.objects, sys.all_columns o
GO 2

-- Rebuild indexes after inserting data
CREATE NONCLUSTERED INDEX [Ix_Id] ON [dbo].[ObjectCategory] ([Id] ASC) WITH (DROP_EXISTING = ON);
CREATE NONCLUSTERED COLUMNSTORE INDEX [ncci] ON [dbo].[ObjectCategory] ([Id]) WITH (DROP_EXISTING = ON);
GO
-- Ensure that everything is flushed to disk in the database.
CHECKPOINT;
GO

Let’s turn on Automatic Plan correction.

Prerequisite: – Automatic Plan correction utilizes the Query Store, so it needs to be turned on first.

-- Turn Query Store on:
ALTER DATABASE current
SET QUERY_STORE = ON
    (
      OPERATION_MODE = READ_WRITE  
    , CLEANUP_POLICY = ( STALE_QUERY_THRESHOLD_DAYS = 5 )
    , DATA_FLUSH_INTERVAL_SECONDS = 900 
    , MAX_STORAGE_SIZE_MB = 10 
    , INTERVAL_LENGTH_MINUTES = 1 
    );

-- Turn Automatic Plan Correction on
ALTER DATABASE current
SET AUTOMATIC_TUNING ( FORCE_LAST_GOOD_PLAN = ON );
-- Check that it is turned on.
SELECT *
FROM   sys.database_automatic_tuning_options;
GO

This query returns the Automatic Tuning options for this database.

Let’s clear the procedure cache and drop the clean buffers.

-- Clear the procedure cache
ALTER DATABASE SCOPED CONFIGURATION CLEAR PROCEDURE_CACHE;
-- Ensure that no data pages are in memory.
DBCC DROPCLEANBUFFERS;
GO

Let’s turn on the actual execution plan and execute the following SQL code.

EXECUTE sp_executesql @stmt = N'SELECT COUNT(*) FROM dbo.ObjectCategory WHERE Id = @Id', @params = N'@Id int', @Id = 2;
GO 50

Execution Plan

Here we can see that the ideal execution plan for returning a count of all records (Id = 2) utilizes a ColumnStore index scan and a hash match aggregate operator. Since a ColumnStore index has the column compressed, this would be the fastest way to get all of the rows for this column, so this makes sense.

Let’s proceed with clearing the procedure cache, dropping clean buffers, and executing the next block of SQL code which gets the count for Id = 1 as shown below.

-- Clear the procedure cache.
ALTER DATABASE SCOPED CONFIGURATION CLEAR PROCEDURE_CACHE;
-- Ensure that no data pages are in memory.
DBCC DROPCLEANBUFFERS;
GO
-- Now run query with Id=1, where only one row is touched.
EXECUTE sp_executesql @stmt = N'SELECT COUNT(*) FROM dbo.ObjectCategory WHERE Id = @Id', @params = N'@Id int', @Id = 1;
GO

Execution Plan

Here we can see that the ideal execution plan is to do an index seek and a stream aggregate operator. Since there is only one record for Id = 1.

In this final step, we will drop the clean buffers. We do not clear the procedure cache as we want to start off with using the execution plan that is in the plan cache. We will then run the count for Id = 2 again a few times. Remember that its optimal plan is different from what is currently in the plan cache. If Automatic Plan Correction is working properly, then during its run it will shift from using this cached plan to the preferred plan for that type – the one with the ColumnStore index scan.

-- Ensure that no data pages are in memory.
DBCC DROPCLEANBUFFERS;
GO
-- Execute query again for Id=2. Don't clear cache - it will use the stream aggregate that is in the plan cache.
-- Sometime during the GO loop, the plan should shift from the stream aggregate to the hash aggregate.
EXECUTE sp_executesql @stmt = N'SELECT COUNT(*) FROM dbo.ObjectCategory WHERE Id = @Id', @params = N'@Id int', @Id = 2;
GO 50

Execution Plan

And here we can see that the execution plan that was utilized started off being the one that was last cached, and then it changed to the one in the Query Store between the 15th and 16th execution of the query. Automatic Plan Correction is in action.

So, what causes a plan to be automatically corrected? Well, there are two conditions. The first is if the plan in the query store has fewer errors in it than the currently executing plan. The second is if the estimated gain (comparison of Execution Counts / Average CPU time for recommended and regressed plans) is greater than 10. Either of these conditions will cause the plan to be automatically corrected.

The tuning recommendations can be seen in a new DMV. The data is stored in JSON, so there are a few new functions to use when querying this DMV. This query also shows the formula used to calculate the estimated gain:

-- See the tuning recommendations
SELECT  reason,
        score,
        script = JSON_VALUE(details, '$.implementationDetails.script'),
        planForceDetails.[query_id],
        planForceDetails.[new plan_id],
        planForceDetails.[recommended plan_id],
        estimated_gain = (regressedPlanExecutionCount+recommendedPlanExecutionCount)*(regressedPlanCpuTimeAverage-recommendedPlanCpuTimeAverage)/1000000,
        error_prone = IIF(regressedPlanErrorCount>recommendedPlanErrorCount, 'YES','NO')
FROM   sys.dm_db_tuning_recommendations
CROSS APPLY OPENJSON (Details, '$.planForceDetails')
                WITH (  [query_id] int '$.queryId',
                        [new plan_id] int '$.regressedPlanId',
                        [recommended plan_id] int '$.recommendedPlanId',
                        regressedPlanErrorCount int,
                        recommendedPlanErrorCount int,
                        regressedPlanExecutionCount int,
                        regressedPlanCpuTimeAverage float,
                        recommendedPlanExecutionCount int,
                        recommendedPlanCpuTimeAverage float ) as planForceDetails;

Output

Let’s verify SQL Server Query Store Data in SQL Server Management Studio.

In SQL Server Management Studio (SSMS), we can also review the Query Store / Regressed Queries report as shown below:

Here we can see that the better plan is forced.

Below we can see the SQL Server query plan causing the issue.

Automatic Plan Correction provides a way for SQL Server to detect query plans that have regressed, and to automatically apply the better plan for you. This will handle the parameter sniffing issue – without you having to investigate and fix it yourself. 

References

https://docs.microsoft.com/en-us/sql/relational-databases/automatic-tuning/automatic-tuning

https://blogs.msdn.microsoft.com/sqlserverstorageengine/2017/05/17/automatic-plan-correction-in-sql-server-2017/

https://docs.microsoft.com/en-us/sql/relational-databases/system-catalog-views/sys-database-automatic-tuning-options-transact-sql

https://docs.microsoft.com/en-us/sql/relational-databases/system-dynamic-management-views/sys-dm-db-tuning-recommendations-transact-sql