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?
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.