July 4, 2011 at 9:19 pm
Comments posted to this topic are about the item Indexes, Indexes, Indexes
July 4, 2011 at 11:47 pm
Hi Ron,
I have seen this situation too.
But I've also found that even if the USER_Scans column was zero, the SYSTEM_scans was not zero.
I'm not sure why the System_Scans column exists. BOL only says: "for operations caused by internally generated queries, such as scans for gathering statistics."
But I suspect that they could also list the situation where it was used for Uniqueness???
Best regards,
Henrik Staun Poulsen
July 5, 2011 at 1:04 am
While an abundance of unused indices are certainly worth taking care of, ther is an equally important task, namely the missing indices. This topic is covered here (external link).
But thank you very much for the heads up for this important area, and also thanks for showing your setup. I just know that these usage statistics disappear when a server is reset, which might happen from time to time.
Best regards,
Keld Laursen
Knowledge Centre for Agriculture
Denmark
July 5, 2011 at 6:17 am
Maybe I am missing something or lack some releveant experience in this area, but i see the following code:
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
To me, it seems like joining between these two tables is an error.
Where SYS.DM_DB_INDEX_USAGE_STATS works over all databases and has a database_id column, SYS.INDEXES 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:
where
s.database_id = db_id()
July 5, 2011 at 7:07 am
Actually 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. 😉
July 5, 2011 at 7:20 am
I 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()
July 5, 2011 at 8:57 am
Isn't the term is Accidental DBA and not volunteer DBA?
July 5, 2011 at 10:19 am
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.
July 5, 2011 at 11:10 am
Nice article.
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
July 5, 2011 at 12:24 pm
tom.groszko (7/5/2011)
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.
I'm mostly with you on this, however the reply given to the poster "you cant delete them, they MIGHT 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.
MCITP SQL Server 2005/2008 DBA/DBD
July 5, 2011 at 1:43 pm
For 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.
July 5, 2011 at 1:49 pm
Did 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.
July 5, 2011 at 1:57 pm
Steven993,
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.
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_stats
GROUP BY object_id
, index_id
HAVING COUNT(*) > 1
ORDER BY object_id
, index_id
July 5, 2011 at 2:28 pm
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)?
Chris
July 5, 2011 at 2:41 pm
doranmackay (7/5/2011)
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.
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 FREEPROCCACHE
Go
USE AdventureWorks
GO
IF 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]
GO
CREATE NONCLUSTERED INDEX [IX_StartDate] ON [Production].[WorkOrder]
(
[StartDate] ASC
)
GO
SELECT
*
FROM Production.WorkOrder
WHERE StartDate >= '20040703'
GO
IF 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]
MCITP SQL Server 2005/2008 DBA/DBD
Viewing 15 posts - 1 through 15 (of 42 total)
You must be logged in to reply to this topic. Login to reply