We are running SQL 2008 Enterprise server with ASP Persistent sessions and our main data DB on the same 2008 Enterprise server. We're currently experiencing random freezes where all websites and applications accessing the DB become un-responsive.
Looking at the activity monitor in SQL Server Management Studio there is nothing out of the ordinary whilst the slow-downs occur. However, there are huge spikes randomly. The "Waiting Tasks" leap from between 0-10 as they normally are to 80,000 and the DB I/O leaps from the 0.3-1MB/sec it normally runs at up to 80MB/sec.
I think that the huge spikes may be due to the ASPState table doing a Commit. I managed to catch it once and it had huge IO so I don't think that this is part of the problem.
Are there any tests I can run to find what is causing the slow down? Our websites use a lot of ajax (one request every three seconds per customer) and we have a ~200 users on the websites concurrently so using the profiler is extremely difficult.