| Author |
 Topic  |
|
|
aiken
Aged Yak Warrior
USA
525 Posts |
 Posted - 11/26/2001 : 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 - 03/19/2003 : 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
USA
525 Posts |
Posted - 05/20/2003 : 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 - 06/10/2003 : 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 Posts |
Posted - 08/29/2003 : 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
USA
35007 Posts |
Posted - 08/29/2003 : 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
USA
1 Posts |
|
|
jerryhung
Starting Member
2 Posts |
Posted - 11/10/2006 : 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 Posts |
Posted - 01/03/2007 : 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
USA
35007 Posts |
Posted - 01/03/2007 : 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 |
|
|
| |
Topic |
|
|
|
Modified bigtables.sql
