space used by a table?

[Ruaidhri]

I have a database that has grown larger than I had expected. I think
there is one table that is at the root of the problem. The table is
defined as follows:

CREATE TABLE [dbo].[UserAudit] (
[UserAudit_id] [int] IDENTITY (1, 1) NOT NULL ,
[UserAuditAction _id] [int] NOT NULL ,
[Dataset_id] [int] NOT NULL ,
[UserName] [char] (64) NOT NULL ,
[TableName] [char] (64) NOT NULL ,
[Detail] [varchar] (4000) NOT NULL ,
[DateRecorded] [smalldatetime] NOT NULL ,
[Dsc] [char] (256) NULL
)

There are 14919 records in this table. When I do the calculations i
approximate that space used by this table should be in the region of
10mb (4+4+4+64+64+29 0(average length of Detail column)+4+256) * 14919.

When I execute "sp_spaceus ed 'UserAudit'" i see that 119MB are being
used by this table.

name rows reserved data index_size unused
UserAudit 14919 119808 KB 119352 KB 400 KB 56 KB


Even if I use 4000 for the Detail column in my calculations I still
come up with about only 64mb.

Any ideas on whats going on here?
Thanks in advance.

[Simon Hayes]

Re: space used by a table?


"Ruaidhri" <rgarvey@polymo rphia.com> wrote in message
news:7681c3eb.0 403020917.1d776 9ae@posting.goo gle.com...[color=blue]
> I have a database that has grown larger than I had expected. I think
> there is one table that is at the root of the problem. The table is
> defined as follows:
>
> CREATE TABLE [dbo].[UserAudit] (
> [UserAudit_id] [int] IDENTITY (1, 1) NOT NULL ,
> [UserAuditAction _id] [int] NOT NULL ,
> [Dataset_id] [int] NOT NULL ,
> [UserName] [char] (64) NOT NULL ,
> [TableName] [char] (64) NOT NULL ,
> [Detail] [varchar] (4000) NOT NULL ,
> [DateRecorded] [smalldatetime] NOT NULL ,
> [Dsc] [char] (256) NULL
> )
>
> There are 14919 records in this table. When I do the calculations i
> approximate that space used by this table should be in the region of
> 10mb (4+4+4+64+64+29 0(average length of Detail column)+4+256) * 14919.
>
> When I execute "sp_spaceus ed 'UserAudit'" i see that 119MB are being
> used by this table.
>
> name rows reserved data index_size unused
> UserAudit 14919 119808 KB 119352 KB 400 KB 56 KB
>
>
> Even if I use 4000 for the Detail column in my calculations I still
> come up with about only 64mb.
>
> Any ideas on whats going on here?
> Thanks in advance.[/color]

Probably the first thing to try is @updateusage, and see if that changes the
figures you see:

exec sp_spaceused @updateusage = 'TRUE'

See DBCC UPDATEUSAGE for more information.

Simon

[Ruaidhri Garvey]

Re: space used by a table?

Thanks for the input Simon. I tried this and unfortunately nothing
changed.



*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

[Gert-Jan Strik]

Re: space used by a table?

It seems the table is extemely fragmented.

Make sure the table has a clustered index and reindex the table.

If the table does not have a clustered index, then add a clustered index
and drop it again.

Hope this helps,
Gert-Jan


Ruaidhri Garvey wrote:[color=blue]
>
> Thanks for the input Simon. I tried this and unfortunately nothing
> changed.
>
> *** Sent via Developersdex http://www.developersdex.com ***
> Don't just participate in USENET...get rewarded for it![/color]

--
(Please reply only to the newsgroup)

[Erland Sommarskog]

Re: space used by a table?

Ruaidhri (rgarvey@polymo rphia.com) writes:[color=blue]
> There are 14919 records in this table. When I do the calculations i
> approximate that space used by this table should be in the region of
> 10mb (4+4+4+64+64+29 0(average length of Detail column)+4+256) * 14919.
>
> When I execute "sp_spaceus ed 'UserAudit'" i see that 119MB are being
> used by this table.
>
> name rows reserved data index_size unused
> UserAudit 14919 119808 KB 119352 KB 400 KB 56 KB
>
>
> Even if I use 4000 for the Detail column in my calculations I still
> come up with about only 64mb.[/color]

Gert-Jan's suggestion about fragmentation is certainly worth pursuing.
DBCC SHOWCONIG on the table can give some information. Scan Densitity
should be close to 100% for a table with low level of fragmentation.

But if we assume that many rows in fact have the maximum length, then
then value is not at all unreasonable. To wit, the maximum row size
is around 4400 bytes. And since the page size in SQL Server is 8192
bytes (with some bytes lost for internal data), you can only fit one
such row on a page. 8192*14919/1024 = 119352, which is exactly what
you have in data...

Hm, when you determined the average length of Detail, did you use the
len() function? What if you use datalength() instead? The difference
here, is that len() does not count trailing space.

--
Erland Sommarskog, SQL Server MVP, sommar@algonet. se

Books Online for SQL Server SP3 at

http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp

[Ruaidhri Garvey]

Re: space used by a table?

Great feedback Erland thanks. I think you have hit the nail on the head.

When I ran the below statement, 4000 was in fact the average. Each row
must be filled with trailing spaces.

select avg(datalength( detail))
from UserAudit



*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

[Erland Sommarskog]

Re: space used by a table?

Ruaidhri Garvey (rgarvey@polymo rphia.com) writes:[color=blue]
> Great feedback Erland thanks. I think you have hit the nail on the head.
>
> When I ran the below statement, 4000 was in fact the average. Each row
> must be filled with trailing spaces.
>
> select avg(datalength( detail))
> from UserAudit[/color]

There is probably reason to look into why you are save all those trailing
blanks.

Way back in 4.2 and 6.0, SQL Server always trimmed trailing spaces
when you saved data. But in ANSI SQL, the word was that trailing
spaces should be save. So in 6.5, Microsoft introduced the setting
SET ANSI_PADDING ON, and by SQL 2000, this setting is the default
in most context. While you could play with it (note that the setting
when the column was created applies), it's probably better to find out
where the spaces comes from. Maybe it is a simple that you call a
stored procedure with a parameter @detail which is char(4000).

--
Erland Sommarskog, SQL Server MVP, sommar@algonet. se

Books Online for SQL Server SP3 at

http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp