SQL - Database Tuning Guide

Overview

Part 1: Is setting up SQL Database Engine to suggested setting over the stock default values.

Part 2: Missing Indexes - Removing Unused Indexes and Adding Missing Indexes.

Part 3: Additional Tools for Performance Monitoring.

 

Where Possible upgrade the client to SQL2019 Express (Latest SQL) due to the way the Query Planner creates execution plans.

Part 1:

 

Configure  TEMPDB

 

Should be on the fastest Harddrive and away from the production database drive and OS if possible if system resources are limited at least split the TEMPDB from the Production Database. (TEMPDB on the OS drive and Production DB on its own Drive)

Suggested would be to Start from 1 GB for all TempDB files and 500MB for the LOG file.

This script can be used if you want to move the TempDB from a Drive to its own Drive

Moves SQL TempDB files to designated location, and sizes them appropriately. If there are multiple instances hosted on the same SQL Server, divide the drive size by the instance count, and create folders for each instance name on the destination drive. · GitHub

 

Configure Database Resizing

 

The Log file (LDF) should always be 25% of the MDF File 
Example 10GB Database the Log should be 2.5GB
The Autogrowth should also be set to Megabytes and not % 

 

 

 

Configure the max degree of parallelism 

Run the following query to get a suggested value for MAXDOP

DECLARE @CoreCount int;
DECLARE @NumaNodes int;
SET @CoreCount = (SELECT i.cpu_count from sys.dm_os_sys_info i);
SET @NumaNodes = (
    SELECT MAX(c.memory_node_id) + 1 
    FROM sys.dm_os_memory_clerks c 
    WHERE memory_node_id < 64
    );
IF @CoreCount > 4 /* If less than 5 cores, don't bother. */
BEGIN
    DECLARE @MaxDOP int;
    /* 3/4 of Total Cores in Machine */
    SET @MaxDOP = @CoreCount * 0.75; 
    /* if @MaxDOP is greater than the per NUMA node
       Core Count, set @MaxDOP = per NUMA node core count
    */
    IF @MaxDOP > (@CoreCount / @NumaNodes) 
        SET @MaxDOP = (@CoreCount / @NumaNodes) * 0.75;
    /*
        Reduce @MaxDOP to an even number 
    */
    SET @MaxDOP = @MaxDOP - (@MaxDOP % 2);
    /* Cap MAXDOP at 8, according to Microsoft */
    IF @MaxDOP > 8 SET @MaxDOP = 8;
    PRINT 'Suggested MAXDOP = ' + CAST(@MaxDOP as varchar(max));
END
ELSE
BEGIN
    PRINT 'Suggested MAXDOP = 0 since you have less than 4 cores total.';
    PRINT 'This is the default setting, you likely do not need to do';
    PRINT 'anything.';
END

 

USE CustomerDatabase;  
GO   
EXEC sp_configure 'show advanced options', 1;  
GO  
RECONFIGURE WITH OVERRIDE;  
GO  
EXEC sp_configure 'max degree of parallelism', 8;  
GO  
RECONFIGURE WITH OVERRIDE;  
GO

 

USE CustomerDatabase;  
GO  
EXEC sp_configure 'show advanced options', 1 ;  
GO  
RECONFIGURE  
GO  
EXEC sp_configure 'cost threshold for parallelism', 50 ;  
GO  
RECONFIGURE  
GO

 

​​​​​​​Set Max Server Memory with T-SQL 

 

ALTER DATABASE (Transact-SQL) Compatibility Level

 

Sets Transact-SQL and query processing behaviours to be compatible with the specified version of the SQL engine.
Generally, allows SQL to process and create better execution plans.

 

--Example of setting SQL compatibility to 2019
ALTER DATABASE CustomerDatabase 
SET COMPATIBILITY_LEVEL =  150 ​​

 

TURN AUTOCLOSE OFF 

USE [master]
GO
ALTER DATABASE [CustomerDatabase] SET AUTO_CLOSE OFF;
GO

 

 

Enabling Parameterization 

Query to check if Parameterization is forced
SELECT name, is_parameterization_forced FROM sys.databases

​​​​​​​--Forced
ALTER DATABASE CustomerDatabase SET PARAMETERIZATION FORCED

Enable Query Store on SQL 2016 and above

 

(Great for Resource/Query monitoring) with only a 3-5% overhead on system resources)

Can be left on permanently 

SQL 2016

ALTER DATABASE [CustomerDatabase]
SET QUERY_STORE = ON
    (
      OPERATION_MODE = READ_WRITE,
      CLEANUP_POLICY = ( STALE_QUERY_THRESHOLD_DAYS = 30 ),
      DATA_FLUSH_INTERVAL_SECONDS = 900,
      QUERY_CAPTURE_MODE = AUTO,
      MAX_STORAGE_SIZE_MB = 1000,
      INTERVAL_LENGTH_MINUTES = 1
    );

 

SQL 2017

ALTER DATABASE [CustomerDatabase]
SET QUERY_STORE = ON
    (
      OPERATION_MODE = READ_WRITE,
      CLEANUP_POLICY = ( STALE_QUERY_THRESHOLD_DAYS = 30 ),
      DATA_FLUSH_INTERVAL_SECONDS = 900,
      MAX_STORAGE_SIZE_MB = 1000,
      INTERVAL_LENGTH_MINUTES = 1,
      SIZE_BASED_CLEANUP_MODE = AUTO,
      MAX_PLANS_PER_QUERY = 200,
      WAIT_STATS_CAPTURE_MODE = ON,
      QUERY_CAPTURE_MODE = CUSTOM,
      QUERY_CAPTURE_POLICY = (
        STALE_CAPTURE_POLICY_THRESHOLD = 24 HOURS,
        EXECUTION_COUNT = 30,
        TOTAL_COMPILE_CPU_TIME_MS = 1000,
        TOTAL_EXECUTION_CPU_TIME_MS = 100
      )
    );

 

SQL 2019

ALTER DATABASE [CustomerDatabase]
SET QUERY_STORE = ON
    (
      OPERATION_MODE = READ_WRITE,
      CLEANUP_POLICY = ( STALE_QUERY_THRESHOLD_DAYS = 30 ),
      DATA_FLUSH_INTERVAL_SECONDS = 900,
      MAX_STORAGE_SIZE_MB = 1000,
      INTERVAL_LENGTH_MINUTES = 1,
      SIZE_BASED_CLEANUP_MODE = AUTO,
      MAX_PLANS_PER_QUERY = 200,
      WAIT_STATS_CAPTURE_MODE = ON,
      QUERY_CAPTURE_MODE = CUSTOM,
      QUERY_CAPTURE_POLICY = (
        STALE_CAPTURE_POLICY_THRESHOLD = 24 HOURS,
        EXECUTION_COUNT = 30,
        TOTAL_COMPILE_CPU_TIME_MS = 1000,
        TOTAL_EXECUTION_CPU_TIME_MS = 100
      )
    );

Enable AutoTuning 

(SQL 2017 and above (Requires Query store running) (cannot be enabled on 2016 and lower)
This will monitor any regressed Queries and if there are multiple Execution plans, SQL will Force the Best Execution Plan.

ALTER DATABASE CustomerDatabase
SET AUTOMATIC_TUNING (FORCE_LAST_GOOD_PLAN = ON ); 
GO

To check Query Store, 
select * from sys.database_query_store_options
You're looking for actual_state_desc to be READ_WRITE.

Then check the tuning options with 
select * from sys.database_automatic_tuning_options where name = 'FORCE_LAST_GOOD_PLAN'
You're looking for actual_state to be set to ON.

 

Part 2:

 

Drop Unused Indexes

This Script will highlight unused indexes from the PLAN CACHE (Should be used when this system has been running for a week without and SQL Server Restart)
This will highlight the TOP 25 Unused Indexes, which can be Dropped via SSMS.

-- Unused Index Script
-- Original Author: Pinal Dave 
SELECT TOP 25
o.name AS ObjectName
, i.name AS IndexName
, i.index_id AS IndexID
, dm_ius.user_seeks AS UserSeek
, dm_ius.user_scans AS UserScans
, dm_ius.user_lookups AS UserLookups
, dm_ius.user_updates AS UserUpdates
, p.TableRows
, 'DROP INDEX ' + QUOTENAME(i.name)
+ ' ON ' + QUOTENAME(s.name) + '.'
+ QUOTENAME(OBJECT_NAME(dm_ius.OBJECT_ID)) AS 'drop statement'
FROM sys.dm_db_index_usage_stats dm_ius
INNER JOIN sys.indexes i ON i.index_id = dm_ius.index_id 
AND dm_ius.OBJECT_ID = i.OBJECT_ID
INNER JOIN sys.objects o ON dm_ius.OBJECT_ID = o.OBJECT_ID
INNER JOIN sys.schemas s ON o.schema_id = s.schema_id
INNER JOIN (SELECT SUM(p.rows) TableRows, p.index_id, p.OBJECT_ID
FROM sys.partitions p GROUP BY p.index_id, p.OBJECT_ID) p
ON p.index_id = dm_ius.index_id AND dm_ius.OBJECT_ID = p.OBJECT_ID
WHERE OBJECTPROPERTY(dm_ius.OBJECT_ID,'IsUserTable') = 1
AND dm_ius.database_id = DB_ID()
AND i.type_desc = 'nonclustered'
AND i.is_primary_key = 0
AND i.is_unique_constraint = 0
ORDER BY (dm_ius.user_seeks + dm_ius.user_scans + dm_ius.user_lookups) ASC
GO

 

 

Create Missing Indexes

This script will highlight the most expensive Queries and Missing Indexes from the PLAN CACHE (Should be used when this system has been running for a week without and SQL Server Restart)

-- Missing Index Script
-- Original Author: Pinal Dave 
SELECT TOP 25
dm_mid.database_id AS DatabaseID,
dm_migs.avg_user_impact*(dm_migs.user_seeks+dm_migs.user_scans) Avg_Estimated_Impact,
dm_migs.last_user_seek AS Last_User_Seek,
OBJECT_NAME(dm_mid.OBJECT_ID,dm_mid.database_id) AS [TableName],
'CREATE INDEX [IX_' + OBJECT_NAME(dm_mid.OBJECT_ID,dm_mid.database_id) + '_'
+ REPLACE(REPLACE(REPLACE(ISNULL(dm_mid.equality_columns,''),', ','_'),'[',''),']','') 
+ CASE
WHEN dm_mid.equality_columns IS NOT NULL
AND dm_mid.inequality_columns IS NOT NULL THEN '_'
ELSE ''
END
+ REPLACE(REPLACE(REPLACE(ISNULL(dm_mid.inequality_columns,''),', ','_'),'[',''),']','')
+ ']'
+ ' ON ' + dm_mid.statement
+ ' (' + ISNULL (dm_mid.equality_columns,'')
+ CASE WHEN dm_mid.equality_columns IS NOT NULL AND dm_mid.inequality_columns 
IS NOT NULL THEN ',' ELSE
'' END
+ ISNULL (dm_mid.inequality_columns, '')
+ ')'
+ ISNULL (' INCLUDE (' + dm_mid.included_columns + ')', '') AS Create_Statement
FROM sys.dm_db_missing_index_groups dm_mig
INNER JOIN sys.dm_db_missing_index_group_stats dm_migs
ON dm_migs.group_handle = dm_mig.index_group_handle
INNER JOIN sys.dm_db_missing_index_details dm_mid
ON dm_mig.index_handle = dm_mid.index_handle
WHERE dm_mid.database_ID = DB_ID()
ORDER BY Avg_Estimated_Impact DESC
GO

 

 

Alternative method to missing Indexes is DETA 

Database engine Tuning Advisor using the query store.  (SQL 2017+ with the latest SSMS) only available on Standard or Dev editions, (Express is a no go).

*Rule no more than 5 indexes per table at max, should really only create an index with careful consideration. 

The DETA could recommend 100’s of index creation, which could ultimately degrade performance.

DETA with the latest SSMS can be used with the Query Store Plan Cache to highlight Expensive Queries.

 

 

 

Part 3:

 

Other Stored Procedures for looking at Database Statistics to highlight Performance issues if you want a more granular level of the Monitoring 

These stored procedures can highlight various statistics to highlight potential performance issues.

Download the Stored Procedures from Brent Ozar (SQL Database Master)

Release 2021-05-30: Fixes and Improvements · BrentOzarULTD/SQL-Server-First-Responder-Kit · GitHub

WhoIsActive - Releases · amachanic/sp_whoisactive · GitHub

SP_BlitzFirst – is used to get a quick snapshot of system resources.

SP_Blitz – Priority checklist of Database backups, performance issues

SP_BlitzCache – Checks the PLAN CACHE to highlight Queries and Indexes

SP_BlitzIndex – a More In-depth Index Check per Database/ Table

sp_blitzQueryStore @databasename = 'CustomerDatabase' – This Stored Procedure is a combination of all the above Stored Procedures but utilize the Query Store Data to highlight performance issues – Great overview on Database Statistics to highlight Missing Index’s etc

 

Query Store Dashboard

 

Query Store Dashboard Report for an Overview Report.

Installation · Evdlaar/QueryStoreDashboard Wiki · GitHub