April 6, 2010 at 6:49 am
For a customer i have to investigate a table wich has more than 250 indexes on it. 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?
April 6, 2010 at 7:53 am
hennie7863 (4/6/2010)
The company who built the software recommends adding more indexes.
why company asking to add more indexes, did they feel any bad performance in that table? and are all existing indexes being used?
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
April 6, 2010 at 7:58 am
Company wants to update the software and more Foreign keys are added. These Foreign keys will be indexed. I now know more about this issue: There are about 80 indexes and de rest are statistics (255 - 80 -1). They want to drop the (less) unused statistics.
April 6, 2010 at 8:10 am
hennie7863 (4/6/2010)
Company wants to update the software and more Foreign keys are added. These Foreign keys will be indexed. I now know more about this issue: There are about 80 indexes and de rest are statistics (255 - 80 -1). They want to drop the (less) unused statistics.
First of all , are the statistics u want to drop are manaully created i think yes. if not then i am not sure whether you can drop them or not ? ( we need some expert to jump here)
additionally drop indexes after complete analysis and investigation as dropping index can harm you application performance someday somewhere.
So better to apply good mind and heart before dropping anything here.
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
April 6, 2010 at 8:50 am
Thnx for your reply,
currently we are not doing anything 😉 Until we are absolutely sure that it will not effect performance (100 consurrent users). We want to drop statistics and not indexes. After this we create the indexes that are needed, quickly. Thats the plan.
Currently i'm trying to figure out whether statistics are used, how they are used and what the effect will be when they are dropped.
April 7, 2010 at 12:10 am
Be careful when you drop statistics. Doing so may affect the execution plan chosen by the query optimizer.Statistics on indexes cannot be dropped by using DROP STATISTICS. Statistics remain as long as the index exists.
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
April 7, 2010 at 12:59 am
If the statistics are vital, and you have autoupdate statistics on - you should be ok. The system should recreate what is necessary.
As for the indexes - I would be leery about having too many indexes in an OLTP database. If this were a Datawarehouse database - I would say go for it.
In all likelihood you will have several indexes that are overlapping. These indexes can be combined to create a covering index. In which case you would be reducing the number of indexes as well as a little table size.
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
April 7, 2010 at 2:38 am
Thnx all for your replies. The case is that the supplier of the software wants to add indexes but can't because the 'slots' are being used by statistics (he said). So there are 250 slots (clustered index (1) + non clustered index + statistics) and they are full. That's what he said.
Currently i'm investigating whether it's true that statistics occupy slots which could also be used by indexes.
He also said that some statistics were used during a migration project and they were never used again. How do you check this?.
April 7, 2010 at 3:54 am
for statistics i dont know but yes following query help you to figure out which indexes are not being used
select i.name as indexname,
object_name(s.[object_id]) as tablename, s.database_id, s.[object_id], s.User_seeks, last_user_seek, user_scans, last_user_scan,
user_lookups, Last_user_lookup, user_updates, last_user_update
from sys.dm_db_index_usage_stats s
join sys.indexes i on s.object_id = i.object_id
and s.index_id = i.index_id
where OBJECTPROPERTY(s.OBJECT_ID,'IsUserTable') = 1
and user_scans + user_lookups + user_seeks = 0
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
April 7, 2010 at 4:26 am
thnx but this case is about SQL Server 2000 and it's not using dmv's. I found out that SQL server 2000 index slots are used by statistics, i read on the site Kimberly Tripp (http://www.sqlskills.com/blogs/Kimberly/post/Indexes-in-SQL-Server-20052008-Best-Practices-Part-1.aspx#ixzz0kP811y1Z). That's all i can find about this subject.
So in SQL Server 2000 the formula is : Clustered index+ nonclustered index + statistics = 250 (max).
The only thing now is to find out whether specific statistics are used. There is a column 'update' which tell you when statistics were updated. But, when auto update statistics is on, this date column is not telling when the statistic is created.
April 7, 2010 at 10:38 am
I would focus your time on the indexes and not so much the statistics. Tune your indexes and combine them where appropriate.
In 2000 there is also no script that can help you determine if an index is used or not. You may be able to take advantage of the Index Tuning Advisor - but be careful.
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
April 8, 2010 at 8:35 am
CirquedeSQLeil (4/7/2010)
I would focus your time on the indexes and not so much the statistics. Tune your indexes and combine them where appropriate.In 2000 there is also no script that can help you determine if an index is used or not. You may be able to take advantage of the Index Tuning Advisor - but be careful.
I agree with the first statement. Often there are indexes that can be combined or are complete overlaps and the shorter one can be deleted.
Strongly disagree with the second statement. Avoid ITA at all costs, especially if you already have a kajillion indexes on the system.
You 'could' simply delete all statistics, auto-generated or otherwise, and let the system recreate what it needs as it does its work. NOTE: performance will SUCK *** for a period of time that could be hours long while the optimizer says "go create this statistic so I can do my job better"!!!! Still, it is a potential solution. But I would simply script out all indexes and start to work on them...
BTW, I spent about two hundred manhours cleaning up indexes on a very complex database for a client that went crazy with the DTA in SQL 2005. DML activity was HORRID, and when I was done I had removed about 2/3rds of the indexes with a negligible reduction in read throughput but an amazing increase in DML throughput and concurrency due to reduced index maintenance for insert/update/delete activity. So you can get some real benefits from a proper indexing strategy/cleanup here.
Oh, since I have been counselled on the forum for advising people to do things that they might not have time or ability to do, I will note that there are professionals who can help you with the index strategy/cleanup effort if you want to go that route but can't take it on it yourself for whatever reason. I cannot stress strongly enough how important good indexing is for getting decent or optimal performance from SQL Server, so the ROI on an indexing effort is usually pretty darn high!!
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
April 8, 2010 at 10:29 am
TheSQLGuru (4/8/2010)
CirquedeSQLeil (4/7/2010)
I would focus your time on the indexes and not so much the statistics. Tune your indexes and combine them where appropriate.In 2000 there is also no script that can help you determine if an index is used or not. You may be able to take advantage of the Index Tuning Advisor - but be careful.
I agree with the first statement. Often there are indexes that can be combined or are complete overlaps and the shorter one can be deleted.
Strongly disagree with the second statement. Avoid ITA at all costs, especially if you already have a kajillion indexes on the system.
I don't think it is a strong disagreement in other than the suggestion to use it. I should have emphasized more emphatically the underlined and bolded statement.
I don't particularly like DTA in 2005 or the ITA in 2000. If one uses such tools, one must proceed with extreme caution.
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
April 9, 2010 at 1:04 am
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
April 9, 2010 at 2:25 am
hennie7863 (4/9/2010)
deleting these statistics could be a quick win.
Are these Statisctics are manually created ??
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
Viewing 15 posts - 1 through 15 (of 38 total)
You must be logged in to reply to this topic. Login to reply