Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
1  
Points for a thorough question, but have you considered that 16 GB of RAM just might not be enough for 1200 databases? –  Nick Vaccaro Apr 3 '12 at 14:19
    
Can't really help in the grand scheme of things but I do know that MSSQL has been designed to consume as much RAM as is available. This makes sense really as otherwise there is RAM going to waste. The fact that it jumps to 15GB shortly after restart isn't really an issue in itself I don't think. However @Norla could be right that the 16 just isn't enough for what you want to do. –  Tobsey Apr 3 '12 at 14:23
    
How many SPIDs are active during the slowness? Run sp_who2 and give the row count please. –  Nick Vaccaro Apr 3 '12 at 14:24
    
Just checking - Do you have any Sql server jobs running? Could you stop them one by one to see If any one of them causing this issue? –  ydobonmai Apr 3 '12 at 14:25
    
What's the output of: select SUM(single_pages_kb + multi_pages_kb)/1024.0 from sys.dm_os_memory_clerks where [name] = 'TokenAndPermUserStore' –  Mark Storey-Smith Apr 3 '12 at 15:04
show 7 more comments

migrated from stackoverflow.com Apr 3 '12 at 14:46

This question came from our site for professional and enthusiast programmers.

5 Answers

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.

share|improve this answer
    
i agree, the behavior does make it sound like a memory leak. –  Nick Kavadias Apr 4 '12 at 16:05
    
+1 For memory leak. I doubt the page life expectancy is very long on this server, but it shouldn't make the pagefile grow rapidly. FYI, almost the same issue here (it was AV that was the issue): social.msdn.microsoft.com/Forums/en/sqlsetupandupgrade/thread/… –  brian Apr 28 '12 at 14:23
add comment

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?

share|improve this answer
add comment

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).

share|improve this answer
    
the 1200 isn't sooo bad! The biggest obstacle was overcoming connection pool issues, which was solved by having the connection string set to master, and then a USE [DBName] after connection. In terms of the query, I ran select * from sys.dm_exec_requests where session_id>50 and session_id<>@@spid, and it's a short list of 4 to 5 requests, max, and they leave the list within 500 ms typically. But I'm going to try this once we get the slow down, it got restarted on Sunday, so now it's humming as usual. –  PaulJ Apr 5 '12 at 1:06
    
@PaulJ thanks for the tip on connection pooling. I am doing some reading on it now. –  StanleyJohns Apr 5 '12 at 2:10
add comment

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
share|improve this answer
    
great stuff, thanks. I've moved it down to 14 GB. Learned something new here, as I had always let SQL Server takes what it wanted. Another good article for reference backing this up: sqlservercentral.com/blogs/glennberry/2009/10/29/… –  PaulJ Apr 5 '12 at 1:00
add comment

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?

share|improve this answer
    
largest db is 5GB. Only ~25 of them are 1GB or more. Vast majority are 50 to 200 MB. –  PaulJ Apr 3 '12 at 17:41
    
" Are you running SSRS, SSIS, or SSAS on the same server?" - Running none of those services. It's a pure sql box. –  PaulJ Apr 3 '12 at 17:43
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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