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