Error in the Application Event Log:
Event Type: Warning
Event Source: W3SVC-WPEvent
Event ID: 2262
Time: 07:31:36 AM
Description:ISAPI 'C:\WINDOWS\Microsoft.NET\Framework64\v2.0.50727\aspnet_isapi.dll' reported itself as unhealthy for the following reason: 'Deadlock detected'.
Checking the SQL server revealed over 30 deadlocks on one content database. Every site in the affected content database was inaccessable. The rest of the sites on other content databases were fine.
While many different reasons could cause the Deadlock, such as third party applications running SQL queries on the content databases with the "No Lock", our problem seemed to be something else.
Further investigation showed that the SQL Server was not configured optimally. The server is 64bit SQL Server 2005 Enterprise. Things we checked and changed:
Make sure that AutoGrow on the Content Databases are set to more than 500MB. As well as the Log files AutoGrow is set to 500MB. IF the log file size increases over 10GB in one day then set the initial size to 10GB.
Max degree of Parallelism, check this blog: http://blogs.msdn.com/grahamk/archive/2009/06/15/troubleshooting-sql-server-deadlocks-and-performance-when-running-sharepoint.aspx
We set our servers Max Degree of Parallelism to 1.
Our server has 16GB Ram. SQL Max memory was set to 14GB, which left 2 GB for the OS and other applications. The number of worker threads in a 64bit environment is 576. Each worker thread requires 2MB of RAM. That equals 1GB of RAM just for the worker threads. AS I understand, if there is not enough memory available for the worker threads, then memory contention occurs.
This could create deadlocks, as some of the worker threads are waiting for the other worker threads to complete.
So, leave 2GB for the OS, 2GB for the worker threads, and at 2 GB for other Applications. So means you set the maximum memory for SQL to 10GB on a 16GB box in a 64bit environment.
All these changes definately increased our server performance.