Rebuild Index

Today our rebuild index task worked about 4 hours. at this moment our table
was locked and we didn't reach table. how many times in a month(or week)
rebuild index task must work?Cenk FIRAT wrote:
> Today our rebuild index task worked about 4 hours. at this moment our table
> was locked and we didn't reach table. how many times in a month(or week)
> rebuild index task must work?
In SQL Server 2000 reindex will lock your tables. You need to check
fragmentation of your index to decide this frequency. Index defrag will
be better option because it doesn't lock tables and online operation.
you can find good example of it in BOL in DBCC Showcontig topic.
Regards
Amish Shah
http://shahamishm.tripod.com|||And in 2005, you can rebuild an index using the ONLINE option. As for how often you want to rebuild
indexes, well, there is no set answer for that. A good start is to read
http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ss2kidbp.mspx
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"amish" <shahamishm@.gmail.com> wrote in message
news:1161159695.233514.326160@.f16g2000cwb.googlegroups.com...
> Cenk FIRAT wrote:
>> Today our rebuild index task worked about 4 hours. at this moment our table
>> was locked and we didn't reach table. how many times in a month(or week)
>> rebuild index task must work?
> In SQL Server 2000 reindex will lock your tables. You need to check
> fragmentation of your index to decide this frequency. Index defrag will
> be better option because it doesn't lock tables and online operation.
> you can find good example of it in BOL in DBCC Showcontig topic.
> Regards
> Amish Shah
> http://shahamishm.tripod.com
>|||If you can afford Enterprise (c:
Regards,
Greg Linwood
SQL Server MVP
http://blogs.sqlserver.org.au/blogs/greg_linwood
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:%23XlWbMp8GHA.1012@.TK2MSFTNGP05.phx.gbl...
> And in 2005, you can rebuild an index using the ONLINE option. As for how
> often you want to rebuild indexes, well, there is no set answer for that.
> A good start is to read
> http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ss2kidbp.mspx
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "amish" <shahamishm@.gmail.com> wrote in message
> news:1161159695.233514.326160@.f16g2000cwb.googlegroups.com...
>> Cenk FIRAT wrote:
>> Today our rebuild index task worked about 4 hours. at this moment our
>> table
>> was locked and we didn't reach table. how many times in a month(or week)
>> rebuild index task must work?
>> In SQL Server 2000 reindex will lock your tables. You need to check
>> fragmentation of your index to decide this frequency. Index defrag will
>> be better option because it doesn't lock tables and online operation.
>> you can find good example of it in BOL in DBCC Showcontig topic.
>> Regards
>> Amish Shah
>> http://shahamishm.tripod.com
>|||If you can't rebuild your whole table (CIX, Heap), consider just rebuilding
your NCIXs as these are where most performance usually comes from & they're
MUCH faster to rebuild than CIXs or HEAPs.
A very common flaw in database administration is to tie index rebuilds to
table rebuilds. They don't have to be done at the same time & you usually
get far more milage from rebuilding the NCIXs than the tables. NCIXs also
fragment significantly faster too, so they need rebuilding more often.
Regards,
Greg Linwood
SQL Server MVP
http://blogs.sqlserver.org.au/blogs/greg_linwood
"Cenk FIRAT" <CenkFIRAT@.discussions.microsoft.com> wrote in message
news:C515B8B2-2564-4A37-A4BB-5F9B85745180@.microsoft.com...
> Today our rebuild index task worked about 4 hours. at this moment our
> table
> was locked and we didn't reach table. how many times in a month(or week)
> rebuild index task must work?|||Cenk FIRAT wrote:
> Today our rebuild index task worked about 4 hours. at this moment our table
> was locked and we didn't reach table. how many times in a month(or week)
> rebuild index task must work?
Consider only rebuilding those that have become badly fragmented...
Schedule this script to run once per week:
http://realsqlguy.com/serendipity/archives/12-Humpty-Dumpty-Sat-On-A-Wall...html|||> If you can afford Enterprise (c:
True. I tend to forget about that small detail. :-)
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Greg Linwood" <g_linwood@.hotmail.com> wrote in message
news:%2317ZPfr8GHA.3916@.TK2MSFTNGP04.phx.gbl...
> If you can afford Enterprise (c:
> Regards,
> Greg Linwood
> SQL Server MVP
> http://blogs.sqlserver.org.au/blogs/greg_linwood
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in message
> news:%23XlWbMp8GHA.1012@.TK2MSFTNGP05.phx.gbl...
>> And in 2005, you can rebuild an index using the ONLINE option. As for how often you want to
>> rebuild indexes, well, there is no set answer for that. A good start is to read
>> http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ss2kidbp.mspx
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>>
>> "amish" <shahamishm@.gmail.com> wrote in message
>> news:1161159695.233514.326160@.f16g2000cwb.googlegroups.com...
>> Cenk FIRAT wrote:
>> Today our rebuild index task worked about 4 hours. at this moment our table
>> was locked and we didn't reach table. how many times in a month(or week)
>> rebuild index task must work?
>> In SQL Server 2000 reindex will lock your tables. You need to check
>> fragmentation of your index to decide this frequency. Index defrag will
>> be better option because it doesn't lock tables and online operation.
>> you can find good example of it in BOL in DBCC Showcontig topic.
>> Regards
>> Amish Shah
>> http://shahamishm.tripod.com
>>
>|||I guess when you live in a country where beer costs $20 a glass, SQL
Enterprise Licensing truely is a trivial matter (c:
Regards,
Greg Linwood
SQL Server MVP
http://blogs.sqlserver.org.au/blogs/greg_linwood
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:uCikhis8GHA.2268@.TK2MSFTNGP05.phx.gbl...
>> If you can afford Enterprise (c:
> True. I tend to forget about that small detail. :-)
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Greg Linwood" <g_linwood@.hotmail.com> wrote in message
> news:%2317ZPfr8GHA.3916@.TK2MSFTNGP04.phx.gbl...
>> If you can afford Enterprise (c:
>> Regards,
>> Greg Linwood
>> SQL Server MVP
>> http://blogs.sqlserver.org.au/blogs/greg_linwood
>> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote
>> in message news:%23XlWbMp8GHA.1012@.TK2MSFTNGP05.phx.gbl...
>> And in 2005, you can rebuild an index using the ONLINE option. As for
>> how often you want to rebuild indexes, well, there is no set answer for
>> that. A good start is to read
>> http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ss2kidbp.mspx
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>>
>> "amish" <shahamishm@.gmail.com> wrote in message
>> news:1161159695.233514.326160@.f16g2000cwb.googlegroups.com...
>> Cenk FIRAT wrote:
>> Today our rebuild index task worked about 4 hours. at this moment our
>> table
>> was locked and we didn't reach table. how many times in a month(or
>> week)
>> rebuild index task must work?
>> In SQL Server 2000 reindex will lock your tables. You need to check
>> fragmentation of your index to decide this frequency. Index defrag will
>> be better option because it doesn't lock tables and online operation.
>> you can find good example of it in BOL in DBCC Showcontig topic.
>> Regards
>> Amish Shah
>> http://shahamishm.tripod.com
>>
>>
>