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.

Wednesday, May 25, 2011

PopSpaceUsed - Populates table SpaceUsed for a given DB or all DBs

If you have been reading the past few blog entries, you know we are building the foundation for having a quick method for viewing the size of all tables in all databases on a given SQL Server instance.  By size I mean both file size in bytes and number of rows.  This global view will be called vwTable and will be presented very soon in a subsequent post.

Here is the last piece of the foundational layer that vwTable will use.  The below proc PopSpaceUsed creates a table called SpaceUsed.  When the proc is run, it will populate the table with the size in bytes for all tables in the SQL Server instance.  The table is truncated and repopulated each time the proc is executed.  The guts of this proc is that it calls Exec sp_spaceused for every table in a given database, or optionally all the databases.  I wasn’t able to find a dynamic management view (DMV) that contains size in bytes for a table.  Please let me know if you run across this.  Besides size in bytes, sp_spaceused  also returns the number of rows.  Nevertheless our future views will not need to use this because the number of rows is available in a DMV. (See vwPartitionRaw which draws from sys.partitions).  Surprisingly, sp_spaceused executes rather quickly. 

Use MyDatabaseName
Exec sp_spaceused 'dbo.Employees'





Since sp_spaceused must be run in the database context of the table, I use a loop to get all the databases on the SQL instance.  It is my custom to do this with a simple WHILE loop, which incidentally is the only LOOP construct that T-SQL offers other than cursors.  I avoid using cursors as a general principle, although it would probably be just fine to do that if you prefer since the cursor would be very small.  

Once the proc is run, you can examine the table.

SELECT *
FROM SpaceUsed

The header of the proc contains a description and proc usage information. 

USE Admin

IF OBJECT_ID('dbo.PopSpaceUsed') Is Not Null
      DROP PROC dbo.PopSpaceUsed
GO

CREATE PROC [dbo].PopSpaceUsed
      @DB varchar(300),
      @ShowSql bit = 0,
      @SuppressOutput bit = 1
AS
/*    DATE        AUTHOR            REMARKS
      5/24/11           PPaiva            Initial creation.

      DESCRIPTION
            Populates SpaceUsed for a given dB.  Based on sp_spaceused.
            To populate tables of all databases on this server,
                  use @DB = ALL.
            Column SizeMB strips the KB out of column Data,
                  and converts to MB.

      DEPENDENCY
            sp_spaceused

      USAGE
            Exec Admin..PopSpaceUsed 'All'
            Exec Admin..PopSpaceUsed 'All', 1
            Exec Admin..PopSpaceUsed 'Blah'
            Exec Admin..PopSpaceUsed 'Admin', 1

      DEBUG
            SELECT *
            FROM SpaceUsed

            Use MyDatabaseName
            Exec sp_spaceused 'dbo.Employees'

            SELECT *
            FROM Admin.sys.tables
            ORDER BY name

            SELECT *
            FROM Admin.sys.schemas


            -- Compare the same table in different DBs
            SELECT *
            FROM SpaceUsed
            ORDER BY Server, TableName, SchemaName, DB

            -- Largest tables
            SELECT *
            FROM SpaceUsed
            ORDER BY Rows desc, TableName, SchemaName, DB

            -- Tables per Database
            SELECT Server, DB, Min(PopDate) PopDate, Count(*) Qty
            FROM SpaceUsed
            GROUP BY Server, DB
            ORDER BY Server, DB


*/
SET NOCOUNT ON

DECLARE @ID int,
            @MaxID int,
            @Name nvarchar(128),
            @MaxDB nvarchar(300),
            @SchemaName nvarchar(50),
            @s nvarchar(1000),
            @ServerName varchar(50) = Convert(varchar(50), ServerProperty('ServerName')),
            @sNow varchar(30) = Convert(varchar, GetDate(), 120)  -- Allows the population date to be consistent


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

-- Create table only once
IF Object_ID('dbo.SpaceUsed') Is Null
      BEGIN
            -- Left indendted columns are inserted and can be NOT NULL
            -- The rest must be NULL unless calculated or a default exists
            SET @s = '
            CREATE TABLE Admin.dbo.SpaceUsed(
                  ID int IDENTITY(1,1) NOT NULL CONSTRAINT pk_SpaceUsed PRIMARY KEY CLUSTERED,
                  Server varchar(100) NOT NULL CONSTRAINT def_SpaceUsed_Server DEFAULT ''' + @ServerName + ''',
                  DB varchar(100) NULL,
                  SchemaName varchar(50) NULL,
            TableName nvarchar(128) NOT NULL,
            Rows int 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
                  )
            '
           
            --Print @s
            Exec(@s)
           
            CREATE UNIQUE INDEX ix_SpaceUsed_ServerDBSchemaTable
                  ON dbo.SpaceUsed (Server, DB, SchemaName, TableName)

            CREATE INDEX ix_SpaceUsed_Rows
                  ON dbo.SpaceUsed (Rows)
      END



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


IF Object_ID('tempdb.dbo.#SpaceUsedDBList') Is Not Null
      DROP TABLE #SpaceUsedDBList
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)
      )


SELECT name
INTO #SpaceUsedDBList
FROM sys.databases
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 Admin.dbo.SpaceUsed (TableName, Rows, Reserved, data, index_size, unused)
Exec sp_spaceused ''[' + @SchemaName + '].[' + @Name + ']'''

            IF @ShowSql = 1
                  Print @s

            Exec(@s)

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

      END


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


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

IF @SuppressOutput = 0
      SELECT *
      FROM SpaceUsed
      ORDER BY Server, DB, TableName, SchemaName