How do I know which SQL Server 2005 index recommendations to implement, if any?

How do I know which SQL Server 2005 index recommendations to implement, if any?

"We're in the process of upgrading one of our SQL Server instances from 2000 to 2005. I installed the performance dashboard (http://www.microsoft.com/downloads/details.aspx?FamilyId=1d3a4a0d-7e0c-4730-8204-e419218c1efc&displaylang=en) for access to some high level reporting. One of the reports shows missing (recommended) indexes. I think it's based on some system view that is maintained by the query optimizer.

My question is what is the best way to determine when to take an index recommendation. I know that it doesn't make sense to apply all of the optimizer's suggestions. I see a lot of advice that basically says to try the index and to keep it if performance improves and to drop it if performances degrades or stays the same. I wondering if there is a better way to make the decision and what best practices exist on this subject."

Asked by: Guest | Views: 397

Total answers/comments: 4

Comments display order:

Guest [Entry]

"First thing to be aware of:

When you upgrade from 2000 to 2005 (by using detach and attach) make sure that you:

Set compability to 90
Rebuild the indexes
Run update statistics with full scan

If you don't do this you will get suboptimal plans.

IF the table is mostly write you want as few indexes as possible
IF the table is used for a lot of read queries you have to make sure that the WHERE clause is covered by indexes."

Guest [Entry]

"The advice you got is right. Try them all, one by one.

There is NO substitute for testing when it comes to performance. Unless you prove it, you haven't done anything."

Guest [Entry]

"Your best researching the most common type of queries that happen on your database and creating indexes based on that research.

For example, if there is a table which stores website hits, which is written to very very often but hardly even read from. Then don't index the table in away.

If how ever you have a list of users which is access more often than is written to, then I would firstly create a clustered index on the column that is access the most, usually the primary key. I would then create an index on commonly search columns, and those which are use in order by clauses."

Yhddvk [Entry]

lipitor 10mg sale <a href="https://lipiws.top/">order atorvastatin 20mg for sale</a> buy lipitor 10mg without prescription