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

Finding Table Space

By Amit Lohia,

In my last article "Suggestion of Datatypes" I described a technique to scan the existing data to determine a better data type for columns, which may save some space. In this article I will focus on determining the size of the objects. In other words, if I have a database of 50GB, I would like to know which are the largest tables in terms of occupying the space (including Indexes and Text/Image Data). The sp_spaceused system procedure can be used to determine this type of information. With this knowledge we can find out how the sp_spaceused stored procedure gets it information, and we can customize it based on our requirements.

With this kind of routine, you should be able to keep an eye on growing tables, and predict the future disk space requirements (assuming the activities remain more or less the same).

Objective:

To determine the space taken by an object within the database.

Code Review:

If you are guessing I will be relying on sysindexes then you are correct. Before we go into the details of the coding, let me explain sysindexes in brief:

It contains one row for each index and table in the database. Column Indid explains whether the entry is of an index or a table:

When Indid = 0 it means a table without a clustered Index

When Indid =1 it means a table with a clustered Index

When Indid >1 it means an index

When Indid = 255 it means an entry for tables that have text or image data

For Indid =1 or 0, column Used determines the total count of pages for all index and table data; and for Indid = 255, Used is a count of the pages used for text or image data.

Column DPages stores the count of data pages for a table with or without clustered Index. For Indid= 255, it is set to 0.

As we know sysindexes, may not always report current information, so to ensure that, we will be required to run DBCC UPDATEUSAGE.

Let us go into the code now.

-- As mentioned earlier, to remove inaccuracies on sysindexes we will run:

DBCC UpdateUsage (0)
WITH  COUNT_ROWS, NO_INFOMSGS

-- We will create a table to store the information:
if NOT exists (select * from dbo.sysobjects 
               where id = object_id(N'[dbo].[SpaceUsedByObject]') 
					and OBJECTPROPERTY(id, N'IsUserTable') = 1)
Begin
	CREATE Table SpaceUsedByObject
	( 	
	[Id] INT Identity(1,1),
  	ObjName sysname,
	TotalSpaceUsed INT,
	DataSpaceUsed INT,
	IndexSpaceUsed INT,
	RowCnt INT,
	TodayDate DateTime Default Getdate()
	)
End

As mentioned earlier, Indid 0 or 1 records the total count of pages for all index and table data, and Indid 255 determine count of the pages used for text or image data. With this information, let add records to the newly created table: */

INSERT INTO SpaceUsedByObject(ObjName,TotalSpaceUsed)
  SELECT Object_Name(sysindexes.ID),SUM(Used)
   FROM sysindexes JOIN sysobjects
	   ON sysindexes.Id=sysobjects.Id and type='u'
   WHERE Indid IN (0,1,255)
   GROUP BY sysindexes.ID

-- We will create a temporary table (a table variable could also be used):
CREATE Table #SpaceUsedByObjectForDpage 
(
ObjName sysname,
DataSpaceUsed INT,
rowcnt INT
)

-- Let us store the DPages 
-- (Data pages which consist of actually table data):
INSERT INTO #SpaceUsedByObjectForDpage
 SELECT Object_Name(sysindexes.ID),Dpages,rowcnt
  FROM sysindexes 
       JOIN sysobjects 
	 ON sysindexes.Id=sysobjects.Id 
	 and type='u'
  WHERE Indid IN (0, 1)

-- Now we will add the Text and images pages to Dpages:

UPDATE #SpaceUsedByObjectForDpage
SET DataSpaceUsed= DataSpaceUsed 
	+ (SELECT ISNULL(SUM(Used),0)
		FROM SysIndexes S1
		WHERE Object_Name(s1.Id)= #SpaceUsedByObjectForDpage.ObjName	
		AND indid=255
	   )	

/* As we have the total
used space and Data pages (table space excluding Indexes), it is
fairly simple to determine the Index space (i.e. Total Space –
Data Pages), so let’s update the main table with our
calculation: */

UPDATE SpaceUsedByObject
SET IndexSpaceUsed = TotalSpaceUsed
         	- (SELECT DataSpaceUsed
           	    FROM #SpaceUsedByObjectForDpage S1
                WHERE S1.ObjName=SpaceUsedByObject.ObjName
                AND SpaceUsedByObject.IndexSpaceUsed IS NULL
	           )
 WHERE IndexSpaceUsed IS NULL	

-- Now Data pages and row count:
UPDATE SpaceUsedByObject
SET DataSpaceUsed = ( SELECT S1.DataSpaceUsed
                       FROM #SpaceUsedByObjectForDpage S1
                       WHERE S1.ObjName=SpaceUsedByObject.ObjName
	                 ),
   rowcnt=( SELECT S1.rowcnt
	          FROM #SpaceUsedByObjectForDpage S1
             WHERE S1.ObjName=SpaceUsedByObject.ObjName
          )
 WHERE DataSpaceUsed IS NULL

Additional points

Our calculations are in terms of pages. Size of each page in SQL 2000 is 8KB. You can also determine page size by querying the undocumented system table:

SELECT low 
 from master.dbo.spt_values
 where number = 1 and type = 'E'  

We can come to this calculation by going the other way, meaning by calculating Index and Data pages, and then calculating total used space. We can set this up as a job monthly or biweekly, or on demand, to see the growth of a particular table, and determine a growth pattern of our database. Sysindexes have other information like total reserved space (column Reserved). and xmaxlen and minlen determine the maximum and minimum size of the row.

If you see xmaxlen for any table to be more than 4030 bytes, it means only 1 record is able to fit in a page. So that means we will have page splits and space wasted.

Example If the records size is 5000 bytes, SQL Server is not able to utilize 3060 bytes and this will be true for all the records with similar size.

Or for example, if I have a record size of 2200 bytes, only 2 records will fit in the page, occupying 4400 bytes and leaving 3660 bytes under utilized. There are several ways to determine such information and manage our resources properly. In particular, see my previous article to try to determine a proper data type, and see if it is possible to reduce the record size so less unutilized space is left.

Conclusion:

The sysindexes system table has much important information, and we can build lots of reports from it to suit our needs. I do agree SQL Server has many system procedures and DBCC commands which help us to get most of the information, but at the same time I believe a true DBA should know how things are internally worked, so he/she can customized the result to suit their requirement.

We can use undocumented SP_MSTablespace and SP_MSindexspace to have similar information. Both the procedure will report inaccurate results if DBCC UpdateUsage is not executed. Ken Henderson in his book The Guru’s Guide to Transact-SQL had mentioned more than 100 undocumented commands, languages, and features.

Total article views: 13319 | Views in the last 30 days: 8
 
Related Articles
FORUM

DBCC SHOWCONTIG and indid

DBCC SHOWCONTIG and indid

BLOG

Determine space used for each table in a SQL Server database

I have written following Microsoft SQL Server T-SQL scirpt to quickly determine space used for each ...

FORUM

index space inside data file

index space inside data file

FORUM

Determinating the space used in specific user table(s)

Determinating the space used in specific user table(s)

FORUM

Index Selectivity

Determining usefulness of an index

Tags
 
Contribute

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones
Editor, SQLServerCentral.com

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones