Note: This blog post is from 2008. Some content may be outdated--though not necessarily. Same with links and subsequent comments from myself or others. Corrections are welcome, in the comments. And I may revise the content as necessary.Need to solve problems with SQL Server performance? Here are several resources that can help:
- How to troubleshoot SQL Server performance issues
- How to troubleshoot application performance issues
- HOW TO: Troubleshoot Application Performance with SQL Server
- How to troubleshoot slow-running queries on SQL Server 7.0 or on later versions
- How to troubleshoot the performance of ad-hoc queries
- Troubleshooting stored procedure recompilation
- Understanding and resolving SQL Server 7.0 or 2000 blocking problems
- How to monitor blocking in SQL Server 2005 and in SQL Server 2000
- How to resolve blocking problems that are caused by lock escalation in SQL Server
- How to capture error messages that are sent to clients from an instance of SQL Server
- How to determine proper SQL Server configuration settings
Some of these offer knowledge and understanding, others offer specific steps to follow. Most offer links to still other resources (including often specific entries in Books Online).
Note that some may be written more to those still running SQL Server 7 (less likely) or SQL Server 2000 (not so unlikely), though many do cover SQL Server 2005 as well. Just keep this in mind while reading, both if some step doesn't seem to follow, and also in case it may be that some setting that suits one release may not suit another. In fact, some of the resources specifically discuss how things have changed in later releases, and how in fact some settings or techniques for older releases may be very different for later ones. All this just calls for discernment while you read.
These are all in addition to a couple of entries I wrote back in April (starting here) on some other advanced tools and techniques for diagnosing SQL Server problems.
Sometimes CF gets blamed for problems when in fact the problem is in the DBMS--and it's not always a problem due to the SQL being sent from CF. Sometimes the same code can run very differently one time than another. In that case, you really need to understand why this is happening. I hope the resources above may help you. If you ever want direct assistance, this is one of the things I help people with in my available consulting.