June 19, 2016 at 1:28 am
Hi Experts,
Observed one table with very high page count but the size of the table is very small. Can you help us understand why?
Table Sizez:840 KB
Page Count:81898
Table got a column with datatype varbinary(Max) which stores attachment.
June 19, 2016 at 4:17 am
VastSQL (6/19/2016)
Table got a column with datatype varbinary(Max) which stores attachment.
That's probably why. Varchar(max) columns are stored out of row, so depending how that 'table size' is being calculated, they may not affect it.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
June 19, 2016 at 5:01 am
GilaMonster (6/19/2016)
VastSQL (6/19/2016)
Table got a column with datatype varbinary(Max) which stores attachment.That's probably why. Varchar(max) columns are stored out of row, so depending how that 'table size' is being calculated, they may not affect it.
Thanks Gail but why such a big difference if we calculate 1KB instead of 8 for the whole pages the size will be much more than the present value.
June 19, 2016 at 2:40 pm
Depending how the 'table size' is being calculated, it may not take the LOB columns into account.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
June 20, 2016 at 12:48 am
GilaMonster (6/19/2016)
Depending how the 'table size' is being calculated, it may not take the LOB columns into account.
Used below script by Bill Graziano.
/**************************************************************************************
*
* BigTables.sql
* Bill Graziano (SQLTeam.com)
* v1.1
*
**************************************************************************************/
declare @idint
declare @typecharacter(2)
declare@pagesint
declare @dbname sysname
declare @dbsize dec(15,0)
declare @bytesperpagedec(15,0)
declare @pagesperMBdec(15,0)
create table #spt_space
(
objidint null,
rowsint null,
reserveddec(15) null,
datadec(15) null,
indexpdec(15) null,
unuseddec(15) null
)
set nocount on
-- Create a cursor to loop through the user tables
declare c_tables cursor for
selectid
fromsysobjects
wherextype = '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
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')
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
June 20, 2016 at 5:26 am
If you want accurate results, you probably don't want to use a script written for SQL 2000 on SQL 2014. Find a correct one for recent versions of SQL (ie not using sysobjects and sysindexes and other SQL 2000 conventions)
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
June 21, 2016 at 3:11 am
Thanks a lot Gail.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply