Silent Performance Killers in SQL Server

Author

Kevin Hill
August 30, 2024

The Top 3!

I get a lot of calls from IT leaders complaining about slowly degrading performance of their SQL Servers. It’s the most common call I get.

Sometimes it’s easy. One missing index stands about above all the rest. Usually, it’s a combination of things.

Here are 3 things you may not even be aware of that can slow your SQL Server down:

Unused Indexes

Indexes that are not being used by the query optimizer when plans are generated are considered “unused”. All indexes on columns that are in your CRUD operations (CReate, Update, Delete) have to be updated for each operation. Index pages take up space on disk, and in memory when being updated. They may also be taking up resources when being defragmented or when stats on them are being updated.

I had a client with 132 indexes on one table recently. 6 were being used. We disabled the others and EVERYTHING on the server went faster, particularly tempdb performance and utilization.

Some code from the internet to find impact of unused indexes:

-- Unused Index Script
-- Original Author: Pinal Dave, modified by me
-- 'Impact' column is just updates * rows, for sorting
-- Run this on a test system first. It checks ALL databases -- by default 

Create Table #Unused(
	[Database] varchar(1000),
	[Table] varchar (500),
	[IndexName] varchar(500),
	[IndexID] bigint,
	[UserSeek] bigint,
	[UserScans] bigint,
	[UserLookups] bigint,
	[UserUpdates] bigint,
	[TableRows] bigint,
	[Impact] bigint
	)

exec sp_MSforeachdb


'use [?]

Insert #Unused

SELECT 
	Db_Name(DB_ID()) as [Database]
	,o.name AS [Table]
	, 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
	, dm_ius.user_updates * p.TableRows
	
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
	--AND o.name in (''CloverSummary'')
ORDER BY 
	(dm_ius.user_seeks + dm_ius.user_scans + dm_ius.user_lookups) ASC
--GO
'

Select * 
from #unused
Where 1=1
	--and [IndexName] like 'NC%' 
	--and [IndexName] IN ('IX_EventDispatched_Includes','IX_EventDispatched_ScheduledDispatchTimestamp_HaltDispatch_Includes','IX_EventDispatched_Includes')
	--and [database] Not in ('MSDB','tempdb')
	--and [database] in ('ChangeMe')
Order By UserSeek + userscans, impact desc


Drop table #Unused

Storage Controllers

This is not my area of expertise per se, but we see a lot of VMWare VMs that only have one virtual controller. Up to 4 are allowed. There are best practices articles available for most of the various virtualization layer offerings. It’s a great idea to give your SQL Server multiple pathways to the disks if you can.

Database Compatibility Level

I see databases every day that are set to Compatibility levels as far back as SQL 2008 (level 100). In some cases, this is completely necessary, especially if you have a ton of legacy code or an old 3rd party app with no ability to update.

One of my clients recently hit me up asking for help with a simple ‘Select Into’ statement that took 9 minutes for 30K rows. It was a cross-database query, but within the same SQL instance. We dug into the execution plans and found a reference to compatibility levels and found that the source database was compatibility level 100 instead of 130 (SQL 2016). That change alone dropped execution time to 3 seconds. This is great for the target query, as well as all the other queries that needed the resources that were being hogged.

More internet code to check your compatibility levels:

Select [name], compatibility_level 
from master.sys.databases

Wrapping up

This is most definitely NOT an exhaustive list of things that can slow SQL Server performance to a crawl, but they are some things that Accidental DBAs and even some Intentional DBAs may not think of at first.

What would you add?

When your SQL Server crashes…who do you call? Microsoft? Ghostbusters? Nope. You call us. Our Pocket DBA® service was designed for this.
1 hour response time, 24 x 7 x 365.
New clients – get the first month FREE on a 6-month agreement!

My Recent LinkedIn Posts:

Interesting Stuff I Read This Week:

Lowe's changes some DEI policies amid legal attacks on diversity programs and activist pressure | AP News – more and more firms are pulling back on DEI, which hurts everyone.

SQL tidBITs:

When you need to make changes to the SQL Server Services, such as the account or password starting and stopping them, use the SQL Server Configuration Manager. Don’t use Control Panel - Services. Config Manager will make necessary permissions changes for you.

Sharing is Caring

If you liked this content, please forward the email to friends and co-workers that also enjoy SQL Server content.

Reply

or to participate.