Indexing Strategies
-
Our organization has users which are requesting separate indexes on fields which are included in a composite index (clustered - primary key fields ) on the table already. The issue is the order of the fields in the existing composite index. Can this issue be resolved by created the additional individual indexes?
-
In most case, no, unless query optimizer can not use it efficiently.
Alwayse verify with execution plan.
-
If the query does the order by with the order different than the order of columns in existing index, the query could run into table scan.
As suggested, you should examine the query execution plan.
Adding additional index will add some overhead when performing insert/delete/update.
-
IMO, your best friend in determing index placement is the "set statistics IO on" command. This will tell you the logical IO of your query which is the number of pages you ahve to touch to return your resultset.
Composite clustered indexes should be used sparingly as (1) it is difficult to gauge the amount of pagesplitting this will case and (2) they bloat any non-clustered indexes you create. There are some great links on this site that discuss clustered indexes.
HTH
------------
Ray Higdon MCSE, MCDBA, CCNA -
Why do your users think, they need their own indexes?
Maybe you can create a test scenario and see what the Index Tuning Wizard is suggesting?
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
Hey Allen, congrats on crossing the 2,000 posts barrier
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply