InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comSun, 19 May 2013 09:11:20 GMT20http://www.sqlservercentral.com/Forums/Topic803185-1407-1.aspxDear its nice article, but I face One issue here.When I run proc_FilltblIndexUsageInfo sp then I get following error.Msg 8134, Level 16, State 1, Procedure proc_FilltblIndexUsageInfo, Line 5Divide by zero error encountered.The statement has been terminated.I thing this message is generated against StatementCast((user_seeks+user_scans+user_lookups+user_updates)/dbo.Uf_GetindexSize(si.index_id,so.object_id) As decimal(10,2)) As IndexUsagetoSizeRatio.Please review the Code because I cant Proceeded further unless this error is removed.ThanksAzhar IqbalWed, 06 Jul 2011 01:11:41 GMTazhar.iqbal499http://www.sqlservercentral.com/Forums/Topic803185-1407-1.aspxadded your special columns to my table for this so that it now looks like[code="sql"]USE [Performance]GO/****** Object: Table [dbo].[perf_index_usage_stats] Script Date: 06/16/2011 15:41:13 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOSET ANSI_PADDING ONGOCREATE TABLE [dbo].[perf_index_usage_stats]( [server_name] [nvarchar](128) NULL, [date_read] [datetime] NULL, [database_id] [smallint] NULL, [database_name] [nvarchar](128) NULL, [table_name] [nvarchar](128) NULL, [index_id] [int] NULL, [index_name] [nvarchar](128) NULL, [user_seeks] [bigint] NULL, [user_scans] [bigint] NULL, [user_lookups] [bigint] NULL, [user_updates] [bigint] NULL, [last_user_seek] [datetime] NULL, [last_user_scan] [datetime] NULL, [last_user_lookup] [datetime] NULL, [last_user_update] [datetime] NULL, [system_seeks] [bigint] NULL, [system_scans] [bigint] NULL, [system_lookups] [bigint] NULL, [system_updates] [bigint] NULL, [last_system_seek] [datetime] NULL, [last_system_scan] [datetime] NULL, [last_system_lookup] [datetime] NULL, [last_system_update] [datetime] NULL, [pk_id] [bigint] IDENTITY(1,1) NOT NULL, [IndexUsage] [int] NULL, [IndexSizeKB] [int] NULL, [IndexUSageToSizeRatio] [decimal](10, 2) NULL, [ConstraintType] [varchar](25) NULL, [IndexKeyColumn] [varchar](1000) NULL, [IncludedColumn] [varchar](1000) NULL, [type_desc] [nvarchar](60) NULL, CONSTRAINT [PK_perf_index_usage_stats] PRIMARY KEY NONCLUSTERED ( [pk_id] ASC)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]) ON [PRIMARY]GOSET ANSI_PADDING OFFGO[/code]for the first part of capturing the index usage info instead of using the functions i'm using a single query[code="sql"]select server_name = @@servername, date_read = getdate(), a.database_id, database_name = db_name(), table_name = OBJECT_NAME(a.[object_id]), a.index_id, c.name as index_name, co.name as column_name, case when ic.is_included_column = 1 then 'Included Column' else 'Key Column' end Column_Type, case when c.is_Primary_key = 1 then 'PK' else 'I' end Index_Type,c.type_desc, (a.user_seeks + a.user_scans + a.user_lookups + a.user_updates) as 'IndexUsage',a.user_seeks, a.user_scans, a.user_lookups, a.user_updates,a.last_user_seek, a.last_user_scan, a.last_user_lookup, a.last_user_update, a.system_seeks, a.system_scans, a.system_lookups, a.system_updates,a.last_system_seek, a.last_system_scan, a.last_system_lookup, a.last_system_updatefrom sys.dm_db_index_usage_stats a inner join sys.indexes c on c.object_id = a.object_id and a.index_id = c.index_idinner join sys.columns co on co.object_id = c.object_id inner join sys.index_columns ic on ic.object_id = co.object_id and ic.index_id = c.index_idinner join sys.objects so on so.object_id = a.object_idinner join INFORMATION_SCHEMA.TABLE_CONSTRAINTS conon so.name=con.TABLE_NAME where so.type='u'and a.database_id in (10)AND OBJECTPROPERTY(a.[object_id], 'IsMsShipped') = 0[/code]tomorrow i'm going to work in the index size data but instead of the function, i have years of saved data from running sys.dm_db_index_physical_stats either daily or every few days and will use that. and going to add the significance to my missing index scans to see if i can filter out some more dataThu, 16 Jun 2011 13:54:23 GMTalen teplitskyhttp://www.sqlservercentral.com/Forums/Topic803185-1407-1.aspxmissed this 2 years ago but will check this out now and see how it fits in with what i built in the last 6 months. i have the DMV's running and putting data into a table and then i use SSRS to generate a few reports from the data. unused indexes, missing indexes, usage of indexes for critical tables and indexes that haven't been used in the last 30 daysunused indexes i mostly see on subscribers. i deleted a lot of them and it improved performance with replication. we have hundreds of replication jobs and used to get the dreaded error pop ups every few weeks. not it's once every few monthsmissing indexes i'm still playing with since a lot of the data on SQL 2005 seems to be buggywhere did the significance factor come from in the calculation? i've never read about it. my missing index report is filtered based on a minimum number of seeks or whatever that SQL says would be saved. i think i filter anything over 1000Thu, 16 Jun 2011 08:17:55 GMTalen teplitskyhttp://www.sqlservercentral.com/Forums/Topic803185-1407-1.aspx[quote][b]alexander.suprun (6/10/2011)[/b][hr]So after 2 years this management script is still has a logic error which makes using it poitless if not saying harmful.Major issues:'IndexUsage' column calculated as (user_seeks+user_scans+user_lookups+user_updates) is used to decide whether we need to rebuild or drop the index.So in case there is a totally useless index on highly active table where user_seeks+user_scans+user_lookups = 0 but user_updates is big enough then such an index would be rebuilt each and every time we run the script consuming cpu and creating extra load on I/O system. When it's obvious that such an index should be dropped as useless one.And what is so called "Unused Indexes" are not affecting performance at all, if there are no updates on these indexes then there are no maintenance costs. The only reason you may want to drop them is because of the disk space.Usually to find good/bad indexes one should use some kind of difference between (user_updates) and (user_seeks+user_scans+user_lookups)Minor issues:Limitition of Uf_GetIndexSize to only non-partitioned tables.Reorganizing heavily used indexes every time even if they don't have fragmentation (@avg_frag<=20)REDUILD index without ONLINE option (for enterprise edition) may lock the whole table and it is very critical for 24/7 systemsThere is no deletes from tblUnusedIndexes, so I'm assuming it's a history table? If so then there is no timestamp in it. Analyzing such a mess can become a challenging task for a DBA.Dropping index by some fake ID - @UnusedIndID is not a wise decision as well.[/quote]Why are people using this script? Just use Ola Hallengren's IndexOptimize stored procedure to take care of your existing indices, and set-up a table to keep track of your indices data. The script in this article just has too many dependencies.This script is bad for many reasons, including the fact that it does not automatically break down fragmentation into external and internal fragmentation. It also divides by zero. Small tables will constantly be rebuilt with this script, which is pointless. SSMS's Index Physical Statistics standard report has the same epic failure.As a brief aside, although I love Ola Hallengren's scripts, I really hate the lack of higher-order functions in TSQL stored procedures. Ola's scripts are not that only TSQL scripts that emulate the usefulness of passing higher-order functions as parameters via passing in strings and parsing the string during execution. The problem with parsing strings is that if Ola or whoever wrote the string parser didn't think of a scenario you had in mind, then you either have to edit the script or do some complex logic before invoking the script.Please also see [url=http://msdn.microsoft.com/en-us/library/ms345485.aspx]Limitations on using Missing Indexes feature[/url] on MSDN.Fri, 10 Jun 2011 14:17:44 GMTjohnzabroskihttp://www.sqlservercentral.com/Forums/Topic803185-1407-1.aspxSo after 2 years this management script is still has a logic error which makes using it poitless if not saying harmful.Major issues:'IndexUsage' column calculated as (user_seeks+user_scans+user_lookups+user_updates) is used to decide whether we need to rebuild or drop the index.So in case there is a totally useless index on highly active table where user_seeks+user_scans+user_lookups = 0 but user_updates is big enough then such an index would be rebuilt each and every time we run the script consuming cpu and creating extra load on I/O system. When it's obvious that such an index should be dropped as useless one.And what is so called "Unused Indexes" are not affecting performance at all, if there are no updates on these indexes then there are no maintenance costs. The only reason you may want to drop them is because of the disk space.Usually to find good/bad indexes one should use some kind of difference between (user_updates) and (user_seeks+user_scans+user_lookups)Minor issues:Limitition of Uf_GetIndexSize to only non-partitioned tables.Reorganizing heavily used indexes every time even if they don't have fragmentation (@avg_frag<=20)REDUILD index without ONLINE option (for enterprise edition) may lock the whole table and it is very critical for 24/7 systemsThere is no deletes from tblUnusedIndexes, so I'm assuming it's a history table? If so then there is no timestamp in it. Analyzing such a mess can become a challenging task for a DBA.Dropping index by some fake ID - @UnusedIndID is not a wise decision as well.Fri, 10 Jun 2011 12:30:39 GMTAlexander Suprunhttp://www.sqlservercentral.com/Forums/Topic803185-1407-1.aspxNot sure what your pasting into Grasshopper, code block I copy, pastes well formed for me in NOTEPAD, NOTEPAD2 and into a query pane.Fri, 10 Jun 2011 11:50:17 GMTwchasterhttp://www.sqlservercentral.com/Forums/Topic803185-1407-1.aspxThis is a great article, and well thought out. I have been in the process of writing something that I can use here - but not to this extent.I am going to implement it to a point and run tests. I am unwilling to automatically drop or create indexes, but will use it to a point. I would, as some have said here, want to evaluate the data which is conveniently stored in a table and decide. But, to me, the theory is sound. I look forward to following this thread further.Fri, 10 Jun 2011 11:50:15 GMTsjimmohttp://www.sqlservercentral.com/Forums/Topic803185-1407-1.aspxA couple of comments:1. I appreciate the offer of these utilities and the benefit of the work put into this.2. However, I get the "Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= ..."3. The code appears to be written in a form to purposefully obfuscate it's meaning.4. If the forum software could make the "code windows" on the site more "copy/paste friendly" that would also be a great help--when I copy/paste it spits everything out in one huge line and seems to strip whitespace, linefeeds, etc, so I have to spend tedious time finding the linebreaks, etc.5. I don't mean to "look a gift horse in the mouth" but I would greatly appreciate it if an updated version could be posted--without the errors, etc.6. Bottom line--this does not work at all--and again--I appreciate free code but if it doesn't work it actually costs me time trying to get it to work so it's not free anymore.7. If I'm out of line, please let me know. I just can't get this bit of code to work when I copy it as posted.Thank you.Fri, 10 Jun 2011 11:24:07 GMTJames Stephenshttp://www.sqlservercentral.com/Forums/Topic803185-1407-1.aspxNice article SqlFrenzy!!I had an in-database index sproc but I like the additional functionality of needed and unused indexes.I am updating the code to run from a Maintenance type database and to run for a specific database. The independent database allows me to update indexes for third party apps and Share Point databases. I am a bit leery of the automatic drop and create of indexes but that looks like a scheduled job on the weekends.THANKS!DarylFri, 10 Jun 2011 10:58:10 GMTDaryl AZhttp://www.sqlservercentral.com/Forums/Topic803185-1407-1.aspxGood dtuff, thank you, but I was getting "division by zero" so I wrapped relevant code of proc_FilltblIndexUsageInfo in a CASE: CASE WHEN dbo.Uf_GetindexSize(si.index_id, so.object_id) != 0 THEN CAST((user_seeks + user_scans + user_lookups + user_updates) / dbo.Uf_GetindexSize(si.index_id, so.object_id) AS DECIMAL(10,2)) ELSE 0 END AS IndexUsagetoSizeRatioFri, 10 Jun 2011 07:44:30 GMTalexms_2001http://www.sqlservercentral.com/Forums/Topic803185-1407-1.aspxHey SQLFrenzy, great work. We all should expect articles to be a good starting point and not a perfect solution. Your thought process is sound to me. SeanWed, 21 Jul 2010 08:02:30 GMTSean Woehrlehttp://www.sqlservercentral.com/Forums/Topic803185-1407-1.aspxam working on automating this across all servers and databases. It may take some time.Fri, 23 Apr 2010 01:59:28 GMTSqlfrenzyhttp://www.sqlservercentral.com/Forums/Topic803185-1407-1.aspxIt doesn't look like it has been updated...SQLFrenzy - can you update the original article with the fixed code please?Thanks much :-)Mon, 19 Apr 2010 06:27:27 GMTmatthew-550133http://www.sqlservercentral.com/Forums/Topic803185-1407-1.aspxI haven't reviewed this article in a while. Is the original article updated with all of the changes mentioned in this discussion?Thu, 01 Apr 2010 10:13:39 GMTpnewharthttp://www.sqlservercentral.com/Forums/Topic803185-1407-1.aspxI have been working through this article and have found very instructional and also useful. I have not noticed anyone having problems with proc_InsertMostUsedIndexes. I am stuck. Whenever I run it I get the following error:Msg 512, Level 16, State 1, Procedure proc_InsertMostUsedIndexes, Line 12Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.I am at a deadend so if anyone could give me any help it would be greatly appriciated.Thanks in advance. Post #861979 Mon, 08 Feb 2010 12:51:24 GMTSQLCrazyhttp://www.sqlservercentral.com/Forums/Topic803185-1407-1.aspxJust found this article. I thought this was a well-thought out article. There is useful information in the article - with some good ideas.Like others, I am not in favor of auto-creating any missing indexes. Nor am I in favor of dropping any indexes that "aren't" used.Since everything is logged to a table though, I think I would use the script to that point and then take the opportunity to add or drop indexes from there.Thanks for the article.Mon, 18 Jan 2010 11:46:19 GMTSQLRNNRhttp://www.sqlservercentral.com/Forums/Topic803185-1407-1.aspxHas anyone implemented this in production? If so, could you share your experience? Any tips or tricks or gotcha's?Thanks in advance.Mon, 18 Jan 2010 11:09:56 GMTJRoughgardenhttp://www.sqlservercentral.com/Forums/Topic803185-1407-1.aspxI'm still playing with this and trying to learn.The following code[code="plain"]select distinct db_name(db_id()) DbName,so.name as 'TableName',ISNULL(si.name,'No Index') as IndexName,si.index_id,Case When is_primary_key=1 then 'Primary Key Constraint' Else 'Index' End ConstraintType, si.type_desc,dbo.udf_GetIndexCol(si.index_id,so.object_id,0) As IndexKeyColumn,dbo.udf_GetIndexCol(si.index_id,so.object_id,1) As IncludedCols,spi.user_seeks,spi.user_scans,spi.user_lookups,spi.user_updates,(user_seeks+user_scans+user_lookups+user_updates) as 'IndexUsage ',dbo.udf_GetIndexsize(si.index_id,so.object_id) as 'IndexSizeKB',Cast((user_seeks+user_scans+user_lookups+user_updates)/dbo.udf_GetIndexSize(si.index_id,so.object_id) As decimal(10,2)) As IndexUsagetoSizeRatio from sys.objects so inner join sys.indexes si on so.object_id=si.Object_id inner join sys.dm_db_index_usage_stats spi on spi.Object_id=so.Object_id and spi.index_id=si.index_id inner join sys.index_columns sic on sic.object_id=si.object_id and sic.index_id=si.index_id inner join sys.columns sc on sc.Column_id=sic.column_id and sc.object_id=sic.object_id inner join INFORMATION_SCHEMA.TABLE_CONSTRAINTS c on so.name=c.TABLE_NAME where so.type='u'[/code]which is used in the initial IndexUsageInfo sp seems to only pull through details on tables which have a Primary Key. I have a whole host of tables which don't have PK's set but do have some indexing, and yet they're not appearing. Why?Edit: - I've just spotted it. if changing the inner join INFORMATION_SCHEMA.TABLE_CONSTRAINTS to LEFT JOIN, it returns all indexes.Wed, 04 Nov 2009 03:25:19 GMTgavinparnabyhttp://www.sqlservercentral.com/Forums/Topic803185-1407-1.aspxkarl;Dont avoid dividing by Zero. SQL has a very good facility to overcome that. For instance, run these 2 queries and tell me the results:(1)DECLARE @a Money, @b int;SET @a = 2;SET @b = 0SELECT @a/@b;(2) DECLARE @a Money, @b int;SET @a = 2;SET @b = 0SELECT @a/ NULLIF(@b,0);ALTERNATIVELY, you dont want a NULL but 0 : SELECT ISNULL(@a/NULLIF(@b,0),0)This issue should not be a show stopper! Always use it when doing divisions, you will not feel bad.....Tue, 03 Nov 2009 23:34:05 GMTINNOCENT GUMBOhttp://www.sqlservercentral.com/Forums/Topic803185-1407-1.aspxI get a divide by Zero error when running proc_FilltblIndexUsageInfo. I believe this is caused by having indexes of 0 bytes when trying to calculate the ratio.Tue, 03 Nov 2009 14:11:02 GMTkarl.spamhttp://www.sqlservercentral.com/Forums/Topic803185-1407-1.aspxgavinparnaby :Sorry, I will check your sub query and I will give you feedback tomorrow. Please bear with us.Tue, 03 Nov 2009 07:18:32 GMTINNOCENT GUMBOhttp://www.sqlservercentral.com/Forums/Topic803185-1407-1.aspxgavinparnaby :If I undestend you correclty you are saying you are getting there is a resultset of duplicate indexes of differnt tables. I need to be corrected here. Indices are other data structures grouped in a DMV called sys.indexes. So, these tables have the same index names. check the names of the tables and run this stored proc under that Database :sp_helpindex '<tablename>' . you will make sure that they share the same names ORrun : select o.Object_id, o.name AS TableName, i.Name AS IndexName FROM sys.Objects o INNER JOIN sys.indexes i ON o.Object_id = i.object_ID AND o.type = 'U' ORDER By o.name , i.Name You will be able to see duplicates. If the TableName and Object_Id are the same then conclude that you are actually duplicating one thing(same object)Tue, 03 Nov 2009 07:15:11 GMTINNOCENT GUMBOhttp://www.sqlservercentral.com/Forums/Topic803185-1407-1.aspxOk, I'm following what Kristoff has done there. I'm still unable to run because of the subquery returning multiple row problem.Am I correct that having multiple indexes named the same is a bad thing? Is there actually a requirement for the subquery (select index_id from sys.indexes where name=b.IndexName) As Index_idin the first place as Index_ID is in the IndexUsageInfo table?Tue, 03 Nov 2009 03:08:29 GMTgavinparnabyhttp://www.sqlservercentral.com/Forums/Topic803185-1407-1.aspxKristofsure, checked. Kristof is right.Tue, 03 Nov 2009 03:04:13 GMTINNOCENT GUMBOhttp://www.sqlservercentral.com/Forums/Topic803185-1407-1.aspxGrassohopper I'am sorry but I disagree with you. I still think that it's not normal.If you look at the tblIndexUsageInfo table you will see that if the table as one index you will have one line with the index name,If the table has two index you will have four line two for each index,If the table have three indexes you will have nine line three for each indexes and so on.More important you will see that the user_seeks, user_scans etc value are the same and are repeated.This is due to a cross join because it miss the "and spi.index_id=si.index_id" statement for the join on dm_db_index_usage_stats. If you look atselect * from sys.dm_db_index_usage_statswhere database_id='9' (put the database_id you wish) you will clearly see that there is only one and unique index_id for each object_id (table) and one and unique values for user_seeks, user_scans etc....for example here what it gives you without the correct join a select on tblIndexUsageInfo: tablename indexname seeks scans lookups update---------- ------------------------------ ------ ------ ------- ------extract_sd IX_extract_sd_wan 0 0 0 11714 extract_sd IX_extract_sd_wan 0 0 0 46838 extract_sd IX_extract_sd_wan 46837 5 0 11714 extract_sd IX_extractsd_exsd_honoraire 0 0 0 11714 extract_sd IX_extractsd_exsd_honoraire 0 0 0 46838 extract_sd IX_extractsd_exsd_honoraire 46837 5 0 11714 extract_sd PK_Extract_sd 0 0 0 11714 extract_sd PK_Extract_sd 0 0 0 46838 extract_sd PK_Extract_sd 46837 5 0 11714 with the correct join you will havetablename indexname seeks scans lookups update---------- ------------------------------ ------ ------ ------- ------extract_sd IX_extract_sd_wan 46837 5 0 11714 extract_sd IX_extractsd_exsd_honoraire 0 0 0 11714 extract_sd PK_Extract_sd 0 0 0 46838 regardsKristofKristofTue, 03 Nov 2009 02:41:07 GMTcroussethttp://www.sqlservercentral.com/Forums/Topic803185-1407-1.aspxHmm, one saying its not normal, one saying it is!:-DOn 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?Tue, 03 Nov 2009 01:48:46 GMTgavinparnabyhttp://www.sqlservercentral.com/Forums/Topic803185-1407-1.aspxgavinparnaby 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!!!Mon, 02 Nov 2009 22:41:11 GMTINNOCENT GUMBOhttp://www.sqlservercentral.com/Forums/Topic803185-1407-1.aspxhi 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 correctionK.Mon, 02 Nov 2009 10:09:49 GMTcroussethttp://www.sqlservercentral.com/Forums/Topic803185-1407-1.aspxI'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 errorMsg 512, Level 16, State 1, Procedure sp_Maint_InsertMostUsedIndexes, Line 3Subquery 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?Mon, 02 Nov 2009 09:58:00 GMTgavinparnabyhttp://www.sqlservercentral.com/Forums/Topic803185-1407-1.aspxHi everybodySql FrenzyApparently you didn't take into account my remark about the bad join into proc_filltblindexusageinfo. Am I wrong ?RegardsKristofThu, 29 Oct 2009 09:36:17 GMTcroussethttp://www.sqlservercentral.com/Forums/Topic803185-1407-1.aspx[quote][b]mail.lists (10/29/2009)[/b][hr][quote][b]Sqlfrenzy (10/26/2009)[/b][hr][quote][b]kanke (10/25/2009)[/b][hr]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.[/quote]I didn't get you....I have rectified the scripts...please post the errors you are getting....[/quote]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?[/quote]I have update the article with the correct scripts i.e the scripts mentioned in the article are now rectified..Thu, 29 Oct 2009 09:11:20 GMTSqlfrenzyhttp://www.sqlservercentral.com/Forums/Topic803185-1407-1.aspx[quote][b]Sqlfrenzy (10/26/2009)[/b][hr][quote][b]kanke (10/25/2009)[/b][hr]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.[/quote]I didn't get you....I have rectified the scripts...please post the errors you are getting....[/quote]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?Thu, 29 Oct 2009 08:02:28 GMTmail.listshttp://www.sqlservercentral.com/Forums/Topic803185-1407-1.aspxSQL 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, Mon, 26 Oct 2009 16:02:42 GMTJRoughgardenhttp://www.sqlservercentral.com/Forums/Topic803185-1407-1.aspx[quote][b]kanke (10/26/2009)[/b][hr]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![/quote]Ohh...but that should not effect the query execution.....Mon, 26 Oct 2009 09:54:05 GMTSqlfrenzyhttp://www.sqlservercentral.com/Forums/Topic803185-1407-1.aspxHi everybodymaybe 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_statsI think spi.object_id=so.object_id is not sufficient as it returns too much line for each index in the tblindexusageinfo tableKristofMon, 26 Oct 2009 09:15:01 GMTcroussethttp://www.sqlservercentral.com/Forums/Topic803185-1407-1.aspxone 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!Mon, 26 Oct 2009 08:26:53 GMTkankehttp://www.sqlservercentral.com/Forums/Topic803185-1407-1.aspx[quote][b]kanke (10/25/2009)[/b][hr]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.[/quote]I didn't get you....I have rectified the scripts...please post the errors you are getting....Mon, 26 Oct 2009 00:15:05 GMTSqlfrenzyhttp://www.sqlservercentral.com/Forums/Topic803185-1407-1.aspxThis 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.Sun, 25 Oct 2009 16:50:43 GMTkarl.spamhttp://www.sqlservercentral.com/Forums/Topic803185-1407-1.aspxthe 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.Sun, 25 Oct 2009 13:41:28 GMTkankehttp://www.sqlservercentral.com/Forums/Topic803185-1407-1.aspxI 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...Sun, 25 Oct 2009 08:18:05 GMTSqlfrenzy