index tuning

Hi,
I have a very large table that holds all the ledger transactions. Its the
number 1 table that gets hit when posting invoices, etc, etc. It started
with 5 indexes that the developers did. I have been running traces that are
fed to the index tuning wizzard and now over the last 3 months it has
generated about 20 new indexes, one is clustered. No significant performance
is noticed, rather the opposite. What do I do now? Please help,
Regards,
dimitris,
The index tuning wizard should be used as a guide, not as a tool for
creating your indexes. I would run a trace on this table over a typical
working day, and find the top 10 most intensive queries against this
table based on IO and CPU, and possibly duration.
Find out what the SARGs are for these queries and make sure you are
indexed for these SARGs. To do this check the WHERE clauses of the
queries. The clustered index should be placed on a narrow column and
chosen very carefully. Try to place it on a FK column, or a column where
range queries are performed, or one where there are frequent ORDER BY
clauses.
Test your new queries in a test environment, don't do this in
production. Benchmark your results so you can compare what works against
what doesn't.
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
dimitris wrote:
> Hi,
> I have a very large table that holds all the ledger transactions. Its the
> number 1 table that gets hit when posting invoices, etc, etc. It started
> with 5 indexes that the developers did. I have been running traces that are
> fed to the index tuning wizzard and now over the last 3 months it has
> generated about 20 new indexes, one is clustered. No significant performance
> is noticed, rather the opposite. What do I do now? Please help,
> Regards,
>
sql