About Me

My photo
Colorado
Paul has 18 years experience with Microsoft SQL Server. He has worked in the roles of production DBA, database developer, database architect, applications developer, business intelligence and data warehouse developer, and instructor for students aspiring for MCDBA certification. He has performed numerous data migrations and supported large databases (3 Terabyte, 1+ billion rows) with high transactions. He is a member of PASS, blogs about lessons learned from a developer’s approach to SQL Server administration, and has been the president of the Boulder SQL Server Users’ Group from January 2009 to present.

Tuesday, March 6, 2018

Table Size in rows and bytes - instance-wide


When troubleshooting disk I/O bottlenecks you may want to see what the largest tables are.  Rather than looking at rows, it is more important to look at table size in bytes - after all disk I/O is all about bytes, not rows.

This script will give you bytes and rows for all tables in all databases in this instance.  The heart of this is to call sp_spaceused for each table and store the results into a table.

Table size in rows and bytes for all tables in all databases on this SQL instance.
No dependencies in this script.



/*     DATE          AUTHOR        REMARKS
       5/16/18              PPaiva        Initial creation.

       NAME
       Table size by rows and bytes

       DESCRIPTION
              Gathers table size by rows and bytes for all databases in this instance.
              Results are stored in table diagSpaceUsed which is not dropped at the end,
              but is dropped if pre-existing prior to running again.  This way you can
              query this table differently to meet your needs.

*/
DECLARE @ID int,
              @MaxID int,
              @Name nvarchar(128),
              @MaxDB nvarchar(300),
              @SchemaName nvarchar(50),
              @s nvarchar(2000),
              @ServerName varchar(50) = Convert(varchar(50), ServerProperty('ServerName')),
              @sNow varchar(30) = Convert(varchar, GetDate(), 120),  -- Allows the population date to be consistent
              @DBNameForSpaceusedTable varchar(30) = db_name(),
              @ShowSql bit = 0,
              @DB varchar(300) = 'All'

IF Object_ID('dbo.diagSpaceUsed') Is NOT Null
    DROP TABLE dbo.diagSpaceUsed

-- Create table only once
IF Object_ID('dbo.diagSpaceUsed') Is Null
      BEGIN
            -- Left indented columns are inserted and can be NOT NULL
            -- The rest must be NULL unless calculated or a default exists
            SET @s = '
            CREATE TABLE dbo.diagSpaceUsed(
                  ID int IDENTITY(1,1) NOT NULL CONSTRAINT pk_diagSpaceUsed PRIMARY KEY CLUSTERED,
                  Instance varchar(100) NOT NULL CONSTRAINT def_diagSpaceUsed_Instance DEFAULT ''' + @ServerName + ''',
                  DB varchar(100) NULL,
                  SchemaName varchar(50) NULL,
            TableName nvarchar(128) NOT NULL,
            Rows bigint NOT NULL,
                  SizeMB decimal(22, 3) NULL,
                  TotalMB  AS (CONVERT(int,replace(index_size,''KB'',''''),0)/(1000.0)+CONVERT(int,replace(data,''KB'',''''),0)/(1000.0)),
                  IndexMB  AS (CONVERT(int,replace(index_size,''KB'',''''),0)/(1000.0)),
                  DataMB  AS (CONVERT(int,replace(data,''KB'',''''),0)/(1000.0)),
                  ReservedMB  AS (CONVERT(int,replace(Reserved,''KB'',''''),0)/(1000.0)),
                  object_id int NULL,
            Reserved varchar(18) NOT NULL,
            data varchar(18) NOT NULL,
            index_size varchar(18) NOT NULL,
            unused varchar(18) NULL,
                  PopDate datetime NULL
                  )
            '
          
            IF @ShowSql = 1
                           Print @s

            Exec(@s)
          
            CREATE UNIQUE INDEX ix_diagSpaceUsed_InstanceDBSchemaTable
                  ON dbo.diagSpaceUsed (Instance, DB, SchemaName, TableName)

            CREATE INDEX ix_diagSpaceUsed_Rows
                  ON dbo.diagSpaceUsed (Rows)
      END



IF @DB = 'All'
      TRUNCATE TABLE dbo.diagSpaceUsed
ELSE
         DELETE
      FROM dbo.diagSpaceUsed
      WHERE DB = @DB
         OR DB Is Null


IF Object_ID('tempdb.dbo.#diagSpaceUsedDBList') Is Not Null
      DROP TABLE #diagSpaceUsedDBList
IF Object_ID('tempdb.dbo.#PopSpaceUsedTableList') Is Not Null
      DROP TABLE #PopSpaceUsedTableList

IF @DB <> 'All'
      IF Not Exists(    SELECT *
                              FROM sys.databases                            
                              WHERE name = @DB
                                AND state_desc = 'online')
            BEGIN
                  SET @s = 'Database <' + @DB + '> was not found on this server.'
                  RaisError(@s, 16, 1)
                  RETURN
            END

-- Since sp_spaceused only works for the current database context,
-- we have to execute it as embedded SQL.  This table will contain
-- the names of all tables with database names, then we are ready
-- to call sp_spaceused.
CREATE TABLE #PopSpaceUsedTableList(
      ID int NOT NULL IDENTITY(1, 1) PRIMARY KEY,
      DB nvarchar(300),
      object_id int,
      SchemaName nvarchar(50),
      TableName nvarchar(128)
      )

IF Object_ID('tempdb.dbo.#SpaceUsedDBList') Is Not Null
       DROP TABLE #SpaceUsedDBList

SELECT name
INTO #SpaceUsedDBList
FROM sys.databases
WHERE name Not In ('tempdb')
--WHERE name Not In ('master', 'model', 'msdb', 'tempdb')
  AND state_desc = 'online'
  AND (name = @DB OR @DB = 'All')


SELECT  @MaxDB = Max(name),
            @DB = ''
FROM #SpaceUsedDBList

-- For all designated databases
WHILE @DB < @MaxDB
      BEGIN
            -- Get next DB
            SELECT @DB = Min(name)
            FROM #SpaceUsedDBList
            WHERE name > @DB


            -- Get table list for this DB
            SET @s = 'INSERT INTO #PopSpaceUsedTableList (DB, object_id, SchemaName, TableName)
SELECT ''' + @DB + ''' , t.object_id, s.name SchemaName, t.Name
FROM [' + @DB + '].sys.tables t
JOIN [' + @DB + '].sys.schemas s
ON t.schema_id = s.schema_id
WHERE t.name Not In (''dtproperties'')
ORDER BY t.name, s.name'
            Exec(@s)

      END

      IF @ShowSql = 1
            SELECT *
            FROM #PopSpaceUsedTableList


      SELECT  @MaxID = Max(ID),
                  @ID = 0
      FROM #PopSpaceUsedTableList


-- Ready to call out sp_spaceused for each table in designated DBs
WHILE @ID < @MaxID
      BEGIN
            -- Get next table
            SELECT @ID = Min(ID)
            FROM #PopSpaceUsedTableList
            WHERE ID > @ID

            SELECT  @DB = DB,
                        @Name = TableName,
                        @SchemaName = SchemaName
            FROM #PopSpaceUsedTableList
            WHERE ID = @ID

            SET @s = 'use [' + @DB + '] INSERT INTO ' + @DBNameForSpaceusedTable + '.dbo.diagSpaceUsed (TableName, Rows, Reserved, data, index_size, unused)
       Exec sp_spaceused ''[' + @SchemaName + '].[' + @Name + ']'''

            IF @ShowSql = 1
                  Print @s

            Exec(@s)

            UPDATE diagSpaceUsed
            SET SchemaName = @SchemaName,
                  DB = @DB,
                  PopDate = @sNow
            WHERE ID = @ID

      END


UPDATE diagSpaceUsed
SET Instance = Convert(varchar, ServerProperty('ServerName')),
      SizeMB = Convert(decimal(22, 3), Replace(data, ' KB', '')) / 1000.

-- Strip out [schema]. from TableName
UPDATE diagSpaceUsed
SET TableName = Replace(Replace(
                                  Replace(TableName, '[', '' ),
                                         ']', ''),
                                                SchemaName + '.', '')

UPDATE diagSpaceUsed
SET object_id = t.object_id
FROM diagSpaceUsed s
JOIN #PopSpaceUsedTableList t
      ON  s.DB = t.DB COLLATE SQL_Latin1_General_CP1_CI_AS
      AND s.SchemaName = t.SchemaName  COLLATE SQL_Latin1_General_CP1_CI_AS
      AND s.TableName = t.TableName COLLATE SQL_Latin1_General_CP1_CI_AS


SELECT  '5 TableSize' Query,
              Instance,
              DB,
              SchemaName,
              TableName,
              Rows,
              TotalMB,
              SizeMB,
              IndexMB,
              DataMB,
              ReservedMB,
              Object_id,
              PopDate,
              ID
FROM diagSpaceUsed
--ORDER BY Instance, Rows desc
--ORDER BY Instance, DB, Rows desc     
ORDER BY Instance, TotalMB desc
--ORDER BY Instance, DB, TotalMB desc