March 3, 2008 at 11:35 pm
Hi,
I have two indexes on one table like below:
CREATE CLUSTERED INDEX Index1 ON table1 (Column1,Column2)
CREATE NONCLUSTERED INDEX Index2 ON table1 (Column1)
Index1 is primary Clustered index and contain other index field, so index2 is redundant.
When I compare performance of these two indexes, I am getting same result. If I look usage of these two indexes, index2 is heavily used and index1 is used less than index2.
If I drop index2, Do I get any peformance problem?
Thanks
March 4, 2008 at 12:41 am
Index2 is redundant. You should be able to drop it without having an effect
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
March 4, 2008 at 5:57 am
Oh come on, it'll have an effect. You're inserts will be faster because you're not maintaining a useless index.
😀
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
March 4, 2008 at 2:20 pm
I hate to disagree but you're both wrong. I've done extensive testing trying to see a performance drop on maintaining indexes for inserts at least. I generated around 2,000 rows a second by individual inserts from several clients. despite my best efforts I was not able to show any difference by adding indexes.
OK the nc vs the clustered . I'd refer you to my posts on index analysis http://sqlblogcasts.com/blogs/grumpyolddba/archive/2008/02/18/analysing-indexes-summary.aspx
however in a nutshell, the clustered index is always the entire table thus if you're not doing a select * a scan on a nc index which is on the same columns as the clustered index will use less pages. e.g. if your rows are over 4k, each row is a page, so to get 10% of a million row table will need 100k page reads, however for say an int + a datetime you'll only need around 340 page reads. Part 4 of my posts covered index sizes. It's usually quite good to have a nc index to cover your clustered index.
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
March 4, 2008 at 11:22 pm
If the NC was wider, I'd agree with you. As it it, unless Column1 is highly selective, or queries are done that only filter on and retrieve Column1, then it's not that useful an index.
If it was likely to cover a number of queries, I'd suggest keeping it. As it is, the only indexes it will cover are EXISTS with a where only on Column1 or queries of the form SELECT Column1, Column2 from tbl where Column1 = 'Something'
A single row seek on the cluster may be 1 or 2 more pages than a single row seek on the non-cluster, but if bookmark lookups are required then the cluster will be read anyway and that gain is lost in spades.
There are certainly a few cases where the NC will be used over the cluster, but they are few and far between, and I wouldn't consider keeping that index around. If necessary, rather than dropping it, widen it to be covering .
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
March 5, 2008 at 6:09 am
I'm not questioning you, per se, but surely there's a DBA "it depends" on the end of a statement that says maintaining extra indexes makes no difference at all. It must make some difference. Maybe that difference is small enough in most cases to not worry about, but it couldn't possibly be a flat, free, operation. Page splits in the index, page splits in the cluster causing changes to the pointers in the index, just simply adding data to the page of an index, none of these are free operations, so it couldn't possibly be that absolute, could it? Is it true when you've got three, four, five? I saw a junior DBA add eight indexes, one to each of the eight columns in a table, once, plus the cluster. I know that was costly to maintain.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply