SQL Server Performance Tuning

SQL Server Index Tuning for Performance

Updated
10 min read
Written by
Mark Varnas

You’ve probably heard: “Indexes are not free”. And this is right!

Indexes can greatly improve query performance in the SQL Server, but they may hamper the performance too.

There are costs in storage (disk space) as well as resources for the maintenance required on Data Manipulation Language (DML) operations.

So, it’s very important to understand your overall workload and find a good balance between making your queries efficient and not paying too much for that.

There are many approaches, concepts, and techniques about the index, below you will see some of them.

Missing indexes

SQL Server will sometimes suggest an index that it believes will help that query run faster.

These are known as missing index suggestions.

SQL Server is very good at determining when an index is needed but sometimes it fails.

The script below identifies missing indexes that have more than 70,000 unique compiles, user seeks, or user scans, and an average usage impact higher than 80%.

Also, take into account the following:

  • You should not create all the missing indexes this script suggests. They are just for your guidance.
  • If the server was rebooted earlier than 2 weeks ago, the stats are useless.
  • You also probably shouldn’t have more than 5 indexes per table (over-indexing).
SELECT sd.name
	,substring(d.statement, charindex('.', d.statement) + 1, len(d.statement)) AS tablename
	,s.unique_compiles
	,s.user_seeks
	,s.user_scans
	,s.avg_user_impact
	,'use [' + sd.name + ']; create index IX_' + replace(reverse(substring(reverse(d.statement), 0, charindex('[', reverse(d.statement)))), ']', '') + '_nn ON ' + substring(d.statement, charindex('.', d.statement) + 1, len(d.statement)) + ' (' + CASE 
		WHEN d.equality_columns IS NOT NULL
			AND d.inequality_columns IS NOT NULL
			THEN d.equality_columns + ',' + d.inequality_columns
		WHEN d.equality_columns IS NOT NULL
			AND d.inequality_columns IS NULL
			THEN d.equality_columns
		WHEN d.equality_columns IS NULL
			AND d.inequality_columns IS NOT NULL
			THEN d.inequality_columns
		END + ')' + CASE 
		WHEN d.included_columns IS NOT NULL
			THEN ' include (' + d.included_columns + ')'
		ELSE ''
		END AS create_cmd
FROM master.sys.dm_db_missing_index_details d
INNER JOIN master.sys.dm_db_missing_index_groups g ON d.index_handle = g.index_handle
INNER JOIN master.sys.dm_db_missing_index_group_stats s ON g.index_group_handle = s.group_handle
INNER JOIN master.dbo.sysdatabases sd ON d.database_id = sd.dbid
WHERE sd.dbid > 4
	AND sd.name NOT IN (
		'distribution'
		,'LogShipping'
		)
	AND (
		s.unique_compiles > 70000
		OR s.user_seeks > 70000
		OR s.user_scans > 70000
		)
	AND s.avg_user_impact > 80
ORDER BY sd.name
	,s.user_seeks DESC
	,s.user_scans DESC

Fill factor option

When creating (or rebuilding) an index, you can set the fill factor.

The fill factor specifies the percentage of each 8K data page to be filled at the ‘leaf’ level of the index.

Lowering this setting may improve performance by reducing page splits, but do not set the system-wide value for it, probably that will hurt more than help.

It would be best if you had a proper maintenance plan and only changed the fill factor for heavily fragmented indexes.

If needed, start reducing the fill factor gradually, going to 95%, and rebuilding the indexes in a planned change.

Unused indexes

Sometimes an index is never used to improve performance.

Regardless, SQL Server must maintain it during every INSERT, UPDATE, and DELETE operation.

So, why keep this index?

You can use the script below to find indexes that were updated more than 5000 times, never used, or with many updates and few lookups ( < 10 ):

  1. Save the ‘create_cmd’ output for the re-creation of the index script in case a rollback is needed.
  2. Execute the output “drop_cmd” to implement the change.
  3. Make sure there is not any query or store procedure forcing hint to the indexes that will be dropped.
SET NOCOUNT ON

DECLARE @DBName NVARCHAR(128)
	,@cmd NVARCHAR(max) = N''
	,@collation VARCHAR(100)

SELECT @collation = convert(VARCHAR, serverproperty('collation'))

IF object_id('tempdb..#indexes') IS NOT NULL
	DROP TABLE #indexes

CREATE TABLE #indexes (
	databasename SYSNAME NOT NULL
	,tablename VARCHAR(200) NOT NULL
	,indexname VARCHAR(200) NOT NULL
	,drop_cmd VARCHAR(max) NOT NULL
	,create_cmd VARCHAR(max) NOT NULL
);

DECLARE DBList CURSOR LOCAL FAST_FORWARD FOR
	SELECT cast(name AS NVARCHAR(128))
	FROM master.dbo.sysdatabases
	WHERE dbid > 4
		AND name NOT IN ('distribution', 'LogShipping')
		AND databasepropertyex(name, 'Status') = 'ONLINE'
		AND databasepropertyex(name, 'Updateability') = 'READ_WRITE'
OPEN DBList
FETCH NEXT FROM DBList INTO @dbname
WHILE @@fetch_status <> - 1
BEGIN
	SET @cmd = N'
;WITH notusedindexes
AS (
		SELECT db_name(db_id()) AS DBName
			,ss.name + ''.'' + o.name AS [table_name]
			,i.name AS [index_name]
			,i.type_desc
			,'' DROP INDEX '' + i.name + '' ON '' + ss.name + ''.'' + o.name AS drop_cmd
		FROM [' + @DBName + '].sys.dm_db_index_usage_stats iu
		INNER JOIN [' + @DBName + '].sys.indexes i ON iu.index_id = i.index_id
			AND iu.object_id = i.object_id
		INNER JOIN [' + @DBName + '].sys.objects o ON i.object_id = o.object_id
		LEFT JOIN [' + @DBName + '].sys.objects n ON n.name = i.name
		INNER JOIN [' + @DBName + '].sys.schemas ss ON o.schema_id = ss.schema_id
		WHERE iu.database_id = db_id()
			AND i.type <> 0
			AND i.type_desc = '' NONCLUSTERED ''
			AND (
					n.TYPE IS NULL
					OR n.type NOT IN ('' PK '', '' UQ '')
				)
			AND (
					(	iu.user_seeks = 0
						AND iu.user_scans = 0
						AND iu.user_lookups = 0
						AND iu.user_updates <> 0
					) -- only ever updated
				OR (	iu.user_updates > 100000
						AND iu.user_seeks < 10
						AND iu.user_scans < 10
						AND iu.user_lookups < 10
					) -- loads of updates, few lookups
				OR (    iu.user_seeks < 10
						AND iu.user_scans < 10
						AND iu.user_lookups < 10
						AND iu.user_updates < 10
					)
				)
	)
	,-- hardly used at all and iu.user_updates > 5000), -- updated more than 5000 times
indexlist
AS (
	SELECT DISTINCT n.table_name tablename
		,n.index_name indexname
		,n.drop_cmd
		,i.is_unique
		,(
			SELECT DISTINCT stuff((
						SELECT ''
							,'' + c.name
						FROM sys.index_columns ic1
						INNER JOIN sys.columns c ON ic1.object_id = c.object_id
							AND ic1.column_id = c.column_id
						WHERE ic1.index_id = ic.index_id
							AND ic1.object_id = i.object_id
							AND ic1.index_id = i.index_id
							AND ic1.is_included_column = 0
						ORDER BY ic1.key_ordinal
						FOR XML PATH('''')
						), 1, 2, '''')
			FROM sys.index_columns ic
			WHERE object_id = i.object_id
				AND index_id = i.index_id
		 ) AS keycolumnlist
		,(
			SELECT DISTINCT stuff((
						SELECT ''
							,'' + c.name
						FROM sys.index_columns ic1
						INNER JOIN sys.columns c ON ic1.object_id = c.object_id
							AND ic1.column_id = c.column_id
						WHERE ic1.index_id = ic.index_id
							AND ic1.object_id = i.object_id
							AND ic1.index_id = i.index_id
							AND ic1.is_included_column = 1
						ORDER BY index_column_id
						FOR XML PATH('''')
						), 1, 2, '''')
			FROM sys.index_columns ic
			WHERE object_id = i.object_id
				AND index_id = i.index_id
			) AS includedcolumnlist
	FROM sys.indexes i
	INNER JOIN sys.index_columns ic ON i.object_id = ic.object_id
		AND i.index_id = ic.index_id
	INNER JOIN sys.objects o ON i.object_id = o.object_id
	INNER JOIN notusedindexes n ON i.object_id = object_id(n.table_name)
		AND i.name collate ' 
		+ @collation + ' = n.index_name collate ' + @collation + '
	WHERE o.is_ms_shipped = 0
		AND i.is_primary_key = 0
		AND i.is_disabled = 0
		AND i.is_unique_constraint = 0
		AND i.has_filter = 0
	)
INSERT INTO #indexes
SELECT db_name() AS databasename
	,tablename
	,indexname
	,drop_cmd
	,''

CREATE '' + CASE 
		WHEN is_unique = 1
			THEN '' UNIQUE ''
		ELSE ''''
		END + '' INDEX '' + indexname + '' ON '' + tablename + '' ('' + keycolumnlist + '') '' + CASE 
		WHEN includedcolumnlist IS NOT NULL
			THEN + '' include('' + includedcolumnlist + '') ''
		ELSE ''''
		END + ''
	WITH (data_compression = page) '' create_cmd
FROM indexlist'

	PRINT @cmd

	--exec sys.sp_executesql @cmd
	FETCH NEXT
		FROM DBList
		INTO @DBName
END
CLOSE DBList
DEALLOCATE DBList

SELECT * FROM #indexes

DROP TABLE #indexes

More information:

Speak with a SQL Expert

In just 30 minutes, we will show you how we can eliminate your SQL Server headaches and provide 
operational peace of mind

Article by
Mark Varnas
Founder | CEO | SQL Veteran
Hey, I'm Mark, one of the guys behind Red9. I make a living performance tuning SQL Servers and making them more stable.

Discover More

SQL Server Health Check SQL Server Migrations & Upgrades SQL Server Performance Tuning SQL Server Security SQL Server Tips

Discover what clients are saying about Red9

Red9 has incredible expertise both in SQL migration and performance tuning.

The biggest benefit has been performance gains and tuning associated with migrating to AWS and a newer version of SQL Server with Always On clustering. Red9 was integral to this process. The deep knowledge of MSSQL and combined experience of Red9 have been a huge asset during a difficult migration. Red9 found inefficient indexes and performance bottlenecks that improved latency by over 400%.

Rich Staats 5 stars
Rich Staats
Cloud Engineer
MetalToad

Always willing to go an extra mile

Working with Red9 DBAs has been a pleasure. They are great team players and have an expert knowledge of SQL Server database administration. And are always willing to go the extra mile to get the project done.
5 stars
Evelyn A.
Sr. Database Administrator

Boosts server health and efficiency for enhanced customer satisfaction

Since adding Red9 to the reporting and DataWarehousing team, Red9 has done a good job coming up to speed on our environments and helping ensure we continue to meet our customer's needs. Red9 has taken ownership of our servers ensuring they remain healthy by monitoring and tuning inefficient queries.
5 stars
Andrew F.
Datawarehousing Manager
See more testimonials