<p>We're in the process of upgrading one of our SQL Server instances from 2000 to 2005. I installed the performance dashboard (<a href="http://www.microsoft.com/downloads/details.aspx?FamilyId=1d3a4a0d-7e0c-4730-8204-e419218c1efc&amp;displaylang=en" rel="noreferrer">http://www.microsoft.com/downloads/details.aspx?FamilyId=1d3a4a0d-7e0c-4730-8204-e419218c1efc&amp;displaylang=en</a>) 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.</p>

<p>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.</p>

[{'answer_id': 3981, 'author': 'GateKiller', 'author_id': 383, 'author_profile': 'https://Stackoverflow.com/users/383', 'pm_score': 0, 'selected': False, 'text': "<p>Your best researching the most common type of queries that happen on your database and creating indexes based on that research.</p>\n\n<p>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.</p>\n\n<p>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.</p>\n"}, {'answer_id': 3989, 'author': 'Stu', 'author_id': 414, 'author_profile': 'https://Stackoverflow.com/users/414', 'pm_score': 2, 'selected': False, 'text': "<p>The advice you got is right. Try them all, one by one. </p>\n\n<p>There is NO substitute for testing when it comes to performance. Unless you prove it, you haven't done anything.</p>\n"}, {'answer_id': 5907, 'author': 'SQLMenace', 'author_id': 740, 'author_profile': 'https://Stackoverflow.com/users/740', 'pm_score': 3, 'selected': True, 'text': "<p>First thing to be aware of:</p>\n\n<p>When you upgrade from 2000 to 2005 (by using detach and attach) make sure that you:</p>\n\n<ol>\n<li>Set compability to 90</li>\n<li>Rebuild the indexes</li>\n<li>Run update statistics with full scan</li>\n</ol>\n\n<p>If you don't do this you will get suboptimal plans.</p>\n\n<p>IF the table is mostly write you want as few indexes as possible\nIF the table is used for a lot of read queries you have to make sure that the WHERE clause is covered by indexes.</p>\n"}]
['https://Stackoverflow.com/questions/3975', 'https://Stackoverflow.com', 'https://Stackoverflow.com/users/162/']