Instantly Find Size of Tables on Disk

Here's a script to tell you how big your table(s) are, subtotaled by indexes, with a grand total. Includes LOBs, and excludes dropped pages.

SELECT CASE WHEN GROUPING(t.name) = 0 THEN t.name ELSE 'GRAND TOTAL' END    AS 'Table Name'
     , CASE WHEN GROUPING(i.name) = 0 THEN i.name ELSE 'TABLE TOTAL' END    AS 'Index Name'
     , SUM(au.total_pages) * 8 / 1024                                       AS 'Allocated MB'
     , SUM(au.used_pages)  * 8 / 1024                                       AS 'Used MB'

  FROM sys.allocation_units   au

  LEFT JOIN sys.partitions    pHobt
    ON au.container_id = pHobt.hobt_id
   AND (au.type_desc = 'IN_ROW_DATA'  OR  au.type_desc = 'ROW_OVERFLOW_DATA')

  LEFT JOIN sys.partitions    pLob
    ON au.container_id = pLob.partition_id
   AND au.type_desc    = 'LOB_DATA'

  JOIN sys.indexes            i  
    ON (i.object_id = pHobt.object_id  AND  i.index_id = pHobt.index_id)
         OR
       (i.object_id = pLob.object_id   AND  i.index_id = pLob.index_id)
   
  JOIN sys.tables             t
    ON i.object_id  = t.object_id
  
 WHERE au.type_desc != 'DROPPED'
   AND t.type_desc   = 'USER_TABLE'
   AND COALESCE(pHobt.rows, pLob.rows, 0) > 0
   AND t.name LIKE '%MyTable%'
  
 GROUP BY ROLLUP(t.name, i.name)
 ORDER BY t.name, i.name 

Easy Documentation: Does Anything Even Use That Index?

I don't know if it's ever happened to you, but I always find myself wishing I had documented what the devil I was thinking when I added an index to a table. I mean, it's all well and good to create an index, but it's annoying when six months later you can't remember what it was for. Is anything actually using this index? Can I maybe drop it?

Yes, yes, I suppose I could document my indexes, and I suppose some people do that kind of thing... but it just seems like so much... work.

Wouldn't it be cool if there was a way to automatically document which stored procedures use which indexes? And how many times each procedure used each index? And when the last time that was? And was integrated into SQL Server Management Studio, and all this with no work required on your part?

Of course it would be cool; so here it is. In this post, I'll just present the code, and what it will do for you; I'll walk through it in my next post.

Briefly, this script examines the current and cached query plans, and parses the info we want out of them; then it adds extended properties to each index.

After you run this script, your indexes will have extended properties like this, one for each stored procedure that uses it. Of course, the index had to have been in the query plan cache to show up here; since the script only takes a few seconds to run, I have a job that runs it every so often. Over the long run, all the indexes eventually get found in the cache; it just depends on how often your various applications run. Note that the date in each extended property is updated every time the script is run.

The Script

-------------------------------------------------------------------------------
-- Document Indexes via Query Plans and Extended Properties.sql                
--                                                                             
-- 2011-06-11 - Larry Leonard - http://sqlsoundings.blogspot.com               
--                                                                             
-- (Note: In the comments below "index" is shorthand for "index or primary key 
-- constraint", and "stored procedure" is shorthand for "stored procedure,     
-- trigger, etc.".)                                                            
--                                                                             
-- This script first examines each currently executing query plan and extracts 
-- (a) the name of each index each query plan is using and (b) the name of the 
-- executing stored procedure for the query plan.  An Extended Property for    
-- each stored procedure is added to the corresponding index(es).              
--                                                                             
-- As this script is run, over time, every index (assuming it's ever caught in 
-- use) gathers a set of Extended Properties.  Each Extended Property displays 
-- the *name* of the stored procedure that used it, how many *times* since the 
-- last SQL Server restart it used it, and the *date* this script last updated 
-- this Extended Property.  For example:                                       
--                                                                             
--    usp_CalculateInvoice     Used this constraint  3315 times   (2011-03-22) 
--    TR_Customer_Update       Used this index         42 times   (2011-05-01) 
--                                                                             
-- This script runs against the the current database.  It should be run on an  
-- irregular basis so that every query is more likely to be caught executing   
-- at least once.  How long it takes for every query to have executed at least 
-- once depends entirely on your system.  If after a sufficient amount of time 
-- has passed, an index does not have any Extended Properties created by this  
-- script, that doesn't prove the index is unused, only that the index needs   
-- investigation. Don't drop indexes based solely on the usage of this script. 
-- You should read and test this script thoroughly before using it in a        
-- production environment.                                                     
-------------------------------------------------------------------------------


RAISERROR('"Document Indexes via Query Plans and Extended Properties.sql" starting on "%s"' , 10, 1, @@SERVERNAME) WITH NOWAIT, LOG

IF DB_ID() < 5
BEGIN
    RAISERROR('This is a system database.', 16, 1)
    RETURN
END

SET NOCOUNT ON
SET XACT_ABORT ON

-- We don't want to kill some other process doing real work.
SET DEADLOCK_PRIORITY -10


-------------------------------------------------------------------------------
-- Variables. None of these should need to be changed in normal use.           
-------------------------------------------------------------------------------

DECLARE @DatabaseName             sysname        = ''
DECLARE @ProcedureName            sysname        = ''
DECLARE @SchemaName               sysname        = ''
DECLARE @SchemaNameUnquoted       sysname        = ''
DECLARE @TableName                sysname        = ''
DECLARE @TableNameUnquoted        sysname        = ''
DECLARE @IndexName                sysname        = ''
DECLARE @PlanUseCount             INT            = 0
DECLARE @PlanUseCountString       NVARCHAR(50)   = ''
DECLARE @Len                      INT            = 0
DECLARE @IndexNamePrev            sysname        = ''
DECLARE @IndexNameUnquoted        sysname        = ''
DECLARE @ExtendedPropertyName     sysname        = ''
DECLARE @ExtendedPropertyValue    NVARCHAR(3750) = ''       -- The value of an extended property is a sql_variant value that can contain up to 7,500 bytes of data. 
DECLARE @ExtendedPropertyValueOld NVARCHAR(3750) = ''
DECLARE @Level2Type               VARCHAR(50)    = ''
DECLARE @Now                      NVARCHAR(50)   = CONVERT(NVARCHAR, CAST(GETDATE() AS DATE), 120)
DECLARE @Msg                      NVARCHAR(2048) = ''
DECLARE @Error                    INT            = 0
DECLARE @AddedCount               INT            = 0
DECLARE @UpdatedCount             INT            = 0
DECLARE @UnchangedCount           INT            = 0

RAISERROR('Looking for indexes in the plan cache...', 10, 1) WITH NOWAIT

DECLARE cur CURSOR FOR 
   WITH XMLNAMESPACES(DEFAULT N'http://schemas.microsoft.com/sqlserver/2004/07/showplan'),
    cteCachedPlansXml
    AS
    (
         SELECT DISTINCT
                cp.plan_handle                                       AS plan_handle

              , COALESCE(RelOp.op.value(N'TableScan [1]/Object[1]/@Database',          N'varchar(128)'),
                         RelOp.op.value(N'OutputList[1]/ColumnReference[1]/@Database', N'varchar(128)'),
                         RelOp.op.value(N'IndexScan [1]/Object[1]/@Database',          N'varchar(128)'))
                                                                     AS DatabaseName

              , COALESCE(RelOp.op.value(N'TableScan [1]/Object[1]/@Schema',            N'varchar(128)'),
                         RelOp.op.value(N'OutputList[1]/ColumnReference[1]/@Schema',   N'varchar(128)'),
                         RelOp.op.value(N'IndexScan [1]/Object[1]/@Schema',            N'varchar(128)')) 
                                                                     AS SchemaName

              , COALESCE(RelOp.op.value(N'TableScan [1]/Object[1]/@Table',             N'varchar(128)'),
                         RelOp.op.value(N'OutputList[1]/ColumnReference[1]/@Table',    N'varchar(128)'),
                         RelOp.op.value(N'IndexScan [1]/Object[1]/@Table',             N'varchar(128)'))
                                                                     AS TableName
             
              , COALESCE(RelOp.op.value(N'TableScan [1]/Object[1]/@Index',             N'varchar(128)'),
                         RelOp.op.value(N'OutputList[1]/ColumnReference[1]/@Index',    N'varchar(128)'),
                         RelOp.op.value(N'IndexScan [1]/Object[1]/@Index',             N'varchar(128)'))
                                                                     AS IndexName

              , QUOTENAME(OBJECT_NAME(qp.objectid))                  AS ProcedureName
              , ISNULL(cp.usecounts, 0)                              AS PlanUseCount              
           FROM sys.dm_exec_cached_plans                 cp
    CROSS APPLY sys.dm_exec_query_plan(cp.plan_handle)   qp
    CROSS APPLY qp.query_plan.nodes(N'//RelOp')          RelOp (op)
          WHERE qp.dbid         = DB_ID()
            AND cp.cacheobjtype IN ('Compiled Plan', 'Compiled Plan Stub', 'Parse Tree', 'Extended Proc', 'CLR Compiled Func', 'CLR Compiled Proc')
            AND cp.objtype      IN ('Proc', 'Prepared', 'Adhoc', 'ReplProc', 'Trigger', 'View', 'Default', 'UsrTab', 'SysTab', 'Check', 'Rule')            
    )
    ,
    cteCachedPlans AS
    (
         SELECT DISTINCT 
                ctecpx.SchemaName                                                                     AS SchemaName
              , ctecpx.TableName                                                                      AS TableName
              , ctecpx.IndexName                                                                      AS IndexName
              , SUBSTRING(ctecpx.IndexName, 2, LEN(ctecpx.IndexName) - 2)                             AS IndexNameUnquoted
              , CASE MIN(CAST(i.is_primary_key as int)) WHEN 0 THEN 'INDEX' ELSE 'CONSTRAINT' END     AS Level2Type
              , ctecpx.ProcedureName                                                                  AS ProcedureName
              , MIN(ctecpx.PlanUseCount)                                                              AS PlanUseCountMin
              , MAX(ctecpx.PlanUseCount)                                                              AS PlanUseCountMax
              , SUM(ctecpx.PlanUseCount)                                                              AS PlanUseCountSum
           FROM cteCachedPlansXml                        ctecpx               
           JOIN sys.dm_exec_query_stats                  qs
             ON ctecpx.plan_handle = qs.plan_handle
           JOIN sys.indexes                              i
             ON OBJECT_ID(ctecpx.TableName) = i.object_id
            AND INDEXPROPERTY(OBJECT_ID(ctecpx.TableName), SUBSTRING(ctecpx.IndexName, 2, LEN(ctecpx.IndexName) - 2), 'IndexId') = i.index_id
           JOIN sys.tables                               t
             ON  i.object_id = t.object_id
    CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle)      st
          WHERE st.dbid              = DB_ID()
            AND ctecpx.DatabaseName  = QUOTENAME(DB_NAME())
            AND ctecpx.SchemaName   != QUOTENAME('sys')
            AND ctecpx.SchemaName    IS NOT NULL 
            AND ctecpx.TableName     NOT LIKE '$[$#%' ESCAPE '$'
            AND ctecpx.TableName     IS NOT NULL 
            AND ctecpx.IndexName     IS NOT NULL 
            AND ctecpx.ProcedureName IS NOT NULL
            AND t.type_desc NOT IN ('SQL_SCALAR_FUNCTION')          -- Added to as I find them. 
       GROUP BY ctecpx.SchemaName
              , ctecpx.TableName
              , ctecpx.IndexName
              , ctecpx.ProcedureName
    )
    ,
    cteIndexExtendedProperties AS
    (
        SELECT ctecp.SchemaName
             , ctecp.TableName
             , ctecp.IndexName
             , ctecp.IndexNameUnquoted
             , ctecp.ProcedureName
             , ctecp.PlanUseCountSum
             , ISNULL(CAST(ep.value AS NVARCHAR(3750)), '')   AS ExtendedPropertyValue
             , ctecp.Level2Type                               AS Level2Type
          FROM cteCachedPlans             ctecp
     LEFT JOIN sys.extended_properties    ep
            ON ep.major_id = OBJECT_ID(ctecp.SchemaName + '.' + ctecp.TableName, 'U')
           AND ep.minor_id = INDEXPROPERTY(OBJECT_ID(ctecp.SchemaName + '.' + ctecp.TableName, 'U'), ctecp.IndexNameUnquoted, 'IndexId')
         WHERE ISNULL(ep.class_desc, 'INDEX') = 'INDEX'
    )
    ,
    ctePkExtendedProperties AS
    (
        SELECT ctecp.SchemaName
             , ctecp.TableName
             , ctecp.IndexName
             , ctecp.IndexNameUnquoted
             , ctecp.ProcedureName
             , ctecp.PlanUseCountSum    
             , ISNULL(CAST(ep.value AS NVARCHAR(3750)), '')   AS ExtendedPropertyValue
             , ctecp.Level2Type                               AS Level2Type
          FROM cteCachedPlans             ctecp
     LEFT JOIN sys.extended_properties    ep
            ON ep.major_id = OBJECT_ID(ctecp.SchemaName + '.' + ctecp.TableName + '.' + ctecp.IndexName, 'PK')
           AND ep.minor_id = 0                                                              -- 0 means on the index, not on any columns. 
         WHERE ISNULL(ep.class_desc, 'OBJECT_OR_COLUMN') = 'OBJECT_OR_COLUMN'
    )
    ,
    cteExtendedProperties AS
    (
        SELECT cteiep.SchemaName                               AS SchemaName
             , cteiep.TableName                                AS TableName
             , cteiep.IndexName                                AS IndexName
             , cteiep.ProcedureName                            AS ProcedureName
             , SUM(cteiep.PlanUseCountSum)                     AS PlanUseCount 
             , MIN(cteiep.ExtendedPropertyValue)               AS ExtendedPropertyValue
             , ISNULL(cteiep.Level2Type, ctepkep.Level2Type)   AS Level2Type
          FROM cteIndexExtendedProperties   cteiep
     LEFT JOIN ctePkExtendedProperties      ctepkep
            ON cteiep.SchemaName    = ctepkep.SchemaName
           AND cteiep.TableName     = ctepkep.TableName
           AND cteiep.IndexName     = ctepkep.IndexName
           AND cteiep.ProcedureName = ctepkep.ProcedureName
      GROUP BY cteiep.SchemaName, cteiep.TableName, cteiep.IndexName, cteiep.ProcedureName, ISNULL(cteiep.Level2Type, ctepkep.Level2Type)
    )
    SELECT SchemaName
         , TableName
         , IndexName
         , ProcedureName
         , PlanUseCount 
         , ExtendedPropertyValue
         , Level2Type
      FROM cteExtendedProperties        
  ORDER BY SchemaName
         , TableName
         , IndexName
         , ProcedureName
         , PlanUseCount 
         , ExtendedPropertyValue
         , Level2Type


-------------------------------------------------------------------------------
-- Now that we've created the SELECT statement, iterate it through a cursor.   
-------------------------------------------------------------------------------

OPEN cur
RAISERROR('Found %d query plans with index usage in the cache', 10, 1, @@CURSOR_ROWS) WITH NOWAIT

FETCH NEXT FROM cur
 INTO @SchemaName, @TableName, @IndexName, @ProcedureName, @PlanUseCount, @ExtendedPropertyValue, @Level2Type
 
WHILE @@FETCH_STATUS = 0
BEGIN
    BEGIN TRY

        -- Build some values we're going to need. 
        SET @Len = LEN(@ProcedureName)  SET @ExtendedPropertyName = SUBSTRING(@ProcedureName, 2, @Len - 2)
        SET @Len = LEN(@SchemaName)     SET @SchemaNameUnquoted   = SUBSTRING(@SchemaName,    2, @Len - 2)
        SET @Len = LEN(@TableName)      SET @TableNameUnquoted    = SUBSTRING(@TableName,     2, @Len - 2)
        SET @Len = LEN(@IndexName)      SET @IndexNameUnquoted    = SUBSTRING(@IndexName,     2, @Len - 2)
        SET @Len = LEN(@Level2Type)     SET @Level2Type           = @Level2Type + SPACE(LEN('CONSTRAINT') - @Len)
        
        SET @ExtendedPropertyValueOld = @ExtendedPropertyValue
        SET @PlanUseCountString       = REPLACE(CONVERT(NVARCHAR(50), CAST(@PlanUseCount AS MONEY), 1), '.00', '')
        SET @PlanUseCountString       = SPACE(10 - LEN(@PlanUseCountString)) + @PlanUseCountString
        SET @ExtendedPropertyValue    = 'Used this ' + LOWER(@Level2Type) + ' ' + @PlanUseCountString 
        SET @ExtendedPropertyValue   += CASE @PlanUseCount WHEN 1 THEN ' time' ELSE ' times' END 
        SET @ExtendedPropertyValue   += '   (' + @Now + ')'

        -- If the index name is new, create a display heading for it; just for readability. 
        IF @IndexName != @IndexNamePrev
        BEGIN
            SET @IndexNamePrev = @IndexName
            SET @Msg = @SchemaNameUnquoted + '.' + @TableNameUnquoted + '.' + @IndexNameUnquoted
            RAISERROR(' ',  10, 1) WITH NOWAIT
            RAISERROR(@Msg, 10, 1) WITH NOWAIT
        END   
        
        -- If the value hasn't changed, nothing to do. 
        BEGIN TRAN 

        IF @ExtendedPropertyValue = @ExtendedPropertyValueOld
        BEGIN
            RAISERROR('   Unchanged: %-40s = %s', 10, 1, @ExtendedPropertyName, @ExtendedPropertyValue) WITH NOWAIT
            SET @UnchangedCount += 1
        END
        ELSE IF EXISTS (SELECT *
                         FROM sys.extended_properties    ep
                        WHERE ep.class_desc = 'INDEX'
                          AND ep.name       = @ExtendedPropertyName
                          AND ep.major_id   = OBJECT_ID(@SchemaNameUnquoted + '.' + @TableNameUnquoted, 'U')
                          AND ep.minor_id   = INDEXPROPERTY(OBJECT_ID(@SchemaNameUnquoted + '.' + @TableNameUnquoted, 'U'), @IndexNameUnquoted, 'IndexId')
                       )
                  OR

                EXISTS (SELECT *
                          FROM sys.extended_properties    ep
                     LEFT JOIN sys.key_constraints        kc
                            ON ep.major_id = kc.parent_object_id
                           AND ep.minor_id = 0   
                         WHERE ep.class_desc = 'OBJECT_OR_COLUMN'
                           AND ep.name       = @ExtendedPropertyName
                           AND ep.major_id   = OBJECT_ID(@SchemaNameUnquoted + '.' + @IndexNameUnquoted, 'PK')
                       )
        BEGIN    
            -- Update any existing one. 
            EXEC @Error = sys.sp_updateextendedproperty 
                @name       = @ExtendedPropertyName, 
                @value      = @ExtendedPropertyValue,
                @level0type = 'SCHEMA',    @level0name = @SchemaNameUnquoted, 
                @level1type = 'TABLE',     @level1name = @TableNameUnquoted,
                @level2type = @Level2Type, @level2name = @IndexNameUnquoted

            IF @Error = 0
            BEGIN
                SET @Msg = @SchemaNameUnquoted + '.' + @TableNameUnquoted + '.' + @IndexNameUnquoted
                RAISERROR('   Updated:   %-40s = %s', 10, 1, @ExtendedPropertyName, @ExtendedPropertyValue) WITH NOWAIT
                SET @UpdatedCount += 1
            END
            ELSE
            BEGIN
                SET @Msg = ISNULL(ERROR_MESSAGE(), '{ No error }')
                RAISERROR(@Msg, 16, 1) WITH NOWAIT, LOG
            END
        END
        ELSE
        BEGIN
            -- Write extended property. 
            EXEC @Error = sys.sp_addextendedproperty 
                @name       = @ExtendedPropertyName, 
                @value      = @ExtendedPropertyValue,
                @level0type = 'SCHEMA',    @level0name = @SchemaNameUnquoted, 
                @level1type = 'TABLE',     @level1name = @TableNameUnquoted,
                @level2type = @Level2Type, @level2name = @IndexNameUnquoted

            IF @Error = 0
            BEGIN
                SET @Msg = @SchemaNameUnquoted + '.' + @TableNameUnquoted + '.' + @IndexNameUnquoted
                RAISERROR('   Added:     %-40s = %s', 10, 1, @ExtendedPropertyName, @ExtendedPropertyValue) WITH NOWAIT
                SET @AddedCount += 1
            END
            ELSE
            BEGIN
                SET @Msg = ISNULL(ERROR_MESSAGE(), '{ No error }')
                RAISERROR(@Msg, 16, 42) WITH NOWAIT, LOG
            END
        END
        
        -- Done with this extended property. 
        COMMIT
    END TRY
    BEGIN CATCH
        -- MS bug: if sp_addextendedproperty or sp_dropextendedproperty throw an exception, they 
        -- leave a transaction open.  You can verify this by examining the @@TRANCOUNT value. 
        SET @Msg  = '   *** ERROR ' + CAST(@@ERROR AS NVARCHAR) + ': ' + ISNULL(ERROR_MESSAGE(), '{ No error }') + CHAR(10)
        SET @Msg += '   The @@TRANCOUNT is ' + CAST(@@TRANCOUNT AS NVARCHAR) + CHAR(10) + '   '
        
        -- If  1, the transaction is committable. 
        -- If -1, the transaction is uncommittable and should be rolled back. 
        -- If  0, there is no transaction and a commit or rollback operation would generate an error. 
        IF XACT_STATE() = -1 
        BEGIN
            SET @Msg += 'The transaction is in an uncommittable state.  Rolling back all transactions.'
            ROLLBACK
        END
        ELSE IF XACT_STATE() = 1 
        BEGIN
            SET @Msg += 'The transaction is committable.  Committing transaction.'
            COMMIT 
        END 

        -- If this is a deadlock, the rest of the indexes on this table will probably deadlock too. 
        -- Skip to the next table. 
        IF ERROR_NUMBER() = 1205
        BEGIN
            WHILE @@FETCH_STATUS = 0
            BEGIN
                FETCH NEXT FROM cur
                 INTO @SchemaName, @TableName, @IndexName, @ProcedureName, @PlanUseCount, @ExtendedPropertyValue, @Level2Type
            
                IF QUOTENAME(@TableNameUnquoted) != @TableName  BREAK
                RAISERROR('   Skipping %s.%s.%s to get to the next table', 10, 1, @SchemaName, @TableName, @IndexName)
            END
        END
        ELSE
        BEGIN
            -- If it wasn't a deadlock, something else bad must have happenned, so we don't want to 
            -- continue - this will cause an abort that displays the error message. 
            RAISERROR(@Msg, 16, 1) WITH NOWAIT, LOG
        END
    END CATCH

    -- Next row. 
    FETCH NEXT FROM cur
     INTO @SchemaName, @TableName, @IndexName, @ProcedureName, @PlanUseCount, @ExtendedPropertyValue, @Level2Type
END

-- Close and deallocate the cursor. 
CLOSE cur
DEALLOCATE cur

RAISERROR(' ',    10, 1) WITH NOWAIT
RAISERROR('Done - Extended properties: added %d new, updated %d existing, %d unchanged', 10, 1, @AddedCount, @UpdatedCount, @UnchangedCount) WITH NOWAIT, LOG

GO

Related Articles

• Modifying and Deleting Extended Properties (sqlservercentral.com)
• Industrial-strength Database Documentation using Extended Properties (sqlservercentral.com)