October 25, 2009 at 8:18 am
I have rectified the mistakes....the only thing I haven't been able correct is the incorrect create index statement in case where a column name has an "_" in its name....I will do it once I get time...
thanks to you guys for appreciating the concept...you can play with the code to make it better....this would also help me.....also I will keep in mind to test the code thoroughly for my future articles....
thanks again...
Regards,
[font="Verdana"]Sqlfrenzy[/font]
October 25, 2009 at 1:41 pm
the scripts are assuming case insensitive...
I spent half hour just want to try to run it, but still not able to due to those up case/lower case.
October 25, 2009 at 4:50 pm
This is a really nice set of scripts. While I know I won't use the auto index creation scripts its good to easily be able to see what is suggested as well as some stats on the current indexes. Thank you.
October 26, 2009 at 12:15 am
kanke (10/25/2009)
the scripts are assuming case insensitive...I spent half hour just want to try to run it, but still not able to due to those up case/lower case.
I didn't get you....I have rectified the scripts...please post the errors you are getting....
Regards,
[font="Verdana"]Sqlfrenzy[/font]
October 26, 2009 at 8:26 am
one example is IndexUsageToSizeRatio and IndexUSageToSizeRatio.
I found a quick way, I changed all case to lower case, the only change need to change is INFORMATION_SCHEMA.TABLE_CONSTRAINTS, which should be up case.
Thanks!
October 26, 2009 at 9:15 am
Hi everybody
maybe I am wrong but it seems to me that in the proc_filltblindexusageinfo it miss the join
spi.index_id=si.index_id on sys.dm_db_index_usage_stats
I think spi.object_id=so.object_id is not sufficient as it returns too much line for each index in the tblindexusageinfo table
Kristof
October 26, 2009 at 9:54 am
kanke (10/26/2009)
one example is IndexUsageToSizeRatio and IndexUSageToSizeRatio.I found a quick way, I changed all case to lower case, the only change need to change is INFORMATION_SCHEMA.TABLE_CONSTRAINTS, which should be up case.
Thanks!
Ohh...but that should not effect the query execution.....
Regards,
[font="Verdana"]Sqlfrenzy[/font]
October 26, 2009 at 4:02 pm
SQL Frenzy,
I really like what you are attempting to do. There are still some rough spots, but they can be worked out.
Clearly DBAs should not go dropping indexes and adding them willy nilly without first thinking through the consequences.
Thank you.
Jeff Roughgarden,
October 29, 2009 at 8:02 am
Sqlfrenzy (10/26/2009)
kanke (10/25/2009)
the scripts are assuming case insensitive...I spent half hour just want to try to run it, but still not able to due to those up case/lower case.
I didn't get you....I have rectified the scripts...please post the errors you are getting....
I am still getting up to speed on this having come from an Oracle world. This topic is very important to me right now as we have been experiencing index issues. I really appreciate what you have done and want to implement this. I am confused though as I can't tell where the rectified scripts can be found! Can you please point me in the right direction?
October 29, 2009 at 9:11 am
mail.lists (10/29/2009)
Sqlfrenzy (10/26/2009)
kanke (10/25/2009)
the scripts are assuming case insensitive...I spent half hour just want to try to run it, but still not able to due to those up case/lower case.
I didn't get you....I have rectified the scripts...please post the errors you are getting....
I am still getting up to speed on this having come from an Oracle world. This topic is very important to me right now as we have been experiencing index issues. I really appreciate what you have done and want to implement this. I am confused though as I can't tell where the rectified scripts can be found! Can you please point me in the right direction?
I have update the article with the correct scripts i.e the scripts mentioned in the article are now rectified..
Regards,
[font="Verdana"]Sqlfrenzy[/font]
October 29, 2009 at 9:36 am
Hi everybody
Sql Frenzy
Apparently you didn't take into account my remark about the bad join into proc_filltblindexusageinfo. Am I wrong ?
Regards
Kristof
November 2, 2009 at 9:58 am
I'm trying to get to grips with this code as I think parts of it will prove extremely useful. However, i'm rather new to SQL and being a DBA so am confused on a number of points.
I'm getting multiple rows per index with differing stats in tblIndexUsageInfo. Is this normal?
Also, I'm unable to proceed past the "Execute proc_InsertMostUsedIndexes 10.00,100" command as I get the following error
Msg 512, Level 16, State 1, Procedure sp_Maint_InsertMostUsedIndexes, Line 3
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
The statement has been terminated.
This appears to be something to do with the duplicates above. I have multiple indexes with the same name on different tables. Is this the issue?
November 2, 2009 at 10:09 am
hi everybody.
I'm getting multiple rows per index with differing stats in tblIndexUsageInfo. Is this normal?
No it's not normal, see my earlier posts with the correction
K.
November 2, 2009 at 10:41 pm
gavinparnaby
Its very normal to get multi rows for one index. For instance if you run your
sys.dm_db_index_physical_stats(db_id(), OBJECT_ID('<tableName>') ,NULL,NULL,'DETAILED'). Yuo are likely to get so many rows of one index with different Statistics.
OBSERVE: I have put 'DETAILED' as a parameter. If you put 'LIMITED' you get 1 row.
Also the sys.db_index_usage_stats will do the same (with many entries per index).Check and you will find that some columns of the same index are not DISTINCT(I mean if you put a SELECT DICTINCT the multiple columns will still come out). These are just Statistics for that index's usage.
SO: Its normal!!!
November 3, 2009 at 1:48 am
Hmm, one saying its not normal, one saying it is!:-D
On the basis that it's normal then, the issue appears to be duplicate index names on different tables. For instance I've named the indexing on customer code idx_cust on about 25 tables.
I take it that is a mistake on my noob behalf?
Viewing 15 posts - 46 through 60 (of 84 total)
You must be logged in to reply to this topic. Login to reply