reindexing table with blob data type

Hi! Is there a way to defrag image data type column in a table? I use dbcc
dbreindex on the table itself during maintenance period, but I don't know if
that is sufficient to defrag those pages which actually stores image data
type. We use third party application and they designed to put some
transaction information on image data type column. Now we are experiencing
gradual degradation on performance and wondering if it is related to image
data type and its growth.
Sql server 2000 sp3a
thanks in advanceUnfortunately, the only way to defrag tables with BLOB columns is to rebuild
the table.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"james" <kush@.brandes.com> wrote in message
news:%23mZz8vJYFHA.3712@.TK2MSFTNGP09.phx.gbl...
Hi! Is there a way to defrag image data type column in a table? I use dbcc
dbreindex on the table itself during maintenance period, but I don't know if
that is sufficient to defrag those pages which actually stores image data
type. We use third party application and they designed to put some
transaction information on image data type column. Now we are experiencing
gradual degradation on performance and wondering if it is related to image
data type and its growth.
Sql server 2000 sp3a
thanks in advance|||Just to clarify, what Tom means is to bcp out/in the data, not to run DBCC
DBREINDEX on the table.
We have this fixed in SQL Server 2005.
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:#je8NBLYFHA.2348@.TK2MSFTNGP14.phx.gbl...
> Unfortunately, the only way to defrag tables with BLOB columns is to
rebuild
> the table.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinpub.com
> .
> "james" <kush@.brandes.com> wrote in message
> news:%23mZz8vJYFHA.3712@.TK2MSFTNGP09.phx.gbl...
> Hi! Is there a way to defrag image data type column in a table? I use dbcc
> dbreindex on the table itself during maintenance period, but I don't know
if
> that is sufficient to defrag those pages which actually stores image data
> type. We use third party application and they designed to put some
> transaction information on image data type column. Now we are experiencing
> gradual degradation on performance and wondering if it is related to image
> data type and its growth.
> Sql server 2000 sp3a
> thanks in advance
>|||Thanks for the reply. How about loading the data into new table? for
example, I create new table and load all data into it, something like
insert into new_table
select * from old_table
Will the new_table have all blob pages placed contigiously?
or will it make it worse, since essentially now there will be additional
blob pages required under the B tree to make room for the new_table and
old_table both?
and finally, Can we bcp out blob data and bcp in afterwards without
corrupting the binary data?
I appreicate your answer
"Paul S Randal [MS]" <prandal@.online.microsoft.com> wrote in message
news:%23CNC7bMYFHA.3132@.TK2MSFTNGP09.phx.gbl...
> Just to clarify, what Tom means is to bcp out/in the data, not to run DBCC
> DBREINDEX on the table.
> We have this fixed in SQL Server 2005.
> --
> Paul Randal
> Dev Lead, Microsoft SQL Server Storage Engine
> This posting is provided "AS IS" with no warranties, and confers no
rights.
> "Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
> news:#je8NBLYFHA.2348@.TK2MSFTNGP14.phx.gbl...
> rebuild
dbcc[vbcol=seagreen]
know[vbcol=seagreen]
> if
data[vbcol=seagreen]
experiencing[vbcol=seagreen]
image[vbcol=seagreen]
>|||Essentially, that has the same effect. You can load the table using that
method and then add the clustered (and nonclustered) indexes.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"james" <kush@.brandes.com> wrote in message
news:%23pDY1UWYFHA.2796@.TK2MSFTNGP09.phx.gbl...
Thanks for the reply. How about loading the data into new table? for
example, I create new table and load all data into it, something like
insert into new_table
select * from old_table
Will the new_table have all blob pages placed contigiously?
or will it make it worse, since essentially now there will be additional
blob pages required under the B tree to make room for the new_table and
old_table both?
and finally, Can we bcp out blob data and bcp in afterwards without
corrupting the binary data?
I appreicate your answer
"Paul S Randal [MS]" <prandal@.online.microsoft.com> wrote in message
news:%23CNC7bMYFHA.3132@.TK2MSFTNGP09.phx.gbl...
> Just to clarify, what Tom means is to bcp out/in the data, not to run DBCC
> DBREINDEX on the table.
> We have this fixed in SQL Server 2005.
> --
> Paul Randal
> Dev Lead, Microsoft SQL Server Storage Engine
> This posting is provided "AS IS" with no warranties, and confers no
rights.
> "Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
> news:#je8NBLYFHA.2348@.TK2MSFTNGP14.phx.gbl...
> rebuild
dbcc[vbcol=seagreen]
know[vbcol=seagreen]
> if
data[vbcol=seagreen]
experiencing[vbcol=seagreen]
image[vbcol=seagreen]
>|||Yes, that works too. It doesn't make it worse because the two sets of text
pages (for the old and new tables) are distinct so the old ones will be
reclaimed when you drop the old table.
Nothing you can do should corrupt any of your data :-)
Regards.
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"james" <kush@.brandes.com> wrote in message
news:#pDY1UWYFHA.2796@.TK2MSFTNGP09.phx.gbl...
> Thanks for the reply. How about loading the data into new table? for
> example, I create new table and load all data into it, something like
> insert into new_table
> select * from old_table
> Will the new_table have all blob pages placed contigiously?
> or will it make it worse, since essentially now there will be additional
> blob pages required under the B tree to make room for the new_table and
> old_table both?
> and finally, Can we bcp out blob data and bcp in afterwards without
> corrupting the binary data?
> I appreicate your answer
> "Paul S Randal [MS]" <prandal@.online.microsoft.com> wrote in message
> news:%23CNC7bMYFHA.3132@.TK2MSFTNGP09.phx.gbl...
DBCC[vbcol=seagreen]
> rights.
> dbcc
> know
> data
> experiencing
> image
>|||Just so you know, when you execute a DBCC SHRINKDATABASE and/or a DBCC
SHRINKFILE on the data files, the physical pages are relocated, just like
creating a new table, which it does. Then reindexing the clustered index
will resort the data pages themselves to be both logically and extent
defragmented.
However, if you are NOT using inline LOB segements, then there really is no
ordering to this data anyway. By having set the LOB inline, the reindex of
the cluster index will reorder to that sort.
Sincerely,
Anthony Thomas
"james" <kush@.brandes.com> wrote in message
news:%23pDY1UWYFHA.2796@.TK2MSFTNGP09.phx.gbl...
Thanks for the reply. How about loading the data into new table? for
example, I create new table and load all data into it, something like
insert into new_table
select * from old_table
Will the new_table have all blob pages placed contigiously?
or will it make it worse, since essentially now there will be additional
blob pages required under the B tree to make room for the new_table and
old_table both?
and finally, Can we bcp out blob data and bcp in afterwards without
corrupting the binary data?
I appreicate your answer
"Paul S Randal [MS]" <prandal@.online.microsoft.com> wrote in message
news:%23CNC7bMYFHA.3132@.TK2MSFTNGP09.phx.gbl...
> Just to clarify, what Tom means is to bcp out/in the data, not to run DBCC
> DBREINDEX on the table.
> We have this fixed in SQL Server 2005.
> --
> Paul Randal
> Dev Lead, Microsoft SQL Server Storage Engine
> This posting is provided "AS IS" with no warranties, and confers no
rights.
> "Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
> news:#je8NBLYFHA.2348@.TK2MSFTNGP14.phx.gbl...
> rebuild
dbcc[vbcol=seagreen]
know[vbcol=seagreen]
> if
data[vbcol=seagreen]
experiencing[vbcol=seagreen]
image[vbcol=seagreen]
>