Indexes, Indexes, Indexes

  • GreyBeard

    Mr or Mrs. 500

    Points: 535

    Comments posted to this topic are about the item Indexes, Indexes, Indexes

  • Henrik Staun Poulsen

    SSCertifiable

    Points: 6339

    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

    http://www.stovi.com

  • kll

    SSC Veteran

    Points: 250

    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

  • peter-757102

    SSCertifiable

    Points: 6877

    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()

  • Steven993

    Mr or Mrs. 500

    Points: 588

    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. 😉

  • Ognjen Kovacevic

    SSC-Addicted

    Points: 436

    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()

  • bdavey

    Ten Centuries

    Points: 1367

    Isn't the term is Accidental DBA and not volunteer DBA?

  • tom.groszko

    SSC Enthusiast

    Points: 116

    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.

  • SQLRNNR

    SSC Guru

    Points: 281210

    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

  • Brian O'Leary

    SSChasing Mays

    Points: 623

    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

  • MadAdmin

    SSChampion

    Points: 11260

    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.

    Catch-all queries done right [/url]
    Gail Shaw's Performance Blog[/url]

  • MadAdmin

    SSChampion

    Points: 11260

    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.

    Catch-all queries done right [/url]
    Gail Shaw's Performance Blog[/url]

  • BW_Toro

    SSC Veteran

    Points: 260

    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

  • Naked Ape

    Ten Centuries

    Points: 1044

    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

  • Brian O'Leary

    SSChasing Mays

    Points: 623

    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 43 total)

You must be logged in to reply to this topic. Login to reply