Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

I have an IIS 6 site running on Windows 2003 Server x86 with MS SQL2005 Enterprise edition running ASP Classic (no choice). The site runs very fast with about 8000 page views per hour. All of my SQL tables are indexed and I have used the profiler to check my queries, the slowest of which is only about 10-15ms. I have autoshrink disabled, autogrow is set to 250mb, database is 2gb with 800mb of free space.

My problem is that every now and then the site will slow to a crawl for no reason. Pages that just have a simple 'connect to databse and increment a hit counter' work ok, but more SQL intensive pages that normally execute in about 60ms take 25,000ms to run. This happens for about 30 seconds and then goes away. I was having an issue with orphan recordsets and connections due to the way I was releasing them. I have fixed this up and the issue is much better, but I am still getting them.

Is there a way with permon, etc. to track when SQL Server or Windows closes these Orphan connections? At least if I can monitor the issue I will know if I am making progress or if I am even looking at the right things. Is there anything else I might be missing?

Thank you!

share|improve this question
    
I should probably mention: Running 16 worker threads on a server with 16 'cores', 6GB of RAM. The server CPU usage never exceeds 5%, plenty of available memory, etc. When it slows down these values don't change. It is as though something is locked, maybe when SQL server releases orphans it freezes? –  Damian Nov 14 '11 at 17:53
    
I am experiencing the exact same symptoms. Our usage cases (and servers) are almost identical except I'm using IIS7.5. What is going on here?? How did you handle the orphan recordsets properly? Set = nothing for all of them at the end of execution? –  MikeMurko Nov 15 '11 at 2:01
add comment

1 Answer

Performance monitoring is really key. But even with some performance monitoring enabled you should really have a baseline of performance metrics from both IIS and SQL, covering CPU, Disk, Network and RAM.

Perfmon can be used to collect various counters to establishment a baseline of performance. Once this baseline is established you can then use it to identify bottlenecks, peaks and unexpected issues.

Your tempdb might be growing. A scheduled task might be running. The windows pagefile might be being resized. SQL might be swapping ram out to disk. A bottleneck of queries, updates, inserts, deletes might be occurring.

Any of these things, and more, can cause temporary performance bottlenecks like you are experiencing.

I'd really recommend running through these sql performance tuning articles and guides from Brent Ozar, an amazing SQL Guru/Master.

One other really handy sql monitoring tool is SQL Monitor by Red-Gate which encapsulates a lot of the manual processes Brent takes you through in an easy to use installable (although not free) product.

share|improve this answer
add comment

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.