InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comThu, 23 May 2013 19:24:55 GMT20http://www.sqlservercentral.com/Forums/Topic1136196-2881-1.aspx[quote][b]Naked Ape (7/5/2011)[/b][hr]Interesting reading. Was that a deliberate reference to Alices Restaurant, Massacre (twenty-eight color graphs with circles and arrows and a paragraph on the bottom of each one)?[/quote]Thank you Naked Ape. It took until the 2nd page of posts but I'm glad I wasn't the only one who picked up on the Arlo Guthrie reference.Fri, 06 Jul 2012 12:08:45 GMTScott D. Jacobsonhttp://www.sqlservercentral.com/Forums/Topic1136196-2881-1.aspxI know this is an old article but since it was recently republished I have to say that I hope and pray that folks read the discussion before they even think about using the code as is especially when it comes to the code that supposedly returns the correct name of the table and index. To be blunt, the code is incorrect for what it's advertised to do and you could end up deleting incorrect indexes if you use the code as is.Thu, 05 Jul 2012 18:16:55 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic1136196-2881-1.aspx@ Brian O'LearyPlease go back and read what I posted earlier in this thread and run the query I provided. I think I already showed where you may be mistaken.The data collection query in the article always returns table names from the current database due to how the OBJECT_NAME function is used, and it always returns index names from the current database, but it can associate them with index usage stats for different indexes on different tables in different databases. The association (JOIN) is on object_id and index_id. If object_id and index_id match across databases (I have several cases of this occurring on a lightly loaded SQL Server instance) then the query returns very misleading rows. The names of the tables and indexes in the other databases are irrelevant because the query does not see them.You have minimized (again) the inaccuracy of the data collection query in the article. Your "observations" are stated authoritatively, but I believe they are wrong.Fri, 08 Jul 2011 17:52:47 GMTBW_Torohttp://www.sqlservercentral.com/Forums/Topic1136196-2881-1.aspx[quote][b]BW_Toro (7/8/2011)[/b][hr]Where is the author? Where are the folks who defended the data collection query as harmless?Steven993 said the query is fine. I think I have shown it's not fine.Brian O'Leary pronounced the effect of the bug to be nothing worse than showing duplicate rows. I think I have shown the effect is much worse.Where is the discussion? Can a few more people weigh in to confirm or refute (with some evidence) the problem? The article is rated 4+ stars, but I'm afraid the core query is fundamentally flawed.[/quote]I highlighted in a previous post that the query is incorrect due to the [object_id] being unique only within a database, I am in no way "defending" anything, having said that I'm not trying to rip into anybody either. I noticed the query that performs the aggregates is grouping on name, so unless there are multiple indexes with the same name AND the same object_id across multiple databases it's likely to give you accurate enough results. (Caveat @ BW_Toro :-)) This statement is indended only as an observation and not an endorsement!) I personally would not use the data this produces to make any decisions.Fri, 08 Jul 2011 15:27:45 GMTBrian O'Learyhttp://www.sqlservercentral.com/Forums/Topic1136196-2881-1.aspx[quote][b]Steven993 (7/8/2011)[/b][hr][quote][b]Ninja's_RGR'us (7/8/2011)[/b][hr]It was like that in 2000 and I don't see why it should have changed since.[/quote]Did I said it changed ?[/quote]I was merely confirming the information.Fri, 08 Jul 2011 11:57:49 GMTNinja's_RGR'ushttp://www.sqlservercentral.com/Forums/Topic1136196-2881-1.aspx[quote][b]Ninja's_RGR'us (7/8/2011)[/b][hr]It was like that in 2000 and I don't see why it should have changed since.[/quote]Did I said it changed ?Fri, 08 Jul 2011 11:22:30 GMTSteven993http://www.sqlservercentral.com/Forums/Topic1136196-2881-1.aspx[quote][b]Steven993 (7/8/2011)[/b][hr]I was wrong.Even if I think I never saw 2 tables with the same object_id within an instance, Object_id is unique only within a database according to MSDN.[/quote]It was like that in 2000 and I don't see why it should have changed since.Fri, 08 Jul 2011 09:51:13 GMTNinja's_RGR'ushttp://www.sqlservercentral.com/Forums/Topic1136196-2881-1.aspxI was wrong.Even if I think I never saw 2 tables with the same object_id within an instance, Object_id is unique only within a database according to MSDN.Fri, 08 Jul 2011 08:41:01 GMTSteven993http://www.sqlservercentral.com/Forums/Topic1136196-2881-1.aspx[quote][b]BW_Toro (7/8/2011)[/b][hr]Where is the author? Where are the folks who defended the data collection query as harmless?[/quote]I'm with you on this one. I think the article should be fixed or removed. Unless there is some processing that the author did not share in the article that fixes the issue, you will indeed get incorrect results from the query. I'd hate for someone to use the code to "tune" their systems. They could end up dropping or keeping the wrong indexes.Can we please get an update from the author or editor here?!?!Thanks,MarcyFri, 08 Jul 2011 08:19:41 GMTMWisehttp://www.sqlservercentral.com/Forums/Topic1136196-2881-1.aspxWhere is the author? Where are the folks who defended the data collection query as harmless?Steven993 said the query is fine. I think I have shown it's not fine.Brian O'Leary pronounced the effect of the bug to be nothing worse than showing duplicate rows. I think I have shown the effect is much worse.Where is the discussion? Can a few more people weigh in to confirm or refute (with some evidence) the problem? The article is rated 4+ stars, but I'm afraid the core query is fundamentally flawed.Fri, 08 Jul 2011 07:20:06 GMTBW_Torohttp://www.sqlservercentral.com/Forums/Topic1136196-2881-1.aspxThis may be the link to the PerfStats scripts. This is from the MSDN blog:[url] http://blogs.msdn.com/b/psssql/archive/2007/02/21/sql-server-2005-performance-statistics-script.aspx [/url]I have not installed these scripts yet so I do not know if this is the same thing referenced in the article or not.Thu, 07 Jul 2011 13:26:29 GMTKWymorehttp://www.sqlservercentral.com/Forums/Topic1136196-2881-1.aspxI think the data aggregation query included in the article can produce wildly skewed results because of the bug in the data collection query. I think it could lead one to conclude an index is used very lightly/rarely when it's used very heavily/frequently. I think it could lead one to conclude an index is used very heavily/frequently when it's used very lightly/rarely. Some folks have dismissed the issue and said the data collection query is acceptable. I have to disagree because the query can produce very misleading results.Thu, 07 Jul 2011 08:49:13 GMTBW_Torohttp://www.sqlservercentral.com/Forums/Topic1136196-2881-1.aspxHi Ron, can you share with us the package?how do you clean the diffrent statistics, so on next run time, you will get only the data from the previous time?Thu, 07 Jul 2011 06:48:31 GMTpeleghttp://www.sqlservercentral.com/Forums/Topic1136196-2881-1.aspxRun the query below. It's a copy of the query in the article with two extra columns and an ORDER BY clause. You will very likely see rows where an index name is associated with correct index usage stats, but also index usage stats from a different index for a different table in a different database (even system databases). The extra columns reveal where the other index lives. The OBJECT_NAME function, without a database_id parameter, returns the object from the current database, fooling you into thinking the index usage stats are for one table when they are for another table in another database. I have no idea how the tool knows which set of index usage stats is correct. The tool could be showing an index as unused when it's used, or it could be showing an index as used when it's unused. It depends which row (for a given object and index) the tool uses for the index usage stats.[code="sql"]select OBJECT_NAME(S.[OBJECT_ID]) AS [OBJECT NAME], DB_NAME(S.DATABASE_ID), OBJECT_NAME(S.[OBJECT_ID],S.DATABASE_ID), I.[NAME] AS [INDEX NAME], USER_SEEKS, USER_SCANS, USER_LOOKUPS, USER_UPDATES, GetDate()FROM SYS.DM_DB_INDEX_USAGE_STATS AS S INNER JOIN SYS.INDEXES AS I ON I.[OBJECT_ID] = S.[OBJECT_ID] AND I.INDEX_ID = S.INDEX_IDORDER BY OBJECT_NAME(S.[OBJECT_ID]), I.[NAME][/code]Wed, 06 Jul 2011 14:15:19 GMTBW_Torohttp://www.sqlservercentral.com/Forums/Topic1136196-2881-1.aspxI believe that the query is incorrect and need to have the database filter (AND S.database_id=DB_ID()) added to it. I ran the query both with and without and ended up with different, meaning incorrect results. Will the author of the article please address this issue?Thanks,MWiseWed, 06 Jul 2011 11:52:03 GMTMWisehttp://www.sqlservercentral.com/Forums/Topic1136196-2881-1.aspx[quote][b]BW_Toro (7/6/2011)[/b][hr]I think the query as highlighted is an accurate representation. The effect of the problem is more than simply listing an index more than once, it can list an index more than once where some rows include index usage stats from other indexes. I have not followed through everything in the tool to see how it reacts to the misleading data, but it could potentially result in showing an index as unused when it's not.[/quote]It wouldnt show an index as used when its not, neither would it aggregate them, it simply has the [u][i]potential[/i][/u] to return the same row twice, giving the impression that things are "worse" than they really are. No query known to return inaccurate results can be an accurate representation of anything (I can argue that fact all day :-)).EDIT: After re reading the original post, these stats are in fact aggregated once the data has been collected in a central point.Wed, 06 Jul 2011 11:32:44 GMTBrian O'Learyhttp://www.sqlservercentral.com/Forums/Topic1136196-2881-1.aspxI think the query as highlighted is an accurate representation. The effect of the problem is more than simply listing an index more than once, it can list an index more than once where some rows include index usage stats from other indexes. I have not followed through everything in the tool to see how it reacts to the misleading data, but it could potentially result in showing an index as unused when it's not.Wed, 06 Jul 2011 11:20:09 GMTBW_Torohttp://www.sqlservercentral.com/Forums/Topic1136196-2881-1.aspx[quote][b]BW_Toro (7/6/2011)[/b][hr]Why is there not more concern being expressed when the query cited by peter-757102 (see page 1) is probably causing incorrect results from this tool? I posted a related query (see page 2) to try to cause others to investigate. I have seen people criticize matters of style with conviction in other threads, but only two of us are mentioning a probable bug here. Am I missing something? If so, please correct me. The explanation by Steven993 seems to fall short.[/quote]try [url=http://blog.sqlauthority.com/2008/10/03/sql-server-2008-find-if-index-is-being-used-in-database/]http://blog.sqlauthority.com/2008/10/03/sql-server-2008-find-if-index-is-being-used-in-database/[/url] by Pinal Dave. It does the same thing. This query gave me accurate enough data to start investigating.Duplicate indexes should be hit list number one. Pinal Dave also has scripts to locate duplicate indexes, and pairs of indexes that are contained by others that would probably be helpful to anyone reading this article.On the general idea in other comments that the unused indexes that was created originally should be left alone, I have yet to find any published text which advocates leaving unused non clustered indexes in a transactional table. Something by Microsoft or Oracle saying when we should leave them would be sufficient to sway me.Wed, 06 Jul 2011 11:15:26 GMTMadAdminhttp://www.sqlservercentral.com/Forums/Topic1136196-2881-1.aspxOh it wasn't intentional!? Google the lyrics to alices restaurant look for the part which is almost verbatim about the pictures with circles and arrows and a paragraph on the back of each one...You'll get a laugh!Wed, 06 Jul 2011 11:02:56 GMTdavid.sirbaskuhttp://www.sqlservercentral.com/Forums/Topic1136196-2881-1.aspxHave to ask whats Alice's Restaurant??Wed, 06 Jul 2011 10:55:24 GMTBrian O'Learyhttp://www.sqlservercentral.com/Forums/Topic1136196-2881-1.aspx[quote][b]BW_Toro (7/6/2011)[/b][hr]Why is there not more concern being expressed when the query cited by peter-757102 (see page 1) is probably causing incorrect results from this tool? I posted a related query (see page 2) to try to cause others to investigate. I have seen people criticize matters of style with conviction in other threads, but only two of us are mentioning a probable bug here. Am I missing something? If so, please correct me. The explanation by Steven993 seems to fall short.[/quote]The query as highlighted by peter-757102 is certainly wrong (of course it could be a copy paste error!). [object_id] is unique within a database, and sys.dm_db_index_usage_stats records index usage for all databases. So a database_id filter is required to produce a correct set.However, the effect of this particular error would be to return a index usage row [i]n[/i] times (slightly better than it not being returned at all), that doesnt change the fact it's potentially not being used (i.e. wasting disk space, cpu and (memory?) due to higher insert, update and maintenance overhead). On large heavily updated tables this can lead to noticeable performance problems.Which leads me to comment on a couple of comments posted that advocate simply removing or disabling them because nobody will notice. Firstly I would say if you try that on a large table with many concurrent users you could come unstuck. Secondly, in [u][i]my opinion[/i][/u], as DBA's our job is to provide the evidence / science to the business to let them decide (unless of course they have made the decsion to let you decide......often the case) the course of action they want to take. They are the ones who pay for it and use it!Wed, 06 Jul 2011 10:52:07 GMTBrian O'Learyhttp://www.sqlservercentral.com/Forums/Topic1136196-2881-1.aspxNice reference to Alice's Restaurant!Wed, 06 Jul 2011 08:30:24 GMTdavid.sirbaskuhttp://www.sqlservercentral.com/Forums/Topic1136196-2881-1.aspxWhy is there not more concern being expressed when the query cited by peter-757102 (see page 1) is probably causing incorrect results from this tool? I posted a related query (see page 2) to try to cause others to investigate. I have seen people criticize matters of style with conviction in other threads, but only two of us are mentioning a probable bug here. Am I missing something? If so, please correct me. The explanation by Steven993 seems to fall short.Wed, 06 Jul 2011 07:08:43 GMTBW_Torohttp://www.sqlservercentral.com/Forums/Topic1136196-2881-1.aspx[quote][b]scottm30 (7/5/2011)[/b][hr]The moment they are dropped, users will believe the system is running slower. Furthermore any application problems will be initially blamed on the missing indexes.[/quote]So don't tell them when you drop them. Do the usual CYA stuff and tell your boss and explain to him exactly why you want to be sneaky!Remember Schrödinger's cat and the Observer-expectancy effect! If they don't know they are being observed then they'll behave as normal. They'll still be complaining, but no more than normal.Either that or drop the indexes, and the say that you have re-added them after the complaints come but leave them disabled.There's more than one way to fool a user!Wed, 06 Jul 2011 00:40:52 GMTToby Harmanhttp://www.sqlservercentral.com/Forums/Topic1136196-2881-1.aspx[quote][b]Toby Harman (7/5/2011)[/b][hr]Good article covering a common situation. Indexes are created because we assume they will be the access path (as pointed out in the DateTime discussion) but because they either aren't covering the entire query result or the result set is too large, we end up with indexes that are never used.As to the "It will get slow if we drop these indexes" discussion, my only comment to that is "We can add them back". It's not like they purging data which cannot be recovered.[/quote]The moment they are dropped, users will believe the system is running slower. Furthermore any application problems will be initially blamed on the missing indexes.Tue, 05 Jul 2011 19:30:14 GMTscottm30http://www.sqlservercentral.com/Forums/Topic1136196-2881-1.aspx[quote][b]Toby Harman (7/5/2011)[/b][hr]Good article covering a common situation. Indexes are created because we assume they will be the access path (as pointed out in the DateTime discussion) but because they either aren't covering the entire query result or the result set is too large, we end up with indexes that are never used.As to the "It will get slow if we drop these indexes" discussion, my only comment to that is "We can add them back". It's not like they purging data which cannot be recovered.[/quote]Just to humor them you could disable them so that you can "easily" reenable them in the future... they probably don't know the difference anyways :w00t:.Tue, 05 Jul 2011 17:03:58 GMTNinja's_RGR'ushttp://www.sqlservercentral.com/Forums/Topic1136196-2881-1.aspxGood article covering a common situation. Indexes are created because we assume they will be the access path (as pointed out in the DateTime discussion) but because they either aren't covering the entire query result or the result set is too large, we end up with indexes that are never used.As to the "It will get slow if we drop these indexes" discussion, my only comment to that is "We can add them back". It's not like they purging data which cannot be recovered.Tue, 05 Jul 2011 17:01:22 GMTToby Harmanhttp://www.sqlservercentral.com/Forums/Topic1136196-2881-1.aspx[quote][b]Brian O'Leary (7/5/2011)[/b][hr][quote][b]doranmackay (7/5/2011)[/b][hr]For me it was quick to find the root cause.So when you have stuff like "where [datetimecolumn] >=getdate()" (if datetimecolumn is indexed)the optimizer says "You know what, range scan on date time is so slow jumping between index and record and I have to do it a milliion times, let me rather do a table scan, as there is no jumping between index and record".It then avoids the index at all costs cause it would be, well, costly.[/quote]Thats not true. If your nonclustered index is not covering you will still need to do a bookmark or rid lookup (thats the point of covering) regardless of the data type. The descision to perform a range scan by the optimizer is to do with cardinality rather than the use of a datetime data type.For example, run this script against the AdventureWorks database and you will see an index seek based on the date filter, however if you change the date from '20040703' to '20040702' the optimzer will choose a clustered index scan.[/quote]Correct, I should have included that the specific indexes existed on a highly transactional database.DateTime has low cardinality when only using the date portion,and based on the size of the result set estimated in adventureworks, I would expect successful usage of datetime index since the results from smaller tables is few.Do the same test, where you have 1000 unique records being created per hour, and modified, with a couple of joins happening and due to the row count of the tables, a date filter will result in too much records. In those situations, inserts and updates cost because the likelihood of benefiting from a datetime index is zero unless you can specify the datetime down to the millisecond and use [b]where x = '20040703.123456'[/b].Tue, 05 Jul 2011 16:15:43 GMTMadAdminhttp://www.sqlservercentral.com/Forums/Topic1136196-2881-1.aspx[quote][b]doranmackay (7/5/2011)[/b][hr]For me it was quick to find the root cause.So when you have stuff like "where [datetimecolumn] >=getdate()" (if datetimecolumn is indexed)the optimizer says "You know what, range scan on date time is so slow jumping between index and record and I have to do it a milliion times, let me rather do a table scan, as there is no jumping between index and record".It then avoids the index at all costs cause it would be, well, costly.[/quote]Thats not true. If your nonclustered index is not covering you will still need to do a bookmark or rid lookup (thats the point of covering) regardless of the data type. The descision to perform a range scan by the optimizer is to do with cardinality rather than the use of a datetime data type.For example, run this script against the AdventureWorks database and you will see an index seek based on the date filter, however if you change the date from '20040703' to '20040702' the optimzer will choose a clustered index scan.DBCC FREEPROCCACHEGoUSE AdventureWorksGOIF EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[Production].[WorkOrder]') AND name = N'IX_StartDate') DROP INDEX [IX_StartDate] ON [Production].[WorkOrder]GOCREATE NONCLUSTERED INDEX [IX_StartDate] ON [Production].[WorkOrder] ( [StartDate] ASC)GOSELECT * FROM Production.WorkOrderWHERE StartDate >= '20040703'GOIF EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[Production].[WorkOrder]') AND name = N'IX_StartDate') DROP INDEX [IX_StartDate] ON [Production].[WorkOrder]Tue, 05 Jul 2011 14:41:37 GMTBrian O'Learyhttp://www.sqlservercentral.com/Forums/Topic1136196-2881-1.aspxInteresting reading. Was that a deliberate reference to Alices Restaurant, Massacre (twenty-eight color graphs with circles and arrows and a paragraph on the bottom of each one)?Tue, 05 Jul 2011 14:28:49 GMTNaked Apehttp://www.sqlservercentral.com/Forums/Topic1136196-2881-1.aspxSteven993,Are you sure the query cited by peter-757102 is fine? The object_id values are not unique across databases. When I run the query below I get numerous cases of the same object_id and index_id in different databases. I think checking the database_id is necessary to avoid associating index usage stats with the wrong object.[code="sql"] SELECT object_id , index_id , MIN(DB_NAME(database_id) + '.' + OBJECT_NAME(object_id,database_id)) , MAX(DB_NAME(database_id) + '.' + OBJECT_NAME(object_id,database_id)) , COUNT(*) FROM sys.dm_db_index_usage_statsGROUP BY object_id , index_id HAVING COUNT(*) > 1ORDER BY object_id , index_id[/code]Tue, 05 Jul 2011 13:57:44 GMTBW_Torohttp://www.sqlservercentral.com/Forums/Topic1136196-2881-1.aspxDid you know it is possible to create duplicate indexes on one table, both non clustered?One will never be used, but will cost in inserts and updates.What reason would there be to not immediately delete the unused index?If it is not used after 6 months, and statistics are up to date, then only due to irrational fear would they not be deleted.Tue, 05 Jul 2011 13:49:50 GMTMadAdminhttp://www.sqlservercentral.com/Forums/Topic1136196-2881-1.aspxFor me it was quick to find the root cause.I found many non clustered indexes included dateTime as part of a covering index. 6 indexes on the main mothershiptable of our app.Problem is that dateTime is not an int.Chances of getting the exact time is very bad so doesnt work with "=".They work best when clustered on datetime , i.e. ">=; <" etc. (if clustered on a column that is ever increasing and never changing.)So when you have stuff like "where [datetimecolumn] >=getdate()" (if datetimecolumn is indexed)the optimizer says "You know what, range scan on date time is so slow jumping between index and record and I have to do it a milliion times, let me rather do a table scan, as there is no jumping between index and record".It then avoids the index at all costs cause it would be, well, costly.The next question should be, who actually allowed an index to be created including datetime on a nonclustered index.I then ran a query finding all nonclustered indexes which include datetime and surprise surprise, they had millions of updates and no userscan, no index seek, no index scan, rid lookup(heap).This is fine if it is the PK that never gets queried on, and links to other tables using FK's.But I am a mere tester so my opinion is worth less than squat. Even with proof.Tue, 05 Jul 2011 13:43:44 GMTMadAdminhttp://www.sqlservercentral.com/Forums/Topic1136196-2881-1.aspx[quote][b]tom.groszko (7/5/2011)[/b][hr]The presense of an index is not necessarly a problem even if the index is not used. In your article you failed to state what problem you were trying to solve. If index analaysis were as simple as looking at the numbers SSMS would have a button to delete them or perhaps just delete them on it's own.Your have apparently not bothered to speak with the develpers to find out why those indexes are there and then taken the opportunity to educate so you won't find the same thing in the next project to hit production.I suspect those who refused to imediately delete those indexes saved your job.[/quote]I'm mostly with you on this, however the reply given to the poster "you cant delete them, they [b][u]MIGHT[/u][/b] be used in the future" coupled with the fact there are 1800 tables and only 392 indexes (unless of course 1408 tables are used for ETL or some such purpose.....although I can see some problems there as well) seem to suggest a lack of understanding towards indexing in general. Of course this doesnt mean they can simply be deleted, they might exist for year end reporting etc.Tue, 05 Jul 2011 12:24:31 GMTBrian O'Learyhttp://www.sqlservercentral.com/Forums/Topic1136196-2881-1.aspxNice article.Tue, 05 Jul 2011 11:10:07 GMTSQLRNNRhttp://www.sqlservercentral.com/Forums/Topic1136196-2881-1.aspxThe presense of an index is not necessarly a problem even if the index is not used. In your article you failed to state what problem you were trying to solve. If index analaysis were as simple as looking at the numbers SSMS would have a button to delete them or perhaps just delete them on it's own.Your have apparently not bothered to speak with the develpers to find out why those indexes are there and then taken the opportunity to educate so you won't find the same thing in the next project to hit production.I suspect those who refused to imediately delete those indexes saved your job.Tue, 05 Jul 2011 10:19:00 GMTtom.groszkohttp://www.sqlservercentral.com/Forums/Topic1136196-2881-1.aspxIsn't the term is Accidental DBA and not volunteer DBA?Tue, 05 Jul 2011 08:57:31 GMTbdaveyhttp://www.sqlservercentral.com/Forums/Topic1136196-2881-1.aspxI think it is better to put "s.database_id = db_id()" in join statement ... INNER JOIN SYS.INDEXES AS I ON I.[OBJECT_ID] = S.[OBJECT_ID] AND I.INDEX_ID = S.INDEX_ID and S.database_id = db_id()Tue, 05 Jul 2011 07:20:10 GMTOgnjen Kovacevichttp://www.sqlservercentral.com/Forums/Topic1136196-2881-1.aspxActually object_id in both views sys.dm_db_index_usage_stats and sys.indexes refers to the index's table.Check MSDN for column description of each views.So it's fine. ;-)Tue, 05 Jul 2011 07:07:04 GMTSteven993http://www.sqlservercentral.com/Forums/Topic1136196-2881-1.aspxMaybe I am missing something or lack some releveant experience in this area, but i see the following code:[code="plain"]FROM SYS.DM_DB_INDEX_USAGE_STATS AS S INNER JOIN SYS.INDEXES AS I ON I.[OBJECT_ID] = S.[OBJECT_ID] AND I.INDEX_ID = S.INDEX_ID[/code]To me, it seems like joining between these two tables is an error.Where [b]SYS.DM_DB_INDEX_USAGE_STATS[/b] works over all databases and has a [b]database_id[/b] column, [b]SYS.INDEXES[/b] does not.Without explicit filtering on the database name, you match index IDs from other databases to the current database, and thus gather the wrong usage statistics. Anyway, please check to see if I am right!PS.I expected the following where clause to be present and the code executed for each database on a server:[code="plain"]where s.database_id = db_id()[/code]Tue, 05 Jul 2011 06:17:38 GMTpeter-757102