Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

DB server comes to a crawl every two weeks Expand / Collapse
Author
Message
Posted Tuesday, December 31, 2013 7:43 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Monday, September 15, 2014 10:32 AM
Points: 54, Visits: 208
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

Post #1526748
Posted Tuesday, December 31, 2013 8:37 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Monday, August 18, 2014 3:04 AM
Points: 1,331, Visits: 15,269
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.

Post #1526765
Posted Tuesday, December 31, 2013 2:19 PM
SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Thursday, October 2, 2014 12:09 PM
Points: 4,358, Visits: 9,538
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 opportunites brilliantly disguised as insurmountable obstacles.

How to post questions to get better answers faster
Managing Transaction Logs
Post #1526851
Posted Tuesday, December 31, 2013 2:33 PM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Monday, August 18, 2014 3:04 AM
Points: 1,331, Visits: 15,269
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.
Post #1526853
Posted Tuesday, December 31, 2013 2:34 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Monday, September 15, 2014 10:32 AM
Points: 54, Visits: 208
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.:)))

Thank you for your questions. Your response is sincerely appreciated.
Post #1526854
Posted Tuesday, December 31, 2013 2:36 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Monday, September 15, 2014 10:32 AM
Points: 54, Visits: 208
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.
Post #1526855
Posted Thursday, January 2, 2014 4:12 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Monday, August 18, 2014 3:04 AM
Points: 1,331, Visits: 15,269
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.
Post #1527030
Posted Thursday, January 2, 2014 5:05 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 4:02 AM
Points: 6,778, Visits: 13,974
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
Post #1527042
Posted Friday, January 3, 2014 6:44 AM


Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: 2 days ago @ 8:10 PM
Points: 672, Visits: 6,756
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?
Post #1527500
Posted Friday, January 3, 2014 9:23 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Monday, September 15, 2014 10:32 AM
Points: 54, Visits: 208
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.
Post #1527615
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse