| Author |
Topic |
|
coolerbob
Aged Yak Warrior
841 Posts |
 Posted - 2005-04-28 : 05:27:21
|
| I have a table that is 16 gig big (so sql server 2000 says anyway).If I dts it out to another db, it's only 1 gig.I have tried all of the following to update sql's idea of how big this database table is but none of the following has worked:exec sp_updatestatsdbcc checkdb DBCC SHRINKDATABASE (Mydatabase, 1)dbcc dbreindex (mytable, '',99)dbcc indexdefragthis is a badly designed table with scores of columns (and FIVE TEXT columns!) but like I said, if I dts export the whole table the size drops by 15 giganyone know what else could be causing this? |
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2005-04-28 : 05:35:54
|
| I'm guessing that the text columns have a lot of wasted space allocated.I think the only way to reclaim this is to recreate the table.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
 |
|
|
coolerbob
Aged Yak Warrior
841 Posts |
Posted - 2005-04-28 : 05:52:10
|
| Is there any way that I could prove that?Because if that is the case, this may the the last nail in the coffin that I need to show why text columns are a bad idea for us. |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2005-04-28 : 07:52:06
|
You can run DBCC CLEANTABLE, that will reclaim unused text pages. You should do that before you do any calculations, also do a DBCC UPDATEUSAGE so that all the page counts are up to date. While the inflated values would make your case better  it would be easy for someone else to call "bullshit" on you just by running these commands.Run this query for each text column in the table:SELECT Max(Datalength(col)) MaxLength, Avg(DataLength(col)) AvgLength, Sum(Datalength(col)%8096) WastedBytes,Count(*) Rows,Sum(CASE WHEN col IS NULL THEN 0 ELSE 1 END) TextRowsFROM myTableIf the MaxLength or AvgLength are less than 8,000 bytes, then you definitely don't need text columns. An average less than 32,000 would indicate a significant waste of space. The WastedBytes calculation should give you a reasonably accurate measure of how much you're wasting. And if you see a low number of text rows compared to the total, then you may not even need that column in the table, since it's not being used much. Although text columns don't waste space if they're null, neither does varchar.Take a look in Books Online for "text in row", this may provide a compromise measure if you can't convince them to get rid of the text columns. It should at least alleviate the waste, but you should still push to use varchar as hard as you can. Also look at moving those columns completely out of the table. 5 text columns is not a normalized design, and if they're sparsely populated you can see a significant improvement by redesigning the table so that they're stored separately. |
|
|
|
JimL
SQL Slinging Yak Ranger
1537 Posts |
Posted - 2005-04-28 : 07:55:02
|
Nigel has probably hit the nail on the head. I had a test DB that grew way too fast. After finding out the max Char in the Text field it was only about 220. Then after changing to a Varchar(250) The DB grew at 1/4 the rate.No Text Field bad bad doggy  JimUsers <> Logic |
|
|
|
coolerbob
Aged Yak Warrior
841 Posts |
Posted - 2005-04-28 : 08:48:52
|
| Robvolk, DBCC CLEANTABLE & DBCC UPDATEUSAGE made no impact Thanks for the other ammo dude.Now, where is that gatling gun... |
|
|
|
coolerbob
Aged Yak Warrior
841 Posts |
Posted - 2005-04-28 : 09:38:34
|
| How can I check if "text in row" is ON at the moment? |
|
|
|
coolerbob
Aged Yak Warrior
841 Posts |
Posted - 2005-04-28 : 09:54:30
|
| sp_helpdb doesnt tell me |
|
|
|
rockmoose
SQL Natt Alfen
3279 Posts |
Posted - 2005-04-28 : 10:59:08
|
| >> How can I check if "text in row" is ON at the moment?SELECT OBJECTPROPERTY(OBJECT_ID('TableName'),'TableTextInRowLimit')rockmoose |
|
|
|
coolerbob
Aged Yak Warrior
841 Posts |
Posted - 2005-04-28 : 12:30:50
|
| thanks rockmooserobvolk,I've been running that analysisI'm working on the 5th column a the mo, but here is what i have at the moment MaxLen AvLen WastedBytes Rows TextRowscol1 268 17 7144941 474526 403036col2 696 41 17255654 474526 419578col3 307 0 347114 474526 375469col4 2215 1 681386 474526 370725As you can see, I'm almost halfway through my 11 columns and so far the wasted space is not even 50 meg.It doesnt look like this is going to prove that the 15 gig that this table has grown by all of a sudden in a few months is caused by the text columns.Could something else be responsible? |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2005-04-28 : 13:21:54
|
I made an error in the WastedBytes calculation, replace it with the following:Sum(8096-(Datalength(col)%8096)) WastedBytes,Run that, and you'll see a MUCH different picture.BTW, every maximum length is WAY under the varchar limit of 8000. Even if it's not wasting space, there is zero justification for using text columns here. The argument "well, we might need the space in the future" is unjustifiable too, since your existing data has never come close to needing it yet.quote:
As you can see, I'm almost halfway through my 11 columns and so far the wasted space is not even 50 meg.
I thought you said this table had 5 text columns? These calculations are meaningful only for text, ntext, and image datatypes. If you're running it on other datatypes it will be completing misleading. |
|
|
|
coolerbob
Aged Yak Warrior
841 Posts |
Posted - 2005-04-28 : 13:32:31
|
| thanks, i'll try thati was wrong, it didnt just hav 5 text columns. it has ELEVEN. can you believe it?? crazy |
|
|
|
coolerbob
Aged Yak Warrior
841 Posts |
Posted - 2005-04-28 : 13:58:49
|
| robvolk,making that change caused this error:Server: Msg 8115, Level 16, State 2, Line 1Arithmetic overflow error converting expression to data type int.Warning: Null value is eliminated by an aggregate or other SET operation.should I convert the sum to bigint somehow? will that solve it? |
|
|
|
coolerbob
Aged Yak Warrior
841 Posts |
Posted - 2005-04-28 : 14:17:22
|
| the sum is causing that right?I mean, there's count_big but there isn't sum_bigAnd I cant see how casting to bigint would help. something like this surely wouldnt work:Sum(cast(8096-(Datalength(col)%8096) bigint)) WastedBytesneither would something like this:CAST(Sum(8096-(Datalength(col)%8096)) bigint) WastedBytesits not logicalis that the end of the road? |
|
|
|
rockmoose
SQL Natt Alfen
3279 Posts |
Posted - 2005-04-28 : 14:26:54
|
| Why do You say that?surely this works:Sum(cast(8096-(Datalength(col)%8096) as bigint))ps. Sorry for barging in like that...rockmoose |
|
|
|
coolerbob
Aged Yak Warrior
841 Posts |
Posted - 2005-04-28 : 14:33:07
|
| genious is always welcometypical me, jump to conclusionsI'll try thatthanks dude |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2005-04-28 : 18:46:35
|
Well, the one thing that that error message proves: you're wasting more than 2 GB of disk space.  |
|
|
|
coolerbob
Aged Yak Warrior
841 Posts |
Posted - 2005-04-29 : 03:23:20
|
| Damn right!!3.1 gig on the first column alone!I think the other 10 columns will explain where the other 12 gig disappeared to!Robvolk, you da man!That sealed the case! |
|
|
|
KLang23
Posting Yak Master
115 Posts |
Posted - 2005-04-29 : 16:39:22
|
| Take a look a whether or not you are taking your indexes, especially clustered indexes, with you to the destination server. That could explain the significant change in size.SQL2K "shares" the pages allocated to text columns with other text columns in a table, so each (populated) text column in a row does not neccessarily use 8K for itself.Best of luck. |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2005-04-29 : 21:57:56
|
quote:
so each (populated) text column in a row does not neccessarily use 8K for itself.
<Johnny Carson> I did not know that. </Johnny Carson> That's pretty interesting. See? This is why everyone should read Books Online!  Try this query, it should provide a more accurate value for wasted space:SELECT a.bytes-b.bytes FROM(SELECT Cast(used as bigint)*8192 as bytes FROM sydindexes WHERE indid=255 AND id=OBJECT_ID('myTable')) a,(SELECT Sum(cast(DataLength(col1) as bigint)+DataLength(col2)+DataLength(col3)...etc.) FROM myTable) bquote:
Take a look a whether or not you are taking your indexes, especially clustered indexes, with you to the destination server. That could explain the significant change in size.
Actually, clustered indexes would be the space-consumers. Non-clustered indexes do not add any additional space except for their non-leaf pages, which would be a very small percentage considering the number of rows you have in your table. |
|
|
|
dursaliye
Starting Member
22 Posts |
Posted - 2005-04-30 : 01:58:48
|
| When our text, ntext, or image "data" (not including pointers, headers..) is smaller than 8080 byte, we should set "text in row" option to on.96 byte header + 8080 byte data + 16 byte text-pointer = 8192 byteIf we consider what Book Online says (1.The lowest limit is 24 bytes, which holds a root node with only one pointer 2. A full root structure placed in a data row requires 72 bytes to hold five pointers.), root node is 8 byte and (72 - 8 ) / 16 = 4 text-pointer. I think its example is wrong.What Books Online says about "text in row" option:If a text, ntext, or image string is longer than the text in row option limit or the available space in the row, the set of pointers otherwise stored in the root node of the pointer tree are stored in the row. Moving the root node to the row itself allows SQL Server to eliminate a page access each time it references the string value, which speeds processing.A full root structure placed in a data row requires 72 bytes to hold five pointers. If the text in row option limit is less than 72 bytes, or if there are fewer than 72 bytes available in the row, SQL Server puts as many pointers as it can in the row. The lowest limit is 24 bytes, which holds a root node with only one pointer.Reducing the number of pointers in the root structure truncates the top level of the tree structure used to store the text, ntext, or image string. For example, if the root structure has only three pointers, the top level of the tree structure can only contain three nodes, not five. Reducing the size of the root structure can introduce extra layers in the tree structure. Setting the text in row option limit under 72 can also cause the top level to be truncated.When text, ntext, or image strings are stored in the row, they are stored similarly to variable-length strings. For example, if the text in row option limit is 500 bytes and you store a 200-byte string in a row, SQL Server uses only the number of bytes needed to store the string. If a string longer than 500 bytes is inserted, so that pointers are stored in the row, SQL Server uses only enough space to hold the pointers and not the entire 500 bytes.If a table has multiple text, ntext, or image columns, and you attempt to insert multiple text, ntext, or image strings, SQL Server assigns space to the strings one at a time in sequence based on column ID. For example, assume you have a table containing four text columns and you have set the text in row option limit to 1000. You then insert a row where with a 900-byte string for each text column, and enough data for all of the other columns in the table so there is only 3,000 bytes of free space in the row to hold the text strings. The strings for the first three text columns are stored in the row, using 2,700 bytes of the 3,000 bytes available. The string for the fourth text column is not stored in the row, but the pointers from the root node are stored in the row. |
|
|
|
Next Page
|
Why big table?