Get Read-to-Write Ratio for All Indexes

Here's a quick script to help you quantify how useful your indexes are, in the sense of how much they're used to speed up queries, compared to how much work it is for SQL Server to maintain them during inserts, updates, and deletes. This only reflects the usage since the last SQL Server boot.

-- Displays the read-to-update ratio of all indexes.  Good for finding ones that may not be needed.
DECLARE @dbid INT = DB_ID(DB_NAME())

; WITH cteUser AS
(
    SELECT object_id,
           index_id,
           user_seeks + user_scans + user_lookups   AS 'User Reads',
           user_updates                             AS 'User Updates'
      FROM sys.dm_db_index_usage_stats   
     WHERE database_id = @dbid
)
,
cteSystem AS
(
    SELECT object_id,
           index_id,
           system_seeks + system_scans + system_lookups   AS 'System Reads',
           system_updates                                 AS 'System Updates'
      FROM sys.dm_db_index_usage_stats   
     WHERE database_id = @dbid
)
,
cteTotal AS
(
    SELECT u.object_id,
           u.index_id,
           [User Reads]   + [System Reads]     AS 'Total Reads',
           [User Updates] + [System Updates]   AS 'Total Updates'
      FROM cteUser     u
      JOIN cteSystem   s
        ON u.object_id = s.object_id
       AND u.index_id  = s.index_id
)
,
cteReadToUpdateRatio AS
(
    SELECT object_id,
           index_id,
           CONVERT(NVARCHAR,
                  CONVERT(MONEY, ISNULL(
                                        CAST([Total Reads] AS REAL)
                                        /
                                        NULLIF([Total Updates], 0.0)
                                        , [Total Reads]
                                        )
                               , 1
                         )
                  )   AS 'Read-to-Update Ratio'
                  
      FROM cteTotal
)
SELECT OBJECT_SCHEMA_NAME(i.object_id)                                                                  AS 'Schema Name',
       OBJECT_NAME(i.object_id)                                                                         AS 'Table Name',
       i.name                                                                                           AS 'Index Name',
       REPLACE(CONVERT(NVARCHAR, CONVERT(MONEY, u.[User Reads]),                      1), '.00', '')    AS 'User Reads',                   
       REPLACE(CONVERT(NVARCHAR, CONVERT(MONEY, u.[User Updates]),                    1), '.00', '')    AS 'User Updates',                 
       REPLACE(CONVERT(NVARCHAR, CONVERT(MONEY, s.[System Reads]),                    1), '.00', '')    AS 'System Reads',                 
       REPLACE(CONVERT(NVARCHAR, CONVERT(MONEY, s.[System Updates]),                  1), '.00', '')    AS 'System Updates',               
       REPLACE(CONVERT(NVARCHAR, CONVERT(MONEY, t.[Total Reads]),                     1), '.00', '')    AS 'Total Reads',                
       REPLACE(CONVERT(NVARCHAR, CONVERT(MONEY, t.[Total Updates]),                   1), '.00', '')    AS 'Total Updates',                
                                                r.[Read-to-Update Ratio]                                AS 'Read-to-Update Ratio',         
       REPLACE(CONVERT(NVARCHAR, CONVERT(MONEY, t.[Total Reads] + t.[Total Updates]), 1), '.00', '')    AS 'Total Reads + Total Updates'
  FROM cteUser                        u
  JOIN cteSystem                      s
    ON u.object_id = s.object_id
   AND u.index_id  = s.index_id
  JOIN cteTotal                       t
    ON u.object_id = t.object_id
   AND u.index_id  = t.index_id
  JOIN cteReadToUpdateRatio           r
    ON u.object_id = r.object_id
   AND u.index_id  = r.index_id
  JOIN sys.indexes                    i
    ON u.object_id = i.object_id
   AND u.index_id  = i.index_id
  JOIN sys.objects                    o
    ON u.object_id = o.object_id
 WHERE OBJECTPROPERTY(o.object_id, 'IsUserTable') = 1
   AND i.is_primary_key = 0
 ORDER BY CAST(r.[Read-to-Update Ratio] AS REAL)

The results appear in "least useful" index first. Be sure to read and test this script before using in a Production environment.

How to Hide Metadata Information Using Extended Properties

Ok, a more honest title for this post might be, 'How to Clean Up the Mess After You've Hosed the Extended Properties on Primary Key Constraints', but that wouldn't fit.

What I was trying to accomplish is a topic for another post. But while grumbling loudly while writing the code to fix what I had inadvertently done, I realized it could be used to "hide" information about tables. I say "hide", in quotes, because anyone with access to Books Online could find it easily. This is really just a way of storing information about tables, and not having it appear in SQL Server Management Studio (SSMS).

What I ran into involves the difference between primary key constraints and primary key indexes. The most important distinction between them is "existence"; there's no such thing as a primary key index. If you're confused, keep reading. If you've heard this story already, skip ahead a bit. Simply put, when someone is talking about "primary key indexes", they're using verbal shorthand for, "the index that the primary key constraint uses to enforce uniqueness." (As far as I know, this kind of index has no specific name, which is good, because if if did, we'd all confuse it with "primary key constraint", so we'd be in the same boat we are now, but without the ability to abruptly clarify things by saying, "but there's no such thing as a whatever-it-is." We should count our blessings.) To sum up, when it comes to primary keys, we have a "primary key constraint", and a unique index of some sort to help it. (We won't be getting into the whole clustered vs. non-clustered thing here.)

Now we're ready to talk about setting extended properties on primary key constraints. First we create a little test table:

CREATE TABLE MyTable
(
    MyPkColumn INT NOT NULL,
    CONSTRAINT PK_MyTable PRIMARY KEY (MyPkColumn) 
) 

And now we add an extended property to the PK_MyTable constraint, as so:

EXEC sys.sp_addextendedproperty 
    @name       = N'Extended property for PK_MyTable constraint'
  , @value      = N'This is the PK for MyTable'
  , @level0type = N'SCHEMA'
  , @level0name = N'dbo'
  , @level1type = N'TABLE'
  , @level1name = N'MyTable'
  , @level2type = N'CONSTRAINT'
  , @level2name = N'PK_MyTable'

Once added, this extended property will appear in SSMS, as expected. Oddly, it appears on the index-the-primary-key-constraint-uses, not on the constraint itself. (And no, I'm not suggesting you create tables in the master database.) So far, so good. But given that the "slot" for the index is already taken, and knowing what we do about about the non-existence of "primary key indexes", what happens if we do this?

EXEC sys.sp_addextendedproperty 
    @name       = N'Secret extended property for PK_MyTable index'
  , @value      = N'This is the PK index for MyTable'
  , @level0type = N'SCHEMA'
  , @level0name = N'dbo'
  , @level1type = N'TABLE'
  , @level1name = N'MyTable'
  , @level2type = N'INDEX'
  , @level2name = N'PK_MyTable'

Ideally (I think anyway) this should generate an error: we're adding an extended property to a CONSTRAINT, but setting @level2name to INDEX. What it actually does is create the extended property as if there really were such a thing as a "primary key index". Interestingly, but perhaps not surprisingly, this new extended property doesn't show up (at least not anywhere I can find) in SSMS. So how do we know it's there? We use the system views, of course!

-- Emit code to drop all extended properties on "primary key indexes". 

SELECT ep.major_id
     , ep.minor_id
     , s.name           AS 'Schema Name'
     , t.name           AS 'Table Name'
     , i.name           AS 'Index Name'
     , ep.name          AS 'EP Name'
     , ep.value         AS 'EP Value'
       
     , 'USE ' + DB_NAME() + '   ' +
       'EXEC sys.sp_dropextendedproperty '
            + '@name = '                                 +  '''' +  ep.name  + ''', '
            + '@level0type = ''SCHEMA'', @level0name = ' + quotename(s.name) + ', ' 
            + '@level1type = ''TABLE'',  @level1name = ' + quotename(t.name) + ', '
            + '@level2type = ''INDEX'',  @level2name = ' + quotename(i.name)   AS 'T-SQL'

  FROM sys.extended_properties    ep
  JOIN sys.indexes                i
    ON ep.major_id = i.object_id
   AND ep.minor_id = i.index_id
  JOIN sys.tables                 t
    ON i.object_id = t.object_id
  JOIN sys.schemas                s
    ON t.schema_id = s.schema_id
 WHERE ep.class_desc    = 'INDEX'
   AND i.is_primary_key = 1
 ORDER BY s.name, t.name, i.name, ep.name

The above displays the offending extended properties on the current database; the last column contains the code necessary to drop each one; for example:

EXEC sys.sp_dropextendedproperty 
    @name       = 'Secret extended property for PK_MyTable index', 
    @level0type = 'SCHEMA', 
    @level0name = [dbo], 
    @level1type = 'TABLE',  
    @level1name = [MyTable], 
    @level2type = 'INDEX',  
    @level2name = [PK_MyTable]
 

Additional Reading

• Modifying and deleting extended properties (sqlservercentral.com)
• Extracting Extended Properties (sqlservercentral.com)
• Industrial-strength database documentation using Extended Properties (sqlservercentral.com)

What About "Under-Used" Indexes?

We've all spent time looking for unused indexes (right?), but today my boss asked about under-used indexes. Now, I wrote a script a while ago to find completely unused indexes, but finding indexes that were only being used a little was just different enough to require a new script.

The script below displays all indexes, with the least-used (including unused) ones first, and totals for user and system reads and updates. I figured having the ratio between reads and updates might be useful, since if it takes more updates to maintain the index than it gets used, you might want to investigate its cost-benefit.

Some caveats:

• If your server just rebooted, these numbers probably won't mean a lot; wait for at least a full day to allow time for the indexes to be read or written to.
• Also, don't forget about periodic events like end-of-month processing. You don't want a phone call at 12:15 am on the first of the month when it turns out that index was needed after all.
• Just because it costs more to maintain an index doesn't mean it's not still worth having. The cost of maintaining the index is probably spread out over a long period of time; the benefit to a waiting user of a query that takes two seconds instead of five minutes to run might outweigh that cost. It depends on your particular environment.
• Note that indexes that support primary key constraints are excluded, as I'm pretty sure I'm not going to ever want to drop them!

Finally, consider disabling any under-used indexes you find, rather than just dropping them. If it turns out the index was important, you'll still have to rebuild the index, but at least the DDL code will be there already.

-- Display unused and under-used indexes. 

DECLARE @dbid INT = DB_ID(DB_NAME())

; WITH cteUser AS
(
    SELECT object_id,
           index_id,
           user_seeks + user_scans + user_lookups   AS 'User Reads',
           user_updates                             AS 'User Updates'
      FROM sys.dm_db_index_usage_stats   
     WHERE database_id = @dbid
)
,
cteSystem AS
(
    SELECT object_id,
           index_id,
           system_seeks + system_scans + system_lookups   AS 'System Reads',
           system_updates                                 AS 'System Updates'
      FROM sys.dm_db_index_usage_stats   
     WHERE database_id = @dbid
)
,
cteTotal AS
(
    SELECT u.object_id,
           u.index_id,
           [User Reads]   + [System Reads]     AS 'Total Reads',
           [User Updates] + [System Updates]   AS 'Total Updates'
      FROM cteUser     u
      JOIN cteSystem   s
        ON u.object_id = s.object_id
       AND u.index_id  = s.index_id
)
,
cteReadToUpdateRatio AS
(
    SELECT object_id,
           index_id,
           CONVERT(NVARCHAR,
                  CONVERT(MONEY, ISNULL(
                                        CAST([Total Reads] AS REAL)
                                        /
                                        NULLIF([Total Updates], 0.0)
                                        , [Total Reads]
                                        )
                               , 1
                         )
                  )   AS 'Read-to-Update Ratio'
                  
      FROM cteTotal
)
SELECT OBJECT_NAME(i.object_id)               AS 'Table Name',
       i.name                                 AS 'Index Name',
       u.[User Reads],
       u.[User Updates],
       s.[System Reads],
       s.[System Updates],
       t.[Total Reads],
       t.[Total Updates],
       r.[Read-to-Update Ratio],
       t.[Total Reads] + t.[Total Updates]    AS 'TOTAL READS + UPDATES'
  FROM cteUser                        u
  JOIN cteSystem                      s
    ON u.object_id = s.object_id
   AND u.index_id  = s.index_id
  JOIN cteTotal                       t
    ON u.object_id = t.object_id
   AND u.index_id  = t.index_id
  JOIN cteReadToUpdateRatio           r
    ON u.object_id = r.object_id
   AND u.index_id  = r.index_id
  JOIN sys.indexes                    i
    ON u.object_id = i.object_id
   AND u.index_id  = i.index_id
  JOIN sys.objects                    o
    ON u.object_id = o.object_id
 WHERE OBJECTPROPERTY(o.object_id, 'IsUserTable') = 1
   AND i.is_primary_key = 0
   --AND (t.[Total Reads] = 0                                  -- These definitely need to be dropped. 
   -- OR CAST(r.[Read-to-Update Ratio] AS REAL) < 1.0)         -- These may need to be dropped. 
 ORDER BY CAST(r.[Read-to-Update Ratio] AS REAL)

A Clustered Index Does Not Guarantee Order

Learned something the hard way recently: just because a table has a clustered index doesn't mean a SELECT without an ORDER BY will return the rows in order. For example, given this table:

CREATE TABLE MyTable
(
    ID  INT IDENTITY (1, 1)  NOT NULL
        CONSTRAINT PK_tblMyTable PRIMARY KEY CLUSTERED (ID)
)

... if you say:

SELECT * FROM MyTable

... then the rows will be returned in undefined (that is, random) order.

"Clustering", I've learned, is a physical construct, not a logical one. The rows are laid out in physical order on the hard drive, but SQL Server is free (in the abscence of an ORDER BY clause) to return them in whatever order it thinks most efficient.

As developers, we don't usually see this behavior, because we tend to have fresh, well-maintained tables. In fact, I only discovered it in a customer's database that hadn't had any maintenance done to it in years. So, the bottom line is, as always, "no ORDER BY, no ordering."

Do your Clustered Indexes on IDENTITYs Have the Correct FILLFACTOR?

The great thing about blogging is that it forces you to be as sure as you can that you know what you're talking about. That alone makes it worthwhile...

I started this FILLFACTOR post a few days ago with a completely different goal in mind. After I did my due diligence, however - perusing Books OnLine, seeing what's in my "dead trees" library, and reading my favorite gurus' blogs - I realized that the "tip" I was preparing to demonstrate was completely wrongheaded.

In the process, I learned a lot about setting FILLFACTOR values. One thing I learned was that clustered indexes on IDENTITY columns should almost always have their FILLFACTOR set to 100% (completely full - no room for expansion). The reasoning has to do with page splits, which are expensive, especially on a clustered index.

Since IDENTITY columns are monotonically increasing, INSERTs will always be applied to the end of the index, so an INSERT will never cause a page split. UPDATE-ing a record can cause page splits, but only if a variable-length field (like, an NVARCHAR) is updated with more data than it had before. Since the page is already 100% full, there's nowhere to put the extra data: that causes a page split.

Can we avoid page splits like this by setting the FILLFACTOR to something less than 100% - that is, leave some empty space on each page? Sure, but, as always, there's a downside. Database "reads" typically outnumber "writes" by a factor of n to 1, right? Whether n is 5 or 50 depends on who you believe, and the unique characteristics of each database, and certainly changes over time; so it's a little vague, but in any case, the typical OLTP database does a lot more reads than writes. That's important, because setting the FILLFACTOR to less than 100% can make reads much slower: that's the downside.

To see why this is, imagine you have a table with 1000 rows, and 50 rows will fit on an 8 KB page. With a FILLFACTOR of 100%, every page is full (by definition), this will require 100 / 50 = 20 pages. So, a scan of this table will require, at most, 20 reads.

Now imagine you've set the FILLFACTOR to 80%, leaving each page 20% empty after you REBUILD it. This leaves lots of room for growth, so we'll reduce the number of page splits. And that's a good thing.

But now, only 40 rows (80% of 50 rows) will fit on each page, so the table now requires 100 / 40 = 25 pages. A scan of the table will now require, at most, 25 reads. You've just decreased read performance by, in the worst case, 1 - (20 / 25) = 20%. Oops.

So, to be sure all the tables that are clustered on an IDENTITY column have a FILLFACTOR setting of 100%, I've written a script, Index_ClusteredOnIdentity_SetFillFactorTo100_All.sql . The script first finds the tables to be rebuilt, using a cursor on this statement:

SELECT OBJECT_SCHEMA_NAME(i.object_id)   AS 'Schema Name',
       OBJECT_NAME(i.object_id)          AS 'Table Name',
       i.name                            AS 'Index Name'
  FROM sys.indexes                  i
  JOIN sys.index_columns            ic
    ON ic.object_id = i.object_id
   AND ic.index_id  = i.index_id 
  JOIN sys.columns                  c
    ON c.object_id  = ic.object_id
   AND c.column_id  = ic.column_id
  JOIN sysindexes                   si
    ON si.id        = i.object_id
   AND si.indid     = i.index_id
 WHERE i.object_id                     > 100
   AND OBJECT_SCHEMA_NAME(i.object_id) != 'sys'
   AND i.is_disabled                   = 0 
   AND i.type_desc                     = 'CLUSTERED'
   AND c.is_identity                   = 1
   AND si.origfillfactor               NOT IN (0, 100)

This should be pretty self-explanatory. The system tables are joined in the usual way to get at the index's column(s). We restrict the result set to exclude system tables and tables in the "sys" that can be returned in certain cases. We don't REBUILD disabled indexes, because that also enables them, a side-effect you may not expect. We of course only want clustered indexes on IDENTITY columns. Finally, there's no point in rebuilding the index unnecessarily.

As we iterate through the resultset, we build up a statement like:

ALTER INDEX PK_Product_ProductID
   ON Production.Product
   REBUILD WITH (ONLINE = OFF, PAD_INDEX = ON, FILLFACTOR = 100)

Rebuilding a clustered index can be a time-intensive process, but at least, as BooksOnLine says, "Rebuilding a clustered index does not rebuild associated nonclustered indexes unless the keyword ALL is specified" - and we're not specifying it.

Script Produces DML to Fix Index Problems

(Getting the hang of this blogging thing... Rather than post an entire SQL script as a blog post, I've created a page for all my scripts. I'll add a blog post whenever a script is added or changed.)

The latest and greatest version of my IndexDemon.sql script (formerly, Index_Analysis.sql) is now available. It examines all the indexes in the current database and emits DML code to correct any problems it finds. It makes no changes to your database itself - you have to copy and paste the emitted SQL - seemed safer.

This is a work in progress, so the usual caveats apply: do not run this on any database you want to keep, for example, a production database, or any database that isn't backed up. Comments welcome!