We're having a significant performance issue with our production DNN system and are reaching out to the community for some help. Essentially, from time to time, our SQL Server just spikes at 100% killing performance on all portals.  It appears to be related to GetTabs or the Event Log Purge Buffer but we're not completely sure if this is the culprit.

Let me start with the hardware particulars:

1. Single DNN instance (code) sitting on a Dell server setup as file sharing only.
   1. Dell PowerEdge Server: Dual P4 2.86 Mhz Processors, 1GB Ram, RAID 5 – 5 x 73GB SCSI-320 Drives 
2. Two Web Servers (pointing to the single code source) setup in a Web Farm
   1. (2) Dell PowerEdge Servers: P4 2.86, 1GB Ram
3. Single SQL Server serving the web servers
   1. Dell PowerEdge Server: Dual P4 3.0 Xeon, 4GB Ram, RAID 1 - 2 x 70GB SCSI (OS, Apps), RAID 5 – 3 x 135 GB SCSI (Databases)  Processors are logically setup as four separate processors
4. Everything sites behind a load balancer
5. All owned equipment in a dedicated rack (co-located)

DNN Information

1. Version 4.0.2
2. 111 Portals
3. 8126 Tabs (across all portals)
4. Core DNN and Purchased Modules
5. All business queries performed in custom queries hit web services from yet another server

Traffic Information

1. Plenty of bandwidth coming in (we push close to 10MB/sec at peak)
2. Avg 50,000 PageViews per day (1.5 Million per month)
3. Avg 7MB/Sec throughput, Peak at 10MB/second
4. 100 MB Switched Network into NOC then 3 OC-48 connections from NOC.

Behavior:

1. In what appears to be random events, all four SQL processors will spike within a second or two to 100%.  
2. Users will experience one of the following issues:
   1. Slow load time
   2. No load (typing in URL shows progress bar but no page loads)
   3. Page loads without skin (shows base blue skin with error saying it can’t find the portal skin)
   4. Page loads but shows only a Server Application Error test message
   5. Spike will continue for as little a one minute or for as long as two hours.
   6. Spike will resolve on it’s own
3. We can force a resolution by performing one or more of the following
   1. Removing one web server from the farm (we have two in the farm)
   2. Turning off one of the DNN Web Sites within IIS
   3. Turning a site back on occasionally re-spikes the SQL Server

Tests completed so far (prior to or during spikes):

1. SQL Profiler, shows no abnormal queries….many GetTabs queries
2. SQL Processes show GetTabs waiting when spike occurs
3. Killing specific spids does not improve performance
4. Removing one of the web applications (or servers) will resolve the SQL spike.

So, we’re pulling our hair out at this point and looking at al levels of the applications.  We’ve turned off most logging, almost all of the DNN schedule, turned off Event Buffer Loads, etc.  While we keep focusing on hardware for resolution it does not appear to be a hardware limit since 99% of the time the hardware handles the load with the SQL Server at 20% and each of the web servers at < 10% processor and memory.

Does anyone have specific suggestions or tests or scripts we can perform to determine what exactly is causing the spike?  Any an all help would be appreciated. 

You said you can see that GetTabs is waiting. Do you mean it is blocked by another SPID? If so, find out the query being run by the SPID that is blocking:

1. Run sp_who2
2. Analyze the BlkBy (blocked by) column to find the SPID that is the root cause of the block
3. Run DBCC inputbuffer(blocking SPID) to get the command it was running

You could try rebuilding or defragmenting the indexes in your database. Badly performing indexes might account for certain queries not being performed efficiently. See this article:

http://www.mssqlcity.com/Articles/Adm/index_fragmentation.htm

Note the warning about using DBCC DBREINDEX :

During rebuilding a clustered index, an exclusive table lock is put on the table, preventing any table access by your users, and during rebuilding a nonclustered index a shared table lock is put on the table, preventing all but SELECT operations to be performed on it, you should schedule DBCC DBREINDEX statement during CPU idle time and slow production periods.