Finding Missing Indexes to be added and Unused Indexes to be dropped

A few weeks ago I helped a client on performance tuning on their production SQL Server database. I provided them a bunch of scripts to do a performance health check. Finally we solved the database slowness problem by adding missing indexes and dropping unused indexes. In fact, I found most developers and software vendors don't bother to do index tuning on database or even they don't have such knowledge and skills. In this monthly blog post I would like to share you the script that I use to discover missing indexes and unused indexes. For missing indexes to be added, there may be some duplicated suggestions that you should merge them into one. Following the principles listed in my another article Indexing Guidelines, especially on the column ordering.

-- Top 10 Missing Indexes

SELECT TOP 10

DB_NAME(dm_mid.database_id) AS DatabaseName,

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

JOIN sys.dm_db_missing_index_group_stats dm_migs

ON dm_migs.group_handle = dm_mig.index_group_handle

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

AND OBJECTPROPERTY(dm_mid.[object_id], 'IsMsShipped') = 0

ORDER BY Avg_Estimated_Impact DESC

GO

-- Unused Indexes

SELECT

DB_NAME(dm_ius.database_id) AS DatabaseName,

o.[name] AS ObjectName,

i.[name] AS IndexName,

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 AS dm_ius

JOIN sys.indexes AS i ON i.index_id = dm_ius.index_id AND dm_ius.[object_id] = i.[object_id]

JOIN sys.objects AS o ON dm_ius.[object_id] = o.[object_id]

JOIN sys.schemas s ON o.[schema_id] = s.[schema_id]

JOIN (SELECT SUM([rows]) AS TableRows, index_id, [object_id] FROM sys.partitions GROUP BY index_id, [object_id]) AS 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

AND (dm_ius.user_seeks + dm_ius.user_scans + dm_ius.user_lookups) = 0

ORDER BY TableRows DESC

GO

Availability Group unexpected Failover due to LeaseTimeout and how to solve it

SQL Server Availability Group (AG) high relies on Windows Server Failover Cluster (WSFC) in failover detection. Frequent communication/heartbeat/ping called "Lease mechanism" is used to ensure that the two sides (the Cluster Service and SQL Server service) are in frequent contact, checking each other's state and ultimately preventing a split-brain scenario. By the way, if your AG primary replica is too heavily-loaded, it may occasionally just too busy to signal the WSFC service that it is still alive, in this case the WSFC will decide the SQL Server service is down and trigger a failover. You will see the below error message in the SQL Server Error Log:

Windows Server Failover Cluster did not receive a process event signal from SQL Server hosting availability group '<AG Group Name>' within the lease timeout period.
The state of the local availability replica in availability group '<AG Group Name>' has changed from 'PRIMARY_NORMAL' to 'RESOLVING_NORMAL'.  The state changed because the lease between the local availability replica and Windows Server Failover Clustering (WSFC) has expired.  For more information, see the SQL Server error log, Windows Server Failover Clustering (WSFC) management console, or WSFC log.

We can also read the same error from the Windows Cluster log:

SQL Server Availability Group <AG Group Name>: Lease renewal failed with timeout error.
SQL Server Availability Group <AG Group Name>: The lease is expired.

The lease is a heartbeat that detects the SQL Server process health hosting the primary replica. A thread runs at priority inside the SQL Server and communicates via a Windows event with the SQL Server resource DLL-hosted process (RHS.EXE) - if that thread does not respond within the lease timeout period, the SQL Server resource DLL reports a lease timeout and reports availability group no longer "looks alive", and the availability group resource transitions to RESOLVING state and fails over if configured to do so. Although the lease is primarily a synchronization mechanism between the primary instance and the cluster, it can also create failure conditions where there was otherwise no need to fail over. For example, high CPU, out-of-memory conditions (low virtual memory, process paging), SQL process not responding while generating a memory dump, system not responding, cluster (WSFC) going offline (e.g. due to quorum loss) can prevent lease renewal from the SQL instance and causing a restart or failover which is unexpected.
One resolution is to increase the LeaseTimeout value (default 20000, which means 20 seconds), for example, set it to 60000 (60 seconds). According to the Microsoft document, 1/2 * LeaseTimeout must be less than SameSubnetThreshold * SameSubnetDelay, and SameSubnetThreshold \<= CrossSubnetThreshold and SameSubnetDelay \<= CrossSubnetDelay should be true of all SQL Server clusters. So in order to set LeaseTimeout to let's say 60 secs, you must also increase the SubnetThreshold values and SubnetDelay values, by running the following PowerShell commands:

(get-cluster).SameSubnetDelay = 2000
(get-cluster).SameSubnetThreshold = 20
(get-cluster).CrossSubnetDelay = 4000
(get-cluster).CrossSubnetThreshold = 40
Get-Cluster | fl *subnet*

After you executed the above PowerShell commands, you set the LeaseTimeout value in Failover Cluster Manager > Roles > Availability Group Name > Other Resources > Right-click the AG resource at the bottom of the window (under Other Resources) and select Properties > properties tab. Follow these steps provided by Microsoft.

The new value of property 'LeaseTimeout' will take effect after the resource is taken offline and brought online again.

Top 10 Expensive Queries in user database

This blog post shares the script to get top 10 most expensive queries in the current database on SQL Server. It uses DMVs (mainly sys.dm_exec_query_stats) so it works on all support versions from SQL Server 2012 to SQL Server 2019, and it doesn't required you to enable any database features such as Query Store. You can modify it to add more top 10 figures that the dm_exec_query_stats DMV has. The results contain a query_plan column that you can save the XML content as a .sqlplan file so that it can be opened by SSMS in another computer to view the graphical execution plan of the expensive query. Here is the script:

-- Top 10 queries - total read

SELECT TOP 10

DB_NAME() AS [db_name],

OBJECT_NAME(qp.objectid) AS [object_name],

SUBSTRING(qt.[text], (qs.statement_start_offset/2)+1,

((CASE qs.statement_end_offset

WHEN -1 THEN DATALENGTH(qt.[text])

ELSE qs.statement_end_offset

END - qs.statement_start_offset)/2)+1) AS query_text,

qs.execution_count,

qs.total_logical_reads, (qs.total_logical_reads / qs.execution_count) AS avg_logical_reads,

qs.last_logical_reads,

qs.last_execution_time,

qp.query_plan

FROM sys.dm_exec_query_stats AS qs

CROSS APPLY sys.dm_exec_sql_text(qs.[sql_handle]) AS qt

CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) AS qp

ORDER BY qs.total_logical_reads DESC;

-- Top 10 queries - avg read

SELECT TOP 10

DB_NAME() AS [db_name],

OBJECT_NAME(qp.objectid) AS [object_name],

SUBSTRING(qt.[text], (qs.statement_start_offset/2)+1,

((CASE qs.statement_end_offset

WHEN -1 THEN DATALENGTH(qt.[text])

ELSE qs.statement_end_offset

END - qs.statement_start_offset)/2)+1) AS query_text,

qs.execution_count,

qs.total_logical_reads, (qs.total_logical_reads / qs.execution_count) AS avg_logical_reads,

qs.last_logical_reads,

qs.last_execution_time,

qp.query_plan

FROM sys.dm_exec_query_stats AS qs

CROSS APPLY sys.dm_exec_sql_text(qs.[sql_handle]) AS qt

CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) AS qp

ORDER BY avg_logical_reads DESC;

-- Top 10 queries - total write

SELECT TOP 10

DB_NAME() AS [db_name],

OBJECT_NAME(qp.objectid) AS [object_name],

SUBSTRING(qt.[text], (qs.statement_start_offset/2)+1,

((CASE qs.statement_end_offset

WHEN -1 THEN DATALENGTH(qt.[text])

ELSE qs.statement_end_offset

END - qs.statement_start_offset)/2)+1) AS query_text,

qs.execution_count,

qs.total_logical_writes, (qs.total_logical_writes / qs.execution_count) AS avg_logical_writes,

qs.last_logical_writes,

qs.last_execution_time,

qp.query_plan

FROM sys.dm_exec_query_stats AS qs

CROSS APPLY sys.dm_exec_sql_text(qs.[sql_handle]) AS qt

CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) AS qp

ORDER BY qs.total_logical_writes DESC;

-- Top 10 queries - avg write

SELECT TOP 10

DB_NAME() AS [db_name],

OBJECT_NAME(qp.objectid) AS [object_name],

SUBSTRING(qt.[text], (qs.statement_start_offset/2)+1,

((CASE qs.statement_end_offset

WHEN -1 THEN DATALENGTH(qt.[text])

ELSE qs.statement_end_offset

END - qs.statement_start_offset)/2)+1) AS query_text,

qs.execution_count,

qs.total_logical_writes, (qs.total_logical_writes / qs.execution_count) AS avg_logical_writes,

qs.last_logical_writes,

qs.last_execution_time,

qp.query_plan

FROM sys.dm_exec_query_stats AS qs

CROSS APPLY sys.dm_exec_sql_text(qs.[sql_handle]) AS qt

CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) AS qp

ORDER BY avg_logical_writes DESC;

-- Top 10 queries - total cpu time

SELECT TOP 10

DB_NAME() AS [db_name],

OBJECT_NAME(qp.objectid) AS [object_name],

SUBSTRING(qt.[text], (qs.statement_start_offset/2)+1,

((CASE qs.statement_end_offset

WHEN -1 THEN DATALENGTH(qt.[text])

ELSE qs.statement_end_offset

END - qs.statement_start_offset)/2)+1) AS query_text,

qs.execution_count,

qs.total_worker_time, (qs.total_worker_time / qs.execution_count) AS avg_worker_time,

qs.last_worker_time,

qs.last_execution_time,

qp.query_plan

FROM sys.dm_exec_query_stats AS qs

CROSS APPLY sys.dm_exec_sql_text(qs.[sql_handle]) AS qt

CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) AS qp

ORDER BY qs.total_worker_time DESC;

-- Top 10 queries - avg cpu time

SELECT TOP 10

DB_NAME() AS [db_name],

OBJECT_NAME(qp.objectid) AS [object_name],

SUBSTRING(qt.[text], (qs.statement_start_offset/2)+1,

((CASE qs.statement_end_offset

WHEN -1 THEN DATALENGTH(qt.[text])

ELSE qs.statement_end_offset

END - qs.statement_start_offset)/2)+1) AS query_text,

qs.execution_count,

qs.total_worker_time, (qs.total_worker_time / qs.execution_count) AS avg_worker_time,

qs.last_worker_time,

qs.last_execution_time,

qp.query_plan

FROM sys.dm_exec_query_stats AS qs

CROSS APPLY sys.dm_exec_sql_text(qs.[sql_handle]) AS qt

CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) AS qp

ORDER BY avg_worker_time DESC;

-- Top 10 queries - total memory grant

SELECT TOP 10

DB_NAME() AS [db_name],

OBJECT_NAME(qp.objectid) AS [object_name],

SUBSTRING(qt.[text], (qs.statement_start_offset/2)+1,

((CASE qs.statement_end_offset

WHEN -1 THEN DATALENGTH(qt.[text])

ELSE qs.statement_end_offset

END - qs.statement_start_offset)/2)+1) AS query_text,

qs.execution_count,

qs.total_grant_kb, (qs.total_grant_kb / qs.execution_count) AS avg_grant_kb,

qs.last_grant_kb,

qs.last_execution_time,

qp.query_plan

FROM sys.dm_exec_query_stats AS qs

CROSS APPLY sys.dm_exec_sql_text(qs.[sql_handle]) AS qt

CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) AS qp

ORDER BY qs.total_grant_kb DESC;

-- Top 10 queries - avg memory grant

SELECT TOP 10

DB_NAME() AS [db_name],

OBJECT_NAME(qp.objectid) AS [object_name],

SUBSTRING(qt.[text], (qs.statement_start_offset/2)+1,

((CASE qs.statement_end_offset

WHEN -1 THEN DATALENGTH(qt.[text])

ELSE qs.statement_end_offset

END - qs.statement_start_offset)/2)+1) AS query_text,

qs.execution_count,

qs.total_grant_kb, (qs.total_grant_kb / qs.execution_count) AS avg_grant_kb,

qs.last_grant_kb,

qs.last_execution_time,

qp.query_plan

FROM sys.dm_exec_query_stats AS qs

CROSS APPLY sys.dm_exec_sql_text(qs.[sql_handle]) AS qt

CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) AS qp

ORDER BY avg_grant_kb DESC;

Top 10 High Memory Grants Queries

If you have a database server that looked like it had been suffering from memory contention, you should check what queries were being run that had high memory requirements. Before the SQL Server database engine can execute a submitted query, SQL Server grants the requested memory to the query, such memory is requested for sort operators, hash operators, and parallelism operators in the execution plan. Without the memory grants, a query cannot be started to execute, and the most important point is a query which is waiting in a Resource Semaphore queue can be only executed when ALL lower-cost queues do not contain any other waiting queries. The SQL Server Query Store is a relatively new feature introduced in SQL Server 2016, a common scenario for using this feature is identifying top n queries (by execution time, memory consumption, etc.) in the past x hours. The column max_query_max_used_memory from sys.query_store_runtime_stats DMV is the maximum memory grant (reported as the number of 8 KB pages) for the query plan within the aggregation interval. Below script uses the Query Store to find out the top 10 queries with the highest memory grants over the last 24 hours in an user database:

USE [master]

GO

ALTER DATABASE [AppDB] SET QUERY_STORE = ON

GO

ALTER DATABASE [AppDB] SET QUERY_STORE (OPERATION_MODE = READ_WRITE)

GO

USE [AppDB]

GO

DECLARE @hours int = 24;

SELECT TOP 10 DB_NAME() AS DB, S.[name] AS SchemaName, O.[name] AS ObjectName,

SUBSTRING(t.query_sql_text, 1, 1000) AS QueryText,

CAST(MAX(rs.max_query_max_used_memory * 8 / 1024.0) AS decimal(9, 2)) AS MaxMemoryMB

FROM sys.query_store_query AS Q

JOIN sys.query_store_query_text AS T ON Q.query_text_id = T.query_text_id

JOIN sys.query_store_plan AS P ON Q.query_id = P.query_id

JOIN sys.query_store_runtime_stats AS RS ON P.plan_id = RS.plan_id

JOIN sys.query_store_runtime_stats_interval AS RSI ON Rs.runtime_stats_interval_id = RSI.runtime_stats_interval_id

LEFT JOIN sys.objects AS O ON Q.object_id = O.[object_id]

LEFT JOIN sys.schemas AS S ON O.schema_id = S.[schema_id]

WHERE RSI.start_time > DATEADD(hour, -@hours, GETDATE())

AND t.query_sql_text NOT LIKE '%SELECT TOP 10 DB_NAME() AS DB%'

GROUP BY S.[name], O.[name], SUBSTRING(T.query_sql_text, 1, 1000)

ORDER BY MaxMemoryMB DESC;

Free performance analyzing tool - solarwinds SQL Plan Warnings

This month blog post I would like to introduce a free performance analyzing tool solarwinds SQL Plan Warnings that provides instant visibility into SQL Server query plan warnings. This awesome tool doesn't require you to install anything into your database server or sql instance, it is just an executable that you can run it in a client host which able to access the sql instance. Here comes a demo of how to use it.

1. Download the tool into your client host and unzip it.
    
2. Run the extracted SQL-Plan-Warnings.exe
   
   
   
   
3. Click Connect to instance
   
   
   
   
4.  Choose the database type, e.g. Microsoft SQL Server, click Next.
   
   
5.  Enter the sql server instance, port number, and authentication information, click Connect.
   
   
6.  Click the settings icon on the upper right corner.
   
   
   
   
7. Modify the filter settings per your workload characteristics, then press Save.
   
   
   
   
8. Let your database server to execute some workload, e.g.
   
   
   
   
9. If some of the queries had plan warnings that match the filtering settings you set before, it will be shown by the SQL Plan Warnings tool, you can press refresh to get the latest result.
   
   
   
   
10. You can further investigate the execution plan by mouse-over the result and click show plan.
    
    

There are many free tools for SQL Server on the internet, as a database administrator we need to test them thoroughly and then use them on even our production database without hesitation, in order to make our work more efficient.

Boosting Analytics Query using Columnstore Index

While row store table and B-tree index perform very well for transactional operations include insert/update/delete and selecting small number of rows, analytics query which access large range of rows will incur high I/O on B-tree index. SQL Server 2012 introduced columnstore index which target analytics query, and SQL Server 2016 further enhanced columnstore index to make it updateable nonclustered columnstore index on heap or B-tree. In this blog post I'm going to show you how columnstore index boost up an analytics query, using the StackOverflow2010 database to demo.

Let's say we have the following analytics query to run:
SELECT U.DisplayName, MAX(P.Score)
FROM Users U JOIN Posts P ON U.Id = P.OwnerUserId
GROUP BY U.DisplayName;

Without any nonclustered index, table scan on both tables are required to run this query.

The execution time and I/O incurred are huge (817,869 logical reads, elapsed time 01:05).

Now we add a nonclustered B-tree index with included column:
CREATE INDEX IX_Posts ON Posts (OwnerUserId) INCLUDE(Score);

The analytics query runs faster by using the new index (22,319 logical reads, elapsed time 8 secs).

Let's create a columnstore index and see how it further boost up the query:
CREATE NONCLUSTERED COLUMNSTORE INDEX CI_Posts ON Posts (OwnerUserId, Score);

The analytics query now using the columnstore index which incur lesser I/O and more faster.
Table 'Posts'. Segment reads 5, segment skipped 0.
Table 'Users'. Scan count 5, logical reads 14,783. Elapsed time 3.4 secs.

For a deeper insight into columnstore index design, you can take a look into the design guidance provided by Microsoft.

Backup Compression for TDE Databases

Backup Compression and TDE were both released as SQL Server 2008, but they were not work so well together, the backup compression ratio for TDE enabled database was nearly nothing. SQL Server 2016 has a hidden gem that easily be overlooked in the SQL Server BOL BACKUP SQL documentation, saying "Starting with SQL Server 2016 (13.x), setting MAXTRANSFERSIZE larger than 65536 (64 KB) enables an optimized compression algorithm for Transparent Data Encryption (TDE) encrypted databases that first decrypts a page, compresses it, and then encrypts it again. If MAXTRANSFERSIZE is not specified, or if MAXTRANSFERSIZE = 65536 (64 KB) is used, backup compression with TDE encrypted databases directly compresses the encrypted pages, and may not yield good compression ratios.". In this blog post, let's do experiment on backup a TDE enabled database, to compare the differences between the default (no compression), specified WITH COMPRESSION but no max transfer size, and WITH COMPRESSION plus MAXTRANSFERSIZE greater than 64KB.

Here I have a TDE encrypted database, data size 30GB, in a SQL Server 2016 instance.

Backup with default option (uncompressed)

Backup with COMPRESSION option but no max transfer size specified

 
Backup with COMPRESSION and MAXTRANSFERSIZE greater than 64kb

As you can see, only the BACKUP WITH COMPRESSION and MAXTRANSFERSIZE > 64kb can really compress the backup.

Good news:
Starting with SQL Server 2019 (15.x) CU5, setting MAXTRANSFERSIZE is no longer required to enable this optimized compression algorithm with TDE. If the backup command is specified WITH COMPRESSION or the backup compression default server configuration is set to 1, MAXTRANSFERSIZE will automatically be increased to 128K to enable the optimized algorithm.