THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Joe Chang

sp_spaceused2

this is my version of sp_spaceused. Instead of just overall spaceusage, all tables are shown with extended information. --sp_spaceused2

USE master -- skip this for Azure
GO

IF EXISTS (
  SELECT * FROM sys.procedures WHERE object_id = OBJECT_ID('sp_spaceused2')
) DROP procedure [dbo].sp_spaceused2
GO

CREATE PROCEDURE [dbo] .[sp_spaceused2] @objname nvarchar( 776) = NULL, @minrow int = 0
AS
DECLARE @objid int , @dbname sysname

-- Check to see that the object names are local to the current database.
select @dbname = parsename(@objname,3)
if @dbname is null
  select @dbname = db_name()
else if @dbname <> db_name()
begin
  raiserror(15250,-1,-1) 
  return (1)
end
-- initialize @objid.
select @objid = object_id(@objname)  

 

;WITH a AS (

SELECT CASE WHEN o.schema_id = 4 THEN CASE WHEN o.type = 'S' THEN 1 WHEN o.type = 'IT' THEN 2 ELSE 3 END ELSE o.object_id END AS object_id

, o.type AS otype, d.index_id, i.data_space_id , d.reserved_page_count, d.used_page_count

, d.in_row_data_page_count, d.lob_used_page_count, d.row_overflow_used_page_count, d.row_count

, r.data_compression, r.partition_number, i.type itype, i.is_unique

, i.fill_factor , i.is_disabled, i.is_hypothetical, i.has_filter

FROM sys.objects o WITH(NOLOCK) INNER JOIN sys.indexes i WITH(NOLOCK) ON i.object_id = o.object_id

LEFT JOIN sys.partitions r WITH(NOLOCK) ON r.object_id = i.object_id AND r.index_id = i.index_id

LEFT JOIN sys.dm_db_partition_stats d WITH(NOLOCK) ON d.partition_id = r.partition_id --AND r.object_id = d.object_id AND r.index_id = d.index_id AND r.partition_number = d.partition_number

WHERE o.type <> 'TF' AND o.type <> 'IT'

AND (@objid IS NULL OR o.object_id = @objid)

AND (@minrow = 0 OR row_count > @minrow)

-- AND row_count > 0 -- optional

 

), b AS (

SELECT object_id, index_id, otype, itype, data_space_id  -- MAX(CASE WHEN index_id <= 1 THEN data_space_id ELSE 0 END) data_space_id

, CASE WHEN COUNT(*) > 1 THEN 1 ELSE 0 END Part, COUNT(*) AS Cnt

, reserved = 8*SUM(reserved_page_count) , used = 8*SUM(used_page_count) , in_row_data = 8*SUM(in_row_data_page_count) , lob_used = 8*SUM(lob_used_page_count)

, row_overflow_used = 8*SUM(row_overflow_used_page_count) , row_count = SUM(row_count) , compressed = SUM(data_compression) -- change to 0 for SQL Server 2005

, Pop = SUM(CASE WHEN row_count = 0 OR index_id > 1 THEN 0 ELSE 1 END)

, Ppz = SUM(CASE WHEN row_count = 0 AND index_id <= 1 THEN 1 ELSE 0 END)

, Clus = MAX(CASE a.index_id WHEN 1 THEN 1 ELSE 0 END) , IxCt = MAX(CASE itype WHEN 2 THEN 1 ELSE 0 END)

, XmlC = MAX(CASE itype WHEN 3 THEN 1 ELSE 0 END)             , SpaC = MAX(CASE itype WHEN 4 THEN 1 ELSE 0 END)

, CoSC = MAX(CASE itype WHEN 5 THEN 1 ELSE 0 END)

, ncs  = MAX(CASE itype WHEN 6 THEN -1 ELSE 0 END)

, MO   = MAX(CASE itype WHEN 7 THEN 1 ELSE 0 END)     

, Uniq = MAX(CASE is_unique WHEN 1 THEN 1 ELSE 0 END)

, disa = MAX(CASE is_disabled WHEN 1 THEN 1 ELSE 0 END) , hypo = MAX(CASE is_hypothetical WHEN 1 THEN 1 ELSE 0 END) , filt = MAX(CASE has_filter WHEN 1 THEN 1 ELSE 0 END)

FROM a GROUP BY object_id, index_id, otype, itype , data_space_id

), c AS (

SELECT CASE WHEN otype IS NULL THEN 'A' ELSE otype END otype

, CASE WHEN b.object_id IS NULL THEN 0 ELSE b.object_id END AS object_id

, CASE WHEN b.object_id IS NULL THEN 0 WHEN b.object_id IN (1,2) THEN b.object_id ELSE 3 END Ord --, data_space_id

, MAX(CASE WHEN index_id <= 1 THEN data_space_id ELSE 0 END) data_space_id

, [Rows] = SUM(CASE WHEN b.index_id < 2 THEN b.row_count ELSE 0 END ) , Reserved = SUM(b.reserved), Used = SUM(b.used)

, Data = SUM(CASE WHEN (b.index_id < 2) THEN (b.in_row_data + b.lob_used + b.row_overflow_used)  ELSE b.lob_used + b.row_overflow_used END )

, index2 = SUM(CASE WHEN b.index_id > 1 AND itype=2 THEN (b.in_row_data) ELSE 0 END)

, index3 = SUM(CASE WHEN b.index_id > 1 AND itype>2 THEN (b.used) ELSE 0 END)

, in_row_data = SUM(in_row_data), lob = SUM(lob_used), ovrflw = SUM(row_overflow_used)

, SUM(CASE compressed WHEN 0 THEN 0 ELSE 1 END) Cmpr , SUM(CASE WHEN b.object_id > 10 AND Part > 0 THEN 1 ELSE 0 END) AS Part

, SUM(Pop) Pop, SUM(Ppz) Ppz

, MAX(CASE WHEN b.object_id < 10 AND disa = 0 THEN Cnt ELSE 0 END) AS Cnt

, SUM(Clus) Clus, SUM(IxCt) IxCt, SUM(XmlC) XmlC, SUM(SpaC) SpaC, SUM(CoSC) CoSC, SUM(ncs) ncs, SUM(Uniq) Uniq, SUM(disa) disa, SUM(hypo) hypo, SUM(filt) filt --, SUM(MO) MO

FROM b GROUP BY b.object_id, otype -- , data_space_id

WITH ROLLUP HAVING (b.object_id IS NOT NULL AND otype IS NOT NULL /*AND data_space_id IS NOT NULL*/ ) OR b.object_id IS NULL

) , j AS (

  SELECT j.object_id, j.index_id, j.key_ordinal, c.column_id, c.name,is_descending_key

  FROM sys.index_columns j INNER JOIN sys.columns c ON c.object_id = j.object_id AND c.column_id = j.column_id )

 

SELECT otype , CASE WHEN t.schema_id IS NULL THEN '' ELSE t.name END [Schema] , CASE c.object_id WHEN 0 THEN '_Total' WHEN 1 THEN '_sys' WHEN 2 THEN '_IT' ELSE o.name END [Table]

, ISNULL(STUFF(( SELECT ', ' + name + CASE is_descending_key WHEN 1 THEN '-' ELSE '' END

   FROM  j WHERE j.object_id = c.object_id AND j.index_id = 1 AND j.key_ordinal >0

   ORDER BY j.key_ordinal FOR XML PATH(''), TYPE, ROOT).value('root[1]','nvarchar(max)'),1,1,'') ,'') as ClKey

, /*CASE is_memory_optimized WHEN 1 THEN x2.rows_returned ELSE [Rows] END*/ [Rows]

, /*CASE is_memory_optimized WHEN 1 THEN memory_allocated_for_table_kb  ELSE Reserved END*/ Reserved

, /*CASE is_memory_optimized WHEN 1 THEN memory_used_by_table_kb ELSE [Data] END*/ [Data]

, lob --, ovrflw

, /*CASE is_memory_optimized WHEN 1 THEN memory_used_by_indexes_kb ELSE*/ index2 /*END*/ [Index]  --, newIx = index3

, /*CASE is_memory_optimized WHEN 1 THEN memory_allocated_for_table_kb+memory_allocated_for_indexes_kb-memory_used_by_table_kb -memory_used_by_indexes_kb ELSE*/ Reserved - Used /*END*/ Unused

 

, AvBR = CASE [Rows] WHEN 0 THEN 0 ELSE 1024*[Data]/ [Rows] END

, CASE WHEN c.object_id IN (1,2,3) THEN Cnt ELSE Clus END Clus , IxCt, Uniq , XmlC Xm, SpaC Sp, CoSC + ncs cs

, /*CASE is_memory_optimized WHEN 1 THEN 1 ELSE 0 END*/ 0 MO

, Stct, kct, Cmpr , Part, Pop, Ppz -- , Cnt

, CIxSk, IxSk, Scans, lkup, upd , cols, guids, ngu

, c.data_space_id dsid , CASE y.lob_data_space_id WHEN 0 THEN NULL ELSE y.lob_data_space_id END lobds  --, fif.ftct, fif.ftsz

, rkey, fkey, def, trg --, cols

, disa -- , hypo

, filt , o.create_date

FROM c 

LEFT JOIN sys.objects o WITH(NOLOCK) ON o.object_id = c.object_id

LEFT JOIN sys.tables y WITH(NOLOCK) ON y.object_id = c.object_id

LEFT JOIN sys.schemas t WITH(NOLOCK) ON t.schema_id = o.schema_id

--LEFT JOIN sys.dm_db_xtp_table_memory_stats x ON x.object_id = y.object_id

--LEFT JOIN sys.dm_db_xtp_index_stats x2 ON x2.object_id = y.object_id AND x2.index_id = 0

LEFT JOIN (

SELECT CASE WHEN object_id IS NULL THEN 0 ELSE object_id END object_id, COUNT(*) Stct FROM sys.stats WITH(NOLOCK) WHERE object_id > 3 /* skip low values */ GROUP BY object_id WITH ROLLUP HAVING object_id IS NOT NULL OR object_id IS NULL ) s ON s.object_id = c.object_id

LEFT JOIN (

SELECT table_id, SUM(data_size)/1024 ftsz , COUNT(*) ftct FROM sys.fulltext_index_fragments WITH(NOLOCK) WHERE [status] = 4 GROUP BY table_id  ) fif ON fif.table_id = c.object_id

LEFT JOIN (

SELECT object_id, COUNT(*) kct FROM sys.index_columns WITH(NOLOCK) WHERE index_id = 1  GROUP BY object_id  ) k ON k.object_id = c.object_id

LEFT JOIN (

SELECT CASE WHEN object_id IS NULL THEN 0 ELSE object_id END object_id, COUNT(*) cols  , SUM(CASE system_type_id WHEN 36 THEN 1 ELSE 0 END) guids , SUM(CASE WHEN system_type_id = 36 AND is_nullable = 1 THEN 1 ELSE 0 END) ngu

FROM sys.columns WITH(NOLOCK)  GROUP BY object_id /*WITH ROLLUP HAVING object_id IS NOT NULL OR object_id IS NULL*/ ) e  ON e.object_id = c.object_id

LEFT JOIN ( SELECT CASE WHEN referenced_object_id IS NULL

THEN 0 ELSE referenced_object_id END referenced_object_id, COUNT(*) rkey

FROM sys.foreign_keys WITH(NOLOCK) GROUP BY referenced_object_id

/*WITH ROLLUP HAVING referenced_object_id IS NOT NULL OR referenced_object_id IS NULL*/ ) r ON r.referenced_object_id = c.object_id

LEFT JOIN ( SELECT CASE WHEN parent_object_id IS NULL THEN 0 ELSE parent_object_id END parent_object_id, COUNT(*) fkey

FROM sys.foreign_keys WITH(NOLOCK) GROUP BY parent_object_id

/* WITH ROLLUP HAVING parent_object_id IS NOT NULL OR parent_object_id IS NULL */

) f ON f.parent_object_id = c.object_id

LEFT JOIN ( SELECT CASE WHEN parent_object_id IS NULL THEN 0 ELSE parent_object_id END parent_object_id, COUNT(*) def

FROM sys.default_constraints WITH(NOLOCK) GROUP BY parent_object_id

/*WITH ROLLUP HAVING parent_object_id IS NOT NULL OR parent_object_id IS NULL*/

) d ON d.parent_object_id = c.object_id

LEFT JOIN ( SELECT CASE WHEN parent_id IS NULL THEN 0 ELSE parent_id END parent_id, COUNT(*) trg

FROM sys.triggers WITH(NOLOCK)

WHERE parent_id > 0 GROUP BY parent_id

/*WITH ROLLUP HAVING parent_id IS NOT NULL OR parent_id IS NULL*/

) g ON g.parent_id = c.object_id

LEFT JOIN ( SELECT object_id , SUM( CASE index_id WHEN 1 THEN user_seeks ELSE 0 END) CIxSk

, SUM( CASE WHEN index_id < 2 THEN 0 ELSE user_seeks END) IxSk

, SUM( CASE WHEN index_id < 2 THEN user_scans ELSE 0 END) Scans

, SUM( CASE WHEN index_id < 2 THEN 0 ELSE user_lookups END) lkup

, SUM( CASE WHEN index_id < 2 THEN 0 ELSE user_updates END) upd

, SUM( CASE WHEN index_id > 1 AND user_seeks = 0 THEN 1 ELSE user_updates END) ZrIx

FROM sys.dm_db_index_usage_stats WITH(NOLOCK) WHERE database_id = DB_ID() GROUP BY object_id

) l ON l.object_id = c.object_id

/*LEFT JOIN ( SELECT object_id , SUM( CASE WHEN s.avg_user_impact > 90.0 THEN 1 ELSE 0 END) mix

FROM sys.dm_db_missing_index_details d WITH(NOLOCK)

INNER JOIN sys.dm_db_missing_index_groups g WITH(NOLOCK) ON g.index_handle = d.index_handle

INNER JOIN sys.dm_db_missing_index_group_stats s WITH(NOLOCK) ON s.group_handle = g.index_group_handle

WHERE d.database_id = DB_ID() GROUP BY object_id

) m ON m.object_id = c.object_id

*/

WHERE --o.type IN ('U','V') AND

(c.object_id IS NOT NULL /*OR x.object_id IS NOT NULL*/)

--WHERE (--t.name <>'dbo' AND o.name NOT LIKE 'Trace%') OR t.name IS NULL

ORDER BY Ord, Reserved DESC --, Reserved DESC

--, t.name, o.name

GO

-- Then mark the procedure as a system procedure.

EXEC sys. sp_MS_marksystemobject 'sp_spaceused2' -- skip this for Azure
GO

SELECT NAME, IS_MS_SHIPPED FROM SYS. OBJECTS WHERE NAME LIKE 'sp_spaceused2%'
GO

Published Friday, February 23, 2018 4:08 PM by jchang

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

Hikari said:

Great, tnx a lot!

Unfortunately there's already a sp_spaceused2 on my instance, and I have no permission to drop it or execute it or create a new sp :/

March 1, 2018 5:53 AM
 

Rambo said:

As i suppose this is not azure compatible?

March 2, 2018 4:00 AM
 

jchang said:

Hikari, you can change the name of this procedure to anything you would like, if you are not dbo in master, then you need to just make this a regular procedure in the database you normally use, and skip the mark as system proc at the end

Rambo, first or second blood? you may not be able to create this in master. if not, then just create the procedure in each of your user databases,

you can comment out any DMVs not supported in Azure.

In a few days when I get back, I will check is this works in Azure

March 2, 2018 2:18 PM
 

ThEk said:

Hi Joe, Great SP again!

I know what the index type columns there are, but it might be a nice idea to add some more info in the SP header explaining them for the newer DBA's who are still struggling with the acronyms out there :)

Theo Ekelmans (NL)

April 4, 2018 5:02 AM
 

jchang said:

I know that there are people who like documentation.

Personally, I follow the school of Gert Drapers: "the source code is the documentation." Not only that, it is guaranteed to be correct!

I probably should write something on how I use this information, which would do more to explain why I have it in the first place.

Perhaps after I am done writing my hardware articles, I can come back to this.

April 5, 2018 8:12 AM

Leave a Comment

(required) 
(required) 
Submit

About jchang

Reverse engineering the SQL Server Cost Based Optimizer (Query Optimizer), NUMA System Architecture, performance tools developer - SQL ExecStats, mucking with the data distribution statistics histogram - decoding STATS_STREAM, Parallel Execution plans, microprocessors, SSD, HDD, SAN, storage performance, performance modeling and prediction, database architecture, SQL Server engine

This Blog

Syndication

Privacy Statement