SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


A bit of a mystery regarding a busy server login timeouts


A bit of a mystery regarding a busy server login timeouts

Author
Message
codykonior
codykonior
SSC-Enthusiastic
SSC-Enthusiastic (109 reputation)SSC-Enthusiastic (109 reputation)SSC-Enthusiastic (109 reputation)SSC-Enthusiastic (109 reputation)SSC-Enthusiastic (109 reputation)SSC-Enthusiastic (109 reputation)SSC-Enthusiastic (109 reputation)SSC-Enthusiastic (109 reputation)

Group: General Forum Members
Points: 109 Visits: 525
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?
Grant Fritchey
Grant Fritchey
SSC-Dedicated
SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)

Group: General Forum Members
Points: 39964 Visits: 32653
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 and SQL Server Execution Plans
Product Evangelist for Red Gate Software
WorkasDBA
WorkasDBA
SSC-Enthusiastic
SSC-Enthusiastic (110 reputation)SSC-Enthusiastic (110 reputation)SSC-Enthusiastic (110 reputation)SSC-Enthusiastic (110 reputation)SSC-Enthusiastic (110 reputation)SSC-Enthusiastic (110 reputation)SSC-Enthusiastic (110 reputation)SSC-Enthusiastic (110 reputation)

Group: General Forum Members
Points: 110 Visits: 122
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?
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search