What is the ideal indexing strategy for SQL Server?

Question

My friend and I are working on a new project using SQL Server. In SQL, which I did past projects, I always put indexes in any field that is used in JOIN or WHERE.

My friend added them only when they needed performance. The idea is that there are costs to maintain the indices, and you want to be sure that you pay this cost. In fairness, it is worth saying that some of the queries will not be used often, and also that some tables will be added more actively than others.

So I'm looking for advice on what is “best practice” for database indexes. What works well for you?

Answer 1

My personal preferences relate to the proactive approach : based on your queries, add indexes where necessary. As you say, in fields that participate in JOINs or WHERE. Each index speeds up read requests, but slows down the record (since each record needs to be updated with an index). Therefore, other solutions (data storage, replication ...) may be required for an intensive recording table.

Another approach, adding only indexes in which their performance is required, is valid only if you are actively monitoring, but even then has several drawbacks :

• You will need to add the index to the table in which there are performance problems. While the index is being added, your table is locked - and this is a heavily used table!
• Often when testing, test data is several orders of magnitude smaller than the actual data in the application. Bottlenecks may be overlooked.

Answer 2

I would try to follow these recommendations:

• There is always a good primary / clustered key - usually INT IDENTITY - to avoid a GUID or large PK / CK compound. A well-thought out PK / CK will go a long way to help overall performance. To fully understand why, read all the Kimberly Tripp cluster blog posts .

• always index all columns of a foreign key - separately or together with other columns that make sense; it helps with JOIN performance

• other than that: less ! Add indexes only if you absolutely need to - keep track of your system, view your data load, see what performance is, fine-tune it, measure it again. If the index helps, save it; if the index is not used, drop it

• use the DMV at hand ( missing DMV index and unused DMV indexes ) to get an idea of ​​which indexes can help and which ones are not used at all ...

Answer 3

You want to place them only in those columns or groups of columns that have many queries on them. You can get a lot of statistics from SQL Server to find out what queries are running against your tables, and SQL Server will even offer indexes that you don’t have.

Here is a good link with useful information and other links to good information. SQL Server Index Checklist and Tips

Answer 4

When developing indexes, follow these guidelines:

• Use indexes for tables with multiple rows, in columns used in the WHERE clause, or in a join table and columns used in ORDER BY and GROUP BY.
• Avoid frequently used indexes on frequently updated columns. In addition, avoid having many indexes on a table that is frequently updated. Otherwise, you unnecessarily increase the time it takes to insert and update your queries. To improve performance, minimize the overall width of indexed columns.
• Use clustered and nonclustered indexes appropriately. Understand each goal and choose the right type for your scenario.
• Use the coverage index to reduce the query execution time of frequently used applications. A coverage index is a non-clustered index that has all the columns that are included in the WHERE clause and in the query column selection.

according to

http://msdn.microsoft.com/en-us/library/ff650692.aspx

Answer 5

There is no easy answer to your question. It all comes down to using tables. Monitoring usage of the table will tell you what to do.

Answer 6

select * from sys.dm_db_missing_index_details

learn your dynamic management views

and then go and use this sproc from this url http://www.sqlservercentral.com/scripts/Index+Management/63937/

also .. what homedude says about “covered indexes”, make sure you understand the difference between covered indexes (SQL 2000) and indexes with the INCLUDE clause (SQL 2005 and newer)

Answer 7

The index is best suited for a value as unique as possible . For example, useless places the index on a column, where 50% of this column is the value "A", and another 50% of the column is the value "B".

Thus, the table will scan at least 50% of the records before choosing the correct values.

So, the best practice is to put the index in the most unique columns and only those columns that are used to select queries using.

example: if you need to create a selection for a typical "login", you must put the index in the "Username" column, as you will see that the username is unique.