I wish I was able to provide you with a comprehensive list of steps that you could follow that would optimize your SQL Server for performance 100%. Unfortunately, life is not so easy. SQL Server performance is a complex interchange among many variables, many of which you have little or no control. For example, here are some of the many variables that affect SQL Server Performance (not comprehensive):
-
SQL Server (the program code itself)
-
SQL Server Configuration
-
The SQL Server Application (the Transact-SQL running on the server)
-
The Client and Server Application Code (not Transact-SQL)
-
Middleware (MTS and MSMQ)
-
Database Design
-
Client and Server Hardware
-
Client and Server Operating System
-
Network Hardware and Bandwidth
-
Client Application
-
The Workload
-
Number of Clients
-
Client Usage Patterns
This list goes on and on. As a DBA or developer, all you can do is attempt to isolate the factors you can control, and optimize performance from there.
Another factor that makes performance tuning difficult is that a recommended performance tuning technique may help in one situation, but the same suggestion may actually slow down performance in another situation. Many times performance tuning is a series of compromises, where you, as the DBA or developer, have to decide which factor is the most important.
SQL Server Performance tuning and optimization is a learned skill, just as any “art” is. The better your foundation in the basics and the more experience you have, the better you will be at your job.
Here are some general introductory tips to help you become better at performance tuning SQL Server:
*****
On this website you will see hundreds of tips for performance tuning Microsoft SQL Server. In effect, they all boil down into these three goals:
-
Reduce disk I/O
-
Reduce CPU utilization
-
Reduce network traffic
Another way to say this is that you want to always move as little data as you can between the SQL Server and the client, and only move it once.
Whatever approach you take to performance tuning and optimizing SQL Server, your overall goals will always be the same. The more you can reduce each of the above, the faster your SQL Server-based application will perform. Added 9-1-2000
*****
Many of the performance tuning and optimization tips on this website only produce very small performance gains. Taken by themselves, they are sometimes hardly worth the trouble. But, your goal should not be to make one simple performance tuning improvement, but many of them. It is only be implementing many different performance tuning and optimization tips that your application will become faster and more scalable. Each little performance boost adds up to a faster performing application. Added 10-26-2000
*****
Some applications have hundreds, if not thousands of queries, making it virtually impossible to try and tune each one by hand. Since you have limited time, focus your query tuning efforts on the queries that are run the most. You might think that a query than runs in .5 seconds may not need to be tuned. But what if that query has to be repeated 10,000 times in your application during an update process? Focus your efforts on those queries that most affect the overall performance of your application. Added 12-11-2000