SQL Server Slowdown?

Question

We're a small shop, and I'm certainly not a DBA by any stretch of the imagination, but I've recently inherited a somewhat large older database (been running since at least 04), that in the past month has experienced a slow down. It started with our more complex queries, but has even started to affect the simpler ones.

Prior to my coworker leaving, he had upgraded the instance from 2008 (Standard) to 2014 (Enterprise). The files were also shrunk on the DB, (which I now know is frowned upon.) However, I believe the instance ran fine directly after the upgrade and the shrink.

We have two dedicated SQL servers, set up with AlwaysOn. The servers are about 5 years old, but they're still good servers (64GB memory, Intel Xeon). It doesn't matter which is primary, both experience the slow down. The servers have a few other instances, but the one I'm trying to troubleshoot is by far the biggest one. A few of these instances are Dev and Staging, both of which are about a month old. A query that I've been running will take two minutes on either of these, but 20-40 minutes on live. I've tried copying live to a new instance, but it too is slow.

I've tried restarting the instance, updating statistics, refreshing views (which we aren't really using), rebuilding indexes, but nothing seemed to help. Any ideas?

Answer 1

Well here is a small checklist. Due to the fact that I'm not on your machine I cannot take a look inside and give you some suggestions. But I'm pretty sure, that you'll find the root cause with the provided statements.

Wrong memory configuration

As you mentioned, you upgraded your SQL Server and run different instances on the same physical machine. One of the things which may be harmful is a restart or anything like that (e.g. Failover). Maybe your instance has reset their memory limits. This may explain your drastically slowdown. You can check if they still up with your documented max memories. You can get them running the following the command below:

EXEC sp_configure N'max server memory (MB)'

If you just want to see this as a quickshot. You can try to query the dm_os_sys_memory which has a nice column system_memory_state_desc which isn't very detailed but it says you the needed things too. It will display if your memory is low, which means there is a constant memory pressure on the system.

SELECT system_memory_state_desc
FROM sys.dm_os_sys_memory

Statistics are out of date

You might want to update your statistics for your database tables, as they may be too old.

EXEC sp_updatestats

In a situation like yours, it is wise to throw away your cache plan, as it might be useless due to the old statistics. If sp_updatestats shows many tables and many statistics which are updated, it may be a good idea to throw it away and let the SQL Server recreate it.

You can achieve this using this DBCC command:

DBCC FREEPROCCACHE

Fragmented indices

It might be, that your indices are too fragmented which cause a huge load on your I/O subsystem. Before you blindly execute the statement, you should check if your machine has a higher load on your I/O subsystem. This may be easily done by using perfmon for example. The READ UNCOMMITTED should be used as some indices may be written at the moment. You can try to run the following statement.

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED 

-- Get Index Fragmentation
SELECT OBJECT_NAME(ind.OBJECT_ID) AS TableName
    , ind.name AS IndexName, indexstats.index_type_desc AS IndexType
    , indexstats.avg_fragmentation_in_percent 
FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, NULL) indexstats 
INNER JOIN sys.indexes ind  
        ON ind.object_id = indexstats.object_id 
        AND ind.index_id = indexstats.index_id 
WHERE indexstats.avg_fragmentation_in_percent > 25 
ORDER BY indexstats.avg_fragmentation_in_percent DESC
GO

Beware, it might run very long and on your system which is already under pressure it might kill it. Only run it, if your system may to be affected by it.

Each row which will be returned is an index which is fragmented and may need an REBUILD or REORGANIZE. This may improve your overall system performance too, but during the rebuild itself, it will slow down!

ARITHABORT problem

It may be an problem with ARITHABORT which tends to make problems on older databases (for some users). Maybe you try to enable/disable it.

Many other causes...

As mentioned in the first sentences, it's pretty hard to see where your problem is. It may be another error - maybe even a hardware fault. I've had a error on a switch to my SAN which killed my performance too for some years (before it was redundant).

But if nothing of the above tends to help, you can still provide the output of your dm_os_wait_stats which may help me to get the right view on your system.

SELECT TOP 30 *
FROM sys.dm_os_wait_stats
ORDER BY wait_time_ms DESC

This will return the biggest waitcounter and may help to find a bottleneck or error. You can do this using the following query:

-- Show all active processes with detailed informations
SELECT pr.spid, pr.loginame, pr.status, pr.blocked, pr.waittime, pr.lastwaittype,pr.cpu, pr.physical_io, pr.memusage, pr.last_batch, db.name AS databaseName, obj.name AS objectName, txt.text AS currentQuery, pr.request_id
FROM sys.sysprocesses as pr
INNER JOIN sys.databases as db
        ON pr.dbid = db.database_id
OUTER APPLY sys.dm_exec_sql_text(pr.sql_handle) as txt
LEFT JOIN sys.all_objects as obj
        ON txt.objectid = obj.object_id
WHERE pr.status <> 'sleeping'
    and pr.spid >= 50

Additionally it can be a process which produces locks or consume more than it should. But I think this isn't the best possibility. But anyway, you can take a look in your running processes. Maybe you see something which isn't correct.

Answer 2

Edit: not mentioned so far....

Check for auto-growth events: https://stackoverflow.com/questions/3752942/how-to-check-when-autogrowth-is-done-last

Check the auto-growth settings. Make sure they are appropriate for the size of your database.

If your files were shrunk you may be experiencing log or data file auto-growth in tiny increments causing queries to run slow and fragmenting your database causing your queries to run slower still.

Finally check for tempdb growth and make sure it is sized appropriately: https://msdn.microsoft.com/en-us/library/ms175527(v=sql.105).aspx

Also make sure auto-shrink is off on each database.

Answer 3

Expanding my comment on Ionic's answer a bit:

If the instances have not had specific memory limits set then I suspect memory contention: the dev/stage/other instances have claimed a pile of memory so there is insufficient for the live instance(s) so queries that would otherwise be performed entirely in RAM are needed extra page reads from disk (because the pages are being evicted to make space for other reads when they would otherwise be kept in RAM for later reuse) and/or spooling intermediate results to tempdb (which is even worse as this both a write and a read operation). In the worst case you have three extra IO operations going on due to the memory starvation: reads of data pages, writes to tempdb, then reads back from tempdb - this can be bad enough with SSDs bit if you are using traditional spinning disk based drives the affect can be massive.

See https://msdn.microsoft.com/en-us/library/ms178067(v=sql.120).aspx for informtaion about setting memory limits.

Instances of SQL server often do not cooperate with each other as well as people often expect. At very least you should set the maximum memory for each non-live instance such that the total when taken leaves plenty for the live instances' common working sets.

NOTE: I appreciate why you would have dev/stage/other on the same kit as live instances (licensing and hardware cost mainly) but I would strongly recommend reconsidering this and instead having dedicated resource for your production environments and keeping other instances on separate (but similarly configured) kit. This way nothing you do in dev/test can adversely affect the performance of your production systems.