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?
Best Answer
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:
If you just want to see this as a quickshot. You can try to query the
dm_os_sys_memory
which has a nice columnsystem_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.Statistics are out of date
You might want to update your statistics for your database tables, as they may be too old.
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: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. TheREAD UNCOMMITTED
should be used as some indices may be written at the moment. You can try to run the following statement.Each row which will be returned is an index which is fragmented and may need an
REBUILD
orREORGANIZE
. 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.This will return the biggest waitcounter and may help to find a bottleneck or error. You can do this using the following query:
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.