DBCC Shrinkfile

Dear All,
I have just tried to shrink a file, both in Enterprise
Manager and in Query Analyser.
Within EM it hung.
Within QA it said it shrinked it but when I looked at the
size of the file it was still the same size.
The code I used in QA was
dbcc shrinkfile (NAMEOFFILE, TRUNCATEONLY).
Can anyone gice me pointers ?
Thanks
JBy any chance, are you trying to shrink the tempdb data file? Sometimes,
that fails because there are temporary worktables in there that are still
being used, though QA would report as if the file had been shrunk.
--
Regards
Ray Mond|||No, I am shrinking a non system, non tempdb file.
Thanks
J
>--Original Message--
>By any chance, are you trying to shrink the tempdb data
file? Sometimes,
>that fails because there are temporary worktables in
there that are still
>being used, though QA would report as if the file had
been shrunk.
>--
>Regards
>Ray Mond
>
>.
>|||Hi,
Please look into the below article from MS. This will help you out in
shrinking the TX Log.
http://support.microsoft.com/default.aspx?scid=kb;en-us;272318
Thanks
Hari
MCDBA
"Julie" <anonymous@.discussions.microsoft.com> wrote in message
news:0b8601c3d9bb$08533b50$a101280a@.phx.gbl...
> Dear All,
> I have just tried to shrink a file, both in Enterprise
> Manager and in Query Analyser.
> Within EM it hung.
> Within QA it said it shrinked it but when I looked at the
> size of the file it was still the same size.
> The code I used in QA was
> dbcc shrinkfile (NAMEOFFILE, TRUNCATEONLY).
> Can anyone gice me pointers ?
> Thanks
> J|||Why don't you try DBCC SHRINKFILE(NAMEOFFILE, somesize) ? Your data might
be residing on the end of the file, which prevents any significant
truncation.
--
Regards
Ray Mond
"Julie" <anonymous@.discussions.microsoft.com> wrote in message
news:07ae01c3d9c0$9415a2e0$a501280a@.phx.gbl...
> No, I am shrinking a non system, non tempdb file.
> Thanks
> J
>
> >--Original Message--
> >By any chance, are you trying to shrink the tempdb data
> file? Sometimes,
> >that fails because there are temporary worktables in
> there that are still
> >being used, though QA would report as if the file had
> been shrunk.
> >
> >--
> >Regards
> >Ray Mond
> >
> >
> >.
> >|||THe command you are using with the Truncateonly parameter does NOT move any
of the data from the end of the file to the front of the file, it only
returns any unused end portion of the file... instead try something like
dbcc shrinkfile(nameoffile, targetfilesizeinMB)
this command will move data from the end to the front , then truncate all of
the unused portion ...
--
Wayne Snyder, MCDBA, SQL Server MVP
Computer Education Services Corporation (CESC), Charlotte, NC
www.computeredservices.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Julie" <anonymous@.discussions.microsoft.com> wrote in message
news:0b8601c3d9bb$08533b50$a101280a@.phx.gbl...
> Dear All,
> I have just tried to shrink a file, both in Enterprise
> Manager and in Query Analyser.
> Within EM it hung.
> Within QA it said it shrinked it but when I looked at the
> size of the file it was still the same size.
> The code I used in QA was
> dbcc shrinkfile (NAMEOFFILE, TRUNCATEONLY).
> Can anyone gice me pointers ?
> Thanks
> J|||Thanks Guys,
It worked using the dbcc shrinkfile(FileName, 4670) option
a couple of you guys sugested.
The main problem here was time. In this case it took over
20 minutes to shrink, and I was about to cancel it when it
worked ;)
Anyway thanks for that.
Question though, is there an automated way without using
DBCC Shrinkfile of moving all the data to the beginning of
the datafile before a DBCC Shrinkfile ?
Again thanks for your help
J
>--Original Message--
>THe command you are using with the Truncateonly parameter
does NOT move any
>of the data from the end of the file to the front of the
file, it only
>returns any unused end portion of the file... instead
try something like
>dbcc shrinkfile(nameoffile, targetfilesizeinMB)
>this command will move data from the end to the front ,
then truncate all of
>the unused portion ...
>--
>Wayne Snyder, MCDBA, SQL Server MVP
>Computer Education Services Corporation (CESC),
Charlotte, NC
>www.computeredservices.com
>(Please respond only to the newsgroups.)
>I support the Professional Association of SQL Server
(PASS) and it's
>community of SQL Server professionals.
>www.sqlpass.org
>
>"Julie" <anonymous@.discussions.microsoft.com> wrote in
message
>news:0b8601c3d9bb$08533b50$a101280a@.phx.gbl...
>> Dear All,
>> I have just tried to shrink a file, both in Enterprise
>> Manager and in Query Analyser.
>> Within EM it hung.
>> Within QA it said it shrinked it but when I looked at
the
>> size of the file it was still the same size.
>> The code I used in QA was
>> dbcc shrinkfile (NAMEOFFILE, TRUNCATEONLY).
>> Can anyone gice me pointers ?
>> Thanks
>> J
>
>.
>|||> Question though, is there an automated way without using
> DBCC Shrinkfile of moving all the data to the beginning of
> the datafile before a DBCC Shrinkfile ?
No - that's what shrink does.
Also, regarding your 20 minutes comment, the runtime of shrink is dependent
on a bunch of things, including how much you ask the file to shrink, the
distribution of free space at the start of the file, the speed of your IO
system, and most importantly, the amount of concurrent activity on the
system that may block shrink. Remember that shrink is an online operation
and so does not block concurrent activity - this means you can let it run
longer than your maintenance window as long as you can cope with the small
drop in workload throughput from having shrink working in the database.
Regards.
--
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Julie" <anonymous@.discussions.microsoft.com> wrote in message
news:0a2501c3d9e1$0740c4a0$a601280a@.phx.gbl...
> Thanks Guys,
> It worked using the dbcc shrinkfile(FileName, 4670) option
> a couple of you guys sugested.
> The main problem here was time. In this case it took over
> 20 minutes to shrink, and I was about to cancel it when it
> worked ;)
> Anyway thanks for that.
> Question though, is there an automated way without using
> DBCC Shrinkfile of moving all the data to the beginning of
> the datafile before a DBCC Shrinkfile ?
> Again thanks for your help
> J
>
>
> >--Original Message--
> >THe command you are using with the Truncateonly parameter
> does NOT move any
> >of the data from the end of the file to the front of the
> file, it only
> >returns any unused end portion of the file... instead
> try something like
> >
> >dbcc shrinkfile(nameoffile, targetfilesizeinMB)
> >
> >this command will move data from the end to the front ,
> then truncate all of
> >the unused portion ...
> >
> >--
> >Wayne Snyder, MCDBA, SQL Server MVP
> >Computer Education Services Corporation (CESC),
> Charlotte, NC
> >www.computeredservices.com
> >(Please respond only to the newsgroups.)
> >
> >I support the Professional Association of SQL Server
> (PASS) and it's
> >community of SQL Server professionals.
> >www.sqlpass.org
> >
> >
> >"Julie" <anonymous@.discussions.microsoft.com> wrote in
> message
> >news:0b8601c3d9bb$08533b50$a101280a@.phx.gbl...
> >> Dear All,
> >>
> >> I have just tried to shrink a file, both in Enterprise
> >> Manager and in Query Analyser.
> >>
> >> Within EM it hung.
> >>
> >> Within QA it said it shrinked it but when I looked at
> the
> >> size of the file it was still the same size.
> >>
> >> The code I used in QA was
> >>
> >> dbcc shrinkfile (NAMEOFFILE, TRUNCATEONLY).
> >>
> >> Can anyone gice me pointers ?
> >>
> >> Thanks
> >> J
> >
> >
> >.
> >|||Thanks Paul
>--Original Message--
>> Question though, is there an automated way without using
>> DBCC Shrinkfile of moving all the data to the beginning
of
>> the datafile before a DBCC Shrinkfile ?
>No - that's what shrink does.
>Also, regarding your 20 minutes comment, the runtime of
shrink is dependent
>on a bunch of things, including how much you ask the file
to shrink, the
>distribution of free space at the start of the file, the
speed of your IO
>system, and most importantly, the amount of concurrent
activity on the
>system that may block shrink. Remember that shrink is an
online operation
>and so does not block concurrent activity - this means
you can let it run
>longer than your maintenance window as long as you can
cope with the small
>drop in workload throughput from having shrink working in
the database.
>Regards.
>--
>Paul Randal
>Dev Lead, Microsoft SQL Server Storage Engine
>This posting is provided "AS IS" with no warranties, and
confers no rights.
>"Julie" <anonymous@.discussions.microsoft.com> wrote in
message
>news:0a2501c3d9e1$0740c4a0$a601280a@.phx.gbl...
>> Thanks Guys,
>> It worked using the dbcc shrinkfile(FileName, 4670)
option
>> a couple of you guys sugested.
>> The main problem here was time. In this case it took
over
>> 20 minutes to shrink, and I was about to cancel it when
it
>> worked ;)
>> Anyway thanks for that.
>> Question though, is there an automated way without using
>> DBCC Shrinkfile of moving all the data to the beginning
of
>> the datafile before a DBCC Shrinkfile ?
>> Again thanks for your help
>> J
>>
>>
>> >--Original Message--
>> >THe command you are using with the Truncateonly
parameter
>> does NOT move any
>> >of the data from the end of the file to the front of
the
>> file, it only
>> >returns any unused end portion of the file... instead
>> try something like
>> >
>> >dbcc shrinkfile(nameoffile, targetfilesizeinMB)
>> >
>> >this command will move data from the end to the front ,
>> then truncate all of
>> >the unused portion ...
>> >
>> >--
>> >Wayne Snyder, MCDBA, SQL Server MVP
>> >Computer Education Services Corporation (CESC),
>> Charlotte, NC
>> >www.computeredservices.com
>> >(Please respond only to the newsgroups.)
>> >
>> >I support the Professional Association of SQL Server
>> (PASS) and it's
>> >community of SQL Server professionals.
>> >www.sqlpass.org
>> >
>> >
>> >"Julie" <anonymous@.discussions.microsoft.com> wrote in
>> message
>> >news:0b8601c3d9bb$08533b50$a101280a@.phx.gbl...
>> >> Dear All,
>> >>
>> >> I have just tried to shrink a file, both in
Enterprise
>> >> Manager and in Query Analyser.
>> >>
>> >> Within EM it hung.
>> >>
>> >> Within QA it said it shrinked it but when I looked at
>> the
>> >> size of the file it was still the same size.
>> >>
>> >> The code I used in QA was
>> >>
>> >> dbcc shrinkfile (NAMEOFFILE, TRUNCATEONLY).
>> >>
>> >> Can anyone gice me pointers ?
>> >>
>> >> Thanks
>> >> J
>> >
>> >
>> >.
>> >
>
>.
>

DBCC SHRINKDATABASE not completing

We're running SQL 2005 Enterprise with a very large database (198,893,696
KB); we had been running DBCC SHRINKDATABASE as part of daily operations,
following deletion of about 4 million records, but found that starting late
last week it is no longer completing - even after 12 hours. (It would
normally take 30 minutes). Other operational steps are running ok. We're
not seeing any error entries. Ideas? Thank you.
Hi Jeffrey
First of all, you should seriously reconsider running DBCC SHRINKDATABASE on
a daily basis. It is an incredibly resource intensive operation, that can
end up hurting as much as help. Take a look here:
http://www.karaszi.com/SQLServer/info_dont_shrink.asp
http://blogs.msdn.com/sqlserverstorageengine/archive/2007/03/28/turn-auto-shrink-off.aspx
http://blogs.msdn.com/sqlserverstorageengine/archive/2007/04/15/how-to-avoid-using-shrink-in-sql-server-2005.aspx
If you have to use DBCC SHRINKDATABASE, you can look in the
sys.dm_exec_requests view, and look at the percent_complete column to verify
that the operation is making progress, and get a rough idea how much longer
it will take.
HTH
Kalen Delaney, SQL Server MVP
www.InsideSQLServer.com
http://sqlblog.com
"Jeffrey Howard" <JeffreyHoward@.discussions.microsoft.com> wrote in message
news:0C88913C-6AF3-4D90-8D08-49B9F0797F02@.microsoft.com...
> We're running SQL 2005 Enterprise with a very large database (198,893,696
> KB); we had been running DBCC SHRINKDATABASE as part of daily operations,
> following deletion of about 4 million records, but found that starting
> late
> last week it is no longer completing - even after 12 hours. (It would
> normally take 30 minutes). Other operational steps are running ok. We're
> not seeing any error entries. Ideas? Thank you.
|||Why shrink today, only to have it grow again tomorrow with the 4M
insert/delete operations? Also it would seem that 4M records in a 200GB
database isn't that much anyway.
TheSQLGuru
President
Indicium Resources, Inc.
"Jeffrey Howard" <JeffreyHoward@.discussions.microsoft.com> wrote in message
news:0C88913C-6AF3-4D90-8D08-49B9F0797F02@.microsoft.com...
> We're running SQL 2005 Enterprise with a very large database (198,893,696
> KB); we had been running DBCC SHRINKDATABASE as part of daily operations,
> following deletion of about 4 million records, but found that starting
> late
> last week it is no longer completing - even after 12 hours. (It would
> normally take 30 minutes). Other operational steps are running ok. We're
> not seeing any error entries. Ideas? Thank you.

DBCC SHRINKDATABASE not completing

We're running SQL 2005 Enterprise with a very large database (198,893,696
KB); we had been running DBCC SHRINKDATABASE as part of daily operations,
following deletion of about 4 million records, but found that starting late
last week it is no longer completing - even after 12 hours. (It would
normally take 30 minutes). Other operational steps are running ok. We're
not seeing any error entries. Ideas? Thank you.Hi Jeffrey
First of all, you should seriously reconsider running DBCC SHRINKDATABASE on
a daily basis. It is an incredibly resource intensive operation, that can
end up hurting as much as help. Take a look here:
http://www.karaszi.com/SQLServer/info_dont_shrink.asp
http://blogs.msdn.com/sqlserverstor...
ff.aspx
http://blogs.msdn.com/sqlserverstor...erver-2005.aspx
If you have to use DBCC SHRINKDATABASE, you can look in the
sys.dm_exec_requests view, and look at the percent_complete column to verify
that the operation is making progress, and get a rough idea how much longer
it will take.
HTH
Kalen Delaney, SQL Server MVP
www.InsideSQLServer.com
http://sqlblog.com
"Jeffrey Howard" <JeffreyHoward@.discussions.microsoft.com> wrote in message
news:0C88913C-6AF3-4D90-8D08-49B9F0797F02@.microsoft.com...
> We're running SQL 2005 Enterprise with a very large database (198,893,696
> KB); we had been running DBCC SHRINKDATABASE as part of daily operations,
> following deletion of about 4 million records, but found that starting
> late
> last week it is no longer completing - even after 12 hours. (It would
> normally take 30 minutes). Other operational steps are running ok. We're
> not seeing any error entries. Ideas? Thank you.|||Why shrink today, only to have it grow again tomorrow with the 4M
insert/delete operations' Also it would seem that 4M records in a 200GB
database isn't that much anyway.
TheSQLGuru
President
Indicium Resources, Inc.
"Jeffrey Howard" <JeffreyHoward@.discussions.microsoft.com> wrote in message
news:0C88913C-6AF3-4D90-8D08-49B9F0797F02@.microsoft.com...
> We're running SQL 2005 Enterprise with a very large database (198,893,696
> KB); we had been running DBCC SHRINKDATABASE as part of daily operations,
> following deletion of about 4 million records, but found that starting
> late
> last week it is no longer completing - even after 12 hours. (It would
> normally take 30 minutes). Other operational steps are running ok. We're
> not seeing any error entries. Ideas? Thank you.

DBCC SHRINKDATABASE not completing

We're running SQL 2005 Enterprise with a very large database (198,893,696
KB); we had been running DBCC SHRINKDATABASE as part of daily operations,
following deletion of about 4 million records, but found that starting late
last week it is no longer completing - even after 12 hours. (It would
normally take 30 minutes). Other operational steps are running ok. We're
not seeing any error entries. Ideas? Thank you.Hi Jeffrey
First of all, you should seriously reconsider running DBCC SHRINKDATABASE on
a daily basis. It is an incredibly resource intensive operation, that can
end up hurting as much as help. Take a look here:
http://www.karaszi.com/SQLServer/info_dont_shrink.asp
http://blogs.msdn.com/sqlserverstorageengine/archive/2007/03/28/turn-auto-shrink-off.aspx
http://blogs.msdn.com/sqlserverstorageengine/archive/2007/04/15/how-to-avoid-using-shrink-in-sql-server-2005.aspx
If you have to use DBCC SHRINKDATABASE, you can look in the
sys.dm_exec_requests view, and look at the percent_complete column to verify
that the operation is making progress, and get a rough idea how much longer
it will take.
--
HTH
Kalen Delaney, SQL Server MVP
www.InsideSQLServer.com
http://sqlblog.com
"Jeffrey Howard" <JeffreyHoward@.discussions.microsoft.com> wrote in message
news:0C88913C-6AF3-4D90-8D08-49B9F0797F02@.microsoft.com...
> We're running SQL 2005 Enterprise with a very large database (198,893,696
> KB); we had been running DBCC SHRINKDATABASE as part of daily operations,
> following deletion of about 4 million records, but found that starting
> late
> last week it is no longer completing - even after 12 hours. (It would
> normally take 30 minutes). Other operational steps are running ok. We're
> not seeing any error entries. Ideas? Thank you.|||Why shrink today, only to have it grow again tomorrow with the 4M
insert/delete operations' Also it would seem that 4M records in a 200GB
database isn't that much anyway.
--
TheSQLGuru
President
Indicium Resources, Inc.
"Jeffrey Howard" <JeffreyHoward@.discussions.microsoft.com> wrote in message
news:0C88913C-6AF3-4D90-8D08-49B9F0797F02@.microsoft.com...
> We're running SQL 2005 Enterprise with a very large database (198,893,696
> KB); we had been running DBCC SHRINKDATABASE as part of daily operations,
> following deletion of about 4 million records, but found that starting
> late
> last week it is no longer completing - even after 12 hours. (It would
> normally take 30 minutes). Other operational steps are running ok. We're
> not seeing any error entries. Ideas? Thank you.

DBCC SHRINKDATABASE

Hello Gurus
In the enterprise manager, when I click on a database, the properties on the right hand side shows that there is 6 GB free space in the secondary data file. However, issuing a DBCC SHRINKDATABASE does not seem to do anything to shrink the database. I have tried SHRINKFILE as well. Why could this be happening
Thanks
Alex GYou have open transactions?
http://www.aspfaq.com/2471
--
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"Alex G" <anonymous@.discussions.microsoft.com> wrote in message
news:6409A05E-E35A-4E3E-A1D3-4B5DCE741E5D@.microsoft.com...
> Hello Gurus,
> In the enterprise manager, when I click on a database, the properties on
the right hand side shows that there is 6 GB free space in the secondary
data file. However, issuing a DBCC SHRINKDATABASE does not seem to do
anything to shrink the database. I have tried SHRINKFILE as well. Why could
this be happening?
> Thanks,
> Alex G|||Try this
backup log DBName with truncate_only
dbcc shrinkfile ('DBname',0,truncateonly)
dbcc shrinkfile ('DBName',0,truncateonly)
>--Original Message--
>No open transactions.
>DB Size = 11 GB
>Free space = 6 GB
>How can I get that 6 GB back? We are doing a lot of
detaching and attaching programatically and it is PIA
trying to move that big file around especially when you
KNOW there is free space.
>Please help!!
> -- Aaron Bertrand - MVP wrote: --
> You have open transactions?
> http://www.aspfaq.com/2471
> --
> Aaron Bertrand
> SQL Server MVP
> http://www.aspfaq.com/
>
>
> "Alex G" <anonymous@.discussions.microsoft.com>
wrote in message
> news:6409A05E-E35A-4E3E-A1D3-
4B5DCE741E5D@.microsoft.com...
> > Hello Gurus,
> >> In the enterprise manager, when I click on a
database, the properties on
> the right hand side shows that there is 6 GB free
space in the secondary
> data file. However, issuing a DBCC SHRINKDATABASE
does not seem to do
> anything to shrink the database. I have tried
SHRINKFILE as well. Why could
> this be happening?
> >> Thanks,
> > Alex G
>
>.
>

DBCC SHRINKDATABASE

Hello Gurus,
In the enterprise manager, when I click on a database, the properties on the
right hand side shows that there is 6 GB free space in the secondary data f
ile. However, issuing a DBCC SHRINKDATABASE does not seem to do anything to
shrink the database. I have
tried SHRINKFILE as well. Why could this be happening?
Thanks,
Alex Gneed to use the shrinkfile with truncateonly option to ensure that unused sp
ace is returned to the OS, however if U do not have disk space/contraint iss
ues then it's better to leave the data files as it is to reduce I/O issues a
t a later time during fileg
rowth.|||You have open transactions?
http://www.aspfaq.com/2471
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"Alex G" <anonymous@.discussions.microsoft.com> wrote in message
news:6409A05E-E35A-4E3E-A1D3-4B5DCE741E5D@.microsoft.com...
> Hello Gurus,
> In the enterprise manager, when I click on a database, the properties on
the right hand side shows that there is 6 GB free space in the secondary
data file. However, issuing a DBCC SHRINKDATABASE does not seem to do
anything to shrink the database. I have tried SHRINKFILE as well. Why could
this be happening?
> Thanks,
> Alex G|||No open transactions.
DB Size = 11 GB
Free space = 6 GB
How can I get that 6 GB back? We are doing a lot of detaching and attaching
programatically and it is PIA trying to move that big file around especially
when you KNOW there is free space.
Please help!!
-- Aaron Bertrand - MVP wrote: --
You have open transactions?
http://www.aspfaq.com/2471
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"Alex G" <anonymous@.discussions.microsoft.com> wrote in message
news:6409A05E-E35A-4E3E-A1D3-4B5DCE741E5D@.microsoft.com...
> Hello Gurus,
the right hand side shows that there is 6 GB free space in the secondary
data file. However, issuing a DBCC SHRINKDATABASE does not seem to do
anything to shrink the database. I have tried SHRINKFILE as well. Why could
this be happening?
> Alex G|||Try this
backup log DBName with truncate_only
dbcc shrinkfile ('DBname',0,truncateonly)
dbcc shrinkfile ('DBName',0,truncateonly)

>--Original Message--
>No open transactions.
>DB Size = 11 GB
>Free space = 6 GB
>How can I get that 6 GB back? We are doing a lot of
detaching and attaching programatically and it is PIA
trying to move that big file around especially when you
KNOW there is free space.
>Please help!!
> -- Aaron Bertrand - MVP wrote: --
> You have open transactions?
> http://www.aspfaq.com/2471
> --
> Aaron Bertrand
> SQL Server MVP
> http://www.aspfaq.com/
>
>
> "Alex G" <anonymous@.discussions.microsoft.com>
wrote in message
> news:6409A05E-E35A-4E3E-A1D3-
4B5DCE741E5D@.microsoft.com...
database, the properties on
> the right hand side shows that there is 6 GB free
space in the secondary
> data file. However, issuing a DBCC SHRINKDATABASE
does not seem to do
> anything to shrink the database. I have tried
SHRINKFILE as well. Why could
> this be happening?
>
>.
>sql

DBCC INDEXDEFRAG and Transaction Log

Hi,
Today I met a problem with DBCC INDEXDEFRAG on SQL Server 2000 Enterprise
with SP3. I run DBCC INDEXDEFRAG on a table with only one primary key. The
size of the table is about 120MB, the size of the index is about 1.6MB.
Before running DBCC INDEXDEFRAG, I run DBCC SHOWCONTIG and found the Logical
Scan Fragmentation was about 10%. The problem is that the transaction log I
backup after running DBCC INDEXDEFRAG on this table is about 600MB. I did
backup transaction log before this operation. There is no other activities
on this server. And I checked there wasn't any open transaction. I don't
know why DBCC INDEXDEFRAG on a small index created so big transaction log.
I appreciate any information and help !
Thanks!
BillBill Wang wrote:
> Hi,
> Today I met a problem with DBCC INDEXDEFRAG on SQL Server 2000
> Enterprise with SP3. I run DBCC INDEXDEFRAG on a table with only one
> primary key. The size of the table is about 120MB, the size of the
> index is about 1.6MB. Before running DBCC INDEXDEFRAG, I run DBCC
> SHOWCONTIG and found the Logical Scan Fragmentation was about 10%.
> The problem is that the transaction log I backup after running DBCC
> INDEXDEFRAG on this table is about 600MB. I did backup transaction
> log before this operation. There is no other activities on this
> server. And I checked there wasn't any open transaction. I don't
> know why DBCC INDEXDEFRAG on a small index created so big
> transaction log.
> I appreciate any information and help !
> Thanks!
>
> Bill
A problem existed in SQL 2000 RTM, but was fixed in SP1. Can you confirm
you are running SP3.
http://support.microsoft.com/kb/q282286/
THe following article may explain some of the looging requirements. See
the "Logging Considerations: DBCC DBREINDEX vs. DBCC INDEXDEFRAG"
section.
http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ss2kidbp.mspx
David Gugick
Quest Software
www.imceda.com
www.quest.com|||Yes, David. It's SP3. I read both of articles before this operation. I will
try on another table and comfirm this problem.
Thanks for oyur help!
Bill
"David Gugick" wrote:
> Bill Wang wrote:
> > Hi,
> > Today I met a problem with DBCC INDEXDEFRAG on SQL Server 2000
> > Enterprise with SP3. I run DBCC INDEXDEFRAG on a table with only one
> > primary key. The size of the table is about 120MB, the size of the
> > index is about 1.6MB. Before running DBCC INDEXDEFRAG, I run DBCC
> > SHOWCONTIG and found the Logical Scan Fragmentation was about 10%.
> > The problem is that the transaction log I backup after running DBCC
> > INDEXDEFRAG on this table is about 600MB. I did backup transaction
> > log before this operation. There is no other activities on this
> > server. And I checked there wasn't any open transaction. I don't
> > know why DBCC INDEXDEFRAG on a small index created so big
> > transaction log.
> >
> > I appreciate any information and help !
> >
> > Thanks!
> >
> >
> > Bill
> A problem existed in SQL 2000 RTM, but was fixed in SP1. Can you confirm
> you are running SP3.
> http://support.microsoft.com/kb/q282286/
> THe following article may explain some of the looging requirements. See
> the "Logging Considerations: DBCC DBREINDEX vs. DBCC INDEXDEFRAG"
> section.
> http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ss2kidbp.mspx
>
> --
> David Gugick
> Quest Software
> www.imceda.com
> www.quest.com
>

DBCC INDEXDEFRAG

I have SQL Server 2000 Enterprise Edition on my server.
I first execute DBCC SHOWCONTIG on my database.
Which outputs logicaldefrag, scan density, and etc.
I apply the DBCC INDEXDEFRAG (0, table_id, index_id) to
every table in my database.
Next I apply the sp_updatestats.
Lastly, I execute DBCC SHOWCONTIG to the database.
When I compare the DBCC SHOWCONTIG before and after
results from applying DBCC INDEXDEFRAG. I noticed the
logicalfrag has the same values?
Please help me fix this issue.
Thanks,
Mike
Well a couple things comes to mind. First off do these tables all have a
clustered index on them? How many pages in these tables? Can you show an
example of before and after?
Andrew J. Kelly SQL MVP
"Mike" <anonymous@.discussions.microsoft.com> wrote in message
news:27d5a01c46449$23fbc7f0$a601280a@.phx.gbl...
> I have SQL Server 2000 Enterprise Edition on my server.
> I first execute DBCC SHOWCONTIG on my database.
> Which outputs logicaldefrag, scan density, and etc.
> I apply the DBCC INDEXDEFRAG (0, table_id, index_id) to
> every table in my database.
> Next I apply the sp_updatestats.
> Lastly, I execute DBCC SHOWCONTIG to the database.
> When I compare the DBCC SHOWCONTIG before and after
> results from applying DBCC INDEXDEFRAG. I noticed the
> logicalfrag has the same values?
> Please help me fix this issue.
> Thanks,
> Mike
|||"Mike" wrote:
> [...]
> I first execute DBCC SHOWCONTIG on my database.
> Which outputs logicaldefrag, scan density, and etc.
> I apply the DBCC INDEXDEFRAG (0, table_id, index_id) to
> every table in my database.
> Next I apply the sp_updatestats.
> Lastly, I execute DBCC SHOWCONTIG to the database.
> When I compare the DBCC SHOWCONTIG before and after
> results from applying DBCC INDEXDEFRAG. I noticed the
> logicalfrag has the same values?
>
Mike,
I have noticed the same thing with indexes on large tables. I'm using the sample script from Books Online to run a nightly DBCC INDEXDEFRAG on indexes with higher than a certain level of fragmentation, but I notice that certain indexes are above the t
hreshold every night. If I run a DBCC DBREINDEX, I have much better results (the fragmentation usually goes down below 1%).
Normally, I would think it was my own mistake... but the strange thing is that many indexes seem to have their fragmentation reduced when running DBCC INDEXDEFRAG on them. I'm now considering giving up on INDEXDEFRAG for my data warehouse DBs...
Regards,
Jonathan
|||What's the reported number of pages moved when running DBCC INDEXDEFRAG? Can
you post sample DBCC SHOWCONTIG output please?
Thanks
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Jonathan Levine" <myfoo@.nospam.nospam> wrote in message
news:9E111CD5-2CD0-4071-B3A9-39E3D5CA4978@.microsoft.com...
> "Mike" wrote:
> Mike,
> I have noticed the same thing with indexes on large tables. I'm using
the sample script from Books Online to run a nightly DBCC INDEXDEFRAG on
indexes with higher than a certain level of fragmentation, but I notice that
certain indexes are above the threshold every night. If I run a DBCC
DBREINDEX, I have much better results (the fragmentation usually goes down
below 1%).
> Normally, I would think it was my own mistake... but the strange thing
is that many indexes seem to have their fragmentation reduced when running
DBCC INDEXDEFRAG on them. I'm now considering giving up on INDEXDEFRAG for
my data warehouse DBs...
> Regards,
> Jonathan
|||Paul,
"Paul S Randal [MS]" wrote:
> "Jonathan Levine" <myfoo@.nospam.nospam> wrote in message
> news:9E111CD5-2CD0-4071-B3A9-39E3D5CA4978@.microsoft.com...
> What's the reported number of pages moved when running DBCC INDEXDEFRAG? Can
> you post sample DBCC SHOWCONTIG output please?
Thanks for your reply.
I'm still looking into this myself, and it's a bit more complicated than I originally reported.
I have a data warehouse that's updated early in the morning. The larger tables are in the order of 5 to 25 million rows. The updates vary in size from 100,000 to 2 million rows.
Before the update, I run INDEXDEFRAG on fragmented indexes. Then I do the update, and it's after the update that the index appears to be refragmented. So, for example, on a 26 million row table:
Before Defrag:
DBCC SHOWCONTIG scanning 'Historical_Rankings' table...
Table: 'Historical_Rankings' (2018106230); index ID: 14, database ID: 8
LEAF level scan performed.
- Pages Scanned........................: 121895
- Extents Scanned.......................: 15316
- Extent Switches.......................: 88061
- Avg. Pages per Extent..................: 8.0
- Scan Density [Best Count:Actual Count]......: 17.30% [15237:88062]
- Logical Scan Fragmentation ..............: 25.04%
- Extent Scan Fragmentation ...............: 44.72%
- Avg. Bytes Free per Page................: 3012.7
- Avg. Page Density (full)................: 62.78%
INDEXDEFRAG scanned 121891, moved 76889, removed 44996
After defrag:
Table: 'Historical_Rankings' (2018106230); index ID: 14, database ID: 8
LEAF level scan performed.
- Pages Scanned........................: 76899
- Extents Scanned.......................: 9670
- Extent Switches.......................: 37754
- Avg. Pages per Extent..................: 8.0
- Scan Density [Best Count:Actual Count]......: 25.46% [9613:37755]
- Logical Scan Fragmentation ..............: 0.01%
- Extent Scan Fragmentation ...............: 61.40%
- Avg. Bytes Free per Page................: 38.3
- Avg. Page Density (full)................: 99.53%
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
After inserting 270,000 rows:
DBCC SHOWCONTIG scanning 'Historical_Rankings' table...
Table: 'Historical_Rankings' (2018106230); index ID: 14, database ID: 8
LEAF level scan performed.
- Pages Scanned........................: 123077
- Extents Scanned.......................: 15464
- Extent Switches.......................: 88745
- Avg. Pages per Extent..................: 8.0
- Scan Density [Best Count:Actual Count]......: 17.34% [15385:88746]
- Logical Scan Fragmentation ..............: 26.34%
- Extent Scan Fragmentation ...............: 65.66%
- Avg. Bytes Free per Page................: 3009.3
- Avg. Page Density (full)................: 62.82%
So... it's very strange to me that, after inserting about 1% new rows, and not deleting or updating any rows, the fragmentation could go from .01% to 26.34%.
I haven't had a chance to fully document this yet, but it seems to me that DBREINDEX results in a substantially better Scan Density, and that subsequent inserts do not result in such extreme changes in Logical Scan Fragmentation. If you think it's helpfu
l, I'll pursue that.
Regards,
Jonathan
|||> So... it's very strange to me that, after inserting about 1% new rows, and
not deleting or updating any rows, the fragmentation >could go from .01% to
26.34%.
This isn't strange at all since your pages are basically 100% full before
the inserts. That means that almost any insert will cause a page split and
the results of the showcontig shows that there was a lot of it. If your
going to do a lot of inserts you should consider setting an appropriate fill
factor and possibly Padindex as well. Leaving more free space on the page
will mean more pages in the database but will result in less pagesplits and
lots less work during the inserts.
Andrew J. Kelly SQL MVP
"Jonathan Levine" <myfoo@.nospam.nospam> wrote in message
news:70809313-9024-428C-BD37-3D035CB44453@.microsoft.com...[vbcol=seagreen]
> Paul,
> "Paul S Randal [MS]" wrote:
Can
> Thanks for your reply.
> I'm still looking into this myself, and it's a bit more complicated than I
originally reported.
> I have a data warehouse that's updated early in the morning. The larger
tables are in the order of 5 to 25 million rows. The updates vary in size
from 100,000 to 2 million rows.
> Before the update, I run INDEXDEFRAG on fragmented indexes. Then I do the
update, and it's after the update that the index appears to be refragmented.
So, for example, on a 26 million row table:
> Before Defrag:
> DBCC SHOWCONTIG scanning 'Historical_Rankings' table...
> Table: 'Historical_Rankings' (2018106230); index ID: 14, database ID: 8
> LEAF level scan performed.
> - Pages Scanned........................: 121895
> - Extents Scanned.......................: 15316
> - Extent Switches.......................: 88061
> - Avg. Pages per Extent..................: 8.0
> - Scan Density [Best Count:Actual Count]......: 17.30% [15237:88062]
> - Logical Scan Fragmentation ..............: 25.04%
> - Extent Scan Fragmentation ...............: 44.72%
> - Avg. Bytes Free per Page................: 3012.7
> - Avg. Page Density (full)................: 62.78%
> INDEXDEFRAG scanned 121891, moved 76889, removed 44996
> After defrag:
> Table: 'Historical_Rankings' (2018106230); index ID: 14, database ID: 8
> LEAF level scan performed.
> - Pages Scanned........................: 76899
> - Extents Scanned.......................: 9670
> - Extent Switches.......................: 37754
> - Avg. Pages per Extent..................: 8.0
> - Scan Density [Best Count:Actual Count]......: 25.46% [9613:37755]
> - Logical Scan Fragmentation ..............: 0.01%
> - Extent Scan Fragmentation ...............: 61.40%
> - Avg. Bytes Free per Page................: 38.3
> - Avg. Page Density (full)................: 99.53%
> DBCC execution completed. If DBCC printed error messages, contact your
system administrator.
> After inserting 270,000 rows:
> DBCC SHOWCONTIG scanning 'Historical_Rankings' table...
> Table: 'Historical_Rankings' (2018106230); index ID: 14, database ID: 8
> LEAF level scan performed.
> - Pages Scanned........................: 123077
> - Extents Scanned.......................: 15464
> - Extent Switches.......................: 88745
> - Avg. Pages per Extent..................: 8.0
> - Scan Density [Best Count:Actual Count]......: 17.34% [15385:88746]
> - Logical Scan Fragmentation ..............: 26.34%
> - Extent Scan Fragmentation ...............: 65.66%
> - Avg. Bytes Free per Page................: 3009.3
> - Avg. Page Density (full)................: 62.82%
> So... it's very strange to me that, after inserting about 1% new rows, and
not deleting or updating any rows, the fragmentation could go from .01% to
26.34%.
> I haven't had a chance to fully document this yet, but it seems to me that
DBREINDEX results in a substantially better Scan Density, and that
subsequent inserts do not result in such extreme changes in Logical Scan
Fragmentation. If you think it's helpful, I'll pursue that.
> Regards,
> Jonathan
|||"Andrew J. Kelly" wrote:
> "Jonathan Levine" <myfoo@.nospam.nospam> wrote in message
> news:70809313-9024-428C-BD37-3D035CB44453@.microsoft.com...
> system administrator.
> This isn't strange at all since your pages are basically 100% full before
> the inserts. That means that almost any insert will cause a page split and
> the results of the showcontig shows that there was a lot of it. If your
> going to do a lot of inserts you should consider setting an appropriate fill
> factor and possibly Padindex as well. Leaving more free space on the page
> will mean more pages in the database but will result in less pagesplits and
> lots less work during the inserts.
Hi Andrew.
I guess that 270K inserts seems like a lot in absolute terms, but relative to the size of the table, it's pretty small. So I guess I'm surprised that one in 6 inserts would cause a page split when I'm changing the table by only 1%. I'm also suprised tha
t simply splitting pages would cause the fragmentation to increase so dramatically.
I had originally created the index with no FILLFACTOR specified. (I thought this was supposed to default to a FILLFACTOR of 80). On your suggestion, I tried rebuilding the index with FILLFACTOR explicitly set to 80. Now DBCC SHOWCONTIG returns:
DBCC SHOWCONTIG scanning 'Historical_Rankings' table...
Table: 'Historical_Rankings' (2018106230); index ID: 14, database ID: 8
LEAF level scan performed.
- Pages Scanned........................: 96535
- Extents Scanned.......................: 12114
- Extent Switches.......................: 12113
- Avg. Pages per Extent..................: 8.0
- Scan Density [Best Count:Actual Count]......: 99.61% [12067:12114]
- Logical Scan Fragmentation ..............: 0.02%
- Extent Scan Fragmentation ...............: 49.70%
- Avg. Bytes Free per Page................: 1610.7
- Avg. Page Density (full)................: 80.10%
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
Note the great improvement in the index's Scan Density. And I also see this kind of improvement in Scan Density when I run DBCC DBREINDEX.
So... I'll keep an eye on it for the next few days. I'll also check into whether my other big indexes have similar FILLFACTOR issues. In the meantime, is Scan Density something that I should be worrying about?
|||> Hi Andrew.
> I guess that 270K inserts seems like a lot in absolute terms, but relative
to the size of the table, it's pretty small. So I guess I'm >surprised that
one in 6 inserts would cause a page split when I'm changing the table by
only 1%. I'm also surprised that simply
> splitting pages would cause the fragmentation to increase so dramatically.
>
When a page is that full (or usually even close) just one new row will cause
a page split since there simply isn't enough room for the new data. A
non-clustered index is essentially a table with a clustered index on the
index expression. That means the data at the leaf level have to basically
be in physical order. So when the data coming in is random (meaning the
value of the column(s) the index is based on) it usually gets placed in
between existing rows. If there is not enough room on the page it splits it
and moves half to another page. When you add new rows in index expression
order (such as a datetime that is in chronological order) they can simply be
appended to the end of the current page. When that page is full it creates
a new page and continues appending from there. This is not a page split and
essentially does not change the logical fragmentation since all the new
pages come directly after the previous ones. This KB is great for this type
stuff:
http://www.microsoft.com/technet/pro.../ss2kidbp.mspx

> I had originally created the index with no FILLFACTOR specified. (I
thought this was supposed to default to a FILLFACTOR of 80). On your
suggestion, I tried rebuilding the index with FILLFACTOR explicitly set to
80. Now DBCC SHOWCONTIG returns:
> DBCC SHOWCONTIG scanning 'Historical_Rankings' table...
> Table: 'Historical_Rankings' (2018106230); index ID: 14, database ID: 8
> LEAF level scan performed.
> - Pages Scanned........................: 96535
> - Extents Scanned.......................: 12114
> - Extent Switches.......................: 12113
> - Avg. Pages per Extent..................: 8.0
> - Scan Density [Best Count:Actual Count]......: 99.61% [12067:12114]
> - Logical Scan Fragmentation ..............: 0.02%
> - Extent Scan Fragmentation ...............: 49.70%
> - Avg. Bytes Free per Page................: 1610.7
> - Avg. Page Density (full)................: 80.10%
> DBCC execution completed. If DBCC printed error messages, contact your
system administrator.
> Note the great improvement in the index's Scan Density. And I also see
this kind of improvement in Scan Density when I run DBCC DBREINDEX.
> So... I'll keep an eye on it for the next few days. I'll also check into
whether my other big indexes have similar FILLFACTOR issues. In the
meantime, is Scan Density something that I should be worrying about?
Logical fragmentation is the most important factor that you should be
worried about. Especially if you have more than 1 file in the filegroup. In
that case Logical is the only one that you can really trust anyway. See the
KB I mentioned.
Andrew J. Kelly SQL MVP
|||Andrew's explanations are spot on.
Why do you consider Scan Density so important? What's the access pattern
over this index?
Do you notice query performance dropping after the insert activity? It's
entirely possible that everything you're doing is a waste of time - akin to
continually shrinking a database after it grows every day. Please read the
whitepaper below which explains a bunch about when to take action on
fragmentation.
http://www.microsoft.com/technet/pro.../ss2kidbp.mspx
Thanks and regards.
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Jonathan Levine" <myfoo@.nospam.nospam> wrote in message
news:05DA17D7-942D-452A-8973-ACCB2DB7A98C@.microsoft.com...[vbcol=seagreen]
> "Andrew J. Kelly" wrote:
8[vbcol=seagreen]
8[vbcol=seagreen]
and[vbcol=seagreen]
..01% to[vbcol=seagreen]
before[vbcol=seagreen]
and[vbcol=seagreen]
fill[vbcol=seagreen]
page[vbcol=seagreen]
and
> Hi Andrew.
> I guess that 270K inserts seems like a lot in absolute terms, but relative
to the size of the table, it's pretty small. So I guess I'm surprised that
one in 6 inserts would cause a page split when I'm changing the table by
only 1%. I'm also suprised that simply splitting pages would cause the
fragmentation to increase so dramatically.
> I had originally created the index with no FILLFACTOR specified. (I
thought this was supposed to default to a FILLFACTOR of 80). On your
suggestion, I tried rebuilding the index with FILLFACTOR explicitly set to
80. Now DBCC SHOWCONTIG returns:
> DBCC SHOWCONTIG scanning 'Historical_Rankings' table...
> Table: 'Historical_Rankings' (2018106230); index ID: 14, database ID: 8
> LEAF level scan performed.
> - Pages Scanned........................: 96535
> - Extents Scanned.......................: 12114
> - Extent Switches.......................: 12113
> - Avg. Pages per Extent..................: 8.0
> - Scan Density [Best Count:Actual Count]......: 99.61% [12067:12114]
> - Logical Scan Fragmentation ..............: 0.02%
> - Extent Scan Fragmentation ...............: 49.70%
> - Avg. Bytes Free per Page................: 1610.7
> - Avg. Page Density (full)................: 80.10%
> DBCC execution completed. If DBCC printed error messages, contact your
system administrator.
> Note the great improvement in the index's Scan Density. And I also see
this kind of improvement in Scan Density when I run DBCC DBREINDEX.
> So... I'll keep an eye on it for the next few days. I'll also check into
whether my other big indexes have similar FILLFACTOR issues. In the
meantime, is Scan Density something that I should be worrying about?
>
>
|||Thanks, Andrew. Very helpful stuff.
-- J

DBCC INDEXDEFRAG

I have SQL Server 2000 Enterprise Edition on my server.
I first execute DBCC SHOWCONTIG on my database.
Which outputs logicaldefrag, scan density, and etc.
I apply the DBCC INDEXDEFRAG (0, table_id, index_id) to
every table in my database.
Next I apply the sp_updatestats.
Lastly, I execute DBCC SHOWCONTIG to the database.
When I compare the DBCC SHOWCONTIG before and after
results from applying DBCC INDEXDEFRAG. I noticed the
logicalfrag has the same values?
Please help me fix this issue.
Thanks,
MikeWell a couple things comes to mind. First off do these tables all have a
clustered index on them? How many pages in these tables? Can you show an
example of before and after?
--
Andrew J. Kelly SQL MVP
"Mike" <anonymous@.discussions.microsoft.com> wrote in message
news:27d5a01c46449$23fbc7f0$a601280a@.phx.gbl...
> I have SQL Server 2000 Enterprise Edition on my server.
> I first execute DBCC SHOWCONTIG on my database.
> Which outputs logicaldefrag, scan density, and etc.
> I apply the DBCC INDEXDEFRAG (0, table_id, index_id) to
> every table in my database.
> Next I apply the sp_updatestats.
> Lastly, I execute DBCC SHOWCONTIG to the database.
> When I compare the DBCC SHOWCONTIG before and after
> results from applying DBCC INDEXDEFRAG. I noticed the
> logicalfrag has the same values?
> Please help me fix this issue.
> Thanks,
> Mike|||"Mike" wrote:
> [...]
> I first execute DBCC SHOWCONTIG on my database.
> Which outputs logicaldefrag, scan density, and etc.
> I apply the DBCC INDEXDEFRAG (0, table_id, index_id) to
> every table in my database.
> Next I apply the sp_updatestats.
> Lastly, I execute DBCC SHOWCONTIG to the database.
> When I compare the DBCC SHOWCONTIG before and after
> results from applying DBCC INDEXDEFRAG. I noticed the
> logicalfrag has the same values?
>
Mike,
I have noticed the same thing with indexes on large tables. I'm using the sample script from Books Online to run a nightly DBCC INDEXDEFRAG on indexes with higher than a certain level of fragmentation, but I notice that certain indexes are above the threshold every night. If I run a DBCC DBREINDEX, I have much better results (the fragmentation usually goes down below 1%).
Normally, I would think it was my own mistake... but the strange thing is that many indexes seem to have their fragmentation reduced when running DBCC INDEXDEFRAG on them. I'm now considering giving up on INDEXDEFRAG for my data warehouse DBs...
Regards,
Jonathan|||What's the reported number of pages moved when running DBCC INDEXDEFRAG? Can
you post sample DBCC SHOWCONTIG output please?
Thanks
--
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Jonathan Levine" <myfoo@.nospam.nospam> wrote in message
news:9E111CD5-2CD0-4071-B3A9-39E3D5CA4978@.microsoft.com...
> "Mike" wrote:
> > [...]
> > I first execute DBCC SHOWCONTIG on my database.
> >
> > Which outputs logicaldefrag, scan density, and etc.
> >
> > I apply the DBCC INDEXDEFRAG (0, table_id, index_id) to
> > every table in my database.
> >
> > Next I apply the sp_updatestats.
> >
> > Lastly, I execute DBCC SHOWCONTIG to the database.
> >
> > When I compare the DBCC SHOWCONTIG before and after
> > results from applying DBCC INDEXDEFRAG. I noticed the
> > logicalfrag has the same values?
> >
> Mike,
> I have noticed the same thing with indexes on large tables. I'm using
the sample script from Books Online to run a nightly DBCC INDEXDEFRAG on
indexes with higher than a certain level of fragmentation, but I notice that
certain indexes are above the threshold every night. If I run a DBCC
DBREINDEX, I have much better results (the fragmentation usually goes down
below 1%).
> Normally, I would think it was my own mistake... but the strange thing
is that many indexes seem to have their fragmentation reduced when running
DBCC INDEXDEFRAG on them. I'm now considering giving up on INDEXDEFRAG for
my data warehouse DBs...
> Regards,
> Jonathan|||Paul,
"Paul S Randal [MS]" wrote:
> "Jonathan Levine" <myfoo@.nospam.nospam> wrote in message
> news:9E111CD5-2CD0-4071-B3A9-39E3D5CA4978@.microsoft.com...
> What's the reported number of pages moved when running DBCC INDEXDEFRAG? Can
> you post sample DBCC SHOWCONTIG output please?
Thanks for your reply.
I'm still looking into this myself, and it's a bit more complicated than I originally reported.
I have a data warehouse that's updated early in the morning. The larger tables are in the order of 5 to 25 million rows. The updates vary in size from 100,000 to 2 million rows.
Before the update, I run INDEXDEFRAG on fragmented indexes. Then I do the update, and it's after the update that the index appears to be refragmented. So, for example, on a 26 million row table:
Before Defrag:
DBCC SHOWCONTIG scanning 'Historical_Rankings' table...
Table: 'Historical_Rankings' (2018106230); index ID: 14, database ID: 8
LEAF level scan performed.
- Pages Scanned........................: 121895
- Extents Scanned.......................: 15316
- Extent Switches.......................: 88061
- Avg. Pages per Extent..................: 8.0
- Scan Density [Best Count:Actual Count]......: 17.30% [15237:88062]
- Logical Scan Fragmentation ..............: 25.04%
- Extent Scan Fragmentation ...............: 44.72%
- Avg. Bytes Free per Page................: 3012.7
- Avg. Page Density (full)................: 62.78%
INDEXDEFRAG scanned 121891, moved 76889, removed 44996
After defrag:
Table: 'Historical_Rankings' (2018106230); index ID: 14, database ID: 8
LEAF level scan performed.
- Pages Scanned........................: 76899
- Extents Scanned.......................: 9670
- Extent Switches.......................: 37754
- Avg. Pages per Extent..................: 8.0
- Scan Density [Best Count:Actual Count]......: 25.46% [9613:37755]
- Logical Scan Fragmentation ..............: 0.01%
- Extent Scan Fragmentation ...............: 61.40%
- Avg. Bytes Free per Page................: 38.3
- Avg. Page Density (full)................: 99.53%
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
After inserting 270,000 rows:
DBCC SHOWCONTIG scanning 'Historical_Rankings' table...
Table: 'Historical_Rankings' (2018106230); index ID: 14, database ID: 8
LEAF level scan performed.
- Pages Scanned........................: 123077
- Extents Scanned.......................: 15464
- Extent Switches.......................: 88745
- Avg. Pages per Extent..................: 8.0
- Scan Density [Best Count:Actual Count]......: 17.34% [15385:88746]
- Logical Scan Fragmentation ..............: 26.34%
- Extent Scan Fragmentation ...............: 65.66%
- Avg. Bytes Free per Page................: 3009.3
- Avg. Page Density (full)................: 62.82%
So... it's very strange to me that, after inserting about 1% new rows, and not deleting or updating any rows, the fragmentation could go from .01% to 26.34%.
I haven't had a chance to fully document this yet, but it seems to me that DBREINDEX results in a substantially better Scan Density, and that subsequent inserts do not result in such extreme changes in Logical Scan Fragmentation. If you think it's helpful, I'll pursue that.
Regards,
Jonathan|||> So... it's very strange to me that, after inserting about 1% new rows, and
not deleting or updating any rows, the fragmentation >could go from .01% to
26.34%.
This isn't strange at all since your pages are basically 100% full before
the inserts. That means that almost any insert will cause a page split and
the results of the showcontig shows that there was a lot of it. If your
going to do a lot of inserts you should consider setting an appropriate fill
factor and possibly Padindex as well. Leaving more free space on the page
will mean more pages in the database but will result in less pagesplits and
lots less work during the inserts.
--
Andrew J. Kelly SQL MVP
"Jonathan Levine" <myfoo@.nospam.nospam> wrote in message
news:70809313-9024-428C-BD37-3D035CB44453@.microsoft.com...
> Paul,
> "Paul S Randal [MS]" wrote:
> > "Jonathan Levine" <myfoo@.nospam.nospam> wrote in message
> > news:9E111CD5-2CD0-4071-B3A9-39E3D5CA4978@.microsoft.com...
> > What's the reported number of pages moved when running DBCC INDEXDEFRAG?
Can
> > you post sample DBCC SHOWCONTIG output please?
> Thanks for your reply.
> I'm still looking into this myself, and it's a bit more complicated than I
originally reported.
> I have a data warehouse that's updated early in the morning. The larger
tables are in the order of 5 to 25 million rows. The updates vary in size
from 100,000 to 2 million rows.
> Before the update, I run INDEXDEFRAG on fragmented indexes. Then I do the
update, and it's after the update that the index appears to be refragmented.
So, for example, on a 26 million row table:
> Before Defrag:
> DBCC SHOWCONTIG scanning 'Historical_Rankings' table...
> Table: 'Historical_Rankings' (2018106230); index ID: 14, database ID: 8
> LEAF level scan performed.
> - Pages Scanned........................: 121895
> - Extents Scanned.......................: 15316
> - Extent Switches.......................: 88061
> - Avg. Pages per Extent..................: 8.0
> - Scan Density [Best Count:Actual Count]......: 17.30% [15237:88062]
> - Logical Scan Fragmentation ..............: 25.04%
> - Extent Scan Fragmentation ...............: 44.72%
> - Avg. Bytes Free per Page................: 3012.7
> - Avg. Page Density (full)................: 62.78%
> INDEXDEFRAG scanned 121891, moved 76889, removed 44996
> After defrag:
> Table: 'Historical_Rankings' (2018106230); index ID: 14, database ID: 8
> LEAF level scan performed.
> - Pages Scanned........................: 76899
> - Extents Scanned.......................: 9670
> - Extent Switches.......................: 37754
> - Avg. Pages per Extent..................: 8.0
> - Scan Density [Best Count:Actual Count]......: 25.46% [9613:37755]
> - Logical Scan Fragmentation ..............: 0.01%
> - Extent Scan Fragmentation ...............: 61.40%
> - Avg. Bytes Free per Page................: 38.3
> - Avg. Page Density (full)................: 99.53%
> DBCC execution completed. If DBCC printed error messages, contact your
system administrator.
> After inserting 270,000 rows:
> DBCC SHOWCONTIG scanning 'Historical_Rankings' table...
> Table: 'Historical_Rankings' (2018106230); index ID: 14, database ID: 8
> LEAF level scan performed.
> - Pages Scanned........................: 123077
> - Extents Scanned.......................: 15464
> - Extent Switches.......................: 88745
> - Avg. Pages per Extent..................: 8.0
> - Scan Density [Best Count:Actual Count]......: 17.34% [15385:88746]
> - Logical Scan Fragmentation ..............: 26.34%
> - Extent Scan Fragmentation ...............: 65.66%
> - Avg. Bytes Free per Page................: 3009.3
> - Avg. Page Density (full)................: 62.82%
> So... it's very strange to me that, after inserting about 1% new rows, and
not deleting or updating any rows, the fragmentation could go from .01% to
26.34%.
> I haven't had a chance to fully document this yet, but it seems to me that
DBREINDEX results in a substantially better Scan Density, and that
subsequent inserts do not result in such extreme changes in Logical Scan
Fragmentation. If you think it's helpful, I'll pursue that.
> Regards,
> Jonathan|||"Andrew J. Kelly" wrote:
> "Jonathan Levine" <myfoo@.nospam.nospam> wrote in message
> news:70809313-9024-428C-BD37-3D035CB44453@.microsoft.com...
> > After defrag:
> >
> > Table: 'Historical_Rankings' (2018106230); index ID: 14, database ID: 8
> > LEAF level scan performed.
> > - Pages Scanned........................: 76899
> > - Extents Scanned.......................: 9670
> > - Extent Switches.......................: 37754
> > - Avg. Pages per Extent..................: 8.0
> > - Scan Density [Best Count:Actual Count]......: 25.46% [9613:37755]
> > - Logical Scan Fragmentation ..............: 0.01%
> > - Extent Scan Fragmentation ...............: 61.40%
> > - Avg. Bytes Free per Page................: 38.3
> > - Avg. Page Density (full)................: 99.53%
> > DBCC execution completed. If DBCC printed error messages, contact your
> system administrator.
> >
> > After inserting 270,000 rows:
> > DBCC SHOWCONTIG scanning 'Historical_Rankings' table...
> > Table: 'Historical_Rankings' (2018106230); index ID: 14, database ID: 8
> > LEAF level scan performed.
> > - Pages Scanned........................: 123077
> > - Extents Scanned.......................: 15464
> > - Extent Switches.......................: 88745
> > - Avg. Pages per Extent..................: 8.0
> > - Scan Density [Best Count:Actual Count]......: 17.34% [15385:88746]
> > - Logical Scan Fragmentation ..............: 26.34%
> > - Extent Scan Fragmentation ...............: 65.66%
> > - Avg. Bytes Free per Page................: 3009.3
> > - Avg. Page Density (full)................: 62.82%
> >
> > So... it's very strange to me that, after inserting about 1% new rows, and
> > not deleting or updating any rows, the fragmentation could go from .01% to
> > 26.34%.
> >
> This isn't strange at all since your pages are basically 100% full before
> the inserts. That means that almost any insert will cause a page split and
> the results of the showcontig shows that there was a lot of it. If your
> going to do a lot of inserts you should consider setting an appropriate fill
> factor and possibly Padindex as well. Leaving more free space on the page
> will mean more pages in the database but will result in less pagesplits and
> lots less work during the inserts.
Hi Andrew.
I guess that 270K inserts seems like a lot in absolute terms, but relative to the size of the table, it's pretty small. So I guess I'm surprised that one in 6 inserts would cause a page split when I'm changing the table by only 1%. I'm also suprised that simply splitting pages would cause the fragmentation to increase so dramatically.
I had originally created the index with no FILLFACTOR specified. (I thought this was supposed to default to a FILLFACTOR of 80). On your suggestion, I tried rebuilding the index with FILLFACTOR explicitly set to 80. Now DBCC SHOWCONTIG returns:
DBCC SHOWCONTIG scanning 'Historical_Rankings' table...
Table: 'Historical_Rankings' (2018106230); index ID: 14, database ID: 8
LEAF level scan performed.
- Pages Scanned........................: 96535
- Extents Scanned.......................: 12114
- Extent Switches.......................: 12113
- Avg. Pages per Extent..................: 8.0
- Scan Density [Best Count:Actual Count]......: 99.61% [12067:12114]
- Logical Scan Fragmentation ..............: 0.02%
- Extent Scan Fragmentation ...............: 49.70%
- Avg. Bytes Free per Page................: 1610.7
- Avg. Page Density (full)................: 80.10%
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
Note the great improvement in the index's Scan Density. And I also see this kind of improvement in Scan Density when I run DBCC DBREINDEX.
So... I'll keep an eye on it for the next few days. I'll also check into whether my other big indexes have similar FILLFACTOR issues. In the meantime, is Scan Density something that I should be worrying about?|||> Hi Andrew.
> I guess that 270K inserts seems like a lot in absolute terms, but relative
to the size of the table, it's pretty small. So I guess I'm >surprised that
one in 6 inserts would cause a page split when I'm changing the table by
only 1%. I'm also surprised that simply
> splitting pages would cause the fragmentation to increase so dramatically.
>
When a page is that full (or usually even close) just one new row will cause
a page split since there simply isn't enough room for the new data. A
non-clustered index is essentially a table with a clustered index on the
index expression. That means the data at the leaf level have to basically
be in physical order. So when the data coming in is random (meaning the
value of the column(s) the index is based on) it usually gets placed in
between existing rows. If there is not enough room on the page it splits it
and moves half to another page. When you add new rows in index expression
order (such as a datetime that is in chronological order) they can simply be
appended to the end of the current page. When that page is full it creates
a new page and continues appending from there. This is not a page split and
essentially does not change the logical fragmentation since all the new
pages come directly after the previous ones. This KB is great for this type
stuff:
http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ss2kidbp.mspx
> I had originally created the index with no FILLFACTOR specified. (I
thought this was supposed to default to a FILLFACTOR of 80). On your
suggestion, I tried rebuilding the index with FILLFACTOR explicitly set to
80. Now DBCC SHOWCONTIG returns:
> DBCC SHOWCONTIG scanning 'Historical_Rankings' table...
> Table: 'Historical_Rankings' (2018106230); index ID: 14, database ID: 8
> LEAF level scan performed.
> - Pages Scanned........................: 96535
> - Extents Scanned.......................: 12114
> - Extent Switches.......................: 12113
> - Avg. Pages per Extent..................: 8.0
> - Scan Density [Best Count:Actual Count]......: 99.61% [12067:12114]
> - Logical Scan Fragmentation ..............: 0.02%
> - Extent Scan Fragmentation ...............: 49.70%
> - Avg. Bytes Free per Page................: 1610.7
> - Avg. Page Density (full)................: 80.10%
> DBCC execution completed. If DBCC printed error messages, contact your
system administrator.
> Note the great improvement in the index's Scan Density. And I also see
this kind of improvement in Scan Density when I run DBCC DBREINDEX.
> So... I'll keep an eye on it for the next few days. I'll also check into
whether my other big indexes have similar FILLFACTOR issues. In the
meantime, is Scan Density something that I should be worrying about?
Logical fragmentation is the most important factor that you should be
worried about. Especially if you have more than 1 file in the filegroup. In
that case Logical is the only one that you can really trust anyway. See the
KB I mentioned.
Andrew J. Kelly SQL MVP|||Andrew's explanations are spot on.
Why do you consider Scan Density so important? What's the access pattern
over this index?
Do you notice query performance dropping after the insert activity? It's
entirely possible that everything you're doing is a waste of time - akin to
continually shrinking a database after it grows every day. Please read the
whitepaper below which explains a bunch about when to take action on
fragmentation.
http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ss2kidbp.mspx
Thanks and regards.
--
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Jonathan Levine" <myfoo@.nospam.nospam> wrote in message
news:05DA17D7-942D-452A-8973-ACCB2DB7A98C@.microsoft.com...
> "Andrew J. Kelly" wrote:
> > "Jonathan Levine" <myfoo@.nospam.nospam> wrote in message
> > news:70809313-9024-428C-BD37-3D035CB44453@.microsoft.com...
> > > After defrag:
> > >
> > > Table: 'Historical_Rankings' (2018106230); index ID: 14, database ID:
8
> > > LEAF level scan performed.
> > > - Pages Scanned........................: 76899
> > > - Extents Scanned.......................: 9670
> > > - Extent Switches.......................: 37754
> > > - Avg. Pages per Extent..................: 8.0
> > > - Scan Density [Best Count:Actual Count]......: 25.46% [9613:37755]
> > > - Logical Scan Fragmentation ..............: 0.01%
> > > - Extent Scan Fragmentation ...............: 61.40%
> > > - Avg. Bytes Free per Page................: 38.3
> > > - Avg. Page Density (full)................: 99.53%
> > > DBCC execution completed. If DBCC printed error messages, contact your
> > system administrator.
> > >
> > > After inserting 270,000 rows:
> > > DBCC SHOWCONTIG scanning 'Historical_Rankings' table...
> > > Table: 'Historical_Rankings' (2018106230); index ID: 14, database ID:
8
> > > LEAF level scan performed.
> > > - Pages Scanned........................: 123077
> > > - Extents Scanned.......................: 15464
> > > - Extent Switches.......................: 88745
> > > - Avg. Pages per Extent..................: 8.0
> > > - Scan Density [Best Count:Actual Count]......: 17.34% [15385:88746]
> > > - Logical Scan Fragmentation ..............: 26.34%
> > > - Extent Scan Fragmentation ...............: 65.66%
> > > - Avg. Bytes Free per Page................: 3009.3
> > > - Avg. Page Density (full)................: 62.82%
> > >
> > > So... it's very strange to me that, after inserting about 1% new rows,
and
> > > not deleting or updating any rows, the fragmentation could go from
.01% to
> > > 26.34%.
> > >
> > This isn't strange at all since your pages are basically 100% full
before
> > the inserts. That means that almost any insert will cause a page split
and
> > the results of the showcontig shows that there was a lot of it. If your
> > going to do a lot of inserts you should consider setting an appropriate
fill
> > factor and possibly Padindex as well. Leaving more free space on the
page
> > will mean more pages in the database but will result in less pagesplits
and
> > lots less work during the inserts.
> Hi Andrew.
> I guess that 270K inserts seems like a lot in absolute terms, but relative
to the size of the table, it's pretty small. So I guess I'm surprised that
one in 6 inserts would cause a page split when I'm changing the table by
only 1%. I'm also suprised that simply splitting pages would cause the
fragmentation to increase so dramatically.
> I had originally created the index with no FILLFACTOR specified. (I
thought this was supposed to default to a FILLFACTOR of 80). On your
suggestion, I tried rebuilding the index with FILLFACTOR explicitly set to
80. Now DBCC SHOWCONTIG returns:
> DBCC SHOWCONTIG scanning 'Historical_Rankings' table...
> Table: 'Historical_Rankings' (2018106230); index ID: 14, database ID: 8
> LEAF level scan performed.
> - Pages Scanned........................: 96535
> - Extents Scanned.......................: 12114
> - Extent Switches.......................: 12113
> - Avg. Pages per Extent..................: 8.0
> - Scan Density [Best Count:Actual Count]......: 99.61% [12067:12114]
> - Logical Scan Fragmentation ..............: 0.02%
> - Extent Scan Fragmentation ...............: 49.70%
> - Avg. Bytes Free per Page................: 1610.7
> - Avg. Page Density (full)................: 80.10%
> DBCC execution completed. If DBCC printed error messages, contact your
system administrator.
> Note the great improvement in the index's Scan Density. And I also see
this kind of improvement in Scan Density when I run DBCC DBREINDEX.
> So... I'll keep an eye on it for the next few days. I'll also check into
whether my other big indexes have similar FILLFACTOR issues. In the
meantime, is Scan Density something that I should be worrying about?
>
>|||Thanks, Andrew. Very helpful stuff.
-- J|||Paul,
Thanks for your quick reply.
"Paul S Randal [MS]" wrote:
> Why do you consider Scan Density so important? What's the access pattern
> over this index?
Sorry if I wasn't clear. I don't know whether Scan Density is important or not. The KB articles are great about Logical Fragmentation, but they don't really seem to address Scan Density. I just observed that (a) it was low and (b) INDEXDEFRAG didn't seem to reduce the total number of extents as effectively as DBREINDEX. I don't know if the total number of pages is important, but I wonder how the fragmentation can be low and the average page density high when there seem to be a significant difference in the total number of extents... is the reason that DBREINDEX tends to group pages together better physically? And is that important?
> Do you notice query performance dropping after the insert activity? It's
> entirely possible that everything you're doing is a waste of time - akin to
> continually shrinking a database after it grows every day. Please read the
> whitepaper below which explains a bunch about when to take action on
> fragmentation.
Yes, the query performance seems to be inversely proportional to the fragmentation of the indexes. I don't have stats on individual queries, but the process which does the queries has become considerably faster after instituting nightly defragmentations. The insert process has not; however, I'm hoping that tuning the FILLFACTOR will help that.
-- J|||You shouldn't worry about Scan Density or Extent Scan Fragmentation. The
main cause of query slowdown is ineffective readahead during range scans
caused by Logical Scan Fragmentation. Extent Scan Fragmentation has only
minimal effect on readahead and so we're de-emphasizing its importance.
--
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Jonathan Levine" <myfoo@.nospam.nospam> wrote in message
news:3D9896FF-FD3A-4DF3-8F14-610E7C856F7F@.microsoft.com...
> Paul,
> Thanks for your quick reply.
> "Paul S Randal [MS]" wrote:
> > Why do you consider Scan Density so important? What's the access pattern
> > over this index?
> Sorry if I wasn't clear. I don't know whether Scan Density is important
or not. The KB articles are great about Logical Fragmentation, but they
don't really seem to address Scan Density. I just observed that (a) it was
low and (b) INDEXDEFRAG didn't seem to reduce the total number of extents as
effectively as DBREINDEX. I don't know if the total number of pages is
important, but I wonder how the fragmentation can be low and the average
page density high when there seem to be a significant difference in the
total number of extents... is the reason that DBREINDEX tends to group
pages together better physically? And is that important?
> > Do you notice query performance dropping after the insert activity? It's
> > entirely possible that everything you're doing is a waste of time - akin
to
> > continually shrinking a database after it grows every day. Please read
the
> > whitepaper below which explains a bunch about when to take action on
> > fragmentation.
> Yes, the query performance seems to be inversely proportional to the
fragmentation of the indexes. I don't have stats on individual queries, but
the process which does the queries has become considerably faster after
instituting nightly defragmentations. The insert process has not; however,
I'm hoping that tuning the FILLFACTOR will help that.
> -- J

DBCC INDEXDEFRAG

I have SQL Server 2000 Enterprise Edition on my server.
I first execute DBCC SHOWCONTIG on my database.
Which outputs logicaldefrag, scan density, and etc.
I apply the DBCC INDEXDEFRAG (0, table_id, index_id) to
every table in my database.
Next I apply the sp_updatestats.
Lastly, I execute DBCC SHOWCONTIG to the database.
When I compare the DBCC SHOWCONTIG before and after
results from applying DBCC INDEXDEFRAG. I noticed the
logicalfrag has the same values?
Please help me fix this issue.
Thanks,
MikeWell a couple things comes to mind. First off do these tables all have a
clustered index on them? How many pages in these tables? Can you show an
example of before and after?
Andrew J. Kelly SQL MVP
"Mike" <anonymous@.discussions.microsoft.com> wrote in message
news:27d5a01c46449$23fbc7f0$a601280a@.phx
.gbl...
> I have SQL Server 2000 Enterprise Edition on my server.
> I first execute DBCC SHOWCONTIG on my database.
> Which outputs logicaldefrag, scan density, and etc.
> I apply the DBCC INDEXDEFRAG (0, table_id, index_id) to
> every table in my database.
> Next I apply the sp_updatestats.
> Lastly, I execute DBCC SHOWCONTIG to the database.
> When I compare the DBCC SHOWCONTIG before and after
> results from applying DBCC INDEXDEFRAG. I noticed the
> logicalfrag has the same values?
> Please help me fix this issue.
> Thanks,
> Mike|||"Mike" wrote:
> [...]
> I first execute DBCC SHOWCONTIG on my database.
> Which outputs logicaldefrag, scan density, and etc.
> I apply the DBCC INDEXDEFRAG (0, table_id, index_id) to
> every table in my database.
> Next I apply the sp_updatestats.
> Lastly, I execute DBCC SHOWCONTIG to the database.
> When I compare the DBCC SHOWCONTIG before and after
> results from applying DBCC INDEXDEFRAG. I noticed the
> logicalfrag has the same values?
>
Mike,
I have noticed the same thing with indexes on large tables. I'm using the s
ample script from Books Online to run a nightly DBCC INDEXDEFRAG on indexes
with higher than a certain level of fragmentation, but I notice that certain
indexes are above the t
hreshold every night. If I run a DBCC DBREINDEX, I have much better results
(the fragmentation usually goes down below 1%).
Normally, I would think it was my own mistake... but the strange thing is th
at many indexes seem to have their fragmentation reduced when running DBCC I
NDEXDEFRAG on them. I'm now considering giving up on INDEXDEFRAG for my dat
a warehouse DBs...
Regards,
Jonathan|||What's the reported number of pages moved when running DBCC INDEXDEFRAG? Can
you post sample DBCC SHOWCONTIG output please?
Thanks
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Jonathan Levine" <myfoo@.nospam.nospam> wrote in message
news:9E111CD5-2CD0-4071-B3A9-39E3D5CA4978@.microsoft.com...
> "Mike" wrote:
> Mike,
> I have noticed the same thing with indexes on large tables. I'm using
the sample script from Books Online to run a nightly DBCC INDEXDEFRAG on
indexes with higher than a certain level of fragmentation, but I notice that
certain indexes are above the threshold every night. If I run a DBCC
DBREINDEX, I have much better results (the fragmentation usually goes down
below 1%).
> Normally, I would think it was my own mistake... but the strange thing
is that many indexes seem to have their fragmentation reduced when running
DBCC INDEXDEFRAG on them. I'm now considering giving up on INDEXDEFRAG for
my data warehouse DBs...
> Regards,
> Jonathan|||Paul,
"Paul S Randal [MS]" wrote:
> "Jonathan Levine" <myfoo@.nospam.nospam> wrote in message
> news:9E111CD5-2CD0-4071-B3A9-39E3D5CA4978@.microsoft.com...
> What's the reported number of pages moved when running DBCC INDEXDEFRAG? C
an
> you post sample DBCC SHOWCONTIG output please?
Thanks for your reply.
I'm still looking into this myself, and it's a bit more complicated than I o
riginally reported.
I have a data warehouse that's updated early in the morning. The larger tab
les are in the order of 5 to 25 million rows. The updates vary in size from
100,000 to 2 million rows.
Before the update, I run INDEXDEFRAG on fragmented indexes. Then I do the u
pdate, and it's after the update that the index appears to be refragmented.
So, for example, on a 26 million row table:
Before Defrag:
DBCC SHOWCONTIG scanning 'Historical_Rankings' table...
Table: 'Historical_Rankings' (2018106230); index ID: 14, database ID: 8
LEAF level scan performed.
- Pages Scanned........................: 121895
- Extents Scanned.......................: 15316
- Extent Switches.......................: 88061
- Avg. Pages per Extent..................: 8.0
- Scan Density [Best Count:Actual Count]......: 17.30% [15237:88062
]
- Logical Scan Fragmentation ..............: 25.04%
- Extent Scan Fragmentation ...............: 44.72%
- Avg. Bytes Free per Page................: 3012.7
- Avg. Page Density (full)................: 62.78%
INDEXDEFRAG scanned 121891, moved 76889, removed 44996
After defrag:
Table: 'Historical_Rankings' (2018106230); index ID: 14, database ID: 8
LEAF level scan performed.
- Pages Scanned........................: 76899
- Extents Scanned.......................: 9670
- Extent Switches.......................: 37754
- Avg. Pages per Extent..................: 8.0
- Scan Density [Best Count:Actual Count]......: 25.46% [9613:37755]
- Logical Scan Fragmentation ..............: 0.01%
- Extent Scan Fragmentation ...............: 61.40%
- Avg. Bytes Free per Page................: 38.3
- Avg. Page Density (full)................: 99.53%
DBCC execution completed. If DBCC printed error messages, contact your syste
m administrator.
After inserting 270,000 rows:
DBCC SHOWCONTIG scanning 'Historical_Rankings' table...
Table: 'Historical_Rankings' (2018106230); index ID: 14, database ID: 8
LEAF level scan performed.
- Pages Scanned........................: 123077
- Extents Scanned.......................: 15464
- Extent Switches.......................: 88745
- Avg. Pages per Extent..................: 8.0
- Scan Density [Best Count:Actual Count]......: 17.34% [15385:88746
]
- Logical Scan Fragmentation ..............: 26.34%
- Extent Scan Fragmentation ...............: 65.66%
- Avg. Bytes Free per Page................: 3009.3
- Avg. Page Density (full)................: 62.82%
So... it's very strange to me that, after inserting about 1% new rows, and n
ot deleting or updating any rows, the fragmentation could go from .01% to 26
.34%.
I haven't had a chance to fully document this yet, but it seems to me that D
BREINDEX results in a substantially better Scan Density, and that subsequent
inserts do not result in such extreme changes in Logical Scan Fragmentation
. If you think it's helpfu
l, I'll pursue that.
Regards,
Jonathan|||> So... it's very strange to me that, after inserting about 1% new rows, and
not deleting or updating any rows, the fragmentation >could go from .01% to
26.34%.
This isn't strange at all since your pages are basically 100% full before
the inserts. That means that almost any insert will cause a page split and
the results of the showcontig shows that there was a lot of it. If your
going to do a lot of inserts you should consider setting an appropriate fill
factor and possibly Padindex as well. Leaving more free space on the page
will mean more pages in the database but will result in less pagesplits and
lots less work during the inserts.
Andrew J. Kelly SQL MVP
"Jonathan Levine" <myfoo@.nospam.nospam> wrote in message
news:70809313-9024-428C-BD37-3D035CB44453@.microsoft.com...
> Paul,
> "Paul S Randal [MS]" wrote:
Can[vbcol=seagreen]
> Thanks for your reply.
> I'm still looking into this myself, and it's a bit more complicated than I
originally reported.
> I have a data warehouse that's updated early in the morning. The larger
tables are in the order of 5 to 25 million rows. The updates vary in size
from 100,000 to 2 million rows.
> Before the update, I run INDEXDEFRAG on fragmented indexes. Then I do the
update, and it's after the update that the index appears to be refragmented.
So, for example, on a 26 million row table:
> Before Defrag:
> DBCC SHOWCONTIG scanning 'Historical_Rankings' table...
> Table: 'Historical_Rankings' (2018106230); index ID: 14, database ID: 8
> LEAF level scan performed.
> - Pages Scanned........................: 121895
> - Extents Scanned.......................: 15316
> - Extent Switches.......................: 88061
> - Avg. Pages per Extent..................: 8.0
> - Scan Density [Best Count:Actual Count]......: 17.30% [15237:880
62]
> - Logical Scan Fragmentation ..............: 25.04%
> - Extent Scan Fragmentation ...............: 44.72%
> - Avg. Bytes Free per Page................: 3012.7
> - Avg. Page Density (full)................: 62.78%
> INDEXDEFRAG scanned 121891, moved 76889, removed 44996
> After defrag:
> Table: 'Historical_Rankings' (2018106230); index ID: 14, database ID: 8
> LEAF level scan performed.
> - Pages Scanned........................: 76899
> - Extents Scanned.......................: 9670
> - Extent Switches.......................: 37754
> - Avg. Pages per Extent..................: 8.0
> - Scan Density [Best Count:Actual Count]......: 25.46% [9613:3775
5]
> - Logical Scan Fragmentation ..............: 0.01%
> - Extent Scan Fragmentation ...............: 61.40%
> - Avg. Bytes Free per Page................: 38.3
> - Avg. Page Density (full)................: 99.53%
> DBCC execution completed. If DBCC printed error messages, contact your
system administrator.
> After inserting 270,000 rows:
> DBCC SHOWCONTIG scanning 'Historical_Rankings' table...
> Table: 'Historical_Rankings' (2018106230); index ID: 14, database ID: 8
> LEAF level scan performed.
> - Pages Scanned........................: 123077
> - Extents Scanned.......................: 15464
> - Extent Switches.......................: 88745
> - Avg. Pages per Extent..................: 8.0
> - Scan Density [Best Count:Actual Count]......: 17.34% [15385:887
46]
> - Logical Scan Fragmentation ..............: 26.34%
> - Extent Scan Fragmentation ...............: 65.66%
> - Avg. Bytes Free per Page................: 3009.3
> - Avg. Page Density (full)................: 62.82%
> So... it's very strange to me that, after inserting about 1% new rows, and
not deleting or updating any rows, the fragmentation could go from .01% to
26.34%.
> I haven't had a chance to fully document this yet, but it seems to me that
DBREINDEX results in a substantially better Scan Density, and that
subsequent inserts do not result in such extreme changes in Logical Scan
Fragmentation. If you think it's helpful, I'll pursue that.
> Regards,
> Jonathan|||"Andrew J. Kelly" wrote:
> "Jonathan Levine" <myfoo@.nospam.nospam> wrote in message
> news:70809313-9024-428C-BD37-3D035CB44453@.microsoft.com...
> system administrator.
> This isn't strange at all since your pages are basically 100% full before
> the inserts. That means that almost any insert will cause a page split an
d
> the results of the showcontig shows that there was a lot of it. If your
> going to do a lot of inserts you should consider setting an appropriate fi
ll
> factor and possibly Padindex as well. Leaving more free space on the page
> will mean more pages in the database but will result in less pagesplits an
d
> lots less work during the inserts.
Hi Andrew.
I guess that 270K inserts seems like a lot in absolute terms, but relative t
o the size of the table, it's pretty small. So I guess I'm surprised that o
ne in 6 inserts would cause a page split when I'm changing the table by only
1%. I'm also suprised tha
t simply splitting pages would cause the fragmentation to increase so dramat
ically.
I had originally created the index with no FILLFACTOR specified. (I thought
this was supposed to default to a FILLFACTOR of 80). On your suggestion, I
tried rebuilding the index with FILLFACTOR explicitly set to 80. Now DBCC
SHOWCONTIG returns:
DBCC SHOWCONTIG scanning 'Historical_Rankings' table...
Table: 'Historical_Rankings' (2018106230); index ID: 14, database ID: 8
LEAF level scan performed.
- Pages Scanned........................: 96535
- Extents Scanned.......................: 12114
- Extent Switches.......................: 12113
- Avg. Pages per Extent..................: 8.0
- Scan Density [Best Count:Actual Count]......: 99.61% [12067:12114
]
- Logical Scan Fragmentation ..............: 0.02%
- Extent Scan Fragmentation ...............: 49.70%
- Avg. Bytes Free per Page................: 1610.7
- Avg. Page Density (full)................: 80.10%
DBCC execution completed. If DBCC printed error messages, contact your syste
m administrator.
Note the great improvement in the index's Scan Density. And I also see this
kind of improvement in Scan Density when I run DBCC DBREINDEX.
So... I'll keep an eye on it for the next few days. I'll also check into wh
ether my other big indexes have similar FILLFACTOR issues. In the meantime,
is Scan Density something that I should be worrying about?|||> Hi Andrew.
> I guess that 270K inserts seems like a lot in absolute terms, but relative
to the size of the table, it's pretty small. So I guess I'm >surprised that
one in 6 inserts would cause a page split when I'm changing the table by
only 1%. I'm also surprised that simply
> splitting pages would cause the fragmentation to increase so dramatically.
>
When a page is that full (or usually even close) just one new row will cause
a page split since there simply isn't enough room for the new data. A
non-clustered index is essentially a table with a clustered index on the
index expression. That means the data at the leaf level have to basically
be in physical order. So when the data coming in is random (meaning the
value of the column(s) the index is based on) it usually gets placed in
between existing rows. If there is not enough room on the page it splits it
and moves half to another page. When you add new rows in index expression
order (such as a datetime that is in chronological order) they can simply be
appended to the end of the current page. When that page is full it creates
a new page and continues appending from there. This is not a page split and
essentially does not change the logical fragmentation since all the new
pages come directly after the previous ones. This KB is great for this type
stuff:
http://www.microsoft.com/technet/pr...n/ss2kidbp.mspx

> I had originally created the index with no FILLFACTOR specified. (I
thought this was supposed to default to a FILLFACTOR of 80). On your
suggestion, I tried rebuilding the index with FILLFACTOR explicitly set to
80. Now DBCC SHOWCONTIG returns:
> DBCC SHOWCONTIG scanning 'Historical_Rankings' table...
> Table: 'Historical_Rankings' (2018106230); index ID: 14, database ID: 8
> LEAF level scan performed.
> - Pages Scanned........................: 96535
> - Extents Scanned.......................: 12114
> - Extent Switches.......................: 12113
> - Avg. Pages per Extent..................: 8.0
> - Scan Density [Best Count:Actual Count]......: 99.61% [12067:121
14]
> - Logical Scan Fragmentation ..............: 0.02%
> - Extent Scan Fragmentation ...............: 49.70%
> - Avg. Bytes Free per Page................: 1610.7
> - Avg. Page Density (full)................: 80.10%
> DBCC execution completed. If DBCC printed error messages, contact your
system administrator.
> Note the great improvement in the index's Scan Density. And I also see
this kind of improvement in Scan Density when I run DBCC DBREINDEX.
> So... I'll keep an eye on it for the next few days. I'll also check into
whether my other big indexes have similar FILLFACTOR issues. In the
meantime, is Scan Density something that I should be worrying about?
Logical fragmentation is the most important factor that you should be
worried about. Especially if you have more than 1 file in the filegroup. In
that case Logical is the only one that you can really trust anyway. See the
KB I mentioned.
Andrew J. Kelly SQL MVP|||Andrew's explanations are spot on.
Why do you consider Scan Density so important? What's the access pattern
over this index?
Do you notice query performance dropping after the insert activity? It's
entirely possible that everything you're doing is a waste of time - akin to
continually shrinking a database after it grows every day. Please read the
whitepaper below which explains a bunch about when to take action on
fragmentation.
http://www.microsoft.com/technet/pr...n/ss2kidbp.mspx
Thanks and regards.
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Jonathan Levine" <myfoo@.nospam.nospam> wrote in message
news:05DA17D7-942D-452A-8973-ACCB2DB7A98C@.microsoft.com...
> "Andrew J. Kelly" wrote:
8[vbcol=seagreen]
8[vbcol=seagreen]
and[vbcol=seagreen]
.01% to[vbcol=seagreen]
before[vbcol=seagreen]
and[vbcol=seagreen]
fill[vbcol=seagreen]
page[vbcol=seagreen]
and[vbcol=seagreen]
> Hi Andrew.
> I guess that 270K inserts seems like a lot in absolute terms, but relative
to the size of the table, it's pretty small. So I guess I'm surprised that
one in 6 inserts would cause a page split when I'm changing the table by
only 1%. I'm also suprised that simply splitting pages would cause the
fragmentation to increase so dramatically.
> I had originally created the index with no FILLFACTOR specified. (I
thought this was supposed to default to a FILLFACTOR of 80). On your
suggestion, I tried rebuilding the index with FILLFACTOR explicitly set to
80. Now DBCC SHOWCONTIG returns:
> DBCC SHOWCONTIG scanning 'Historical_Rankings' table...
> Table: 'Historical_Rankings' (2018106230); index ID: 14, database ID: 8
> LEAF level scan performed.
> - Pages Scanned........................: 96535
> - Extents Scanned.......................: 12114
> - Extent Switches.......................: 12113
> - Avg. Pages per Extent..................: 8.0
> - Scan Density [Best Count:Actual Count]......: 99.61% [12067:121
14]
> - Logical Scan Fragmentation ..............: 0.02%
> - Extent Scan Fragmentation ...............: 49.70%
> - Avg. Bytes Free per Page................: 1610.7
> - Avg. Page Density (full)................: 80.10%
> DBCC execution completed. If DBCC printed error messages, contact your
system administrator.
> Note the great improvement in the index's Scan Density. And I also see
this kind of improvement in Scan Density when I run DBCC DBREINDEX.
> So... I'll keep an eye on it for the next few days. I'll also check into
whether my other big indexes have similar FILLFACTOR issues. In the
meantime, is Scan Density something that I should be worrying about?
>
>|||Thanks, Andrew. Very helpful stuff.
-- J