Modified bigtables.sql - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

Script Library

Modified bigtables.sql

Author

Topic

aiken
Aged Yak Warrior

525 Posts

Posted - 2001-11-26 : 16:14:24

Here's an updated version of bigtables.sql that also displays the ratio of index size to data size and the percentage of unused space per table. I've found the index to data ratio particularly helpful for finding and fixing over-indexing.

Cheers
-b

/**************************************************************************************
*
*  BigTables.sql
*  Bill Graziano (SQLTeam.com)
*  graz@sqlteam.com
*  v1.11
*
**************************************************************************************/

declare @id	int			
declare @type	character(2) 		
declare	@pages	int			
declare @dbname sysname
declare @dbsize dec(15,0)
declare @bytesperpage	dec(15,0)
declare @pagesperMB		dec(15,0)


create table #spt_space
(
	objid		int null,
	rows		int null,
	reserved	dec(15) null,
	data		dec(15) null,
	indexp		dec(15) null,
	unused		dec(15) null
)

set nocount on

-- Create a cursor to loop through the user tables
declare c_tables cursor for
select	id
from	sysobjects
where	xtype = 'U'

open c_tables

fetch next from c_tables
into @id

while @@fetch_status = 0
begin

	/* Code from sp_spaceused */
	insert into #spt_space (objid, reserved)
		select objid = @id, sum(reserved)
			from sysindexes
				where indid in (0, 1, 255)
					and id = @id

	select @pages = sum(dpages)
			from sysindexes
				where indid < 2
					and id = @id
	select @pages = @pages + isnull(sum(used), 0)
		from sysindexes
			where indid = 255
				and id = @id
	update #spt_space
		set data = @pages
	where objid = @id


	/* index: sum(used) where indid in (0, 1, 255) - data */
	update #spt_space
		set indexp = (select sum(used)
				from sysindexes
				where indid in (0, 1, 255)
				and id = @id)
			    - data
		where objid = @id

	/* unused: sum(reserved) - sum(used) where indid in (0, 1, 255) */
	update #spt_space
		set unused = reserved
				- (select sum(used)
					from sysindexes
						where indid in (0, 1, 255)
						and id = @id)
		where objid = @id

	update #spt_space
		set rows = i.rows
			from sysindexes i
				where i.indid < 2
				and i.id = @id
				and objid = @id

	fetch next from c_tables
	into @id
end


select top 25
	Table_Name = (select left(name,25) from sysobjects where id = objid),
	rows = convert(char(11), rows),
	reserved_KB = ltrim(str(reserved * d.low / 1024.,15,0) + ' ' + 'KB'),
	data_KB = ltrim(str(data * d.low / 1024.,15,0) + ' ' + 'KB'),
	index_size_KB = ltrim(str(indexp * d.low / 1024.,15,0) + ' ' + 'KB'),
	unused_KB = ltrim(str(unused * d.low / 1024.,15,0) + ' ' + 'KB'),
        idx_data_ratio = ltrim(str(indexp*100 /data) + '%'),
        unused_pct = ltrim(str(unused * 100 /reserved) + '%')
		
from 	#spt_space, master.dbo.spt_values d
where 	d.number = 1
and 	d.type = 'E'
order by reserved desc

drop table #spt_space
close c_tables
deallocate c_tables

Edited by graz to add code tags around the code.

bflorac
Starting Member

2 Posts

Posted - 2003-03-19 : 01:57:44

Script fails if data or reserved are 0. I modified the result select statement to case out 0 values:

select top 25
Table_Name = (select left(name,25) from sysobjects where id = objid),
rows = convert(char(11), rows),
reserved_KB = ltrim(str(reserved * d.low / 1024.,15,0) + ' ' + 'KB'),
data_KB = ltrim(str(data * d.low / 1024.,15,0) + ' ' + 'KB'),
index_size_KB = ltrim(str(indexp * d.low / 1024.,15,0) + ' ' + 'KB'),
unused_KB = ltrim(str(unused * d.low / 1024.,15,0) + ' ' + 'KB'),

case data
when 0 then '100%'
else ltrim(str(indexp*100 /data) + '%')
end as idx_data_ratio,

case reserved
when 0 then '100%'
else ltrim(str(unused * 100 /reserved) + '%')
end as unused_pct

from #spt_space, master.dbo.spt_values d
where d.number = 1
and d.type = 'E'
order by reserved desc

Bill Florac

aiken
Aged Yak Warrior

525 Posts

Posted - 2003-05-20 : 13:08:53

Important note: Be sure to run DBCC UPDATEUSAGE('database') before running bigtables.sql in order to ensure accurate results.

I just spent a couple of hours pulling my hair out trying to figure out why a 12GB table was 46% unused -- after updateusage, all of the data and index numbers are the same, but the unused percentage dropped to 7%. Apparently sysobjects was wrong about the number of pages in the table.

FYI
-b

nguyen
Starting Member

8 Posts

Posted - 2003-06-10 : 13:07:23

I changed the script into a stored procedure. I placed this in my Master DB, so I can run the SP in any DB. I've also added an @orderby attribute, so I can sort the results as needed. And changed the results to "mega bytes" (I divided it by 1000.. I know a real megabyte is 1028K or something like that).

________-------------------------------------------------------------
CREATE PROCEDURE sp_spaceUsedByTable
@orderby varchar(50)='reserved_MB desc'
AS

/*
NOTE: you may need to run (takes a long time) DBCC UPDATEUSAGE('database')
You can also specify an @orderby
Example: sp_spaceUsedByTable @orderby='rows desc'

Louis Nguyen
*/

set nocount on
set ansi_warnings off
set transaction isolation level read uncommitted

create table #S
(
[name] varchar(50) null,
[rows] varchar(50) null,
[reserved] varchar(50) null,
[data] varchar(50) null,
[index_size] varchar(50) null,
[unused] varchar(50) null
)

-- Create a cursor to loop through the user tables
declare @name varchar(50)
declare c_tables cursor for
select name from sysobjects where xtype = 'U'

open c_tables
fetch next from c_tables
into @name

while @@fetch_status = 0 begin
insert into #S
exec sp_spaceUsed @name
fetch next from c_tables into @name
end

close c_tables deallocate c_tables

select [name],[rows],reserved_MB,data_MB,[index_MB],unused_MB
into #T
from(
select [name]
,[rows]=cast([rows] as int)
,reserved_MB=cast(replace(reserved,'KB','') as int)/1000
,data_MB=cast(replace(data,'KB','') as int)/1000
,[index_MB]=cast(replace(index_size,'KB','') as int)/1000
,unused_MB=cast(replace(unused,'KB','') as int)/1000
from #S
) as XX
order by reserved_MB desc

exec ('select * from #T order by '+@orderby)

drop table #S
drop table #T

Keith Mescha
Starting Member

1 Post

Posted - 2003-08-29 : 15:44:06

I try running this however many of my tables are not owned by dbo so on all the tables where the owner name is other than dbo this fails. Any work around for this situation?

Keith

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-08-29 : 16:07:49

You'll have to change the code in a couple of places. The cursor's select statement will have to be changed so that it grabs the owner's name for each object (uid column needs to be used in select statement). You'll also have to change the exec sp_spaceused part so that it uses the ownername.objectname.

Tara

yoadrian11
Starting Member

1 Post

Posted - 2005-09-21 : 16:10:59

what does the unused % really represent?

Thanks
yoadrian11@yahoo.com

jerryhung
Starting Member

2 Posts

Posted - 2006-11-10 : 15:43:09

sorry to bring this up, this script is very useful

my question are
- what does 'index-to-data' ratio mean? the higher the better?
- what does 'unused %' mean? the higher the better?

jac
Starting Member

1 Post

Posted - 2007-01-03 : 14:01:16

Hi, I have a problem. In a table, I have a negative value in the index_Size column. Do you know why?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-01-03 : 14:03:51

quote:
Originally posted by jac

Hi, I have a problem. In a table, I have a negative value in the index_Size column. Do you know why?

Run DBCC UPDATEUSAGE to correct inaccuracies in sysindexes.

Tara Kizer

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources