Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

is there an accepted ratio of reads to writes that makes an index worthwhile or is it less cut and dried than that?

I am using this:

WITH    UnusedIndexQuery ( Object_ID, ObjectName, IndexName, Index_ID, Reads, Writes, Rows )
          AS ( SELECT
                s.object_id ,
                objectname = OBJECT_NAME(s.OBJECT_ID) ,
                indexname = i.name ,
                i.index_id ,
                reads = user_seeks + user_scans + user_lookups ,
                writes = user_updates ,
                p.rows
               FROM
                sys.dm_db_index_usage_stats s
               JOIN 
                sys.indexes i
               ON
                i.index_id = s.index_id
                AND s.OBJECT_ID = i.OBJECT_ID
               JOIN 
                sys.partitions p
               ON
                p.index_id = s.index_id
                AND s.OBJECT_ID = p.OBJECT_ID
               WHERE
                OBJECTPROPERTY(s.OBJECT_ID, 'IsUserTable') = 1
                AND s.database_id = DB_ID()
                AND i.type_desc = 'nonclustered'
                AND i.is_primary_key = 0
                AND i.is_unique_constraint = 0
                AND p.rows > 10000
             ),
        IndexSizes ( schemaname, tablename, object_id, indexname, index_id, indextype, indexsizekb, indexsizemb, indexsizegb )
          AS ( SELECT
                sys_schemas.name AS SchemaName ,
                sys_objects.name AS TableName ,
                sys_objects.[object_id] AS object_id ,
                sys_indexes.name AS IndexName ,
                sys_indexes.index_id AS index_id ,
                sys_indexes.type_desc AS IndexType ,
                partition_stats.used_page_count * 8 AS IndexSizeKB ,
                CAST(partition_stats.used_page_count * 8 / 1024.00 AS DECIMAL(10,
                                                              3)) AS IndexSizeMB ,
                CAST(partition_stats.used_page_count * 8 / 1048576.00 AS DECIMAL(10,
                                                              3)) AS IndexSizeGB
               FROM
                sys.dm_db_partition_stats partition_stats
               INNER JOIN sys.indexes sys_indexes
               ON
                partition_stats.[object_id] = sys_indexes.[object_id]
                AND partition_stats.index_id = sys_indexes.index_id
                AND sys_indexes.type_desc <> 'HEAP'
               INNER JOIN sys.objects sys_objects
               ON
                sys_objects.[object_id] = partition_stats.[object_id]
               INNER JOIN sys.schemas sys_schemas
               ON
                sys_objects.[schema_id] = sys_schemas.[schema_id]
                AND sys_schemas.name <> 'SYS'
             )
    SELECT
        [IndexSizes].[tablename] ,
        [IndexSizes].[indexname] ,
        [IndexSizes].[indextype] ,
        [IndexSizes].[indexsizekb] ,
        [IndexSizes].[indexsizemb] ,
        [IndexSizes].[indexsizegb] ,
        UnusedIndexQuery.Reads ,
        UnusedIndexQuery.Writes ,
        CAST(CASE WHEN [Reads] = 0 THEN 1
                  ELSE [Reads]
             END / CASE WHEN [Writes] = 0 THEN 1
                        ELSE writes
                   END AS NVARCHAR(8)) + ':1' AS [Benefit Ratio (Read:Write)] ,
        UnusedIndexQuery.[Rows]
    FROM
        UnusedIndexQuery
    INNER JOIN IndexSizes
    ON  UnusedIndexQuery.object_id = IndexSizes.object_id
        AND UnusedIndexQuery.index_id = IndexSizes.index_id
    ORDER BY
        CASE WHEN [Reads] = 0 THEN 1
             ELSE [Reads]
        END / CASE WHEN [Writes] = 0 THEN 1
                   ELSE writes
              END ,
        reads ,
        [Writes] DESC ,
        [indexsizemb] DESC

to get an idea of the state of the benefit of my indexes.

At the two ends of the results I am clear - 1,000,000 reads and 0 writes = good index to speed up data retrieval, 1,000,000 writes and 0 reads means we are maintaining an index for zero reference.
What I am not certain on is where the activity is shown as more balanced - where do I make the cut and start dropping indexes?

thanks

Jonathan

share|improve this question
    
That's one mouthful of an SQL Query! –  Josh Oct 14 '09 at 22:58
    
I cant take more than maybe 5% credit for the TSQL - I have adapted it from the original here : sqlsolace.blogspot.com/2009/08/… –  Fatherjack Oct 15 '09 at 7:42

2 Answers 2

up vote 2 down vote accepted

I don't think it makes sense to base the decision on the number of reads/writes alone (unless of course you reads==0, but then why do you have the table? :-)).

Consider that:

  • even if there are few reads, these may be extremely time-consuming without the index
  • the reads may be more time-critical than the writes, so an index might be worth it despite reduced write performance
  • the write performance need not necessarily suffer; I would assume most modern DBMS can delay updating the index until it's needed, so e.g. many INSERTs in sequence should only cause one index update

In short, like always, the only advice is: profile before optimizing. There is no easy shortcut :-/.

share|improve this answer
    
There are certainly rows returned from the quoted TSQL that have 0 reads. This only shows that the index wasnt used doesnt it? The request for data was necessary but that particular index wasnt used ... ? The table itself is vital but the indexes seem to have been created along the lines of "Well it looked like a good idea at the time."! The 0 read indexes are certainly on the list to remove. What I am after is how much further should I go - to the 1:1 point..? –  Fatherjack Oct 14 '09 at 14:35
    
Ah sorry, of course the reads refer to index usage. –  sleske Oct 14 '09 at 15:10

What are you trying to achieve? are you trying to improve i/o performance? are you running low on disk space? Premature optimization is the root of all evil!

Stick with the quick wins like 0 reads & 100,000,000 writes. Everything else is a trade off. If your server has headroom, but no disk space, then start working backwards from the lowest ratio of reads to writes and keep an eye on the performance.

It may be wiser to explore other alternatives such as optimizing the procedures/queries, adding page compression, adding disk space/RAM ect.

share|improve this answer
    
Nick, wise words for sure. Once I get the chance to go back to the database (later this pm or tomorrow) then the 0 reads indexes are being deleted without question. My intention is to optimise the database, those indexes are deadweight - about 2GB of deadweight. 2GB that we backup, replicate, mirror, and so on. We have HDD space, we are coping with bandwidth, backup times, CPU etc but if we are doing something that is a waste of server activity I'd prefer not to. Largely I wanted to satisfy my interest in whether someone knew/used a given ratio as their keep/drop point that I could adopt –  Fatherjack Oct 15 '09 at 12:44

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.