August 25, 2014 at 12:53 am
I have a 24core server with 256GB of memory, only running SQL Server 2008R2 Enterprise x64. Connecting locally (e.g. going on through remote desktop and then SSMS):
- You get connection timeouts.
- Activity Monitor runs for a little while, then has timeouts.
- While it is running, almost nothing is going on. Literally 0-4% CPU (in task manager I can see one or two spike every now and again for very short periods of time), 0-1 waiting tasks, database I/O mostly flatlined at 0 and occasionally 30-60MB/s for very short periods of time, 0-18 requests a second, and a fairly small list of open connections.
- The list of long running queries indicates literally a half dozen queries that look fairly simple... with monster times... the rest are very short-running things.
And yet whatever is going on on there is making it utterly dog slow. The memory ring buffers have a lot of type 2 events which indicate the OS is asking for more memory very regularly. The memory limit is set to 245GB so there is some reserved for the OS and it does have about 1GB free in task manager.
I'm kind of at a loss with where to start doing anything to it. Finding the application owner is a mission in itself 😉
I thought I read an article at one stage where SQL Server could gobble memory that should be reserved for making connections, and there was an option to prevent this. Does that ring any bells?
August 25, 2014 at 4:49 am
Might want to check the power settings on the server. That's been hitting a lot of people lately and it will certainly have some of the effects you're outlining here.
If it wasn't for the memory issues, this sounds like either a disk sub-system issue, or possibly, a network issue (which would affect disks too). You might want to validate those settings, check the windows error logs for anything it shows. Take a look at the wait statistics to see what exactly is causing things to slow down. I'd also probably set up an extended event session to capture query metrics.
In short, gather more information, go from there.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
August 25, 2014 at 9:28 am
If you have reasoned out the issue to be Memory, does the simple query do lot of in memory sort operations. May be creating an ordered/grouped by covered index can help. Check the plan for each query and get an estimated size of data. Also, you mentioned that the OS is demanding more memory, do you see lot of paging?
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply