Indexes in SQL Server 2008
As many experienced database developers know, table indexes are essential for optimal performance when working with large sets of data. In fact, a program that retrieves data from a large database can be ground to a halt if no database indexing is done. Thankfully, working with indexes are somewhat easier in SQL Server 2008 than they were in SQL Server 2000. First, you'll want to analyze your database tables and any commonly used SELECT queries that are called by your program. One thing to keep in mind about indexes is that too many of them create overhead that will slow down UPDATE, INSERT, and DELETE statements. So - you want to be careful about how many indexes to create per table. Some of the best practices with a T-SQL index are the following.
Consult the Database Engine Tuning Advisor. This is an analytical program that comes with SQL Server 2008. Save some of your program's more commonly used SELECT queries as .sql files. Then run those .sql files against your database via Database Engine Tuning Advisor. It'll come up with some pretty good index and stats suggestions. It's wise to listen to what it suggests in many cases (although it'll often suggest too many indexes).
In your SELECT queries, analyze your WHERE clause and JOIN statements. The more commonly used fields in those two places deserve to be indexed. These should be in non-clustered indexes.
Make sure that your primary keys are indexed. Every table should have a unique primary key. SQL Server 2005/2008 will often generate indexes for those primary keys. In the event that those indexes are missing, create a clustered index on all primary key fields.
CTRL+L is your friend. When designing a query through SQL Server Management Studio, check it's execution plan. A lot of times this will show you a spot in your query that's really bottlenecking the overall execution of the command.
This should serve as guidance for index building.There are a lot of optimization options when it comes to SQL indexes, but I believe that these 4 tips will always lead you on the right path. If anybody has any other index strategies, I'd love to hear it in the comments section.
Tags: Database, database engine tuning advisor, dba, delete, index, insert, join, management studio, programming, queries, select, sql 2005, sql 2008, t-sql, update
Comments
Got something to say?