Re: index tuning
From: Mark Allison (marka_at_no.tinned.meat.mvps.org)
Date: 07/14/04
- Next message: PO: "Exporting to textfiles"
- Previous message: dimitris: "index tuning"
- In reply to: dimitris: "index tuning"
- Messages sorted by: [ date ] [ thread ]
Date: Wed, 14 Jul 2004 08:59:04 +0100
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, > >
- Next message: PO: "Exporting to textfiles"
- Previous message: dimitris: "index tuning"
- In reply to: dimitris: "index tuning"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
