A post in a troubleshooting forum on Linked In got me thinking about query optimization. The original poster asked about identifying and solving deadlock situations. Many great responses preceded mine, but it got me thinking about how I troubleshoot these problems. So I thought I would write it up over a series of posts.
Deadlocks are actually occurrences where two resources are waiting for each other to complete before continuing. While these do, indeed happen, most of the time people use the phrase to mean a long-running process is hogging one or more resources and preventing those resources from being utilized by other processes.
When the former occurs, I find the best approach to be finding a way to remove the reliance of the deadlocking resources. Removing the inter-dependence, the deadlock can no longer occur.
But I want to focus on the latter situation as it is the more common. When your queries start taking longer and longer to run there are a few simple steps you can take to regain the speed you saw when you were testing as a single user.
It would be easy to say optimize your indexes and queries, but that's not so easy to do. My first step is to leverage SQL Server to tell me what is going on and what it thinks I should do to be more efficient. SQL Server has a nice feature called the SQL Server Profiler. This allows you to record what is happening and analyze it later. By generating a profile on your server you get to see every query or stored procedure that is accessed, when it occurred and how long it took to complete. Coupled with the SQL Server Database Tuning Wizard, SQL Server can do some heavy lifting and recommend some areas to start your tuning such as missing or unused indexes.
One of the databases I administered was over 250GB and housed a couple thousand tables. There is no way I could go through each table and manually decide how to optimize the indexes. With the recommendations from the tuning wizard, I was able to concentrate on the bigger bang optimizations that should net immediate results. I was able to easily generate missing indexes and disable those that were not used. The best part was no guessing was involved. Because the tuning wizard's suggestions were based on real-world usage, gathered over several hours (at minimum), I knew that these indexes would have a positive impact.
I found that the tools offered by Idera were also very valuable. Idera's SQL Doctor is like a tuning wizard on steroids and helps get your SQL Server purring like a cheetah.
I will post further on this topic over the next few days; touching on areas such as "stored procedures vs ad hoc queries" and "is real-time processing required".
