Cool Tips‎ > ‎Data/Database Size‎ > ‎

Data/Database Size - Get Index Size and Counts

Applicability:

                 SQL Server 2000:        Tested
                 SQL Server 2005:        Tested
                 SQL Server 2008:        Tested
                 SQL Server 2008R2:    Tested
                 SQL Server 2012:        Not Tested        

Credits:

Author:    Jeffrey Yao/ChillyDBA    
Date:        27 Sep 2010

Description

From SQL 2005 onwards, the retrieval of index metadata was made more straightforward with the introduction of the sys.dm_db_partition_stats view.  With SQL 2000 and prior versions, the retrieval of this information meant an iteration through the tables in a DB to collate the output from sp_spaceused.
I have listed below two versions of an SP to retrieve index stats for all tables in a database.

 

Code

SQL Server 2000:

DROP PROCEDURE dbo.usp_GetTableSizes_2000
Go

CREATE PROCEDURE dbo.usp_GetTableSizes_2000
AS

/**********************************************************
Purpose:   To retrieve table and index sizes for all tables
           in a DB.  Index and Statistics (ie '_wa_%') counts and
           index statistics update date are also included
          
Author:    ChillyDBA
History:   2001 - Initial Release
***********************************************************/

CREATE TABLE #TableSizes
(
  
name                SYSNAME,
  
rows                INT,
  
reserved            VARCHAR(30),
  
data                VARCHAR(30),
  
index_size          VARCHAR(30),
  
unused              VARCHAR(30)
)


CREATE TABLE #TableIndexes
(
  
name                SYSNAME,
  
total_indexes       INT,
  
total_clustered     INT,
  
total_nonclustered  INT,
  
total_statistics    INT,
  
stats_last_updated  DATETIME
)


DECLARE
  
@SnapshotDate       DATETIME,   -- in case this information is to be persisted for trend analysis
  
@TableName          SYSNAME,
  
@Cmd                VARCHAR(1000)

SELECT @SnapshotDate = GETDATE()

SELECT @TableName = MIN(name)
FROM sysobjects
WHERE TYPE = 'u'
AND name NOT LIKE 'sys%'


WHILE @TableName IS NOT NULL
BEGIN
   SELECT
@Cmd = 'sp_spaceused ' + @TableName

  
INSERT #TableSizes
  
EXEC (@Cmd)

  
INSERT #TableIndexes (name, total_indexes, total_nonclustered, total_clustered, total_statistics, stats_last_updated)
  
SELECT @TableName,
      
-- get # indexes that are not system stats
      
(SELECT COUNT(*)
      
FROM sysindexes
      
WHERE id = OBJECT_ID(@TableName)
       AND
name NOT LIKE '_wa_%'
      
AND indid > 0),

      
-- get # nonclustered indexes that are not system stats
      
(SELECT COUNT(*)
      
FROM sysindexes
      
WHERE id = OBJECT_ID(@TableName)
       AND
name NOT LIKE '_wa_%'
      
AND indid > 1),

      
-- get # clustered indexes that are not system stats
      
(SELECT COUNT(*)
      
FROM sysindexes
      
WHERE id = OBJECT_ID(@TableName)
       AND
name NOT LIKE '_wa_%'
      
AND indid = 1),

      
-- get # system stats
      
(SELECT COUNT(*)
      
FROM sysindexes
      
WHERE id = OBJECT_ID(@TableName)
       AND
name LIKE '_wa_%'
      
AND indid > 0),

      
-- get date that index stats were last updated
      
(SELECT MAX(STATS_DATE(ID, indid))
      
FROM sysindexes
      
WHERE id = OBJECT_ID(@TableName))

  
SELECT @TableName = MIN(name)
  
FROM sysobjects
  
WHERE TYPE = 'u'
  
AND name > @TableName
  
AND name NOT LIKE 'sys%'
  
AND uid = 1
END

--insert dba_table_sizes
SELECT
  
DB_NAME()                                       AS DatabaseName,
  
ts.name                                         AS TableName,
  
  
-- remove the kb suffix so that these figures can be manipulated easily in excel or any other query
  
REPLACE(rows, 'kb','')                          AS #Rows,
  
REPLACE(reserved, 'kb','')                      AS KB_Reserved,
  
REPLACE(data, 'kb','')                          AS KB_Data,
  
REPLACE(index_size, 'kb','')                    AS KB_Index,
  
REPLACE(unused, 'kb','')                        AS KB_Unused,
  
  
ti.total_indexes                                AS #TotalIndexes,
  
ti.total_nonclustered                           AS #NonClusteredIndexes,
  
ti.total_clustered                              AS #ClusteredIndexes,
  
ti.total_statistics                             AS #Statistics,
  
ti.stats_last_updated                           AS StatsLastUpdatedDate,
  
DATEDIFF( dd, ti.stats_last_updated, GETDATE()) AS StatsAgeInDays,
  
@SnapshotDate                                   AS SnapshotDate
FROM #TableSizes ts
INNER JOIN #TableIndexes ti
  
ON ts.name = ti.name
--order by cast(replace(reserved, 'kb','')as int) desc
ORDER BY ts.name


DROP TABLE #TableSizes
DROP TABLE #TableIndexes


SQL Server 2005 onwards:

DROP PROCEDURE dbo.usp_GetTableSizes_2005
Go

CREATE PROCEDURE dbo.usp_GetTableSizes_2005
AS

/**********************************************************
Purpose:   To retrieve table and index sizes for all tables
           in a DB.  Index and Statistics (ie '_wa_%') counts and
           index statistics update date are also included
          
Author:    Jeffrey Yao
History:   27 Sep 2010 - Initial Release
          19 Jun 2012 - ChillyDBA - Augmented with # coumns to match the
                   output with the usp_GetTableSizes_2000 SP  
***********************************************************/
DECLARE
      
@SnapshotDate       DATETIME    -- in case this information is to be persisted for trend analysis

SELECT @SnapshotDate = GETDATE()


SELECT
  
DB_Name()                                                           AS DatabaseName,
  
object_schema_name(OBJECT_ID) + '.' + OBJECT_NAME(OBJECT_ID)        AS TableName,
  
SUM(CASE WHEN index_id < 2 THEN row_count ELSE 0 END)                AS #Rows,
  
8*SUM(reserved_page_count)                                          AS KB_Reserved,
  
  
8*SUM
      
(   CASE
              
WHEN index_id<2 THEN in_row_data_page_count
                                      
+ lob_used_page_count
                                      
+ row_overflow_used_page_count
              
ELSE lob_used_page_count
                      
+ row_overflow_used_page_count
          
END
      
)                                                               AS KB_Data,
      
  
8*
       (
          
SUM(used_page_count) - SUM
                                  
(   CASE
                                      
WHEN index_id<2 THEN in_row_data_page_count
                                                              
+ lob_used_page_count
                                                              
+ row_overflow_used_page_count
                                      
ELSE lob_used_page_count
                                              
+ row_overflow_used_page_count
                                      
END
                                  
)
       )                                                              
AS KB_Index,
          
  
8*SUM(reserved_page_count-used_page_count)                          AS KB_Unused,
  

   (
      
SELECT COUNT(*)
      
FROM sys.dm_db_partition_stats ps2 (NOLOCK)
      
WHERE ps.object_id = ps2.object_id
      
AND OBJECT_NAME(ps2.object_ID) NOT LIKE '_wa_%'
      
AND ps2.index_id > 0
  
)                                                                   AS #TotalIndexes,

   (
      
SELECT COUNT(*)
      
FROM sys.dm_db_partition_stats ps3 (NOLOCK)
      
WHERE ps.object_id = ps3.object_id
      
AND OBJECT_NAME(ps3.object_ID) NOT LIKE '_wa_%'
      
AND ps3.index_id > 1
  
)                                                                   AS #TotalNonClusteredIndexes,

   (
      
SELECT COUNT(*)
      
FROM sys.dm_db_partition_stats ps4 (NOLOCK)
      
WHERE ps.object_id = ps4.object_id
      
AND OBJECT_NAME(ps4.object_ID) NOT LIKE '_wa_%'
      
AND ps4.index_id = 1
  
)                                                                   AS #TotalClusteredIndexes,

   (
      
SELECT COUNT(*)
      
FROM sysindexes si (NOLOCK)
      
WHERE ps.object_id = si.id
      
AND si.name LIKE '_wa_%'
      
AND si.indid > 0
  
)                                                                   AS #Statistics,
  
   (
      
SELECT MAX(STATS_DATE(object_id, index_id))
      
FROM sys.dm_db_partition_stats ps6 (NOLOCK)
      
WHERE ps.object_id = ps6.object_id
  
)                                                                   AS StatsLastUpdatedDate,
  
  
DATEDIFF( dd,    (
                      
SELECT MAX(STATS_DATE(object_id, index_id))
                      
FROM sys.dm_db_partition_stats ps6 (NOLOCK)
                      
WHERE ps.object_id = ps6.object_id
                  
), GETDATE())                                        AS  StatsAgeInDays,
  
  
@SnapshotDate                                                       AS SnapshotDate
FROM sys.dm_db_partition_stats ps  (NOLOCK)
WHERE OBJECT_ID > 1024
GROUP BY OBJECT_ID
ORDER BY TableName


ċ
usp_GetTableSizes_2000.sql
(3k)
Andy Hughes,
Jun 19, 2012, 10:12 AM
ċ
usp_GetTableSizes_2005.sql
(3k)
Andy Hughes,
Jun 19, 2012, 10:12 AM
Comments