I have a SQL Server 2000 database that I would like to delete the Cluster
Index with a primary key. This primary key has a foreign key constraint.
Please help me create the syntax to delete the Cluster Index on Table A.
Thank You,
Table A
A1 PK (PK Constraint Name:A_A1)
A2
A3 (Non-Clustered Index Name: A_A3)
Table B
B1 PK
B2 FK (FK Constraint Name: B2_A_A1)
B3 (Non-Clustered Index Name: B_B3)
Hi,
You cannot remove Clustered index in your case as Primery Key is relying on
index and index is required for primery key.
All you can do is change clustered index to non-clustered or you can break
the relation and remove primery key to delete index.
Danijel Novak
"Joe K." <Joe K.@.discussions.microsoft.com> wrote in message
news:E7AF8E98-702E-4505-8B23-6282E615FD79@.microsoft.com...
> I have a SQL Server 2000 database that I would like to delete the Cluster
> Index with a primary key. This primary key has a foreign key constraint.
> Please help me create the syntax to delete the Cluster Index on Table A.
> Thank You,
> Table A
> A1 PK (PK Constraint Name:A_A1)
> A2
> A3 (Non-Clustered Index Name: A_A3)
> Table B
> B1 PK
> B2 FK (FK Constraint Name: B2_A_A1)
> B3 (Non-Clustered Index Name: B_B3)
|||As you have a PRIMARY KEY this is treated as a CONSTRAINT rather than an
INDEX.
The syntax is :-
ALTER TABLE table_name DROP CONSTRAINT constraint_name
Firstly you will need to DROP the FOREIGN KEY constraint on TableB, then
DROP the PK on TableA
HTH
"Joe K." <Joe K.@.discussions.microsoft.com> wrote in message
news:E7AF8E98-702E-4505-8B23-6282E615FD79@.microsoft.com...
> I have a SQL Server 2000 database that I would like to delete the Cluster
> Index with a primary key. This primary key has a foreign key constraint.
> Please help me create the syntax to delete the Cluster Index on Table A.
> Thank You,
> Table A
> A1 PK (PK Constraint Name:A_A1)
> A2
> A3 (Non-Clustered Index Name: A_A3)
> Table B
> B1 PK
> B2 FK (FK Constraint Name: B2_A_A1)
> B3 (Non-Clustered Index Name: B_B3)
Showing posts with label index. Show all posts
Showing posts with label index. Show all posts
Tuesday, March 27, 2012
Delete Cluster Index With PK and FK Constraints
I have a SQL Server 2000 database that I would like to delete the Cluster
Index with a primary key. This primary key has a foreign key constraint.
Please help me create the syntax to delete the Cluster Index on Table A.
Thank You,
Table A
A1 PK (PK Constraint Name:A_A1)
A2
A3 (Non-Clustered Index Name: A_A3)
Table B
B1 PK
B2 FK (FK Constraint Name: B2_A_A1)
B3 (Non-Clustered Index Name: B_B3)Hi,
You cannot remove Clustered index in your case as Primery Key is relying on
index and index is required for primery key.
All you can do is change clustered index to non-clustered or you can break
the relation and remove primery key to delete index.
--
Danijel Novak
"Joe K." <Joe K.@.discussions.microsoft.com> wrote in message
news:E7AF8E98-702E-4505-8B23-6282E615FD79@.microsoft.com...
> I have a SQL Server 2000 database that I would like to delete the Cluster
> Index with a primary key. This primary key has a foreign key constraint.
> Please help me create the syntax to delete the Cluster Index on Table A.
> Thank You,
> Table A
> A1 PK (PK Constraint Name:A_A1)
> A2
> A3 (Non-Clustered Index Name: A_A3)
> Table B
> B1 PK
> B2 FK (FK Constraint Name: B2_A_A1)
> B3 (Non-Clustered Index Name: B_B3)|||As you have a PRIMARY KEY this is treated as a CONSTRAINT rather than an
INDEX.
The syntax is :-
ALTER TABLE table_name DROP CONSTRAINT constraint_name
Firstly you will need to DROP the FOREIGN KEY constraint on TableB, then
DROP the PK on TableA
HTH
"Joe K." <Joe K.@.discussions.microsoft.com> wrote in message
news:E7AF8E98-702E-4505-8B23-6282E615FD79@.microsoft.com...
> I have a SQL Server 2000 database that I would like to delete the Cluster
> Index with a primary key. This primary key has a foreign key constraint.
> Please help me create the syntax to delete the Cluster Index on Table A.
> Thank You,
> Table A
> A1 PK (PK Constraint Name:A_A1)
> A2
> A3 (Non-Clustered Index Name: A_A3)
> Table B
> B1 PK
> B2 FK (FK Constraint Name: B2_A_A1)
> B3 (Non-Clustered Index Name: B_B3)
Index with a primary key. This primary key has a foreign key constraint.
Please help me create the syntax to delete the Cluster Index on Table A.
Thank You,
Table A
A1 PK (PK Constraint Name:A_A1)
A2
A3 (Non-Clustered Index Name: A_A3)
Table B
B1 PK
B2 FK (FK Constraint Name: B2_A_A1)
B3 (Non-Clustered Index Name: B_B3)Hi,
You cannot remove Clustered index in your case as Primery Key is relying on
index and index is required for primery key.
All you can do is change clustered index to non-clustered or you can break
the relation and remove primery key to delete index.
--
Danijel Novak
"Joe K." <Joe K.@.discussions.microsoft.com> wrote in message
news:E7AF8E98-702E-4505-8B23-6282E615FD79@.microsoft.com...
> I have a SQL Server 2000 database that I would like to delete the Cluster
> Index with a primary key. This primary key has a foreign key constraint.
> Please help me create the syntax to delete the Cluster Index on Table A.
> Thank You,
> Table A
> A1 PK (PK Constraint Name:A_A1)
> A2
> A3 (Non-Clustered Index Name: A_A3)
> Table B
> B1 PK
> B2 FK (FK Constraint Name: B2_A_A1)
> B3 (Non-Clustered Index Name: B_B3)|||As you have a PRIMARY KEY this is treated as a CONSTRAINT rather than an
INDEX.
The syntax is :-
ALTER TABLE table_name DROP CONSTRAINT constraint_name
Firstly you will need to DROP the FOREIGN KEY constraint on TableB, then
DROP the PK on TableA
HTH
"Joe K." <Joe K.@.discussions.microsoft.com> wrote in message
news:E7AF8E98-702E-4505-8B23-6282E615FD79@.microsoft.com...
> I have a SQL Server 2000 database that I would like to delete the Cluster
> Index with a primary key. This primary key has a foreign key constraint.
> Please help me create the syntax to delete the Cluster Index on Table A.
> Thank You,
> Table A
> A1 PK (PK Constraint Name:A_A1)
> A2
> A3 (Non-Clustered Index Name: A_A3)
> Table B
> B1 PK
> B2 FK (FK Constraint Name: B2_A_A1)
> B3 (Non-Clustered Index Name: B_B3)
Delete Cluster Index With PK and FK Constraints
I have a SQL Server 2000 database that I would like to delete the Cluster
Index with a primary key. This primary key has a foreign key constraint.
Please help me create the syntax to delete the Cluster Index on Table A.
Thank You,
Table A
A1 PK (PK Constraint Name:A_A1)
A2
A3 (Non-Clustered Index Name: A_A3)
Table B
B1 PK
B2 FK (FK Constraint Name: B2_A_A1)
B3 (Non-Clustered Index Name: B_B3)Hi,
You cannot remove Clustered index in your case as Primery Key is relying on
index and index is required for primery key.
All you can do is change clustered index to non-clustered or you can break
the relation and remove primery key to delete index.
Danijel Novak
"Joe K." <Joe K.@.discussions.microsoft.com> wrote in message
news:E7AF8E98-702E-4505-8B23-6282E615FD79@.microsoft.com...
> I have a SQL Server 2000 database that I would like to delete the Cluster
> Index with a primary key. This primary key has a foreign key constraint.
> Please help me create the syntax to delete the Cluster Index on Table A.
> Thank You,
> Table A
> A1 PK (PK Constraint Name:A_A1)
> A2
> A3 (Non-Clustered Index Name: A_A3)
> Table B
> B1 PK
> B2 FK (FK Constraint Name: B2_A_A1)
> B3 (Non-Clustered Index Name: B_B3)|||As you have a PRIMARY KEY this is treated as a CONSTRAINT rather than an
INDEX.
The syntax is :-
ALTER TABLE table_name DROP CONSTRAINT constraint_name
Firstly you will need to DROP the FOREIGN KEY constraint on TableB, then
DROP the PK on TableA
HTH
"Joe K." <Joe K.@.discussions.microsoft.com> wrote in message
news:E7AF8E98-702E-4505-8B23-6282E615FD79@.microsoft.com...
> I have a SQL Server 2000 database that I would like to delete the Cluster
> Index with a primary key. This primary key has a foreign key constraint.
> Please help me create the syntax to delete the Cluster Index on Table A.
> Thank You,
> Table A
> A1 PK (PK Constraint Name:A_A1)
> A2
> A3 (Non-Clustered Index Name: A_A3)
> Table B
> B1 PK
> B2 FK (FK Constraint Name: B2_A_A1)
> B3 (Non-Clustered Index Name: B_B3)
Index with a primary key. This primary key has a foreign key constraint.
Please help me create the syntax to delete the Cluster Index on Table A.
Thank You,
Table A
A1 PK (PK Constraint Name:A_A1)
A2
A3 (Non-Clustered Index Name: A_A3)
Table B
B1 PK
B2 FK (FK Constraint Name: B2_A_A1)
B3 (Non-Clustered Index Name: B_B3)Hi,
You cannot remove Clustered index in your case as Primery Key is relying on
index and index is required for primery key.
All you can do is change clustered index to non-clustered or you can break
the relation and remove primery key to delete index.
Danijel Novak
"Joe K." <Joe K.@.discussions.microsoft.com> wrote in message
news:E7AF8E98-702E-4505-8B23-6282E615FD79@.microsoft.com...
> I have a SQL Server 2000 database that I would like to delete the Cluster
> Index with a primary key. This primary key has a foreign key constraint.
> Please help me create the syntax to delete the Cluster Index on Table A.
> Thank You,
> Table A
> A1 PK (PK Constraint Name:A_A1)
> A2
> A3 (Non-Clustered Index Name: A_A3)
> Table B
> B1 PK
> B2 FK (FK Constraint Name: B2_A_A1)
> B3 (Non-Clustered Index Name: B_B3)|||As you have a PRIMARY KEY this is treated as a CONSTRAINT rather than an
INDEX.
The syntax is :-
ALTER TABLE table_name DROP CONSTRAINT constraint_name
Firstly you will need to DROP the FOREIGN KEY constraint on TableB, then
DROP the PK on TableA
HTH
"Joe K." <Joe K.@.discussions.microsoft.com> wrote in message
news:E7AF8E98-702E-4505-8B23-6282E615FD79@.microsoft.com...
> I have a SQL Server 2000 database that I would like to delete the Cluster
> Index with a primary key. This primary key has a foreign key constraint.
> Please help me create the syntax to delete the Cluster Index on Table A.
> Thank You,
> Table A
> A1 PK (PK Constraint Name:A_A1)
> A2
> A3 (Non-Clustered Index Name: A_A3)
> Table B
> B1 PK
> B2 FK (FK Constraint Name: B2_A_A1)
> B3 (Non-Clustered Index Name: B_B3)
Sunday, March 25, 2012
Delete a SQL Index in a table has become fragmented.
Can Anyone tell me what would happen if I delete an SQL Index in an table.
Will it re-index straight away? If so does the database become useable? will
I still be able to add and retrieve data while it re-index's?I assume you're asking about deleting and recreating an
index:
Technically, you could still work with a table using DBCC
DBREINDEX, although I wouldn't recommend it. That
command can hold locks on the table as it rebuilds the
index that would probably affect usage of the tables in
question. Personally, I schedule all reindexing off
hours and check to make sure that no one is logged in.
DBCC INDEXDEFRAG, on the other hand, is intended to be an
online operation. It will not hold locks for long
periods of time, so database usage will be relatively
unaffected.
DBCC INDEXDEFRAG may not be as effective as DBREINDEX --
the latter fully drops and recreates the index.
Check out BOL for more info on each statement.
Hope that helps.
>--Original Message--
>Can Anyone tell me what would happen if I delete an SQL
Index in an table.
>Will it re-index straight away? If so does the database
become useable? will
>I still be able to add and retrieve data while it re-
index's?
>
>.
>|||If you delete a nonclustered index, then the table and other indexes (if
applicable) are not affected.
If you delete a clustered index, then basically, the table is rebuild,
and all nonclustered indexes are rebuild. During this rebuild the
database is still usuable, but the table will be locked.
Gert-Jan
RockStdy wrote:
> Can Anyone tell me what would happen if I delete an SQL Index in an table.
> Will it re-index straight away? If so does the database become useable? will
> I still be able to add and retrieve data while it re-index's?|||If I use the DBReindex to reindex the index and then stop it with the
database table do a rollback? Or will I be able to process the table over a
period of time able to stop and start the DBReindex?
"RockStdy" <Graham@.Rock.fsbusiness.co.uk> wrote in message
news:bt1gui$geo$1@.news7.svr.pol.co.uk...
> Can Anyone tell me what would happen if I delete an SQL Index in an table.
> Will it re-index straight away? If so does the database become useable?
will
> I still be able to add and retrieve data while it re-index's?
>
>|||DBREINDEX is an all or nothing operation. If you stop it before it is
complete it will rollback to the way it was originally. DBCC INDEXDEFRAG is
what you want to use if you want to stop and restart it or if the operation
needs to be online.
--
Andrew J. Kelly
SQL Server MVP
"RockStdy" <Graham@.Rock.fsbusiness.co.uk> wrote in message
news:bt2cv9$8vq$1@.news8.svr.pol.co.uk...
> If I use the DBReindex to reindex the index and then stop it with the
> database table do a rollback? Or will I be able to process the table over
a
> period of time able to stop and start the DBReindex?
>
> "RockStdy" <Graham@.Rock.fsbusiness.co.uk> wrote in message
> news:bt1gui$geo$1@.news7.svr.pol.co.uk...
> > Can Anyone tell me what would happen if I delete an SQL Index in an
table.
> >
> > Will it re-index straight away? If so does the database become useable?
> will
> > I still be able to add and retrieve data while it re-index's?
> >
> >
> >
>
Will it re-index straight away? If so does the database become useable? will
I still be able to add and retrieve data while it re-index's?I assume you're asking about deleting and recreating an
index:
Technically, you could still work with a table using DBCC
DBREINDEX, although I wouldn't recommend it. That
command can hold locks on the table as it rebuilds the
index that would probably affect usage of the tables in
question. Personally, I schedule all reindexing off
hours and check to make sure that no one is logged in.
DBCC INDEXDEFRAG, on the other hand, is intended to be an
online operation. It will not hold locks for long
periods of time, so database usage will be relatively
unaffected.
DBCC INDEXDEFRAG may not be as effective as DBREINDEX --
the latter fully drops and recreates the index.
Check out BOL for more info on each statement.
Hope that helps.
>--Original Message--
>Can Anyone tell me what would happen if I delete an SQL
Index in an table.
>Will it re-index straight away? If so does the database
become useable? will
>I still be able to add and retrieve data while it re-
index's?
>
>.
>|||If you delete a nonclustered index, then the table and other indexes (if
applicable) are not affected.
If you delete a clustered index, then basically, the table is rebuild,
and all nonclustered indexes are rebuild. During this rebuild the
database is still usuable, but the table will be locked.
Gert-Jan
RockStdy wrote:
> Can Anyone tell me what would happen if I delete an SQL Index in an table.
> Will it re-index straight away? If so does the database become useable? will
> I still be able to add and retrieve data while it re-index's?|||If I use the DBReindex to reindex the index and then stop it with the
database table do a rollback? Or will I be able to process the table over a
period of time able to stop and start the DBReindex?
"RockStdy" <Graham@.Rock.fsbusiness.co.uk> wrote in message
news:bt1gui$geo$1@.news7.svr.pol.co.uk...
> Can Anyone tell me what would happen if I delete an SQL Index in an table.
> Will it re-index straight away? If so does the database become useable?
will
> I still be able to add and retrieve data while it re-index's?
>
>|||DBREINDEX is an all or nothing operation. If you stop it before it is
complete it will rollback to the way it was originally. DBCC INDEXDEFRAG is
what you want to use if you want to stop and restart it or if the operation
needs to be online.
--
Andrew J. Kelly
SQL Server MVP
"RockStdy" <Graham@.Rock.fsbusiness.co.uk> wrote in message
news:bt2cv9$8vq$1@.news8.svr.pol.co.uk...
> If I use the DBReindex to reindex the index and then stop it with the
> database table do a rollback? Or will I be able to process the table over
a
> period of time able to stop and start the DBReindex?
>
> "RockStdy" <Graham@.Rock.fsbusiness.co.uk> wrote in message
> news:bt1gui$geo$1@.news7.svr.pol.co.uk...
> > Can Anyone tell me what would happen if I delete an SQL Index in an
table.
> >
> > Will it re-index straight away? If so does the database become useable?
> will
> > I still be able to add and retrieve data while it re-index's?
> >
> >
> >
>
Monday, March 19, 2012
Defragment tables that have no clustered index
Hi!
We have several SQL Servers in a system that replicates
information in non-realtime between them using MSMQ and
Biztalk and to guarantee uniqueness it uses GUID:s as
primary and foreign keys. When planning this solution we
were recommended by Microsoft to use only non-clustered
indexes on these tables.
Since there are a lot of inserts and updates to the data
in this system we have now got a lot of really
fragmentated tables but since we have no clustered indexes
DBCC INDEXDEFRAG wouldn't help us. Does anyone now how to
solve this problem?
I have searched the newsgroups (and of course "Inside sQL
Server 2000", Hi Kalen! Any suggestions..?) but found no
answers that works. After reading a post here I tried both
DBCC SHRINKDATABASE and SHRINKFILE with NOTRUNCATE but it
hardly effects the terrible scan density (DBCC SHOWCONTIG)
for these tables. The only other solution I have read
about is to use BCP to export and import the tables but
this quite complicated solution can't really be included
in our weekly maintenance job which is what we want.
Please, anyone, suggestions? I can't be the only one with
this problem..?
- AllanRead in "BOL - DBCC DBREINDEX" after reindexing, update
your statistics manually after the reindex.
Greg
>--Original Message--
>Hi!
>We have several SQL Servers in a system that replicates
>information in non-realtime between them using MSMQ and
>Biztalk and to guarantee uniqueness it uses GUID:s as
>primary and foreign keys. When planning this solution we
>were recommended by Microsoft to use only non-clustered
>indexes on these tables.
>Since there are a lot of inserts and updates to the data
>in this system we have now got a lot of really
>fragmentated tables but since we have no clustered
indexes
>DBCC INDEXDEFRAG wouldn't help us. Does anyone now how to
>solve this problem?
>I have searched the newsgroups (and of course "Inside sQL
>Server 2000", Hi Kalen! Any suggestions..?) but found no
>answers that works. After reading a post here I tried
both
>DBCC SHRINKDATABASE and SHRINKFILE with NOTRUNCATE but it
>hardly effects the terrible scan density (DBCC
SHOWCONTIG)
>for these tables. The only other solution I have read
>about is to use BCP to export and import the tables but
>this quite complicated solution can't really be included
>in our weekly maintenance job which is what we want.
>Please, anyone, suggestions? I can't be the only one with
>this problem..?
>- Allan
>.
>|||There's no easy way to reorg (or perhaps "compact" is a better word as the data isn't sorted in any
way) for a heap. Two ways I can think of:
Create a clustered index and drop it.
Export/import of the data.
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as ugroup=microsoft.public.sqlserver
"Allan" <allan@.post.reply.in.the.newsgroup> wrote in message
news:03d101c38386$33166d70$a301280a@.phx.gbl...
> As far as I have understood (and tested) neither DBCC
> DBREINDEX or DBCC INDEXDEFRAG will help me. Since I don't
> have clustered indexes on these tables the data is not
> stored on the leaf level of the index but in a heap. What
> I want to know is how to defragment this heap..
> >--Original Message--
> >Read in "BOL - DBCC DBREINDEX" after reindexing, update
> >your statistics manually after the reindex.
> >
> >Greg
> >
> >>--Original Message--
> >>Hi!
> >>
> >>We have several SQL Servers in a system that replicates
> >>information in non-realtime between them using MSMQ and
> >>Biztalk and to guarantee uniqueness it uses GUID:s as
> >>primary and foreign keys. When planning this solution we
> >>were recommended by Microsoft to use only non-clustered
> >>indexes on these tables.
> >>
> >>Since there are a lot of inserts and updates to the data
> >>in this system we have now got a lot of really
> >>fragmentated tables but since we have no clustered
> >indexes
> >>DBCC INDEXDEFRAG wouldn't help us. Does anyone now how
> to
> >>solve this problem?
> >>
> >>I have searched the newsgroups (and of course "Inside
> sQL
> >>Server 2000", Hi Kalen! Any suggestions..?) but found no
> >>answers that works. After reading a post here I tried
> >both
> >>DBCC SHRINKDATABASE and SHRINKFILE with NOTRUNCATE but
> it
> >>hardly effects the terrible scan density (DBCC
> >SHOWCONTIG)
> >>for these tables. The only other solution I have read
> >>about is to use BCP to export and import the tables but
> >>this quite complicated solution can't really be included
> >>in our weekly maintenance job which is what we want.
> >>
> >>Please, anyone, suggestions? I can't be the only one
> with
> >>this problem..?
> >>
> >>- Allan
> >>
> >>.
> >>
> >.
> >|||Yes Tibor has it correct. That's one of the reasons I suggest that most
tables have a clustered index.
--
Andrew J. Kelly
SQL Server MVP
"Tibor Karaszi" <tibor.please_reply_to_public_forum.karaszi@.cornerstone.se>
wrote in message news:%23W979hBhDHA.616@.TK2MSFTNGP11.phx.gbl...
> There's no easy way to reorg (or perhaps "compact" is a better word as the
data isn't sorted in any
> way) for a heap. Two ways I can think of:
> Create a clustered index and drop it.
> Export/import of the data.
> --
> Tibor Karaszi, SQL Server MVP
> Archive at: http://groups.google.com/groups?oi=djq&as
ugroup=microsoft.public.sqlserver
>
> "Allan" <allan@.post.reply.in.the.newsgroup> wrote in message
> news:03d101c38386$33166d70$a301280a@.phx.gbl...
> > As far as I have understood (and tested) neither DBCC
> > DBREINDEX or DBCC INDEXDEFRAG will help me. Since I don't
> > have clustered indexes on these tables the data is not
> > stored on the leaf level of the index but in a heap. What
> > I want to know is how to defragment this heap..
> >
> > >--Original Message--
> > >Read in "BOL - DBCC DBREINDEX" after reindexing, update
> > >your statistics manually after the reindex.
> > >
> > >Greg
> > >
> > >>--Original Message--
> > >>Hi!
> > >>
> > >>We have several SQL Servers in a system that replicates
> > >>information in non-realtime between them using MSMQ and
> > >>Biztalk and to guarantee uniqueness it uses GUID:s as
> > >>primary and foreign keys. When planning this solution we
> > >>were recommended by Microsoft to use only non-clustered
> > >>indexes on these tables.
> > >>
> > >>Since there are a lot of inserts and updates to the data
> > >>in this system we have now got a lot of really
> > >>fragmentated tables but since we have no clustered
> > >indexes
> > >>DBCC INDEXDEFRAG wouldn't help us. Does anyone now how
> > to
> > >>solve this problem?
> > >>
> > >>I have searched the newsgroups (and of course "Inside
> > sQL
> > >>Server 2000", Hi Kalen! Any suggestions..?) but found no
> > >>answers that works. After reading a post here I tried
> > >both
> > >>DBCC SHRINKDATABASE and SHRINKFILE with NOTRUNCATE but
> > it
> > >>hardly effects the terrible scan density (DBCC
> > >SHOWCONTIG)
> > >>for these tables. The only other solution I have read
> > >>about is to use BCP to export and import the tables but
> > >>this quite complicated solution can't really be included
> > >>in our weekly maintenance job which is what we want.
> > >>
> > >>Please, anyone, suggestions? I can't be the only one
> > with
> > >>this problem..?
> > >>
> > >>- Allan
> > >>
> > >>.
> > >>
> > >.
> > >
>
We have several SQL Servers in a system that replicates
information in non-realtime between them using MSMQ and
Biztalk and to guarantee uniqueness it uses GUID:s as
primary and foreign keys. When planning this solution we
were recommended by Microsoft to use only non-clustered
indexes on these tables.
Since there are a lot of inserts and updates to the data
in this system we have now got a lot of really
fragmentated tables but since we have no clustered indexes
DBCC INDEXDEFRAG wouldn't help us. Does anyone now how to
solve this problem?
I have searched the newsgroups (and of course "Inside sQL
Server 2000", Hi Kalen! Any suggestions..?) but found no
answers that works. After reading a post here I tried both
DBCC SHRINKDATABASE and SHRINKFILE with NOTRUNCATE but it
hardly effects the terrible scan density (DBCC SHOWCONTIG)
for these tables. The only other solution I have read
about is to use BCP to export and import the tables but
this quite complicated solution can't really be included
in our weekly maintenance job which is what we want.
Please, anyone, suggestions? I can't be the only one with
this problem..?
- AllanRead in "BOL - DBCC DBREINDEX" after reindexing, update
your statistics manually after the reindex.
Greg
>--Original Message--
>Hi!
>We have several SQL Servers in a system that replicates
>information in non-realtime between them using MSMQ and
>Biztalk and to guarantee uniqueness it uses GUID:s as
>primary and foreign keys. When planning this solution we
>were recommended by Microsoft to use only non-clustered
>indexes on these tables.
>Since there are a lot of inserts and updates to the data
>in this system we have now got a lot of really
>fragmentated tables but since we have no clustered
indexes
>DBCC INDEXDEFRAG wouldn't help us. Does anyone now how to
>solve this problem?
>I have searched the newsgroups (and of course "Inside sQL
>Server 2000", Hi Kalen! Any suggestions..?) but found no
>answers that works. After reading a post here I tried
both
>DBCC SHRINKDATABASE and SHRINKFILE with NOTRUNCATE but it
>hardly effects the terrible scan density (DBCC
SHOWCONTIG)
>for these tables. The only other solution I have read
>about is to use BCP to export and import the tables but
>this quite complicated solution can't really be included
>in our weekly maintenance job which is what we want.
>Please, anyone, suggestions? I can't be the only one with
>this problem..?
>- Allan
>.
>|||There's no easy way to reorg (or perhaps "compact" is a better word as the data isn't sorted in any
way) for a heap. Two ways I can think of:
Create a clustered index and drop it.
Export/import of the data.
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as ugroup=microsoft.public.sqlserver
"Allan" <allan@.post.reply.in.the.newsgroup> wrote in message
news:03d101c38386$33166d70$a301280a@.phx.gbl...
> As far as I have understood (and tested) neither DBCC
> DBREINDEX or DBCC INDEXDEFRAG will help me. Since I don't
> have clustered indexes on these tables the data is not
> stored on the leaf level of the index but in a heap. What
> I want to know is how to defragment this heap..
> >--Original Message--
> >Read in "BOL - DBCC DBREINDEX" after reindexing, update
> >your statistics manually after the reindex.
> >
> >Greg
> >
> >>--Original Message--
> >>Hi!
> >>
> >>We have several SQL Servers in a system that replicates
> >>information in non-realtime between them using MSMQ and
> >>Biztalk and to guarantee uniqueness it uses GUID:s as
> >>primary and foreign keys. When planning this solution we
> >>were recommended by Microsoft to use only non-clustered
> >>indexes on these tables.
> >>
> >>Since there are a lot of inserts and updates to the data
> >>in this system we have now got a lot of really
> >>fragmentated tables but since we have no clustered
> >indexes
> >>DBCC INDEXDEFRAG wouldn't help us. Does anyone now how
> to
> >>solve this problem?
> >>
> >>I have searched the newsgroups (and of course "Inside
> sQL
> >>Server 2000", Hi Kalen! Any suggestions..?) but found no
> >>answers that works. After reading a post here I tried
> >both
> >>DBCC SHRINKDATABASE and SHRINKFILE with NOTRUNCATE but
> it
> >>hardly effects the terrible scan density (DBCC
> >SHOWCONTIG)
> >>for these tables. The only other solution I have read
> >>about is to use BCP to export and import the tables but
> >>this quite complicated solution can't really be included
> >>in our weekly maintenance job which is what we want.
> >>
> >>Please, anyone, suggestions? I can't be the only one
> with
> >>this problem..?
> >>
> >>- Allan
> >>
> >>.
> >>
> >.
> >|||Yes Tibor has it correct. That's one of the reasons I suggest that most
tables have a clustered index.
--
Andrew J. Kelly
SQL Server MVP
"Tibor Karaszi" <tibor.please_reply_to_public_forum.karaszi@.cornerstone.se>
wrote in message news:%23W979hBhDHA.616@.TK2MSFTNGP11.phx.gbl...
> There's no easy way to reorg (or perhaps "compact" is a better word as the
data isn't sorted in any
> way) for a heap. Two ways I can think of:
> Create a clustered index and drop it.
> Export/import of the data.
> --
> Tibor Karaszi, SQL Server MVP
> Archive at: http://groups.google.com/groups?oi=djq&as
ugroup=microsoft.public.sqlserver
>
> "Allan" <allan@.post.reply.in.the.newsgroup> wrote in message
> news:03d101c38386$33166d70$a301280a@.phx.gbl...
> > As far as I have understood (and tested) neither DBCC
> > DBREINDEX or DBCC INDEXDEFRAG will help me. Since I don't
> > have clustered indexes on these tables the data is not
> > stored on the leaf level of the index but in a heap. What
> > I want to know is how to defragment this heap..
> >
> > >--Original Message--
> > >Read in "BOL - DBCC DBREINDEX" after reindexing, update
> > >your statistics manually after the reindex.
> > >
> > >Greg
> > >
> > >>--Original Message--
> > >>Hi!
> > >>
> > >>We have several SQL Servers in a system that replicates
> > >>information in non-realtime between them using MSMQ and
> > >>Biztalk and to guarantee uniqueness it uses GUID:s as
> > >>primary and foreign keys. When planning this solution we
> > >>were recommended by Microsoft to use only non-clustered
> > >>indexes on these tables.
> > >>
> > >>Since there are a lot of inserts and updates to the data
> > >>in this system we have now got a lot of really
> > >>fragmentated tables but since we have no clustered
> > >indexes
> > >>DBCC INDEXDEFRAG wouldn't help us. Does anyone now how
> > to
> > >>solve this problem?
> > >>
> > >>I have searched the newsgroups (and of course "Inside
> > sQL
> > >>Server 2000", Hi Kalen! Any suggestions..?) but found no
> > >>answers that works. After reading a post here I tried
> > >both
> > >>DBCC SHRINKDATABASE and SHRINKFILE with NOTRUNCATE but
> > it
> > >>hardly effects the terrible scan density (DBCC
> > >SHOWCONTIG)
> > >>for these tables. The only other solution I have read
> > >>about is to use BCP to export and import the tables but
> > >>this quite complicated solution can't really be included
> > >>in our weekly maintenance job which is what we want.
> > >>
> > >>Please, anyone, suggestions? I can't be the only one
> > with
> > >>this problem..?
> > >>
> > >>- Allan
> > >>
> > >>.
> > >>
> > >.
> > >
>
Sunday, March 11, 2012
Defrag large production table
I currently have one table, highly utilized, and it is 80% fragmented. I
need to defrag the index, but due to log shipping, I am unable to defrag the
index without creating a 2 gig log. A log this size will hose the pipe
between our production and offsite sql server. Any ideas on how to defrag
without creating such a huge log?Hi
You are probably going to take this hit regardless, what you may want to try
is to stop the log shipping and re-syncronise afterwards. Alternatively drop
all the indexes and re-create them one at a time allowing the log to be
shipped in-between ( I would also make sure that all indexes are necessary
and test the system with alternative indexes).
What you would need to ascertain is why the index is so fragmented and try
and rectify this.
John
"Patrick" wrote:
> I currently have one table, highly utilized, and it is 80% fragmented. I
> need to defrag the index, but due to log shipping, I am unable to defrag t
he
> index without creating a 2 gig log. A log this size will hose the pipe
> between our production and offsite sql server. Any ideas on how to defrag
> without creating such a huge log?
>|||I forgot to add...
Have you considered partitioning this table?
John
"Patrick" wrote:
> I currently have one table, highly utilized, and it is 80% fragmented. I
> need to defrag the index, but due to log shipping, I am unable to defrag t
he
> index without creating a 2 gig log. A log this size will hose the pipe
> between our production and offsite sql server. Any ideas on how to defrag
> without creating such a huge log?
>|||Just started a new job and came accross tables fragmented 80% or more;
however, they also have log shipping. Users are complaining about
performance and I can see why, but resolving the fragmentation issue is more
of a task than first anticipated.
I will look into partitioning the table, but I want to see if I defrag some
of the tables before I start the partitioning project.
"John Bell" wrote:
[vbcol=seagreen]
> I forgot to add...
> Have you considered partitioning this table?
> John
> "Patrick" wrote:
>|||Hi Patrick
It sounds like you certainly need to look at what the indexes are and if
they are really needed or if a better indexing scheme could be employed.
John
"Patrick" wrote:
[vbcol=seagreen]
> Just started a new job and came accross tables fragmented 80% or more;
> however, they also have log shipping. Users are complaining about
> performance and I can see why, but resolving the fragmentation issue is mo
re
> of a task than first anticipated.
> I will look into partitioning the table, but I want to see if I defrag som
e
> of the tables before I start the partitioning project.
> "John Bell" wrote:
>|||Will switching from Full to Bulk recovery during DEFRAG and then switching
back from Bulk to Full recovery break log shipping?
"John Bell" wrote:
[vbcol=seagreen]
> Hi Patrick
> It sounds like you certainly need to look at what the indexes are and if
> they are really needed or if a better indexing scheme could be employed.
> John
> "Patrick" wrote:
>|||Hi Patrick
You can use log shipping on a BULK_LOGGED database, but I am not sure if you
can switch half way through.
Have you tried decreasing the log shipping interval (make it more frequent)?
Have easy would it be to switch of log shipping during this interval and
restore afterwards?
John
"Patrick" wrote:
[vbcol=seagreen]
> Will switching from Full to Bulk recovery during DEFRAG and then switching
> back from Bulk to Full recovery break log shipping?
> "John Bell" wrote:
>|||That won't help you at all. Creating an index in Bulk Logged mode will send
less data to the log. But the log backup will be just as large as before
since it copies the extents that were modified in a Bulk Logged operation to
the log backup file. IndexDefrag has the potential to send even more data
to the log file than a DBREINDEX. This is especially true if the file is
heavily fragmented. The only thing is you can control it better as to when
this happens. You can start INDEXDEFRAG and stop it shortly afterwards.
Issue a Log backup then restart it, stop it etc. until it is complete. You
can't get away from logging the data but you do have some options to deal
with it. Ultimately if this reindexing is a problem then Johns suggestions
for partitioning and proper fill factors is something you may want to look
into.
Andrew J. Kelly SQL MVP
"Patrick" <Patrick@.discussions.microsoft.com> wrote in message
news:D5B901A7-135E-40D3-AAFF-B9CC30D533E6@.microsoft.com...[vbcol=seagreen]
> Will switching from Full to Bulk recovery during DEFRAG and then switching
> back from Bulk to Full recovery break log shipping?
> "John Bell" wrote:
>|||Hi
A different approach to the stop/backup/restart method might be to use DBCC
SHOWCONTIG and DBCC INDEXDEFRAG with decreasing levels of fragmentation,
therefore limiting the number of indexes defragmented per go. Once all
completed then do a final DBCC DBREINDEX.
John
"Patrick" wrote:
[vbcol=seagreen]
> Will switching from Full to Bulk recovery during DEFRAG and then switching
> back from Bulk to Full recovery break log shipping?
> "John Bell" wrote:
>|||John,
Can you explain what you mean by (or want to accomplish) by doing a final
DBREINDEX? I am confused by the statement.
Andrew J. Kelly SQL MVP
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:9E899144-8414-435B-B6E9-374365B6015A@.microsoft.com...[vbcol=seagreen]
> Hi
> A different approach to the stop/backup/restart method might be to use
> DBCC
> SHOWCONTIG and DBCC INDEXDEFRAG with decreasing levels of fragmentation,
> therefore limiting the number of indexes defragmented per go. Once all
> completed then do a final DBCC DBREINDEX.
> John
> "Patrick" wrote:
>
need to defrag the index, but due to log shipping, I am unable to defrag the
index without creating a 2 gig log. A log this size will hose the pipe
between our production and offsite sql server. Any ideas on how to defrag
without creating such a huge log?Hi
You are probably going to take this hit regardless, what you may want to try
is to stop the log shipping and re-syncronise afterwards. Alternatively drop
all the indexes and re-create them one at a time allowing the log to be
shipped in-between ( I would also make sure that all indexes are necessary
and test the system with alternative indexes).
What you would need to ascertain is why the index is so fragmented and try
and rectify this.
John
"Patrick" wrote:
> I currently have one table, highly utilized, and it is 80% fragmented. I
> need to defrag the index, but due to log shipping, I am unable to defrag t
he
> index without creating a 2 gig log. A log this size will hose the pipe
> between our production and offsite sql server. Any ideas on how to defrag
> without creating such a huge log?
>|||I forgot to add...
Have you considered partitioning this table?
John
"Patrick" wrote:
> I currently have one table, highly utilized, and it is 80% fragmented. I
> need to defrag the index, but due to log shipping, I am unable to defrag t
he
> index without creating a 2 gig log. A log this size will hose the pipe
> between our production and offsite sql server. Any ideas on how to defrag
> without creating such a huge log?
>|||Just started a new job and came accross tables fragmented 80% or more;
however, they also have log shipping. Users are complaining about
performance and I can see why, but resolving the fragmentation issue is more
of a task than first anticipated.
I will look into partitioning the table, but I want to see if I defrag some
of the tables before I start the partitioning project.
"John Bell" wrote:
[vbcol=seagreen]
> I forgot to add...
> Have you considered partitioning this table?
> John
> "Patrick" wrote:
>|||Hi Patrick
It sounds like you certainly need to look at what the indexes are and if
they are really needed or if a better indexing scheme could be employed.
John
"Patrick" wrote:
[vbcol=seagreen]
> Just started a new job and came accross tables fragmented 80% or more;
> however, they also have log shipping. Users are complaining about
> performance and I can see why, but resolving the fragmentation issue is mo
re
> of a task than first anticipated.
> I will look into partitioning the table, but I want to see if I defrag som
e
> of the tables before I start the partitioning project.
> "John Bell" wrote:
>|||Will switching from Full to Bulk recovery during DEFRAG and then switching
back from Bulk to Full recovery break log shipping?
"John Bell" wrote:
[vbcol=seagreen]
> Hi Patrick
> It sounds like you certainly need to look at what the indexes are and if
> they are really needed or if a better indexing scheme could be employed.
> John
> "Patrick" wrote:
>|||Hi Patrick
You can use log shipping on a BULK_LOGGED database, but I am not sure if you
can switch half way through.
Have you tried decreasing the log shipping interval (make it more frequent)?
Have easy would it be to switch of log shipping during this interval and
restore afterwards?
John
"Patrick" wrote:
[vbcol=seagreen]
> Will switching from Full to Bulk recovery during DEFRAG and then switching
> back from Bulk to Full recovery break log shipping?
> "John Bell" wrote:
>|||That won't help you at all. Creating an index in Bulk Logged mode will send
less data to the log. But the log backup will be just as large as before
since it copies the extents that were modified in a Bulk Logged operation to
the log backup file. IndexDefrag has the potential to send even more data
to the log file than a DBREINDEX. This is especially true if the file is
heavily fragmented. The only thing is you can control it better as to when
this happens. You can start INDEXDEFRAG and stop it shortly afterwards.
Issue a Log backup then restart it, stop it etc. until it is complete. You
can't get away from logging the data but you do have some options to deal
with it. Ultimately if this reindexing is a problem then Johns suggestions
for partitioning and proper fill factors is something you may want to look
into.
Andrew J. Kelly SQL MVP
"Patrick" <Patrick@.discussions.microsoft.com> wrote in message
news:D5B901A7-135E-40D3-AAFF-B9CC30D533E6@.microsoft.com...[vbcol=seagreen]
> Will switching from Full to Bulk recovery during DEFRAG and then switching
> back from Bulk to Full recovery break log shipping?
> "John Bell" wrote:
>|||Hi
A different approach to the stop/backup/restart method might be to use DBCC
SHOWCONTIG and DBCC INDEXDEFRAG with decreasing levels of fragmentation,
therefore limiting the number of indexes defragmented per go. Once all
completed then do a final DBCC DBREINDEX.
John
"Patrick" wrote:
[vbcol=seagreen]
> Will switching from Full to Bulk recovery during DEFRAG and then switching
> back from Bulk to Full recovery break log shipping?
> "John Bell" wrote:
>|||John,
Can you explain what you mean by (or want to accomplish) by doing a final
DBREINDEX? I am confused by the statement.
Andrew J. Kelly SQL MVP
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:9E899144-8414-435B-B6E9-374365B6015A@.microsoft.com...[vbcol=seagreen]
> Hi
> A different approach to the stop/backup/restart method might be to use
> DBCC
> SHOWCONTIG and DBCC INDEXDEFRAG with decreasing levels of fragmentation,
> therefore limiting the number of indexes defragmented per go. Once all
> completed then do a final DBCC DBREINDEX.
> John
> "Patrick" wrote:
>
Defrag large production table
I currently have one table, highly utilized, and it is 80% fragmented. I
need to defrag the index, but due to log shipping, I am unable to defrag the
index without creating a 2 gig log. A log this size will hose the pipe
between our production and offsite sql server. Any ideas on how to defrag
without creating such a huge log?Hi
You are probably going to take this hit regardless, what you may want to try
is to stop the log shipping and re-syncronise afterwards. Alternatively drop
all the indexes and re-create them one at a time allowing the log to be
shipped in-between ( I would also make sure that all indexes are necessary
and test the system with alternative indexes).
What you would need to ascertain is why the index is so fragmented and try
and rectify this.
John
"Patrick" wrote:
> I currently have one table, highly utilized, and it is 80% fragmented. I
> need to defrag the index, but due to log shipping, I am unable to defrag the
> index without creating a 2 gig log. A log this size will hose the pipe
> between our production and offsite sql server. Any ideas on how to defrag
> without creating such a huge log?
>|||I forgot to add...
Have you considered partitioning this table?
John
"Patrick" wrote:
> I currently have one table, highly utilized, and it is 80% fragmented. I
> need to defrag the index, but due to log shipping, I am unable to defrag the
> index without creating a 2 gig log. A log this size will hose the pipe
> between our production and offsite sql server. Any ideas on how to defrag
> without creating such a huge log?
>|||Just started a new job and came accross tables fragmented 80% or more;
however, they also have log shipping. Users are complaining about
performance and I can see why, but resolving the fragmentation issue is more
of a task than first anticipated.
I will look into partitioning the table, but I want to see if I defrag some
of the tables before I start the partitioning project.
"John Bell" wrote:
> I forgot to add...
> Have you considered partitioning this table?
> John
> "Patrick" wrote:
> > I currently have one table, highly utilized, and it is 80% fragmented. I
> > need to defrag the index, but due to log shipping, I am unable to defrag the
> > index without creating a 2 gig log. A log this size will hose the pipe
> > between our production and offsite sql server. Any ideas on how to defrag
> > without creating such a huge log?
> >
> >|||Hi Patrick
It sounds like you certainly need to look at what the indexes are and if
they are really needed or if a better indexing scheme could be employed.
John
"Patrick" wrote:
> Just started a new job and came accross tables fragmented 80% or more;
> however, they also have log shipping. Users are complaining about
> performance and I can see why, but resolving the fragmentation issue is more
> of a task than first anticipated.
> I will look into partitioning the table, but I want to see if I defrag some
> of the tables before I start the partitioning project.
> "John Bell" wrote:
> > I forgot to add...
> >
> > Have you considered partitioning this table?
> >
> > John
> >
> > "Patrick" wrote:
> >
> > > I currently have one table, highly utilized, and it is 80% fragmented. I
> > > need to defrag the index, but due to log shipping, I am unable to defrag the
> > > index without creating a 2 gig log. A log this size will hose the pipe
> > > between our production and offsite sql server. Any ideas on how to defrag
> > > without creating such a huge log?
> > >
> > >|||Will switching from Full to Bulk recovery during DEFRAG and then switching
back from Bulk to Full recovery break log shipping?
"John Bell" wrote:
> Hi Patrick
> It sounds like you certainly need to look at what the indexes are and if
> they are really needed or if a better indexing scheme could be employed.
> John
> "Patrick" wrote:
> > Just started a new job and came accross tables fragmented 80% or more;
> > however, they also have log shipping. Users are complaining about
> > performance and I can see why, but resolving the fragmentation issue is more
> > of a task than first anticipated.
> >
> > I will look into partitioning the table, but I want to see if I defrag some
> > of the tables before I start the partitioning project.
> >
> > "John Bell" wrote:
> >
> > > I forgot to add...
> > >
> > > Have you considered partitioning this table?
> > >
> > > John
> > >
> > > "Patrick" wrote:
> > >
> > > > I currently have one table, highly utilized, and it is 80% fragmented. I
> > > > need to defrag the index, but due to log shipping, I am unable to defrag the
> > > > index without creating a 2 gig log. A log this size will hose the pipe
> > > > between our production and offsite sql server. Any ideas on how to defrag
> > > > without creating such a huge log?
> > > >
> > > >|||Hi Patrick
You can use log shipping on a BULK_LOGGED database, but I am not sure if you
can switch half way through.
Have you tried decreasing the log shipping interval (make it more frequent)?
Have easy would it be to switch of log shipping during this interval and
restore afterwards?
John
"Patrick" wrote:
> Will switching from Full to Bulk recovery during DEFRAG and then switching
> back from Bulk to Full recovery break log shipping?
> "John Bell" wrote:
> > Hi Patrick
> >
> > It sounds like you certainly need to look at what the indexes are and if
> > they are really needed or if a better indexing scheme could be employed.
> >
> > John
> >
> > "Patrick" wrote:
> >
> > > Just started a new job and came accross tables fragmented 80% or more;
> > > however, they also have log shipping. Users are complaining about
> > > performance and I can see why, but resolving the fragmentation issue is more
> > > of a task than first anticipated.
> > >
> > > I will look into partitioning the table, but I want to see if I defrag some
> > > of the tables before I start the partitioning project.
> > >
> > > "John Bell" wrote:
> > >
> > > > I forgot to add...
> > > >
> > > > Have you considered partitioning this table?
> > > >
> > > > John
> > > >
> > > > "Patrick" wrote:
> > > >
> > > > > I currently have one table, highly utilized, and it is 80% fragmented. I
> > > > > need to defrag the index, but due to log shipping, I am unable to defrag the
> > > > > index without creating a 2 gig log. A log this size will hose the pipe
> > > > > between our production and offsite sql server. Any ideas on how to defrag
> > > > > without creating such a huge log?
> > > > >
> > > > >|||That won't help you at all. Creating an index in Bulk Logged mode will send
less data to the log. But the log backup will be just as large as before
since it copies the extents that were modified in a Bulk Logged operation to
the log backup file. IndexDefrag has the potential to send even more data
to the log file than a DBREINDEX. This is especially true if the file is
heavily fragmented. The only thing is you can control it better as to when
this happens. You can start INDEXDEFRAG and stop it shortly afterwards.
Issue a Log backup then restart it, stop it etc. until it is complete. You
can't get away from logging the data but you do have some options to deal
with it. Ultimately if this reindexing is a problem then Johns suggestions
for partitioning and proper fill factors is something you may want to look
into.
--
Andrew J. Kelly SQL MVP
"Patrick" <Patrick@.discussions.microsoft.com> wrote in message
news:D5B901A7-135E-40D3-AAFF-B9CC30D533E6@.microsoft.com...
> Will switching from Full to Bulk recovery during DEFRAG and then switching
> back from Bulk to Full recovery break log shipping?
> "John Bell" wrote:
>> Hi Patrick
>> It sounds like you certainly need to look at what the indexes are and if
>> they are really needed or if a better indexing scheme could be employed.
>> John
>> "Patrick" wrote:
>> > Just started a new job and came accross tables fragmented 80% or more;
>> > however, they also have log shipping. Users are complaining about
>> > performance and I can see why, but resolving the fragmentation issue is
>> > more
>> > of a task than first anticipated.
>> >
>> > I will look into partitioning the table, but I want to see if I defrag
>> > some
>> > of the tables before I start the partitioning project.
>> >
>> > "John Bell" wrote:
>> >
>> > > I forgot to add...
>> > >
>> > > Have you considered partitioning this table?
>> > >
>> > > John
>> > >
>> > > "Patrick" wrote:
>> > >
>> > > > I currently have one table, highly utilized, and it is 80%
>> > > > fragmented. I
>> > > > need to defrag the index, but due to log shipping, I am unable to
>> > > > defrag the
>> > > > index without creating a 2 gig log. A log this size will hose the
>> > > > pipe
>> > > > between our production and offsite sql server. Any ideas on how to
>> > > > defrag
>> > > > without creating such a huge log?
>> > > >
>> > > >|||Hi
A different approach to the stop/backup/restart method might be to use DBCC
SHOWCONTIG and DBCC INDEXDEFRAG with decreasing levels of fragmentation,
therefore limiting the number of indexes defragmented per go. Once all
completed then do a final DBCC DBREINDEX.
John
"Patrick" wrote:
> Will switching from Full to Bulk recovery during DEFRAG and then switching
> back from Bulk to Full recovery break log shipping?
> "John Bell" wrote:
> > Hi Patrick
> >
> > It sounds like you certainly need to look at what the indexes are and if
> > they are really needed or if a better indexing scheme could be employed.
> >
> > John
> >
> > "Patrick" wrote:
> >
> > > Just started a new job and came accross tables fragmented 80% or more;
> > > however, they also have log shipping. Users are complaining about
> > > performance and I can see why, but resolving the fragmentation issue is more
> > > of a task than first anticipated.
> > >
> > > I will look into partitioning the table, but I want to see if I defrag some
> > > of the tables before I start the partitioning project.
> > >
> > > "John Bell" wrote:
> > >
> > > > I forgot to add...
> > > >
> > > > Have you considered partitioning this table?
> > > >
> > > > John
> > > >
> > > > "Patrick" wrote:
> > > >
> > > > > I currently have one table, highly utilized, and it is 80% fragmented. I
> > > > > need to defrag the index, but due to log shipping, I am unable to defrag the
> > > > > index without creating a 2 gig log. A log this size will hose the pipe
> > > > > between our production and offsite sql server. Any ideas on how to defrag
> > > > > without creating such a huge log?
> > > > >
> > > > >|||John,
Can you explain what you mean by (or want to accomplish) by doing a final
DBREINDEX? I am confused by the statement.
--
Andrew J. Kelly SQL MVP
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:9E899144-8414-435B-B6E9-374365B6015A@.microsoft.com...
> Hi
> A different approach to the stop/backup/restart method might be to use
> DBCC
> SHOWCONTIG and DBCC INDEXDEFRAG with decreasing levels of fragmentation,
> therefore limiting the number of indexes defragmented per go. Once all
> completed then do a final DBCC DBREINDEX.
> John
> "Patrick" wrote:
>> Will switching from Full to Bulk recovery during DEFRAG and then
>> switching
>> back from Bulk to Full recovery break log shipping?
>> "John Bell" wrote:
>> > Hi Patrick
>> >
>> > It sounds like you certainly need to look at what the indexes are and
>> > if
>> > they are really needed or if a better indexing scheme could be
>> > employed.
>> >
>> > John
>> >
>> > "Patrick" wrote:
>> >
>> > > Just started a new job and came accross tables fragmented 80% or
>> > > more;
>> > > however, they also have log shipping. Users are complaining about
>> > > performance and I can see why, but resolving the fragmentation issue
>> > > is more
>> > > of a task than first anticipated.
>> > >
>> > > I will look into partitioning the table, but I want to see if I
>> > > defrag some
>> > > of the tables before I start the partitioning project.
>> > >
>> > > "John Bell" wrote:
>> > >
>> > > > I forgot to add...
>> > > >
>> > > > Have you considered partitioning this table?
>> > > >
>> > > > John
>> > > >
>> > > > "Patrick" wrote:
>> > > >
>> > > > > I currently have one table, highly utilized, and it is 80%
>> > > > > fragmented. I
>> > > > > need to defrag the index, but due to log shipping, I am unable to
>> > > > > defrag the
>> > > > > index without creating a 2 gig log. A log this size will hose
>> > > > > the pipe
>> > > > > between our production and offsite sql server. Any ideas on how
>> > > > > to defrag
>> > > > > without creating such a huge log?
>> > > > >
>> > > > >|||Hi Andrew
The final dbreindex would be to defragment root and intermediate level
pages, and hopefully if the leaf level pages were already defragmented, less
work would be required to defragment them. It is a theory that would need to
be tested, certainly if the root/intermediate nodes were very fragmented
there may be less gain from the indexdefrags, there may be a point where you
don't do the indexdefrags if DBCC SHOWCONTIG shows non-leaf levels to be very
fragmented.
John
"Andrew J. Kelly" wrote:
> John,
> Can you explain what you mean by (or want to accomplish) by doing a final
> DBREINDEX? I am confused by the statement.
> --
> Andrew J. Kelly SQL MVP
>
> "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
> news:9E899144-8414-435B-B6E9-374365B6015A@.microsoft.com...
> > Hi
> >
> > A different approach to the stop/backup/restart method might be to use
> > DBCC
> > SHOWCONTIG and DBCC INDEXDEFRAG with decreasing levels of fragmentation,
> > therefore limiting the number of indexes defragmented per go. Once all
> > completed then do a final DBCC DBREINDEX.
> >
> > John
> >
> > "Patrick" wrote:
> >
> >> Will switching from Full to Bulk recovery during DEFRAG and then
> >> switching
> >> back from Bulk to Full recovery break log shipping?
> >>
> >> "John Bell" wrote:
> >>
> >> > Hi Patrick
> >> >
> >> > It sounds like you certainly need to look at what the indexes are and
> >> > if
> >> > they are really needed or if a better indexing scheme could be
> >> > employed.
> >> >
> >> > John
> >> >
> >> > "Patrick" wrote:
> >> >
> >> > > Just started a new job and came accross tables fragmented 80% or
> >> > > more;
> >> > > however, they also have log shipping. Users are complaining about
> >> > > performance and I can see why, but resolving the fragmentation issue
> >> > > is more
> >> > > of a task than first anticipated.
> >> > >
> >> > > I will look into partitioning the table, but I want to see if I
> >> > > defrag some
> >> > > of the tables before I start the partitioning project.
> >> > >
> >> > > "John Bell" wrote:
> >> > >
> >> > > > I forgot to add...
> >> > > >
> >> > > > Have you considered partitioning this table?
> >> > > >
> >> > > > John
> >> > > >
> >> > > > "Patrick" wrote:
> >> > > >
> >> > > > > I currently have one table, highly utilized, and it is 80%
> >> > > > > fragmented. I
> >> > > > > need to defrag the index, but due to log shipping, I am unable to
> >> > > > > defrag the
> >> > > > > index without creating a 2 gig log. A log this size will hose
> >> > > > > the pipe
> >> > > > > between our production and offsite sql server. Any ideas on how
> >> > > > > to defrag
> >> > > > > without creating such a huge log?
> >> > > > >
> >> > > > >
>
>|||OK I see what you were after now. But unfortunately it would not matter too
much in his case if the table was heavily or lightly fragmented. When you
issue a DBREINDEX it will completely rebuild the indexes in a new section of
the data files. This will always result in basically the same amount of
pages or extents being logged or needed to be backed up in the case of
BulkLogged recovery model. It is true that the work itself may be a little
easier to sort the data for the rebuild when there is less fragmentation.
But as far as log shipping goes it would not matter either way. Actually
that way would be worse since you would log it twice. Once for Indexdefrag
and once for DBreindex.
--
Andrew J. Kelly SQL MVP
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:082B1590-3F81-4D82-8AD3-F008C974EC4B@.microsoft.com...
> Hi Andrew
> The final dbreindex would be to defragment root and intermediate level
> pages, and hopefully if the leaf level pages were already defragmented,
> less
> work would be required to defragment them. It is a theory that would need
> to
> be tested, certainly if the root/intermediate nodes were very fragmented
> there may be less gain from the indexdefrags, there may be a point where
> you
> don't do the indexdefrags if DBCC SHOWCONTIG shows non-leaf levels to be
> very
> fragmented.
> John
>
> "Andrew J. Kelly" wrote:
>> John,
>> Can you explain what you mean by (or want to accomplish) by doing a final
>> DBREINDEX? I am confused by the statement.
>> --
>> Andrew J. Kelly SQL MVP
>>
>> "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
>> news:9E899144-8414-435B-B6E9-374365B6015A@.microsoft.com...
>> > Hi
>> >
>> > A different approach to the stop/backup/restart method might be to use
>> > DBCC
>> > SHOWCONTIG and DBCC INDEXDEFRAG with decreasing levels of
>> > fragmentation,
>> > therefore limiting the number of indexes defragmented per go. Once all
>> > completed then do a final DBCC DBREINDEX.
>> >
>> > John
>> >
>> > "Patrick" wrote:
>> >
>> >> Will switching from Full to Bulk recovery during DEFRAG and then
>> >> switching
>> >> back from Bulk to Full recovery break log shipping?
>> >>
>> >> "John Bell" wrote:
>> >>
>> >> > Hi Patrick
>> >> >
>> >> > It sounds like you certainly need to look at what the indexes are
>> >> > and
>> >> > if
>> >> > they are really needed or if a better indexing scheme could be
>> >> > employed.
>> >> >
>> >> > John
>> >> >
>> >> > "Patrick" wrote:
>> >> >
>> >> > > Just started a new job and came accross tables fragmented 80% or
>> >> > > more;
>> >> > > however, they also have log shipping. Users are complaining about
>> >> > > performance and I can see why, but resolving the fragmentation
>> >> > > issue
>> >> > > is more
>> >> > > of a task than first anticipated.
>> >> > >
>> >> > > I will look into partitioning the table, but I want to see if I
>> >> > > defrag some
>> >> > > of the tables before I start the partitioning project.
>> >> > >
>> >> > > "John Bell" wrote:
>> >> > >
>> >> > > > I forgot to add...
>> >> > > >
>> >> > > > Have you considered partitioning this table?
>> >> > > >
>> >> > > > John
>> >> > > >
>> >> > > > "Patrick" wrote:
>> >> > > >
>> >> > > > > I currently have one table, highly utilized, and it is 80%
>> >> > > > > fragmented. I
>> >> > > > > need to defrag the index, but due to log shipping, I am unable
>> >> > > > > to
>> >> > > > > defrag the
>> >> > > > > index without creating a 2 gig log. A log this size will hose
>> >> > > > > the pipe
>> >> > > > > between our production and offsite sql server. Any ideas on
>> >> > > > > how
>> >> > > > > to defrag
>> >> > > > > without creating such a huge log?
>> >> > > > >
>> >> > > > >
>>|||Hi Andrew
Thinking of it that way, you are right, the last DBREINDEX would increase
the logging. I would still favour a more controlled defragmentation than one
that would entailed killed of the reindexing!
John
"Andrew J. Kelly" wrote:
> OK I see what you were after now. But unfortunately it would not matter too
> much in his case if the table was heavily or lightly fragmented. When you
> issue a DBREINDEX it will completely rebuild the indexes in a new section of
> the data files. This will always result in basically the same amount of
> pages or extents being logged or needed to be backed up in the case of
> BulkLogged recovery model. It is true that the work itself may be a little
> easier to sort the data for the rebuild when there is less fragmentation.
> But as far as log shipping goes it would not matter either way. Actually
> that way would be worse since you would log it twice. Once for Indexdefrag
> and once for DBreindex.
> --
> Andrew J. Kelly SQL MVP
>
> "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
> news:082B1590-3F81-4D82-8AD3-F008C974EC4B@.microsoft.com...
> > Hi Andrew
> >
> > The final dbreindex would be to defragment root and intermediate level
> > pages, and hopefully if the leaf level pages were already defragmented,
> > less
> > work would be required to defragment them. It is a theory that would need
> > to
> > be tested, certainly if the root/intermediate nodes were very fragmented
> > there may be less gain from the indexdefrags, there may be a point where
> > you
> > don't do the indexdefrags if DBCC SHOWCONTIG shows non-leaf levels to be
> > very
> > fragmented.
> >
> > John
> >
> >
> > "Andrew J. Kelly" wrote:
> >
> >> John,
> >>
> >> Can you explain what you mean by (or want to accomplish) by doing a final
> >> DBREINDEX? I am confused by the statement.
> >>
> >> --
> >> Andrew J. Kelly SQL MVP
> >>
> >>
> >> "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
> >> news:9E899144-8414-435B-B6E9-374365B6015A@.microsoft.com...
> >> > Hi
> >> >
> >> > A different approach to the stop/backup/restart method might be to use
> >> > DBCC
> >> > SHOWCONTIG and DBCC INDEXDEFRAG with decreasing levels of
> >> > fragmentation,
> >> > therefore limiting the number of indexes defragmented per go. Once all
> >> > completed then do a final DBCC DBREINDEX.
> >> >
> >> > John
> >> >
> >> > "Patrick" wrote:
> >> >
> >> >> Will switching from Full to Bulk recovery during DEFRAG and then
> >> >> switching
> >> >> back from Bulk to Full recovery break log shipping?
> >> >>
> >> >> "John Bell" wrote:
> >> >>
> >> >> > Hi Patrick
> >> >> >
> >> >> > It sounds like you certainly need to look at what the indexes are
> >> >> > and
> >> >> > if
> >> >> > they are really needed or if a better indexing scheme could be
> >> >> > employed.
> >> >> >
> >> >> > John
> >> >> >
> >> >> > "Patrick" wrote:
> >> >> >
> >> >> > > Just started a new job and came accross tables fragmented 80% or
> >> >> > > more;
> >> >> > > however, they also have log shipping. Users are complaining about
> >> >> > > performance and I can see why, but resolving the fragmentation
> >> >> > > issue
> >> >> > > is more
> >> >> > > of a task than first anticipated.
> >> >> > >
> >> >> > > I will look into partitioning the table, but I want to see if I
> >> >> > > defrag some
> >> >> > > of the tables before I start the partitioning project.
> >> >> > >
> >> >> > > "John Bell" wrote:
> >> >> > >
> >> >> > > > I forgot to add...
> >> >> > > >
> >> >> > > > Have you considered partitioning this table?
> >> >> > > >
> >> >> > > > John
> >> >> > > >
> >> >> > > > "Patrick" wrote:
> >> >> > > >
> >> >> > > > > I currently have one table, highly utilized, and it is 80%
> >> >> > > > > fragmented. I
> >> >> > > > > need to defrag the index, but due to log shipping, I am unable
> >> >> > > > > to
> >> >> > > > > defrag the
> >> >> > > > > index without creating a 2 gig log. A log this size will hose
> >> >> > > > > the pipe
> >> >> > > > > between our production and offsite sql server. Any ideas on
> >> >> > > > > how
> >> >> > > > > to defrag
> >> >> > > > > without creating such a huge log?
> >> >> > > > >
> >> >> > > > >
> >>
> >>
> >>
>
>|||"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:8B4E0C43-B5BE-4631-AE23-0AF894DCC3F6@.microsoft.com...
> Hi Andrew
> Thinking of it that way, you are right, the last DBREINDEX would increase
> the logging. I would still favour a more controlled defragmentation than
> one
> that would entailed killed of the reindexing!
> John
Can you explain what you mean by "controlled defragmentation?" I know you
can use dbcc showcontig results in logic to determine whether or not to
defrag (i.e., "if more than 20% fragmented, then defrag"). And I know you
can run dbcc indexdefrag for a while and stop it at any point. But I was
unaware that you could control the amount of defragmentation dbcc
indexdefrag does.
Thanks
Karen|||Hi Karen
You have described what I would call a controlled defragmentation, only
defrag tables/indexes that are necessary, choosing whether to do a DBCC
DBREINDEX or DBCC INDEXDEFRAG as necessary, rather than having (say) a timer
that kills the process part way through.
Without partitioning then you may have to resort to killing off the reindex
process.
John
"Karen Collins" wrote:
> "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
> news:8B4E0C43-B5BE-4631-AE23-0AF894DCC3F6@.microsoft.com...
> > Hi Andrew
> >
> > Thinking of it that way, you are right, the last DBREINDEX would increase
> > the logging. I would still favour a more controlled defragmentation than
> > one
> > that would entailed killed of the reindexing!
> >
> > John
> Can you explain what you mean by "controlled defragmentation?" I know you
> can use dbcc showcontig results in logic to determine whether or not to
> defrag (i.e., "if more than 20% fragmented, then defrag"). And I know you
> can run dbcc indexdefrag for a while and stop it at any point. But I was
> unaware that you could control the amount of defragmentation dbcc
> indexdefrag does.
> Thanks
> Karen
>
>
need to defrag the index, but due to log shipping, I am unable to defrag the
index without creating a 2 gig log. A log this size will hose the pipe
between our production and offsite sql server. Any ideas on how to defrag
without creating such a huge log?Hi
You are probably going to take this hit regardless, what you may want to try
is to stop the log shipping and re-syncronise afterwards. Alternatively drop
all the indexes and re-create them one at a time allowing the log to be
shipped in-between ( I would also make sure that all indexes are necessary
and test the system with alternative indexes).
What you would need to ascertain is why the index is so fragmented and try
and rectify this.
John
"Patrick" wrote:
> I currently have one table, highly utilized, and it is 80% fragmented. I
> need to defrag the index, but due to log shipping, I am unable to defrag the
> index without creating a 2 gig log. A log this size will hose the pipe
> between our production and offsite sql server. Any ideas on how to defrag
> without creating such a huge log?
>|||I forgot to add...
Have you considered partitioning this table?
John
"Patrick" wrote:
> I currently have one table, highly utilized, and it is 80% fragmented. I
> need to defrag the index, but due to log shipping, I am unable to defrag the
> index without creating a 2 gig log. A log this size will hose the pipe
> between our production and offsite sql server. Any ideas on how to defrag
> without creating such a huge log?
>|||Just started a new job and came accross tables fragmented 80% or more;
however, they also have log shipping. Users are complaining about
performance and I can see why, but resolving the fragmentation issue is more
of a task than first anticipated.
I will look into partitioning the table, but I want to see if I defrag some
of the tables before I start the partitioning project.
"John Bell" wrote:
> I forgot to add...
> Have you considered partitioning this table?
> John
> "Patrick" wrote:
> > I currently have one table, highly utilized, and it is 80% fragmented. I
> > need to defrag the index, but due to log shipping, I am unable to defrag the
> > index without creating a 2 gig log. A log this size will hose the pipe
> > between our production and offsite sql server. Any ideas on how to defrag
> > without creating such a huge log?
> >
> >|||Hi Patrick
It sounds like you certainly need to look at what the indexes are and if
they are really needed or if a better indexing scheme could be employed.
John
"Patrick" wrote:
> Just started a new job and came accross tables fragmented 80% or more;
> however, they also have log shipping. Users are complaining about
> performance and I can see why, but resolving the fragmentation issue is more
> of a task than first anticipated.
> I will look into partitioning the table, but I want to see if I defrag some
> of the tables before I start the partitioning project.
> "John Bell" wrote:
> > I forgot to add...
> >
> > Have you considered partitioning this table?
> >
> > John
> >
> > "Patrick" wrote:
> >
> > > I currently have one table, highly utilized, and it is 80% fragmented. I
> > > need to defrag the index, but due to log shipping, I am unable to defrag the
> > > index without creating a 2 gig log. A log this size will hose the pipe
> > > between our production and offsite sql server. Any ideas on how to defrag
> > > without creating such a huge log?
> > >
> > >|||Will switching from Full to Bulk recovery during DEFRAG and then switching
back from Bulk to Full recovery break log shipping?
"John Bell" wrote:
> Hi Patrick
> It sounds like you certainly need to look at what the indexes are and if
> they are really needed or if a better indexing scheme could be employed.
> John
> "Patrick" wrote:
> > Just started a new job and came accross tables fragmented 80% or more;
> > however, they also have log shipping. Users are complaining about
> > performance and I can see why, but resolving the fragmentation issue is more
> > of a task than first anticipated.
> >
> > I will look into partitioning the table, but I want to see if I defrag some
> > of the tables before I start the partitioning project.
> >
> > "John Bell" wrote:
> >
> > > I forgot to add...
> > >
> > > Have you considered partitioning this table?
> > >
> > > John
> > >
> > > "Patrick" wrote:
> > >
> > > > I currently have one table, highly utilized, and it is 80% fragmented. I
> > > > need to defrag the index, but due to log shipping, I am unable to defrag the
> > > > index without creating a 2 gig log. A log this size will hose the pipe
> > > > between our production and offsite sql server. Any ideas on how to defrag
> > > > without creating such a huge log?
> > > >
> > > >|||Hi Patrick
You can use log shipping on a BULK_LOGGED database, but I am not sure if you
can switch half way through.
Have you tried decreasing the log shipping interval (make it more frequent)?
Have easy would it be to switch of log shipping during this interval and
restore afterwards?
John
"Patrick" wrote:
> Will switching from Full to Bulk recovery during DEFRAG and then switching
> back from Bulk to Full recovery break log shipping?
> "John Bell" wrote:
> > Hi Patrick
> >
> > It sounds like you certainly need to look at what the indexes are and if
> > they are really needed or if a better indexing scheme could be employed.
> >
> > John
> >
> > "Patrick" wrote:
> >
> > > Just started a new job and came accross tables fragmented 80% or more;
> > > however, they also have log shipping. Users are complaining about
> > > performance and I can see why, but resolving the fragmentation issue is more
> > > of a task than first anticipated.
> > >
> > > I will look into partitioning the table, but I want to see if I defrag some
> > > of the tables before I start the partitioning project.
> > >
> > > "John Bell" wrote:
> > >
> > > > I forgot to add...
> > > >
> > > > Have you considered partitioning this table?
> > > >
> > > > John
> > > >
> > > > "Patrick" wrote:
> > > >
> > > > > I currently have one table, highly utilized, and it is 80% fragmented. I
> > > > > need to defrag the index, but due to log shipping, I am unable to defrag the
> > > > > index without creating a 2 gig log. A log this size will hose the pipe
> > > > > between our production and offsite sql server. Any ideas on how to defrag
> > > > > without creating such a huge log?
> > > > >
> > > > >|||That won't help you at all. Creating an index in Bulk Logged mode will send
less data to the log. But the log backup will be just as large as before
since it copies the extents that were modified in a Bulk Logged operation to
the log backup file. IndexDefrag has the potential to send even more data
to the log file than a DBREINDEX. This is especially true if the file is
heavily fragmented. The only thing is you can control it better as to when
this happens. You can start INDEXDEFRAG and stop it shortly afterwards.
Issue a Log backup then restart it, stop it etc. until it is complete. You
can't get away from logging the data but you do have some options to deal
with it. Ultimately if this reindexing is a problem then Johns suggestions
for partitioning and proper fill factors is something you may want to look
into.
--
Andrew J. Kelly SQL MVP
"Patrick" <Patrick@.discussions.microsoft.com> wrote in message
news:D5B901A7-135E-40D3-AAFF-B9CC30D533E6@.microsoft.com...
> Will switching from Full to Bulk recovery during DEFRAG and then switching
> back from Bulk to Full recovery break log shipping?
> "John Bell" wrote:
>> Hi Patrick
>> It sounds like you certainly need to look at what the indexes are and if
>> they are really needed or if a better indexing scheme could be employed.
>> John
>> "Patrick" wrote:
>> > Just started a new job and came accross tables fragmented 80% or more;
>> > however, they also have log shipping. Users are complaining about
>> > performance and I can see why, but resolving the fragmentation issue is
>> > more
>> > of a task than first anticipated.
>> >
>> > I will look into partitioning the table, but I want to see if I defrag
>> > some
>> > of the tables before I start the partitioning project.
>> >
>> > "John Bell" wrote:
>> >
>> > > I forgot to add...
>> > >
>> > > Have you considered partitioning this table?
>> > >
>> > > John
>> > >
>> > > "Patrick" wrote:
>> > >
>> > > > I currently have one table, highly utilized, and it is 80%
>> > > > fragmented. I
>> > > > need to defrag the index, but due to log shipping, I am unable to
>> > > > defrag the
>> > > > index without creating a 2 gig log. A log this size will hose the
>> > > > pipe
>> > > > between our production and offsite sql server. Any ideas on how to
>> > > > defrag
>> > > > without creating such a huge log?
>> > > >
>> > > >|||Hi
A different approach to the stop/backup/restart method might be to use DBCC
SHOWCONTIG and DBCC INDEXDEFRAG with decreasing levels of fragmentation,
therefore limiting the number of indexes defragmented per go. Once all
completed then do a final DBCC DBREINDEX.
John
"Patrick" wrote:
> Will switching from Full to Bulk recovery during DEFRAG and then switching
> back from Bulk to Full recovery break log shipping?
> "John Bell" wrote:
> > Hi Patrick
> >
> > It sounds like you certainly need to look at what the indexes are and if
> > they are really needed or if a better indexing scheme could be employed.
> >
> > John
> >
> > "Patrick" wrote:
> >
> > > Just started a new job and came accross tables fragmented 80% or more;
> > > however, they also have log shipping. Users are complaining about
> > > performance and I can see why, but resolving the fragmentation issue is more
> > > of a task than first anticipated.
> > >
> > > I will look into partitioning the table, but I want to see if I defrag some
> > > of the tables before I start the partitioning project.
> > >
> > > "John Bell" wrote:
> > >
> > > > I forgot to add...
> > > >
> > > > Have you considered partitioning this table?
> > > >
> > > > John
> > > >
> > > > "Patrick" wrote:
> > > >
> > > > > I currently have one table, highly utilized, and it is 80% fragmented. I
> > > > > need to defrag the index, but due to log shipping, I am unable to defrag the
> > > > > index without creating a 2 gig log. A log this size will hose the pipe
> > > > > between our production and offsite sql server. Any ideas on how to defrag
> > > > > without creating such a huge log?
> > > > >
> > > > >|||John,
Can you explain what you mean by (or want to accomplish) by doing a final
DBREINDEX? I am confused by the statement.
--
Andrew J. Kelly SQL MVP
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:9E899144-8414-435B-B6E9-374365B6015A@.microsoft.com...
> Hi
> A different approach to the stop/backup/restart method might be to use
> DBCC
> SHOWCONTIG and DBCC INDEXDEFRAG with decreasing levels of fragmentation,
> therefore limiting the number of indexes defragmented per go. Once all
> completed then do a final DBCC DBREINDEX.
> John
> "Patrick" wrote:
>> Will switching from Full to Bulk recovery during DEFRAG and then
>> switching
>> back from Bulk to Full recovery break log shipping?
>> "John Bell" wrote:
>> > Hi Patrick
>> >
>> > It sounds like you certainly need to look at what the indexes are and
>> > if
>> > they are really needed or if a better indexing scheme could be
>> > employed.
>> >
>> > John
>> >
>> > "Patrick" wrote:
>> >
>> > > Just started a new job and came accross tables fragmented 80% or
>> > > more;
>> > > however, they also have log shipping. Users are complaining about
>> > > performance and I can see why, but resolving the fragmentation issue
>> > > is more
>> > > of a task than first anticipated.
>> > >
>> > > I will look into partitioning the table, but I want to see if I
>> > > defrag some
>> > > of the tables before I start the partitioning project.
>> > >
>> > > "John Bell" wrote:
>> > >
>> > > > I forgot to add...
>> > > >
>> > > > Have you considered partitioning this table?
>> > > >
>> > > > John
>> > > >
>> > > > "Patrick" wrote:
>> > > >
>> > > > > I currently have one table, highly utilized, and it is 80%
>> > > > > fragmented. I
>> > > > > need to defrag the index, but due to log shipping, I am unable to
>> > > > > defrag the
>> > > > > index without creating a 2 gig log. A log this size will hose
>> > > > > the pipe
>> > > > > between our production and offsite sql server. Any ideas on how
>> > > > > to defrag
>> > > > > without creating such a huge log?
>> > > > >
>> > > > >|||Hi Andrew
The final dbreindex would be to defragment root and intermediate level
pages, and hopefully if the leaf level pages were already defragmented, less
work would be required to defragment them. It is a theory that would need to
be tested, certainly if the root/intermediate nodes were very fragmented
there may be less gain from the indexdefrags, there may be a point where you
don't do the indexdefrags if DBCC SHOWCONTIG shows non-leaf levels to be very
fragmented.
John
"Andrew J. Kelly" wrote:
> John,
> Can you explain what you mean by (or want to accomplish) by doing a final
> DBREINDEX? I am confused by the statement.
> --
> Andrew J. Kelly SQL MVP
>
> "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
> news:9E899144-8414-435B-B6E9-374365B6015A@.microsoft.com...
> > Hi
> >
> > A different approach to the stop/backup/restart method might be to use
> > DBCC
> > SHOWCONTIG and DBCC INDEXDEFRAG with decreasing levels of fragmentation,
> > therefore limiting the number of indexes defragmented per go. Once all
> > completed then do a final DBCC DBREINDEX.
> >
> > John
> >
> > "Patrick" wrote:
> >
> >> Will switching from Full to Bulk recovery during DEFRAG and then
> >> switching
> >> back from Bulk to Full recovery break log shipping?
> >>
> >> "John Bell" wrote:
> >>
> >> > Hi Patrick
> >> >
> >> > It sounds like you certainly need to look at what the indexes are and
> >> > if
> >> > they are really needed or if a better indexing scheme could be
> >> > employed.
> >> >
> >> > John
> >> >
> >> > "Patrick" wrote:
> >> >
> >> > > Just started a new job and came accross tables fragmented 80% or
> >> > > more;
> >> > > however, they also have log shipping. Users are complaining about
> >> > > performance and I can see why, but resolving the fragmentation issue
> >> > > is more
> >> > > of a task than first anticipated.
> >> > >
> >> > > I will look into partitioning the table, but I want to see if I
> >> > > defrag some
> >> > > of the tables before I start the partitioning project.
> >> > >
> >> > > "John Bell" wrote:
> >> > >
> >> > > > I forgot to add...
> >> > > >
> >> > > > Have you considered partitioning this table?
> >> > > >
> >> > > > John
> >> > > >
> >> > > > "Patrick" wrote:
> >> > > >
> >> > > > > I currently have one table, highly utilized, and it is 80%
> >> > > > > fragmented. I
> >> > > > > need to defrag the index, but due to log shipping, I am unable to
> >> > > > > defrag the
> >> > > > > index without creating a 2 gig log. A log this size will hose
> >> > > > > the pipe
> >> > > > > between our production and offsite sql server. Any ideas on how
> >> > > > > to defrag
> >> > > > > without creating such a huge log?
> >> > > > >
> >> > > > >
>
>|||OK I see what you were after now. But unfortunately it would not matter too
much in his case if the table was heavily or lightly fragmented. When you
issue a DBREINDEX it will completely rebuild the indexes in a new section of
the data files. This will always result in basically the same amount of
pages or extents being logged or needed to be backed up in the case of
BulkLogged recovery model. It is true that the work itself may be a little
easier to sort the data for the rebuild when there is less fragmentation.
But as far as log shipping goes it would not matter either way. Actually
that way would be worse since you would log it twice. Once for Indexdefrag
and once for DBreindex.
--
Andrew J. Kelly SQL MVP
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:082B1590-3F81-4D82-8AD3-F008C974EC4B@.microsoft.com...
> Hi Andrew
> The final dbreindex would be to defragment root and intermediate level
> pages, and hopefully if the leaf level pages were already defragmented,
> less
> work would be required to defragment them. It is a theory that would need
> to
> be tested, certainly if the root/intermediate nodes were very fragmented
> there may be less gain from the indexdefrags, there may be a point where
> you
> don't do the indexdefrags if DBCC SHOWCONTIG shows non-leaf levels to be
> very
> fragmented.
> John
>
> "Andrew J. Kelly" wrote:
>> John,
>> Can you explain what you mean by (or want to accomplish) by doing a final
>> DBREINDEX? I am confused by the statement.
>> --
>> Andrew J. Kelly SQL MVP
>>
>> "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
>> news:9E899144-8414-435B-B6E9-374365B6015A@.microsoft.com...
>> > Hi
>> >
>> > A different approach to the stop/backup/restart method might be to use
>> > DBCC
>> > SHOWCONTIG and DBCC INDEXDEFRAG with decreasing levels of
>> > fragmentation,
>> > therefore limiting the number of indexes defragmented per go. Once all
>> > completed then do a final DBCC DBREINDEX.
>> >
>> > John
>> >
>> > "Patrick" wrote:
>> >
>> >> Will switching from Full to Bulk recovery during DEFRAG and then
>> >> switching
>> >> back from Bulk to Full recovery break log shipping?
>> >>
>> >> "John Bell" wrote:
>> >>
>> >> > Hi Patrick
>> >> >
>> >> > It sounds like you certainly need to look at what the indexes are
>> >> > and
>> >> > if
>> >> > they are really needed or if a better indexing scheme could be
>> >> > employed.
>> >> >
>> >> > John
>> >> >
>> >> > "Patrick" wrote:
>> >> >
>> >> > > Just started a new job and came accross tables fragmented 80% or
>> >> > > more;
>> >> > > however, they also have log shipping. Users are complaining about
>> >> > > performance and I can see why, but resolving the fragmentation
>> >> > > issue
>> >> > > is more
>> >> > > of a task than first anticipated.
>> >> > >
>> >> > > I will look into partitioning the table, but I want to see if I
>> >> > > defrag some
>> >> > > of the tables before I start the partitioning project.
>> >> > >
>> >> > > "John Bell" wrote:
>> >> > >
>> >> > > > I forgot to add...
>> >> > > >
>> >> > > > Have you considered partitioning this table?
>> >> > > >
>> >> > > > John
>> >> > > >
>> >> > > > "Patrick" wrote:
>> >> > > >
>> >> > > > > I currently have one table, highly utilized, and it is 80%
>> >> > > > > fragmented. I
>> >> > > > > need to defrag the index, but due to log shipping, I am unable
>> >> > > > > to
>> >> > > > > defrag the
>> >> > > > > index without creating a 2 gig log. A log this size will hose
>> >> > > > > the pipe
>> >> > > > > between our production and offsite sql server. Any ideas on
>> >> > > > > how
>> >> > > > > to defrag
>> >> > > > > without creating such a huge log?
>> >> > > > >
>> >> > > > >
>>|||Hi Andrew
Thinking of it that way, you are right, the last DBREINDEX would increase
the logging. I would still favour a more controlled defragmentation than one
that would entailed killed of the reindexing!
John
"Andrew J. Kelly" wrote:
> OK I see what you were after now. But unfortunately it would not matter too
> much in his case if the table was heavily or lightly fragmented. When you
> issue a DBREINDEX it will completely rebuild the indexes in a new section of
> the data files. This will always result in basically the same amount of
> pages or extents being logged or needed to be backed up in the case of
> BulkLogged recovery model. It is true that the work itself may be a little
> easier to sort the data for the rebuild when there is less fragmentation.
> But as far as log shipping goes it would not matter either way. Actually
> that way would be worse since you would log it twice. Once for Indexdefrag
> and once for DBreindex.
> --
> Andrew J. Kelly SQL MVP
>
> "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
> news:082B1590-3F81-4D82-8AD3-F008C974EC4B@.microsoft.com...
> > Hi Andrew
> >
> > The final dbreindex would be to defragment root and intermediate level
> > pages, and hopefully if the leaf level pages were already defragmented,
> > less
> > work would be required to defragment them. It is a theory that would need
> > to
> > be tested, certainly if the root/intermediate nodes were very fragmented
> > there may be less gain from the indexdefrags, there may be a point where
> > you
> > don't do the indexdefrags if DBCC SHOWCONTIG shows non-leaf levels to be
> > very
> > fragmented.
> >
> > John
> >
> >
> > "Andrew J. Kelly" wrote:
> >
> >> John,
> >>
> >> Can you explain what you mean by (or want to accomplish) by doing a final
> >> DBREINDEX? I am confused by the statement.
> >>
> >> --
> >> Andrew J. Kelly SQL MVP
> >>
> >>
> >> "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
> >> news:9E899144-8414-435B-B6E9-374365B6015A@.microsoft.com...
> >> > Hi
> >> >
> >> > A different approach to the stop/backup/restart method might be to use
> >> > DBCC
> >> > SHOWCONTIG and DBCC INDEXDEFRAG with decreasing levels of
> >> > fragmentation,
> >> > therefore limiting the number of indexes defragmented per go. Once all
> >> > completed then do a final DBCC DBREINDEX.
> >> >
> >> > John
> >> >
> >> > "Patrick" wrote:
> >> >
> >> >> Will switching from Full to Bulk recovery during DEFRAG and then
> >> >> switching
> >> >> back from Bulk to Full recovery break log shipping?
> >> >>
> >> >> "John Bell" wrote:
> >> >>
> >> >> > Hi Patrick
> >> >> >
> >> >> > It sounds like you certainly need to look at what the indexes are
> >> >> > and
> >> >> > if
> >> >> > they are really needed or if a better indexing scheme could be
> >> >> > employed.
> >> >> >
> >> >> > John
> >> >> >
> >> >> > "Patrick" wrote:
> >> >> >
> >> >> > > Just started a new job and came accross tables fragmented 80% or
> >> >> > > more;
> >> >> > > however, they also have log shipping. Users are complaining about
> >> >> > > performance and I can see why, but resolving the fragmentation
> >> >> > > issue
> >> >> > > is more
> >> >> > > of a task than first anticipated.
> >> >> > >
> >> >> > > I will look into partitioning the table, but I want to see if I
> >> >> > > defrag some
> >> >> > > of the tables before I start the partitioning project.
> >> >> > >
> >> >> > > "John Bell" wrote:
> >> >> > >
> >> >> > > > I forgot to add...
> >> >> > > >
> >> >> > > > Have you considered partitioning this table?
> >> >> > > >
> >> >> > > > John
> >> >> > > >
> >> >> > > > "Patrick" wrote:
> >> >> > > >
> >> >> > > > > I currently have one table, highly utilized, and it is 80%
> >> >> > > > > fragmented. I
> >> >> > > > > need to defrag the index, but due to log shipping, I am unable
> >> >> > > > > to
> >> >> > > > > defrag the
> >> >> > > > > index without creating a 2 gig log. A log this size will hose
> >> >> > > > > the pipe
> >> >> > > > > between our production and offsite sql server. Any ideas on
> >> >> > > > > how
> >> >> > > > > to defrag
> >> >> > > > > without creating such a huge log?
> >> >> > > > >
> >> >> > > > >
> >>
> >>
> >>
>
>|||"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:8B4E0C43-B5BE-4631-AE23-0AF894DCC3F6@.microsoft.com...
> Hi Andrew
> Thinking of it that way, you are right, the last DBREINDEX would increase
> the logging. I would still favour a more controlled defragmentation than
> one
> that would entailed killed of the reindexing!
> John
Can you explain what you mean by "controlled defragmentation?" I know you
can use dbcc showcontig results in logic to determine whether or not to
defrag (i.e., "if more than 20% fragmented, then defrag"). And I know you
can run dbcc indexdefrag for a while and stop it at any point. But I was
unaware that you could control the amount of defragmentation dbcc
indexdefrag does.
Thanks
Karen|||Hi Karen
You have described what I would call a controlled defragmentation, only
defrag tables/indexes that are necessary, choosing whether to do a DBCC
DBREINDEX or DBCC INDEXDEFRAG as necessary, rather than having (say) a timer
that kills the process part way through.
Without partitioning then you may have to resort to killing off the reindex
process.
John
"Karen Collins" wrote:
> "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
> news:8B4E0C43-B5BE-4631-AE23-0AF894DCC3F6@.microsoft.com...
> > Hi Andrew
> >
> > Thinking of it that way, you are right, the last DBREINDEX would increase
> > the logging. I would still favour a more controlled defragmentation than
> > one
> > that would entailed killed of the reindexing!
> >
> > John
> Can you explain what you mean by "controlled defragmentation?" I know you
> can use dbcc showcontig results in logic to determine whether or not to
> defrag (i.e., "if more than 20% fragmented, then defrag"). And I know you
> can run dbcc indexdefrag for a while and stop it at any point. But I was
> unaware that you could control the amount of defragmentation dbcc
> indexdefrag does.
> Thanks
> Karen
>
>
Defrag and Index
I am by no means an SQL expert, I need to figure out if doing a nightly
reindex defrags my indexes. From the data below it seems pretty obvious it
doesn't, but I am unsure.
I ran a DBCC SHOWCONTIG and I have a index that appears to be absolutely a
mess. Is this correct?
Will running DBCC INDEXDEFRAG help? Accordingly, the documentation is less
than enthusiastic about doing an online defrag.
Do I need to do an DBCC DBREINDEX
So to recap...
1) Does night reindex, defrag?
2) Is the index below a disaster?
3) What is the best way to get this defragged? Keeping the index online
is preferred, but not necessarily required.
========================================================
DBCC SHOWCONTIG scanning 'TS1Endpoints' table...
Table: 'TS1Endpoints' (1860201677); index ID: 0, database ID: 10
TABLE level scan performed.
- Pages Scanned........................: 948
- Extents Scanned.......................: 204
- Extent Switches.......................: 203
- Avg. Pages per Extent..................: 4.6
- Scan Density [Best Count:Actual Count]......: 58.33% [119:204]
- Extent Scan Fragmentation ...............: 99.51%
- Avg. Bytes Free per Page................: 889.5
- Avg. Page Density (full)................: 89.01%
DBCC SHOWCONTIG scanning 'TS1Endpoints' table...
Table: 'TS1Endpoints' (1860201677); index ID: 2, database ID: 10
LEAF level scan performed.
- Pages Scanned........................: 292
- Extents Scanned.......................: 37
- Extent Switches.......................: 36
- Avg. Pages per Extent..................: 7.9
- Scan Density [Best Count:Actual Count]......: 100.00% [37:37]
- Logical Scan Fragmentation ..............: 0.00%
- Extent Scan Fragmentation ...............: 0.00%
- Avg. Bytes Free per Page................: 810.7
- Avg. Page Density (full)................: 89.98%
--
Paul Bergson
MVP - Directory Services
MCT, MCSE, MCSA, Security+, BS CSci
2003, 2000 (Early Achiever), NT
http://www.pbbergs.com
Please no e-mails, any questions should be posted in the NewsGroup
This posting is provided "AS IS" with no warranties, and confers no rights.Paul,
There are not to many pages, so I will not worry much. If you really want to
defrag that table, then you have to add a clustered index, or dump the data,
recreate the table and import the data.
AMB
"Paul Bergson [MVP-DS]" wrote:
> I am by no means an SQL expert, I need to figure out if doing a nightly
> reindex defrags my indexes. From the data below it seems pretty obvious it
> doesn't, but I am unsure.
> I ran a DBCC SHOWCONTIG and I have a index that appears to be absolutely a
> mess. Is this correct?
> Will running DBCC INDEXDEFRAG help? Accordingly, the documentation is less
> than enthusiastic about doing an online defrag.
> Do I need to do an DBCC DBREINDEX
> So to recap...
> 1) Does night reindex, defrag?
> 2) Is the index below a disaster?
> 3) What is the best way to get this defragged? Keeping the index online
> is preferred, but not necessarily required.
> ========================================================> DBCC SHOWCONTIG scanning 'TS1Endpoints' table...
> Table: 'TS1Endpoints' (1860201677); index ID: 0, database ID: 10
> TABLE level scan performed.
> - Pages Scanned........................: 948
> - Extents Scanned.......................: 204
> - Extent Switches.......................: 203
> - Avg. Pages per Extent..................: 4.6
> - Scan Density [Best Count:Actual Count]......: 58.33% [119:204]
> - Extent Scan Fragmentation ...............: 99.51%
> - Avg. Bytes Free per Page................: 889.5
> - Avg. Page Density (full)................: 89.01%
> DBCC SHOWCONTIG scanning 'TS1Endpoints' table...
> Table: 'TS1Endpoints' (1860201677); index ID: 2, database ID: 10
> LEAF level scan performed.
> - Pages Scanned........................: 292
> - Extents Scanned.......................: 37
> - Extent Switches.......................: 36
> - Avg. Pages per Extent..................: 7.9
> - Scan Density [Best Count:Actual Count]......: 100.00% [37:37]
> - Logical Scan Fragmentation ..............: 0.00%
> - Extent Scan Fragmentation ...............: 0.00%
> - Avg. Bytes Free per Page................: 810.7
> - Avg. Page Density (full)................: 89.98%
>
> --
> Paul Bergson
> MVP - Directory Services
> MCT, MCSE, MCSA, Security+, BS CSci
> 2003, 2000 (Early Achiever), NT
> http://www.pbbergs.com
> Please no e-mails, any questions should be posted in the NewsGroup
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
>|||So the line
Extent Scan Fragmentation ...............: 99.51%
Is not bad?
--
Paul Bergson
MVP - Directory Services
MCT, MCSE, MCSA, Security+, BS CSci
2003, 2000 (Early Achiever), NT
http://www.pbbergs.com
Please no e-mails, any questions should be posted in the NewsGroup
This posting is provided "AS IS" with no warranties, and confers no rights.
"Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in message
news:C52B9490-28EE-4CDD-AF1B-3C8B135FD898@.microsoft.com...
> Paul,
> There are not to many pages, so I will not worry much. If you really want
> to
> defrag that table, then you have to add a clustered index, or dump the
> data,
> recreate the table and import the data.
>
> AMB
> "Paul Bergson [MVP-DS]" wrote:
>> I am by no means an SQL expert, I need to figure out if doing a nightly
>> reindex defrags my indexes. From the data below it seems pretty obvious
>> it
>> doesn't, but I am unsure.
>> I ran a DBCC SHOWCONTIG and I have a index that appears to be absolutely
>> a
>> mess. Is this correct?
>> Will running DBCC INDEXDEFRAG help? Accordingly, the documentation is
>> less
>> than enthusiastic about doing an online defrag.
>> Do I need to do an DBCC DBREINDEX
>> So to recap...
>> 1) Does night reindex, defrag?
>> 2) Is the index below a disaster?
>> 3) What is the best way to get this defragged? Keeping the index
>> online
>> is preferred, but not necessarily required.
>> ========================================================>> DBCC SHOWCONTIG scanning 'TS1Endpoints' table...
>> Table: 'TS1Endpoints' (1860201677); index ID: 0, database ID: 10
>> TABLE level scan performed.
>> - Pages Scanned........................: 948
>> - Extents Scanned.......................: 204
>> - Extent Switches.......................: 203
>> - Avg. Pages per Extent..................: 4.6
>> - Scan Density [Best Count:Actual Count]......: 58.33% [119:204]
>> - Extent Scan Fragmentation ...............: 99.51%
>> - Avg. Bytes Free per Page................: 889.5
>> - Avg. Page Density (full)................: 89.01%
>> DBCC SHOWCONTIG scanning 'TS1Endpoints' table...
>> Table: 'TS1Endpoints' (1860201677); index ID: 2, database ID: 10
>> LEAF level scan performed.
>> - Pages Scanned........................: 292
>> - Extents Scanned.......................: 37
>> - Extent Switches.......................: 36
>> - Avg. Pages per Extent..................: 7.9
>> - Scan Density [Best Count:Actual Count]......: 100.00% [37:37]
>> - Logical Scan Fragmentation ..............: 0.00%
>> - Extent Scan Fragmentation ...............: 0.00%
>> - Avg. Bytes Free per Page................: 810.7
>> - Avg. Page Density (full)................: 89.98%
>>
>> --
>> Paul Bergson
>> MVP - Directory Services
>> MCT, MCSE, MCSA, Security+, BS CSci
>> 2003, 2000 (Early Achiever), NT
>> http://www.pbbergs.com
>> Please no e-mails, any questions should be posted in the NewsGroup
>> This posting is provided "AS IS" with no warranties, and confers no
>> rights.
>>|||Paul,
That number is not relevant to heaps (tables without clustered index) and is
meaningless when the index spans multiple files.
DBCC SHOWCONTIG
http://msdn2.microsoft.com/en-US/library/aa258803(SQL.80).aspx
Microsoft SQL Server 2000 Index Defragmentation Best Practices
http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ss2kidbp.mspx
AMB
"Paul Bergson [MVP-DS]" wrote:
> So the line
> Extent Scan Fragmentation ...............: 99.51%
> Is not bad?
> --
> Paul Bergson
> MVP - Directory Services
> MCT, MCSE, MCSA, Security+, BS CSci
> 2003, 2000 (Early Achiever), NT
> http://www.pbbergs.com
> Please no e-mails, any questions should be posted in the NewsGroup
> This posting is provided "AS IS" with no warranties, and confers no rights.
> "Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in message
> news:C52B9490-28EE-4CDD-AF1B-3C8B135FD898@.microsoft.com...
> > Paul,
> >
> > There are not to many pages, so I will not worry much. If you really want
> > to
> > defrag that table, then you have to add a clustered index, or dump the
> > data,
> > recreate the table and import the data.
> >
> >
> > AMB
> >
> > "Paul Bergson [MVP-DS]" wrote:
> >
> >> I am by no means an SQL expert, I need to figure out if doing a nightly
> >> reindex defrags my indexes. From the data below it seems pretty obvious
> >> it
> >> doesn't, but I am unsure.
> >>
> >> I ran a DBCC SHOWCONTIG and I have a index that appears to be absolutely
> >> a
> >> mess. Is this correct?
> >>
> >> Will running DBCC INDEXDEFRAG help? Accordingly, the documentation is
> >> less
> >> than enthusiastic about doing an online defrag.
> >>
> >> Do I need to do an DBCC DBREINDEX
> >>
> >> So to recap...
> >> 1) Does night reindex, defrag?
> >> 2) Is the index below a disaster?
> >> 3) What is the best way to get this defragged? Keeping the index
> >> online
> >> is preferred, but not necessarily required.
> >>
> >> ========================================================> >>
> >> DBCC SHOWCONTIG scanning 'TS1Endpoints' table...
> >>
> >> Table: 'TS1Endpoints' (1860201677); index ID: 0, database ID: 10
> >>
> >> TABLE level scan performed.
> >>
> >> - Pages Scanned........................: 948
> >>
> >> - Extents Scanned.......................: 204
> >>
> >> - Extent Switches.......................: 203
> >>
> >> - Avg. Pages per Extent..................: 4.6
> >>
> >> - Scan Density [Best Count:Actual Count]......: 58.33% [119:204]
> >>
> >> - Extent Scan Fragmentation ...............: 99.51%
> >>
> >> - Avg. Bytes Free per Page................: 889.5
> >>
> >> - Avg. Page Density (full)................: 89.01%
> >>
> >> DBCC SHOWCONTIG scanning 'TS1Endpoints' table...
> >>
> >> Table: 'TS1Endpoints' (1860201677); index ID: 2, database ID: 10
> >>
> >> LEAF level scan performed.
> >>
> >> - Pages Scanned........................: 292
> >>
> >> - Extents Scanned.......................: 37
> >>
> >> - Extent Switches.......................: 36
> >>
> >> - Avg. Pages per Extent..................: 7.9
> >>
> >> - Scan Density [Best Count:Actual Count]......: 100.00% [37:37]
> >>
> >> - Logical Scan Fragmentation ..............: 0.00%
> >>
> >> - Extent Scan Fragmentation ...............: 0.00%
> >>
> >> - Avg. Bytes Free per Page................: 810.7
> >>
> >> - Avg. Page Density (full)................: 89.98%
> >>
> >>
> >> --
> >> Paul Bergson
> >> MVP - Directory Services
> >> MCT, MCSE, MCSA, Security+, BS CSci
> >> 2003, 2000 (Early Achiever), NT
> >>
> >> http://www.pbbergs.com
> >>
> >> Please no e-mails, any questions should be posted in the NewsGroup
> >> This posting is provided "AS IS" with no warranties, and confers no
> >> rights.
> >>
> >>
> >>
>
>|||Paul Bergson [MVP-DS] (pbergson@.allete_nospam.com) writes:
> I am by no means an SQL expert, I need to figure out if doing a nightly
> reindex defrags my indexes. From the data below it seems pretty obvious
> it doesn't, but I am unsure.
> I ran a DBCC SHOWCONTIG and I have a index that appears to be absolutely a
> mess. Is this correct?
No, because that's not an index, but a heap, a table without a
clustered index. Heaps are quite prone to fragmentation, and your
table is not in the best shape with 4.6 pages per extent. But as
Alejandro pointed out the table is not that big, and it is not likely
to be a major problem.
> Will running DBCC INDEXDEFRAG help? Accordingly, the documentation is
> less than enthusiastic about doing an online defrag.
Neither INDEXDEFRAG or DBREINDEX works on heap. You can build a clustered
index on the table and then drop it. Or you can just build a clustered index
and have it that way.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
reindex defrags my indexes. From the data below it seems pretty obvious it
doesn't, but I am unsure.
I ran a DBCC SHOWCONTIG and I have a index that appears to be absolutely a
mess. Is this correct?
Will running DBCC INDEXDEFRAG help? Accordingly, the documentation is less
than enthusiastic about doing an online defrag.
Do I need to do an DBCC DBREINDEX
So to recap...
1) Does night reindex, defrag?
2) Is the index below a disaster?
3) What is the best way to get this defragged? Keeping the index online
is preferred, but not necessarily required.
========================================================
DBCC SHOWCONTIG scanning 'TS1Endpoints' table...
Table: 'TS1Endpoints' (1860201677); index ID: 0, database ID: 10
TABLE level scan performed.
- Pages Scanned........................: 948
- Extents Scanned.......................: 204
- Extent Switches.......................: 203
- Avg. Pages per Extent..................: 4.6
- Scan Density [Best Count:Actual Count]......: 58.33% [119:204]
- Extent Scan Fragmentation ...............: 99.51%
- Avg. Bytes Free per Page................: 889.5
- Avg. Page Density (full)................: 89.01%
DBCC SHOWCONTIG scanning 'TS1Endpoints' table...
Table: 'TS1Endpoints' (1860201677); index ID: 2, database ID: 10
LEAF level scan performed.
- Pages Scanned........................: 292
- Extents Scanned.......................: 37
- Extent Switches.......................: 36
- Avg. Pages per Extent..................: 7.9
- Scan Density [Best Count:Actual Count]......: 100.00% [37:37]
- Logical Scan Fragmentation ..............: 0.00%
- Extent Scan Fragmentation ...............: 0.00%
- Avg. Bytes Free per Page................: 810.7
- Avg. Page Density (full)................: 89.98%
--
Paul Bergson
MVP - Directory Services
MCT, MCSE, MCSA, Security+, BS CSci
2003, 2000 (Early Achiever), NT
http://www.pbbergs.com
Please no e-mails, any questions should be posted in the NewsGroup
This posting is provided "AS IS" with no warranties, and confers no rights.Paul,
There are not to many pages, so I will not worry much. If you really want to
defrag that table, then you have to add a clustered index, or dump the data,
recreate the table and import the data.
AMB
"Paul Bergson [MVP-DS]" wrote:
> I am by no means an SQL expert, I need to figure out if doing a nightly
> reindex defrags my indexes. From the data below it seems pretty obvious it
> doesn't, but I am unsure.
> I ran a DBCC SHOWCONTIG and I have a index that appears to be absolutely a
> mess. Is this correct?
> Will running DBCC INDEXDEFRAG help? Accordingly, the documentation is less
> than enthusiastic about doing an online defrag.
> Do I need to do an DBCC DBREINDEX
> So to recap...
> 1) Does night reindex, defrag?
> 2) Is the index below a disaster?
> 3) What is the best way to get this defragged? Keeping the index online
> is preferred, but not necessarily required.
> ========================================================> DBCC SHOWCONTIG scanning 'TS1Endpoints' table...
> Table: 'TS1Endpoints' (1860201677); index ID: 0, database ID: 10
> TABLE level scan performed.
> - Pages Scanned........................: 948
> - Extents Scanned.......................: 204
> - Extent Switches.......................: 203
> - Avg. Pages per Extent..................: 4.6
> - Scan Density [Best Count:Actual Count]......: 58.33% [119:204]
> - Extent Scan Fragmentation ...............: 99.51%
> - Avg. Bytes Free per Page................: 889.5
> - Avg. Page Density (full)................: 89.01%
> DBCC SHOWCONTIG scanning 'TS1Endpoints' table...
> Table: 'TS1Endpoints' (1860201677); index ID: 2, database ID: 10
> LEAF level scan performed.
> - Pages Scanned........................: 292
> - Extents Scanned.......................: 37
> - Extent Switches.......................: 36
> - Avg. Pages per Extent..................: 7.9
> - Scan Density [Best Count:Actual Count]......: 100.00% [37:37]
> - Logical Scan Fragmentation ..............: 0.00%
> - Extent Scan Fragmentation ...............: 0.00%
> - Avg. Bytes Free per Page................: 810.7
> - Avg. Page Density (full)................: 89.98%
>
> --
> Paul Bergson
> MVP - Directory Services
> MCT, MCSE, MCSA, Security+, BS CSci
> 2003, 2000 (Early Achiever), NT
> http://www.pbbergs.com
> Please no e-mails, any questions should be posted in the NewsGroup
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
>|||So the line
Extent Scan Fragmentation ...............: 99.51%
Is not bad?
--
Paul Bergson
MVP - Directory Services
MCT, MCSE, MCSA, Security+, BS CSci
2003, 2000 (Early Achiever), NT
http://www.pbbergs.com
Please no e-mails, any questions should be posted in the NewsGroup
This posting is provided "AS IS" with no warranties, and confers no rights.
"Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in message
news:C52B9490-28EE-4CDD-AF1B-3C8B135FD898@.microsoft.com...
> Paul,
> There are not to many pages, so I will not worry much. If you really want
> to
> defrag that table, then you have to add a clustered index, or dump the
> data,
> recreate the table and import the data.
>
> AMB
> "Paul Bergson [MVP-DS]" wrote:
>> I am by no means an SQL expert, I need to figure out if doing a nightly
>> reindex defrags my indexes. From the data below it seems pretty obvious
>> it
>> doesn't, but I am unsure.
>> I ran a DBCC SHOWCONTIG and I have a index that appears to be absolutely
>> a
>> mess. Is this correct?
>> Will running DBCC INDEXDEFRAG help? Accordingly, the documentation is
>> less
>> than enthusiastic about doing an online defrag.
>> Do I need to do an DBCC DBREINDEX
>> So to recap...
>> 1) Does night reindex, defrag?
>> 2) Is the index below a disaster?
>> 3) What is the best way to get this defragged? Keeping the index
>> online
>> is preferred, but not necessarily required.
>> ========================================================>> DBCC SHOWCONTIG scanning 'TS1Endpoints' table...
>> Table: 'TS1Endpoints' (1860201677); index ID: 0, database ID: 10
>> TABLE level scan performed.
>> - Pages Scanned........................: 948
>> - Extents Scanned.......................: 204
>> - Extent Switches.......................: 203
>> - Avg. Pages per Extent..................: 4.6
>> - Scan Density [Best Count:Actual Count]......: 58.33% [119:204]
>> - Extent Scan Fragmentation ...............: 99.51%
>> - Avg. Bytes Free per Page................: 889.5
>> - Avg. Page Density (full)................: 89.01%
>> DBCC SHOWCONTIG scanning 'TS1Endpoints' table...
>> Table: 'TS1Endpoints' (1860201677); index ID: 2, database ID: 10
>> LEAF level scan performed.
>> - Pages Scanned........................: 292
>> - Extents Scanned.......................: 37
>> - Extent Switches.......................: 36
>> - Avg. Pages per Extent..................: 7.9
>> - Scan Density [Best Count:Actual Count]......: 100.00% [37:37]
>> - Logical Scan Fragmentation ..............: 0.00%
>> - Extent Scan Fragmentation ...............: 0.00%
>> - Avg. Bytes Free per Page................: 810.7
>> - Avg. Page Density (full)................: 89.98%
>>
>> --
>> Paul Bergson
>> MVP - Directory Services
>> MCT, MCSE, MCSA, Security+, BS CSci
>> 2003, 2000 (Early Achiever), NT
>> http://www.pbbergs.com
>> Please no e-mails, any questions should be posted in the NewsGroup
>> This posting is provided "AS IS" with no warranties, and confers no
>> rights.
>>|||Paul,
That number is not relevant to heaps (tables without clustered index) and is
meaningless when the index spans multiple files.
DBCC SHOWCONTIG
http://msdn2.microsoft.com/en-US/library/aa258803(SQL.80).aspx
Microsoft SQL Server 2000 Index Defragmentation Best Practices
http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ss2kidbp.mspx
AMB
"Paul Bergson [MVP-DS]" wrote:
> So the line
> Extent Scan Fragmentation ...............: 99.51%
> Is not bad?
> --
> Paul Bergson
> MVP - Directory Services
> MCT, MCSE, MCSA, Security+, BS CSci
> 2003, 2000 (Early Achiever), NT
> http://www.pbbergs.com
> Please no e-mails, any questions should be posted in the NewsGroup
> This posting is provided "AS IS" with no warranties, and confers no rights.
> "Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in message
> news:C52B9490-28EE-4CDD-AF1B-3C8B135FD898@.microsoft.com...
> > Paul,
> >
> > There are not to many pages, so I will not worry much. If you really want
> > to
> > defrag that table, then you have to add a clustered index, or dump the
> > data,
> > recreate the table and import the data.
> >
> >
> > AMB
> >
> > "Paul Bergson [MVP-DS]" wrote:
> >
> >> I am by no means an SQL expert, I need to figure out if doing a nightly
> >> reindex defrags my indexes. From the data below it seems pretty obvious
> >> it
> >> doesn't, but I am unsure.
> >>
> >> I ran a DBCC SHOWCONTIG and I have a index that appears to be absolutely
> >> a
> >> mess. Is this correct?
> >>
> >> Will running DBCC INDEXDEFRAG help? Accordingly, the documentation is
> >> less
> >> than enthusiastic about doing an online defrag.
> >>
> >> Do I need to do an DBCC DBREINDEX
> >>
> >> So to recap...
> >> 1) Does night reindex, defrag?
> >> 2) Is the index below a disaster?
> >> 3) What is the best way to get this defragged? Keeping the index
> >> online
> >> is preferred, but not necessarily required.
> >>
> >> ========================================================> >>
> >> DBCC SHOWCONTIG scanning 'TS1Endpoints' table...
> >>
> >> Table: 'TS1Endpoints' (1860201677); index ID: 0, database ID: 10
> >>
> >> TABLE level scan performed.
> >>
> >> - Pages Scanned........................: 948
> >>
> >> - Extents Scanned.......................: 204
> >>
> >> - Extent Switches.......................: 203
> >>
> >> - Avg. Pages per Extent..................: 4.6
> >>
> >> - Scan Density [Best Count:Actual Count]......: 58.33% [119:204]
> >>
> >> - Extent Scan Fragmentation ...............: 99.51%
> >>
> >> - Avg. Bytes Free per Page................: 889.5
> >>
> >> - Avg. Page Density (full)................: 89.01%
> >>
> >> DBCC SHOWCONTIG scanning 'TS1Endpoints' table...
> >>
> >> Table: 'TS1Endpoints' (1860201677); index ID: 2, database ID: 10
> >>
> >> LEAF level scan performed.
> >>
> >> - Pages Scanned........................: 292
> >>
> >> - Extents Scanned.......................: 37
> >>
> >> - Extent Switches.......................: 36
> >>
> >> - Avg. Pages per Extent..................: 7.9
> >>
> >> - Scan Density [Best Count:Actual Count]......: 100.00% [37:37]
> >>
> >> - Logical Scan Fragmentation ..............: 0.00%
> >>
> >> - Extent Scan Fragmentation ...............: 0.00%
> >>
> >> - Avg. Bytes Free per Page................: 810.7
> >>
> >> - Avg. Page Density (full)................: 89.98%
> >>
> >>
> >> --
> >> Paul Bergson
> >> MVP - Directory Services
> >> MCT, MCSE, MCSA, Security+, BS CSci
> >> 2003, 2000 (Early Achiever), NT
> >>
> >> http://www.pbbergs.com
> >>
> >> Please no e-mails, any questions should be posted in the NewsGroup
> >> This posting is provided "AS IS" with no warranties, and confers no
> >> rights.
> >>
> >>
> >>
>
>|||Paul Bergson [MVP-DS] (pbergson@.allete_nospam.com) writes:
> I am by no means an SQL expert, I need to figure out if doing a nightly
> reindex defrags my indexes. From the data below it seems pretty obvious
> it doesn't, but I am unsure.
> I ran a DBCC SHOWCONTIG and I have a index that appears to be absolutely a
> mess. Is this correct?
No, because that's not an index, but a heap, a table without a
clustered index. Heaps are quite prone to fragmentation, and your
table is not in the best shape with 4.6 pages per extent. But as
Alejandro pointed out the table is not that big, and it is not likely
to be a major problem.
> Will running DBCC INDEXDEFRAG help? Accordingly, the documentation is
> less than enthusiastic about doing an online defrag.
Neither INDEXDEFRAG or DBREINDEX works on heap. You can build a clustered
index on the table and then drop it. Or you can just build a clustered index
and have it that way.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
Subscribe to:
Posts (Atom)