SQL Server performance is always one of the most challenging subjects. Hard drives are getting cheaper and cheaper, and data is growing exponentially. With this new pattern, we all have a significant challenge. In the database world, though, we now have new problems affecting performance. Here are a few issues that keep coming up in various systems in the industry:

• A query which was running quickly now takes too long to run
• Our report is now running very slow
• During data import everything gets slow
• Every day during a specific period we are facing many deadlocks
• Queries are continuously responding with time-outs
• SELECT queries are slower when INSERT, UPDATE and DELETE are happening

Throughout the years Pinal Dave has seen and solved many similar issues. He has seen many bad practices implemented in SQL Server at the server and database level. There are hundreds of tips that one can practice to keep a database at optimal performance.

The purpose of this whitepaper is to highlight a few best practices that can give maximum benefits to the SQL Server system. Out of thousands of best practices, Pinal has selected the seven best practices related to indexes. The whitepaper describes how to drop unused indexes, create missing indexes, remove duplicate indexes, the significance of clustered indexes, column store indexes for data warehousing, cardinality estimation, and additional indexing best practices.