SQL Server – Get Table Sizes

Background

For a project we are doing we need to know which tables are the biggest in the system.

Here is what we came up with thus far.

Code

Function dbo.Format

Source

  1. Format a number with commas but without decimals in SQL Server 2008 R2?
    http://stackoverflow.com/questions/13207075/format-a-number-with-commas-but-without-decimals-in-sql-server-2008-r2

Code


use master
go

--drop function dbo.Format
if object_id('dbo.Format') is null
begin

	exec(
			'create function dbo.Format() returns varchar(30) as begin return null end '
		)

end
go

/*
	Format a number with commas but without decimals in SQL Server 2008 R2?
	http://stackoverflow.com/questions/13207075/format-a-number-with-commas-but-without-decimals-in-sql-server-2008-r2
*/
ALTER FUNCTION dbo.[Format]
(
	@num bigint
)
returns varChar(30)
As
Begin

	return(
			left(
					  CONVERT(varchar(50), CAST(@num AS money), 1)
					, len(CONVERT(varchar(50), CAST(@num AS money), 1)) - 3
				)
		   )	

end
go


Function :- [dbo].[DelimitedSplit8K]

Source

  1. Jeff Moden – Tally OH! An Improved SQL 8K “CSV Splitter” Function
    http://www.sqlservercentral.com/articles/Tally+Table/72993/

Code


USE [master]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[DelimitedSplit8K]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
BEGIN
execute dbo.sp_executesql @statement = N'CREATE FUNCTION [dbo].[DelimitedSplit8K]
--===== Define I/O parameters
        (@pString VARCHAR(8000), @pDelimiter CHAR(1)='','')
--WARNING!!! DO NOT USE MAX DATA-TYPES HERE!  IT WILL KILL PERFORMANCE!
RETURNS TABLE WITH SCHEMABINDING AS
 RETURN
--===== "Inline" CTE Driven "Tally Table" produces values from 1 up to 10,000...
     -- enough to cover VARCHAR(8000)
  WITH E1(N) AS (
                 SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
                 SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
                 SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1
                ),                          --10E+1 or 10 rows
       E2(N) AS (SELECT 1 FROM E1 a, E1 b), --10E+2 or 100 rows
       E4(N) AS (SELECT 1 FROM E2 a, E2 b), --10E+4 or 10,000 rows max
 cteTally(N) AS (--==== This provides the "base" CTE and limits the number of rows right up front
                     -- for both a performance gain and prevention of accidental "overruns"
                 SELECT TOP (ISNULL(DATALENGTH(@pString),0)) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM E4
                ),
cteStart(N1) AS (--==== This returns N+1 (starting position of each "element" just once for each delimiter)
                 SELECT 1 UNION ALL
                 SELECT t.N+1 FROM cteTally t WHERE SUBSTRING(@pString,t.N,1) = @pDelimiter
                ),
cteLen(N1,L1) AS(--==== Return start and length (for use in substring)
                 SELECT s.N1,
                        ISNULL(NULLIF(CHARINDEX(@pDelimiter,@pString,s.N1),0)-s.N1,8000)
                   FROM cteStart s
                )
--===== Do the actual split. The ISNULL/NULLIF combo handles the length for the final element when no delimiter is found.
 SELECT ItemNumber = ROW_NUMBER() OVER(ORDER BY l.N1),
        Item       = SUBSTRING(@pString, l.N1, l.L1),
		[Delimiter] = @pDelimiter
   FROM cteLen l
;' 
END

GO

Stored Procedure – sp_helpTableSize

Source



use master
go

if object_id('[dbo].[sp_helpTableSize]') is null
begin

	exec('create procedure  [dbo].[sp_helpTableSize] as select 1/0 as [shell] ')

end
go

alter procedure [dbo].[sp_helpTableSize]
(
	  @skipSchema		varchar(600) = null
	, @skipTables		varchar(600) = null
	, @orderBy			varchar(600) = null
	, @skipXMLIndexes	bit = 1
)
as
begin

	set nocount		on;
	SET XACT_ABORT  ON;
	set transaction isolation level read uncommitted;

	declare @mode		sysname
	declare @PageSize	int

	declare @INDEX_ID_XML int

	set		@INDEX_ID_XML = 32000

	Select  @PageSize = low/1024.0 
	from    master.dbo.spt_values 
	where   Number = 1 
	And     [type] = 'E'

	/*
		sys.dm_db_index_physical_stats (Transact-SQL)
			https://msdn.microsoft.com/en-us/library/ms188917.aspx

		The DETAILED mode scans all pages and returns all statistics.
		The modes are progressively slower from LIMITED to DETAILED, because more work is performed in each mode. 
		To quickly gauge the size or fragmentation level of a table or index, use the LIMITED mode. 
		It is the fastest and will not return a row for each nonleaf level in the IN_ROW_DATA 
		   allocation unit of the index.
	*/
	--set @mode = 'detailed'
	set @mode = 'limited'

	declare @skipSchemaTable TABLE
	(
		  [itemNumber] int
		, [Item]	   varchar(255)	
	)

	declare @skipTable TABLE
	(
		  [itemNumber] int
		, [Item]	   varchar(255)	
		, [schema]	   varchar(255)
		, [object]	   varchar(255)
	)

	declare @outputTable TABLE
	(

		  [object]								sysname

		, [rowCountFormated]					sysname null 

		, [ClusteredIndexeOrHeapSizeReserved]	sysname null 

		, [ClusteredIndexeOrHeapSizeUsed]		sysname null 
			
		, [OtherIndexesSizeReserved]			sysname null 

		, [OtherIndexesSizeUsed]				sysname null 
					
		, [XMLIndexesSize]						sysname null 

		, [TotalSizeReserved]					sysname null 

		, [TotalSizeUsed]						sysname null 

		, [NumberofOtherIndexes]				tinyint null

		, [NumberofXMLIndexes]					tinyint	null

	)

	declare @tblIndexPhysicalStatsCandidate TABLE
	(
		  [id]			int 
							identity(1,1)
		, [object_id]	int
		, index_id		int
	)

	declare @tblIndexPhysicalStats TABLE
	(
		  [id]							  int
		, [object_id]					  int
		, [index_id]					  int
		, page_count					  int

		--, avg_page_space_used_in_percent  float
		, page_size						  int

		--, page_size_used
		--	as ( page_size_allocated * avg_page_space_used_in_percent)


	)

	declare @tblIndexPhysicalStatsSummary TABLE
	(

		  [id]			int 
							identity(1,1)

		, [object_id]					  int
		, page_count					  int

		--, avg_page_space_used_in_percent  float
		, page_size						  int

		, [NumberofIndexes]				  tinyint

	)

	declare @INDEX_TYPE_XML		tinyint

	declare @objectIDCand		int
	declare @indexIDCand		int

	declare @NumberofIndexes	int

	declare @candID				int
	declare @candIDMax			int


	set @INDEX_TYPE_XML = 3

	if (@skipTables is not null)
	begin

		insert into @skipSchemaTable
		(
			  [itemNumber]
			, [Item]	  
		)
		select 
			  [itemNumber]
			, ltrim(rtrim([Item]))
		from [dbo].[DelimitedSplit8K]( @skipSchema, ',')

	end

	if (@skipTables is not null)
	begin

		insert into @skipTable
		(
			  [itemNumber]
			, [Item]	  
		)

		select 
			  [itemNumber]
			, ltrim(rtrim([Item]))
		from [dbo].[DelimitedSplit8K]( @skipTables, ',')

		update tblST
		set       [schema] = ParseName(tblST.[Item], 2)
				, [object] = ParseName(tblST.[Item], 1)
		from   @skipTable tblST

	end


	if (@skipXMLIndexes = 0)
	begin

		/*
			Get list of XML Indexes
		*/
		insert into @tblIndexPhysicalStatsCandidate
		(
			      [object_id]
				, [index_id]
		)

		select
				    tblIndex.[object_id]
				  , tblIndex.index_id

		from    sys.objects tblObject

		inner join sys.schemas tblSchema

				on tblObject.schema_id = tblSchema.schema_id

		inner join sys.indexes tblIndex

				on tblObject.object_id = tblIndex.object_id

		inner join sys.data_spaces da 

				on da.data_space_id= tblIndex.data_space_id

		where  tblIndex.[type] in (@INDEX_TYPE_XML)

		group by
				  tblIndex.[object_id]
				, tblIndex.[index_id]

		set @candID = 1
		set @candIDMax = ( select max([id]) from @tblIndexPhysicalStatsCandidate)

		/*
			Iterate XML Index list
		*/
		while (@candID <= @candIDMax)
		begin

			/*
				Get Candidate data
			*/
			select
					 @objectIDCand
						 = tblIPSC.[object_id]

					, @indexIDCand
						= tblIPSC.index_id

			from    @tblIndexPhysicalStatsCandidate tblIPSC

			where   tblIPSC.[id] = @candID


				/*
					Get Stats
				*/
				insert into @tblIndexPhysicalStats
				(
					  [id]
					, [object_id]
					, [index_id]
					, page_count

					--, avg_page_space_used_in_percent
					, page_size	

					--, page_size_used
					--	as ( page_size_allocated * avg_page_space_used_in_percent)

				)
				select 
					  @candID

					, [objectID]
						= @objectIDCand -- tblSI.OBJECT_ID

					, [indexID]
						= @indexIDCand -- tblSI.OBJECT_ID

					, page_count
						= sum(tblIS.page_count)

					, page_size
						= CONVERT
							(
								DECIMAL(16, 1)
								, CAST( 8 * sum(tblIS.page_count)/1024.0 AS DECIMAL(20,1))

							)

					--, avg_page_space_used_in_percent 
					--	= avg(tblIS.avg_page_space_used_in_percent)

				from sys.dm_db_index_physical_stats
				 --cross apply master.dbo.tvf_dm_db_index_physical_stats
				 (
						db_id()
					
					  , @objectIDCand

					  , @indexIDCand
					
					  , null	

					  , @mode

				) tblIS 


			set @candID = @candID + 1

		end -- while (@candID <= @candIDMax)

		insert into @tblIndexPhysicalStatsSummary
		(
			 [object_id]

			, page_count

			, page_size	

			, NumberofIndexes
		)
		select
				  [object_id]

				, sum(page_count)

				, sum(page_size	)

				, NumberofIndexes
					= count(*)

		from @tblIndexPhysicalStats
		
		group by
				[object_id]


	end -- if (@skipXMLIndexes = 0)


	;with cteTable
	as
	(
		select 

				  [object_id] = object_id
				, [object] = tblO.name

				, [schema_id] = tblO.schema_id
				, [schema] = schema_name(tblO.schema_id)


		from    sys.objects tblO

		where   tblO.[type] = 'U'

		and    not exists
					(

						select tblSST.[Item]
						from    @skipSchemaTable tblSST
						where   schema_name(tblO.schema_id) = tblSST.[Item]
					)


		and    not exists
					(
						select 1
						from    @skipTable tblST
						where   tblO.name = tblST.[object]
					)


	)

	, cteTableBase
	as
	(
		select 
				  [object_id] = tblO.object_id
				, [schema_id] = tblO.schema_id

				, [schema] = schema_name(tblO.schema_id)
				, [object] = tblO.name

				, [rowCount] = sum(tblPS.row_count)

				, [IndexSizeReserved]
					= cast(
							(SUM(tblPS.reserved_page_count) * @PageSize ) / 1024
							as bigint
						  )

				, [IndexSizeUsed]
					= cast(
							(SUM(tblPS.used_page_count) * @PageSize ) / 1024
							as bigint
						  )

		from    sys.objects tblO

		inner join sys.indexes tblI

				on  tblO.object_id = tblI.object_id

		inner join sys.dm_db_partition_stats  tblPS

				on   tblI.object_id = tblPS.object_id
				and  tblI.index_id = tblPS.index_id

		where   tblO.[type] = 'U'
		and     tblI.index_id in (0,1 )

		group by
				  tblO.object_id
				, tblO.schema_id
				, tblO.name

	)
	, cteTableIndex
	as
	(
		select 
				  object_id = tblO.object_id
				, [schema_id] = tblO.schema_id

				, [schema] = schema_name(tblO.schema_id)
				, [object] = tblO.name

				, [IndexSizeReserved]
					= cast( 
								(SUM(tblPS.reserved_page_count) * @PageSize ) / 1024
								as bigint
						  )	

				, [IndexSizeUsed]
					= cast( 
								(SUM(tblPS.used_page_count) * @PageSize ) / 1024
								as bigint
						  )	

				, [NumberofIndexes]
					= count(*)

		from    sys.objects tblO

		inner join sys.indexes tblI

				on  tblO.object_id = tblI.object_id

		inner join sys.dm_db_partition_stats  tblPS

				on   tblI.object_id = tblPS.object_id
				and  tblI.index_id = tblPS.index_id

		where   tblO.[type] = 'U'

		and     tblI.index_id >  1

		-- XML Indexes are greater than 32000
		and     tblI.Index_id < @INDEX_ID_XML

		and     tblI.[is_disabled] =0 		
		and     tblI.type_desc not in 
					( 
						'XML'
					)

		group by
				  tblO.object_id
				, tblO.schema_id
				, tblO.name

	)


	insert into @outputTable
	(
		  [object]

		, [rowCountFormated]

		, [ClusteredIndexeOrHeapSizeReserved]
		, [ClusteredIndexeOrHeapSizeUsed]

		, [OtherIndexesSizeReserved]
		, [OtherIndexesSizeUsed]

		, [XMLIndexesSize]

		, [TotalSizeReserved]	
		, [TotalSizeUsed]		

		, [NumberofOtherIndexes]

		, [NumberofXMLIndexes]	
	)

	select 
			  [object] = cteT.[schema]
							+ '.'
							+ cteT.[object]

			, [rowCountFormated] 
					= [master].[dbo].[format](cteTB.[rowCount])

			, [ClusteredIndexeOrHeapSizeReserved]
					= [master].[dbo].[format](cteTB.[IndexSizeReserved])

			, [ClusteredIndexeOrHeapSizeUsed]
					= [master].[dbo].[format](cteTB.[IndexSizeUsed])

			, [OtherIndexesSizeReserved] 
					= [master].[dbo].[format](cteTI.[IndexSizeReserved])

			, [OtherIndexesSizeUsed] 
					= [master].[dbo].[format](cteTI.[IndexSizeUsed])

			, [XMLIndexesSize]
					= cteISX.page_size

			, [TotalSizeReserved] = [master].[dbo].[format]
								(
									  isNull(cteTB.[IndexSizeReserved], 0)
									+ isNull(cteTI.[IndexSizeReserved], 0)
									+ isNull(cteISX.page_size, 0)
								)

			, [TotalSizeUsed] = [master].[dbo].[format]
								(
									  isNull(cteTB.[IndexSizeUsed], 0)
									+ isNull(cteTI.[IndexSizeUsed], 0)
									+ isNull(cteISX.page_size, 0)
								)

			, [NumberofOtherIndexes]
				 = [master].[dbo].[format](isNull(cteTI.[NumberofIndexes], 0))

			, [NumberofXMLIndexes]
				 = [master].[dbo].[format](isNull(cteISX.[NumberofIndexes], 0))

	from   cteTable cteT

	inner join cteTableBase cteTB

		on    cteT.object_id = cteTB.object_id

	left outer join cteTableIndex cteTI

		on    cteT.object_id = cteTI.object_id

	--left outer join cteTableIndexXML cteISX
	left outer join @tblIndexPhysicalStatsSummary  cteISX

		on    cteT.object_id = cteISX.object_id
		and   @skipXMLIndexes = 0

	order by

			  case when (@orderBy = 'object') then cteT.[schema] + '.'	+ cteT.[object] end asc		 
			, case when (@orderBy = 'rowCount') then cteTB.[rowCount] end desc			
			, case when (@orderBy = 'Size') then 
								(
									  isNull(cteTB.[IndexSizeReserved], 0)
									+ isNull(cteTI.[IndexSizeReserved], 0)
									+ isNull(cteISX.page_size, 0)
								) end desc			


	OPTION (FORCE ORDER)		


	if (@skipXMLIndexes = 1)
	begin

		select 
				  [object]

				, [rowCountFormated]

				, [ClusteredIndexeOrHeapSizeReserved]
				, [ClusteredIndexeOrHeapSizeUsed]

				, [OtherIndexesSizeReserved]
				, [OtherIndexesSizeUsed]

				--, [XMLIndexesSize]	
				, [TotalSizeReserved]
				, [TotalSizeUsed]

				, [NumberofOtherIndexes]
				--, [NumberofXMLIndexes]

		from   @outputTable

	end
	else
	begin

		select 
				  [object]
				, [rowCountFormated]
				, [ClusteredIndexeOrHeapSizeReserved]
				, [ClusteredIndexeOrHeapSizeUsed]
				, [OtherIndexesSizeReserved]
				, [OtherIndexesSizeUsed]
				, [XMLIndexesSize]	
				, [TotalSizeReserved]
				, [TotalSizeUsed]
				, [NumberofOtherIndexes]
				, [NumberofXMLIndexes]

		from   @outputTable

	end



end

go

exec sys.sp_MS_marksystemobject '[dbo].[sp_helpTableSize]'
go


 

Quick Explanation

  1. The sizes are in MB

 

Sample Usage

List Tables ordered by Size

Code


declare
	  @skipSchema varchar(600) --= null
	, @skipTables varchar(600) --= null
	, @orderBy varchar(600) --= null

set @skipSchema = 'Archive'
set @skipTables = 'CommonDB.CommonDBLog,UserAcct.UserActity'

--set @orderBy = 'RowCount'
--set @orderBy = 'object'
set @orderBy = 'Size'


exec [dbo].[sp_helpTableSize]
	  @skipSchema = @skipSchema
	, @skipTables = @skipTables
	, @orderBy = @orderBy

Output

OrderBySize

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s