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


Server becomes very slow [RESOURCE_SEMAPHORE] waittype.


Server becomes very slow [RESOURCE_SEMAPHORE] waittype.

Author
Message
andyansryan
andyansryan
Forum Newbie
Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)

Group: General Forum Members
Points: 5 Visits: 49
We had the same issue. Initially we did not think it was memory bottlenecks since cache hit ratio was around 100. But we increased the server memory and the issues went away for now.
Marios Philippopoulos
Marios Philippopoulos
SSCarpal Tunnel
SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)

Group: General Forum Members
Points: 4490 Visits: 3755
andyansryan (9/19/2012)
We had the same issue. Initially we did not think it was memory bottlenecks since cache hit ratio was around 100. But we increased the server memory and the issues went away for now.


Page life expectancy, along with memory stalls per sec and lazy writes per sec, is a more reliable indicator of memory pressure than cache-hit ratio. Should be over 1000-2000 sec, depending on server memory.

__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables
Persisting SQL Server Index-Usage Statistics with MERGE
Turbocharge Your Database Maintenance With Service Broker: Part 2
himanshu827 21014
himanshu827 21014
Grasshopper
Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)

Group: General Forum Members
Points: 12 Visits: 91
Hi

Did you try installing SP4 for 2005 . See if that can help.
TheSQLGuru
TheSQLGuru
SSChampion
SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)

Group: General Forum Members
Points: 12303 Visits: 8546
Please don't post to 2 year old threads.

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
Gail Wanabee
Gail Wanabee
Right there with Babe
Right there with Babe (734 reputation)Right there with Babe (734 reputation)Right there with Babe (734 reputation)Right there with Babe (734 reputation)Right there with Babe (734 reputation)Right there with Babe (734 reputation)Right there with Babe (734 reputation)Right there with Babe (734 reputation)

Group: General Forum Members
Points: 734 Visits: 1339
TheSQLGuru (9/20/2012)
Please don't post to 2 year old threads.


Why not? If the information people post is useful, what does the age of the thread have to do with anything?

When I search for information on this website, I search on keywords, irrespective of the date the information was posted.
vishalsurya50
vishalsurya50
Valued Member
Valued Member (56 reputation)Valued Member (56 reputation)Valued Member (56 reputation)Valued Member (56 reputation)Valued Member (56 reputation)Valued Member (56 reputation)Valued Member (56 reputation)Valued Member (56 reputation)

Group: General Forum Members
Points: 56 Visits: 786
Hi ,

Last week we increased RAM and processors. now ,In our environment we have 2 processors,6 cores and hyper threading present ,so a total of 24 processors (6*2*2) available with 48GB RAM. I gave 28gb for sql as max server memory. in event viewer after a day i got insufficient memory to run the query. There are lot of blockings so that application gets hanged and nothing is working fine. Could anyone suggest me in which aspect i should check the performance of sql.

Now i have increased as 38 gb out of total of 48 gb for sql server.

Server:sql server 2012 enterprise
OS:Windows server 2012 standard

In our applications we have long running threads with many joins ,but before there was no issues like blocking as of now.....Need to resolve blockings and run apps fastly . Any one please help me !!!!
Lynn Pettis
Lynn Pettis
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: 39049 Visits: 38518
vishalsurya50 (6/11/2013)
Hi ,

Last week we increased RAM and processors. now ,In our environment we have 2 processors,6 cores and hyper threading present ,so a total of 24 processors (6*2*2) available with 48GB RAM. I gave 28gb for sql as max server memory. in event viewer after a day i got insufficient memory to run the query. There are lot of blockings so that application gets hanged and nothing is working fine. Could anyone suggest me in which aspect i should check the performance of sql.

Now i have increased as 38 gb out of total of 48 gb for sql server.

Server:sql server 2012 enterprise
OS:Windows server 2012 standard

In our applications we have long running threads with many joins ,but before there was no issues like blocking as of now.....Need to resolve blockings and run apps fastly . Any one please help me !!!!


Duplicate post. Please post any responses here.

Please don't make multiple posts. It will simply fragment any responses you may get regarding your problem.

Cool
Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
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