articles >> performance tuning >> Best SQL Server Performance Tuning Tips ...

Best SQL Server Performance Tuning Tips

By : Brad McGehee Jul 31, 2003

This website offers hundreds of different ways to tune SQL Server for optimum performance. But as you read over the many tips, you might be asking yourself, which tips offer the most performance gains for the effort expended. As you can imagine, it is not always easy to know which tips might help boost your server’s performance the most, as each database and application is different.

But there are some basic performance tips that generally can be considered critical to the performance of all database applications, and they are described in this article. You have probably seen most of them before if you have been a DBA or Transact-SQL developer for long. But being familiar with them is not the same as actually applying them.

If your goal is to maximize the performance of your SQL Server, before trying any of the advanced performance tips I list in my website, start with these basic tips. Once you have implemented all of these, then are ready to starting working on the more advanced tips, assuming that you still need to tune your server, database, or application for performance.

Many of you may be familiar with what is commonly called the Pareto Principle (80/20 Rule). I think this same idea applies to SQL Server performance tuning. It is probably fairly accurate to say that 80% of performance gains can be had for about 20% of your effort, while it will take about 80% of your time and effort to get that last 20% of performance out of SQL Server. Because of this, I would first focus your efforts on that first 80%. By implementing the following tips (which are already listed on this website), the first 80% of your SQL Server performance gains will be attained.

The tips listed below are from my website, and most of them have been abbreviated here for space. If you want to find out more about a particular tip, or related tips, then click on the "More" after the tip. This will bring you to the web page that has the full text of the tip, along with additional ideas for boosting your SQL Server's performance.

 

Database Design

Unfortunately, for the most part, these tips apply to databases that haven’t been designed yet. So if you are beginning a database-related project, this is the time when you should begin to consider performance issues. Once your database has been put into production, it often takes a tremendous amount of effort to make changes. But in some cases, you will have no choice but to make database design changes after the database is in production if you need to boost performance. The following tips are critical for getting the best performance out of SQL Server.

• Bad logical database design results in bad physical database design, and generally results in poor database performance. So, if it is your responsibility to design a database from scratch, be sure you take the necessary time and effort to get the logical database design right. More
  
  
• Following standard database normalization recommendations when designing OLTP databases can greatly maximize a database's performance. More
  
  
• If normalizing your OLTP database forces you to create queries with many multiple joins (4 or more), you may want to consider denormalizing some of the tables in order to reduce the number of required joins. More
  
  
• When you are first designing your application, don't automatically assume you have to de-normalize your data. Your first goal in the design process should be to normalize your data. Next, you can test your design with realistic data and transactions. At this point if you see that de-normalization will help, then by all means do so. More
  
  
• Always specify the narrowest columns you can. The narrower the column, the less amount of data SQL Server has to store, and the faster SQL Server is able to read and write data. More
  
  
• Don't try to perform both OLTP and OLAP transactions within the same database. If you do, OLTP transactions will slow down OLAP transactions, and OLAP transactions will slow down OLTP transactions. OLTP and OLAP transactions should be considered mutually exclusive transactions, and ideally, should be relegated to their own databases. More