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


SQL server become unresponsive


SQL server become unresponsive

Author
Message
Rem70Rem
Rem70Rem
Hall of Fame
Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)

Group: General Forum Members
Points: 3241 Visits: 745
Hi,

Since a few weeks, our SQL server (2005 64 bits Standard Edition running on Windows 2003 DataCenter) has some issues performance wise. A few times a weeks the dataserver become very slow even unresponsive. I found in the event log the following entry:

A significant part of sql server process memory has been paged out. This may result in a performance degradation. Duration: 14419 seconds. Working set (KB): 47888, committed (KB): 161528, memory utilization: 29%%.

Since we are using the standard edition, we can’t use the Lock pages in memory user right. I have no idea what to do next. Can someone has a good workaround?

Rem
David Benoit
David Benoit
SSCoach
SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)

Group: General Forum Members
Points: 18746 Visits: 3650
There is a great post on your problem exactly here - http://weblogs.asp.net/omarzabir/archive/2007/10/19/a-significant-part-of-sql-server-process-memory-has-been-paged-out-this-may-result-in-performance-degradation.aspx

Please reply and comment as to whether it was useful and fit your situation. There are several links there to MS articles as well so, be sure to check them out as part of your TS.

David

@SQLTentmaker

“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot
GilaMonster
GilaMonster
SSC Guru
SSC Guru (547K reputation)SSC Guru (547K reputation)SSC Guru (547K reputation)SSC Guru (547K reputation)SSC Guru (547K reputation)SSC Guru (547K reputation)SSC Guru (547K reputation)SSC Guru (547K reputation)

Group: General Forum Members
Points: 547971 Visits: 47738
How much memory on the server? What's SQL's max memory set to? Are there other apps on the server?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass


Rem70Rem
Rem70Rem
Hall of Fame
Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)

Group: General Forum Members
Points: 3241 Visits: 745
Currently the server had 16GB of memory and 8gb are assigned (as min & max) to SQL server. As of now, there is no other application running in the server.

This is a fresh installation on a new hardware. I found this a bit scary.

Rem
GilaMonster
GilaMonster
SSC Guru
SSC Guru (547K reputation)SSC Guru (547K reputation)SSC Guru (547K reputation)SSC Guru (547K reputation)SSC Guru (547K reputation)SSC Guru (547K reputation)SSC Guru (547K reputation)SSC Guru (547K reputation)

Group: General Forum Members
Points: 547971 Visits: 47738
Is the min and max memory set to the same value? If so, please change that, there's a problem whereby if they are identical then some of SQL's memory management abilities are unavailable. If max is 8GB, set the min to 7.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass


ALZDBA
ALZDBA
SSC Guru
SSC Guru (70K reputation)SSC Guru (70K reputation)SSC Guru (70K reputation)SSC Guru (70K reputation)SSC Guru (70K reputation)SSC Guru (70K reputation)SSC Guru (70K reputation)SSC Guru (70K reputation)

Group: General Forum Members
Points: 70326 Visits: 9209
Rem,
Can you post sqlserver version info (sp/cu) ?

Gail,
Do you have a kb ref for the min = max memory config problem ?

Johan


Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere w00t

- How to post Performance Problems
- How to post data/code to get the best help


- How to prevent a sore throat after hours of presenting ppt ?


press F1 for solution, press shift+F1 for urgent solution :-D


Need a bit of Powershell? How about this

Who am I ? Sometimes this is me Alien but most of the time this is me Hehe
GilaMonster
GilaMonster
SSC Guru
SSC Guru (547K reputation)SSC Guru (547K reputation)SSC Guru (547K reputation)SSC Guru (547K reputation)SSC Guru (547K reputation)SSC Guru (547K reputation)SSC Guru (547K reputation)SSC Guru (547K reputation)

Group: General Forum Members
Points: 547971 Visits: 47738
I don't. It was mentioned in a presentation. I'll see if I can find a ref for you.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass


Rem70Rem
Rem70Rem
Hall of Fame
Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)

Group: General Forum Members
Points: 3241 Visits: 745
The hardware has 8 CPU (Hyper-Threading). CPUs 0 to 5 are Processors Affinity while 6 to 15 are I/O Affinity (because I was seeing entry like this on e in the log "The time stamp counter of CPU on scheduler id 14 is not synchronized with other CPUs")

Is it OK to have the HT setup for a SQL server? I saw somewhere that it is not recommended, is it right?


I have set the max memory at 8gb for the SQL and the min to 0 (as mentioned above).
Rem70Rem
Rem70Rem
Hall of Fame
Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)

Group: General Forum Members
Points: 3241 Visits: 745
Forgot to add the version...

Microsoft SQL Server 2005 - 9.00.3042.00 (X64) Feb 10 2007 00:59:02 Copyright (c) 1988-2005 Microsoft Corporation Standard Edition (64-bit) on Windows NT 5.2 (Build 3790: Service Pack 1)
RBarryYoung
RBarryYoung
SSC Guru
SSC Guru (79K reputation)SSC Guru (79K reputation)SSC Guru (79K reputation)SSC Guru (79K reputation)SSC Guru (79K reputation)SSC Guru (79K reputation)SSC Guru (79K reputation)SSC Guru (79K reputation)

Group: General Forum Members
Points: 79908 Visits: 9519
To the best of my knowledge, Hyperthreading is not recommended for SQL Server.

-- RBarryYoung, (302)375-0451 blog: MovingSQL.com, Twitter: @RBarryYoung
Proactive Performance Solutions, Inc.
"Performance is our middle name."
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