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


Go to Top of Page

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

Go to Top of Page

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



Go to Top of Page

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
Go to Top of Page

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
Go to Top of Page

yoadrian11
Starting Member

1 Post

Posted - 2005-09-21 : 16:10:59
what does the unused % really represent?

Thanks
yoadrian11@yahoo.com
Go to Top of Page

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?
Go to Top of Page

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?
Go to Top of Page

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
Go to Top of Page
   

- Advertisement -