Missing Index Recommendation Column Order

When SQL Server is processing a query, it will sometimes make a suggestion for an index that it believes will help that query run faster. These are known as missing indexes, which can be obtained from the missing index DMVs, or more directly by showing the graphical execution plan when you execute the query in SSMS. But can you blindly create the missing index that recommended by SQL Server? In this post, I will show you why you cannot blind trust what SQL Server told you to do.

CREATE TABLE Candidates (

id int PRIMARY KEY IDENTITY(1, 1),

gender char(1) NOT NULL,

displayName varchar(10) NOT NULL

);

GO

SET NOCOUNT ON;

INSERT Candidates (gender, displayName) SELECT 'M', substring(replace(newID(), '-', ''), cast(RAND() * (31 - 10) AS INT), 10);

GO 500000

INSERT Candidates (gender, displayName) SELECT 'F', substring(replace(newID(), '-', ''), cast(RAND() * (31 - 10) AS INT), 10);

GO 500000

UPDATE STATISTICS Candidates WITH FULLSCAN;

GO

-- Enable Actual Execution Plan for below query

SELECT displayName, gender FROM Candidates WHERE displayName = 'CF53A906FD' AND gender = 'M' OPTION (RECOMPILE);

And here is the recommended index to create:

CREATE NONCLUSTERED INDEX [<Name of Missing Index, sysname,>] ON [dbo].[Candidates] ([gender],[displayName]);

As you can see, the column order has no any considerations on selectivity, they are just a comma-delimited list of column order in the table (actually the key columns in the recommended index will be ordered by equality predicate group first, then inequality predicate group, and inside each group follows the column order in table definition). You can double check the column order in the table definition by executing sp_help system stored procedure.

Solving Last-Page Insert Latch Contention by SQL Server 2019 new feature: OPTIMIZE_FOR_SEQUENTIAL_KEY

Two years ago I wrote a blog post Solving Last-Page Insert Latch Contention by Index Partitioning which explains how to solve a problem called last-page insert PAGELATCH_EX contention, That is if you have an even-increasing index on a table, such as IDENTITY or GETDATE(), then during huge amount of concurrent queries/threads inserting new rows into that table, different threads will contend for exclusive latch PAGELATCH_EX on the last data page of that index in the buffer pool. The solution that I provided on that old post requires adding a computed column on the table, append that column into the clustered index, and enabling table partitioning, which involved quite a lot of table structure changes. SQL Server 2019 introduces a new feature, OPTIMIZE_FOR_SEQUENTIAL_KEY, which is a new index option that intended to address the last page insert contention. Below demonstration shows how you do that.

--When you create the table, e.g.

CREATE TABLE DemoTbl (

Id INT IDENTITY(1, 1) NOT NULL,

col1 varchar(50),

CONSTRAINT PK_DemoTbl PRIMARY KEY CLUSTERED (Id ASC) WITH (OPTIMIZE_FOR_SEQUENTIAL_KEY = ON)

);

GO

--When you create the index, e.g.

CREATE INDEX DemoTbl_Id ON DemoTbl (Id) WITH (OPTIMIZE_FOR_SEQUENTIAL_KEY = ON);

GO

--Or if you need to alter an existing index:

ALTER INDEX DemoTbl_Id ON DemoTbl SET(OPTIMIZE_FOR_SEQUENTIAL_KEY = ON);

GO

--Check which indexes enabled this option in the current database:

SELECT * FROM sys.indexes WHERE optimize_for_sequential_key = 1;

GO

Simple enough :)

Availability Group Listener RegisterAllProvidersIP Setting

Last month I encountered an issue on our production sql server availability group, the AG listener fails to connect after failover. After spending hours to troubleshoot the problem with Microsoft support engineer, we find out the root cause is the cluster resources didn't register all listener IP addresses. There is only one IP address of the primary server, where our sql server is actually configured across multiple subnets. In order to solve it, we executed PowerShell command Set-ClusterParameter RegisterAllProvidersIP 1 which enables the RegisterAllProvidersIP setting on the AG listener. The lesson learned: When we set up new sql server availability group, make sure the parameter (ClusterParameter RegisterAllProvidersIP 1) setting is correct and other settings please refer following link. Reference: Configure availability group listener - SQL Server Always On | Microsoft Docs It's fun to learn something on sql server that I never met before even I'm already a certified sql server DBA with 10+ years hands-on working experience! :D

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;