SQL Server Performance Forum – Threads Archive

fragmentation pages used by text fields

Hi This is result from DBCC SHOWCONTIG on table ‘xxx’. Table: ‘xxx’ (416316843); index ID: 255, database ID: 13 LEAF level scan performed. - Pages Scanned…………………………..: 526120 - Extents Scanned…………………………: 140314 - Extent Switches…………………………: 140313 - Avg. Pages per Extent……………………: 3.7 - Scan Density [Best Count:Actual Count]…….: 46.87% [65765:140314] - Extent Scan Fragmentation ……………….: 38.32% - Avg. Bytes Free per Page…………………: 5257.2 - Avg. Page Density (full)…………………: 35.05% Table contains 15 fields but 5 of them are ‘text’. Those text fields are very fragmented because application use this table for life-cycle documents. When docs are die, there are moved to history table. Table ‘xxx’ has a lot of free space but i can’t get it back. <img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ /> I try everything: DBCC DBREINDEX DBCC CLEANTABLE DBCC CHECKTABLE even SHRINKDATABASE :/ How can I do this? Thanks in advice dgi Sorry for my poor english

As the main data is stored in text datatype, so nothing much we can exercise to get the optimum performance. As you know SQL Server stores a 16 byte pointer to the data pages that actually hold the text values. Therefore a select that doesn’t return the text value and runs very quickly. The TEXT datatype is not flexible to query, is slow and wastes a lot of space if used incorrectly. Sometimes a VARCHAR will handle your data better. You can also look at the "text in row" feature with the table options for SQL Server 2000. If possible try to modify the provision of storing the datafiles in the database and store the pointer of the file to get optimum performance. Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.

<blockquote id="quote">quote:<hr height="1" noshade id="quote">Originally posted by satya As the main data is stored in text datatype, so nothing much we can exercise to get the optimum performance. As you know SQL Server stores a 16 byte pointer to the data pages that actually hold the text values. Therefore a select that doesn’t return the text value and runs very quickly. The TEXT datatype is not flexible to query, is slow and wastes a lot of space if used incorrectly. Sometimes a VARCHAR will handle your data better. You can also look at the "text in row" feature with the table options for SQL Server 2000. If possible try to modify the provision of storing the datafiles in the database and store the pointer of the file to get optimum performance. <hr height="1" noshade id="quote"></blockquote id="quote"> Yes, this is all true but I want only defragment my table. <img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ /> exec sp_spaceused ‘xxx' shows that table have 2,4GB ‘reserved’ and 1,7GB ‘data’. So, I copied all data from table ‘xxx’ to ‘xxx2’ using SELECT INTO and exec sp_spaceused ‘xxx2′ shows 0,7GB ‘reserved’ and ‘data’. :/ Now, I think this is one and only one way to do defragmentation my ‘xxx’ table. Others methods fails. Thanks for reply. Best regards dgi

Run the SP_SPACEUSED statment like:

Code:

 SP_SPACEUSED 'xx' @UPDATEUSAGE='TRUE'
[B]Satya SKJ[/B]
Moderator
[URL]http://www.SQL-Server-Performance.Com/forum[/URL]
This posting is provided â€œAS ISâ€ with no rights for the sake of [I]knowledge sharing.[/I]

Originally posted by satya Run the SP_SPACEUSED statment like: SP_SPACEUSED ‘xx’ @UPDATEUSAGE=’TRUE’ I use

Code:

DBCC UPDATEUSAGE
but it doesn't change anything.
Best regards
dgi[/INDENT]

Then the space is true.
Luis Martin
Moderator
SQL-Server-Performance.com One of the symptoms of an approaching nervous breakdown is the belief that one’s work is terribly important
Bertrand Russell
All postings are provided â€œAS ISâ€ with no warranties for accuracy.

You could try creating a copy of the table structure and inserting all the data into it, then rename them so you have a new copy of it which shouln’t be fragmented. It’s a bit of a hassle if you have Foreign Keys though, all these plus indexes need to be recreated afterwards. But I don’t know of any [other] way to defrag text columns. Tom Pullen
DBA, Oxfam GB

<blockquote id="quote">quote:<hr height="1" noshade id="quote">Originally posted by thomas You could try creating a copy of the table structure and inserting all the data into it, then rename them so you have a new copy of it which shouln’t be fragmented. It’s a bit of a hassle if you have Foreign Keys though, all these plus indexes need to be recreated afterwards. But I don’t know of any [other] way to defrag text columns. <hr height="1" noshade id="quote"></blockquote id="quote"> That’s I wrote in my post earlier. I have one big problem with that. That operation takes 1 hour but I haven’t even 10 minutes. Users working 24/h. <img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ /> Thanks for replies. Best regards dgi

If you cannot afford the downtime then ensure to maintain much free space on this database and ensure the disk space is not an issue. This type of issues are general when using TEXT datatypes and you must ensure the free space is available all the times. The TEXT datatype is not flexible to query, is slow and wastes a lot of space if used incorrectly. Sometimes a VARCHAR will handle your data better. You can also look at the "text in row" feature with the table options for SQL Server 2000. But still I would stick to the first statement, Avoid using them on first place. Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.

If you have enough space for another (shrinked) copy of original table you can populate new table first, then drop original one, then rename new one using sp_rename.
Select *
into newTable
from oldTable
go
drop table oldTable
go
exec sp_rename ‘newTable’, ‘oldTable’
go
That way you will have a few ms downtime.

The only solution to reclaim that free space previously occupied by LOB data in SQL Server 2000 is to bcp the data out, truncate the table and bcp back in. SQL Server 2005 will fix this, however currently you don’t have another choice. —
Frank
http://www.insidesql.de

quote:Originally posted by FrankKalis The only solution to reclaim that free space previously occupied by LOB data in SQL Server 2000 is to bcp the data out, truncate the table and bcp back in. SQL Server 2005 will fix this, however currently you don’t have another choice.

Yes, this is true. I did it at last release. This operation takes me about 1 hour but result are very suprising. Table ‘xxx’ reduced from 2,4GB to 440MB[:0] Thanks for replies
dgi

]]>