Tell me more ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.
up vote 12 down vote favorite
3

I'm a long time DBA (12 years in SQL). I'm running SQL Server 2008 Enterprise in a Win 2008 clustered environment. 128gb memory and 32cpu in each cluster node. Not wimpy.

I've got the weirdest performance issue I've seen to date, and its baffling me why it would occur...looking for ideas.

Occasionally, maybe 2-3 times per week, the performance of queries made by the application to the database will come to a crawl. Stored procedures that normally execute in 500ms, suddenly are up around 50 seconds. Forget about saving data...application .net code will time out before the data even makes the trip.

First time this happened was about 4 weeks ago. I kicked off a profiler so I could see if there were excessive queries or something, and didn't see anything strange. So, I copied one of the batches from the profile and executed it in SSMS. From that moment, the performance was perfect again. Application was snappy and no issues.

Next time it happened, I remembered that just running a batch in ssms seemed to "fix" the issue. So I took some benchmarks first and did it again. Same thing. Running ANY batch in SSMS relieves whatever is happening. I have reproduced this about 15-18 times over the past 4 weeks: The application comes to a crawl, and then I run any query in ssms, and all is well.

This is obviously a problem. In order to find out what is running or going on, I have to run a batch--which makes the problem stop. SO the act of trying to observe the issue resolves it. GAH!

Note: There are two clustered instances running on the same node. Only one of the two experiences this issue. And they are roughly the same size in terms of disk space and activity.

Anyone ever seen this?

EDIT:

Re: levorf's answer: The cpu and memory are not a problem. I know that for sure. The cpu capacity is way above what the application needs. And memory too. The instance is limited to 40gb, but the allocated memory is hovering right around 30gb--it has 10gb more to expand into, if it needs it.

There's only one application that uses the db. However, we log (on a different instance and db) user activity, including the start and end time of every page that is called. This gives me an indication of where to look for bad sql. In this case however, there doesnt appear to be a pattern. There's also not a pattern on time of day, nor is there correlation to any running agent jobs.

Also, there have been no changes to this application in several months--so its not like we introduced something new and then started having issues.

There's no pattern to where performance is poor. It is in all parts of the application (i.e., navigation, saving different kinds of data, lookup data, etc.).

I guess the question I'm stuck on is what would really be that different that running a batch via SSMS would "unclog" whatever is coming from the application server. <--by the way, I know that "unclog" isn't the right term, and I feel like I sound like my grandmother when I say it...I just can't think of how else to describe it.

Can't check waits because querying the server resolves the problem. Anything that WAS a problem before I query, no longer is a problem after I query. Same with locking.

share|improve this question
Is there any CLR code running when the problem arrises? – Pete Carter Oct 31 '12 at 15:43
No. No CLR code in the application. – Jason Stewart Oct 31 '12 at 16:14
1  
Ok, have you considered outside influence, such as firewall or antivirus. If defiantly internal to SQL, set up an Extended Event session that you can then query after the event. It's very lightweight and you should be able to leave it running, unlike a traditional trace – Pete Carter Oct 31 '12 at 20:33
1  
I would check the error log for any stuck scheduler messages and check the connectivity ring buffer too. Please confirm the exact build of SQL Server you are using too (might be an already-fixed issue). – Paul White Oct 31 '12 at 22:27
1  
sys.dm_os_wait_stats accumulates stats from the last restart, or deliberate clear via DBCC SQLPERF. If you periodically log the DMV output (example), you can review after the event occurs to see if anything was elevated during the problem. – Mark Storey-Smith Nov 1 '12 at 0:46

migrated from stackoverflow.com Oct 31 '12 at 15:20

2 Answers

up vote 1 down vote

Not quite giving you an answer, but it's hard to figure things without more specific information. Specially this kind. Might help you come with any idea.

Have you tried to identify any abnormal behavior while the performance issue occurs? For example:

  • Which application(s) triggers the procedures that slows down?
  • Which operation(s) are they trying to attempt?
  • Anything suspicious that would do something wrong or an expensive query that might conflict or highly increase database usage?
  • Are there any threads waiting for resources?
  • Anything locking something that shouldn't? Hows the application's and database health (CPU/memory usage)?

If the hardware are OK, I would start thinking around those questions. From a developer point of view.

share|improve this answer
1  
> In order to find out what is running or going on, I have to run a batch--which makes the problem stop. SO the act of trying to observe the issue resolves it. – GSerg Oct 31 '12 at 15:54
He responded as an answer which has been rolled into the question as more detail (too long for comments really) – jcolebrand♦ Nov 1 '12 at 20:13
up vote 1 down vote

I had the exact same issue and it was tempdb logical latch contention in TempDB (pagelatch_she as opposed pageiolatch which would indicate physical contention) I upped the physical tempdb files to 8 in a 32 core system and it took care of everything. What are your wait stats? Have you used extended events to see what wait stat you're waiting on?

share|improve this answer
I'm really curious how many tempdb files you have, esp with the number of cores you currently have in production. Can you let us know? – Ali Razeghi Nov 2 '12 at 1:08

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.