DBCC showcontig

Hello,
I have a very fragmented database and am running dbcc dbreindex but
getting a very odd issue.
When running DBCC SHOWCONTIG before and after reindexing the tables,
there is no difference in the statistics. The scan fragmentation levels
(in fact all of the statistics) are exactly the same.
How can this be?
I've also run the indexdefrag command and it gives exactly the same
results before and after.
Am i doing something very wrong?
Cheers in advance,
j
Hi, can you show an output of DBCC command?
"jumblesale" <mcgants@.gmail.com> wrote in message
news:1158146780.765411.233730@.e3g2000cwe.googlegro ups.com...
> Hello,
> I have a very fragmented database and am running dbcc dbreindex but
> getting a very odd issue.
> When running DBCC SHOWCONTIG before and after reindexing the tables,
> there is no difference in the statistics. The scan fragmentation levels
> (in fact all of the statistics) are exactly the same.
> How can this be?
> I've also run the indexdefrag command and it gives exactly the same
> results before and after.
> Am i doing something very wrong?
> Cheers in advance,
> j
>
|||Hi,
Based on the SHOWCONTIG result; take a look into Scan Density, Login scan
and Extend scan fragmentation.
Read this article; this help you to analyze more:-
http://www.sql-server-performance.co...gmentation.asp
Thanks
Hari
SQL Server MVP
"jumblesale" <mcgants@.gmail.com> wrote in message
news:1158146780.765411.233730@.e3g2000cwe.googlegro ups.com...
> Hello,
> I have a very fragmented database and am running dbcc dbreindex but
> getting a very odd issue.
> When running DBCC SHOWCONTIG before and after reindexing the tables,
> there is no difference in the statistics. The scan fragmentation levels
> (in fact all of the statistics) are exactly the same.
> How can this be?
> I've also run the indexdefrag command and it gives exactly the same
> results before and after.
> Am i doing something very wrong?
> Cheers in advance,
> j
>
|||On 13 Sep 2006 04:26:20 -0700, "jumblesale" <mcgants@.gmail.com> wrote:

>Hello,
>I have a very fragmented database and am running dbcc dbreindex but
>getting a very odd issue.
>When running DBCC SHOWCONTIG before and after reindexing the tables,
>there is no difference in the statistics. The scan fragmentation levels
>(in fact all of the statistics) are exactly the same.
>How can this be?
>I've also run the indexdefrag command and it gives exactly the same
>results before and after.
>Am i doing something very wrong?
Do you have any clustered indexes on the tables involved?
J.

DBCC showcontig

Hello,
I have a very fragmented database and am running dbcc dbreindex but
getting a very odd issue.
When running DBCC SHOWCONTIG before and after reindexing the tables,
there is no difference in the statistics. The scan fragmentation levels
(in fact all of the statistics) are exactly the same.
How can this be?
I've also run the indexdefrag command and it gives exactly the same
results before and after.
Am i doing something very wrong'
Cheers in advance,
jHi, can you show an output of DBCC command?
"jumblesale" <mcgants@.gmail.com> wrote in message
news:1158146780.765411.233730@.e3g2000cwe.googlegroups.com...
> Hello,
> I have a very fragmented database and am running dbcc dbreindex but
> getting a very odd issue.
> When running DBCC SHOWCONTIG before and after reindexing the tables,
> there is no difference in the statistics. The scan fragmentation levels
> (in fact all of the statistics) are exactly the same.
> How can this be?
> I've also run the indexdefrag command and it gives exactly the same
> results before and after.
> Am i doing something very wrong'
> Cheers in advance,
> j
>|||Hi,
Based on the SHOWCONTIG result; take a look into Scan Density, Login scan
and Extend scan fragmentation.
Read this article; this help you to analyze more:-
http://www.sql-server-performance.com/rd_index_fragmentation.asp
Thanks
Hari
SQL Server MVP
"jumblesale" <mcgants@.gmail.com> wrote in message
news:1158146780.765411.233730@.e3g2000cwe.googlegroups.com...
> Hello,
> I have a very fragmented database and am running dbcc dbreindex but
> getting a very odd issue.
> When running DBCC SHOWCONTIG before and after reindexing the tables,
> there is no difference in the statistics. The scan fragmentation levels
> (in fact all of the statistics) are exactly the same.
> How can this be?
> I've also run the indexdefrag command and it gives exactly the same
> results before and after.
> Am i doing something very wrong'
> Cheers in advance,
> j
>|||On 13 Sep 2006 04:26:20 -0700, "jumblesale" <mcgants@.gmail.com> wrote:
>Hello,
>I have a very fragmented database and am running dbcc dbreindex but
>getting a very odd issue.
>When running DBCC SHOWCONTIG before and after reindexing the tables,
>there is no difference in the statistics. The scan fragmentation levels
>(in fact all of the statistics) are exactly the same.
>How can this be?
>I've also run the indexdefrag command and it gives exactly the same
>results before and after.
>Am i doing something very wrong'
Do you have any clustered indexes on the tables involved?
J.sql

DBCC showcontig

Hello,
I have a very fragmented database and am running dbcc dbreindex but
getting a very odd issue.
When running DBCC SHOWCONTIG before and after reindexing the tables,
there is no difference in the statistics. The scan fragmentation levels
(in fact all of the statistics) are exactly the same.
How can this be?
I've also run the indexdefrag command and it gives exactly the same
results before and after.
Am i doing something very wrong'
Cheers in advance,
jHi, can you show an output of DBCC command?
"jumblesale" <mcgants@.gmail.com> wrote in message
news:1158146780.765411.233730@.e3g2000cwe.googlegroups.com...
> Hello,
> I have a very fragmented database and am running dbcc dbreindex but
> getting a very odd issue.
> When running DBCC SHOWCONTIG before and after reindexing the tables,
> there is no difference in the statistics. The scan fragmentation levels
> (in fact all of the statistics) are exactly the same.
> How can this be?
> I've also run the indexdefrag command and it gives exactly the same
> results before and after.
> Am i doing something very wrong'
> Cheers in advance,
> j
>|||Hi,
Based on the SHOWCONTIG result; take a look into Scan Density, Login scan
and Extend scan fragmentation.
Read this article; this help you to analyze more:-
http://www.sql-server-performance.c...agmentation.asp
Thanks
Hari
SQL Server MVP
"jumblesale" <mcgants@.gmail.com> wrote in message
news:1158146780.765411.233730@.e3g2000cwe.googlegroups.com...
> Hello,
> I have a very fragmented database and am running dbcc dbreindex but
> getting a very odd issue.
> When running DBCC SHOWCONTIG before and after reindexing the tables,
> there is no difference in the statistics. The scan fragmentation levels
> (in fact all of the statistics) are exactly the same.
> How can this be?
> I've also run the indexdefrag command and it gives exactly the same
> results before and after.
> Am i doing something very wrong'
> Cheers in advance,
> j
>|||On 13 Sep 2006 04:26:20 -0700, "jumblesale" <mcgants@.gmail.com> wrote:

>Hello,
>I have a very fragmented database and am running dbcc dbreindex but
>getting a very odd issue.
>When running DBCC SHOWCONTIG before and after reindexing the tables,
>there is no difference in the statistics. The scan fragmentation levels
>(in fact all of the statistics) are exactly the same.
>How can this be?
>I've also run the indexdefrag command and it gives exactly the same
>results before and after.
>Am i doing something very wrong'
Do you have any clustered indexes on the tables involved?
J.

DBCC DBREINDEX question and problem

I have really bad database fragmentation, in some cases up to 95% fragmented. I continue to run DBCC DBREINDEX on these tables to try and fix this problem but from some reason, no matter how often I do it, I never see an increase in Scan Density. The tables in question do have more than 8 pages so I know that is not the issue. Anyone have any insight on this?

http://www.sql-server-performance.com/rd_index_fragmentation.asp

http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ss2kidbp.mspx

The above links should help you to understand the features available in SQL Server and resolve the defragmentation problems.

DBCC DBREINDEX and diskspace

I have a large table that is fragmented and causing poor query
performance. I want to use DBCC DBREINDEX to defragment them. I used
it previously and noticed that I almost ran out of drive space. Then
the files compacted down to a managebale size. The table has grown
again and I fear that it might run out of drive space.
What happens if DBCC DBREINDEX maxes out the drive space. Does it
partially created the index? Does it just not run because it knows it
doesnt have enough space to complete? Or does it just use as much as
it can use without running out of space?
It will fail and the index rebuild will be rolled back. You need 1.2X the
size of the data in your table in free space in order to use DBREINDEX. If
you cannot get more space, consider using DBCC INDEXDEFRAG.
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"dramos" <dan.ramos@.gartner.com> wrote in message
news:641f8b7.0404190802.2e6ca0e1@.posting.google.co m...
I have a large table that is fragmented and causing poor query
performance. I want to use DBCC DBREINDEX to defragment them. I used
it previously and noticed that I almost ran out of drive space. Then
the files compacted down to a managebale size. The table has grown
again and I fear that it might run out of drive space.
What happens if DBCC DBREINDEX maxes out the drive space. Does it
partially created the index? Does it just not run because it knows it
doesnt have enough space to complete? Or does it just use as much as
it can use without running out of space?
|||This helps me out alot:
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
ALTER procedure admin_DBCCDBReindex
as
declare @.Tablename varchar(128),@.db varchar(128),@.exec varchar(256)
set nocount on
set @.db = (select db_name())
declare @.Table table(TableName varchar(128))
insert into @.Table
select so.name
from sysobjects so
where so.name not like 'sys%' and
so.name not like 'dt%' and
so.name not like 'tmp%' and
so.xtype = 'u'
order by so.name
declare mycur cursor for (select TableName from @.Table)
open mycur
fetch next from mycur into @.TableName
while @.@.fetch_status = 0
begin
set @.exec = 'dbcc dbreindex (''' + @.db + '.' + 'dbo.' + @.TableName + ''')'
exec (@.exec)
BACKUP LOG [tmpReindex] TO [tmp]
WITH INIT , NOUNLOAD , NAME = N'tmp log backup', NOSKIP , STATS = 10,
NOFORMAT
fetch next from mycur into @.TableName
end
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
"dramos" <dan.ramos@.gartner.com> wrote in message
news:641f8b7.0404190802.2e6ca0e1@.posting.google.co m...
> I have a large table that is fragmented and causing poor query
> performance. I want to use DBCC DBREINDEX to defragment them. I used
> it previously and noticed that I almost ran out of drive space. Then
> the files compacted down to a managebale size. The table has grown
> again and I fear that it might run out of drive space.
> What happens if DBCC DBREINDEX maxes out the drive space. Does it
> partially created the index? Does it just not run because it knows it
> doesnt have enough space to complete? Or does it just use as much as
> it can use without running out of space?
|||As Tom suggests, consider DBCC INDEXDEFRAG. There's a whitepaper at
http://www.microsoft.com/technet/pro.../ss2kidbp.mspx
that explains the difference between the two, how to choose between them,
performance comparison etc.
Let us know if you have any more questions after reading the whitepaper.
Regards.
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"dramos" <dan.ramos@.gartner.com> wrote in message
news:641f8b7.0404190802.2e6ca0e1@.posting.google.co m...
> I have a large table that is fragmented and causing poor query
> performance. I want to use DBCC DBREINDEX to defragment them. I used
> it previously and noticed that I almost ran out of drive space. Then
> the files compacted down to a managebale size. The table has grown
> again and I fear that it might run out of drive space.
> What happens if DBCC DBREINDEX maxes out the drive space. Does it
> partially created the index? Does it just not run because it knows it
> doesnt have enough space to complete? Or does it just use as much as
> it can use without running out of space?

DBCC DBREINDEX and diskspace

I have a large table that is fragmented and causing poor query
performance. I want to use DBCC DBREINDEX to defragment them. I used
it previously and noticed that I almost ran out of drive space. Then
the files compacted down to a managebale size. The table has grown
again and I fear that it might run out of drive space.
What happens if DBCC DBREINDEX maxes out the drive space. Does it
partially created the index? Does it just not run because it knows it
doesnt have enough space to complete? Or does it just use as much as
it can use without running out of space?It will fail and the index rebuild will be rolled back. You need 1.2X the
size of the data in your table in free space in order to use DBREINDEX. If
you cannot get more space, consider using DBCC INDEXDEFRAG.
--
Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"dramos" <dan.ramos@.gartner.com> wrote in message
news:641f8b7.0404190802.2e6ca0e1@.posting.google.com...
I have a large table that is fragmented and causing poor query
performance. I want to use DBCC DBREINDEX to defragment them. I used
it previously and noticed that I almost ran out of drive space. Then
the files compacted down to a managebale size. The table has grown
again and I fear that it might run out of drive space.
What happens if DBCC DBREINDEX maxes out the drive space. Does it
partially created the index? Does it just not run because it knows it
doesnt have enough space to complete? Or does it just use as much as
it can use without running out of space?|||This helps me out alot:
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
ALTER procedure admin_DBCCDBReindex
as
declare @.Tablename varchar(128),@.db varchar(128),@.exec varchar(256)
set nocount on
set @.db = (select db_name())
declare @.Table table(TableName varchar(128))
insert into @.Table
select so.name
from sysobjects so
where so.name not like 'sys%' and
so.name not like 'dt%' and
so.name not like 'tmp%' and
so.xtype = 'u'
order by so.name
declare mycur cursor for (select TableName from @.Table)
open mycur
fetch next from mycur into @.TableName
while @.@.fetch_status = 0
begin
set @.exec = 'dbcc dbreindex (''' + @.db + '.' + 'dbo.' + @.TableName + ''')'
exec (@.exec)
BACKUP LOG [tmpReindex] TO [tmp]
WITH INIT , NOUNLOAD , NAME = N'tmp log backup', NOSKIP , STATS = 10,
NOFORMAT
fetch next from mycur into @.TableName
end
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
"dramos" <dan.ramos@.gartner.com> wrote in message
news:641f8b7.0404190802.2e6ca0e1@.posting.google.com...
> I have a large table that is fragmented and causing poor query
> performance. I want to use DBCC DBREINDEX to defragment them. I used
> it previously and noticed that I almost ran out of drive space. Then
> the files compacted down to a managebale size. The table has grown
> again and I fear that it might run out of drive space.
> What happens if DBCC DBREINDEX maxes out the drive space. Does it
> partially created the index? Does it just not run because it knows it
> doesnt have enough space to complete? Or does it just use as much as
> it can use without running out of space?|||As Tom suggests, consider DBCC INDEXDEFRAG. There's a whitepaper at
http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ss2kidbp.mspx
that explains the difference between the two, how to choose between them,
performance comparison etc.
Let us know if you have any more questions after reading the whitepaper.
Regards.
--
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"dramos" <dan.ramos@.gartner.com> wrote in message
news:641f8b7.0404190802.2e6ca0e1@.posting.google.com...
> I have a large table that is fragmented and causing poor query
> performance. I want to use DBCC DBREINDEX to defragment them. I used
> it previously and noticed that I almost ran out of drive space. Then
> the files compacted down to a managebale size. The table has grown
> again and I fear that it might run out of drive space.
> What happens if DBCC DBREINDEX maxes out the drive space. Does it
> partially created the index? Does it just not run because it knows it
> doesnt have enough space to complete? Or does it just use as much as
> it can use without running out of space?

DBCC DBREINDEX and diskspace

I have a large table that is fragmented and causing poor query
performance. I want to use DBCC DBREINDEX to defragment them. I used
it previously and noticed that I almost ran out of drive space. Then
the files compacted down to a managebale size. The table has grown
again and I fear that it might run out of drive space.
What happens if DBCC DBREINDEX maxes out the drive space. Does it
partially created the index? Does it just not run because it knows it
doesnt have enough space to complete? Or does it just use as much as
it can use without running out of space?It will fail and the index rebuild will be rolled back. You need 1.2X the
size of the data in your table in free space in order to use DBREINDEX. If
you cannot get more space, consider using DBCC INDEXDEFRAG.
Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"dramos" <dan.ramos@.gartner.com> wrote in message
news:641f8b7.0404190802.2e6ca0e1@.posting.google.com...
I have a large table that is fragmented and causing poor query
performance. I want to use DBCC DBREINDEX to defragment them. I used
it previously and noticed that I almost ran out of drive space. Then
the files compacted down to a managebale size. The table has grown
again and I fear that it might run out of drive space.
What happens if DBCC DBREINDEX maxes out the drive space. Does it
partially created the index? Does it just not run because it knows it
doesnt have enough space to complete? Or does it just use as much as
it can use without running out of space?|||This helps me out alot:
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
ALTER procedure admin_DBCCDBReindex
as
declare @.Tablename varchar(128),@.db varchar(128),@.exec varchar(256)
set nocount on
set @.db = (select db_name())
declare @.Table table(TableName varchar(128))
insert into @.Table
select so.name
from sysobjects so
where so.name not like 'sys%' and
so.name not like 'dt%' and
so.name not like 'tmp%' and
so.xtype = 'u'
order by so.name
declare mycur cursor for (select TableName from @.Table)
open mycur
fetch next from mycur into @.TableName
while @.@.fetch_status = 0
begin
set @.exec = 'dbcc dbreindex (''' + @.db + '.' + 'dbo.' + @.TableName + ''')'
exec (@.exec)
BACKUP LOG [tmpReindex] TO [tmp]
WITH INIT , NOUNLOAD , NAME = N'tmp log backup', NOSKIP , STATS = 10,
NOFORMAT
fetch next from mycur into @.TableName
end
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
"dramos" <dan.ramos@.gartner.com> wrote in message
news:641f8b7.0404190802.2e6ca0e1@.posting.google.com...
> I have a large table that is fragmented and causing poor query
> performance. I want to use DBCC DBREINDEX to defragment them. I used
> it previously and noticed that I almost ran out of drive space. Then
> the files compacted down to a managebale size. The table has grown
> again and I fear that it might run out of drive space.
> What happens if DBCC DBREINDEX maxes out the drive space. Does it
> partially created the index? Does it just not run because it knows it
> doesnt have enough space to complete? Or does it just use as much as
> it can use without running out of space?|||As Tom suggests, consider DBCC INDEXDEFRAG. There's a whitepaper at
http://www.microsoft.com/technet/pr...n/ss2kidbp.mspx
that explains the difference between the two, how to choose between them,
performance comparison etc.
Let us know if you have any more questions after reading the whitepaper.
Regards.
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"dramos" <dan.ramos@.gartner.com> wrote in message
news:641f8b7.0404190802.2e6ca0e1@.posting.google.com...
> I have a large table that is fragmented and causing poor query
> performance. I want to use DBCC DBREINDEX to defragment them. I used
> it previously and noticed that I almost ran out of drive space. Then
> the files compacted down to a managebale size. The table has grown
> again and I fear that it might run out of drive space.
> What happens if DBCC DBREINDEX maxes out the drive space. Does it
> partially created the index? Does it just not run because it knows it
> doesnt have enough space to complete? Or does it just use as much as
> it can use without running out of space?