Sql Server Alignment problem: reorganize

Showing posts with label reorganize. Show all posts

Thursday, March 8, 2012

alter index syntax

I'm using sql server 2005 sp1 on win2003 server
I want to reorganize all indices in my database.
Online-docu says that dbcc indexdefrag should not be used anymore.
Instead, 'alter index' should be used
My syntax for one table is like this:
alter index all on bew reorganize
sql server says:
Meldung 156, Ebene 15, Status 1, Zeile 1
Incorrect syntax near the keyword 'index'.
What's wrong ?
Furthermore I would like to know, what the sql-command looks like for
reorganizing indices for
ALL the tables in the database?My guess database isn't in 90 compatibility mode. See sp_dbcmptlevel. Also, see Books Online for
sample code on how to reorg all indexes for a database:
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/tsqlref9/html/d294dd8e-82d5-4628-aa2d-e57702230613.htm
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"keltchen" <m.kaltenboeck@.powersoftware.at> wrote in message
news:1154958955.385435.160610@.n13g2000cwa.googlegroups.com...
> I'm using sql server 2005 sp1 on win2003 server
> I want to reorganize all indices in my database.
> Online-docu says that dbcc indexdefrag should not be used anymore.
> Instead, 'alter index' should be used
> My syntax for one table is like this:
> alter index all on bew reorganize
> sql server says:
> Meldung 156, Ebene 15, Status 1, Zeile 1
> Incorrect syntax near the keyword 'index'.
> What's wrong ?
> Furthermore I would like to know, what the sql-command looks like for
> reorganizing indices for
> ALL the tables in the database?
>|||Hi Tibor,
that might be the reason.
So I tried to use this command
sp_dbcmptlevel mydb, 90
I get this error:
Meldung 15416, Ebene 16, Status 1, Prozedur sp_dbcmptlevel, Zeile 92
Usage: sp_dbcmptlevel [dbname [, compatibilitylevel]]
Inside the procedure I can see that only the levels 60, 65, 70, 80 are
allowed.
Also using object explorer (db options) I only can see 70 and 80.
What's the matter?
I'm testing with a new installed 2005 instance,
The database 'comes' from SQL 7.0 and I did a restore of the .bak file
on the 2005 server
Thank you for more help|||This sounds like you have connected to a SQL 2000 instance.
Try this:
SELECT serverproperty('ProductVersion')
--
HTH
Kalen Delaney, SQL Server MVP
"keltchen" <m.kaltenboeck@.powersoftware.at> wrote in message
news:1155022695.236345.244990@.b28g2000cwb.googlegroups.com...
> Hi Tibor,
> that might be the reason.
> So I tried to use this command
> sp_dbcmptlevel mydb, 90
> I get this error:
> Meldung 15416, Ebene 16, Status 1, Prozedur sp_dbcmptlevel, Zeile 92
> Usage: sp_dbcmptlevel [dbname [, compatibilitylevel]]
> Inside the procedure I can see that only the levels 60, 65, 70, 80 are
> allowed.
> Also using object explorer (db options) I only can see 70 and 80.
> What's the matter?
> I'm testing with a new installed 2005 instance,
> The database 'comes' from SQL 7.0 and I did a restore of the .bak file
> on the 2005 server
> Thank you for more help
>

alter index syntax

I'm using sql server 2005 sp1 on win2003 server
I want to reorganize all indices in my database.
Online-docu says that dbcc indexdefrag should not be used anymore.
Instead, 'alter index' should be used
My syntax for one table is like this:
alter index all on bew reorganize
sql server says:
Meldung 156, Ebene 15, Status 1, Zeile 1
Incorrect syntax near the keyword 'index'.
What's wrong ?
Furthermore I would like to know, what the sql-command looks like for
reorganizing indices for
ALL the tables in the database?My guess database isn't in 90 compatibility mode. See sp_dbcmptlevel. Also,
see Books Online for
sample code on how to reorg all indexes for a database:
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/tsqlref9/html/d294dd8e-82d5-4628-aa2d-
e57702230613.htm
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"keltchen" <m.kaltenboeck@.powersoftware.at> wrote in message
news:1154958955.385435.160610@.n13g2000cwa.googlegroups.com...
> I'm using sql server 2005 sp1 on win2003 server
> I want to reorganize all indices in my database.
> Online-docu says that dbcc indexdefrag should not be used anymore.
> Instead, 'alter index' should be used
> My syntax for one table is like this:
> alter index all on bew reorganize
> sql server says:
> Meldung 156, Ebene 15, Status 1, Zeile 1
> Incorrect syntax near the keyword 'index'.
> What's wrong ?
> Furthermore I would like to know, what the sql-command looks like for
> reorganizing indices for
> ALL the tables in the database?
>|||Hi Tibor,
that might be the reason.
So I tried to use this command
sp_dbcmptlevel mydb, 90
I get this error:
Meldung 15416, Ebene 16, Status 1, Prozedur sp_dbcmptlevel, Zeile 92
Usage: sp_dbcmptlevel [dbname [, compatibilitylevel]]
Inside the procedure I can see that only the levels 60, 65, 70, 80 are
allowed.
Also using object explorer (db options) I only can see 70 and 80.
What's the matter?
I'm testing with a new installed 2005 instance,
The database 'comes' from SQL 7.0 and I did a restore of the .bak file
on the 2005 server
Thank you for more help|||This sounds like you have connected to a SQL 2000 instance.
Try this:
SELECT serverproperty('ProductVersion')
HTH
Kalen Delaney, SQL Server MVP
"keltchen" <m.kaltenboeck@.powersoftware.at> wrote in message
news:1155022695.236345.244990@.b28g2000cwb.googlegroups.com...
> Hi Tibor,
> that might be the reason.
> So I tried to use this command
> sp_dbcmptlevel mydb, 90
> I get this error:
> Meldung 15416, Ebene 16, Status 1, Prozedur sp_dbcmptlevel, Zeile 92
> Usage: sp_dbcmptlevel [dbname [, compatibilitylevel]]
> Inside the procedure I can see that only the levels 60, 65, 70, 80 are
> allowed.
> Also using object explorer (db options) I only can see 70 and 80.
> What's the matter?
> I'm testing with a new installed 2005 instance,
> The database 'comes' from SQL 7.0 and I did a restore of the .bak file
> on the 2005 server
> Thank you for more help
>

ALTER INDEX REORGANIZE vs DBCC INDEXDEFRAG

Is ALTER INDEX REORGANIZE equivalent to DBCC INDEXDEFRAG? In
http://msdn2.microsoft.com/en-us/library/ms189858.aspx it says "
Reorganizing a specified clustered index will compact all LOB columns that
are contained in the leaf level (data rows) of the clustered index" .
According to Kalen Delaney's Inside SQL Server 2005 The Storage Engine p 324
" In SQL Server 2000, the only way you can compact LOBs in a table is to
unload and reload the LOB data"
This would make ALTER INDEX REORGANIZE not equivalent but superior to DBCC
INDEXDEFRAG since it can compact LOB columns whereas DBCC INDEXDEFRAG cannot.
Is this correct?Mladen Andrijasevic wrote:
> Is ALTER INDEX REORGANIZE equivalent to DBCC INDEXDEFRAG? In
> http://msdn2.microsoft.com/en-us/library/ms189858.aspx it says "
> Reorganizing a specified clustered index will compact all LOB columns that
> are contained in the leaf level (data rows) of the clustered index" .
> According to Kalen Delaney's Inside SQL Server 2005 The Storage Engine p 324
> " In SQL Server 2000, the only way you can compact LOBs in a table is to
> unload and reload the LOB data"
> This would make ALTER INDEX REORGANIZE not equivalent but superior to DBCC
> INDEXDEFRAG since it can compact LOB columns whereas DBCC INDEXDEFRAG cannot.
> Is this correct?
from BOL:
ALTER INDEX
...
...
WITH ( LOB_COMPACTION = { ON | OFF } )
Specifies that all pages that contain large object (LOB) data are
compacted. The LOB data types are image, text, ntext, varchar(max),
nvarchar(max), varbinary(max), and xml. Compacting this data can improve
disk space use. The default is ON.|||Hi, according BOL (Jul 2007), ALTER INDEX ... REORGANIZE is Equivalent to
DBCC INDEXDEFRAG. is the same.
Regards,
"Mladen Andrijasevic" wrote:
> Is ALTER INDEX REORGANIZE equivalent to DBCC INDEXDEFRAG? In
> http://msdn2.microsoft.com/en-us/library/ms189858.aspx it says "
> Reorganizing a specified clustered index will compact all LOB columns that
> are contained in the leaf level (data rows) of the clustered index" .
> According to Kalen Delaney's Inside SQL Server 2005 The Storage Engine p 324
> " In SQL Server 2000, the only way you can compact LOBs in a table is to
> unload and reload the LOB data"
> This would make ALTER INDEX REORGANIZE not equivalent but superior to DBCC
> INDEXDEFRAG since it can compact LOB columns whereas DBCC INDEXDEFRAG cannot.
> Is this correct?|||I believe the following white has the info you need. It covers SQL2000, but
most materials should still be relevant.
http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ss2kidbp.mspx
Linchi
"Mladen Andrijasevic" wrote:
> Is ALTER INDEX REORGANIZE equivalent to DBCC INDEXDEFRAG? In
> http://msdn2.microsoft.com/en-us/library/ms189858.aspx it says "
> Reorganizing a specified clustered index will compact all LOB columns that
> are contained in the leaf level (data rows) of the clustered index" .
> According to Kalen Delaney's Inside SQL Server 2005 The Storage Engine p 324
> " In SQL Server 2000, the only way you can compact LOBs in a table is to
> unload and reload the LOB data"
> This would make ALTER INDEX REORGANIZE not equivalent but superior to DBCC
> INDEXDEFRAG since it can compact LOB columns whereas DBCC INDEXDEFRAG cannot.
> Is this correct?|||Zarko,
I am aware that LOB_COMPACTION default is ON . It was the point of my
question. From this it would fiollow that saying that "ALTER INDEX ...
REORGANIZE is Equivalent to DBCC INDEXDEFRAG " is wrong . But I have not come
across a statement saying so , or saying that ALTER INDEX ... REORGANIZE
should be immediately implemented on sql 2005 instead of DBCC INDEXDEFRAG
because it does more than DBCC INDEXDEFRAG , i.e. it can compact LOBs
Thanks
Mladen
"Zarko Jovanovic" wrote:
> Mladen Andrijasevic wrote:
> > Is ALTER INDEX REORGANIZE equivalent to DBCC INDEXDEFRAG? In
> > http://msdn2.microsoft.com/en-us/library/ms189858.aspx it says "
> > Reorganizing a specified clustered index will compact all LOB columns that
> > are contained in the leaf level (data rows) of the clustered index" .
> >
> > According to Kalen Delaney's Inside SQL Server 2005 The Storage Engine p 324
> > " In SQL Server 2000, the only way you can compact LOBs in a table is to
> > unload and reload the LOB data"
> >
> > This would make ALTER INDEX REORGANIZE not equivalent but superior to DBCC
> > INDEXDEFRAG since it can compact LOB columns whereas DBCC INDEXDEFRAG cannot.
> > Is this correct?
> from BOL:
> ALTER INDEX
> ...
> ...
> WITH ( LOB_COMPACTION = { ON | OFF } )
> Specifies that all pages that contain large object (LOB) data are
> compacted. The LOB data types are image, text, ntext, varchar(max),
> nvarchar(max), varbinary(max), and xml. Compacting this data can improve
> disk space use. The default is ON.
>|||Carlos ,
Well, the purpose of my question was precisely to clarify how to reconcile
what is written in BOL in one place , i.e. that ALTER INDEX ...
REORGANIZE is Equivalent to DBCC INDEXDEFRAG, with what is written in
another place in SQL Server 2005 Books Online (September 2007) i.e. that
ALTER INDEX ... REORGANIZE can compact LOBs . They cannot be equivalent if
ALTER INDEX ... REORGANIZE does more than DBCC INDEXDEFRAG!
Thanks
Mladen
"Carlos A." wrote:
> Hi, according BOL (Jul 2007), ALTER INDEX ... REORGANIZE is Equivalent to
> DBCC INDEXDEFRAG. is the same.
> Regards,
>
> "Mladen Andrijasevic" wrote:
> > Is ALTER INDEX REORGANIZE equivalent to DBCC INDEXDEFRAG? In
> > http://msdn2.microsoft.com/en-us/library/ms189858.aspx it says "
> > Reorganizing a specified clustered index will compact all LOB columns that
> > are contained in the leaf level (data rows) of the clustered index" .
> >
> > According to Kalen Delaney's Inside SQL Server 2005 The Storage Engine p 324
> > " In SQL Server 2000, the only way you can compact LOBs in a table is to
> > unload and reload the LOB data"
> >
> > This would make ALTER INDEX REORGANIZE not equivalent but superior to DBCC
> > INDEXDEFRAG since it can compact LOB columns whereas DBCC INDEXDEFRAG cannot.
> > Is this correct?|||Linchi,
I 've read Microsoft SQL Server 2000 Index Defragmentation Best Practices
and implemented its recommendations a few years ago . However, it cannot
provide an answer to my question since my question has todo with the
comparsion of DBCC INDEXDEFRAG of sql 2000 . to ALTER INDEX ... REORGANIZE of
sql 2005, which is not mentioned in the Server 2000 Index Defragmentation
Best Practices document since it appeared only in SQL 2005
Thanks
Mladen
"Linchi Shea" wrote:
> I believe the following white has the info you need. It covers SQL2000, but
> most materials should still be relevant.
> http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ss2kidbp.mspx
> Linchi
> "Mladen Andrijasevic" wrote:
> > Is ALTER INDEX REORGANIZE equivalent to DBCC INDEXDEFRAG? In
> > http://msdn2.microsoft.com/en-us/library/ms189858.aspx it says "
> > Reorganizing a specified clustered index will compact all LOB columns that
> > are contained in the leaf level (data rows) of the clustered index" .
> >
> > According to Kalen Delaney's Inside SQL Server 2005 The Storage Engine p 324
> > " In SQL Server 2000, the only way you can compact LOBs in a table is to
> > unload and reload the LOB data"
> >
> > This would make ALTER INDEX REORGANIZE not equivalent but superior to DBCC
> > INDEXDEFRAG since it can compact LOB columns whereas DBCC INDEXDEFRAG cannot.
> > Is this correct?|||> Well, the purpose of my question was precisely to clarify how to
> reconcile
> what is written in BOL in one place , i.e. that ALTER INDEX ...
> REORGANIZE is Equivalent to DBCC INDEXDEFRAG, with what is written in
> another place in SQL Server 2005 Books Online (September 2007) i.e. that
> ALTER INDEX ... REORGANIZE can compact LOBs . They cannot be equivalent
> if
> ALTER INDEX ... REORGANIZE does more than DBCC INDEXDEFRAG!
I think using the default behavior they are equivalent. Just because one
has some different *optional* commands does not make them completely
different animals.
Would you say that CAST and CONVERT are "equivalent"? How about ISNULL()
and COALESCE()?
In any case, I do agree that perhaps the wording could be a little less
ambiguous. Maybe you should click on the feedback item on that page in
Books Online, and voice your concerns? That feedback will make its way
directly to the writer of the topic.
A|||Aaron ,
I am not such a stickler over the wording at all. I just need to find out
whether it makes sense to implement ALTER INDEX . REORGANIZE over DBCC
INDEXDEFRAG on our SQL 2005 systems. If they were equivalent I would not
bother to do it right away . So would you say that ALTER INDEX . REORGANIZE
would better defrgarment than DBCC INDEXDEFRAG since ALTER INDEX .
REORGANIZE would compact LOBs and DBCC INDEXDEFRAG would not?
Thanks
Mladen
"Aaron Bertrand [SQL Server MVP]" wrote:
> > Well, the purpose of my question was precisely to clarify how to
> > reconcile
> > what is written in BOL in one place , i.e. that ALTER INDEX ...
> > REORGANIZE is Equivalent to DBCC INDEXDEFRAG, with what is written in
> > another place in SQL Server 2005 Books Online (September 2007) i.e. that
> > ALTER INDEX ... REORGANIZE can compact LOBs . They cannot be equivalent
> > if
> > ALTER INDEX ... REORGANIZE does more than DBCC INDEXDEFRAG!
> I think using the default behavior they are equivalent. Just because one
> has some different *optional* commands does not make them completely
> different animals.
> Would you say that CAST and CONVERT are "equivalent"? How about ISNULL()
> and COALESCE()?
> In any case, I do agree that perhaps the wording could be a little less
> ambiguous. Maybe you should click on the feedback item on that page in
> Books Online, and voice your concerns? That feedback will make its way
> directly to the writer of the topic.
> A
>
>|||Aaron,
Just noticed the " I think using the default behavior they are equivalent"
I do not think this is true either since LOB_COMPACTION = ON is the default
and that is precisely where they differ!
Mladen
"Aaron Bertrand [SQL Server MVP]" wrote:
> > Well, the purpose of my question was precisely to clarify how to
> > reconcile
> > what is written in BOL in one place , i.e. that ALTER INDEX ...
> > REORGANIZE is Equivalent to DBCC INDEXDEFRAG, with what is written in
> > another place in SQL Server 2005 Books Online (September 2007) i.e. that
> > ALTER INDEX ... REORGANIZE can compact LOBs . They cannot be equivalent
> > if
> > ALTER INDEX ... REORGANIZE does more than DBCC INDEXDEFRAG!
> I think using the default behavior they are equivalent. Just because one
> has some different *optional* commands does not make them completely
> different animals.
> Would you say that CAST and CONVERT are "equivalent"? How about ISNULL()
> and COALESCE()?
> In any case, I do agree that perhaps the wording could be a little less
> ambiguous. Maybe you should click on the feedback item on that page in
> Books Online, and voice your concerns? That feedback will make its way
> directly to the writer of the topic.
> A
>
>|||Sorry, my (clearly wrong) recollection was that LOB_COMPACTION defaulted to
OFF.
But still, I think it is weird for you to be asking us whether you should
use ALTER INDEX instead of DBCC. Isn't that really your call? Do you want
your LOBs compacted, or not? If not, then you can use ALTER INDEX with that
setting to OFF, no? Do you want your code to be forward compatible? I
envision that someday they will deprecate the DBCC command completely.
In any case, not really our decision.
"Mladen Andrijasevic" <MladenAndrijasevic@.discussions.microsoft.com> wrote
in message news:BA1DD97B-3DB5-43A2-AAE0-07C8AC1674D2@.microsoft.com...
> Aaron,
> Just noticed the " I think using the default behavior they are equivalent"
> I do not think this is true either since LOB_COMPACTION = ON is the
> default
> and that is precisely where they differ!
> Mladen
> "Aaron Bertrand [SQL Server MVP]" wrote:
>> > Well, the purpose of my question was precisely to clarify how to
>> > reconcile
>> > what is written in BOL in one place , i.e. that ALTER INDEX ...
>> > REORGANIZE is Equivalent to DBCC INDEXDEFRAG, with what is written in
>> > another place in SQL Server 2005 Books Online (September 2007) i.e.
>> > that
>> > ALTER INDEX ... REORGANIZE can compact LOBs . They cannot be
>> > equivalent
>> > if
>> > ALTER INDEX ... REORGANIZE does more than DBCC INDEXDEFRAG!
>> I think using the default behavior they are equivalent. Just because one
>> has some different *optional* commands does not make them completely
>> different animals.
>> Would you say that CAST and CONVERT are "equivalent"? How about ISNULL()
>> and COALESCE()?
>> In any case, I do agree that perhaps the wording could be a little less
>> ambiguous. Maybe you should click on the feedback item on that page in
>> Books Online, and voice your concerns? That feedback will make its way
>> directly to the writer of the topic.
>> A
>>|||Hi Mladen
Internally DBCC INDEXDEFRAG and ALTER INDEX REORGANIZE use the same
algorithm, so the only 'improvements' in the REORGANIZE option are that
there are additional features you can control, such as the LOB compaction.
As Aaron states, whether or not you see the new syntax as an improvement
depends on whether you want to control LOB compaction or not. Going
forward, it's a good idea to use the ALTER INDEX because the DBCC option
will be eventually going away, but there is no word yet on when that will
be.
--
HTH
Kalen Delaney, SQL Server MVP
www.InsideSQLServer.com
http://blog.kalendelaney.com
"Mladen Andrijasevic" <MladenAndrijasevic@.discussions.microsoft.com> wrote
in message news:91BF82DD-6C5A-4702-972A-EFD6A841A8FF@.microsoft.com...
> Is ALTER INDEX REORGANIZE equivalent to DBCC INDEXDEFRAG? In
> http://msdn2.microsoft.com/en-us/library/ms189858.aspx it says "
> Reorganizing a specified clustered index will compact all LOB columns that
> are contained in the leaf level (data rows) of the clustered index" .
> According to Kalen Delaney's Inside SQL Server 2005 The Storage Engine p
> 324
> " In SQL Server 2000, the only way you can compact LOBs in a table is to
> unload and reload the LOB data"
> This would make ALTER INDEX REORGANIZE not equivalent but superior to
> DBCC
> INDEXDEFRAG since it can compact LOB columns whereas DBCC INDEXDEFRAG
> cannot.
> Is this correct?|||Apparently there is a misunderstanding here. First the documentation
stated that the two (ALTER INDEX REORGANIZE and DBCC INDEXDEFRAG) were
equivalent, which they obviously are not. Next, you mistook the default
value for LOB_COMPACTION. This gives me the impression that not many have
been using ALTER INDEX REORGANIZE yet, else things would have been clarified
by now.
I do not expect you to make a decision for me, but to possibly point to a
study, white paper, where the performance benefits of LOB_COMPACTION
through ALTER INDEX REORGANIZE are quantified. Something on the line of a
sequel to Server 2000 Index Defragmentation Best Practices document . Is
there a Server 2005 Index Defragmentation Best Practices document planned?
I have not done LOB compaction through unload and reload of LOB data in SQL
2000 that Kalen Delaney mentioned in her book. Are the benefits of compaction
comparable to compacting of non LOB data? Any idiosyncrasies? If there are
documents discussing this topic in somewhat more detail I would definitely
switch to ALTER INDEX, once convinced of its benefits, even if it is not
backward compatible.
tks
Mladen
"Aaron Bertrand [SQL Server MVP]" wrote:
> Sorry, my (clearly wrong) recollection was that LOB_COMPACTION defaulted to
> OFF.
> But still, I think it is weird for you to be asking us whether you should
> use ALTER INDEX instead of DBCC. Isn't that really your call? Do you want
> your LOBs compacted, or not? If not, then you can use ALTER INDEX with that
> setting to OFF, no? Do you want your code to be forward compatible? I
> envision that someday they will deprecate the DBCC command completely.
> In any case, not really our decision.
>
> "Mladen Andrijasevic" <MladenAndrijasevic@.discussions.microsoft.com> wrote
> in message news:BA1DD97B-3DB5-43A2-AAE0-07C8AC1674D2@.microsoft.com...
> >
> > Aaron,
> >
> > Just noticed the " I think using the default behavior they are equivalent"
> >
> > I do not think this is true either since LOB_COMPACTION = ON is the
> > default
> > and that is precisely where they differ!
> >
> > Mladen
> >
> > "Aaron Bertrand [SQL Server MVP]" wrote:
> >
> >> > Well, the purpose of my question was precisely to clarify how to
> >> > reconcile
> >> > what is written in BOL in one place , i.e. that ALTER INDEX ...
> >> > REORGANIZE is Equivalent to DBCC INDEXDEFRAG, with what is written in
> >> > another place in SQL Server 2005 Books Online (September 2007) i.e.
> >> > that
> >> > ALTER INDEX ... REORGANIZE can compact LOBs . They cannot be
> >> > equivalent
> >> > if
> >> > ALTER INDEX ... REORGANIZE does more than DBCC INDEXDEFRAG!
> >>
> >> I think using the default behavior they are equivalent. Just because one
> >> has some different *optional* commands does not make them completely
> >> different animals.
> >>
> >> Would you say that CAST and CONVERT are "equivalent"? How about ISNULL()
> >> and COALESCE()?
> >>
> >> In any case, I do agree that perhaps the wording could be a little less
> >> ambiguous. Maybe you should click on the feedback item on that page in
> >> Books Online, and voice your concerns? That feedback will make its way
> >> directly to the writer of the topic.
> >>
> >> A
> >>
> >>
> >>
>
>|||> Apparently there is a misunderstanding here. First the documentation
> stated that the two (ALTER INDEX REORGANIZE and DBCC INDEXDEFRAG) were
> equivalent, which they obviously are not. Next, you mistook the default
> value for LOB_COMPACTION. This gives me the impression that not many have
> been using ALTER INDEX REORGANIZE yet, else things would have been
> clarified
> by now.
Or maybe we are using it in places where we don't have LOBs?
> I do not expect you to make a decision for me, but to possibly point to a
> study, white paper, where the performance benefits of LOB_COMPACTION
> through ALTER INDEX REORGANIZE are quantified.
<shrug>
I don't know of any. I could Google, but of course, so could you.
A|||Thanks Kalen,
I was responding to Aaronâ's post and did not notice your answer.
Is there a white paper, where the performance benefits of LOB_COMPACTION
through ALTER INDEX REORGANIZE are quantified? Something on the line of a
sequel to Server 2000 Index Defragmentation Best Practices document . Is
there a Server 2005 Index Defragmentation Best Practices document planned?
I have not used LOB compaction through unload and reload of LOB data that
you mentioned in your book. I do not think we could have done it (even if I
had known about it) for the same reason that we used DBCC INDEXDEFRAG in the
first place â' we need the system to be up 24/7 and the defragmenting
operation must be an online one. But I would definitely switch to ALTER
INDEX REORGANIZE if I could find some more documentation on LOB_COMPACTION .
Thank you for posting the new_helpindex command. Will definitely use it.
Mladen
"Kalen Delaney" wrote:
> Hi Mladen
> Internally DBCC INDEXDEFRAG and ALTER INDEX REORGANIZE use the same
> algorithm, so the only 'improvements' in the REORGANIZE option are that
> there are additional features you can control, such as the LOB compaction.
> As Aaron states, whether or not you see the new syntax as an improvement
> depends on whether you want to control LOB compaction or not. Going
> forward, it's a good idea to use the ALTER INDEX because the DBCC option
> will be eventually going away, but there is no word yet on when that will
> be.
> --
> HTH
> Kalen Delaney, SQL Server MVP
> www.InsideSQLServer.com
> http://blog.kalendelaney.com
>
> "Mladen Andrijasevic" <MladenAndrijasevic@.discussions.microsoft.com> wrote
> in message news:91BF82DD-6C5A-4702-972A-EFD6A841A8FF@.microsoft.com...
> > Is ALTER INDEX REORGANIZE equivalent to DBCC INDEXDEFRAG? In
> > http://msdn2.microsoft.com/en-us/library/ms189858.aspx it says "
> > Reorganizing a specified clustered index will compact all LOB columns that
> > are contained in the leaf level (data rows) of the clustered index" .
> >
> > According to Kalen Delaney's Inside SQL Server 2005 The Storage Engine p
> > 324
> > " In SQL Server 2000, the only way you can compact LOBs in a table is to
> > unload and reload the LOB data"
> >
> > This would make ALTER INDEX REORGANIZE not equivalent but superior to
> > DBCC
> > INDEXDEFRAG since it can compact LOB columns whereas DBCC INDEXDEFRAG
> > cannot.
> > Is this correct?
>
>|||>>Or maybe we are using it in places where we don't have LOBs?
Indeed. Sorry. Should have been more precise: â'This gives me the
impression that not many have been using ALTER INDEX REORGANIZE , in the
context of knowingly, deliberately compacting LOBs yet, else things would
have been clarified by nowâ'. I assumed the LOBs context from my initial
post.
Thanks for the answers.
Mladen
"Aaron Bertrand [SQL Server MVP]" wrote:
> > Apparently there is a misunderstanding here. First the documentation
> > stated that the two (ALTER INDEX REORGANIZE and DBCC INDEXDEFRAG) were
> > equivalent, which they obviously are not. Next, you mistook the default
> > value for LOB_COMPACTION. This gives me the impression that not many have
> > been using ALTER INDEX REORGANIZE yet, else things would have been
> > clarified
> > by now.
> Or maybe we are using it in places where we don't have LOBs?
> > I do not expect you to make a decision for me, but to possibly point to a
> > study, white paper, where the performance benefits of LOB_COMPACTION
> > through ALTER INDEX REORGANIZE are quantified.
> <shrug>
> I don't know of any. I could Google, but of course, so could you.
> A
>|||I'm not aware of any paper, but as Aaron suggests, you can use google as
well as any of us. My guess is that the impact would completely depend on
your application, and what you were doing with the lob data. It should be
very straightforward for you to run your own tests with and without lob
compaction, and check the performance difference.
--
HTH
Kalen Delaney, SQL Server MVP
www.InsideSQLServer.com
http://blog.kalendelaney.com
"Mladen Andrijasevic" <MladenAndrijasevic@.discussions.microsoft.com> wrote
in message news:1017B786-D603-4252-A0FC-63E4AAC94E9A@.microsoft.com...
> Thanks Kalen,
> I was responding to Aaron's post and did not notice your answer.
> Is there a white paper, where the performance benefits of LOB_COMPACTION
> through ALTER INDEX REORGANIZE are quantified? Something on the line of a
> sequel to Server 2000 Index Defragmentation Best Practices document . Is
> there a Server 2005 Index Defragmentation Best Practices document planned?
> I have not used LOB compaction through unload and reload of LOB data that
> you mentioned in your book. I do not think we could have done it (even if
> I
> had known about it) for the same reason that we used DBCC INDEXDEFRAG in
> the
> first place - we need the system to be up 24/7 and the defragmenting
> operation must be an online one. But I would definitely switch to ALTER
> INDEX REORGANIZE if I could find some more documentation on LOB_COMPACTION
> .
> Thank you for posting the new_helpindex command. Will definitely use it.
> Mladen
>
> "Kalen Delaney" wrote:
>> Hi Mladen
>> Internally DBCC INDEXDEFRAG and ALTER INDEX REORGANIZE use the same
>> algorithm, so the only 'improvements' in the REORGANIZE option are that
>> there are additional features you can control, such as the LOB
>> compaction.
>> As Aaron states, whether or not you see the new syntax as an improvement
>> depends on whether you want to control LOB compaction or not. Going
>> forward, it's a good idea to use the ALTER INDEX because the DBCC option
>> will be eventually going away, but there is no word yet on when that will
>> be.
>> --
>> HTH
>> Kalen Delaney, SQL Server MVP
>> www.InsideSQLServer.com
>> http://blog.kalendelaney.com
>>
>> "Mladen Andrijasevic" <MladenAndrijasevic@.discussions.microsoft.com>
>> wrote
>> in message news:91BF82DD-6C5A-4702-972A-EFD6A841A8FF@.microsoft.com...
>> > Is ALTER INDEX REORGANIZE equivalent to DBCC INDEXDEFRAG? In
>> > http://msdn2.microsoft.com/en-us/library/ms189858.aspx it says "
>> > Reorganizing a specified clustered index will compact all LOB columns
>> > that
>> > are contained in the leaf level (data rows) of the clustered index" .
>> >
>> > According to Kalen Delaney's Inside SQL Server 2005 The Storage Engine
>> > p
>> > 324
>> > " In SQL Server 2000, the only way you can compact LOBs in a table is
>> > to
>> > unload and reload the LOB data"
>> >
>> > This would make ALTER INDEX REORGANIZE not equivalent but superior to
>> > DBCC
>> > INDEXDEFRAG since it can compact LOB columns whereas DBCC INDEXDEFRAG
>> > cannot.
>> > Is this correct?
>>

Alter Index issue & Try/Catch question

All,
I'm currently modifying a BOL procedure to rebuild/reorganize my
indexes. I've changed the ALTER INDEX command so that it performs the
rebuild online. The first time I ran the script, it errored with the
following error below:
Msg 2725, Level 16, State 2, Line 1
Online index operation cannot be performed for index
'Company$Attachment$0' because the index contains column 'Entry Pointer
ID' of data type text, ntext, image, varchar(max), nvarchar(max),
varbinary(max) or xml. For non-clustered index the column could be an
include column of the index, for clustered index it could be any column
of the table. In case of drop_existing the column could be part of new
or old index. The operation must be performed offline.
At this point, I have tried to integrate the TRY/CATCH routine so that
when this error appears, the script executes the ALTER INDEXES offline
instead (script below). I was wondering if there is a way to possible
write out the error to a log file perhaps?
Thanks,
Ian
SET NOCOUNT ON;
DECLARE @.objectid int;
DECLARE @.indexid int;
DECLARE @.partitioncount bigint;
DECLARE @.schemaname sysname;
DECLARE @.objectname sysname;
DECLARE @.indexname sysname;
DECLARE @.partitionnum bigint;
DECLARE @.partitions bigint;
DECLARE @.frag float;
DECLARE @.command varchar(8000);
-- ensure the temporary table does not exist
IF EXISTS (SELECT name FROM sys.objects WHERE name = 'work_to_do')
DROP TABLE work_to_do;
-- conditionally select from the function, converting object and index
IDs to names.
SELECT
object_id AS objectid,
index_id AS indexid,
partition_number as partitionnum,
avg_fragmentation_in_percent as frag
INTO work_to_do
FROM sys.dm_db_index_physical_stats (5, NULL, NULL , NULL, 'LIMITED')
WHERE avg_fragmentation_in_percent > 10.0 AND index_id > 0;
-- Declare the cursor for the list of partitions to be processed.
DECLARE partitions CURSOR FOR SELECT * FROM work_to_do;
-- Open the cursor.
OPEN partitions;
-- Loop through the partitions.
FETCH NEXT
FROM partitions
INTO @.objectid, @.indexid, @.partitionnum, @.frag;
WHILE @.@.FETCH_STATUS = 0
BEGIN;
SELECT @.objectname = o.name, @.schemaname = s.name
FROM sys.objects AS o
JOIN sys.schemas as s ON s.schema_id = o.schema_id
WHERE o.object_id = @.objectid;
SELECT @.indexname = name
FROM sys.indexes
WHERE object_id = @.objectid AND index_id = @.indexid;
SELECT @.partitioncount = count (*)
FROM sys.partitions
WHERE object_id = @.objectid AND index_id = @.indexid;
-- 30 is an arbitrary decision point at which to switch between
reorganizing and rebuilding
IF @.frag < 30.0
BEGIN;
SELECT @.command = 'ALTER INDEX [' + @.indexname + '] ON ' + '[' +
@.objectname + '] REORGANIZE';
IF @.partitioncount > 1
SELECT @.command = @.command + ' PARTITION=' + CONVERT (CHAR,
@.partitionnum);
PRINT (@.command);
EXEC (@.command);
END;
IF @.frag >= 30.0
BEGIN;
SELECT @.command = 'ALTER INDEX [' + @.indexname +'] ON ' + '[' +
@.objectname + '] REBUILD WITH (ONLINE=OFF, SORT_IN_TEMPDB=ON,
STATISTICS_NORECOMPUTE=OFF) ';
IF @.partitioncount > 1
SELECT @.command = @.command + ' PARTITION=' + CONVERT (CHAR,
@.partitionnum);
BEGIN TRY
EXEC (@.command);
END TRY
BEGIN CATCH
SELECT @.command = 'ALTER INDEX [' + @.indexname +'] ON ' + '[' +
@.objectname + '] REBUILD WITH (ONLINE=OFF, SORT_IN_TEMPDB=ON,
STATISTICS_NORECOMPUTE=OFF) ';
EXEC (@.command);
END CATCH
PRINT (@.command);
END;
PRINT 'Executed ' + @.command;
PRINT
'----';
FETCH NEXT FROM partitions INTO @.objectid, @.indexid, @.partitionnum,
@.frag;
END;
-- Close and deallocate the cursor.
CLOSE partitions;
DEALLOCATE partitions;
To write a event log entry use: RAISERROR
RAISERROR ('Something happened and needs to be logged', 10, 1 ) WITH LOG
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
You can't help someone get up a hill without getting a little closer to the
top yourself.
- H. Norman Schwarzkopf
<theredmiata@.hotmail.com> wrote in message
news:1165272932.638685.169330@.n67g2000cwd.googlegr oups.com...
> All,
> I'm currently modifying a BOL procedure to rebuild/reorganize my
> indexes. I've changed the ALTER INDEX command so that it performs the
> rebuild online. The first time I ran the script, it errored with the
> following error below:
> Msg 2725, Level 16, State 2, Line 1
> Online index operation cannot be performed for index
> 'Company$Attachment$0' because the index contains column 'Entry Pointer
> ID' of data type text, ntext, image, varchar(max), nvarchar(max),
> varbinary(max) or xml. For non-clustered index the column could be an
> include column of the index, for clustered index it could be any column
> of the table. In case of drop_existing the column could be part of new
> or old index. The operation must be performed offline.
> At this point, I have tried to integrate the TRY/CATCH routine so that
> when this error appears, the script executes the ALTER INDEXES offline
> instead (script below). I was wondering if there is a way to possible
> write out the error to a log file perhaps?
> Thanks,
> Ian
>
> SET NOCOUNT ON;
> DECLARE @.objectid int;
> DECLARE @.indexid int;
> DECLARE @.partitioncount bigint;
> DECLARE @.schemaname sysname;
> DECLARE @.objectname sysname;
> DECLARE @.indexname sysname;
> DECLARE @.partitionnum bigint;
> DECLARE @.partitions bigint;
> DECLARE @.frag float;
> DECLARE @.command varchar(8000);
> -- ensure the temporary table does not exist
> IF EXISTS (SELECT name FROM sys.objects WHERE name = 'work_to_do')
> DROP TABLE work_to_do;
> -- conditionally select from the function, converting object and index
> IDs to names.
> SELECT
> object_id AS objectid,
> index_id AS indexid,
> partition_number as partitionnum,
> avg_fragmentation_in_percent as frag
> INTO work_to_do
> FROM sys.dm_db_index_physical_stats (5, NULL, NULL , NULL, 'LIMITED')
> WHERE avg_fragmentation_in_percent > 10.0 AND index_id > 0;
> -- Declare the cursor for the list of partitions to be processed.
> DECLARE partitions CURSOR FOR SELECT * FROM work_to_do;
> -- Open the cursor.
> OPEN partitions;
> -- Loop through the partitions.
> FETCH NEXT
> FROM partitions
> INTO @.objectid, @.indexid, @.partitionnum, @.frag;
> WHILE @.@.FETCH_STATUS = 0
> BEGIN;
> SELECT @.objectname = o.name, @.schemaname = s.name
> FROM sys.objects AS o
> JOIN sys.schemas as s ON s.schema_id = o.schema_id
> WHERE o.object_id = @.objectid;
> SELECT @.indexname = name
> FROM sys.indexes
> WHERE object_id = @.objectid AND index_id = @.indexid;
> SELECT @.partitioncount = count (*)
> FROM sys.partitions
> WHERE object_id = @.objectid AND index_id = @.indexid;
> -- 30 is an arbitrary decision point at which to switch between
> reorganizing and rebuilding
> IF @.frag < 30.0
> BEGIN;
> SELECT @.command = 'ALTER INDEX [' + @.indexname + '] ON ' + '[' +
> @.objectname + '] REORGANIZE';
> IF @.partitioncount > 1
> SELECT @.command = @.command + ' PARTITION=' + CONVERT (CHAR,
> @.partitionnum);
> PRINT (@.command);
> EXEC (@.command);
> END;
> IF @.frag >= 30.0
> BEGIN;
> SELECT @.command = 'ALTER INDEX [' + @.indexname +'] ON ' + '[' +
> @.objectname + '] REBUILD WITH (ONLINE=OFF, SORT_IN_TEMPDB=ON,
> STATISTICS_NORECOMPUTE=OFF) ';
> IF @.partitioncount > 1
> SELECT @.command = @.command + ' PARTITION=' + CONVERT (CHAR,
> @.partitionnum);
> BEGIN TRY
> EXEC (@.command);
> END TRY
> BEGIN CATCH
> SELECT @.command = 'ALTER INDEX [' + @.indexname +'] ON ' + '[' +
> @.objectname + '] REBUILD WITH (ONLINE=OFF, SORT_IN_TEMPDB=ON,
> STATISTICS_NORECOMPUTE=OFF) ';
> EXEC (@.command);
> END CATCH
> PRINT (@.command);
> END;
> PRINT 'Executed ' + @.command;
> PRINT
> '----';
> FETCH NEXT FROM partitions INTO @.objectid, @.indexid, @.partitionnum,
> @.frag;
> END;
> -- Close and deallocate the cursor.
> CLOSE partitions;
> DEALLOCATE partitions;
>

Alter Index issue & Try/Catch question

All,
I'm currently modifying a BOL procedure to rebuild/reorganize my
indexes. I've changed the ALTER INDEX command so that it performs the
rebuild online. The first time I ran the script, it errored with the
following error below:
Msg 2725, Level 16, State 2, Line 1
Online index operation cannot be performed for index
'Company$Attachment$0' because the index contains column 'Entry Pointer
ID' of data type text, ntext, image, varchar(max), nvarchar(max),
varbinary(max) or xml. For non-clustered index the column could be an
include column of the index, for clustered index it could be any column
of the table. In case of drop_existing the column could be part of new
or old index. The operation must be performed offline.
At this point, I have tried to integrate the TRY/CATCH routine so that
when this error appears, the script executes the ALTER INDEXES offline
instead (script below). I was wondering if there is a way to possible
write out the error to a log file perhaps?
Thanks,
Ian
SET NOCOUNT ON;
DECLARE @.objectid int;
DECLARE @.indexid int;
DECLARE @.partitioncount bigint;
DECLARE @.schemaname sysname;
DECLARE @.objectname sysname;
DECLARE @.indexname sysname;
DECLARE @.partitionnum bigint;
DECLARE @.partitions bigint;
DECLARE @.frag float;
DECLARE @.command varchar(8000);
-- ensure the temporary table does not exist
IF EXISTS (SELECT name FROM sys.objects WHERE name = 'work_to_do')
DROP TABLE work_to_do;
-- conditionally select from the function, converting object and index
IDs to names.
SELECT
object_id AS objectid,
index_id AS indexid,
partition_number as partitionnum,
avg_fragmentation_in_percent as frag
INTO work_to_do
FROM sys.dm_db_index_physical_stats (5, NULL, NULL , NULL, 'LIMITED')
WHERE avg_fragmentation_in_percent > 10.0 AND index_id > 0;
-- Declare the cursor for the list of partitions to be processed.
DECLARE partitions CURSOR FOR SELECT * FROM work_to_do;
-- Open the cursor.
OPEN partitions;
-- Loop through the partitions.
FETCH NEXT
FROM partitions
INTO @.objectid, @.indexid, @.partitionnum, @.frag;
WHILE @.@.FETCH_STATUS = 0
BEGIN;
SELECT @.objectname = o.name, @.schemaname = s.name
FROM sys.objects AS o
JOIN sys.schemas as s ON s.schema_id = o.schema_id
WHERE o.object_id = @.objectid;
SELECT @.indexname = name
FROM sys.indexes
WHERE object_id = @.objectid AND index_id = @.indexid;
SELECT @.partitioncount = count (*)
FROM sys.partitions
WHERE object_id = @.objectid AND index_id = @.indexid;
-- 30 is an arbitrary decision point at which to switch between
reorganizing and rebuilding
IF @.frag < 30.0
BEGIN;
SELECT @.command = 'ALTER INDEX [' + @.indexname + '] ON ' + '[' +
@.objectname + '] REORGANIZE';
IF @.partitioncount > 1
SELECT @.command = @.command + ' PARTITION=' + CONVERT (CHAR,
@.partitionnum);
PRINT (@.command);
EXEC (@.command);
END;
IF @.frag >= 30.0
BEGIN;
SELECT @.command = 'ALTER INDEX [' + @.indexname +'] ON ' + '[' +
@.objectname + '] REBUILD WITH (ONLINE=OFF, SORT_IN_TEMPDB=ON,
STATISTICS_NORECOMPUTE=OFF) ';
IF @.partitioncount > 1
SELECT @.command = @.command + ' PARTITION=' + CONVERT (CHAR,
@.partitionnum);
BEGIN TRY
EXEC (@.command);
END TRY
BEGIN CATCH
SELECT @.command = 'ALTER INDEX [' + @.indexname +'] ON ' + '[' +
@.objectname + '] REBUILD WITH (ONLINE=OFF, SORT_IN_TEMPDB=ON,
STATISTICS_NORECOMPUTE=OFF) ';
EXEC (@.command);
END CATCH
PRINT (@.command);
END;
PRINT 'Executed ' + @.command;
PRINT
'----
--';
FETCH NEXT FROM partitions INTO @.objectid, @.indexid, @.partitionnum,
@.frag;
END;
-- Close and deallocate the cursor.
CLOSE partitions;
DEALLOCATE partitions;To write a event log entry use: RAISERROR
RAISERROR ('Something happened and needs to be logged', 10, 1 ) WITH LOG
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
You can't help someone get up a hill without getting a little closer to the
top yourself.
- H. Norman Schwarzkopf
<theredmiata@.hotmail.com> wrote in message
news:1165272932.638685.169330@.n67g2000cwd.googlegroups.com...
> All,
> I'm currently modifying a BOL procedure to rebuild/reorganize my
> indexes. I've changed the ALTER INDEX command so that it performs the
> rebuild online. The first time I ran the script, it errored with the
> following error below:
> Msg 2725, Level 16, State 2, Line 1
> Online index operation cannot be performed for index
> 'Company$Attachment$0' because the index contains column 'Entry Pointer
> ID' of data type text, ntext, image, varchar(max), nvarchar(max),
> varbinary(max) or xml. For non-clustered index the column could be an
> include column of the index, for clustered index it could be any column
> of the table. In case of drop_existing the column could be part of new
> or old index. The operation must be performed offline.
> At this point, I have tried to integrate the TRY/CATCH routine so that
> when this error appears, the script executes the ALTER INDEXES offline
> instead (script below). I was wondering if there is a way to possible
> write out the error to a log file perhaps?
> Thanks,
> Ian
>
> SET NOCOUNT ON;
> DECLARE @.objectid int;
> DECLARE @.indexid int;
> DECLARE @.partitioncount bigint;
> DECLARE @.schemaname sysname;
> DECLARE @.objectname sysname;
> DECLARE @.indexname sysname;
> DECLARE @.partitionnum bigint;
> DECLARE @.partitions bigint;
> DECLARE @.frag float;
> DECLARE @.command varchar(8000);
> -- ensure the temporary table does not exist
> IF EXISTS (SELECT name FROM sys.objects WHERE name = 'work_to_do')
> DROP TABLE work_to_do;
> -- conditionally select from the function, converting object and index
> IDs to names.
> SELECT
> object_id AS objectid,
> index_id AS indexid,
> partition_number as partitionnum,
> avg_fragmentation_in_percent as frag
> INTO work_to_do
> FROM sys.dm_db_index_physical_stats (5, NULL, NULL , NULL, 'LIMITED')
> WHERE avg_fragmentation_in_percent > 10.0 AND index_id > 0;
> -- Declare the cursor for the list of partitions to be processed.
> DECLARE partitions CURSOR FOR SELECT * FROM work_to_do;
> -- Open the cursor.
> OPEN partitions;
> -- Loop through the partitions.
> FETCH NEXT
> FROM partitions
> INTO @.objectid, @.indexid, @.partitionnum, @.frag;
> WHILE @.@.FETCH_STATUS = 0
> BEGIN;
> SELECT @.objectname = o.name, @.schemaname = s.name
> FROM sys.objects AS o
> JOIN sys.schemas as s ON s.schema_id = o.schema_id
> WHERE o.object_id = @.objectid;
> SELECT @.indexname = name
> FROM sys.indexes
> WHERE object_id = @.objectid AND index_id = @.indexid;
> SELECT @.partitioncount = count (*)
> FROM sys.partitions
> WHERE object_id = @.objectid AND index_id = @.indexid;
> -- 30 is an arbitrary decision point at which to switch between
> reorganizing and rebuilding
> IF @.frag < 30.0
> BEGIN;
> SELECT @.command = 'ALTER INDEX [' + @.indexname + '] ON ' + '['
+
> @.objectname + '] REORGANIZE';
> IF @.partitioncount > 1
> SELECT @.command = @.command + ' PARTITION=' + CONVERT (CHAR,
> @.partitionnum);
> PRINT (@.command);
> EXEC (@.command);
> END;
> IF @.frag >= 30.0
> BEGIN;
> SELECT @.command = 'ALTER INDEX [' + @.indexname +'] ON ' + '[' +
> @.objectname + '] REBUILD WITH (ONLINE=OFF, SORT_IN_TEMPDB=ON,
> STATISTICS_NORECOMPUTE=OFF) ';
> IF @.partitioncount > 1
> SELECT @.command = @.command + ' PARTITION=' + CONVERT (CHAR,
> @.partitionnum);
> BEGIN TRY
> EXEC (@.command);
> END TRY
> BEGIN CATCH
> SELECT @.command = 'ALTER INDEX [' + @.indexname +'] ON ' + '[' +
> @.objectname + '] REBUILD WITH (ONLINE=OFF, SORT_IN_TEMPDB=ON,
> STATISTICS_NORECOMPUTE=OFF) ';
> EXEC (@.command);
> END CATCH
> PRINT (@.command);
> END;
> PRINT 'Executed ' + @.command;
> PRINT
> '----
--';
> FETCH NEXT FROM partitions INTO @.objectid, @.indexid, @.partitionnum,
> @.frag;
> END;
> -- Close and deallocate the cursor.
> CLOSE partitions;
> DEALLOCATE partitions;
>

Alter Index issue & Try/Catch question

All,
I'm currently modifying a BOL procedure to rebuild/reorganize my
indexes. I've changed the ALTER INDEX command so that it performs the
rebuild online. The first time I ran the script, it errored with the
following error below:
Msg 2725, Level 16, State 2, Line 1
Online index operation cannot be performed for index
'Company$Attachment$0' because the index contains column 'Entry Pointer
ID' of data type text, ntext, image, varchar(max), nvarchar(max),
varbinary(max) or xml. For non-clustered index the column could be an
include column of the index, for clustered index it could be any column
of the table. In case of drop_existing the column could be part of new
or old index. The operation must be performed offline.
At this point, I have tried to integrate the TRY/CATCH routine so that
when this error appears, the script executes the ALTER INDEXES offline
instead (script below). I was wondering if there is a way to possible
write out the error to a log file perhaps?
Thanks,
Ian
SET NOCOUNT ON;
DECLARE @.objectid int;
DECLARE @.indexid int;
DECLARE @.partitioncount bigint;
DECLARE @.schemaname sysname;
DECLARE @.objectname sysname;
DECLARE @.indexname sysname;
DECLARE @.partitionnum bigint;
DECLARE @.partitions bigint;
DECLARE @.frag float;
DECLARE @.command varchar(8000);
-- ensure the temporary table does not exist
IF EXISTS (SELECT name FROM sys.objects WHERE name = 'work_to_do')
DROP TABLE work_to_do;
-- conditionally select from the function, converting object and index
IDs to names.
SELECT
object_id AS objectid,
index_id AS indexid,
partition_number as partitionnum,
avg_fragmentation_in_percent as frag
INTO work_to_do
FROM sys.dm_db_index_physical_stats (5, NULL, NULL , NULL, 'LIMITED')
WHERE avg_fragmentation_in_percent > 10.0 AND index_id > 0;
-- Declare the cursor for the list of partitions to be processed.
DECLARE partitions CURSOR FOR SELECT * FROM work_to_do;
-- Open the cursor.
OPEN partitions;
-- Loop through the partitions.
FETCH NEXT
FROM partitions
INTO @.objectid, @.indexid, @.partitionnum, @.frag;
WHILE @.@.FETCH_STATUS = 0
BEGIN;
SELECT @.objectname = o.name, @.schemaname = s.name
FROM sys.objects AS o
JOIN sys.schemas as s ON s.schema_id = o.schema_id
WHERE o.object_id = @.objectid;
SELECT @.indexname = name
FROM sys.indexes
WHERE object_id = @.objectid AND index_id = @.indexid;
SELECT @.partitioncount = count (*)
FROM sys.partitions
WHERE object_id = @.objectid AND index_id = @.indexid;
-- 30 is an arbitrary decision point at which to switch between
reorganizing and rebuilding
IF @.frag < 30.0
BEGIN;
SELECT @.command = 'ALTER INDEX [' + @.indexname + '] ON ' + '[' +
@.objectname + '] REORGANIZE';
IF @.partitioncount > 1
SELECT @.command = @.command + ' PARTITION=' + CONVERT (CHAR,
@.partitionnum);
PRINT (@.command);
EXEC (@.command);
END;
IF @.frag >= 30.0
BEGIN;
SELECT @.command = 'ALTER INDEX [' + @.indexname +'] ON ' + '[' +
@.objectname + '] REBUILD WITH (ONLINE=OFF, SORT_IN_TEMPDB=ON,
STATISTICS_NORECOMPUTE=OFF) ';
IF @.partitioncount > 1
SELECT @.command = @.command + ' PARTITION=' + CONVERT (CHAR,
@.partitionnum);
BEGIN TRY
EXEC (@.command);
END TRY
BEGIN CATCH
SELECT @.command = 'ALTER INDEX [' + @.indexname +'] ON ' + '[' +
@.objectname + '] REBUILD WITH (ONLINE=OFF, SORT_IN_TEMPDB=ON,
STATISTICS_NORECOMPUTE=OFF) ';
EXEC (@.command);
END CATCH
PRINT (@.command);
END;
PRINT 'Executed ' + @.command;
PRINT
'----';
FETCH NEXT FROM partitions INTO @.objectid, @.indexid, @.partitionnum,
@.frag;
END;
-- Close and deallocate the cursor.
CLOSE partitions;
DEALLOCATE partitions;To write a event log entry use: RAISERROR
RAISERROR ('Something happened and needs to be logged', 10, 1 ) WITH LOG
--
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
You can't help someone get up a hill without getting a little closer to the
top yourself.
- H. Norman Schwarzkopf
<theredmiata@.hotmail.com> wrote in message
news:1165272932.638685.169330@.n67g2000cwd.googlegroups.com...
> All,
> I'm currently modifying a BOL procedure to rebuild/reorganize my
> indexes. I've changed the ALTER INDEX command so that it performs the
> rebuild online. The first time I ran the script, it errored with the
> following error below:
> Msg 2725, Level 16, State 2, Line 1
> Online index operation cannot be performed for index
> 'Company$Attachment$0' because the index contains column 'Entry Pointer
> ID' of data type text, ntext, image, varchar(max), nvarchar(max),
> varbinary(max) or xml. For non-clustered index the column could be an
> include column of the index, for clustered index it could be any column
> of the table. In case of drop_existing the column could be part of new
> or old index. The operation must be performed offline.
> At this point, I have tried to integrate the TRY/CATCH routine so that
> when this error appears, the script executes the ALTER INDEXES offline
> instead (script below). I was wondering if there is a way to possible
> write out the error to a log file perhaps?
> Thanks,
> Ian
>
> SET NOCOUNT ON;
> DECLARE @.objectid int;
> DECLARE @.indexid int;
> DECLARE @.partitioncount bigint;
> DECLARE @.schemaname sysname;
> DECLARE @.objectname sysname;
> DECLARE @.indexname sysname;
> DECLARE @.partitionnum bigint;
> DECLARE @.partitions bigint;
> DECLARE @.frag float;
> DECLARE @.command varchar(8000);
> -- ensure the temporary table does not exist
> IF EXISTS (SELECT name FROM sys.objects WHERE name = 'work_to_do')
> DROP TABLE work_to_do;
> -- conditionally select from the function, converting object and index
> IDs to names.
> SELECT
> object_id AS objectid,
> index_id AS indexid,
> partition_number as partitionnum,
> avg_fragmentation_in_percent as frag
> INTO work_to_do
> FROM sys.dm_db_index_physical_stats (5, NULL, NULL , NULL, 'LIMITED')
> WHERE avg_fragmentation_in_percent > 10.0 AND index_id > 0;
> -- Declare the cursor for the list of partitions to be processed.
> DECLARE partitions CURSOR FOR SELECT * FROM work_to_do;
> -- Open the cursor.
> OPEN partitions;
> -- Loop through the partitions.
> FETCH NEXT
> FROM partitions
> INTO @.objectid, @.indexid, @.partitionnum, @.frag;
> WHILE @.@.FETCH_STATUS = 0
> BEGIN;
> SELECT @.objectname = o.name, @.schemaname = s.name
> FROM sys.objects AS o
> JOIN sys.schemas as s ON s.schema_id = o.schema_id
> WHERE o.object_id = @.objectid;
> SELECT @.indexname = name
> FROM sys.indexes
> WHERE object_id = @.objectid AND index_id = @.indexid;
> SELECT @.partitioncount = count (*)
> FROM sys.partitions
> WHERE object_id = @.objectid AND index_id = @.indexid;
> -- 30 is an arbitrary decision point at which to switch between
> reorganizing and rebuilding
> IF @.frag < 30.0
> BEGIN;
> SELECT @.command = 'ALTER INDEX [' + @.indexname + '] ON ' + '[' +
> @.objectname + '] REORGANIZE';
> IF @.partitioncount > 1
> SELECT @.command = @.command + ' PARTITION=' + CONVERT (CHAR,
> @.partitionnum);
> PRINT (@.command);
> EXEC (@.command);
> END;
> IF @.frag >= 30.0
> BEGIN;
> SELECT @.command = 'ALTER INDEX [' + @.indexname +'] ON ' + '[' +
> @.objectname + '] REBUILD WITH (ONLINE=OFF, SORT_IN_TEMPDB=ON,
> STATISTICS_NORECOMPUTE=OFF) ';
> IF @.partitioncount > 1
> SELECT @.command = @.command + ' PARTITION=' + CONVERT (CHAR,
> @.partitionnum);
> BEGIN TRY
> EXEC (@.command);
> END TRY
> BEGIN CATCH
> SELECT @.command = 'ALTER INDEX [' + @.indexname +'] ON ' + '[' +
> @.objectname + '] REBUILD WITH (ONLINE=OFF, SORT_IN_TEMPDB=ON,
> STATISTICS_NORECOMPUTE=OFF) ';
> EXEC (@.command);
> END CATCH
> PRINT (@.command);
> END;
> PRINT 'Executed ' + @.command;
> PRINT
> '----';
> FETCH NEXT FROM partitions INTO @.objectid, @.indexid, @.partitionnum,
> @.frag;
> END;
> -- Close and deallocate the cursor.
> CLOSE partitions;
> DEALLOCATE partitions;
>

Thursday, March 8, 2012

alter index syntax

alter index syntax

ALTER INDEX REORGANIZE vs DBCC INDEXDEFRAG

ALTER INDEX REORGANIZE vs DBCC INDEXDEFRAG

ALTER INDEX REORGANIZE vs DBCC INDEXDEFRAG

Alter Index issue & Try/Catch question

Alter Index issue & Try/Catch question

Alter Index issue & Try/Catch question

Sql Server Alignment problem

Blog Archive

About Me