more than 250 indexes on a table

Question

Post reply

more than 250 indexes on a table

Viewing 15 posts - 16 through 30 (of 38 total)

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

hennie7863 SSCommitted Points: 1653 More actions · Answer 1

hennie7863

SSCommitted

Points: 1653

April 9, 2010 at 4:44 am

#1148948

Don't know. Why do you ask?

Bhuvnesh SSC Guru Points: 59351 More actions · Answer 2

hennie7863 (4/9/2010)
Don't know. Why do you ask?

because these are automatically generated by Sql server and you cant drop them (as far as i know )

-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

chrisfradenburg SSCrazy Eights Points: 9592 More actions · Answer 3

It's possible

select count(*) from sys.stats where auto_created = 1

1804

drop statistics tablename._WA_Sys_00000002_00750D23

select count(*) from sys.stats where auto_created = 1

1803

Bhuvnesh SSC Guru Points: 59351 More actions · Answer 4

cfradenburg (4/9/2010)
It's possible
select count(*) from sys.stats where auto_created = 1
1804
drop statistics tablename._WA_Sys_00000002_00750D23
select count(*) from sys.stats where auto_created = 1
1803

Thanks , good learning for me

-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

Bhuvnesh SSC Guru Points: 59351 More actions · Answer 5

select * from sys.stats where auto_created = 0 And what does this query mean ?

-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

chrisfradenburg SSCrazy Eights Points: 9592 More actions · Answer 6

That will be all user created stats including those for indexes.

I should have also pointed out earlier that while it is possible to drop system created stats SQL created them for a reason. Doing so may be unwise. I would go with Jason's recommendation of looking at the indexes the vendor supplied. There may be some optimization that can be done there.

SQLRNNR SSC Guru Points: 281334 More actions · Answer 7

hennie7863 (4/9/2010)
Why should ik focus on the indexes? The indexes are advised by the vendor. When i try to change the indexes i will danger the support from the vendor.
The statistics could be created during one time used queries. Or during a migration project or whatever. Perhaps there are statistics based on columns which don't affect the overall performance. I think that this will more advisable because deleting these statistics could be a quick win.
thnx for ur replies

If creating an index which is better than theirs violates support, then it is an ego issue.

The vendor doesn't need to know that you have created better indexes either.

Statistics would be more likely to be created by queries that are repeatable. Besides, if you drop them, the database will recreate them.

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

SQLRNNR SSC Guru Points: 281334 More actions · Answer 8

Bhuvnesh (4/9/2010)
select * from sys.stats where auto_created = 0 And what does this query mean ?

Word of caution. That view is SQL 2005. This thread is in reference to SQL 2000.

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

Kimberly L. Tripp SSC Journeyman Points: 89 More actions · Answer 9

There's really nothing that tracks index or statistics usage in SQL Server 2000... which is a bummer.

At this point, I'd drop all of the statistics that match a leading column of one of your indexes. 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. The best way to do this is to "leverage" the code of sp_helpindex (sp_helptext sp_helpindex) and then create a modified version of it to display what you're interested in.

If this doesn't make sense or you're having troubles - let me know. I'll try and help you out. Unfortunately, I'm heading to a conference tomorrow but later in the week next week I can probably help if you're still unable to do it!

Cheers,

kt

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 10

Hey - look at the "newbie" who is posting on SSC now!! Welcome aboard Kimberly! 🙂

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

Jeff Moden SSC Guru Points: 1003873 More actions · Answer 11

hennie7863 (4/6/2010)
For a customer i have to investigate a table [font="Arial Black"]wich has more than 250 indexes on it[/font]. The company who built the software recommends adding more indexes. The question is whether this is advisable. My assigment is to investigate the indexes. database is SQL Server 2000 and they want to migrate to SQL Server 2008. Anyone an idea of a proper approach of this problem?

Sorry but I have to ask... since it's not possible to have more than 250 indexes on a single table, are you pulling our leg? Where did you get the information that the table has more than 250 indexes on it?

--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)

SQLRNNR SSC Guru Points: 281334 More actions · Answer 12

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.

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: 1003873 More actions · Answer 13

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. 😉

--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)

Bhuvnesh SSC Guru Points: 59351 More actions · Answer 14

Jeff Moden (4/10/2010)
Sorry but I have to ask... since it's not possible to have more than 250 indexes on a single table, are you pulling our leg? Where did you get the information that the table has more than 250 indexes on it?

Surprise!!! nobody had catch this leaky part 🙂 Jeff you are great

-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

Bhuvnesh SSC Guru Points: 59351 More actions · Answer 15

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 ?

-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)