VSTSC's Community Server

Community Server: The platform that enables you to build rich, interactive communities.
VSTeamSystemCentral: Your Team System community!

Welcome to VSTSC's Community Server Sign in | Join | Help
in Search

Steve Fibich

space_used_sp vs. sp_spaceUsed

Ok I'm not one to re-write standard system stored procedures for SQL Server.  That being said I have been working a good deal with files and file groups within SQL Server over the past few days moving data and indexes to files and file groups other than the default PRIMARY file group.  Sp_spaceUsed is great procedure for giving you overall storage utilization information for a database or database objects (table, index view, or queue).  As I have been moving indexes and tables over to different file groups I wanted to see how this was splitting my data as far as size and row counts was going and how it was being disturbed across the file system.  Unfortunately sp_spaceUsed does not give you this level of detail and I could not find a system view or other system procedure to give me all of the information that I wanted. 

sp_spaceUsed

So I set out to create my own and now that I have it working and figured I'll share it with everyone.  I ended up creating two objects, one a view sp_space_used_view and the second a procedure space_used_sp. I’m a database guy so I love organization so I put both of these objects into a schema named metadata that is owned by DBO.  The view displays the space used by all objects in the database with rollup levels being displayed as ' -db-level', ' -schema-level', and ' -table-level'.  The view relies upon the sys.objects (due to the fact that system tables are not in sys.tables so I was losing the space and row count information for these objects.), sys.indexes, sys.partions, sys.database_files, sys.schemas, sys.dm_db_partion_stats, sys.internal_tables, and sys.index_columns catalog views; all of which have detail coverage in BOL.  The view utilizes these system catalog views to show rows counts, reserved space, data space, index space, and unused space in the database files broken down by table.  I figure I would want to see all of the detailed data for a given database sometimes, rollups others, and individual objects separately as well so encapsulation of the select logic into a view allows re-use of this code in other objects.  The procedure utilizes the view to show you information for a particular table or for the overall database.  I hope this code is useful to others as well.  One of the main differences between sp_spaceUsed and my space_used_sp is that sp_spaceUsed allows for the updating of system information which my procedure does not, it only returns data to the screen.  The second difference is that sp_spaceUsed requires the schema name to be passed as part of the table name if you want to view information on an object outside of your default schema, space_used_sp does not require this and displays all objects with the name you provide giving you the schema name as part of the output.

space_used_sp vs. sp_spaceUsed

Here it is:  (Note:everything is created in a schema call metadata)

IF  EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[metadata].[space_used_vw]'))
DROP VIEW [metadata].[space_used_vw]
go
create view metadata.space_used_vw
-------------------------------------------
--SFIBICH
--7/7/2008
--The purpose of this view is to show space used broken
--down by SQL Server data files.  Currently is does not calculate
--space for fulltext indexes and or XML indexes.
--
-------------------------------------------
as
select
top 100 PERCENT
coalesce([schema_name],' -db level-') schemaName
,coalesce(table_name,' -schema level-') tableName
,coalesce(data_file_name,' -table level-') dataFileName
,[rowCount]
,reserved*8 reservedKB
,data*8 dataKB
,case when used_page_count>data then (used_page_count-data)*8 else 0 end indexKB
,case when reserved>used_page_count then (reserved-used_page_count)*8 else 0 end unusedKB
from
(
 SELECT  
 t.name table_name
 ,df.name data_file_name
 ,s.name [schema_name]
 ,SUM( 
    CASE 
  WHEN (p.index_id < 2) THEN row_count 
  ELSE 0 
    END 
 )  [rowCount]
 ,SUM(reserved_page_count)+coalesce(max(IT_reserved_page_count),0) reserved
 ,SUM( 
    CASE 
  WHEN (p.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 
 )data 
 ,SUM(used_page_count)+coalesce(max(IT_used_page_count),0) used_page_count
  FROM sys.dm_db_partition_stats p
 inner join
 sys.objects t
 on
 p.object_id=t.object_id
 inner join
 sys.schemas s
 on
 t.schema_id=s.schema_id
 inner join
 sys.indexes i
 on
 p.object_id=i.object_id
 and
 p.index_id=i.index_id
 inner join
 sys.database_files df
 on
 i.data_space_id=df.data_space_id
 left outer join
 (--Internal tables are placed on the same filegroup as the parent entity. (BOL)
  select
  pp.object_id
  ,min(pp.index_id) index_id
  ,sum(p.reserved_page_count) IT_reserved_page_count
  ,sum(p.used_page_count)  IT_used_page_count
  FROM
  sys.dm_db_partition_stats p
  inner join
  sys.internal_tables it
  on
  p.object_id = it.object_id 
  inner join
  sys.dm_db_partition_stats pp
  ON
  it.parent_object_id=pp.object_id
  WHERE
  it.internal_type IN (202,204) 
  group by
  pp.object_id
 )it
 on
 p.object_id=it.object_id
 and
 p.index_id=it.index_id
 group by
 s.name
 ,t.name
 ,df.name
 with rollup
) dataTable
order by
[schema_name]
,table_name
,[rowCount] desc
,data_file_name asc
go
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[metadata].[space_used_sp]') AND type in (N'P', N'PC'))
DROP PROCEDURE [metadata].[space_used_sp]
go
create procedure metadata.space_used_sp
-------------------------------------------
--SFIBICH
--7/7/2008
--This is a replacement for sp_spaceused, it utlizes the view
--[metadata].[space_used_vw].  Currently it only reports on tables
--and the total for tables in a schema, or database level broken
--down by database files.
--
-------------------------------------------
@tableName sysname =null
as
if @tableName is null
begin
--------------------
--
--database, schema info
--
--------------------
 select
 ' -db level-' schemaName
 ,' -schema level-' tableName
 ,dataFileName
 ,sum([rowCount]) [rowCount]
 ,sum(reservedKB) reservedKB
 ,sum(dataKB) dataKB
 ,sum(indexKB) indexKB
 ,sum(unusedKB) unusedKB
 from
 [metadata].[space_used_vw]
 where
 dataFileName <>' -table level-'
 or
 (
 dataFileName =' -table level-'
 and
 schemaName=' -db level-'
 and
 tableName =' -schema level-'
 )
 group by
 dataFileName
 order by [rowCount] desc,dataFileName
end
else
begin
--------------------
--
--particular table
--
--------------------
 select
 schemaName
 ,tableName
 ,dataFileName
 ,[rowCount]
 ,reservedKB
 ,dataKB
 ,indexKB
 ,unusedKB
 from
 [metadata].[space_used_vw]
 where
 tableName =@tableName
end

go

Published Tuesday, July 08, 2008 5:07 AM by steve

Comments

 

Log Buffer #105, a Carnival of the Vanities for DBAs said:

July 11, 2008 9:06 AM
 

steve said:

The code comments states that it does not include XML and fulltext indexes.  This is not true, it does include them and attributes there values to the filegroup for the table that they are related to.  I updated the code and forgot to update the code comments.

July 14, 2008 7:30 AM
 

Bookmarks about Metadata said:

February 19, 2009 4:15 PM
Anonymous comments are disabled
Powered by Community Server (Personal Edition), by Telligent Systems