I need to reindex a table with a computed column. The column is not
included in any indexes, but when I run the DBCC it crashes with the
following error:
DBCC failed because the following SET options have incorrect settings:
'QUOTED_IDENTIFIER'.
Any ideas on how I can reindex these tables?
Thanks!
Richard
*** Sent via Developersdex http://www.codecomments.com ***
Richard,
Sounds like the QUOTED_IDENTIFIER option needs to be ON.
Try the last section of this link:
Creating Indexes on Computed Columns
http://msdn.microsoft.com/library/de...es_05_8os3.asp
HTH
Jerry
"Richard" <nospam@.devdex.com> wrote in message
news:%23Qes6HM2FHA.460@.TK2MSFTNGP15.phx.gbl...
>I need to reindex a table with a computed column. The column is not
> included in any indexes, but when I run the DBCC it crashes with the
> following error:
> DBCC failed because the following SET options have incorrect settings:
> 'QUOTED_IDENTIFIER'.
> Any ideas on how I can reindex these tables?
> Thanks!
> Richard
>
> *** Sent via Developersdex http://www.codecomments.com ***
|||If you're using a non-named instance and are running SP4, you can use
a -supportcomputedcolumn parameter in the first step of the job. If you're
using < SP4 or a named instance, you'll have to create a separate job to
execute the integrity/optimizations. See
http://support.microsoft.com/default...b;en-us;902388
I had this trouble in a Sharepoint database. I created a separate job with
two steps, one for integrity checks and one for reorg on all tables. This
KB will give you the script to reorg all tables
http://support.microsoft.com/kb/301292/
HTH
--Lori
"Richard" <nospam@.devdex.com> wrote in message
news:%23Qes6HM2FHA.460@.TK2MSFTNGP15.phx.gbl...
>I need to reindex a table with a computed column. The column is not
> included in any indexes, but when I run the DBCC it crashes with the
> following error:
> DBCC failed because the following SET options have incorrect settings:
> 'QUOTED_IDENTIFIER'.
> Any ideas on how I can reindex these tables?
> Thanks!
> Richard
>
> *** Sent via Developersdex http://www.codecomments.com ***
|||Note that the scripts reorgs whether the index is fragmented or not (just as maint wiz does). If you
only want to reorg if there is any fragmentation in the first place, you should use the sample code
provided in Books Online, DBCC SHOWCONTIG.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Lori Clark" <lclark@.dbadvisor.com> wrote in message news:eCYr0NM2FHA.3864@.TK2MSFTNGP12.phx.gbl...
> If you're using a non-named instance and are running SP4, you can use a -supportcomputedcolumn
> parameter in the first step of the job. If you're using < SP4 or a named instance, you'll have to
> create a separate job to execute the integrity/optimizations. See
> http://support.microsoft.com/default...b;en-us;902388
> I had this trouble in a Sharepoint database. I created a separate job with two steps, one for
> integrity checks and one for reorg on all tables. This KB will give you the script to reorg all
> tables
> http://support.microsoft.com/kb/301292/
>
> HTH
> --Lori
> "Richard" <nospam@.devdex.com> wrote in message news:%23Qes6HM2FHA.460@.TK2MSFTNGP15.phx.gbl...
>
Showing posts with label reindex. Show all posts
Showing posts with label reindex. Show all posts
Tuesday, March 20, 2012
Monday, March 12, 2012
Reindexing tables with computed columns
I need to reindex a table with a computed column. The column is not
included in any indexes, but when I run the DBCC it crashes with the
following error:
DBCC failed because the following SET options have incorrect settings:
'QUOTED_IDENTIFIER'.
Any ideas on how I can reindex these tables'
Thanks!
Richard
*** Sent via Developersdex http://www.codecomments.com ***Richard,
Sounds like the QUOTED_IDENTIFIER option needs to be ON.
Try the last section of this link:
Creating Indexes on Computed Columns
http://msdn.microsoft.com/library/d...>
_05_8os3.asp
HTH
Jerry
"Richard" <nospam@.devdex.com> wrote in message
news:%23Qes6HM2FHA.460@.TK2MSFTNGP15.phx.gbl...
>I need to reindex a table with a computed column. The column is not
> included in any indexes, but when I run the DBCC it crashes with the
> following error:
> DBCC failed because the following SET options have incorrect settings:
> 'QUOTED_IDENTIFIER'.
> Any ideas on how I can reindex these tables'
> Thanks!
> Richard
>
> *** Sent via Developersdex http://www.codecomments.com ***|||If you're using a non-named instance and are running SP4, you can use
a -supportcomputedcolumn parameter in the first step of the job. If you're
using < SP4 or a named instance, you'll have to create a separate job to
execute the integrity/optimizations. See
http://support.microsoft.com/defaul...kb;en-us;902388
I had this trouble in a Sharepoint database. I created a separate job with
two steps, one for integrity checks and one for reorg on all tables. This
KB will give you the script to reorg all tables
http://support.microsoft.com/kb/301292/
HTH
--Lori
"Richard" <nospam@.devdex.com> wrote in message
news:%23Qes6HM2FHA.460@.TK2MSFTNGP15.phx.gbl...
>I need to reindex a table with a computed column. The column is not
> included in any indexes, but when I run the DBCC it crashes with the
> following error:
> DBCC failed because the following SET options have incorrect settings:
> 'QUOTED_IDENTIFIER'.
> Any ideas on how I can reindex these tables'
> Thanks!
> Richard
>
> *** Sent via Developersdex http://www.codecomments.com ***|||Note that the scripts reorgs whether the index is fragmented or not (just as
maint wiz does). If you
only want to reorg if there is any fragmentation in the first place, you sho
uld use the sample code
provided in Books Online, DBCC SHOWCONTIG.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Lori Clark" <lclark@.dbadvisor.com> wrote in message news:eCYr0NM2FHA.3864@.TK2MSFTNGP12.phx.
gbl...
> If you're using a non-named instance and are running SP4, you can use a -s
upportcomputedcolumn
> parameter in the first step of the job. If you're using < SP4 or a named
instance, you'll have to
> create a separate job to execute the integrity/optimizations. See
> http://support.microsoft.com/defaul...kb;en-us;902388
> I had this trouble in a Sharepoint database. I created a separate job wit
h two steps, one for
> integrity checks and one for reorg on all tables. This KB will give you t
he script to reorg all
> tables
> http://support.microsoft.com/kb/301292/
>
> HTH
> --Lori
> "Richard" <nospam@.devdex.com> wrote in message news:%23Qes6HM2FHA.460@.TK2M
SFTNGP15.phx.gbl...
>
included in any indexes, but when I run the DBCC it crashes with the
following error:
DBCC failed because the following SET options have incorrect settings:
'QUOTED_IDENTIFIER'.
Any ideas on how I can reindex these tables'
Thanks!
Richard
*** Sent via Developersdex http://www.codecomments.com ***Richard,
Sounds like the QUOTED_IDENTIFIER option needs to be ON.
Try the last section of this link:
Creating Indexes on Computed Columns
http://msdn.microsoft.com/library/d...>
_05_8os3.asp
HTH
Jerry
"Richard" <nospam@.devdex.com> wrote in message
news:%23Qes6HM2FHA.460@.TK2MSFTNGP15.phx.gbl...
>I need to reindex a table with a computed column. The column is not
> included in any indexes, but when I run the DBCC it crashes with the
> following error:
> DBCC failed because the following SET options have incorrect settings:
> 'QUOTED_IDENTIFIER'.
> Any ideas on how I can reindex these tables'
> Thanks!
> Richard
>
> *** Sent via Developersdex http://www.codecomments.com ***|||If you're using a non-named instance and are running SP4, you can use
a -supportcomputedcolumn parameter in the first step of the job. If you're
using < SP4 or a named instance, you'll have to create a separate job to
execute the integrity/optimizations. See
http://support.microsoft.com/defaul...kb;en-us;902388
I had this trouble in a Sharepoint database. I created a separate job with
two steps, one for integrity checks and one for reorg on all tables. This
KB will give you the script to reorg all tables
http://support.microsoft.com/kb/301292/
HTH
--Lori
"Richard" <nospam@.devdex.com> wrote in message
news:%23Qes6HM2FHA.460@.TK2MSFTNGP15.phx.gbl...
>I need to reindex a table with a computed column. The column is not
> included in any indexes, but when I run the DBCC it crashes with the
> following error:
> DBCC failed because the following SET options have incorrect settings:
> 'QUOTED_IDENTIFIER'.
> Any ideas on how I can reindex these tables'
> Thanks!
> Richard
>
> *** Sent via Developersdex http://www.codecomments.com ***|||Note that the scripts reorgs whether the index is fragmented or not (just as
maint wiz does). If you
only want to reorg if there is any fragmentation in the first place, you sho
uld use the sample code
provided in Books Online, DBCC SHOWCONTIG.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Lori Clark" <lclark@.dbadvisor.com> wrote in message news:eCYr0NM2FHA.3864@.TK2MSFTNGP12.phx.
gbl...
> If you're using a non-named instance and are running SP4, you can use a -s
upportcomputedcolumn
> parameter in the first step of the job. If you're using < SP4 or a named
instance, you'll have to
> create a separate job to execute the integrity/optimizations. See
> http://support.microsoft.com/defaul...kb;en-us;902388
> I had this trouble in a Sharepoint database. I created a separate job wit
h two steps, one for
> integrity checks and one for reorg on all tables. This KB will give you t
he script to reorg all
> tables
> http://support.microsoft.com/kb/301292/
>
> HTH
> --Lori
> "Richard" <nospam@.devdex.com> wrote in message news:%23Qes6HM2FHA.460@.TK2M
SFTNGP15.phx.gbl...
>
Reindexing tables with computed columns
I need to reindex a table with a computed column. The column is not
included in any indexes, but when I run the DBCC it crashes with the
following error:
DBCC failed because the following SET options have incorrect settings:
'QUOTED_IDENTIFIER'.
Any ideas on how I can reindex these tables'
Thanks!
Richard
*** Sent via Developersdex http://www.developersdex.com ***Richard,
Sounds like the QUOTED_IDENTIFIER option needs to be ON.
Try the last section of this link:
Creating Indexes on Computed Columns
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/createdb/cm_8_des_05_8os3.asp
HTH
Jerry
"Richard" <nospam@.devdex.com> wrote in message
news:%23Qes6HM2FHA.460@.TK2MSFTNGP15.phx.gbl...
>I need to reindex a table with a computed column. The column is not
> included in any indexes, but when I run the DBCC it crashes with the
> following error:
> DBCC failed because the following SET options have incorrect settings:
> 'QUOTED_IDENTIFIER'.
> Any ideas on how I can reindex these tables'
> Thanks!
> Richard
>
> *** Sent via Developersdex http://www.developersdex.com ***|||If you're using a non-named instance and are running SP4, you can use
a -supportcomputedcolumn parameter in the first step of the job. If you're
using < SP4 or a named instance, you'll have to create a separate job to
execute the integrity/optimizations. See
http://support.microsoft.com/default.aspx?scid=kb;en-us;902388
I had this trouble in a Sharepoint database. I created a separate job with
two steps, one for integrity checks and one for reorg on all tables. This
KB will give you the script to reorg all tables
http://support.microsoft.com/kb/301292/
HTH
--Lori
"Richard" <nospam@.devdex.com> wrote in message
news:%23Qes6HM2FHA.460@.TK2MSFTNGP15.phx.gbl...
>I need to reindex a table with a computed column. The column is not
> included in any indexes, but when I run the DBCC it crashes with the
> following error:
> DBCC failed because the following SET options have incorrect settings:
> 'QUOTED_IDENTIFIER'.
> Any ideas on how I can reindex these tables'
> Thanks!
> Richard
>
> *** Sent via Developersdex http://www.developersdex.com ***|||Note that the scripts reorgs whether the index is fragmented or not (just as maint wiz does). If you
only want to reorg if there is any fragmentation in the first place, you should use the sample code
provided in Books Online, DBCC SHOWCONTIG.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Lori Clark" <lclark@.dbadvisor.com> wrote in message news:eCYr0NM2FHA.3864@.TK2MSFTNGP12.phx.gbl...
> If you're using a non-named instance and are running SP4, you can use a -supportcomputedcolumn
> parameter in the first step of the job. If you're using < SP4 or a named instance, you'll have to
> create a separate job to execute the integrity/optimizations. See
> http://support.microsoft.com/default.aspx?scid=kb;en-us;902388
> I had this trouble in a Sharepoint database. I created a separate job with two steps, one for
> integrity checks and one for reorg on all tables. This KB will give you the script to reorg all
> tables
> http://support.microsoft.com/kb/301292/
>
> HTH
> --Lori
> "Richard" <nospam@.devdex.com> wrote in message news:%23Qes6HM2FHA.460@.TK2MSFTNGP15.phx.gbl...
>>I need to reindex a table with a computed column. The column is not
>> included in any indexes, but when I run the DBCC it crashes with the
>> following error:
>> DBCC failed because the following SET options have incorrect settings:
>> 'QUOTED_IDENTIFIER'.
>> Any ideas on how I can reindex these tables'
>> Thanks!
>> Richard
>>
>> *** Sent via Developersdex http://www.developersdex.com ***
>
included in any indexes, but when I run the DBCC it crashes with the
following error:
DBCC failed because the following SET options have incorrect settings:
'QUOTED_IDENTIFIER'.
Any ideas on how I can reindex these tables'
Thanks!
Richard
*** Sent via Developersdex http://www.developersdex.com ***Richard,
Sounds like the QUOTED_IDENTIFIER option needs to be ON.
Try the last section of this link:
Creating Indexes on Computed Columns
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/createdb/cm_8_des_05_8os3.asp
HTH
Jerry
"Richard" <nospam@.devdex.com> wrote in message
news:%23Qes6HM2FHA.460@.TK2MSFTNGP15.phx.gbl...
>I need to reindex a table with a computed column. The column is not
> included in any indexes, but when I run the DBCC it crashes with the
> following error:
> DBCC failed because the following SET options have incorrect settings:
> 'QUOTED_IDENTIFIER'.
> Any ideas on how I can reindex these tables'
> Thanks!
> Richard
>
> *** Sent via Developersdex http://www.developersdex.com ***|||If you're using a non-named instance and are running SP4, you can use
a -supportcomputedcolumn parameter in the first step of the job. If you're
using < SP4 or a named instance, you'll have to create a separate job to
execute the integrity/optimizations. See
http://support.microsoft.com/default.aspx?scid=kb;en-us;902388
I had this trouble in a Sharepoint database. I created a separate job with
two steps, one for integrity checks and one for reorg on all tables. This
KB will give you the script to reorg all tables
http://support.microsoft.com/kb/301292/
HTH
--Lori
"Richard" <nospam@.devdex.com> wrote in message
news:%23Qes6HM2FHA.460@.TK2MSFTNGP15.phx.gbl...
>I need to reindex a table with a computed column. The column is not
> included in any indexes, but when I run the DBCC it crashes with the
> following error:
> DBCC failed because the following SET options have incorrect settings:
> 'QUOTED_IDENTIFIER'.
> Any ideas on how I can reindex these tables'
> Thanks!
> Richard
>
> *** Sent via Developersdex http://www.developersdex.com ***|||Note that the scripts reorgs whether the index is fragmented or not (just as maint wiz does). If you
only want to reorg if there is any fragmentation in the first place, you should use the sample code
provided in Books Online, DBCC SHOWCONTIG.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Lori Clark" <lclark@.dbadvisor.com> wrote in message news:eCYr0NM2FHA.3864@.TK2MSFTNGP12.phx.gbl...
> If you're using a non-named instance and are running SP4, you can use a -supportcomputedcolumn
> parameter in the first step of the job. If you're using < SP4 or a named instance, you'll have to
> create a separate job to execute the integrity/optimizations. See
> http://support.microsoft.com/default.aspx?scid=kb;en-us;902388
> I had this trouble in a Sharepoint database. I created a separate job with two steps, one for
> integrity checks and one for reorg on all tables. This KB will give you the script to reorg all
> tables
> http://support.microsoft.com/kb/301292/
>
> HTH
> --Lori
> "Richard" <nospam@.devdex.com> wrote in message news:%23Qes6HM2FHA.460@.TK2MSFTNGP15.phx.gbl...
>>I need to reindex a table with a computed column. The column is not
>> included in any indexes, but when I run the DBCC it crashes with the
>> following error:
>> DBCC failed because the following SET options have incorrect settings:
>> 'QUOTED_IDENTIFIER'.
>> Any ideas on how I can reindex these tables'
>> Thanks!
>> Richard
>>
>> *** Sent via Developersdex http://www.developersdex.com ***
>
Reindexing log shipped database
Hallo
I need to setup log shipping on a DB and to create a job that would reindex
that same DB.
If I use a maintenance plan to reindex a DB that is 30 GB in size, it takes
more than 1 hour and during that time the DB is not accessible for users.
This is NOT OK so I'm planning to use a script that would use dbcc
indexdefrag.
I don't know how that would effect transaction log growth. I suspect that
log would grow very much in full-mode or in bulk-logged mode.
But that means that after i set up log shipping on that database, first log
backup after reindexation will be huge and it will take a lot of time to
transfer it over network to secondary server. During that time SQL would
probably not be accessible or time-outs would accour.
Anyone has any advice on this? Or is there any other way to reindex a
log-shipped database?
thanks
Tomyou want to Defrag the "Source" Database or the "Destination" ?
the Destination is a Standby so, essentially, read only. I dont think you
want to be defragging it.
Greg Jackson
PDX, Oregon|||I want to defrag source database. Fregmentation of destination base is not
so important to me.
Tom
"pdxJaxon" <GregoryAJackson@.Hotmail.com> wrote in message
news:%23a$pMckCFHA.1432@.tk2msftngp13.phx.gbl...
> you want to Defrag the "Source" Database or the "Destination" ?
>
> the Destination is a Standby so, essentially, read only. I dont think you
> want to be defragging it.
>
>
> Greg Jackson
> PDX, Oregon
>|||you can use IndexDefrag.
Yes the logging can be fairly extensive and it's a pain to ship the log
activity for index defrags. Defrag indexes regularly so they dont get
massively fragmented. Also monitor fill factor settings etc to reduce
fragmentation.
The database should be available...if not, what is causing it to be blocked
?
your other options are to Reseed the standby server with a full backup after
your defrag jobs.
GAJ|||You should read the whitepaper on fragmentation as it goes into details of
logging. It will also help you determine whether your query workload will
benefit from removing fragmentation regularly.
http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ss2kidbp.mspx
Regards
--
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"pdxJaxon" <GregoryAJackson@.Hotmail.com> wrote in message
news:OVrIHSuCFHA.2676@.TK2MSFTNGP12.phx.gbl...
> you can use IndexDefrag.
> Yes the logging can be fairly extensive and it's a pain to ship the log
> activity for index defrags. Defrag indexes regularly so they dont get
> massively fragmented. Also monitor fill factor settings etc to reduce
> fragmentation.
> The database should be available...if not, what is causing it to be
blocked
> ?
> your other options are to Reseed the standby server with a full backup
after
> your defrag jobs.
>
> GAJ
>
I need to setup log shipping on a DB and to create a job that would reindex
that same DB.
If I use a maintenance plan to reindex a DB that is 30 GB in size, it takes
more than 1 hour and during that time the DB is not accessible for users.
This is NOT OK so I'm planning to use a script that would use dbcc
indexdefrag.
I don't know how that would effect transaction log growth. I suspect that
log would grow very much in full-mode or in bulk-logged mode.
But that means that after i set up log shipping on that database, first log
backup after reindexation will be huge and it will take a lot of time to
transfer it over network to secondary server. During that time SQL would
probably not be accessible or time-outs would accour.
Anyone has any advice on this? Or is there any other way to reindex a
log-shipped database?
thanks
Tomyou want to Defrag the "Source" Database or the "Destination" ?
the Destination is a Standby so, essentially, read only. I dont think you
want to be defragging it.
Greg Jackson
PDX, Oregon|||I want to defrag source database. Fregmentation of destination base is not
so important to me.
Tom
"pdxJaxon" <GregoryAJackson@.Hotmail.com> wrote in message
news:%23a$pMckCFHA.1432@.tk2msftngp13.phx.gbl...
> you want to Defrag the "Source" Database or the "Destination" ?
>
> the Destination is a Standby so, essentially, read only. I dont think you
> want to be defragging it.
>
>
> Greg Jackson
> PDX, Oregon
>|||you can use IndexDefrag.
Yes the logging can be fairly extensive and it's a pain to ship the log
activity for index defrags. Defrag indexes regularly so they dont get
massively fragmented. Also monitor fill factor settings etc to reduce
fragmentation.
The database should be available...if not, what is causing it to be blocked
?
your other options are to Reseed the standby server with a full backup after
your defrag jobs.
GAJ|||You should read the whitepaper on fragmentation as it goes into details of
logging. It will also help you determine whether your query workload will
benefit from removing fragmentation regularly.
http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ss2kidbp.mspx
Regards
--
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"pdxJaxon" <GregoryAJackson@.Hotmail.com> wrote in message
news:OVrIHSuCFHA.2676@.TK2MSFTNGP12.phx.gbl...
> you can use IndexDefrag.
> Yes the logging can be fairly extensive and it's a pain to ship the log
> activity for index defrags. Defrag indexes regularly so they dont get
> massively fragmented. Also monitor fill factor settings etc to reduce
> fragmentation.
> The database should be available...if not, what is causing it to be
blocked
> ?
> your other options are to Reseed the standby server with a full backup
after
> your defrag jobs.
>
> GAJ
>
Reindexing log shipped database
Hallo
I need to setup log shipping on a DB and to create a job that would reindex
that same DB.
If I use a maintenance plan to reindex a DB that is 30 GB in size, it takes
more than 1 hour and during that time the DB is not accessible for users.
This is NOT OK so I'm planning to use a script that would use dbcc
indexdefrag.
I don't know how that would effect transaction log growth. I suspect that
log would grow very much in full-mode or in bulk-logged mode.
But that means that after i set up log shipping on that database, first log
backup after reindexation will be huge and it will take a lot of time to
transfer it over network to secondary server. During that time SQL would
probably not be accessible or time-outs would accour.
Anyone has any advice on this? Or is there any other way to reindex a
log-shipped database?
thanks
Tom
you want to Defrag the "Source" Database or the "Destination" ?
the Destination is a Standby so, essentially, read only. I dont think you
want to be defragging it.
Greg Jackson
PDX, Oregon
|||I want to defrag source database. Fregmentation of destination base is not
so important to me.
Tom
"pdxJaxon" <GregoryAJackson@.Hotmail.com> wrote in message
news:%23a$pMckCFHA.1432@.tk2msftngp13.phx.gbl...
> you want to Defrag the "Source" Database or the "Destination" ?
>
> the Destination is a Standby so, essentially, read only. I dont think you
> want to be defragging it.
>
>
> Greg Jackson
> PDX, Oregon
>
|||you can use IndexDefrag.
Yes the logging can be fairly extensive and it's a pain to ship the log
activity for index defrags. Defrag indexes regularly so they dont get
massively fragmented. Also monitor fill factor settings etc to reduce
fragmentation.
The database should be available...if not, what is causing it to be blocked
?
your other options are to Reseed the standby server with a full backup after
your defrag jobs.
GAJ
|||You should read the whitepaper on fragmentation as it goes into details of
logging. It will also help you determine whether your query workload will
benefit from removing fragmentation regularly.
http://www.microsoft.com/technet/pro.../ss2kidbp.mspx
Regards
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"pdxJaxon" <GregoryAJackson@.Hotmail.com> wrote in message
news:OVrIHSuCFHA.2676@.TK2MSFTNGP12.phx.gbl...
> you can use IndexDefrag.
> Yes the logging can be fairly extensive and it's a pain to ship the log
> activity for index defrags. Defrag indexes regularly so they dont get
> massively fragmented. Also monitor fill factor settings etc to reduce
> fragmentation.
> The database should be available...if not, what is causing it to be
blocked
> ?
> your other options are to Reseed the standby server with a full backup
after
> your defrag jobs.
>
> GAJ
>
I need to setup log shipping on a DB and to create a job that would reindex
that same DB.
If I use a maintenance plan to reindex a DB that is 30 GB in size, it takes
more than 1 hour and during that time the DB is not accessible for users.
This is NOT OK so I'm planning to use a script that would use dbcc
indexdefrag.
I don't know how that would effect transaction log growth. I suspect that
log would grow very much in full-mode or in bulk-logged mode.
But that means that after i set up log shipping on that database, first log
backup after reindexation will be huge and it will take a lot of time to
transfer it over network to secondary server. During that time SQL would
probably not be accessible or time-outs would accour.
Anyone has any advice on this? Or is there any other way to reindex a
log-shipped database?
thanks
Tom
you want to Defrag the "Source" Database or the "Destination" ?
the Destination is a Standby so, essentially, read only. I dont think you
want to be defragging it.
Greg Jackson
PDX, Oregon
|||I want to defrag source database. Fregmentation of destination base is not
so important to me.
Tom
"pdxJaxon" <GregoryAJackson@.Hotmail.com> wrote in message
news:%23a$pMckCFHA.1432@.tk2msftngp13.phx.gbl...
> you want to Defrag the "Source" Database or the "Destination" ?
>
> the Destination is a Standby so, essentially, read only. I dont think you
> want to be defragging it.
>
>
> Greg Jackson
> PDX, Oregon
>
|||you can use IndexDefrag.
Yes the logging can be fairly extensive and it's a pain to ship the log
activity for index defrags. Defrag indexes regularly so they dont get
massively fragmented. Also monitor fill factor settings etc to reduce
fragmentation.
The database should be available...if not, what is causing it to be blocked
?
your other options are to Reseed the standby server with a full backup after
your defrag jobs.
GAJ
|||You should read the whitepaper on fragmentation as it goes into details of
logging. It will also help you determine whether your query workload will
benefit from removing fragmentation regularly.
http://www.microsoft.com/technet/pro.../ss2kidbp.mspx
Regards
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"pdxJaxon" <GregoryAJackson@.Hotmail.com> wrote in message
news:OVrIHSuCFHA.2676@.TK2MSFTNGP12.phx.gbl...
> you can use IndexDefrag.
> Yes the logging can be fairly extensive and it's a pain to ship the log
> activity for index defrags. Defrag indexes regularly so they dont get
> massively fragmented. Also monitor fill factor settings etc to reduce
> fragmentation.
> The database should be available...if not, what is causing it to be
blocked
> ?
> your other options are to Reseed the standby server with a full backup
after
> your defrag jobs.
>
> GAJ
>
Reindexing log shipped database
Hallo
I need to setup log shipping on a DB and to create a job that would reindex
that same DB.
If I use a maintenance plan to reindex a DB that is 30 GB in size, it takes
more than 1 hour and during that time the DB is not accessible for users.
This is NOT OK so I'm planning to use a script that would use dbcc
indexdefrag.
I don't know how that would effect transaction log growth. I suspect that
log would grow very much in full-mode or in bulk-logged mode.
But that means that after i set up log shipping on that database, first log
backup after reindexation will be huge and it will take a lot of time to
transfer it over network to secondary server. During that time SQL would
probably not be accessible or time-outs would accour.
Anyone has any advice on this? Or is there any other way to reindex a
log-shipped database?
thanks
Tomyou want to Defrag the "Source" Database or the "Destination" ?
the Destination is a Standby so, essentially, read only. I dont think you
want to be defragging it.
Greg Jackson
PDX, Oregon|||I want to defrag source database. Fregmentation of destination base is not
so important to me.
Tom
"pdxJaxon" <GregoryAJackson@.Hotmail.com> wrote in message
news:%23a$pMckCFHA.1432@.tk2msftngp13.phx.gbl...
> you want to Defrag the "Source" Database or the "Destination" ?
>
> the Destination is a Standby so, essentially, read only. I dont think you
> want to be defragging it.
>
>
> Greg Jackson
> PDX, Oregon
>|||you can use IndexDefrag.
Yes the logging can be fairly extensive and it's a pain to ship the log
activity for index defrags. Defrag indexes regularly so they dont get
massively fragmented. Also monitor fill factor settings etc to reduce
fragmentation.
The database should be available...if not, what is causing it to be blocked
?
your other options are to Reseed the standby server with a full backup after
your defrag jobs.
GAJ|||You should read the whitepaper on fragmentation as it goes into details of
logging. It will also help you determine whether your query workload will
benefit from removing fragmentation regularly.
http://www.microsoft.com/technet/pr...n/ss2kidbp.mspx
Regards
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"pdxJaxon" <GregoryAJackson@.Hotmail.com> wrote in message
news:OVrIHSuCFHA.2676@.TK2MSFTNGP12.phx.gbl...
> you can use IndexDefrag.
> Yes the logging can be fairly extensive and it's a pain to ship the log
> activity for index defrags. Defrag indexes regularly so they dont get
> massively fragmented. Also monitor fill factor settings etc to reduce
> fragmentation.
> The database should be available...if not, what is causing it to be
blocked
> ?
> your other options are to Reseed the standby server with a full backup
after
> your defrag jobs.
>
> GAJ
>
I need to setup log shipping on a DB and to create a job that would reindex
that same DB.
If I use a maintenance plan to reindex a DB that is 30 GB in size, it takes
more than 1 hour and during that time the DB is not accessible for users.
This is NOT OK so I'm planning to use a script that would use dbcc
indexdefrag.
I don't know how that would effect transaction log growth. I suspect that
log would grow very much in full-mode or in bulk-logged mode.
But that means that after i set up log shipping on that database, first log
backup after reindexation will be huge and it will take a lot of time to
transfer it over network to secondary server. During that time SQL would
probably not be accessible or time-outs would accour.
Anyone has any advice on this? Or is there any other way to reindex a
log-shipped database?
thanks
Tomyou want to Defrag the "Source" Database or the "Destination" ?
the Destination is a Standby so, essentially, read only. I dont think you
want to be defragging it.
Greg Jackson
PDX, Oregon|||I want to defrag source database. Fregmentation of destination base is not
so important to me.
Tom
"pdxJaxon" <GregoryAJackson@.Hotmail.com> wrote in message
news:%23a$pMckCFHA.1432@.tk2msftngp13.phx.gbl...
> you want to Defrag the "Source" Database or the "Destination" ?
>
> the Destination is a Standby so, essentially, read only. I dont think you
> want to be defragging it.
>
>
> Greg Jackson
> PDX, Oregon
>|||you can use IndexDefrag.
Yes the logging can be fairly extensive and it's a pain to ship the log
activity for index defrags. Defrag indexes regularly so they dont get
massively fragmented. Also monitor fill factor settings etc to reduce
fragmentation.
The database should be available...if not, what is causing it to be blocked
?
your other options are to Reseed the standby server with a full backup after
your defrag jobs.
GAJ|||You should read the whitepaper on fragmentation as it goes into details of
logging. It will also help you determine whether your query workload will
benefit from removing fragmentation regularly.
http://www.microsoft.com/technet/pr...n/ss2kidbp.mspx
Regards
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"pdxJaxon" <GregoryAJackson@.Hotmail.com> wrote in message
news:OVrIHSuCFHA.2676@.TK2MSFTNGP12.phx.gbl...
> you can use IndexDefrag.
> Yes the logging can be fairly extensive and it's a pain to ship the log
> activity for index defrags. Defrag indexes regularly so they dont get
> massively fragmented. Also monitor fill factor settings etc to reduce
> fragmentation.
> The database should be available...if not, what is causing it to be
blocked
> ?
> your other options are to Reseed the standby server with a full backup
after
> your defrag jobs.
>
> GAJ
>
Reindexing has different behaviour in 2005
I noticed that when I use Maintenace plan wizard to create maintenance plan
to reindex a database, it runs different than it did on SQL2000.
On SQL2000, the job was running about 15 minutes on my 10GB database. During
that time there was a lot of disk activity (loging) and CPU was around 30%.
Now on SQL2005 if I run reindexing maintenance plan on the same 10 GB
database, it first utilizes CPU to maximum (one thread on hyper-threaded
CPU - 3.4GHz Xeon) and during that time there is no disk activity at all.
Profiler tells me that it selects data about database schema. That runs
about 30 minutes and after that actual reindexing begins that also couses a
lot of disk activity and lasts about 15 minutes (same as on SQL2000).
I'm a bit confused about that first 30 minutes when only CPU is doing all
the work. That part was not there in SQL2000. If it takes 30 minutes on 10GB
database, what will happen on 100GB or larger databases? Has anyone some
info if that behaviour has changed in SQL2005?
TomCan you tell us what command(s) the mp is running? You should be able to
script it out.
"Tom" wrote:
> I noticed that when I use Maintenace plan wizard to create maintenance pla
n
> to reindex a database, it runs different than it did on SQL2000.
> On SQL2000, the job was running about 15 minutes on my 10GB database. Duri
ng
> that time there was a lot of disk activity (loging) and CPU was around 30%
.
> Now on SQL2005 if I run reindexing maintenance plan on the same 10 GB
> database, it first utilizes CPU to maximum (one thread on hyper-threaded
> CPU - 3.4GHz Xeon) and during that time there is no disk activity at all.
> Profiler tells me that it selects data about database schema. That runs
> about 30 minutes and after that actual reindexing begins that also couses
a
> lot of disk activity and lasts about 15 minutes (same as on SQL2000).
> I'm a bit confused about that first 30 minutes when only CPU is doing all
> the work. That part was not there in SQL2000. If it takes 30 minutes on 10
GB
> database, what will happen on 100GB or larger databases? Has anyone some
> info if that behaviour has changed in SQL2005?
> Tom
>
>
to reindex a database, it runs different than it did on SQL2000.
On SQL2000, the job was running about 15 minutes on my 10GB database. During
that time there was a lot of disk activity (loging) and CPU was around 30%.
Now on SQL2005 if I run reindexing maintenance plan on the same 10 GB
database, it first utilizes CPU to maximum (one thread on hyper-threaded
CPU - 3.4GHz Xeon) and during that time there is no disk activity at all.
Profiler tells me that it selects data about database schema. That runs
about 30 minutes and after that actual reindexing begins that also couses a
lot of disk activity and lasts about 15 minutes (same as on SQL2000).
I'm a bit confused about that first 30 minutes when only CPU is doing all
the work. That part was not there in SQL2000. If it takes 30 minutes on 10GB
database, what will happen on 100GB or larger databases? Has anyone some
info if that behaviour has changed in SQL2005?
TomCan you tell us what command(s) the mp is running? You should be able to
script it out.
"Tom" wrote:
> I noticed that when I use Maintenace plan wizard to create maintenance pla
n
> to reindex a database, it runs different than it did on SQL2000.
> On SQL2000, the job was running about 15 minutes on my 10GB database. Duri
ng
> that time there was a lot of disk activity (loging) and CPU was around 30%
.
> Now on SQL2005 if I run reindexing maintenance plan on the same 10 GB
> database, it first utilizes CPU to maximum (one thread on hyper-threaded
> CPU - 3.4GHz Xeon) and during that time there is no disk activity at all.
> Profiler tells me that it selects data about database schema. That runs
> about 30 minutes and after that actual reindexing begins that also couses
a
> lot of disk activity and lasts about 15 minutes (same as on SQL2000).
> I'm a bit confused about that first 30 minutes when only CPU is doing all
> the work. That part was not there in SQL2000. If it takes 30 minutes on 10
GB
> database, what will happen on 100GB or larger databases? Has anyone some
> info if that behaviour has changed in SQL2005?
> Tom
>
>
ReIndexing all Tables in a Database
I need to reindex all tables in my database and would like to do this without using a Cursor. What is the simplest way to achieve this.
Cheers
Nat
Hi Nat,
If you didn't want to use a cursor, you could use the sp_MSForEachTable undocumented proc:
exec sp_msforeachtable "dbcc dbreindex('?')"
Cheers,
Rob
|||You could also look at SMO for this operation. I have been working with it and have been very impressed. Here is a link to the database class, it will be a good starting point.
http://msdn2.microsoft.com/en-us/library/microsoft.sqlserver.management.smo.database.aspx
Reindexing 27GB table
On one of the database, I support has 16957722 records in a table and it's
clustered index size is 27606.30.
I used to run the Reindex job created on weekly Sunday starting at 11:00 PM.
It takes more than 7 hours.
Since it is a 24x7 server, when Reindexing job runs it causes blocks so I am
cancelling the job.
In this situation what would you suggest to reindex the table of
such big one ( 27 GB ) ?
Appreciate your advise.
--
Thanks
S SIVAPRASADFirst you need to determine whether you benefit from reindexing. See
[url]http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ss2kidbp.mspx.[/url
]
To check frag level use DBCC SHOWCONTIG (2000) or sys.dm_db_index_physical_s
tats (2005).
In 2000, you have little options of you want to do this "online", your optio
n is DBCC INDEXDEFRAG.
In 2005, you can use either ALTER INDEX with REORGANIZE (same as INDEXDEFRAG
), or use ALTER INDEX
with REBUILD, ONLINE (same as DBREINDEX, except for ONLINE which is new opti
on).
All have pro and cons, one thing to consider is the usage of transaction log
space (which depends on
what type of command you run and can also be influenced by recovery model).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"S Siva Prasad [SIVA]" <SSivaPrasadSIVA@.discussions.microsoft.com> wrote
in message
news:2484E248-2FCD-47F8-BACB-0AB9E71DEAC3@.microsoft.com...
> On one of the database, I support has 16957722 records in a table and it's
> clustered index size is 27606.30.
> I used to run the Reindex job created on weekly Sunday starting at 11:00 P
M.
> It takes more than 7 hours.
> Since it is a 24x7 server, when Reindexing job runs it causes blocks so I
am
> cancelling the job.
> In this situation what would you suggest to reindex the table of
> such big one ( 27 GB ) ?
> Appreciate your advise.
> --
> Thanks
> S SIVAPRASAD|||It is on SQL Server 2000 Enterprise Edition with SP4 (8.00.2040) .
Database is on Full recovery model and it has enough space for Log file
growth.
DBCC SHOWCONITG() ran and rebuilding of the indexes is necessary now.
Let me know what would be the best optimal way of rebuilding the indexes.
The box is on 24x7 and I want to rebuild the index as quick as possible.
The clustered index sized around 27 GB.
--
Thanks
S SIVAPRASAD
"Tibor Karaszi" wrote:
> First you need to determine whether you benefit from reindexing. See
> [url]http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ss2kidbp.mspx.[/u
rl]
> To check frag level use DBCC SHOWCONTIG (2000) or sys.dm_db_index_physical
_stats (2005).
> In 2000, you have little options of you want to do this "online", your opt
ion is DBCC INDEXDEFRAG.
> In 2005, you can use either ALTER INDEX with REORGANIZE (same as INDEXDEFR
AG), or use ALTER INDEX
> with REBUILD, ONLINE (same as DBREINDEX, except for ONLINE which is new op
tion).
> All have pro and cons, one thing to consider is the usage of transaction l
og space (which depends on
> what type of command you run and can also be influenced by recovery model)
.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "S Siva Prasad [SIVA]" <SSivaPrasadSIVA@.discussions.microsoft.com> wro
te in message
> news:2484E248-2FCD-47F8-BACB-0AB9E71DEAC3@.microsoft.com...
>
>|||There's no "optimal way". There are different options, all with advantages a
nd disadvantages. Based
on your requirements, you can weigh the advantages to the disadvantages and
pick the one that suits
your particular need best. Make sure you understand the pros and cons with e
ach method and read the
whitepaper I posted a link to.
Having said that, if the box is 24*7, indexdefrag might be better. But it ca
n take longer time than
dbreindex (but it doesn't block nearly as much), it can produce more log rec
ords, and the
defragmentation might not be as perfect as dbreindex.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"S Siva Prasad [SIVA]" <SSivaPrasadSIVA@.discussions.microsoft.com> wrote
in message
news:8A93A9FA-E357-475D-BB40-8F9BE0A43CCF@.microsoft.com...[vbcol=seagreen]
> It is on SQL Server 2000 Enterprise Edition with SP4 (8.00.2040) .
> Database is on Full recovery model and it has enough space for Log file
> growth.
> DBCC SHOWCONITG() ran and rebuilding of the indexes is necessary now.
> Let me know what would be the best optimal way of rebuilding the indexes.
> The box is on 24x7 and I want to rebuild the index as quick as possible.
> The clustered index sized around 27 GB.
> --
> Thanks
> S SIVAPRASAD
>
> "Tibor Karaszi" wrote:
>
clustered index size is 27606.30.
I used to run the Reindex job created on weekly Sunday starting at 11:00 PM.
It takes more than 7 hours.
Since it is a 24x7 server, when Reindexing job runs it causes blocks so I am
cancelling the job.
In this situation what would you suggest to reindex the table of
such big one ( 27 GB ) ?
Appreciate your advise.
--
Thanks
S SIVAPRASADFirst you need to determine whether you benefit from reindexing. See
[url]http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ss2kidbp.mspx.[/url
]
To check frag level use DBCC SHOWCONTIG (2000) or sys.dm_db_index_physical_s
tats (2005).
In 2000, you have little options of you want to do this "online", your optio
n is DBCC INDEXDEFRAG.
In 2005, you can use either ALTER INDEX with REORGANIZE (same as INDEXDEFRAG
), or use ALTER INDEX
with REBUILD, ONLINE (same as DBREINDEX, except for ONLINE which is new opti
on).
All have pro and cons, one thing to consider is the usage of transaction log
space (which depends on
what type of command you run and can also be influenced by recovery model).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"S Siva Prasad [SIVA]" <SSivaPrasadSIVA@.discussions.microsoft.com> wrote
in message
news:2484E248-2FCD-47F8-BACB-0AB9E71DEAC3@.microsoft.com...
> On one of the database, I support has 16957722 records in a table and it's
> clustered index size is 27606.30.
> I used to run the Reindex job created on weekly Sunday starting at 11:00 P
M.
> It takes more than 7 hours.
> Since it is a 24x7 server, when Reindexing job runs it causes blocks so I
am
> cancelling the job.
> In this situation what would you suggest to reindex the table of
> such big one ( 27 GB ) ?
> Appreciate your advise.
> --
> Thanks
> S SIVAPRASAD|||It is on SQL Server 2000 Enterprise Edition with SP4 (8.00.2040) .
Database is on Full recovery model and it has enough space for Log file
growth.
DBCC SHOWCONITG() ran and rebuilding of the indexes is necessary now.
Let me know what would be the best optimal way of rebuilding the indexes.
The box is on 24x7 and I want to rebuild the index as quick as possible.
The clustered index sized around 27 GB.
--
Thanks
S SIVAPRASAD
"Tibor Karaszi" wrote:
> First you need to determine whether you benefit from reindexing. See
> [url]http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ss2kidbp.mspx.[/u
rl]
> To check frag level use DBCC SHOWCONTIG (2000) or sys.dm_db_index_physical
_stats (2005).
> In 2000, you have little options of you want to do this "online", your opt
ion is DBCC INDEXDEFRAG.
> In 2005, you can use either ALTER INDEX with REORGANIZE (same as INDEXDEFR
AG), or use ALTER INDEX
> with REBUILD, ONLINE (same as DBREINDEX, except for ONLINE which is new op
tion).
> All have pro and cons, one thing to consider is the usage of transaction l
og space (which depends on
> what type of command you run and can also be influenced by recovery model)
.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "S Siva Prasad [SIVA]" <SSivaPrasadSIVA@.discussions.microsoft.com> wro
te in message
> news:2484E248-2FCD-47F8-BACB-0AB9E71DEAC3@.microsoft.com...
>
>|||There's no "optimal way". There are different options, all with advantages a
nd disadvantages. Based
on your requirements, you can weigh the advantages to the disadvantages and
pick the one that suits
your particular need best. Make sure you understand the pros and cons with e
ach method and read the
whitepaper I posted a link to.
Having said that, if the box is 24*7, indexdefrag might be better. But it ca
n take longer time than
dbreindex (but it doesn't block nearly as much), it can produce more log rec
ords, and the
defragmentation might not be as perfect as dbreindex.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"S Siva Prasad [SIVA]" <SSivaPrasadSIVA@.discussions.microsoft.com> wrote
in message
news:8A93A9FA-E357-475D-BB40-8F9BE0A43CCF@.microsoft.com...[vbcol=seagreen]
> It is on SQL Server 2000 Enterprise Edition with SP4 (8.00.2040) .
> Database is on Full recovery model and it has enough space for Log file
> growth.
> DBCC SHOWCONITG() ran and rebuilding of the indexes is necessary now.
> Let me know what would be the best optimal way of rebuilding the indexes.
> The box is on 24x7 and I want to rebuild the index as quick as possible.
> The clustered index sized around 27 GB.
> --
> Thanks
> S SIVAPRASAD
>
> "Tibor Karaszi" wrote:
>
Reindexing 27GB table
On one of the database, I support has 16957722 records in a table and it's
clustered index size is 27606.30.
I used to run the Reindex job created on weekly Sunday starting at 11:00 PM.
It takes more than 7 hours.
Since it is a 24x7 server, when Reindexing job runs it causes blocks so I am
cancelling the job.
In this situation what would you suggest to reindex the table of
such big one ( 27 GB ) ?
Appreciate your advise.
--
Thanks
S SIVAPRASADFirst you need to determine whether you benefit from reindexing. See
http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ss2kidbp.mspx.
To check frag level use DBCC SHOWCONTIG (2000) or sys.dm_db_index_physical_stats (2005).
In 2000, you have little options of you want to do this "online", your option is DBCC INDEXDEFRAG.
In 2005, you can use either ALTER INDEX with REORGANIZE (same as INDEXDEFRAG), or use ALTER INDEX
with REBUILD, ONLINE (same as DBREINDEX, except for ONLINE which is new option).
All have pro and cons, one thing to consider is the usage of transaction log space (which depends on
what type of command you run and can also be influenced by recovery model).
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"S Siva Prasad [SIVA]" <SSivaPrasadSIVA@.discussions.microsoft.com> wrote in message
news:2484E248-2FCD-47F8-BACB-0AB9E71DEAC3@.microsoft.com...
> On one of the database, I support has 16957722 records in a table and it's
> clustered index size is 27606.30.
> I used to run the Reindex job created on weekly Sunday starting at 11:00 PM.
> It takes more than 7 hours.
> Since it is a 24x7 server, when Reindexing job runs it causes blocks so I am
> cancelling the job.
> In this situation what would you suggest to reindex the table of
> such big one ( 27 GB ) ?
> Appreciate your advise.
> --
> Thanks
> S SIVAPRASAD|||It is on SQL Server 2000 Enterprise Edition with SP4 (8.00.2040) .
Database is on Full recovery model and it has enough space for Log file
growth.
DBCC SHOWCONITG() ran and rebuilding of the indexes is necessary now.
Let me know what would be the best optimal way of rebuilding the indexes.
The box is on 24x7 and I want to rebuild the index as quick as possible.
The clustered index sized around 27 GB.
--
Thanks
S SIVAPRASAD
"Tibor Karaszi" wrote:
> First you need to determine whether you benefit from reindexing. See
> http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ss2kidbp.mspx.
> To check frag level use DBCC SHOWCONTIG (2000) or sys.dm_db_index_physical_stats (2005).
> In 2000, you have little options of you want to do this "online", your option is DBCC INDEXDEFRAG.
> In 2005, you can use either ALTER INDEX with REORGANIZE (same as INDEXDEFRAG), or use ALTER INDEX
> with REBUILD, ONLINE (same as DBREINDEX, except for ONLINE which is new option).
> All have pro and cons, one thing to consider is the usage of transaction log space (which depends on
> what type of command you run and can also be influenced by recovery model).
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "S Siva Prasad [SIVA]" <SSivaPrasadSIVA@.discussions.microsoft.com> wrote in message
> news:2484E248-2FCD-47F8-BACB-0AB9E71DEAC3@.microsoft.com...
> > On one of the database, I support has 16957722 records in a table and it's
> > clustered index size is 27606.30.
> >
> > I used to run the Reindex job created on weekly Sunday starting at 11:00 PM.
> > It takes more than 7 hours.
> >
> > Since it is a 24x7 server, when Reindexing job runs it causes blocks so I am
> > cancelling the job.
> >
> > In this situation what would you suggest to reindex the table of
> > such big one ( 27 GB ) ?
> >
> > Appreciate your advise.
> > --
> > Thanks
> > S SIVAPRASAD
>
>|||There's no "optimal way". There are different options, all with advantages and disadvantages. Based
on your requirements, you can weigh the advantages to the disadvantages and pick the one that suits
your particular need best. Make sure you understand the pros and cons with each method and read the
whitepaper I posted a link to.
Having said that, if the box is 24*7, indexdefrag might be better. But it can take longer time than
dbreindex (but it doesn't block nearly as much), it can produce more log records, and the
defragmentation might not be as perfect as dbreindex.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"S Siva Prasad [SIVA]" <SSivaPrasadSIVA@.discussions.microsoft.com> wrote in message
news:8A93A9FA-E357-475D-BB40-8F9BE0A43CCF@.microsoft.com...
> It is on SQL Server 2000 Enterprise Edition with SP4 (8.00.2040) .
> Database is on Full recovery model and it has enough space for Log file
> growth.
> DBCC SHOWCONITG() ran and rebuilding of the indexes is necessary now.
> Let me know what would be the best optimal way of rebuilding the indexes.
> The box is on 24x7 and I want to rebuild the index as quick as possible.
> The clustered index sized around 27 GB.
> --
> Thanks
> S SIVAPRASAD
>
> "Tibor Karaszi" wrote:
>> First you need to determine whether you benefit from reindexing. See
>> http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ss2kidbp.mspx.
>> To check frag level use DBCC SHOWCONTIG (2000) or sys.dm_db_index_physical_stats (2005).
>> In 2000, you have little options of you want to do this "online", your option is DBCC
>> INDEXDEFRAG.
>> In 2005, you can use either ALTER INDEX with REORGANIZE (same as INDEXDEFRAG), or use ALTER INDEX
>> with REBUILD, ONLINE (same as DBREINDEX, except for ONLINE which is new option).
>> All have pro and cons, one thing to consider is the usage of transaction log space (which depends
>> on
>> what type of command you run and can also be influenced by recovery model).
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>>
>> "S Siva Prasad [SIVA]" <SSivaPrasadSIVA@.discussions.microsoft.com> wrote in message
>> news:2484E248-2FCD-47F8-BACB-0AB9E71DEAC3@.microsoft.com...
>> > On one of the database, I support has 16957722 records in a table and it's
>> > clustered index size is 27606.30.
>> >
>> > I used to run the Reindex job created on weekly Sunday starting at 11:00 PM.
>> > It takes more than 7 hours.
>> >
>> > Since it is a 24x7 server, when Reindexing job runs it causes blocks so I am
>> > cancelling the job.
>> >
>> > In this situation what would you suggest to reindex the table of
>> > such big one ( 27 GB ) ?
>> >
>> > Appreciate your advise.
>> > --
>> > Thanks
>> > S SIVAPRASAD
>>
clustered index size is 27606.30.
I used to run the Reindex job created on weekly Sunday starting at 11:00 PM.
It takes more than 7 hours.
Since it is a 24x7 server, when Reindexing job runs it causes blocks so I am
cancelling the job.
In this situation what would you suggest to reindex the table of
such big one ( 27 GB ) ?
Appreciate your advise.
--
Thanks
S SIVAPRASADFirst you need to determine whether you benefit from reindexing. See
http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ss2kidbp.mspx.
To check frag level use DBCC SHOWCONTIG (2000) or sys.dm_db_index_physical_stats (2005).
In 2000, you have little options of you want to do this "online", your option is DBCC INDEXDEFRAG.
In 2005, you can use either ALTER INDEX with REORGANIZE (same as INDEXDEFRAG), or use ALTER INDEX
with REBUILD, ONLINE (same as DBREINDEX, except for ONLINE which is new option).
All have pro and cons, one thing to consider is the usage of transaction log space (which depends on
what type of command you run and can also be influenced by recovery model).
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"S Siva Prasad [SIVA]" <SSivaPrasadSIVA@.discussions.microsoft.com> wrote in message
news:2484E248-2FCD-47F8-BACB-0AB9E71DEAC3@.microsoft.com...
> On one of the database, I support has 16957722 records in a table and it's
> clustered index size is 27606.30.
> I used to run the Reindex job created on weekly Sunday starting at 11:00 PM.
> It takes more than 7 hours.
> Since it is a 24x7 server, when Reindexing job runs it causes blocks so I am
> cancelling the job.
> In this situation what would you suggest to reindex the table of
> such big one ( 27 GB ) ?
> Appreciate your advise.
> --
> Thanks
> S SIVAPRASAD|||It is on SQL Server 2000 Enterprise Edition with SP4 (8.00.2040) .
Database is on Full recovery model and it has enough space for Log file
growth.
DBCC SHOWCONITG() ran and rebuilding of the indexes is necessary now.
Let me know what would be the best optimal way of rebuilding the indexes.
The box is on 24x7 and I want to rebuild the index as quick as possible.
The clustered index sized around 27 GB.
--
Thanks
S SIVAPRASAD
"Tibor Karaszi" wrote:
> First you need to determine whether you benefit from reindexing. See
> http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ss2kidbp.mspx.
> To check frag level use DBCC SHOWCONTIG (2000) or sys.dm_db_index_physical_stats (2005).
> In 2000, you have little options of you want to do this "online", your option is DBCC INDEXDEFRAG.
> In 2005, you can use either ALTER INDEX with REORGANIZE (same as INDEXDEFRAG), or use ALTER INDEX
> with REBUILD, ONLINE (same as DBREINDEX, except for ONLINE which is new option).
> All have pro and cons, one thing to consider is the usage of transaction log space (which depends on
> what type of command you run and can also be influenced by recovery model).
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "S Siva Prasad [SIVA]" <SSivaPrasadSIVA@.discussions.microsoft.com> wrote in message
> news:2484E248-2FCD-47F8-BACB-0AB9E71DEAC3@.microsoft.com...
> > On one of the database, I support has 16957722 records in a table and it's
> > clustered index size is 27606.30.
> >
> > I used to run the Reindex job created on weekly Sunday starting at 11:00 PM.
> > It takes more than 7 hours.
> >
> > Since it is a 24x7 server, when Reindexing job runs it causes blocks so I am
> > cancelling the job.
> >
> > In this situation what would you suggest to reindex the table of
> > such big one ( 27 GB ) ?
> >
> > Appreciate your advise.
> > --
> > Thanks
> > S SIVAPRASAD
>
>|||There's no "optimal way". There are different options, all with advantages and disadvantages. Based
on your requirements, you can weigh the advantages to the disadvantages and pick the one that suits
your particular need best. Make sure you understand the pros and cons with each method and read the
whitepaper I posted a link to.
Having said that, if the box is 24*7, indexdefrag might be better. But it can take longer time than
dbreindex (but it doesn't block nearly as much), it can produce more log records, and the
defragmentation might not be as perfect as dbreindex.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"S Siva Prasad [SIVA]" <SSivaPrasadSIVA@.discussions.microsoft.com> wrote in message
news:8A93A9FA-E357-475D-BB40-8F9BE0A43CCF@.microsoft.com...
> It is on SQL Server 2000 Enterprise Edition with SP4 (8.00.2040) .
> Database is on Full recovery model and it has enough space for Log file
> growth.
> DBCC SHOWCONITG() ran and rebuilding of the indexes is necessary now.
> Let me know what would be the best optimal way of rebuilding the indexes.
> The box is on 24x7 and I want to rebuild the index as quick as possible.
> The clustered index sized around 27 GB.
> --
> Thanks
> S SIVAPRASAD
>
> "Tibor Karaszi" wrote:
>> First you need to determine whether you benefit from reindexing. See
>> http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ss2kidbp.mspx.
>> To check frag level use DBCC SHOWCONTIG (2000) or sys.dm_db_index_physical_stats (2005).
>> In 2000, you have little options of you want to do this "online", your option is DBCC
>> INDEXDEFRAG.
>> In 2005, you can use either ALTER INDEX with REORGANIZE (same as INDEXDEFRAG), or use ALTER INDEX
>> with REBUILD, ONLINE (same as DBREINDEX, except for ONLINE which is new option).
>> All have pro and cons, one thing to consider is the usage of transaction log space (which depends
>> on
>> what type of command you run and can also be influenced by recovery model).
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>>
>> "S Siva Prasad [SIVA]" <SSivaPrasadSIVA@.discussions.microsoft.com> wrote in message
>> news:2484E248-2FCD-47F8-BACB-0AB9E71DEAC3@.microsoft.com...
>> > On one of the database, I support has 16957722 records in a table and it's
>> > clustered index size is 27606.30.
>> >
>> > I used to run the Reindex job created on weekly Sunday starting at 11:00 PM.
>> > It takes more than 7 hours.
>> >
>> > Since it is a 24x7 server, when Reindexing job runs it causes blocks so I am
>> > cancelling the job.
>> >
>> > In this situation what would you suggest to reindex the table of
>> > such big one ( 27 GB ) ?
>> >
>> > Appreciate your advise.
>> > --
>> > Thanks
>> > S SIVAPRASAD
>>
Reindexation
I have a very large database, in wich the indexes are constantly "getting lost". I execute DBCC Reindex and everything goes back to normal. Still, I don't know why this happens. I have to run this process once or twice a week, sometimes as a scheduled task, some others manually.
Why are the indexes 'getting lost'?
What could be wrong with the database?
Thanks in advance
Can you elaborate on what you mean by "indexes are getting lost"...are you saying the index no longer exists? That cannot be the case because dbcc reindex implies you have an index that is being reindexed.
Thx
Ajay
|||I'm deeply sorry for not being able to put this in other way. It's just that i do not understand what is happening, even less how to ask it or explain it... someone told me that "the indexes get corrupted". Still, I don't know what this means, or why...
Thans again
Eduardo
|||You might find some help with the DBCC commands. They will help you with physical and logical errors. Here is a good link for an introduction to those:
http://www.informit.com/guides/content.asp?g=sqlserver&seqNum=30&rl=1
And here is a more in-depth explanation of the DBCC CHECKDB command:
http://msdn2.microsoft.com/en-us/library/aa258278(SQL.80).aspx
ReIndex with fillfactor of 100
Our results of reindexing with a fillfactor of 100% for a read only
database are surprising:
Here is before using a fillfactor of 100%, the previous fillfactor was
90% and had some inserts and updates since the last reindex:
DBCC SHOWCONTIG scanning 'RNUMBER' table...
Table: 'RNUMBER' (2065754762); index ID: 1, database ID: 10
TABLE level scan performed.
- Pages Scanned........................: 301013
- Extents Scanned.......................: 37859
- Extent Switches.......................: 40777
- Avg. Pages per Extent..................: 8.0
- Scan Density [Best Count:Actual Count]......: 92.27% [37627:40778]
- Logical Scan Fragmentation ..............: 0.74%
- Extent Scan Fragmentation ...............: 6.30%
- Avg. Bytes Free per Page................: 784.6
- Avg. Page Density (full)................: 90.31%
Here are the results after a reindex with fillfactor of 100%:
DBCC SHOWCONTIG scanning 'RNUMBER' table...
Table: 'RNUMBER' (2065754762); index ID: 1, database ID: 10
TABLE level scan performed.
- Pages Scanned........................: 299768
- Extents Scanned.......................: 37696
- Extent Switches.......................: 52261
- Avg. Pages per Extent..................: 8.0
- Scan Density [Best Count:Actual Count]......: 71.70% [37471:52262]
- Logical Scan Fragmentation ..............: 0.00%
- Extent Scan Fragmentation ...............: 6.03%
- Avg. Bytes Free per Page................: 754.3
- Avg. Page Density (full)................: 90.68%
The results show that logical scan fragmentation has been removed, but
extent scan fragmentation has only been slightly lowered. In some
other test indexes, the extent scan fragmentation actually increased.
Scan density went down significantly and avg page density barely
increased. Once again with other indexes, we are seeing avg page
density also decreasing.
Finally, extent switches went up significantly. while the pages scanned
decreased.
I would assume that a fillfactor of 100% should condense the index as
much as possible, optimizing reads to a read only database. But our
testing seem to show different results.
Can anyone shed some light on this for us?
Thank You
Ooops, the stored proc I was executing the INDEXDEFRAG logic, not the
REINDEX. I fixed the stored proc.
All is better now, but I am still seeing extent scan fragmentation
around 3-5% even with the REINDEX.
|||A reindex is not guaranteed to remove 100% of the fragmentation, especially
if you did not have LOTS of free contiguous space in your data file before
you reindexed. Do you have more than 1 data file in that filegroup? If so
you should only go by the Logical Fragmentation as some of the rest are
invalid for multiple files.
Andrew J. Kelly SQL MVP
"kghammond" <kghammond@.nrscorp.com> wrote in message
news:1140798715.538612.221680@.u72g2000cwu.googlegr oups.com...
> Ooops, the stored proc I was executing the INDEXDEFRAG logic, not the
> REINDEX. I fixed the stored proc.
> All is better now, but I am still seeing extent scan fragmentation
> around 3-5% even with the REINDEX.
>
database are surprising:
Here is before using a fillfactor of 100%, the previous fillfactor was
90% and had some inserts and updates since the last reindex:
DBCC SHOWCONTIG scanning 'RNUMBER' table...
Table: 'RNUMBER' (2065754762); index ID: 1, database ID: 10
TABLE level scan performed.
- Pages Scanned........................: 301013
- Extents Scanned.......................: 37859
- Extent Switches.......................: 40777
- Avg. Pages per Extent..................: 8.0
- Scan Density [Best Count:Actual Count]......: 92.27% [37627:40778]
- Logical Scan Fragmentation ..............: 0.74%
- Extent Scan Fragmentation ...............: 6.30%
- Avg. Bytes Free per Page................: 784.6
- Avg. Page Density (full)................: 90.31%
Here are the results after a reindex with fillfactor of 100%:
DBCC SHOWCONTIG scanning 'RNUMBER' table...
Table: 'RNUMBER' (2065754762); index ID: 1, database ID: 10
TABLE level scan performed.
- Pages Scanned........................: 299768
- Extents Scanned.......................: 37696
- Extent Switches.......................: 52261
- Avg. Pages per Extent..................: 8.0
- Scan Density [Best Count:Actual Count]......: 71.70% [37471:52262]
- Logical Scan Fragmentation ..............: 0.00%
- Extent Scan Fragmentation ...............: 6.03%
- Avg. Bytes Free per Page................: 754.3
- Avg. Page Density (full)................: 90.68%
The results show that logical scan fragmentation has been removed, but
extent scan fragmentation has only been slightly lowered. In some
other test indexes, the extent scan fragmentation actually increased.
Scan density went down significantly and avg page density barely
increased. Once again with other indexes, we are seeing avg page
density also decreasing.
Finally, extent switches went up significantly. while the pages scanned
decreased.
I would assume that a fillfactor of 100% should condense the index as
much as possible, optimizing reads to a read only database. But our
testing seem to show different results.
Can anyone shed some light on this for us?
Thank You
Ooops, the stored proc I was executing the INDEXDEFRAG logic, not the
REINDEX. I fixed the stored proc.
All is better now, but I am still seeing extent scan fragmentation
around 3-5% even with the REINDEX.
|||A reindex is not guaranteed to remove 100% of the fragmentation, especially
if you did not have LOTS of free contiguous space in your data file before
you reindexed. Do you have more than 1 data file in that filegroup? If so
you should only go by the Logical Fragmentation as some of the rest are
invalid for multiple files.
Andrew J. Kelly SQL MVP
"kghammond" <kghammond@.nrscorp.com> wrote in message
news:1140798715.538612.221680@.u72g2000cwu.googlegr oups.com...
> Ooops, the stored proc I was executing the INDEXDEFRAG logic, not the
> REINDEX. I fixed the stored proc.
> All is better now, but I am still seeing extent scan fragmentation
> around 3-5% even with the REINDEX.
>
ReIndex with fillfactor of 100
Our results of reindexing with a fillfactor of 100% for a read only
database are surprising:
Here is before using a fillfactor of 100%, the previous fillfactor was
90% and had some inserts and updates since the last reindex:
DBCC SHOWCONTIG scanning 'RNUMBER' table...
Table: 'RNUMBER' (2065754762); index ID: 1, database ID: 10
TABLE level scan performed.
- Pages Scanned........................: 301013
- Extents Scanned.......................: 37859
- Extent Switches.......................: 40777
- Avg. Pages per Extent..................: 8.0
- Scan Density [Best Count:Actual Count]......: 92.27% [37627:40778
]
- Logical Scan Fragmentation ..............: 0.74%
- Extent Scan Fragmentation ...............: 6.30%
- Avg. Bytes Free per Page................: 784.6
- Avg. Page Density (full)................: 90.31%
Here are the results after a reindex with fillfactor of 100%:
DBCC SHOWCONTIG scanning 'RNUMBER' table...
Table: 'RNUMBER' (2065754762); index ID: 1, database ID: 10
TABLE level scan performed.
- Pages Scanned........................: 299768
- Extents Scanned.......................: 37696
- Extent Switches.......................: 52261
- Avg. Pages per Extent..................: 8.0
- Scan Density [Best Count:Actual Count]......: 71.70% [37471:52262
]
- Logical Scan Fragmentation ..............: 0.00%
- Extent Scan Fragmentation ...............: 6.03%
- Avg. Bytes Free per Page................: 754.3
- Avg. Page Density (full)................: 90.68%
The results show that logical scan fragmentation has been removed, but
extent scan fragmentation has only been slightly lowered. In some
other test indexes, the extent scan fragmentation actually increased.
Scan density went down significantly and avg page density barely
increased. Once again with other indexes, we are seeing avg page
density also decreasing.
Finally, extent switches went up significantly. while the pages scanned
decreased.
I would assume that a fillfactor of 100% should condense the index as
much as possible, optimizing reads to a read only database. But our
testing seem to show different results.
Can anyone shed some light on this for us?
Thank YouOoops, the stored proc I was executing the INDEXDEFRAG logic, not the
REINDEX. I fixed the stored proc.
All is better now, but I am still seeing extent scan fragmentation
around 3-5% even with the REINDEX.|||A reindex is not guaranteed to remove 100% of the fragmentation, especially
if you did not have LOTS of free contiguous space in your data file before
you reindexed. Do you have more than 1 data file in that filegroup? If so
you should only go by the Logical Fragmentation as some of the rest are
invalid for multiple files.
Andrew J. Kelly SQL MVP
"kghammond" <kghammond@.nrscorp.com> wrote in message
news:1140798715.538612.221680@.u72g2000cwu.googlegroups.com...
> Ooops, the stored proc I was executing the INDEXDEFRAG logic, not the
> REINDEX. I fixed the stored proc.
> All is better now, but I am still seeing extent scan fragmentation
> around 3-5% even with the REINDEX.
>
database are surprising:
Here is before using a fillfactor of 100%, the previous fillfactor was
90% and had some inserts and updates since the last reindex:
DBCC SHOWCONTIG scanning 'RNUMBER' table...
Table: 'RNUMBER' (2065754762); index ID: 1, database ID: 10
TABLE level scan performed.
- Pages Scanned........................: 301013
- Extents Scanned.......................: 37859
- Extent Switches.......................: 40777
- Avg. Pages per Extent..................: 8.0
- Scan Density [Best Count:Actual Count]......: 92.27% [37627:40778
]
- Logical Scan Fragmentation ..............: 0.74%
- Extent Scan Fragmentation ...............: 6.30%
- Avg. Bytes Free per Page................: 784.6
- Avg. Page Density (full)................: 90.31%
Here are the results after a reindex with fillfactor of 100%:
DBCC SHOWCONTIG scanning 'RNUMBER' table...
Table: 'RNUMBER' (2065754762); index ID: 1, database ID: 10
TABLE level scan performed.
- Pages Scanned........................: 299768
- Extents Scanned.......................: 37696
- Extent Switches.......................: 52261
- Avg. Pages per Extent..................: 8.0
- Scan Density [Best Count:Actual Count]......: 71.70% [37471:52262
]
- Logical Scan Fragmentation ..............: 0.00%
- Extent Scan Fragmentation ...............: 6.03%
- Avg. Bytes Free per Page................: 754.3
- Avg. Page Density (full)................: 90.68%
The results show that logical scan fragmentation has been removed, but
extent scan fragmentation has only been slightly lowered. In some
other test indexes, the extent scan fragmentation actually increased.
Scan density went down significantly and avg page density barely
increased. Once again with other indexes, we are seeing avg page
density also decreasing.
Finally, extent switches went up significantly. while the pages scanned
decreased.
I would assume that a fillfactor of 100% should condense the index as
much as possible, optimizing reads to a read only database. But our
testing seem to show different results.
Can anyone shed some light on this for us?
Thank YouOoops, the stored proc I was executing the INDEXDEFRAG logic, not the
REINDEX. I fixed the stored proc.
All is better now, but I am still seeing extent scan fragmentation
around 3-5% even with the REINDEX.|||A reindex is not guaranteed to remove 100% of the fragmentation, especially
if you did not have LOTS of free contiguous space in your data file before
you reindexed. Do you have more than 1 data file in that filegroup? If so
you should only go by the Logical Fragmentation as some of the rest are
invalid for multiple files.
Andrew J. Kelly SQL MVP
"kghammond" <kghammond@.nrscorp.com> wrote in message
news:1140798715.538612.221680@.u72g2000cwu.googlegroups.com...
> Ooops, the stored proc I was executing the INDEXDEFRAG logic, not the
> REINDEX. I fixed the stored proc.
> All is better now, but I am still seeing extent scan fragmentation
> around 3-5% even with the REINDEX.
>
ReIndex with fillfactor of 100
Our results of reindexing with a fillfactor of 100% for a read only
database are surprising:
Here is before using a fillfactor of 100%, the previous fillfactor was
90% and had some inserts and updates since the last reindex:
DBCC SHOWCONTIG scanning 'RNUMBER' table...
Table: 'RNUMBER' (2065754762); index ID: 1, database ID: 10
TABLE level scan performed.
- Pages Scanned........................: 301013
- Extents Scanned.......................: 37859
- Extent Switches.......................: 40777
- Avg. Pages per Extent..................: 8.0
- Scan Density [Best Count:Actual Count]......: 92.27% [37627:40778]
- Logical Scan Fragmentation ..............: 0.74%
- Extent Scan Fragmentation ...............: 6.30%
- Avg. Bytes Free per Page................: 784.6
- Avg. Page Density (full)................: 90.31%
Here are the results after a reindex with fillfactor of 100%:
DBCC SHOWCONTIG scanning 'RNUMBER' table...
Table: 'RNUMBER' (2065754762); index ID: 1, database ID: 10
TABLE level scan performed.
- Pages Scanned........................: 299768
- Extents Scanned.......................: 37696
- Extent Switches.......................: 52261
- Avg. Pages per Extent..................: 8.0
- Scan Density [Best Count:Actual Count]......: 71.70% [37471:52262]
- Logical Scan Fragmentation ..............: 0.00%
- Extent Scan Fragmentation ...............: 6.03%
- Avg. Bytes Free per Page................: 754.3
- Avg. Page Density (full)................: 90.68%
The results show that logical scan fragmentation has been removed, but
extent scan fragmentation has only been slightly lowered. In some
other test indexes, the extent scan fragmentation actually increased.
Scan density went down significantly and avg page density barely
increased. Once again with other indexes, we are seeing avg page
density also decreasing.
Finally, extent switches went up significantly. while the pages scanned
decreased.
I would assume that a fillfactor of 100% should condense the index as
much as possible, optimizing reads to a read only database. But our
testing seem to show different results.
Can anyone shed some light on this for us?
Thank YouOoops, the stored proc I was executing the INDEXDEFRAG logic, not the
REINDEX. I fixed the stored proc.
All is better now, but I am still seeing extent scan fragmentation
around 3-5% even with the REINDEX.|||A reindex is not guaranteed to remove 100% of the fragmentation, especially
if you did not have LOTS of free contiguous space in your data file before
you reindexed. Do you have more than 1 data file in that filegroup? If so
you should only go by the Logical Fragmentation as some of the rest are
invalid for multiple files.
--
Andrew J. Kelly SQL MVP
"kghammond" <kghammond@.nrscorp.com> wrote in message
news:1140798715.538612.221680@.u72g2000cwu.googlegroups.com...
> Ooops, the stored proc I was executing the INDEXDEFRAG logic, not the
> REINDEX. I fixed the stored proc.
> All is better now, but I am still seeing extent scan fragmentation
> around 3-5% even with the REINDEX.
>
database are surprising:
Here is before using a fillfactor of 100%, the previous fillfactor was
90% and had some inserts and updates since the last reindex:
DBCC SHOWCONTIG scanning 'RNUMBER' table...
Table: 'RNUMBER' (2065754762); index ID: 1, database ID: 10
TABLE level scan performed.
- Pages Scanned........................: 301013
- Extents Scanned.......................: 37859
- Extent Switches.......................: 40777
- Avg. Pages per Extent..................: 8.0
- Scan Density [Best Count:Actual Count]......: 92.27% [37627:40778]
- Logical Scan Fragmentation ..............: 0.74%
- Extent Scan Fragmentation ...............: 6.30%
- Avg. Bytes Free per Page................: 784.6
- Avg. Page Density (full)................: 90.31%
Here are the results after a reindex with fillfactor of 100%:
DBCC SHOWCONTIG scanning 'RNUMBER' table...
Table: 'RNUMBER' (2065754762); index ID: 1, database ID: 10
TABLE level scan performed.
- Pages Scanned........................: 299768
- Extents Scanned.......................: 37696
- Extent Switches.......................: 52261
- Avg. Pages per Extent..................: 8.0
- Scan Density [Best Count:Actual Count]......: 71.70% [37471:52262]
- Logical Scan Fragmentation ..............: 0.00%
- Extent Scan Fragmentation ...............: 6.03%
- Avg. Bytes Free per Page................: 754.3
- Avg. Page Density (full)................: 90.68%
The results show that logical scan fragmentation has been removed, but
extent scan fragmentation has only been slightly lowered. In some
other test indexes, the extent scan fragmentation actually increased.
Scan density went down significantly and avg page density barely
increased. Once again with other indexes, we are seeing avg page
density also decreasing.
Finally, extent switches went up significantly. while the pages scanned
decreased.
I would assume that a fillfactor of 100% should condense the index as
much as possible, optimizing reads to a read only database. But our
testing seem to show different results.
Can anyone shed some light on this for us?
Thank YouOoops, the stored proc I was executing the INDEXDEFRAG logic, not the
REINDEX. I fixed the stored proc.
All is better now, but I am still seeing extent scan fragmentation
around 3-5% even with the REINDEX.|||A reindex is not guaranteed to remove 100% of the fragmentation, especially
if you did not have LOTS of free contiguous space in your data file before
you reindexed. Do you have more than 1 data file in that filegroup? If so
you should only go by the Logical Fragmentation as some of the rest are
invalid for multiple files.
--
Andrew J. Kelly SQL MVP
"kghammond" <kghammond@.nrscorp.com> wrote in message
news:1140798715.538612.221680@.u72g2000cwu.googlegroups.com...
> Ooops, the stored proc I was executing the INDEXDEFRAG logic, not the
> REINDEX. I fixed the stored proc.
> All is better now, but I am still seeing extent scan fragmentation
> around 3-5% even with the REINDEX.
>
reindex versus maintenance plan
Hi,
I would like to reindex all the tables in my database by using a job
instead of a maintenance plan. In a maintenance plan this is possible with
the checkbox: "reorganize data and index pages". How can I do the same thing
in a script without using DDBC REINDEX for every table?Can use sp_msForEachTable system procedure for the same. Like
EXEC sp_msForEachTable 'DBCC DBREINDEX (''?'')'
--
HTH,
Vinod Kumar
MCSE, DBA, MCAD, MCSD
http://www.extremeexperts.com
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp
"Jo Segers" <segers_jo@.hotmail.com> wrote in message
news:%23pu65zeIEHA.1944@.TK2MSFTNGP11.phx.gbl...
> Hi,
> I would like to reindex all the tables in my database by using a job
> instead of a maintenance plan. In a maintenance plan this is possible with
> the checkbox: "reorganize data and index pages". How can I do the same
thing
> in a script without using DDBC REINDEX for every table?
>
>|||Thanks,
This solved my problem.
"Vinodk" <vinodk_sct@.NO_SPAM_hotmail.com> schreef in bericht
news:ePhnW9eIEHA.3376@.TK2MSFTNGP09.phx.gbl...
> Can use sp_msForEachTable system procedure for the same. Like
> EXEC sp_msForEachTable 'DBCC DBREINDEX (''?'')'
> --
> HTH,
> Vinod Kumar
> MCSE, DBA, MCAD, MCSD
> http://www.extremeexperts.com
> Books Online for SQL Server SP3 at
> http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp
>
> "Jo Segers" <segers_jo@.hotmail.com> wrote in message
> news:%23pu65zeIEHA.1944@.TK2MSFTNGP11.phx.gbl...
> > Hi,
> >
> > I would like to reindex all the tables in my database by using a job
> > instead of a maintenance plan. In a maintenance plan this is possible
with
> > the checkbox: "reorganize data and index pages". How can I do the same
> thing
> > in a script without using DDBC REINDEX for every table?
> >
> >
> >
>|||Here is what I use to dbcc indexDefrag all indexes each night:
--***********************************************************************
DECLARE @.Table sysname
DECLARE @.Indid Int
DECLARE cur_tblFetch CURSOR FOR
SELECT Table_Name from information_Schema.tables where table_type = 'base
table'
OPEN cur_tblFetch
FETCH NEXT From cur_tblFetch INTO @.Table
While @.@.FETCH_STATUS = 0
BEGIN
DECLARE cur_indFetch CURSOR FOR
SELECT indid FROM SysIndexes WHERE id = Object_ID(@.Table) AND keycnt > 0
OPEN cur_indFetch
FETCH NEXT FROM cur_indFetch INTO @.Indid
WHILE @.@.FETCH_STATUS = 0
BEGIN
IF @.Indid <> 255
BEGIN
DBCC INDEXDEFRAG(Creditnet,@.Table,@.Indid) WITH NO_INFOMSGS
END
FETCH NEXT FROM cur_indFetch INTO @.Indid
END
CLOSE cur_IndFetch
DEALLOCATE cur_IndFetch
FETCH NEXT FROM cur_tblFetch INTO @.Table
END
CLOSE cur_tblFetch
DEALLOCATE cur_tblFetch
EXEC sp_updatestats
--***********************************************************************
cheers,
Greg Jackson
PDX, Oregon
I would like to reindex all the tables in my database by using a job
instead of a maintenance plan. In a maintenance plan this is possible with
the checkbox: "reorganize data and index pages". How can I do the same thing
in a script without using DDBC REINDEX for every table?Can use sp_msForEachTable system procedure for the same. Like
EXEC sp_msForEachTable 'DBCC DBREINDEX (''?'')'
--
HTH,
Vinod Kumar
MCSE, DBA, MCAD, MCSD
http://www.extremeexperts.com
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp
"Jo Segers" <segers_jo@.hotmail.com> wrote in message
news:%23pu65zeIEHA.1944@.TK2MSFTNGP11.phx.gbl...
> Hi,
> I would like to reindex all the tables in my database by using a job
> instead of a maintenance plan. In a maintenance plan this is possible with
> the checkbox: "reorganize data and index pages". How can I do the same
thing
> in a script without using DDBC REINDEX for every table?
>
>|||Thanks,
This solved my problem.
"Vinodk" <vinodk_sct@.NO_SPAM_hotmail.com> schreef in bericht
news:ePhnW9eIEHA.3376@.TK2MSFTNGP09.phx.gbl...
> Can use sp_msForEachTable system procedure for the same. Like
> EXEC sp_msForEachTable 'DBCC DBREINDEX (''?'')'
> --
> HTH,
> Vinod Kumar
> MCSE, DBA, MCAD, MCSD
> http://www.extremeexperts.com
> Books Online for SQL Server SP3 at
> http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp
>
> "Jo Segers" <segers_jo@.hotmail.com> wrote in message
> news:%23pu65zeIEHA.1944@.TK2MSFTNGP11.phx.gbl...
> > Hi,
> >
> > I would like to reindex all the tables in my database by using a job
> > instead of a maintenance plan. In a maintenance plan this is possible
with
> > the checkbox: "reorganize data and index pages". How can I do the same
> thing
> > in a script without using DDBC REINDEX for every table?
> >
> >
> >
>|||Here is what I use to dbcc indexDefrag all indexes each night:
--***********************************************************************
DECLARE @.Table sysname
DECLARE @.Indid Int
DECLARE cur_tblFetch CURSOR FOR
SELECT Table_Name from information_Schema.tables where table_type = 'base
table'
OPEN cur_tblFetch
FETCH NEXT From cur_tblFetch INTO @.Table
While @.@.FETCH_STATUS = 0
BEGIN
DECLARE cur_indFetch CURSOR FOR
SELECT indid FROM SysIndexes WHERE id = Object_ID(@.Table) AND keycnt > 0
OPEN cur_indFetch
FETCH NEXT FROM cur_indFetch INTO @.Indid
WHILE @.@.FETCH_STATUS = 0
BEGIN
IF @.Indid <> 255
BEGIN
DBCC INDEXDEFRAG(Creditnet,@.Table,@.Indid) WITH NO_INFOMSGS
END
FETCH NEXT FROM cur_indFetch INTO @.Indid
END
CLOSE cur_IndFetch
DEALLOCATE cur_IndFetch
FETCH NEXT FROM cur_tblFetch INTO @.Table
END
CLOSE cur_tblFetch
DEALLOCATE cur_tblFetch
EXEC sp_updatestats
--***********************************************************************
cheers,
Greg Jackson
PDX, Oregon
reindex versus maintenance plan
Hi,
I would like to reindex all the tables in my database by using a job
instead of a maintenance plan. In a maintenance plan this is possible with
the checkbox: "reorganize data and index pages". How can I do the same thing
in a script without using DDBC REINDEX for every table?
Can use sp_msForEachTable system procedure for the same. Like
EXEC sp_msForEachTable 'DBCC DBREINDEX (''?'')'
HTH,
Vinod Kumar
MCSE, DBA, MCAD, MCSD
http://www.extremeexperts.com
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
"Jo Segers" <segers_jo@.hotmail.com> wrote in message
news:%23pu65zeIEHA.1944@.TK2MSFTNGP11.phx.gbl...
> Hi,
> I would like to reindex all the tables in my database by using a job
> instead of a maintenance plan. In a maintenance plan this is possible with
> the checkbox: "reorganize data and index pages". How can I do the same
thing
> in a script without using DDBC REINDEX for every table?
>
>
|||Thanks,
This solved my problem.
"Vinodk" <vinodk_sct@.NO_SPAM_hotmail.com> schreef in bericht
news:ePhnW9eIEHA.3376@.TK2MSFTNGP09.phx.gbl...[vbcol=seagreen]
> Can use sp_msForEachTable system procedure for the same. Like
> EXEC sp_msForEachTable 'DBCC DBREINDEX (''?'')'
> --
> HTH,
> Vinod Kumar
> MCSE, DBA, MCAD, MCSD
> http://www.extremeexperts.com
> Books Online for SQL Server SP3 at
> http://www.microsoft.com/sql/techinf...2000/books.asp
>
> "Jo Segers" <segers_jo@.hotmail.com> wrote in message
> news:%23pu65zeIEHA.1944@.TK2MSFTNGP11.phx.gbl...
with
> thing
>
|||Here is what I use to dbcc indexDefrag all indexes each night:
--************************************************** *********************
DECLARE @.Table sysname
DECLARE @.Indid Int
DECLARE cur_tblFetch CURSOR FOR
SELECT Table_Name from information_Schema.tables where table_type = 'base
table'
OPEN cur_tblFetch
FETCH NEXT From cur_tblFetch INTO @.Table
While @.@.FETCH_STATUS = 0
BEGIN
DECLARE cur_indFetch CURSOR FOR
SELECT indid FROM SysIndexes WHERE id = Object_ID(@.Table) AND keycnt > 0
OPEN cur_indFetch
FETCH NEXT FROM cur_indFetch INTO @.Indid
WHILE @.@.FETCH_STATUS = 0
BEGIN
IF @.Indid <> 255
BEGIN
DBCC INDEXDEFRAG(Creditnet,@.Table,@.Indid) WITH NO_INFOMSGS
END
FETCH NEXT FROM cur_indFetch INTO @.Indid
END
CLOSE cur_IndFetch
DEALLOCATE cur_IndFetch
FETCH NEXT FROM cur_tblFetch INTO @.Table
END
CLOSE cur_tblFetch
DEALLOCATE cur_tblFetch
EXEC sp_updatestats
--************************************************** *********************
cheers,
Greg Jackson
PDX, Oregon
|||I have been looking for a resolution to this as well. Thank you for this information! I have another question - how do I write this if I need to do a fill factor of 90?
-- Vinodk wrote: --
Can use sp_msForEachTable system procedure for the same. Like
EXEC sp_msForEachTable 'DBCC DBREINDEX (''?'')'
HTH,
Vinod Kumar
MCSE, DBA, MCAD, MCSD
http://www.extremeexperts.com
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
"Jo Segers" <segers_jo@.hotmail.com> wrote in message
news:%23pu65zeIEHA.1944@.TK2MSFTNGP11.phx.gbl...
> Hi,
> instead of a maintenance plan. In a maintenance plan this is possible with
> the checkbox: "reorganize data and index pages". How can I do the same
thing[vbcol=seagreen]
> in a script without using DDBC REINDEX for every table?
I would like to reindex all the tables in my database by using a job
instead of a maintenance plan. In a maintenance plan this is possible with
the checkbox: "reorganize data and index pages". How can I do the same thing
in a script without using DDBC REINDEX for every table?
Can use sp_msForEachTable system procedure for the same. Like
EXEC sp_msForEachTable 'DBCC DBREINDEX (''?'')'
HTH,
Vinod Kumar
MCSE, DBA, MCAD, MCSD
http://www.extremeexperts.com
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
"Jo Segers" <segers_jo@.hotmail.com> wrote in message
news:%23pu65zeIEHA.1944@.TK2MSFTNGP11.phx.gbl...
> Hi,
> I would like to reindex all the tables in my database by using a job
> instead of a maintenance plan. In a maintenance plan this is possible with
> the checkbox: "reorganize data and index pages". How can I do the same
thing
> in a script without using DDBC REINDEX for every table?
>
>
|||Thanks,
This solved my problem.
"Vinodk" <vinodk_sct@.NO_SPAM_hotmail.com> schreef in bericht
news:ePhnW9eIEHA.3376@.TK2MSFTNGP09.phx.gbl...[vbcol=seagreen]
> Can use sp_msForEachTable system procedure for the same. Like
> EXEC sp_msForEachTable 'DBCC DBREINDEX (''?'')'
> --
> HTH,
> Vinod Kumar
> MCSE, DBA, MCAD, MCSD
> http://www.extremeexperts.com
> Books Online for SQL Server SP3 at
> http://www.microsoft.com/sql/techinf...2000/books.asp
>
> "Jo Segers" <segers_jo@.hotmail.com> wrote in message
> news:%23pu65zeIEHA.1944@.TK2MSFTNGP11.phx.gbl...
with
> thing
>
|||Here is what I use to dbcc indexDefrag all indexes each night:
--************************************************** *********************
DECLARE @.Table sysname
DECLARE @.Indid Int
DECLARE cur_tblFetch CURSOR FOR
SELECT Table_Name from information_Schema.tables where table_type = 'base
table'
OPEN cur_tblFetch
FETCH NEXT From cur_tblFetch INTO @.Table
While @.@.FETCH_STATUS = 0
BEGIN
DECLARE cur_indFetch CURSOR FOR
SELECT indid FROM SysIndexes WHERE id = Object_ID(@.Table) AND keycnt > 0
OPEN cur_indFetch
FETCH NEXT FROM cur_indFetch INTO @.Indid
WHILE @.@.FETCH_STATUS = 0
BEGIN
IF @.Indid <> 255
BEGIN
DBCC INDEXDEFRAG(Creditnet,@.Table,@.Indid) WITH NO_INFOMSGS
END
FETCH NEXT FROM cur_indFetch INTO @.Indid
END
CLOSE cur_IndFetch
DEALLOCATE cur_IndFetch
FETCH NEXT FROM cur_tblFetch INTO @.Table
END
CLOSE cur_tblFetch
DEALLOCATE cur_tblFetch
EXEC sp_updatestats
--************************************************** *********************
cheers,
Greg Jackson
PDX, Oregon
|||I have been looking for a resolution to this as well. Thank you for this information! I have another question - how do I write this if I need to do a fill factor of 90?
-- Vinodk wrote: --
Can use sp_msForEachTable system procedure for the same. Like
EXEC sp_msForEachTable 'DBCC DBREINDEX (''?'')'
HTH,
Vinod Kumar
MCSE, DBA, MCAD, MCSD
http://www.extremeexperts.com
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
"Jo Segers" <segers_jo@.hotmail.com> wrote in message
news:%23pu65zeIEHA.1944@.TK2MSFTNGP11.phx.gbl...
> Hi,
> instead of a maintenance plan. In a maintenance plan this is possible with
> the checkbox: "reorganize data and index pages". How can I do the same
thing[vbcol=seagreen]
> in a script without using DDBC REINDEX for every table?
reindex versus maintenance plan
Hi,
I would like to reindex all the tables in my database by using a job
instead of a maintenance plan. In a maintenance plan this is possible with
the checkbox: "reorganize data and index pages". How can I do the same thing
in a script without using DDBC REINDEX for every table?Can use sp_msForEachTable system procedure for the same. Like
EXEC sp_msForEachTable 'DBCC DBREINDEX (''?'')'
HTH,
Vinod Kumar
MCSE, DBA, MCAD, MCSD
http://www.extremeexperts.com
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp
"Jo Segers" <segers_jo@.hotmail.com> wrote in message
news:%23pu65zeIEHA.1944@.TK2MSFTNGP11.phx.gbl...
> Hi,
> I would like to reindex all the tables in my database by using a job
> instead of a maintenance plan. In a maintenance plan this is possible with
> the checkbox: "reorganize data and index pages". How can I do the same
thing
> in a script without using DDBC REINDEX for every table?
>
>|||Thanks,
This solved my problem.
"Vinodk" <vinodk_sct@.NO_SPAM_hotmail.com> schreef in bericht
news:ePhnW9eIEHA.3376@.TK2MSFTNGP09.phx.gbl...
> Can use sp_msForEachTable system procedure for the same. Like
> EXEC sp_msForEachTable 'DBCC DBREINDEX (''?'')'
> --
> HTH,
> Vinod Kumar
> MCSE, DBA, MCAD, MCSD
> http://www.extremeexperts.com
> Books Online for SQL Server SP3 at
> http://www.microsoft.com/sql/techin.../2000/books.asp
>
> "Jo Segers" <segers_jo@.hotmail.com> wrote in message
> news:%23pu65zeIEHA.1944@.TK2MSFTNGP11.phx.gbl...
with
> thing
>|||Here is what I use to dbcc indexDefrag all indexes each night:
-- ****************************************
*******************************
DECLARE @.Table sysname
DECLARE @.Indid Int
DECLARE cur_tblFetch CURSOR FOR
SELECT Table_Name from information_Schema.tables where table_type = 'base
table'
OPEN cur_tblFetch
FETCH NEXT From cur_tblFetch INTO @.Table
While @.@.FETCH_STATUS = 0
BEGIN
DECLARE cur_indFetch CURSOR FOR
SELECT indid FROM SysIndexes WHERE id = Object_ID(@.Table) AND keycnt > 0
OPEN cur_indFetch
FETCH NEXT FROM cur_indFetch INTO @.Indid
WHILE @.@.FETCH_STATUS = 0
BEGIN
IF @.Indid <> 255
BEGIN
DBCC INDEXDEFRAG(Creditnet,@.Table,@.Indid) WITH NO_INFOMSGS
END
FETCH NEXT FROM cur_indFetch INTO @.Indid
END
CLOSE cur_IndFetch
DEALLOCATE cur_IndFetch
FETCH NEXT FROM cur_tblFetch INTO @.Table
END
CLOSE cur_tblFetch
DEALLOCATE cur_tblFetch
EXEC sp_updatestats
-- ****************************************
*******************************
cheers,
Greg Jackson
PDX, Oregon|||I have been looking for a resolution to this as well. Thank you for this in
formation! I have another question - how do I write this if I need to do a
fill factor of 90?
-- Vinodk wrote: --
Can use sp_msForEachTable system procedure for the same. Like
EXEC sp_msForEachTable 'DBCC DBREINDEX (''?'')'
HTH,
Vinod Kumar
MCSE, DBA, MCAD, MCSD
http://www.extremeexperts.com
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp
"Jo Segers" <segers_jo@.hotmail.com> wrote in message
news:%23pu65zeIEHA.1944@.TK2MSFTNGP11.phx.gbl...
> Hi,
> instead of a maintenance plan. In a maintenance plan this is possible with
> the checkbox: "reorganize data and index pages". How can I do the same
thing[vbcol=seagreen]
> in a script without using DDBC REINDEX for every table?
I would like to reindex all the tables in my database by using a job
instead of a maintenance plan. In a maintenance plan this is possible with
the checkbox: "reorganize data and index pages". How can I do the same thing
in a script without using DDBC REINDEX for every table?Can use sp_msForEachTable system procedure for the same. Like
EXEC sp_msForEachTable 'DBCC DBREINDEX (''?'')'
HTH,
Vinod Kumar
MCSE, DBA, MCAD, MCSD
http://www.extremeexperts.com
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp
"Jo Segers" <segers_jo@.hotmail.com> wrote in message
news:%23pu65zeIEHA.1944@.TK2MSFTNGP11.phx.gbl...
> Hi,
> I would like to reindex all the tables in my database by using a job
> instead of a maintenance plan. In a maintenance plan this is possible with
> the checkbox: "reorganize data and index pages". How can I do the same
thing
> in a script without using DDBC REINDEX for every table?
>
>|||Thanks,
This solved my problem.
"Vinodk" <vinodk_sct@.NO_SPAM_hotmail.com> schreef in bericht
news:ePhnW9eIEHA.3376@.TK2MSFTNGP09.phx.gbl...
> Can use sp_msForEachTable system procedure for the same. Like
> EXEC sp_msForEachTable 'DBCC DBREINDEX (''?'')'
> --
> HTH,
> Vinod Kumar
> MCSE, DBA, MCAD, MCSD
> http://www.extremeexperts.com
> Books Online for SQL Server SP3 at
> http://www.microsoft.com/sql/techin.../2000/books.asp
>
> "Jo Segers" <segers_jo@.hotmail.com> wrote in message
> news:%23pu65zeIEHA.1944@.TK2MSFTNGP11.phx.gbl...
with
> thing
>|||Here is what I use to dbcc indexDefrag all indexes each night:
-- ****************************************
*******************************
DECLARE @.Table sysname
DECLARE @.Indid Int
DECLARE cur_tblFetch CURSOR FOR
SELECT Table_Name from information_Schema.tables where table_type = 'base
table'
OPEN cur_tblFetch
FETCH NEXT From cur_tblFetch INTO @.Table
While @.@.FETCH_STATUS = 0
BEGIN
DECLARE cur_indFetch CURSOR FOR
SELECT indid FROM SysIndexes WHERE id = Object_ID(@.Table) AND keycnt > 0
OPEN cur_indFetch
FETCH NEXT FROM cur_indFetch INTO @.Indid
WHILE @.@.FETCH_STATUS = 0
BEGIN
IF @.Indid <> 255
BEGIN
DBCC INDEXDEFRAG(Creditnet,@.Table,@.Indid) WITH NO_INFOMSGS
END
FETCH NEXT FROM cur_indFetch INTO @.Indid
END
CLOSE cur_IndFetch
DEALLOCATE cur_IndFetch
FETCH NEXT FROM cur_tblFetch INTO @.Table
END
CLOSE cur_tblFetch
DEALLOCATE cur_tblFetch
EXEC sp_updatestats
-- ****************************************
*******************************
cheers,
Greg Jackson
PDX, Oregon|||I have been looking for a resolution to this as well. Thank you for this in
formation! I have another question - how do I write this if I need to do a
fill factor of 90?
-- Vinodk wrote: --
Can use sp_msForEachTable system procedure for the same. Like
EXEC sp_msForEachTable 'DBCC DBREINDEX (''?'')'
HTH,
Vinod Kumar
MCSE, DBA, MCAD, MCSD
http://www.extremeexperts.com
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp
"Jo Segers" <segers_jo@.hotmail.com> wrote in message
news:%23pu65zeIEHA.1944@.TK2MSFTNGP11.phx.gbl...
> Hi,
> instead of a maintenance plan. In a maintenance plan this is possible with
> the checkbox: "reorganize data and index pages". How can I do the same
thing[vbcol=seagreen]
> in a script without using DDBC REINDEX for every table?
re-index takes twoce as long on 2005..
Hi
I run a reindex of a 130GB dabase on SQL 2000 and it takes almost exactly 4
hours. IF I run exactly the same command on a 2005 box against a restore of
exactly the same database, it takes almost exactly 8 hours.
am I missing someting here? does a re-ind on 2005 work differently than on
2000? is it doing something twice that I havent asked it to, and that it must
be doing by default?
Any help would be appreciated.
ta
Hi,
Can you see if you have enough room in LDF file while doing the reindex in
SQL 2005. Otherwise the autogrow will happen
and slow down the reindex command...
Thanks
Hari
"Methodology" <Methodology@.discussions.microsoft.com> wrote in message
news:285AE110-BFF1-4CAF-88A6-616C2D8491C9@.microsoft.com...
> Hi
> I run a reindex of a 130GB dabase on SQL 2000 and it takes almost exactly
> 4
> hours. IF I run exactly the same command on a 2005 box against a restore
> of
> exactly the same database, it takes almost exactly 8 hours.
> am I missing someting here? does a re-ind on 2005 work differently than on
> 2000? is it doing something twice that I havent asked it to, and that it
> must
> be doing by default?
> Any help would be appreciated.
> ta
|||Ive tried 'alter index x rebuild' instead of 'dbcc dbreindex' and its
slightly more thsan an hour quicker on my largest table...I think therefore
problem solved.
ta
"Hari Prasad" wrote:
> Hi,
> Can you see if you have enough room in LDF file while doing the reindex in
> SQL 2005. Otherwise the autogrow will happen
> and slow down the reindex command...
> Thanks
> Hari
> "Methodology" <Methodology@.discussions.microsoft.com> wrote in message
> news:285AE110-BFF1-4CAF-88A6-616C2D8491C9@.microsoft.com...
>
>
|||They use exactly the same code. Is the hardware the same between 2000 and
2005, specifically number of CPUs and IO bandwidth?
Paul Randal
Lead Program Manager, Microsoft SQL Server Storage Engine
http://blogs.msdn.com/sqlserverstorageengine/default.aspx
This posting is provided "AS IS" with no warranties, and confers no rights.
"Methodology" <Methodology@.discussions.microsoft.com> wrote in message
news:F3B117CE-C3AF-4FCB-A328-A7FC66B7344B@.microsoft.com...[vbcol=seagreen]
> Ive tried 'alter index x rebuild' instead of 'dbcc dbreindex' and its
> slightly more thsan an hour quicker on my largest table...I think
> therefore
> problem solved.
> ta
> "Hari Prasad" wrote:
I run a reindex of a 130GB dabase on SQL 2000 and it takes almost exactly 4
hours. IF I run exactly the same command on a 2005 box against a restore of
exactly the same database, it takes almost exactly 8 hours.
am I missing someting here? does a re-ind on 2005 work differently than on
2000? is it doing something twice that I havent asked it to, and that it must
be doing by default?
Any help would be appreciated.
ta
Hi,
Can you see if you have enough room in LDF file while doing the reindex in
SQL 2005. Otherwise the autogrow will happen
and slow down the reindex command...
Thanks
Hari
"Methodology" <Methodology@.discussions.microsoft.com> wrote in message
news:285AE110-BFF1-4CAF-88A6-616C2D8491C9@.microsoft.com...
> Hi
> I run a reindex of a 130GB dabase on SQL 2000 and it takes almost exactly
> 4
> hours. IF I run exactly the same command on a 2005 box against a restore
> of
> exactly the same database, it takes almost exactly 8 hours.
> am I missing someting here? does a re-ind on 2005 work differently than on
> 2000? is it doing something twice that I havent asked it to, and that it
> must
> be doing by default?
> Any help would be appreciated.
> ta
|||Ive tried 'alter index x rebuild' instead of 'dbcc dbreindex' and its
slightly more thsan an hour quicker on my largest table...I think therefore
problem solved.
ta
"Hari Prasad" wrote:
> Hi,
> Can you see if you have enough room in LDF file while doing the reindex in
> SQL 2005. Otherwise the autogrow will happen
> and slow down the reindex command...
> Thanks
> Hari
> "Methodology" <Methodology@.discussions.microsoft.com> wrote in message
> news:285AE110-BFF1-4CAF-88A6-616C2D8491C9@.microsoft.com...
>
>
|||They use exactly the same code. Is the hardware the same between 2000 and
2005, specifically number of CPUs and IO bandwidth?
Paul Randal
Lead Program Manager, Microsoft SQL Server Storage Engine
http://blogs.msdn.com/sqlserverstorageengine/default.aspx
This posting is provided "AS IS" with no warranties, and confers no rights.
"Methodology" <Methodology@.discussions.microsoft.com> wrote in message
news:F3B117CE-C3AF-4FCB-A328-A7FC66B7344B@.microsoft.com...[vbcol=seagreen]
> Ive tried 'alter index x rebuild' instead of 'dbcc dbreindex' and its
> slightly more thsan an hour quicker on my largest table...I think
> therefore
> problem solved.
> ta
> "Hari Prasad" wrote:
Reindex tables question
We reindexed all of our tables this weekend. We ran the showcontig for
all of the tables before the reindex and after and when I was comparing
the 2 reports, I noticed that 2 of the tables' (out of about 50) scan
indexes actually increased immediately after the reindex. One went from
100% to 83.33% and the other went down from 99.79% to 99.62%. I know
that during the reindex, we changed some of the fill factors. Does
anyone have any ideas why this would happen. Both tables are frequently
used tables.
Thanks,
Debbie
*** Sent via Devdex http://www.devdex.com ***
Don't just participate in USENET...get rewarded for it!How many pages are the indexes? How much free space in the database? Clustered or non-clustered indexes? Do
you have one or several data files?
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Debbie" <anonymous@.email.com> wrote in message news:%23zfQzSuXEHA.1652@.TK2MSFTNGP09.phx.gbl...
> We reindexed all of our tables this weekend. We ran the showcontig for
> all of the tables before the reindex and after and when I was comparing
> the 2 reports, I noticed that 2 of the tables' (out of about 50) scan
> indexes actually increased immediately after the reindex. One went from
> 100% to 83.33% and the other went down from 99.79% to 99.62%. I know
> that during the reindex, we changed some of the fill factors. Does
> anyone have any ideas why this would happen. Both tables are frequently
> used tables.
>
> Thanks,
> Debbie
>
> *** Sent via Devdex http://www.devdex.com ***
> Don't just participate in USENET...get rewarded for it!|||If you have multiple files the only numbers to worry about are the Logical
Fragmentation ones.
--
Andrew J. Kelly SQL MVP
"Debbie" <anonymous@.email.com> wrote in message
news:%23zfQzSuXEHA.1652@.TK2MSFTNGP09.phx.gbl...
> We reindexed all of our tables this weekend. We ran the showcontig for
> all of the tables before the reindex and after and when I was comparing
> the 2 reports, I noticed that 2 of the tables' (out of about 50) scan
> indexes actually increased immediately after the reindex. One went from
> 100% to 83.33% and the other went down from 99.79% to 99.62%. I know
> that during the reindex, we changed some of the fill factors. Does
> anyone have any ideas why this would happen. Both tables are frequently
> used tables.
>
> Thanks,
> Debbie
>
> *** Sent via Devdex http://www.devdex.com ***
> Don't just participate in USENET...get rewarded for it!|||You should read the whitepaper below which will explain all this to you.
http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ss2kidbp.mspx
--
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Debbie" <anonymous@.email.com> wrote in message
news:#zfQzSuXEHA.1652@.TK2MSFTNGP09.phx.gbl...
> We reindexed all of our tables this weekend. We ran the showcontig for
> all of the tables before the reindex and after and when I was comparing
> the 2 reports, I noticed that 2 of the tables' (out of about 50) scan
> indexes actually increased immediately after the reindex. One went from
> 100% to 83.33% and the other went down from 99.79% to 99.62%. I know
> that during the reindex, we changed some of the fill factors. Does
> anyone have any ideas why this would happen. Both tables are frequently
> used tables.
>
> Thanks,
> Debbie
>
> *** Sent via Devdex http://www.devdex.com ***
> Don't just participate in USENET...get rewarded for it!
all of the tables before the reindex and after and when I was comparing
the 2 reports, I noticed that 2 of the tables' (out of about 50) scan
indexes actually increased immediately after the reindex. One went from
100% to 83.33% and the other went down from 99.79% to 99.62%. I know
that during the reindex, we changed some of the fill factors. Does
anyone have any ideas why this would happen. Both tables are frequently
used tables.
Thanks,
Debbie
*** Sent via Devdex http://www.devdex.com ***
Don't just participate in USENET...get rewarded for it!How many pages are the indexes? How much free space in the database? Clustered or non-clustered indexes? Do
you have one or several data files?
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Debbie" <anonymous@.email.com> wrote in message news:%23zfQzSuXEHA.1652@.TK2MSFTNGP09.phx.gbl...
> We reindexed all of our tables this weekend. We ran the showcontig for
> all of the tables before the reindex and after and when I was comparing
> the 2 reports, I noticed that 2 of the tables' (out of about 50) scan
> indexes actually increased immediately after the reindex. One went from
> 100% to 83.33% and the other went down from 99.79% to 99.62%. I know
> that during the reindex, we changed some of the fill factors. Does
> anyone have any ideas why this would happen. Both tables are frequently
> used tables.
>
> Thanks,
> Debbie
>
> *** Sent via Devdex http://www.devdex.com ***
> Don't just participate in USENET...get rewarded for it!|||If you have multiple files the only numbers to worry about are the Logical
Fragmentation ones.
--
Andrew J. Kelly SQL MVP
"Debbie" <anonymous@.email.com> wrote in message
news:%23zfQzSuXEHA.1652@.TK2MSFTNGP09.phx.gbl...
> We reindexed all of our tables this weekend. We ran the showcontig for
> all of the tables before the reindex and after and when I was comparing
> the 2 reports, I noticed that 2 of the tables' (out of about 50) scan
> indexes actually increased immediately after the reindex. One went from
> 100% to 83.33% and the other went down from 99.79% to 99.62%. I know
> that during the reindex, we changed some of the fill factors. Does
> anyone have any ideas why this would happen. Both tables are frequently
> used tables.
>
> Thanks,
> Debbie
>
> *** Sent via Devdex http://www.devdex.com ***
> Don't just participate in USENET...get rewarded for it!|||You should read the whitepaper below which will explain all this to you.
http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ss2kidbp.mspx
--
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Debbie" <anonymous@.email.com> wrote in message
news:#zfQzSuXEHA.1652@.TK2MSFTNGP09.phx.gbl...
> We reindexed all of our tables this weekend. We ran the showcontig for
> all of the tables before the reindex and after and when I was comparing
> the 2 reports, I noticed that 2 of the tables' (out of about 50) scan
> indexes actually increased immediately after the reindex. One went from
> 100% to 83.33% and the other went down from 99.79% to 99.62%. I know
> that during the reindex, we changed some of the fill factors. Does
> anyone have any ideas why this would happen. Both tables are frequently
> used tables.
>
> Thanks,
> Debbie
>
> *** Sent via Devdex http://www.devdex.com ***
> Don't just participate in USENET...get rewarded for it!
Reindex tables question
We reindexed all of our tables this weekend. We ran the showcontig for
all of the tables before the reindex and after and when I was comparing
the 2 reports, I noticed that 2 of the tables' (out of about 50) scan
indexes actually increased immediately after the reindex. One went from
100% to 83.33% and the other went down from 99.79% to 99.62%. I know
that during the reindex, we changed some of the fill factors. Does
anyone have any ideas why this would happen. Both tables are frequently
used tables.
Thanks,
Debbie
*** Sent via Devdex http://www.devdex.com ***
Don't just participate in USENET...get rewarded for it!
How many pages are the indexes? How much free space in the database? Clustered or non-clustered indexes? Do
you have one or several data files?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Debbie" <anonymous@.email.com> wrote in message news:%23zfQzSuXEHA.1652@.TK2MSFTNGP09.phx.gbl...
> We reindexed all of our tables this weekend. We ran the showcontig for
> all of the tables before the reindex and after and when I was comparing
> the 2 reports, I noticed that 2 of the tables' (out of about 50) scan
> indexes actually increased immediately after the reindex. One went from
> 100% to 83.33% and the other went down from 99.79% to 99.62%. I know
> that during the reindex, we changed some of the fill factors. Does
> anyone have any ideas why this would happen. Both tables are frequently
> used tables.
>
> Thanks,
> Debbie
>
> *** Sent via Devdex http://www.devdex.com ***
> Don't just participate in USENET...get rewarded for it!
|||If you have multiple files the only numbers to worry about are the Logical
Fragmentation ones.
Andrew J. Kelly SQL MVP
"Debbie" <anonymous@.email.com> wrote in message
news:%23zfQzSuXEHA.1652@.TK2MSFTNGP09.phx.gbl...
> We reindexed all of our tables this weekend. We ran the showcontig for
> all of the tables before the reindex and after and when I was comparing
> the 2 reports, I noticed that 2 of the tables' (out of about 50) scan
> indexes actually increased immediately after the reindex. One went from
> 100% to 83.33% and the other went down from 99.79% to 99.62%. I know
> that during the reindex, we changed some of the fill factors. Does
> anyone have any ideas why this would happen. Both tables are frequently
> used tables.
>
> Thanks,
> Debbie
>
> *** Sent via Devdex http://www.devdex.com ***
> Don't just participate in USENET...get rewarded for it!
|||You should read the whitepaper below which will explain all this to you.
http://www.microsoft.com/technet/pro.../ss2kidbp.mspx
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Debbie" <anonymous@.email.com> wrote in message
news:#zfQzSuXEHA.1652@.TK2MSFTNGP09.phx.gbl...
> We reindexed all of our tables this weekend. We ran the showcontig for
> all of the tables before the reindex and after and when I was comparing
> the 2 reports, I noticed that 2 of the tables' (out of about 50) scan
> indexes actually increased immediately after the reindex. One went from
> 100% to 83.33% and the other went down from 99.79% to 99.62%. I know
> that during the reindex, we changed some of the fill factors. Does
> anyone have any ideas why this would happen. Both tables are frequently
> used tables.
>
> Thanks,
> Debbie
>
> *** Sent via Devdex http://www.devdex.com ***
> Don't just participate in USENET...get rewarded for it!
|||Thanks everyone for responding. I actually printed out that white paper
this morning and am going to wait to play with this anymore until I have
read the whole thing cover to cover.
*** Sent via Devdex http://www.devdex.com ***
Don't just participate in USENET...get rewarded for it!
all of the tables before the reindex and after and when I was comparing
the 2 reports, I noticed that 2 of the tables' (out of about 50) scan
indexes actually increased immediately after the reindex. One went from
100% to 83.33% and the other went down from 99.79% to 99.62%. I know
that during the reindex, we changed some of the fill factors. Does
anyone have any ideas why this would happen. Both tables are frequently
used tables.
Thanks,
Debbie
*** Sent via Devdex http://www.devdex.com ***
Don't just participate in USENET...get rewarded for it!
How many pages are the indexes? How much free space in the database? Clustered or non-clustered indexes? Do
you have one or several data files?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Debbie" <anonymous@.email.com> wrote in message news:%23zfQzSuXEHA.1652@.TK2MSFTNGP09.phx.gbl...
> We reindexed all of our tables this weekend. We ran the showcontig for
> all of the tables before the reindex and after and when I was comparing
> the 2 reports, I noticed that 2 of the tables' (out of about 50) scan
> indexes actually increased immediately after the reindex. One went from
> 100% to 83.33% and the other went down from 99.79% to 99.62%. I know
> that during the reindex, we changed some of the fill factors. Does
> anyone have any ideas why this would happen. Both tables are frequently
> used tables.
>
> Thanks,
> Debbie
>
> *** Sent via Devdex http://www.devdex.com ***
> Don't just participate in USENET...get rewarded for it!
|||If you have multiple files the only numbers to worry about are the Logical
Fragmentation ones.
Andrew J. Kelly SQL MVP
"Debbie" <anonymous@.email.com> wrote in message
news:%23zfQzSuXEHA.1652@.TK2MSFTNGP09.phx.gbl...
> We reindexed all of our tables this weekend. We ran the showcontig for
> all of the tables before the reindex and after and when I was comparing
> the 2 reports, I noticed that 2 of the tables' (out of about 50) scan
> indexes actually increased immediately after the reindex. One went from
> 100% to 83.33% and the other went down from 99.79% to 99.62%. I know
> that during the reindex, we changed some of the fill factors. Does
> anyone have any ideas why this would happen. Both tables are frequently
> used tables.
>
> Thanks,
> Debbie
>
> *** Sent via Devdex http://www.devdex.com ***
> Don't just participate in USENET...get rewarded for it!
|||You should read the whitepaper below which will explain all this to you.
http://www.microsoft.com/technet/pro.../ss2kidbp.mspx
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Debbie" <anonymous@.email.com> wrote in message
news:#zfQzSuXEHA.1652@.TK2MSFTNGP09.phx.gbl...
> We reindexed all of our tables this weekend. We ran the showcontig for
> all of the tables before the reindex and after and when I was comparing
> the 2 reports, I noticed that 2 of the tables' (out of about 50) scan
> indexes actually increased immediately after the reindex. One went from
> 100% to 83.33% and the other went down from 99.79% to 99.62%. I know
> that during the reindex, we changed some of the fill factors. Does
> anyone have any ideas why this would happen. Both tables are frequently
> used tables.
>
> Thanks,
> Debbie
>
> *** Sent via Devdex http://www.devdex.com ***
> Don't just participate in USENET...get rewarded for it!
|||Thanks everyone for responding. I actually printed out that white paper
this morning and am going to wait to play with this anymore until I have
read the whole thing cover to cover.
*** Sent via Devdex http://www.devdex.com ***
Don't just participate in USENET...get rewarded for it!
Subscribe to:
Posts (Atom)