September 24, 2008 at 12:51 pm
SQL server is reserving a great deal of space within the database and causing everything to be bloated.
What should I do?
September 24, 2008 at 1:21 pm
Could you explain your problem a little more? How are you viewing the space used by tables?
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
September 25, 2008 at 7:02 am
This is what I was told:
Basically, many columns within select tables are allowing NULL values (as a default). No values are entered and because the data types are varchar(X), SQL server is reserving a great deal of space within the database and causing everything to be bloated.
Here are the tables:
Table Name Rows Reserved (KB) Data (KB) Index Size (KB) UnUsed (KB)
Table1 1079450 42066440 KB 5511024 KB 78776 KB 36476640 KB
Table2 609095 34410160 KB 4412056 KB 90936 KB 29907168 KB
Table3 609095 33447480 KB 4351776 KB 46624 KB 29049080 KB
Table4 741589 23550968 KB 3467656 KB 30192 KB 20053120 KB
Table5 521988 4522704 KB 1221448 KB 50880 KB 3250376 KB
September 25, 2008 at 9:48 am
varchar doesn't reserve space. If a varchar(4000) column contains only 4 characters, it takes only 6 byes to store (there are two bytes overhead because it's a variable length column). If the varchar(4000) is null, it takes no storage space at all, as SQL does not store nulls.
Have you tried rebuilding the clustered index?
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
September 25, 2008 at 10:05 am
in testing I see that nulls do in fact take up some space. Possibly my test is flawed. Could someone verify.
create table null_tst
(col1 varchar(4000) null)
sp_spaceused null_tst
yields result
null_tst0 0 KB0 KB0 KB0 KB
then i load nulls into table
insert into null_tst(col1)
values (null)
go 500
yields results
null_tst500 16 KB8 KB8 KB0 KB
and at 2500 nulls i get following from sp+_spaceused
null_tst2500 40 KB32 KB8 KB0 KB
Is there something that I am overlooking that is causing this, or do nulls take up some space?
September 25, 2008 at 10:26 am
Unless the column is marked as sparse, there is a (small) cost to storing nulls. There's a flag that gets set per row that gets set denoting it as null, but the space required to describe how long the varchar would be is still allocated.
On other (non-variable length) columns, such as int, the space is still allocated even when the Bit map is set.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
September 25, 2008 at 10:39 am
But it's not going to be 42 GB for a million rows.
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
September 25, 2008 at 10:48 am
Robert klimes (9/25/2008)
Is there something that I am overlooking that is causing this, or do nulls take up some space?
Don't forget that the row headers and footers do take some space. With the entire row null, you're fitting 500 rows onto 2 pages. A page is 8kb, including a page header (of about 96 bytes) and the slot indexes at the bottom of the page, etc, etc.
We're looking at about 32 bytes per row (including the slot index, the row header, the null bitmap, the variable column offset and a couple other things)
Sorry I can't be more exact, my resources on page structure are 600km away.
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
September 25, 2008 at 10:50 am
GilaMonster (9/25/2008)
But it's not going to be 42 GB for a million rows.
True. that's more than the overhead observed. Besides - that space isn't free and wouldn't come up as such.
On the large table - what's the fill factor of the clustered index? Speaking of which - have you tried rebuilding the clustered index? You may see that number drop drastically.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
September 25, 2008 at 10:55 am
GilaMonster (9/25/2008)
Matt Miller (9/25/2008)
On other (non-variable length) columns, such as int, the space is still allocated even when the Bit map is set.I don't think they are. Will test.
I should rephrase just a little. As far as I know - this applies to the non-character non-variable. A CHAR which is set to NULLable is treated like a variable length (meaning - it's essentially a VARCHAR in everything except for the label)
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
September 25, 2008 at 10:57 am
Matt Miller (9/25/2008)
GilaMonster (9/25/2008)
Matt Miller (9/25/2008)
On other (non-variable length) columns, such as int, the space is still allocated even when the Bit map is set.I don't think they are. Will test.
I should rephrase just a little. As far as I know - this applies to the non-character non-variable. A CHAR which is set to NULLable is treated like a variable length (meaning - it's essentially a VARCHAR in everything except for the label)
You're right. I checked.
5000 rows with a null int took 72kb, 5000 rows with a non-null int took 72kb as well.
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
September 25, 2008 at 10:57 am
I thought Gail was right here. Shouldn't use space for int/char if it's null, the null bitmap should carry that.
September 25, 2008 at 10:59 am
Matt Miller (9/25/2008)
On the large table - what's the fill factor of the clustered index? Speaking of which - have you tried rebuilding the clustered index? You may see that number drop drastically.
Also, have there been a lot of deletes recently? That would account for the large amount of free space.
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
September 25, 2008 at 11:06 am
Steve Jones - Editor (9/25/2008)
I thought Gail was right here. Shouldn't use space for int/char if it's null, the null bitmap should carry that.
That's how I thought the null bitmap worked too. I will consult with the relevant documentation (and test more extensively) when I get home.
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
September 25, 2008 at 11:06 am
Steve Jones - Editor (9/25/2008)
I thought Gail was right here. Shouldn't use space for int/char if it's null, the null bitmap should carry that.
Again - speaking form memory here, but INT and CHAR handles this a little differently. INT allocates the space no matter what. CHAR on the other hand falls under the ANSI_PADDING setting: if it's OFF, a nullable CHAR is essentially a VARCHAR, so you store the bit (for null) and the length marker (2Bytes/row?); if it's ON, the NULL gets padded out to the field length, so the bit marker is set AND spaces are stored up to the length of the field.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
Viewing 15 posts - 1 through 15 (of 17 total)
You must be logged in to reply to this topic. Login to reply