9

Yesterday, my SQL Server database was fine. Today it is almost unusable - it's slowed down by a factor of between five to twenty, depending on when I hit it.

Some data was added to the server in an overnight load process, but nothing like a volume that should impact a database that much. About 50,000 plain text records (no XML or other frippery).

The server was patched this morning before we rebooted it. However, none of our other database servers which also got patched are behaving differently.

Resource Monitor would seem to suggest that its disk IO that's at fault. It's running at close 100% of capacity on the .mdf file the whole time, even when there's not much actually happening in the database. Access to Templog.ldf is also running quite high.

No-one here is an expert DBA (we're all developers with varying amount of SQL skills) and we're all baffled by what's happened. We've tried running sp_updatestats and moving some of the big indexes to different discs, to no avail.

I think this must have something to do with the patch - it seems too much of a co-incidence. A colleague is convinced that it's the data load having caused the size of the mdf to increase to a point where it's caused execution plans to become inefficient.

What on earth has caused this? How can we find out, and what can we do to fix it?

EDIT:

Using sp_WhoIsActive reveals nothing out of the ordinary. It registers my own use of the sproc and some commands from a colleague who is currently trying to move another index. That's probably holding up the DB right now but it was running just as badly before.

It's the Standard version of SQL Server 2008 R2. SELECT @@VERSION gives:

Microsoft SQL Server 2008 R2 (SP2) - 10.50.4033.0 (X64)
Jul 9 2014 16:04:25
Copyright (c) Microsoft Corporation Standard Edition (64-bit) on Windows NT 6.1 (Build 7601: Service Pack 1) (Hypervisor)

The server has 72GB of RAM and three quad-core 2GHz processors.

The patching was only applied to Windows. There were no changes other than the patch.

Selected settings:

_id     name                        value   minimum     maximum     value_in_use    description                                 is_dynamic  is_advanced
1540    min memory per query (KB)   1024    512         2147483647  1024            minimum memory per query (kBytes)           1           1
1541    query wait (s)              -1      -1          2147483647  -1              maximum time to wait for query memory (s)   1           1
1543    min server memory (MB)      0       0           2147483647  16              Minimum size of server memory (MB)          1           1
1544    max server memory (MB)      65536   16          2147483647  65536           Maximum size of server memory (MB)          1           1

UPDATE: Shifting indexes and tables to different disk partitions seems to be improving things. I'm still confused over how we could have reached a tipping point so suddenly with such drastic results.

  • Can you run sp_whoisactive for 5 mins and capture the output to table. You can download it from here and this will show how you can capture output to table – Kin Shah Jul 1 '15 at 15:00
  • Well, if you restarted the server, that means all of your cached data was dumped from the buffer pool, and all of your cached execution plans were dumped as well. This means that SQL Server will have to ramp up both - every execution plan will have to be re-compiled, and if stats are stale you might not get the most efficient plans. It also means data will have to be read into memory from disk, whereas before the restart it was probably humming along with the data in memory. This should be short-lived. – Aaron Bertrand Jul 1 '15 at 15:00
  • @AaronBertrand It's been like this for eight hours. We regularly reboot the server for patching and have never noticed anything like this before. – Bob Tway Jul 1 '15 at 15:01
  • 1
    Don't use the UI to check for configuration settings. SELECT * FROM sys.configurations; - you want value, value_in_use for things like max server memory (MB). Also the build number in SELECT @@VERSION; would be useful, as well as whether this is in a hypervisor and if anything changed on the host since yesterday (or since the last time SQL Server was restarted). – Aaron Bertrand Jul 1 '15 at 15:13
  • 2
    What type of IO subsystem are you using? SAN, local disk, etc? Is there any chance you coincidentally have a drive going bad? Also are any of your DBs stored in the same location as any OS files? And last question. Part of our process before doing an OS upgrade was to take a VM snapshot beforehand. Unfortunately the person responsible forgot to commit it. Very quickly the whole system got slower and slower. Any chance this happened to you? – Kenneth Fisher Jul 1 '15 at 16:18
3

It could happen that a small amount of data reaches a certain limit in the SQL Server to force another plan or something like that. This is not unlikely. But the fact that your disc seems to be heavily under duty takes me to another conclusion.

There are 2 possible base reasons for your slow down.

  1. You upgraded your system and rebooted it
  2. You load a bunch of data in it

Let's take a look at part No. 1

It might be that your SQL Server configuration might be broken. This can cause serious problems regarding your Server speed and the disc usage.

Please check in the first instance your basic server settings. Those basic settings are max server memory, affinity I/O mask, affinity mask and max degree of parallelism. You may need to enable the advanced options using show advanced options.

Here is a complete script:

-- enable advanced options
EXEC sp_configure 'show advanced options',1
-- apply configuration
RECONFIGURE
-- how much memory can the sql server allocate?
EXEC sp_configure 'max server memory'
-- which cpu is used to run I/O operations
EXEC sp_configure 'affinity I/O mask'
-- which cpus can run processes?
EXEC sp_configure 'affinity mask'
-- how many threads can work on one query part?
EXEC sp_configure 'max degree of parallelism'

Compare the result with your documented values in your installation steps. Are they still the same?

It may have many reasons why your server behaves so strange. I would normally bet, that your max server memory is just wrong. This will cause your SQL Server permanently swapping data pages. He can't hold everything in his memory. This means he need to read the pages from the disc, update it, write it instantly back. If another update comes along and uses the same page for an update, it can't be read from the memory. Instead the server needs to read it again from the disc. Just swapping...

Another problem can be a to high affinity on disc or processes. If you used a shared Server (SQL Server + other services) with a dedicated disc for SQL Server (which may be a rare case, but it could be), this could be your problem. Your server normally used to have for example 3 cpus for processes and one for I/O. The other 12 cpus are used for other services. In this case your affinity mask is wrong and uses for example an automatic configuration. This means your Server uses all the 16 cores for processes and I/O dynamically. If you have huge processes running, they can put a huge load on the disc, which it may not handle. But in fact, I don't believe that this is your case. It would be faster (even if just a bit) if this would apply, but your case is a slow down.

Another problem may be a too high degree of parallelism. Which means you have too many threads idling on one partial of a query. This could also cause a huge slow down if the parallelism don't work as expected. But this won't describe your high I/O in total.

Now let's take a look at part No. 2 too

You load a bunch of rows into your system. Even if this is a regular job, it could raised a limit in which your query plans escalate. It could be even the case that your insert in combination with SQL Server produces this behavior.

Yοu mentioned that you already tried to migrate your indices to another disc, which seems to help. This can be the happened just to the fact that you split the load on two different discs.

It may be that your indices were fractured, that your plans were fractured or that your statistics are just outdated.

1. lets check the statistics last update You can do this manually over the interface for each single statistic element. Which would be a pain. Or you can try this code:

SELECT name AS indexname,
STATS_DATE(OBJECT_ID, index_id) AS StatsUpdated
FROM sys.indexes

This will give you a complete information over each index (and heap) and the statistics behind them. Even if you run sp_updatestats it doesn't mean that the statistics were updated. The part when an update is quite tricky, even if you run sp_updatestats or even if auto update statistics is enabled, the statistics won't be updated just in time. Here are some edge points, when an update is needed/generated:

  • An empty table gets one or more rows
  • A table with more than 500 rows updates 20% + 500 additional rows and an insert happened afterwards
  • When 500 rows were changed in a table that holds less than 500 rows

This means, your statistics may be outdated even if you run the update.

You can take a look at the query above. If you find some pretty old statistics in some tables, you may want to run an manual statistic update for this table:

UPDATE STATISTICS dbo.YourBadTable WITH FULLSCAN

After that, you may want to give your server a kick in the ass to throw away all old plans.

DBCC FREEPROCCACHE 

If you just want to clean all caches, you might want to run this instead:

DBCC FREESYSTEMCACHE ('ALL')

This will clean up all caches, not just the plan cache. I would normally warn, to use this on a production server in production phase. But as your server don't work currently, you can't harm them too much. It might slow down for some seconds maybe 1-2 minutes as he needs to rebuild all caches, but after that he should run with the correct plans.

Another reason can be totally fragmented indices. This can be checked on the whole server using this statement:

SELECT * 
FROM sys.dm_db_index_physical_stats (NULL, NULL, NULL, NULL, NULL)

If the fragmentation is very high, you might need to reorganize (fragmentation < 20%) or totally rebuild (>20%) it. This may take more pressure on your disc and cause trouble. On the other hand, if the indices are that bad, it probably would help in the end more than it harms.

Beside those two reasons, there still may be a third problem

It maybe that your server is configured probably, you haven't changed any code in this time, just added a few rows. All statistics are updated and all caches are rebuild. All your indices are reorganized in the way you need them, but still - nothing works. It just might be that you reached the limit of available memory in your processes. Maybe you need more. You can simply check if there is any process which tries to get more memory than you have.

You can check this using this command:

SELECT * FROM sys.dm_exec_query_memory_grants

It will provide you a list of all sessions which consuming memory. There might be some query which is still waiting to get memory. Those queries can be easily filtered. All sessions where granted_memory_kb IS NULL. These are sessions which requested memory but don't get it. Another thing can be an granted memory which may be to low. You can compare the columns requested_memory_kb with granted_memory_kb. Requested shows how much memory the process needs to run optimal while granted shows up the memory which is enable for the process. If a process needs 2GB to run but only gets 2MB... you might get it on your own. ;-)

Another way is to check the RESSOURCE_SEMAPHORE:

SELECT * FROM sys.dm_exec_query_resource_semaphore

You can take a look at the waiter_count and the grantee_count. If the waiter is above 0, you have pressure on your memory, which may cause swapping and may cause the disc pressure seen by you in the perfmon.

0

In addition to possible drive failures, check the state of your RAID subsystem. We saw something similar and it turned out the battery on the RAID controller failed so there was no write cache available - all writes had to go directly to disk. One side note - we could feel the system pause while RDC'ing into it.

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.