Click here to monitor SSC
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in
Home       Members    Calendar    Who's On

Add to briefcase

A bit of a mystery regarding a busy server login timeouts Expand / Collapse
Posted Monday, August 25, 2014 12:53 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Today @ 4:08 AM
Points: 59, Visits: 457
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?
Post #1606970
Posted Monday, August 25, 2014 4:49 AM



Group: General Forum Members
Last Login: Yesterday @ 8:46 PM
Points: 16,199, Visits: 30,702
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
The Scary DBA
Author of:
SQL Server Query Performance Tuning
SQL Server Execution Plans

Product Evangelist for Red Gate Software
Post #1607011
Posted Monday, August 25, 2014 9:28 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, January 14, 2016 11:34 AM
Points: 46, Visits: 104
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?
Post #1607115
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse