SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 Script Library
 Modified bigtables.sql
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

aiken
Aged Yak Warrior

USA
525 Posts

Posted - 11/26/2001 :  16:14:24  Show Profile  Send aiken an ICQ Message  Reply with Quote
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 - 03/19/2003 :  01:57:44  Show Profile  Reply with Quote
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

USA
525 Posts

Posted - 05/20/2003 :  13:08:53  Show Profile  Send aiken an ICQ Message  Reply with Quote
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 - 06/10/2003 :  13:07:23  Show Profile  Reply with Quote
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 Posts

Posted - 08/29/2003 :  15:44:06  Show Profile  Reply with Quote
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

USA
36845 Posts

Posted - 08/29/2003 :  16:07:49  Show Profile  Visit tkizer's Homepage  Reply with Quote
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

USA
1 Posts

Posted - 09/21/2005 :  16:10:59  Show Profile  Send yoadrian11 a Yahoo! Message  Reply with Quote
what does the unused % really represent?

Thanks
yoadrian11@yahoo.com
Go to Top of Page

jerryhung
Starting Member

2 Posts

Posted - 11/10/2006 :  15:43:09  Show Profile  Reply with Quote
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 Posts

Posted - 01/03/2007 :  14:01:16  Show Profile  Reply with Quote
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

USA
36845 Posts

Posted - 01/03/2007 :  14:03:51  Show Profile  Visit tkizer's Homepage  Reply with Quote
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
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.2 seconds. Powered By: Snitz Forums 2000