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.

Reclaim Space using DBCC CLEANTABLE

Image by The Real Estreya via Flickr

I just read Pradeep Adiga's excellent post on using DBCC CLEANTABLE to reclaim space left over after a column with a variable length type has been dropped from a table.

According to Books OnLine, the variable length data types are VARCHAR, NVARCHAR, VARCHAR(MAX), NVARCHAR(MAX), VARBINARY, VARBINARY(MAX), TEXT, NTEXT, IMAGE, SQL_VARIANT, and XML. If you drop one of these type columns from a table, SQL Server doesn't release the space it occupied; I imagine this is a speed optimization.

Thus inspired, here's a little script to run DBCC CLEANTABLE on all the user tables in the current database. You might be thinking, "How often does a column get dropped from a table, really?" The answer of course is, "It depends." On one huge (and abused) database I ran this script on, I gained 8 GB of space!

-----------------------------------------------------------------------------
-- Reclaim space from dropped variable length columns and text columns.      
-----------------------------------------------------------------------------

RAISERROR('Reclaiming space from dropped variable length columns and text columns...', 10, 1) WITH NOWAIT

DECLARE @TableName  sysname       = ''
DECLARE @SchemaName sysname       = ''

DECLARE cur CURSOR FOR 
    SELECT SCHEMA_NAME(schema_id)   AS 'SchemaName' 
         , name                     AS 'TableName'
      FROM sys.tables
     WHERE is_ms_shipped = 0
       AND type_desc     = 'USER_TABLE'
  ORDER BY 'SchemaName', 'TableName'

OPEN cur
FETCH NEXT FROM cur INTO @SchemaName, @TableName        

WHILE @@FETCH_STATUS = 0
BEGIN
    SET @TableName = @SchemaName + '.' + @TableName
    RAISERROR('   Examining table %s', 10, 1, @TableName) WITH NOWAIT
    DBCC CLEANTABLE(0, @TableName, 100000) WITH NO_INFOMSGS
    FETCH NEXT FROM cur INTO @SchemaName, @TableName        
END

CLOSE cur
DEALLOCATE cur

RAISERROR('Done.', 10, 1) WITH NOWAIT

Note that we specify a "batch_size" value of 100,000 rows per transaction to keep the logfile small while processing, and to keep the table locks (!) short. The appropriate value for a given system will require some experimentation: for mine, it stopped getting faster at 100,000 rows. Also, BOL points out that this script does not need to be run on a regular basis, just when a variable length or text column is dropped: but how often that actually is depends on your 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)

Find and Kill All System-Named Statistics

I hate system-named objects.

Some of this is to be expected from someone like me, who, depending on the decade, could be called "persnickety", "perfectionist", "OCD", or "The Anal Retentive Chef." But there's also a very practical reason to name all your database objects.

If you're using a third-party tool to synch up database schemas, you may find that every time you copy a system-named object from one side of the synch to the other, an entirely new name is generated. For example, you might have a statistic object named dbo.Customers._WA_Sys_00000001_45544755 on the left-hand side of the synch, but when you tell the tool to copy it to the right-hand side, you get a statistic named dbo.Customers._WA_Sys_00000001_436BFEE3.

Well, that won't do.

Below is a stored procedure that locates and drops all system named statistics. After you run this, just create a new statistic object with a name that follows your SQL nomenclature guidelines. (Whaddaya mean you don't have any? Get some!)

-- Comment. 
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO


-------------------------------------------------------------------------------
-- Drops all system-named statistics.                                          
-------------------------------------------------------------------------------

CREATE PROCEDURE dbo.usp_CleanupStatisticsWithSysNames
AS
BEGIN
    SET NOCOUNT ON

    DECLARE @Cmd        NVARCHAR(MAX) = ''
    DECLARE @SchemaName sysname       = ''
    DECLARE @TableName  sysname       = ''
    DECLARE @StatName   sysname       = ''

    DECLARE cur CURSOR FOR
        SELECT m.name   AS SchemaName
             , t.name   AS TableName
             , s.name   AS StatName
          FROM sys.stats           s
          JOIN sys.stats_columns   sc
            ON s.object_id = sc.object_id
           AND s.stats_id  = sc.stats_id
          JOIN sys.tables          t
            ON s.object_id = t.object_id
          JOIN sys.schemas         m
            ON t.schema_id = m.schema_id
         WHERE (s.name LIKE '\_WA\_Sys\_%' ESCAPE '\'  OR  s.auto_created = 1)
           AND s.object_id > 100
      ORDER BY SchemaName
             , TableName
             , StatName

    OPEN cur
    FETCH NEXT FROM cur INTO @SchemaName, @TableName, @StatName

    WHILE @@FETCH_STATUS = 0
    BEGIN
        SET @Cmd = 'DROP STATISTICS ' + QUOTENAME(@SchemaName) + '.' + QUOTENAME(@TableName) + '.' + QUOTENAME(@StatName)
        RAISERROR(@Cmd, 10, 1) WITH NOWAIT
        EXEC sp_executesql @Cmd
        FETCH NEXT FROM cur INTO @SchemaName, @TableName, @StatName
    END

    CLOSE cur
    DEALLOCATE cur
END

Rumor has it that the "WA" in the system-generated names stands for "Washington", as in "Redmond".