Why are periodic restarts required to keep my instance performing well?

Question

We've got a production DB server on SQL 2005. Everything runs normally for a while, but after a couple weeks we see a notable performance drop. Only restarting SQL Server brings performance back to normal.

Some background:

• Running over 1200 databases (mostly single tenant, some multi-tenant). Before anyone lectures on moving to only multi-tenant, there are valid reasons for keeping this structure......
• RAM is 16 GB. After restarting, it doesn't take too long for SQL Server to go back to 15 GB usage.
• Active DB connections is about 80 connections - which we feel is fairly healthy considering there is one connection pool per web server per process - so we don't have a connection leak issue.

We've tried several things in non-peak times: - Run DBCC DROPCLEANBUFFERS (with a CHECKPOINT) to clear data cache. It has no effect, nor does it clear any of the RAM usage). - Run FREEPROCCACHE and FREESYSTEMCACHE to clear query plans and stored proc cache. No effect.

Obviously restarting SQL Server is not ideal in an active production environment. We're missing something. Anyone else go through this?

UPDATE: April-28-2012 Still battling this problem. I've lowered the memory for SQL Server to 10 GB, just to rule out any contention with the OS. I'm getting closer to narrowing it down, but need some help from my next step.

Here's what I found, after restarting SQL Server, the page file hovers between 12.3 GB and 12.5 GB. It'll stay that way for days. Total server threads will hang out between 850 and 930 - also stable and consistent for days on end (sqlserver is steadily between 55 and 85 of those depending on traffic).

Then, there's "an event". I have no idea what the event is, I can't see it in the logs, and I can't see anything consistent on the day of week or time it happens, but all of the suddent he pagefile jumps to either 14.1 or 14.2 GB, and the threads jump to between 1750 and 1785.

Checking perfom when this happens, over 900 of those threads are sqlserver. So I go to sp_who2 to see where are these threads are coming from...and there's just the used 80 or so db connections.

So....does anyone have any ideas how I can locate where the rest of these 900 threads on SQL server are, and what they are doing?

UPDATE: June-01-2012 Still battling the issue. For anyone reading this still, the issue with the threads jumping up has been resolved. This was caused by autodated ComVault backup software. It was creating a thread trying to backup databases that were no longer there (it was maintaining a list of previous databases) rather than just backing up the current databases.

But - the issue still remains, and we have to restart every week, give or take a few days. Working with the Rackspace team to see if they can shed any light.

Answer 1

You say that everything is fine, then after a couple of weeks, performance drops. (Usually, people claim that performance drops quickly, or at specific times, or at seemingly random intervals. That could mean bad I/O performance or lock storms or cpu-intensive queries running at wierd times, or a heavyweight scheduled job or lack of indexing or bad stats causing cpu-intensive queries or disk reads. Or other stuff.) Weeks is unusual.

My hypothesis is that another application on your server is leaking memory. I have seen this with virus software (every DBA's favorite server software villain) and 3rd party monitoring software. I would double check the memory usage of SQL Server, over time, and I'd grab all of the memory usage of all of the other applications on the box as well. If you have hard limits set on SQL Server's memory usage and have it set to not allow paging, it might be other apps that are getting paged out and eating up I/O capacity.

It's not hard to look for. If you are not already keeping metrics on the server, I would just start up Perfmon and have it grab a sample every 30 or 60 minutes. After a few days, you may see another applications memory usage creep upwards.

Are there error messages in the SQL Server log stating that "significant portions of sql server have been paged out"? That would also be a big clue.

Answer 2

Let me congratulate you on being able to run 1200 DBs on a single instance of SQL server with only 16 GB of RAM and have only these type of issues after a couple of weeks of smooth running. Nice story to tell at the local PASS chapter.

Now to troubleshoot: Your RAM is 16 GB for both the SQL and OS. I am assuming your max memory setting is at 15 GB or max. This could be causing the buffer pool to use up all the memory and choke the OS. You are saying that clearing up the buffer pool and caches are not showing any differences, plus your PLE is above 300. This testifies against memory bottle necks. How is the CPU and IO on the server (specs/stats)?

Run select * from sys.dm_exec_request where session_id>50 and session_id<>@@spid and what are the resource contentions you see (wait_type, wait_time, last_wait_type, wait_resource).

Answer 3

1200 databases, an os, and possibly other stuff? Yeah, I think the server itself is going to need more than 1gb of ram to function, especially considering that, if you set 15gb as SQL Server's max memory setting, it still needs additional memory outside of that 15gb for threads.

I'd bump SQL Server down to 14gb to give the server a little more breathing room.

Also, an example given in "Professional SQL Server 2008 Internals and Troubleshooting" for memory allowances on a SQL Server 2008 x64 system with third-part backup utility with 16GB RAM:

• 2 GB for Windows
• 1GB for worker threads
• 1GB for MPAs, etc.
• 1GB for the backup program
• 11GB for SQL Server

In the book it shows how to determine the max number of threads you can have, and how to calculate how much memory they'll take up. Run this (change the server type to match your server) to figure out how much memory your threads will need.

declare @servertype int

set @servertype=1
/*
1: x86 (32-bit)
2: x64 (64-bit)
3: IA64

*/

select max_workers_count *
    (
        case @servertype when 1 then .5
            when 2 then 2
            when 3 then 4
            else .5
        end
    )
from sys.dm_os_sys_info

Answer 4

If the database memory is evenly distributed across all the databases you only have 12.8 Megs for each database (15*1024)/1200=12.8. You need more memory.

You need to look into why performance is slowing down. Are you seeing locking, blocking, etc? What are the wait stats looking like?

Answer 5

The DBCC commands are only going to clear the memory buffers they are not going to release the memory back to the OS.

Do you know that SQL Server is actually consuming the memory? I would suggest looking at setting up Perfmon session or start collecting DMV information after a restart to find out what SQL Server is doing and working on. Also take note if users are doing more work than normal during your collection time (such as End-of-Month processing, etc). Are you running SSRS, SSIS, or SSAS on the same server?

You have 1200 databases on the system, what is the largest size DB you have?