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...
> > 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
> >
> >
>|||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...
> > 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
> >
> >
>|||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...
> > 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
> > >
> > >
> >
> >
>|||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...
> > 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
> >
> >
>

reindex and shrinking question

Hi,
I have a large table and would like to run dbcc dbreindex
on the table and also shrink the database. As I understand
it, both of these can be used to remove fragmentation. My
question is what is the correct order? Reindex first then
shrink or vice-versa?
TIA,
JBShrink then reindex. If you do it the other way, the shrink will scatter
your index into the available spaces and you work will have been for naught.
Russell Fields
"JB" <anonymous@.discussions.microsoft.com> wrote in message
news:5f5301c49281$20d2aa10$a601280a@.phx.gbl...
> Hi,
> I have a large table and would like to run dbcc dbreindex
> on the table and also shrink the database. As I understand
> it, both of these can be used to remove fragmentation. My
> question is what is the correct order? Reindex first then
> shrink or vice-versa?
> TIA,
> JB|||Thanks again Russell,
final question - do you need to do shrink, reindex
iteratively until nothing happens, as the reindex will
presumably free up some pages, or are the pages it frees
up already at the end of the file.
TIA,
JB|||Actually ideally you don't want to shrink at all. Shrinking will fragment
your tables and indexes and you need lots of free space in the database to
do operate properly for such things as Reindexing. If you shrink the DB and
remove most or all of the free space then reindex it will just have to grow
the database all over again. If the database grew to that size before it
will most likely need that much room again. The best thing to do is usually
leave it at that size. You can then reindex the tables and get the best
chance of optimized indexes and won't have to worry about the files growing
when you do so.
--
Andrew J. Kelly SQL MVP
"JB" <anonymous@.discussions.microsoft.com> wrote in message
news:607501c492a9$c0a343b0$a501280a@.phx.gbl...
> Thanks again Russell,
> final question - do you need to do shrink, reindex
> iteratively until nothing happens, as the reindex will
> presumably free up some pages, or are the pages it frees
> up already at the end of the file.
> TIA,
> JB
>|||Thanks Andrew,
so the number of page reads would be the same if you do
shrink followed by reindex compared to reindex on its own?
TIA,
JB|||Well the number of pages in the index will be the same given they both use
the same fill factor. But the order in which they get stored on disk may
not be the same.
--
Andrew J. Kelly SQL MVP
"JB" <anonymous@.discussions.microsoft.com> wrote in message
news:68e701c493e7$783bcd10$a501280a@.phx.gbl...
> Thanks Andrew,
> so the number of page reads would be the same if you do
> shrink followed by reindex compared to reindex on its own?
> TIA,
> JB

re-index

Take a look at dbcc dbreindex on Books Online.
Ben Nevarez, MCDBA, OCP
Database Administrator
"00KobeBrian" wrote:

> How do you re-index in SQL 2000? Thanks.
>
>Hi,
Check following two articles:
http://msdn2.microsoft.com/en-us/library/ms181671.aspx
http://msdn2.microsoft.com/en-us/library/ms188388.aspx
They apply to SQL 2000 as well.
Thanks.
Best regards,
Vincent Xu
Microsoft Online Partner Support
========================================
==============
Get Secure! - www.microsoft.com/security
========================================
==============
When responding to posts, please "Reply to Group" via your newsreader so
that others
may learn and benefit from this issue.
========================================
==============
This posting is provided "AS IS" with no warranties,and confers no rights.
========================================
==============
--[vbcol=seagreen]|||Hi Vincent,
Looks like alter index does not apply to SQL Server 2000.
Ben Nevarez, MCDBA, OCP
Database Administrator
"Vincent Xu [MSFT]" wrote:

> Hi,
> Check following two articles:
> http://msdn2.microsoft.com/en-us/library/ms181671.aspx
> http://msdn2.microsoft.com/en-us/library/ms188388.aspx
> They apply to SQL 2000 as well.
> Thanks.
>
> Best regards,
> Vincent Xu
> Microsoft Online Partner Support
> ========================================
==============
> Get Secure! - www.microsoft.com/security
> ========================================
==============
> When responding to posts, please "Reply to Group" via your newsreader so
> that others
> may learn and benefit from this issue.
> ========================================
==============
> This posting is provided "AS IS" with no warranties,and confers no rights.
> ========================================
==============
>
> --
>|||How do you re-index in SQL 2000? Thanks.|||Take a look at dbcc dbreindex on Books Online.
Ben Nevarez, MCDBA, OCP
Database Administrator
"00KobeBrian" wrote:

> How do you re-index in SQL 2000? Thanks.
>
>|||Hi,
Check following two articles:
http://msdn2.microsoft.com/en-us/library/ms181671.aspx
http://msdn2.microsoft.com/en-us/library/ms188388.aspx
They apply to SQL 2000 as well.
Thanks.
Best regards,
Vincent Xu
Microsoft Online Partner Support
========================================
==============
Get Secure! - www.microsoft.com/security
========================================
==============
When responding to posts, please "Reply to Group" via your newsreader so
that others
may learn and benefit from this issue.
========================================
==============
This posting is provided "AS IS" with no warranties,and confers no rights.
========================================
==============
--[vbcol=seagreen]|||Hi Vincent,
Looks like alter index does not apply to SQL Server 2000.
Ben Nevarez, MCDBA, OCP
Database Administrator
"Vincent Xu [MSFT]" wrote:

> Hi,
> Check following two articles:
> http://msdn2.microsoft.com/en-us/library/ms181671.aspx
> http://msdn2.microsoft.com/en-us/library/ms188388.aspx
> They apply to SQL 2000 as well.
> Thanks.
>
> Best regards,
> Vincent Xu
> Microsoft Online Partner Support
> ========================================
==============
> Get Secure! - www.microsoft.com/security
> ========================================
==============
> When responding to posts, please "Reply to Group" via your newsreader so
> that others
> may learn and benefit from this issue.
> ========================================
==============
> This posting is provided "AS IS" with no warranties,and confers no rights.
> ========================================
==============
>
> --
>|||Hi All,
Sorry, my mistake.I assume this as a matter of course
We have to use DBCC
Check following article:
<http://www.microsoft.com/technet/pr...in/ss2kidbp.msp
x>
Best regards,
Vincent Xu
Microsoft Online Partner Support
========================================
==============
Get Secure! - www.microsoft.com/security
========================================
==============
When responding to posts, please "Reply to Group" via your newsreader so
that others
may learn and benefit from this issue.
========================================
==============
This posting is provided "AS IS" with no warranties,and confers no rights.
========================================
==============
--[vbcol=seagreen]
<DD275389-46FA-4FA8-AD04-C9AB9FE75B21@.microsoft.com>
<rJ$Z6gAnGHA.2028@.TK2MSFTNGXA01.phx.gbl>[vbcol=seagreen]
so[vbcol=seagreen]
rights.[vbcol=seagreen]|||Hi All,
Sorry, my mistake.I assume this as a matter of course
We have to use DBCC
Check following article:
<http://www.microsoft.com/technet/pr...in/ss2kidbp.msp
x>
Best regards,
Vincent Xu
Microsoft Online Partner Support
========================================
==============
Get Secure! - www.microsoft.com/security
========================================
==============
When responding to posts, please "Reply to Group" via your newsreader so
that others
may learn and benefit from this issue.
========================================
==============
This posting is provided "AS IS" with no warranties,and confers no rights.
========================================
==============
--[vbcol=seagreen]
<DD275389-46FA-4FA8-AD04-C9AB9FE75B21@.microsoft.com>
<rJ$Z6gAnGHA.2028@.TK2MSFTNGXA01.phx.gbl>[vbcol=seagreen]
so[vbcol=seagreen]
rights.[vbcol=seagreen]

Reindex

The dbcc dbreindex job that takes about 40 minutes on a 32 bit SQL Server
takes about 1hr and 20 minutes in a 64 bit server. The same set of databases
exist on both servers and the file structure also is the same with the drives
for the database files for both being SAN LUNs. The activity on SQL Server is
also the same for both servers when the reindex job is run , there is no one
else on the system. The 32 bit server has only 2G available to SQL whereas
the 64 bit has 8G available to SQL.
How many processors on the 32 bit and the 64bit? What is your MAXDOP set
to? Just because the 64bit is connected to a SAN does not mean you will get
the same performance as another LUN.
Andrew J. Kelly SQL MVP
"MANCHO" <MANCHO@.discussions.microsoft.com> wrote in message
news:3EC0BE26-484E-4714-A25D-35A038E2D7A7@.microsoft.com...
> The dbcc dbreindex job that takes about 40 minutes on a 32 bit SQL Server
> takes about 1hr and 20 minutes in a 64 bit server. The same set of
> databases
> exist on both servers and the file structure also is the same with the
> drives
> for the database files for both being SAN LUNs. The activity on SQL Server
> is
> also the same for both servers when the reindex job is run , there is no
> one
> else on the system. The 32 bit server has only 2G available to SQL whereas
> the 64 bit has 8G available to SQL.

Reindex

The dbcc dbreindex job that takes about 40 minutes on a 32 bit SQL Server
takes about 1hr and 20 minutes in a 64 bit server. The same set of databases
exist on both servers and the file structure also is the same with the drive
s
for the database files for both being SAN LUNs. The activity on SQL Server i
s
also the same for both servers when the reindex job is run , there is no one
else on the system. The 32 bit server has only 2G available to SQL whereas
the 64 bit has 8G available to SQL.How many processors on the 32 bit and the 64bit? What is your MAXDOP set
to? Just because the 64bit is connected to a SAN does not mean you will get
the same performance as another LUN.
Andrew J. Kelly SQL MVP
"MANCHO" <MANCHO@.discussions.microsoft.com> wrote in message
news:3EC0BE26-484E-4714-A25D-35A038E2D7A7@.microsoft.com...
> The dbcc dbreindex job that takes about 40 minutes on a 32 bit SQL Server
> takes about 1hr and 20 minutes in a 64 bit server. The same set of
> databases
> exist on both servers and the file structure also is the same with the
> drives
> for the database files for both being SAN LUNs. The activity on SQL Server
> is
> also the same for both servers when the reindex job is run , there is no
> one
> else on the system. The 32 bit server has only 2G available to SQL whereas
> the 64 bit has 8G available to SQL.

Reindex

The dbcc dbreindex job that takes about 40 minutes on a 32 bit SQL Server
takes about 1hr and 20 minutes in a 64 bit server. The same set of databases
exist on both servers and the file structure also is the same with the drives
for the database files for both being SAN LUNs. The activity on SQL Server is
also the same for both servers when the reindex job is run , there is no one
else on the system. The 32 bit server has only 2G available to SQL whereas
the 64 bit has 8G available to SQL.How many processors on the 32 bit and the 64bit? What is your MAXDOP set
to? Just because the 64bit is connected to a SAN does not mean you will get
the same performance as another LUN.
--
Andrew J. Kelly SQL MVP
"MANCHO" <MANCHO@.discussions.microsoft.com> wrote in message
news:3EC0BE26-484E-4714-A25D-35A038E2D7A7@.microsoft.com...
> The dbcc dbreindex job that takes about 40 minutes on a 32 bit SQL Server
> takes about 1hr and 20 minutes in a 64 bit server. The same set of
> databases
> exist on both servers and the file structure also is the same with the
> drives
> for the database files for both being SAN LUNs. The activity on SQL Server
> is
> also the same for both servers when the reindex job is run , there is no
> one
> else on the system. The 32 bit server has only 2G available to SQL whereas
> the 64 bit has 8G available to SQL.