Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

TableSpace Update

The last post in the series on finding the sizes of your tables showed us how we could find that size via a set-based method similar to using sp_MStablespace.  In that post I showed you how to find the sizes using the SQL 2005 system objects rather than the scheduled to be deprecated objects.  You can read more about it here.

I needed to go back and revisit that post and perform a little spring cleaning on it.  I noticed after publication that the script had multiple columns by the same name that were unintended.  I discovered this as I was prepping and reviewing for my presentation at our SSSOLV (PASS UG) meeting last week.  During that review I came across another bug.  The script was not consistently returning the index size for the tables.  After reviewing the script more closely, against more databases, I found the problem and fixed it.  The change was rather simple – I changed the Join from the #indstats table to go directly to sys.partitions rather than through sys.dm_db_index_usage_stats.  I was able to spot this one due to running the query against one of my databases that is seldom used and thus the indexes were less likely to have statistics populated in that DMV.

So, here is the updated query.  Rather than breaking out into sections like I did in that last article, I am just pasting the script in its entirety.

/* Part I */
--Drop Table #indstats
IF exists (SELECT * FROM tempdb.sys.objects WHERE name like '%#indstats%')
BEGIN
	DROP TABLE tempdb.dbo.#indstats
END
 
BEGIN
CREATE TABLE #indstats (
         IndStatsID INT PRIMARY KEY CLUSTERED
        ,database_id BIGINT
        ,index_id BIGINT
        ,IndexSizeMB DECIMAL(16,1)
        ,OBJECT_ID	BIGINT
);
END
 
INSERT INTO #indstats (IndStatsID,database_id,index_id,OBJECT_ID,IndexSizeMB)
        SELECT Row_Number() OVER (ORDER BY OBJECT_ID) AS IndStatsID
				,database_id,index_id,OBJECT_ID
				,CONVERT(DECIMAL(19,2),(SUM(ps.page_count))) * 8 /1024 AS IndexSizeMB
			FROM sys.dm_db_index_physical_stats(DB_ID(),null,NULL,NULL,'DETAILED') ps
			GROUP BY database_id,OBJECT_ID,index_id;
 
/* Part II */
DECLARE @dbsize DECIMAL(19,2)
 
SET NOCOUNT ON
 
/*
**  Summary data.
*/
BEGIN
	SELECT @dbsize = SUM(CONVERT(DECIMAL(19,2),CASE WHEN type = 0 THEN SIZE ELSE 0 END)) * 8/1024
		FROM sys.database_files
 
END
 
/* Part III */
BEGIN
	WITH RegData AS (
		SELECT a.container_id,p.OBJECT_ID,p.index_id,us.database_id
			,FileGroupName = FILEGROUP_NAME(a.data_space_id)
			,TableName = OBJECT_NAME(p.OBJECT_ID)
			,NumRows = p.ROWS
			,UsedPages = IsNull(a.used_pages,0)
			,TotalPages = IsNull(a.total_pages,0)
			,DataSizeMB = CONVERT(DECIMAL(19,2),IsNull(a.used_pages,0)) * 8/1024
			,IndexSizeMB = CASE WHEN ps.index_id < 2 THEN 0 ELSE ps.IndexSizeMB END
			,UserRequests = IsNull(us.user_seeks,0) + IsNull(us.user_scans,0) + IsNull(us.user_lookups,0)
			,UserUpdates = IsNull(us.user_updates,0)
			,LastUpdate = IsNull(us.last_user_update,null)
			,RatioRequestsToUpdates = CAST(IsNull(us.user_seeks,0)
					+ IsNull(us.user_scans,0) + IsNull(us.user_lookups,0) AS REAL)
				/ CAST(CASE us.user_updates WHEN 0 THEN 1 ELSE us.user_updates END AS REAL)
		FROM sys.allocation_units a
			INNER Join sys.partitions p
				ON p.hobt_id = a.container_id
				And a.type = 1
			LEFT Outer Join sys.dm_db_index_usage_stats us
				ON us.OBJECT_ID = p.OBJECT_ID
				And us.index_id = p.index_id
				And us.database_id = DB_ID()
			LEFT Outer Join #indstats ps
				ON p.index_id = ps.index_id
				And ps.database_id = DB_ID()
				And p.OBJECT_ID = ps.OBJECT_ID
		--WHERE OBJECTPROPERTY(p.OBJECT_ID,'IsMSShipped') = 0
	)
	, LOBData AS (
		SELECT a.container_id,p.OBJECT_ID,p.index_id,us.database_id
			,FileGroupName = FILEGROUP_NAME(a.data_space_id)
			,TableName = OBJECT_NAME(p.OBJECT_ID)
			,NumRows = p.ROWS
			,UsedPages = IsNull(a.used_pages,0)
			,TotalPages = IsNull(a.total_pages,0)
			,DataSizeMB = CONVERT(DECIMAL(19,2),IsNull(a.used_pages,0)) * 8/1024
			,IndexSizeMB = CASE WHEN ps.index_id < 2 THEN 0 ELSE ps.IndexSizeMB END
			,UserRequests = IsNull(us.user_seeks,0) + IsNull(us.user_scans,0) + IsNull(us.user_lookups,0)
			,UserUpdates = IsNull(us.user_updates,0)
			,LastUpdate = IsNull(us.last_user_update,null)
			,RatioRequestsToUpdates = CAST(IsNull(us.user_seeks,0)
					+ IsNull(us.user_scans,0) + IsNull(us.user_lookups,0) AS REAL)
				/ CAST(CASE us.user_updates WHEN 0 THEN 1 ELSE us.user_updates END AS REAL)
		FROM sys.allocation_units a
			INNER Join sys.partitions p
				ON p.partition_id = a.container_id
				And a.type = 2
			LEFT Outer Join sys.dm_db_index_usage_stats us
				ON us.OBJECT_ID = p.OBJECT_ID
				And us.index_id = p.index_id
				And us.database_id = DB_ID()
			LEFT Outer Join #indstats ps
				ON p.index_id = ps.index_id
				And ps.database_id = DB_ID()
				And p.OBJECT_ID = ps.OBJECT_ID
		--WHERE OBJECTPROPERTY(p.OBJECT_ID,'IsMSShipped') = 0
	)
	, OverFlowData AS (
		SELECT a.container_id,p.OBJECT_ID,p.index_id,us.database_id
			,FileGroupName = FILEGROUP_NAME(a.data_space_id)
			,TableName = OBJECT_NAME(p.OBJECT_ID)
			,NumRows = p.ROWS
			,UsedPages = IsNull(a.used_pages,0)
			,TotalPages = IsNull(a.total_pages,0)
			,DataSizeMB = CONVERT(DECIMAL(19,2),IsNull(a.used_pages,0)) * 8/1024
			,IndexSizeMB = CASE WHEN ps.index_id < 2 THEN 0 ELSE ps.IndexSizeMB END
			,UserRequests = IsNull(us.user_seeks,0) + IsNull(us.user_scans,0) + IsNull(us.user_lookups,0)
			,UserUpdates = IsNull(us.user_updates,0)
			,LastUpdate = IsNull(us.last_user_update,null)
			,RatioRequestsToUpdates = CAST(IsNull(us.user_seeks,0)
					+ IsNull(us.user_scans,0) + IsNull(us.user_lookups,0) AS REAL)
				/ CAST(CASE us.user_updates WHEN 0 THEN 1 ELSE us.user_updates END AS REAL)
		FROM sys.allocation_units a
			INNER Join sys.partitions p
				ON p.hobt_id = a.container_id
				And a.type = 3
			LEFT Outer Join sys.dm_db_index_usage_stats us
				ON us.OBJECT_ID = p.OBJECT_ID
				And us.index_id = p.index_id
				And us.database_id = DB_ID()
			LEFT Outer Join #indstats ps
				ON p.index_id = ps.index_id
				And ps.database_id = DB_ID()
				And p.OBJECT_ID = ps.OBJECT_ID
		--WHERE OBJECTPROPERTY(p.OBJECT_ID,'IsMSShipped') = 0
	), IndexSum AS (
		SELECT a.OBJECT_ID
		,AllDataSizeMB = SUM(CASE WHEN a.index_id in (0,1)
							THEN IsNull(a.DataSizeMB,0) +  IsNull(p2.DataSizeMB,0) +  IsNull(p3.DataSizeMB,0)
							ELSE IsNull(p2.DataSizeMB,0) + IsNull(p3.DataSizeMB,0)
							END)
		FROM RegData a
			LEFT Outer Join LOBData p2
				ON p2.container_id = a.container_id
			LEFT Outer Join OverFlowData p3
				ON p3.container_id = a.container_id
		GROUP BY a.OBJECT_ID
	), SummaryInfo AS (
		SELECT
			TableName = MAX(a.TableName)
			,InRowDataSizeMB = SUM(IsNull(a.DataSizeMB,0))
			,LOBDataSizeMB = SUM(IsNull(p2.DataSizeMB,0))
			,OFlowDataSizeMB = SUM(IsNull(p3.DataSizeMB,0))
			,NumRows = MAX(COALESCE(a.NumRows,p2.NumRows,p3.NumRows,0))
			,AllUsedPages = SUM(IsNull(a.UsedPages,0) + IsNull(p2.UsedPages,0) + IsNull(p3.UsedPages,0))
			,AllPages = SUM(IsNull(a.TotalPages,0) + IsNull(p2.TotalPages,0) + IsNull(p3.TotalPages,0))
			,FreeDataSpace = CONVERT(DECIMAL(19,2),
				SUM(IsNull(a.TotalPages,0) + IsNull(p2.TotalPages,0) + IsNull(p3.TotalPages,0))
				- SUM(IsNull(a.UsedPages,0) + IsNull(p2.UsedPages,0) + IsNull(p3.UsedPages,0)))* 8 / 1024
			,AllDataSizeMB = MAX(ids.AllDataSizeMB)
			,IndexSizeMB = SUM(IsNull(a.IndexSizeMB,0))
				+ SUM(IsNull(p2.IndexSizeMB,0)) + SUM(IsNull(p3.IndexSizeMB,0))
			,UserRequests = AVG(IsNull(a.UserRequests,0)
				+ IsNull(p2.UserRequests,0) + IsNull(p3.UserRequests,0))
			,UserUpdates = AVG(IsNull(a.UserUpdates,0) + IsNull(p2.UserUpdates,0) + IsNull(p3.UserUpdates,0))
			,LastUpdate = MAX(COALESCE(a.LastUpdate,p2.LastUpdate,p3.LastUpdate,null))
			,DatabaseSize = @dbsize
		FROM RegData a
			LEFT Outer Join LOBData p2
				ON p2.container_id = a.container_id
			LEFT Outer Join OverFlowData p3
				ON p3.container_id = a.container_id
			LEFT Outer Join sys.indexes i
				ON i.OBJECT_ID = a.OBJECT_ID
				And i.index_id = a.index_id
			LEFT Outer Join IndexSum ids
				ON i.OBJECT_ID = ids.OBJECT_ID
		--WHERE filegroup_name(a.data_space_id) = 'Primary'
		GROUP BY a.OBJECT_ID
	)
	SELECT TableName,NumRows,InRowDataSizeMB,LOBDataSizeMB,OFlowDataSizeMB
			,AllUsedPages,AllPages
			,FreeDataSpace,AllDataSizeMB,IndexSizeMB
			,TableSizeMB = AllDataSizeMB + IndexSizeMB + FreeDataSpace
			,UserRequests,UserUpdates,LastUpdate
			,PercentofDB = ((IndexSizeMB + AllDataSizeMB) / DatabaseSize) * 100
			,DatabaseSize
		FROM SummaryInfo
		ORDER BY PercentofDB DESC
END

At some point in the future, I intend on modifying this query to make it more flexible in output, as well as to make it into a stored procedure.

Comments

Posted by Anonymous on 20 May 2010

Pingback from  Dew Drop &#8211; May 20, 2010 | Alvin Ashcraft&#039;s Morning Dew

Posted by Dukagjin Maloku on 20 May 2010

Jason,

I guess that SSOLV members enjoy your nice presentation, thanks for sharing and explanation!

Posted by Dukagjin Maloku on 20 May 2010

WOW Spam attack ... same as in one of my post!

Posted by Jason Brimhall on 20 May 2010

Holy crap - quite the spam attack.  Time to delete 35 comments about various cars for sale.

Posted by Jason Brimhall on 20 May 2010

Dukagjin,

I will be finishing up this series with the remainder of what was presented and then some. Stay tuned :)

Posted by Anonymous on 20 May 2010

Pingback from  Stop Lamp Volvo S80, Order S800

Posted by Anonymous on 20 May 2010

Pingback from  Eos Rebel Xsi Black Slr Digital Camera, Eos 450d 1000d

Posted by Anonymous on 20 May 2010

Pingback from  Gmc R3500 Side Skirts, R350 Heater Order

Posted by Anonymous on 20 May 2010

Pingback from  F100fd Black, 1974 Ford F100

Posted by Anonymous on 20 May 2010

Pingback from  560sl Game, 1988 Parts Mercedes Benz 560sl

Posted by Anonymous on 20 May 2010

Pingback from  Entourage Change Date, Watch Entourage Online Boiler Scared Straight

Posted by Anonymous on 20 May 2010

Pingback from  Sw1 Rent Agents, Saturn Sw1 Car Mats

Posted by Anonymous on 20 May 2010

Pingback from  Pilot Used Honda Odyssey Lease Accord Coupe, Daytime Running Lights Honda Pilot

Posted by Anonymous on 20 May 2010

Pingback from  Infusion Jar Stands, Parts Style 2006 Ford Fusion Body Kits

Posted by Anonymous on 20 May 2010

Pingback from  Volvo 264 Part Fuel Pump, Volvo 264 Bertone

Posted by Anonymous on 20 May 2010

Pingback from  Clk500 Sale Clk55 Amg Ignition, Clk500 Clearance Mercedes Class Clk 63 Amg

Posted by Anonymous on 20 May 2010

Pingback from  Dodge W250 Aftermarket Oem Parts Ram 1500, Aggiungere Ram Mac Mini G4

Posted by Anonymous on 20 May 2010

Pingback from  2006 Avalanche 2500 Fuel Economy 2005 Chevrolet Silverado, Gemballa Avalanche For Sale 911 Turbo News

Posted by Anonymous on 20 May 2010

Pingback from  Eastern Music Festival Transmission, 1981 Chevrolet Chevette Manual Transmission

Posted by Anonymous on 20 May 2010

Pingback from  Used Volvo Xc90 For Sale Warranty, 09 Xc90 Volkswagen Touareg

Posted by Anonymous on 20 May 2010

Pingback from  Volvo P1800 144 145 Sale, 145 Halogen

Posted by Anonymous on 20 May 2010

Pingback from  1964 Chevy Corvair Parts, Corvair Used S10 1964 Chevrolet

Posted by Anonymous on 20 May 2010

Pingback from  Five Hundred Used Chrome Door Handle Covers, Ford Five Hundred Sale Owner Listings Online

Posted by Anonymous on 20 May 2010

Pingback from  Nissan 240sx Wiki, 1995 Nissan 240sx Aftermarket Parts

Posted by Anonymous on 20 May 2010

Pingback from  Discount Corinthian Leather 1975 Chrysler Cordoba, Factory Chrysler Cordoba

Posted by Anonymous on 20 May 2010

Pingback from  Bleed Brakes 240sx, 93 Nissan 240sx Pics

Posted by Anonymous on 20 May 2010

Pingback from  250sl Value Auto Parts, Headlight 250sl Mercedes Benz 380sl

Posted by Anonymous on 20 May 2010

Pingback from  Child Bike Seat Copilot Limo, Pilot Car

Posted by Anonymous on 20 May 2010

Pingback from  Gmc P1500 Promo Ac Delco, 2008 Gmc Sierra 1500 Forum

Posted by Anonymous on 20 May 2010

Pingback from  Firebird Server Amd Opteron, Firebird Our Camaro Parts Trans Am

Posted by Anonymous on 20 May 2010

Pingback from  Ac Adapter 12v 300ma, Chrysler 300m Replacement Driver Side Mirror

Posted by Anonymous on 20 May 2010

Pingback from  Find Mercedes Benz G500 Cadillac Escalade, Mercedes Benz G500 Lightbulb Piaa

Posted by Anonymous on 20 May 2010

Pingback from  Ub12120 Universal Power Group, Nissan Versa Sl Forum

Posted by Jason Brimhall on 20 May 2010

Heh - deleted 30 comments and now have 30 more within the same day.

Posted by Anonymous on 20 May 2010

Pingback from  Express 2500 Aftermarket Brakes Kitty, Swift Express

Posted by Anonymous on 20 May 2010

Pingback from  325ix Car Parts Discount Bmw 330xi, 325ix Cooling System Thermostat Housing

Posted by Anonymous on 20 May 2010

Pingback from  Sapporo Refurbished Keio Plaza Hotel, Sapporo Aftermarket New Plymouth Order

Posted by Anonymous on 20 May 2010

Pingback from  Celebrity Hot Sheet, Celebrity Homes Bangalore

Posted by Anonymous on 20 May 2010

Pingback from  Legend Provides Data, Codes Legend Zelda Wind Waker Gamecube

Posted by Anonymous on 20 May 2010

Pingback from  Plymouth Bp300 Second Hand, P30 Radiator Oe Covered

Posted by Anonymous on 20 May 2010

Pingback from  Sundance Exhaust Call Mandrel Bent, Sundance Repair Routine Maintenance 1994 Dodge Shadow

Posted by Anonymous on 20 May 2010

Pingback from  Convertible 2000 Volvo C70, 2001 Volvo S80 Sedan

Posted by Anonymous on 20 May 2010

Pingback from  Part 2003 Subaru Outback, Timing Belt Parts 2008 Subaru Outback

Posted by Anonymous on 20 May 2010

Pingback from  Tiburon Headlight Low Price Guarantee, Hyundai Tiburon Electrical Help

Posted by Anonymous on 20 May 2010

Pingback from  Dodge Dakota Discount Extended Cab Pickup Trucks, Aberdeen South Dakota Jobs

Posted by Anonymous on 20 May 2010

Pingback from  K10 Pickup Coupon, K10 Collection Headlight

Posted by Anonymous on 20 May 2010

Pingback from  Brother M K231 Tape Ink Depot Pt 100, 2003 Trek 1000 Specs

Posted by Anonymous on 20 May 2010

Pingback from  Walmart Omni, Cobra Consolidated Omnibus Budget Reconciliation Act

Posted by Anonymous on 20 May 2010

Pingback from  Student Eurostar Tickets, Headlight Ac Saturn Starfire

Posted by Anonymous on 20 May 2010

Pingback from  Scoupe Headlight Slyvania Wiper Blade, Scoupe Airways

Posted by Anonymous on 20 May 2010

Pingback from  Rodeo Msrp, 1996 Honda Passport Info Isuzu Rodeo

Posted by Anonymous on 20 May 2010

Pingback from  Lexus Lx450 Discount Autozone, Lexus Lx450 Replacement Land Cruiser Left

Posted by Anonymous on 20 May 2010

Pingback from  Station Wagon Used Cutlass Oldsmobile Bravada, Bravada Discount 1993 Oldsmobile Cutlass Ciera

Posted by Anonymous on 20 May 2010

Pingback from  Buick Rendezvous Seat Belt Universal, Used Reviews New Buick Rendezvous

Posted by Anonymous on 20 May 2010

Pingback from  Canyon 3500 Lake Mead, Xl 1500 Electronic 2003 Gmc Yukon

Posted by Anonymous on 20 May 2010

Pingback from  300sd Heater Automatic Brake Master Cylinder, 300sd Cheap Cars

Leave a Comment

Please register or log in to leave a comment.