Hi,
SQL Server 2K Enterprise Ed, running Transactional replication, four
articles being replicated, 1 article containing over 15 million rows.
That table has 4 indexes on it. I created a job that executes a DBCC
INDEXDEFRAG statement against each of the indecies, I run the job at
the weekend, the job never errors, however it kills the Trans
replication.
What am I doing wrong? I used INDEXDEFRAG because of its online
capabilities, but the replication still fails.
Cheers
Scott
Are you having a problem with the Log Reader agent?
If so the problem is that Index Defragging is a logged operation and your
Tlog will balloon. This puts stress on your log reader agent and you will
see that it will experience time outs. The best way to fix this is to change
your Log Reader Agent's PollingInterval - set it to 1, and change the
ReadBatchSize - probably to 50 when you are doing the defragging. When you
not, use the defaults. Using profiles is an excellend way to do this.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
<quackhandle1975@.yahoo.co.uk> wrote in message
news:1108472684.342558.177210@.z14g2000cwz.googlegr oups.com...
> Hi,
> SQL Server 2K Enterprise Ed, running Transactional replication, four
> articles being replicated, 1 article containing over 15 million rows.
> That table has 4 indexes on it. I created a job that executes a DBCC
> INDEXDEFRAG statement against each of the indecies, I run the job at
> the weekend, the job never errors, however it kills the Trans
> replication.
> What am I doing wrong? I used INDEXDEFRAG because of its online
> capabilities, but the replication still fails.
> Cheers
> Scott
>
Showing posts with label containing. Show all posts
Showing posts with label containing. Show all posts
Monday, March 19, 2012
Indexing Huge Table
Well i have a database on my production server which is containing a table
having 10 million records and daily 10 - 20 K records are being inserted int
o
the same. The Table is also containing 25 indexes on different columns so
which is best time and way to reindex my table for newly inserted records.
Please helpAmit Patel wrote:
> Well i have a database on my production server which is containing a
> table having 10 million records and daily 10 - 20 K records are being
> inserted into the same. The Table is also containing 25 indexes on
> different columns so which is best time and way to reindex my table
> for newly inserted records.
> Please help
You don't need to reindex for new rows. They are automatically added to
indexes on the table when the row is added/updated/deleted.
I'm guessing this table is used in a DSS scenario and that's the reason
for all the indexing. Without knowing more about your environment and
how the table is used, it's hard to recommend options other than to say
you may want the clustered index on the table to be on a key(s) that
won't cause too much page splitting. But, then again, for DSS it may be
better to have the clustered index on a set of keys that benefits
queries.
If you need to reindex then you should do this off-hours. Rebuilding the
clustered index will cause a non-clustered rebuild as well. You can also
defrag tables/indexes using DBCC INDEXDEFRAG.
David Gugick
Imceda Software
www.imceda.com|||Amit,
Have a look into the below article. This is really great.
http://www.microsoft.com/technet/pr...n/ss2kidbp.mspx
Thanks
Hari
SQL Server MVP
"Amit Patel" wrote:
> Well i have a database on my production server which is containing a table
> having 10 million records and daily 10 - 20 K records are being inserted i
nto
> the same. The Table is also containing 25 indexes on different columns so
> which is best time and way to reindex my table for newly inserted records
.
> Please help
having 10 million records and daily 10 - 20 K records are being inserted int
o
the same. The Table is also containing 25 indexes on different columns so
which is best time and way to reindex my table for newly inserted records.
Please helpAmit Patel wrote:
> Well i have a database on my production server which is containing a
> table having 10 million records and daily 10 - 20 K records are being
> inserted into the same. The Table is also containing 25 indexes on
> different columns so which is best time and way to reindex my table
> for newly inserted records.
> Please help
You don't need to reindex for new rows. They are automatically added to
indexes on the table when the row is added/updated/deleted.
I'm guessing this table is used in a DSS scenario and that's the reason
for all the indexing. Without knowing more about your environment and
how the table is used, it's hard to recommend options other than to say
you may want the clustered index on the table to be on a key(s) that
won't cause too much page splitting. But, then again, for DSS it may be
better to have the clustered index on a set of keys that benefits
queries.
If you need to reindex then you should do this off-hours. Rebuilding the
clustered index will cause a non-clustered rebuild as well. You can also
defrag tables/indexes using DBCC INDEXDEFRAG.
David Gugick
Imceda Software
www.imceda.com|||Amit,
Have a look into the below article. This is really great.
http://www.microsoft.com/technet/pr...n/ss2kidbp.mspx
Thanks
Hari
SQL Server MVP
"Amit Patel" wrote:
> Well i have a database on my production server which is containing a table
> having 10 million records and daily 10 - 20 K records are being inserted i
nto
> the same. The Table is also containing 25 indexes on different columns so
> which is best time and way to reindex my table for newly inserted records
.
> Please help
Indexing Huge Table
Well i have a database on my production server which is containing a table
having 10 million records and daily 10 - 20 K records are being inserted into
the same. The Table is also containing 25 indexes on different columns so
which is best time and way to reindex my table for newly inserted records.
Please helpAmit Patel wrote:
> Well i have a database on my production server which is containing a
> table having 10 million records and daily 10 - 20 K records are being
> inserted into the same. The Table is also containing 25 indexes on
> different columns so which is best time and way to reindex my table
> for newly inserted records.
> Please help
You don't need to reindex for new rows. They are automatically added to
indexes on the table when the row is added/updated/deleted.
I'm guessing this table is used in a DSS scenario and that's the reason
for all the indexing. Without knowing more about your environment and
how the table is used, it's hard to recommend options other than to say
you may want the clustered index on the table to be on a key(s) that
won't cause too much page splitting. But, then again, for DSS it may be
better to have the clustered index on a set of keys that benefits
queries.
If you need to reindex then you should do this off-hours. Rebuilding the
clustered index will cause a non-clustered rebuild as well. You can also
defrag tables/indexes using DBCC INDEXDEFRAG.
David Gugick
Imceda Software
www.imceda.com|||Amit,
Have a look into the below article. This is really great.
http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ss2kidbp.mspx
Thanks
Hari
SQL Server MVP
"Amit Patel" wrote:
> Well i have a database on my production server which is containing a table
> having 10 million records and daily 10 - 20 K records are being inserted into
> the same. The Table is also containing 25 indexes on different columns so
> which is best time and way to reindex my table for newly inserted records.
> Please help
having 10 million records and daily 10 - 20 K records are being inserted into
the same. The Table is also containing 25 indexes on different columns so
which is best time and way to reindex my table for newly inserted records.
Please helpAmit Patel wrote:
> Well i have a database on my production server which is containing a
> table having 10 million records and daily 10 - 20 K records are being
> inserted into the same. The Table is also containing 25 indexes on
> different columns so which is best time and way to reindex my table
> for newly inserted records.
> Please help
You don't need to reindex for new rows. They are automatically added to
indexes on the table when the row is added/updated/deleted.
I'm guessing this table is used in a DSS scenario and that's the reason
for all the indexing. Without knowing more about your environment and
how the table is used, it's hard to recommend options other than to say
you may want the clustered index on the table to be on a key(s) that
won't cause too much page splitting. But, then again, for DSS it may be
better to have the clustered index on a set of keys that benefits
queries.
If you need to reindex then you should do this off-hours. Rebuilding the
clustered index will cause a non-clustered rebuild as well. You can also
defrag tables/indexes using DBCC INDEXDEFRAG.
David Gugick
Imceda Software
www.imceda.com|||Amit,
Have a look into the below article. This is really great.
http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ss2kidbp.mspx
Thanks
Hari
SQL Server MVP
"Amit Patel" wrote:
> Well i have a database on my production server which is containing a table
> having 10 million records and daily 10 - 20 K records are being inserted into
> the same. The Table is also containing 25 indexes on different columns so
> which is best time and way to reindex my table for newly inserted records.
> Please help
Indexing Huge Table
Well i have a database on my production server which is containing a table
having 10 million records and daily 10 - 20 K records are being inserted into
the same. The Table is also containing 25 indexes on different columns so
which is best time and way to reindex my table for newly inserted records.
Please help
Amit Patel wrote:
> Well i have a database on my production server which is containing a
> table having 10 million records and daily 10 - 20 K records are being
> inserted into the same. The Table is also containing 25 indexes on
> different columns so which is best time and way to reindex my table
> for newly inserted records.
> Please help
You don't need to reindex for new rows. They are automatically added to
indexes on the table when the row is added/updated/deleted.
I'm guessing this table is used in a DSS scenario and that's the reason
for all the indexing. Without knowing more about your environment and
how the table is used, it's hard to recommend options other than to say
you may want the clustered index on the table to be on a key(s) that
won't cause too much page splitting. But, then again, for DSS it may be
better to have the clustered index on a set of keys that benefits
queries.
If you need to reindex then you should do this off-hours. Rebuilding the
clustered index will cause a non-clustered rebuild as well. You can also
defrag tables/indexes using DBCC INDEXDEFRAG.
David Gugick
Imceda Software
www.imceda.com
|||Amit,
Have a look into the below article. This is really great.
http://www.microsoft.com/technet/pro.../ss2kidbp.mspx
Thanks
Hari
SQL Server MVP
"Amit Patel" wrote:
> Well i have a database on my production server which is containing a table
> having 10 million records and daily 10 - 20 K records are being inserted into
> the same. The Table is also containing 25 indexes on different columns so
> which is best time and way to reindex my table for newly inserted records.
> Please help
having 10 million records and daily 10 - 20 K records are being inserted into
the same. The Table is also containing 25 indexes on different columns so
which is best time and way to reindex my table for newly inserted records.
Please help
Amit Patel wrote:
> Well i have a database on my production server which is containing a
> table having 10 million records and daily 10 - 20 K records are being
> inserted into the same. The Table is also containing 25 indexes on
> different columns so which is best time and way to reindex my table
> for newly inserted records.
> Please help
You don't need to reindex for new rows. They are automatically added to
indexes on the table when the row is added/updated/deleted.
I'm guessing this table is used in a DSS scenario and that's the reason
for all the indexing. Without knowing more about your environment and
how the table is used, it's hard to recommend options other than to say
you may want the clustered index on the table to be on a key(s) that
won't cause too much page splitting. But, then again, for DSS it may be
better to have the clustered index on a set of keys that benefits
queries.
If you need to reindex then you should do this off-hours. Rebuilding the
clustered index will cause a non-clustered rebuild as well. You can also
defrag tables/indexes using DBCC INDEXDEFRAG.
David Gugick
Imceda Software
www.imceda.com
|||Amit,
Have a look into the below article. This is really great.
http://www.microsoft.com/technet/pro.../ss2kidbp.mspx
Thanks
Hari
SQL Server MVP
"Amit Patel" wrote:
> Well i have a database on my production server which is containing a table
> having 10 million records and daily 10 - 20 K records are being inserted into
> the same. The Table is also containing 25 indexes on different columns so
> which is best time and way to reindex my table for newly inserted records.
> Please help
Indexing Huge Table
Well i have a database on my production server which is containing a table
having 10 million records and daily 10 - 20 K records are being inserted int
o
the same. The Table is also containing 25 indexes on different columns so
which is best time and way to reindex my table for newly inserted records.
Please helpAmit Patel wrote:
> Well i have a database on my production server which is containing a
> table having 10 million records and daily 10 - 20 K records are being
> inserted into the same. The Table is also containing 25 indexes on
> different columns so which is best time and way to reindex my table
> for newly inserted records.
> Please help
multi-post. see response in other ng.
David Gugick
Imceda Software
www.imceda.com
having 10 million records and daily 10 - 20 K records are being inserted int
o
the same. The Table is also containing 25 indexes on different columns so
which is best time and way to reindex my table for newly inserted records.
Please helpAmit Patel wrote:
> Well i have a database on my production server which is containing a
> table having 10 million records and daily 10 - 20 K records are being
> inserted into the same. The Table is also containing 25 indexes on
> different columns so which is best time and way to reindex my table
> for newly inserted records.
> Please help
multi-post. see response in other ng.
David Gugick
Imceda Software
www.imceda.com
Subscribe to:
Posts (Atom)