Master the basics. The better you understand how SQL Server works internally, the better you will be able to optimize it. Any attempt at optimization without knowing what is going on under the hood, is like trying to optimize the operation of a nuclear power plant without any understanding of nuclear power.
*****
Read the book, Inside SQL Server 7.0 or Inside SQL Server 2000 by Kalen Delaney. These are the best books on the market on how SQL Server works internally.
*****
Performance tuning and optimization is not something that should be left to the end of a project, or after you have gone into production and discovered performance problems, it is something that needs to be considered up front when the project begins. In fact, you should consider performance tuning during the earliest requirements gathering process. Find out from users what they expect in terms of performance. Once you know this, your logical and physical design of your application can incorporate this information. Updated 12-29-2000
*****
When performance tuning SQL Server, make only one change at a time, then check to see if the change bought you anything. This assumes you have already some benchmark on which to compare the results after your change. If you don’t have good before and after records, then you won’t be able to tell if the change you made was helpful.
Record your performance tuning experiments in a log. This forces you to do your experimenting a little more carefully and helps to prevent yourself from repeating mistakes.
*****
Unless the performance issue is obvious, the best places to starting looking for performance issues are the following areas, and in this order:
-
The Application (includes the client and server code)
-
Database Design (logical and physical)
-
SQL Server (configuration settings)
-
Hardware (bottlenecks, bad design, bad configurations, etc.)
This may seem unintuitive as first glance. Most DBAs want to start with the hardware. But in the real world, the above order indicates the greatest likelihood of where performance problems may lay. While all these areas can provide performance improvement, the first two often provide the greatest room for performance improvements.
*****
In some cases, finding performance problems is made more difficult because two or more performance problems are conspiring together against you. This is a tough problem to work out. The secret to dealing with this is to try only one fix at a time, testing performance before and after each change. Eventually, you will find all your performance problems.
*****
When attempting to locate a performance issue, find out if the problem is consistent, or if it varies over time. If it does vary over time, the issue may have to do with how busy the overall server is. For example, perhaps there are no performance issues before 8:00AM or after 4:00PM, but between these hours the number of users on the system may max out the server’s ability to function quickly. Knowing this can be of great benefit when locating performance issues.
*****
Ideally, you should tune your SQL Servers to meet peak usage levels, not average usage levels. For example, if you know that between 10:00 AM and 11:00 AM every day that usage peaks and causes a bottleneck on the server, you should act to remove that bottleneck, even though it only lasts for a short time. You don’t want your users to suffer during that very busy hour. Design your server capacities with peak usage in mind.
*****
Don’t overtune your application or SQL Server. For example, a particular index choice might produce a result in one second, while another index choice might product a result in about two seconds. At first glance, it might appear that the query that produces the result in one second is better, and you may be right. But consider this, what if two seconds is adequate from the user’s point of view, and getting the results in only one second is not important. And what if adding using the one second index creates an index that is five megabytes larger than the second index, and it also slightly degrades INSERTS, UPDATES, and DELETES more than the two second index. Sure, these are a lot of what-ifs, but this is the real world. There are often situations where overtuning your application or SQL Server is a waste of time. Always focus on what is critical. Don’t performance tune just for the sake of it. Added 8-28-2000
*****
Always test any tip you find on this site, and anywhere else for that matter, on non-production SQL Servers first. Not every performance tuning tip is designed for every SQL Server environment. Ideally, you should have a controlled test environment you can use to perform testing.
]]>