Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


DB server comes to a crawl every two weeks


DB server comes to a crawl every two weeks

Author
Message
TC-416047
TC-416047
Valued Member
Valued Member (54 reputation)Valued Member (54 reputation)Valued Member (54 reputation)Valued Member (54 reputation)Valued Member (54 reputation)Valued Member (54 reputation)Valued Member (54 reputation)Valued Member (54 reputation)

Group: General Forum Members
Points: 54 Visits: 213
We have had an on going issue with our ERP system that neither Microsoft nor three consulting forms can resolve.
MS did diagnostics on the DB server (2008 R2 runing on server 2012). They found no issues with the server or the configuration.
The same goes for consulting firms we've hired.

Symptoms:
Sales order entry comes to a crawl. It takes 12 seconds to retrieve a part description and price for a new line item.
Existing orders with 200 lines take 10 minutes to retrieve.
No other programs are impacted that we can tell.

Current Solution:
If we restart the sql server service the problem goes away for about two weeks.
Today is the first time the problem came back the next day.

Observations:
All reports show very low usage of the server.
The server (DELL R720) which has 128 gigs of ram and 98gigs have been allocated to SQL.
Nothing indicates the server is working hard. CPU usage is <10%.
Cache is perfect.
No locks
No Blocking
We've monitored wait states and found nothing unusual.
Nothing else is running on this server except SQL Server.

Request:
We are at our wits end trying to figure out what is causing this.
Some people try to tell us it's the application.
But we are the only company running this software that has this problem.
The strange part is that it only effects the order entry system.

Can anyone provide insight, ask the right question, or make the right comment that can help us?

tia,
Todd
MysteryJimbo
MysteryJimbo
Ten Centuries
Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)

Group: General Forum Members
Points: 1348 Visits: 15327
This may or may not be of help. We had a similar problem, on an older version of windows, and this was related to not enabling "background memory trimming" as the MS technician called it.

Lock Pages in Memory fixed it but as I recall there was a hotfix available. I've not found it yet but I think it was to do with large applications like SQL and Exchange.
Jeffrey Williams 3188
Jeffrey Williams 3188
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: 4463 Visits: 9829
How often are you updating statistics?
How often are you rebuilding your indexes?

Have you reviewed the 'slow' queries to see if they can be improved?

Just a few thoughts...

Jeffrey Williams
Problems are opportunities brilliantly disguised as insurmountable obstacles.

How to post questions to get better answers faster
Managing Transaction Logs

MysteryJimbo
MysteryJimbo
Ten Centuries
Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)

Group: General Forum Members
Points: 1348 Visits: 15327
Jeffrey Williams 3188 (12/31/2013)
How often are you updating statistics?
How often are you rebuilding your indexes?

Have you reviewed the 'slow' queries to see if they can be improved?

Just a few thoughts...


I would hope these guys checked these things

TC-416047 (12/31/2013)
that neither Microsoft nor three consulting forms can resolve.

TC-416047
TC-416047
Valued Member
Valued Member (54 reputation)Valued Member (54 reputation)Valued Member (54 reputation)Valued Member (54 reputation)Valued Member (54 reputation)Valued Member (54 reputation)Valued Member (54 reputation)Valued Member (54 reputation)

Group: General Forum Members
Points: 54 Visits: 213
Jeffrey Williams 3188 (12/31/2013)
How often are you updating statistics?
How often are you rebuilding your indexes?

Have you reviewed the 'slow' queries to see if they can be improved?

Just a few thoughts...


We update statistics every evening.
We rebuild indexes every evening if fragmentation > 30%.

The queries aren't slow except when they are.
Yes....... I'm from Oklahoma where the fork in the road is taken.Smile))

Thank you for your questions. Your response is sincerely appreciated.
TC-416047
TC-416047
Valued Member
Valued Member (54 reputation)Valued Member (54 reputation)Valued Member (54 reputation)Valued Member (54 reputation)Valued Member (54 reputation)Valued Member (54 reputation)Valued Member (54 reputation)Valued Member (54 reputation)

Group: General Forum Members
Points: 54 Visits: 213
MysteryJimbo (12/31/2013)
This may or may not be of help. We had a similar problem, on an older version of windows, and this was related to not enabling "background memory trimming" as the MS technician called it.

Lock Pages in Memory fixed it but as I recall there was a hotfix available. I've not found it yet but I think it was to do with large applications like SQL and Exchange.



I'm not sure what to do with this yet but thank you for responding.
We'll certainly look into this.
MysteryJimbo
MysteryJimbo
Ten Centuries
Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)

Group: General Forum Members
Points: 1348 Visits: 15327
Here's a couple of articles for you to look into it.

http://bradmcgehee.com/2011/03/10/do-you-enable-lock-pages-in-memory/

https://www.simple-talk.com/sql/database-administration/great-sql-server-debates-lock-pages-in-memory/

In my case, the only reason this option wasn't enabled was based on a MS article at the time saying it was no longer required. As it turns out it was.

FWIW the SQLCAT team now recommend this be enabled as standard. If you do need to enable this setting, ensure you set you MAX Server memory to allow for enough OS space and any other over heads the system has.
ChrisM@Work
ChrisM@Work
SSCrazy Eights
SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)

Group: General Forum Members
Points: 8970 Visits: 19020
Grab actual query plans for whatever query is responsible for "Existing orders with 200 lines take 10 minutes to retrieve."
One plan from when response is fast, one plan from when response is slow. Save the plans as .sqlplan files and post here.

“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Exploring Recursive CTEs by Example Dwain Camps
Greg Edwards-268690
Greg Edwards-268690
SSC Eights!
SSC Eights! (817 reputation)SSC Eights! (817 reputation)SSC Eights! (817 reputation)SSC Eights! (817 reputation)SSC Eights! (817 reputation)SSC Eights! (817 reputation)SSC Eights! (817 reputation)SSC Eights! (817 reputation)

Group: General Forum Members
Points: 817 Visits: 8259
MysteryJimbo (1/2/2014)
Here's a couple of articles for you to look into it.

http://bradmcgehee.com/2011/03/10/do-you-enable-lock-pages-in-memory/

https://www.simple-talk.com/sql/database-administration/great-sql-server-debates-lock-pages-in-memory/

In my case, the only reason this option wasn't enabled was based on a MS article at the time saying it was no longer required. As it turns out it was.

FWIW the SQLCAT team now recommend this be enabled as standard. If you do need to enable this setting, ensure you set you MAX Server memory to allow for enough OS space and any other over heads the system has.


Lock Pages is a good start.
If this truly impacts only Sales Orders in your ERP, has anything come up in a trace when you run a query?
And any unusual disk activity at this time?
Restarting clearing up the issue would tend to be a memory issue.
Only SQL on the machine, no SSAS, correct?
TC-416047
TC-416047
Valued Member
Valued Member (54 reputation)Valued Member (54 reputation)Valued Member (54 reputation)Valued Member (54 reputation)Valued Member (54 reputation)Valued Member (54 reputation)Valued Member (54 reputation)Valued Member (54 reputation)

Group: General Forum Members
Points: 54 Visits: 213
After reading these links this sounds promising.

Thank you both (Greg and MysteryJimbo).

The next occurrence of the problem should be somewhere around the 15th +/- a day or two.
We will have a chance to test this settings impact.
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