more than 250 indexes on a table

Question

more than 250 indexes on a table

Viewing 9 posts - 31 through 38 (of 38 total)

You must be logged in to reply to this topic. Login to reply

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 1

Bhuvnesh (4/11/2010)
Kimberly L. Tripp (4/9/2010)
I would first check to see if these are single columns stats (the simple answer will be YES if all of the index names start with _WA_SYS). If that's the case, pull the key out and for all statistics that are also a leading column of an index - drop those.
New thing for me.can you elaborate it please ?

An index on a column is going to have statistics for it so having another statistic for that same column is redundant and thus wasted overhead for no gain.

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service

Greg Edwards-268690 SSC-Insane Points: 20680 More actions · Answer 2

Jeff Moden (4/10/2010)
CirquedeSQLeil (4/10/2010)
After re-reading the OP, it seems that the best plan of attack to me would be to migrate to SQL 2008 first and then add the new indexes. In 2K8 take advantage of the dmvs and eliminate useless stats and indexes as well as combine indexes to make covering indexes.
Just another 2 cents.
I agree. Move first, investigate indexes later. According to the OP, they'll definitely need to delete some indexes before they add any new ones. 😉

I would migrate first too. As mentioned before, 2008 has some better tools to help with seeing performance bottlenecks.

And if the vendor is saying "more indexes", maybe they could evaluate the database schema for some normalization. That just seems like a lot of indexes on 1 table.

Indexes are not something we just add into our without investigating and proving they would be used and improve performance.

Greg E

Jeff Moden SSC Guru Points: 1004405 More actions · Answer 3

I think that the code that uses the table and the design of the databased needs serious review once it's moved. Perhaps the parental lineage of the vendor needs to be seriously questioned, as well. I've not yet seen a properly designed table/database yet that has been in need of more than a dozen or so indexes. Having 250 indexes is going to kill all performance expectations for inserts and updates.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

JacekO SSCertifiable Points: 6610 More actions · Answer 4

Jeff Moden (4/12/2010)
I think that the code that uses the table and the design of the database needs serious review once it's moved. Perhaps the parental lineage of the vendor needs to be seriously questioned, as well. I've not yet seen a properly designed table/database yet that has been in need of more than a dozen or so indexes. Having 250 indexes is going to kill all performance expectations for inserts and updates.

That was my first reaction when started reading the thread. Unfortunately it looks like he is stuck with the design because of the vendor issue. It is hard to judge without any insight into the database schema if this is the case, but it is a pity how many times the one to one relationships are forgotten in database design.

---------------------------------------------
[font="Verdana"]Nothing is impossible.
It is just a matter of time and money.[/font]

hennie7863 SSCommitted Points: 1653 More actions · Answer 5

Thanx for your replies. It's hard answering all your remarks and questions.

Upgrading to SQL2K8 is currently not an option for the vendor. I'm still thinking about deleting statistics which probably are created based on fields which are migrated during an upgrade of the vendows software.

There are about 80 indexes on that table. Yes i can't imagine that these indexes on this table function very well.

Again, thanx all for your replies and i will look into taking proper measures/advice towards to vendor.

SQLRNNR SSC Guru Points: 281334 More actions · Answer 6

hennie7863 (4/12/2010)
Thanx for your replies. It's hard answering all your remarks and questions.
Upgrading to SQL2K8 is currently not an option for the vendor. I'm still thinking about deleting statistics which probably are created based on fields which are migrated during an upgrade of the vendows software.
There are about 80 indexes on that table. Yes i can't imagine that these indexes on this table function very well.
Again, thanx all for your replies and i will look into taking proper measures/advice towards to vendor.

You're welcome.

One more tidbit.

Here is a script at SSC that should help you eliminate those stats or indexes that are duplicates in SQL 2000.

http://www.sqlservercentral.com/scripts/Miscellaneous/30701/

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

Jeff Moden SSC Guru Points: 1004405 More actions · Answer 7

hennie7863 (4/6/2010)
For a customer i have to investigate a table wich has more than 250 indexes on it.

hennie7863 (4/12/2010)
There are about 80 indexes on that table.

Heh... silly me. What's 170 indexes between friends? 😛 Perhaps you were including the stats that support those 80 indexes?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

hennie7863 SSCommitted Points: 1653 More actions · Answer 8

80 non clustered indexes+ 1 clustered index + 81 statisics for the indexes + 88 left over statistics = 250.

Okay my initial description was a bit fuzzy. I opened this post without clarification of the SQL 2000 problem and the precise description of the problem of my internal customer.

hennie7863 SSCommitted Points: 1653 More actions · Answer 9

Well, couple of weeks later. The investigation is now focussed on deleting the statistics on SQL Server 2000. First i have to find out whether the statistics can be scripted? if it cant't a backup of database should be an option. Any one ideas?

Thnx

Hennie