3

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?

  • Maybe the "ARITHABORT"-Problem? Could it be related to this: dba.stackexchange.com/q/9840/70663? We had a very similar problem. It seems to pop up with older databases, EDIT: Read the article "sommarskog"! – Shnugo Jul 15 '15 at 13:41
  • You should also check statistics io output & query plan for your example query to see if the problem is that you get a totally different kind of plan in production or that the same plan is running a lot slower. – James Z Jul 15 '15 at 13:57
  • 2
    Just noticed that you say that problems started when you installed dev & staging to the same machine? Are they using all the memory that was used for buffer pool earlier (or maybe other resources) – James Z Jul 15 '15 at 13:59
  • JamesZ, I worded it incorrectly. Dev and Staging are a month out of sync of live (last time we restored Live to Dev), but their instances have been around for years. – Michael Jul 15 '15 at 14:07
  • 1
    A reach but fragmentation of the disk that hold the database and log files. Are you possibly on a RAID 5 in a degraded mode. – paparazzo Jul 15 '15 at 14:13
4

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.

  • awesome explanation @ionic – mohan111 Jul 15 '15 at 13:54
  • Thanks for the reply Ionic! Memory reports "Available physical memory is high". I tried the Statistics/Indexes, but to no avail. I tried running my long query with ARITHABORT On and Off, but both times it still experiences a long runtime. dm_os_wait_stats can be found here: i.imgur.com/hphUaPG.png – Michael Jul 15 '15 at 14:39
  • Regarding memory use for each instance: if explicit settings have not been configured all the instances will try grab all the memory they think they need and won't really cooperate with each other when it gets full: so if dev and stage instances are using a lot of RAM then the production instances may not be able to allocate what they need. Set the production instances to allocate at least as much as they need for good running and set the others to make sure that much is always spare for the live instances to take. Or separate production systems from dev/stage/test ones. – David Spillett Jul 15 '15 at 15:18
  • As @DavidSpillett stated, it's always good to set the max memory usage. The described behavior is correct. As the counter stated, your instance is running for a while and hadn't a restart in the youngest time, right? Most of the values seem to be ok. Only the I/O values seem a bit strange. Maybe there is your bottleneck. As you mentioned in another post, your Database is set to growth by 10%. Try to set it to a defined lower level. It might be a problem with your I/O system. – Ionic Jul 15 '15 at 15:21
  • I set max memory on all the instances, giving the one we're troubleshooting 32GB, leaving plenty of free memory summing all the instances. Wouldn't the other instances (dev/staging) be slow if it was I/O? I set growth to 128MB. – Michael Jul 15 '15 at 16:16
1

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.

  • 1
    These were my first thoughts as well. Tempdb contention and autogrowth events for the DB and transaction logs are known causes for slowdown. – Bacon Bits Jul 15 '15 at 14:02
  • Autoshrink is off, Autogrowth is set to 10%, Unlimited max size. The "ROWS Data" is still 43GB, Logs are 51GB, so a 10% growth is still around 5GB for each. The report says "No entry found for autogrow/autoshrink...", but we're in our very light traffic season, so it doesn't surprise me the database hasn't needed to grow since the shrink event. tempdev is 305MB, templog is 739MB, growth 10%. Is that too small? – Michael Jul 15 '15 at 15:09
  • Well percentage values are bad. Try to set a more specific value in MB. 10% means on a 100GB it will grow by 10GB. This is nonsense. Set it to a more specific value like 64MB for example. – Ionic Jul 15 '15 at 15:15
  • 1
    I would suggest a larger value than 64Mb. Small a values could result in significant external (filesystem level) fragmentation which could impact performance. While 64Mb isn't exactly small, it is far from large these days. – David Spillett Jul 15 '15 at 15:35
  • Good point on using a number over a percent. I changed to 128MB for now. – Michael Jul 15 '15 at 15:39
0

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.

  • Thanks for explaining David. Following your advice I set memory limits on the other instances. When we have hardware free up, I'll see if we can move dev/staging to one of those. – Michael Jul 15 '15 at 17:27

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.