DBfile usage: client

Showing posts with label client. Show all posts

Sunday, March 11, 2012

DBCC INDEXDEFRAG.. no impact?

I see an impact.
Using Profiler..Duration, I can see client query duration times go from 16ms
to 9000ms and higher immediately after executing
INDEXDEFRAG(db,tlb,index)..and back down to 16ms after the dbcc is stopped.
BOL says that there will be no impact on SELECTS.
Anyone know what could be going on? There was no activity going on other
than the SELECTs during this test.
Thx,
Don
SQL2000Did you look to see what botlenecks there were during this? How many CPU's
do you have? Is your log file on a separate drive array than the data?
What is your cache hit ratio? And by the way running profiler on a busy
system will slow things down dramatically as well, use trace instead. But
my guess is your hardware configuration is not setup to handle much in the
way of CPU or I/O.
Andrew J. Kelly SQL MVP
"donsql22222" <donsql22222@.discussions.microsoft.com> wrote in message
news:B5BBE9FB-21B1-4870-85A8-EA9B11E3B90A@.microsoft.com...
>I see an impact.
> Using Profiler..Duration, I can see client query duration times go from
> 16ms
> to 9000ms and higher immediately after executing
> INDEXDEFRAG(db,tlb,index)..and back down to 16ms after the dbcc is
> stopped.
> BOL says that there will be no impact on SELECTS.
> Anyone know what could be going on? There was no activity going on other
> than the SELECTs during this test.
> Thx,
> Don
> SQL2000
>|||Unlike dbreindex, indexdefrag allows your queries to continue while it's
defragging. But there will be cost for shuffing index pages so it's *normal*
to see degration.
A slower query is still better than a completely blocked query. ;-)
-oj
"donsql22222" <donsql22222@.discussions.microsoft.com> wrote in message
news:B5BBE9FB-21B1-4870-85A8-EA9B11E3B90A@.microsoft.com...
>I see an impact.
> Using Profiler..Duration, I can see client query duration times go from
> 16ms
> to 9000ms and higher immediately after executing
> INDEXDEFRAG(db,tlb,index)..and back down to 16ms after the dbcc is
> stopped.
> BOL says that there will be no impact on SELECTS.
> Anyone know what could be going on? There was no activity going on other
> than the SELECTs during this test.
> Thx,
> Don
> SQL2000
>|||Also, check out:
http://support.microsoft.com/?kbid=907250&SD=tech
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:OrUaLeITGHA.4300@.TK2MSFTNGP14.phx.gbl...
Did you look to see what botlenecks there were during this? How many CPU's
do you have? Is your log file on a separate drive array than the data?
What is your cache hit ratio? And by the way running profiler on a busy
system will slow things down dramatically as well, use trace instead. But
my guess is your hardware configuration is not setup to handle much in the
way of CPU or I/O.
Andrew J. Kelly SQL MVP
"donsql22222" <donsql22222@.discussions.microsoft.com> wrote in message
news:B5BBE9FB-21B1-4870-85A8-EA9B11E3B90A@.microsoft.com...
>I see an impact.
> Using Profiler..Duration, I can see client query duration times go from
> 16ms
> to 9000ms and higher immediately after executing
> INDEXDEFRAG(db,tlb,index)..and back down to 16ms after the dbcc is
> stopped.
> BOL says that there will be no impact on SELECTS.
> Anyone know what could be going on? There was no activity going on other
> than the SELECTs during this test.
> Thx,
> Don
> SQL2000
>

DBCC INDEXDEFRAG.. no impact?

I see an impact.
Using Profiler..Duration, I can see client query duration times go from 16ms
to 9000ms and higher immediately after executing
INDEXDEFRAG(db,tlb,index)..and back down to 16ms after the dbcc is stopped.
BOL says that there will be no impact on SELECTS.
Anyone know what could be going on? There was no activity going on other
than the SELECTs during this test.
Thx,
Don
SQL2000Did you look to see what botlenecks there were during this? How many CPU's
do you have? Is your log file on a separate drive array than the data?
What is your cache hit ratio? And by the way running profiler on a busy
system will slow things down dramatically as well, use trace instead. But
my guess is your hardware configuration is not setup to handle much in the
way of CPU or I/O.
--
Andrew J. Kelly SQL MVP
"donsql22222" <donsql22222@.discussions.microsoft.com> wrote in message
news:B5BBE9FB-21B1-4870-85A8-EA9B11E3B90A@.microsoft.com...
>I see an impact.
> Using Profiler..Duration, I can see client query duration times go from
> 16ms
> to 9000ms and higher immediately after executing
> INDEXDEFRAG(db,tlb,index)..and back down to 16ms after the dbcc is
> stopped.
> BOL says that there will be no impact on SELECTS.
> Anyone know what could be going on? There was no activity going on other
> than the SELECTs during this test.
> Thx,
> Don
> SQL2000
>|||Unlike dbreindex, indexdefrag allows your queries to continue while it's
defragging. But there will be cost for shuffing index pages so it's *normal*
to see degration.
A slower query is still better than a completely blocked query. ;-)
--
-oj
"donsql22222" <donsql22222@.discussions.microsoft.com> wrote in message
news:B5BBE9FB-21B1-4870-85A8-EA9B11E3B90A@.microsoft.com...
>I see an impact.
> Using Profiler..Duration, I can see client query duration times go from
> 16ms
> to 9000ms and higher immediately after executing
> INDEXDEFRAG(db,tlb,index)..and back down to 16ms after the dbcc is
> stopped.
> BOL says that there will be no impact on SELECTS.
> Anyone know what could be going on? There was no activity going on other
> than the SELECTs during this test.
> Thx,
> Don
> SQL2000
>|||Also, check out:
http://support.microsoft.com/?kbid=907250&SD=tech
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:OrUaLeITGHA.4300@.TK2MSFTNGP14.phx.gbl...
Did you look to see what botlenecks there were during this? How many CPU's
do you have? Is your log file on a separate drive array than the data?
What is your cache hit ratio? And by the way running profiler on a busy
system will slow things down dramatically as well, use trace instead. But
my guess is your hardware configuration is not setup to handle much in the
way of CPU or I/O.
--
Andrew J. Kelly SQL MVP
"donsql22222" <donsql22222@.discussions.microsoft.com> wrote in message
news:B5BBE9FB-21B1-4870-85A8-EA9B11E3B90A@.microsoft.com...
>I see an impact.
> Using Profiler..Duration, I can see client query duration times go from
> 16ms
> to 9000ms and higher immediately after executing
> INDEXDEFRAG(db,tlb,index)..and back down to 16ms after the dbcc is
> stopped.
> BOL says that there will be no impact on SELECTS.
> Anyone know what could be going on? There was no activity going on other
> than the SELECTs during this test.
> Thx,
> Don
> SQL2000
>

DBCC INDEXDEFRAG.. no impact?

I see an impact.
Using Profiler..Duration, I can see client query duration times go from 16ms
to 9000ms and higher immediately after executing
INDEXDEFRAG(db,tlb,index)..and back down to 16ms after the dbcc is stopped.
BOL says that there will be no impact on SELECTS.
Anyone know what could be going on? There was no activity going on other
than the SELECTs during this test.
Thx,
Don
SQL2000
Did you look to see what botlenecks there were during this? How many CPU's
do you have? Is your log file on a separate drive array than the data?
What is your cache hit ratio? And by the way running profiler on a busy
system will slow things down dramatically as well, use trace instead. But
my guess is your hardware configuration is not setup to handle much in the
way of CPU or I/O.
Andrew J. Kelly SQL MVP
"donsql22222" <donsql22222@.discussions.microsoft.com> wrote in message
news:B5BBE9FB-21B1-4870-85A8-EA9B11E3B90A@.microsoft.com...
>I see an impact.
> Using Profiler..Duration, I can see client query duration times go from
> 16ms
> to 9000ms and higher immediately after executing
> INDEXDEFRAG(db,tlb,index)..and back down to 16ms after the dbcc is
> stopped.
> BOL says that there will be no impact on SELECTS.
> Anyone know what could be going on? There was no activity going on other
> than the SELECTs during this test.
> Thx,
> Don
> SQL2000
>
|||Unlike dbreindex, indexdefrag allows your queries to continue while it's
defragging. But there will be cost for shuffing index pages so it's *normal*
to see degration.
A slower query is still better than a completely blocked query. ;-)
-oj
"donsql22222" <donsql22222@.discussions.microsoft.com> wrote in message
news:B5BBE9FB-21B1-4870-85A8-EA9B11E3B90A@.microsoft.com...
>I see an impact.
> Using Profiler..Duration, I can see client query duration times go from
> 16ms
> to 9000ms and higher immediately after executing
> INDEXDEFRAG(db,tlb,index)..and back down to 16ms after the dbcc is
> stopped.
> BOL says that there will be no impact on SELECTS.
> Anyone know what could be going on? There was no activity going on other
> than the SELECTs during this test.
> Thx,
> Don
> SQL2000
>
|||Also, check out:
http://support.microsoft.com/?kbid=907250&SD=tech
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
..
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:OrUaLeITGHA.4300@.TK2MSFTNGP14.phx.gbl...
Did you look to see what botlenecks there were during this? How many CPU's
do you have? Is your log file on a separate drive array than the data?
What is your cache hit ratio? And by the way running profiler on a busy
system will slow things down dramatically as well, use trace instead. But
my guess is your hardware configuration is not setup to handle much in the
way of CPU or I/O.
Andrew J. Kelly SQL MVP
"donsql22222" <donsql22222@.discussions.microsoft.com> wrote in message
news:B5BBE9FB-21B1-4870-85A8-EA9B11E3B90A@.microsoft.com...
>I see an impact.
> Using Profiler..Duration, I can see client query duration times go from
> 16ms
> to 9000ms and higher immediately after executing
> INDEXDEFRAG(db,tlb,index)..and back down to 16ms after the dbcc is
> stopped.
> BOL says that there will be no impact on SELECTS.
> Anyone know what could be going on? There was no activity going on other
> than the SELECTs during this test.
> Thx,
> Don
> SQL2000
>

Wednesday, March 7, 2012

DBCC DBREINDEX or DBCC INDEXDEFRAG

We have a client who switched from using DBCC DBREINDEX to DBCC INDEXDEFRAG
because DBREINDEX caused some blocking issues. We suggested that DBCC
DBREINDEX does a more thorough job of handling fragmentation and that they
should really try to figure out what blocking is being caused instead of
switching to INDEXDEFRAG.
Can anyone clarify whether we were wrong in suggesting DBREINDEX vs
INDEXDEFRAG?All documentation I have seen suggests that DBREINDEX does a more
thorough job than INDEXDEFRAG.
Thanks in advance.Hi
read the following page
http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ss2kidbp.mspx
regards
VT
Knowledge is power, share it...
http://oneplace4sql.blogspot.com/
"Frank1213" <Frank1213@.discussions.microsoft.com> wrote in message
news:629A02AB-2F02-4210-9B57-463D111C47A8@.microsoft.com...
> We have a client who switched from using DBCC DBREINDEX to DBCC
> INDEXDEFRAG
> because DBREINDEX caused some blocking issues. We suggested that DBCC
> DBREINDEX does a more thorough job of handling fragmentation and that they
> should really try to figure out what blocking is being caused instead of
> switching to INDEXDEFRAG.
> Can anyone clarify whether we were wrong in suggesting DBREINDEX vs
> INDEXDEFRAG?All documentation I have seen suggests that DBREINDEX does a
> more
> thorough job than INDEXDEFRAG.
> Thanks in advance.|||Well, you and your client are both right.
Yes, DBREINDEX does a better job. For example, it will restore the
original (or specified) fill factor, even if that means adding pages.
And it will process the entire table (and not skip pages).
But during the entire DBREINDEX operation, the table cannot be updated,
and will cause blocking for such activity. INDEXDEFRAG does not have
this problem. Any blocking will be very short.
So there is a valid tradeoff. How much better does DBREINDEX do its job,
and do you need that extra bit? And is this little bit of extra worth
the blocking, or are long running blocks (which might trigger timeouts)
simply unacceptable?
Personally, I would not worry very much about the switch. Especially if
INDEXDEFRAG is run on a regular basis, and the (on average) the table
doesn't grow or doesn't grow much. If the table does grow continuously,
then the number of page splits will increase. If that is not acceptable,
then your client should revert to DBREINDEX (at least once in a while).
HTH,
Gert-Jan
Frank1213 wrote:
> We have a client who switched from using DBCC DBREINDEX to DBCC INDEXDEFRAG
> because DBREINDEX caused some blocking issues. We suggested that DBCC
> DBREINDEX does a more thorough job of handling fragmentation and that they
> should really try to figure out what blocking is being caused instead of
> switching to INDEXDEFRAG.
> Can anyone clarify whether we were wrong in suggesting DBREINDEX vs
> INDEXDEFRAG?All documentation I have seen suggests that DBREINDEX does a more
> thorough job than INDEXDEFRAG.
> Thanks in advance.|||Thanks to both of you for the replies.
"Gert-Jan Strik" wrote:
> Well, you and your client are both right.
> Yes, DBREINDEX does a better job. For example, it will restore the
> original (or specified) fill factor, even if that means adding pages.
> And it will process the entire table (and not skip pages).
> But during the entire DBREINDEX operation, the table cannot be updated,
> and will cause blocking for such activity. INDEXDEFRAG does not have
> this problem. Any blocking will be very short.
> So there is a valid tradeoff. How much better does DBREINDEX do its job,
> and do you need that extra bit? And is this little bit of extra worth
> the blocking, or are long running blocks (which might trigger timeouts)
> simply unacceptable?
> Personally, I would not worry very much about the switch. Especially if
> INDEXDEFRAG is run on a regular basis, and the (on average) the table
> doesn't grow or doesn't grow much. If the table does grow continuously,
> then the number of page splits will increase. If that is not acceptable,
> then your client should revert to DBREINDEX (at least once in a while).
> HTH,
> Gert-Jan
>
> Frank1213 wrote:
> >
> > We have a client who switched from using DBCC DBREINDEX to DBCC INDEXDEFRAG
> > because DBREINDEX caused some blocking issues. We suggested that DBCC
> > DBREINDEX does a more thorough job of handling fragmentation and that they
> > should really try to figure out what blocking is being caused instead of
> > switching to INDEXDEFRAG.
> > Can anyone clarify whether we were wrong in suggesting DBREINDEX vs
> > INDEXDEFRAG?All documentation I have seen suggests that DBREINDEX does a more
> > thorough job than INDEXDEFRAG.
> > Thanks in advance.
>

DBCC DBREINDEX or DBCC INDEXDEFRAG

We have a client who switched from using DBCC DBREINDEX to DBCC INDEXDEFRAG
because DBREINDEX caused some blocking issues. We suggested that DBCC
DBREINDEX does a more thorough job of handling fragmentation and that they
should really try to figure out what blocking is being caused instead of
switching to INDEXDEFRAG.
Can anyone clarify whether we were wrong in suggesting DBREINDEX vs
INDEXDEFRAG?All documentation I have seen suggests that DBREINDEX does a mor
e
thorough job than INDEXDEFRAG.
Thanks in advance.Hi
read the following page
http://www.microsoft.com/technet/pr...n/ss2kidbp.mspx
regards
VT
Knowledge is power, share it...
http://oneplace4sql.blogspot.com/
"Frank1213" <Frank1213@.discussions.microsoft.com> wrote in message
news:629A02AB-2F02-4210-9B57-463D111C47A8@.microsoft.com...
> We have a client who switched from using DBCC DBREINDEX to DBCC
> INDEXDEFRAG
> because DBREINDEX caused some blocking issues. We suggested that DBCC
> DBREINDEX does a more thorough job of handling fragmentation and that they
> should really try to figure out what blocking is being caused instead of
> switching to INDEXDEFRAG.
> Can anyone clarify whether we were wrong in suggesting DBREINDEX vs
> INDEXDEFRAG?All documentation I have seen suggests that DBREINDEX does a
> more
> thorough job than INDEXDEFRAG.
> Thanks in advance.|||Well, you and your client are both right.
Yes, DBREINDEX does a better job. For example, it will restore the
original (or specified) fill factor, even if that means adding pages.
And it will process the entire table (and not skip pages).
But during the entire DBREINDEX operation, the table cannot be updated,
and will cause blocking for such activity. INDEXDEFRAG does not have
this problem. Any blocking will be very short.
So there is a valid tradeoff. How much better does DBREINDEX do its job,
and do you need that extra bit? And is this little bit of extra worth
the blocking, or are long running blocks (which might trigger timeouts)
simply unacceptable?
Personally, I would not worry very much about the switch. Especially if
INDEXDEFRAG is run on a regular basis, and the (on average) the table
doesn't grow or doesn't grow much. If the table does grow continuously,
then the number of page splits will increase. If that is not acceptable,
then your client should revert to DBREINDEX (at least once in a while).
HTH,
Gert-Jan
Frank1213 wrote:
> We have a client who switched from using DBCC DBREINDEX to DBCC INDEXDEFRA
G
> because DBREINDEX caused some blocking issues. We suggested that DBCC
> DBREINDEX does a more thorough job of handling fragmentation and that they
> should really try to figure out what blocking is being caused instead of
> switching to INDEXDEFRAG.
> Can anyone clarify whether we were wrong in suggesting DBREINDEX vs
> INDEXDEFRAG?All documentation I have seen suggests that DBREINDEX does a m
ore
> thorough job than INDEXDEFRAG.
> Thanks in advance.|||Thanks to both of you for the replies.
"Gert-Jan Strik" wrote:

> Well, you and your client are both right.
> Yes, DBREINDEX does a better job. For example, it will restore the
> original (or specified) fill factor, even if that means adding pages.
> And it will process the entire table (and not skip pages).
> But during the entire DBREINDEX operation, the table cannot be updated,
> and will cause blocking for such activity. INDEXDEFRAG does not have
> this problem. Any blocking will be very short.
> So there is a valid tradeoff. How much better does DBREINDEX do its job,
> and do you need that extra bit? And is this little bit of extra worth
> the blocking, or are long running blocks (which might trigger timeouts)
> simply unacceptable?
> Personally, I would not worry very much about the switch. Especially if
> INDEXDEFRAG is run on a regular basis, and the (on average) the table
> doesn't grow or doesn't grow much. If the table does grow continuously,
> then the number of page splits will increase. If that is not acceptable,
> then your client should revert to DBREINDEX (at least once in a while).
> HTH,
> Gert-Jan
>
> Frank1213 wrote:
>

DBCC DBReindex follwed by DBCC ShrinkFile

We have a client who is running DBCC DBReindex (all tables) followed by DBCC
ShrinkFile on the log and data files with AutoShrink on their database
turned on.
We have already advised them to turn autoshrink off.
In our tests running a show contig after each operation the index
optimization is erased by running the ShrinkFile operation with many indexes
showing 100% fragmentation.
Does anyone know of documentation which reflects this circumstance?I haven't seen any documentation per se, saying that shrink causes fragmentation. But if you read about what
shrink does and how it work, it is clear that you get fragmented data after the shrink. I recommend below two
articles:
http://www.karaszi.com/SQLServer/info_dont_shrink.asp
http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ss2kidbp.mspx
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Gene Black" <geblack@.cox.net> wrote in message news:%23LU8bhigEHA.1644@.tk2msftngp13.phx.gbl...
> We have a client who is running DBCC DBReindex (all tables) followed by DBCC
> ShrinkFile on the log and data files with AutoShrink on their database
> turned on.
> We have already advised them to turn autoshrink off.
> In our tests running a show contig after each operation the index
> optimization is erased by running the ShrinkFile operation with many indexes
> showing 100% fragmentation.
> Does anyone know of documentation which reflects this circumstance?
>
>|||It looks like shrinkdatabase does not have the same implications (this is
what the built in maintenance plan uses as viewed through profiler)
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:e3YE8oigEHA.2916@.TK2MSFTNGP12.phx.gbl...
> I haven't seen any documentation per se, saying that shrink causes
fragmentation. But if you read about what
> shrink does and how it work, it is clear that you get fragmented data
after the shrink. I recommend below two
> articles:
> http://www.karaszi.com/SQLServer/info_dont_shrink.asp
>
http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ss2kidbp.mspx
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Gene Black" <geblack@.cox.net> wrote in message
news:%23LU8bhigEHA.1644@.tk2msftngp13.phx.gbl...
> > We have a client who is running DBCC DBReindex (all tables) followed by
DBCC
> > ShrinkFile on the log and data files with AutoShrink on their database
> > turned on.
> >
> > We have already advised them to turn autoshrink off.
> >
> > In our tests running a show contig after each operation the index
> > optimization is erased by running the ShrinkFile operation with many
indexes
> > showing 100% fragmentation.
> >
> > Does anyone know of documentation which reflects this circumstance?
> >
> >
> >
>|||Shrinkdatabase does the same thing as shrinkfile, but it targets all the files for the database.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Gene Black" <geblack@.hotmail.com> wrote in message news:%23o4Z58igEHA.596@.TK2MSFTNGP11.phx.gbl...
> It looks like shrinkdatabase does not have the same implications (this is
> what the built in maintenance plan uses as viewed through profiler)
>
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
> message news:e3YE8oigEHA.2916@.TK2MSFTNGP12.phx.gbl...
> > I haven't seen any documentation per se, saying that shrink causes
> fragmentation. But if you read about what
> > shrink does and how it work, it is clear that you get fragmented data
> after the shrink. I recommend below two
> > articles:
> > http://www.karaszi.com/SQLServer/info_dont_shrink.asp
> >
> http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ss2kidbp.mspx
> >
> > --
> > Tibor Karaszi, SQL Server MVP
> > http://www.karaszi.com/sqlserver/default.asp
> > http://www.solidqualitylearning.com/
> >
> >
> > "Gene Black" <geblack@.cox.net> wrote in message
> news:%23LU8bhigEHA.1644@.tk2msftngp13.phx.gbl...
> > > We have a client who is running DBCC DBReindex (all tables) followed by
> DBCC
> > > ShrinkFile on the log and data files with AutoShrink on their database
> > > turned on.
> > >
> > > We have already advised them to turn autoshrink off.
> > >
> > > In our tests running a show contig after each operation the index
> > > optimization is erased by running the ShrinkFile operation with many
> indexes
> > > showing 100% fragmentation.
> > >
> > > Does anyone know of documentation which reflects this circumstance?
> > >
> > >
> > >
> >
> >
>|||Evidently it doesn't since the results of showcontig don't reflect
fragmentation after the dbcc shrinkdatabase
I did
1. sp_msforeachtable 'dbcc dbreindex(''?'')'
dbcc showcontig for all tables
ADBForecasts-2 0%
ADBProjects-1 0%
ADBProjects-3 0%
ADBReference-2 0%
ADBTasks-1 0%
ADBTasks-3 14%
ADBTasks-4 0%
ADBTasks-5 0%
ADBUsers-2 0%
ADBUsers-3 0%
ADBUsers-4 0%
ADBViews-2 0%
ADBWorkSheets-2 0%
ADBWorkSheetTasks-2 0%
ADBWorkSheetTasks-3 0%
ADBWorkSheetTasks-4 0%
ADBWorkSheetTasks-5 0%
ADBWorkSheetTasks-19 0%
2. DBCC shrinkfile (2,5) then showcontig
results:
ADBForecasts-2 98%
ADBProjects-1 0%
ADBProjects-3 0%
ADBReference-2 0%
ADBTasks-1 98%
ADBTasks-3 0%
ADBTasks-4 0%
ADBTasks-5 0%
ADBUsers-2 0%
ADBUsers-3 0%
ADBUsers-4 0%
ADBViews-2 0%
ADBWorkSheets-2 0%
ADBWorkSheetTasks-2 98%
ADBWorkSheetTasks-3 92%
ADBWorkSheetTasks-4 96%
ADBWorkSheetTasks-5 96%
ADBWorkSheetTasks-19 98%
3. sp_msforeachtable 'dbcc dbreindex(''?'')' then showcontig
ADBForecasts-2 0%
ADBProjects-1 0%
ADBProjects-3 0%
ADBReference-2 0%
ADBTasks-1 0%
ADBTasks-3 14%
ADBTasks-4 0%
ADBTasks-5 0%
ADBUsers-2 0%
ADBUsers-3 0%
ADBUsers-4 0%
ADBViews-2 0%
ADBWorkSheets-2 0%
ADBWorkSheetTasks-2 0%
ADBWorkSheetTasks-3 0%
ADBWorkSheetTasks-4 0%
ADBWorkSheetTasks-5 0%
ADBWorkSheetTasks-19 0%
4. dbcc shrinkdatabase then showcontig
ADBForecasts-2 0%
ADBProjects-1 0%
ADBProjects-3 0%
ADBReference-2 0%
ADBTasks-1 0%
ADBTasks-3 14%
ADBTasks-4 0%
ADBTasks-5 0%
ADBUsers-2 0%
ADBUsers-3 0%
ADBUsers-4 0%
ADBViews-2 0%
ADBWorkSheets-2 0%
ADBWorkSheetTasks-2 0%
ADBWorkSheetTasks-3 0%
ADBWorkSheetTasks-4 0%
ADBWorkSheetTasks-5 0%
ADBWorkSheetTasks-19 0%
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:OQR6P%23igEHA.4092@.TK2MSFTNGP10.phx.gbl...
> Shrinkdatabase does the same thing as shrinkfile, but it targets all the
files for the database.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Gene Black" <geblack@.hotmail.com> wrote in message
news:%23o4Z58igEHA.596@.TK2MSFTNGP11.phx.gbl...
> > It looks like shrinkdatabase does not have the same implications (this
is
> > what the built in maintenance plan uses as viewed through profiler)
> >
> >
> > "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote
in
> > message news:e3YE8oigEHA.2916@.TK2MSFTNGP12.phx.gbl...
> > > I haven't seen any documentation per se, saying that shrink causes
> > fragmentation. But if you read about what
> > > shrink does and how it work, it is clear that you get fragmented data
> > after the shrink. I recommend below two
> > > articles:
> > > http://www.karaszi.com/SQLServer/info_dont_shrink.asp
> > >
> >
http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ss2kidbp.mspx
> > >
> > > --
> > > Tibor Karaszi, SQL Server MVP
> > > http://www.karaszi.com/sqlserver/default.asp
> > > http://www.solidqualitylearning.com/
> > >
> > >
> > > "Gene Black" <geblack@.cox.net> wrote in message
> > news:%23LU8bhigEHA.1644@.tk2msftngp13.phx.gbl...
> > > > We have a client who is running DBCC DBReindex (all tables) followed
by
> > DBCC
> > > > ShrinkFile on the log and data files with AutoShrink on their
database
> > > > turned on.
> > > >
> > > > We have already advised them to turn autoshrink off.
> > > >
> > > > In our tests running a show contig after each operation the index
> > > > optimization is erased by running the ShrinkFile operation with many
> > indexes
> > > > showing 100% fragmentation.
> > > >
> > > > Does anyone know of documentation which reflects this circumstance?
> > > >
> > > >
> > > >
> > >
> > >
> >
> >
>|||Note:
this database had autoshrink off
the shinkdatbase command was
DBCC shrinkdatabase(N'AutoADB', 10, TRUNCATEONLY )
could it be the truncateonly option which is causing the difference?
"Gene Black" <geblack@.hotmail.com> wrote in message
news:OnwTlHjgEHA.3944@.tk2msftngp13.phx.gbl...
> Evidently it doesn't since the results of showcontig don't reflect
> fragmentation after the dbcc shrinkdatabase
> I did
> 1. sp_msforeachtable 'dbcc dbreindex(''?'')'
> dbcc showcontig for all tables
> ADBForecasts-2 0%
> ADBProjects-1 0%
> ADBProjects-3 0%
> ADBReference-2 0%
> ADBTasks-1 0%
> ADBTasks-3 14%
> ADBTasks-4 0%
> ADBTasks-5 0%
> ADBUsers-2 0%
> ADBUsers-3 0%
> ADBUsers-4 0%
> ADBViews-2 0%
> ADBWorkSheets-2 0%
> ADBWorkSheetTasks-2 0%
> ADBWorkSheetTasks-3 0%
> ADBWorkSheetTasks-4 0%
> ADBWorkSheetTasks-5 0%
> ADBWorkSheetTasks-19 0%
> 2. DBCC shrinkfile (2,5) then showcontig
> results:
> ADBForecasts-2 98%
> ADBProjects-1 0%
> ADBProjects-3 0%
> ADBReference-2 0%
> ADBTasks-1 98%
> ADBTasks-3 0%
> ADBTasks-4 0%
> ADBTasks-5 0%
> ADBUsers-2 0%
> ADBUsers-3 0%
> ADBUsers-4 0%
> ADBViews-2 0%
> ADBWorkSheets-2 0%
> ADBWorkSheetTasks-2 98%
> ADBWorkSheetTasks-3 92%
> ADBWorkSheetTasks-4 96%
> ADBWorkSheetTasks-5 96%
> ADBWorkSheetTasks-19 98%
> 3. sp_msforeachtable 'dbcc dbreindex(''?'')' then showcontig
> ADBForecasts-2 0%
> ADBProjects-1 0%
> ADBProjects-3 0%
> ADBReference-2 0%
> ADBTasks-1 0%
> ADBTasks-3 14%
> ADBTasks-4 0%
> ADBTasks-5 0%
> ADBUsers-2 0%
> ADBUsers-3 0%
> ADBUsers-4 0%
> ADBViews-2 0%
> ADBWorkSheets-2 0%
> ADBWorkSheetTasks-2 0%
> ADBWorkSheetTasks-3 0%
> ADBWorkSheetTasks-4 0%
> ADBWorkSheetTasks-5 0%
> ADBWorkSheetTasks-19 0%
>
> 4. dbcc shrinkdatabase then showcontig
> ADBForecasts-2 0%
> ADBProjects-1 0%
> ADBProjects-3 0%
> ADBReference-2 0%
> ADBTasks-1 0%
> ADBTasks-3 14%
> ADBTasks-4 0%
> ADBTasks-5 0%
> ADBUsers-2 0%
> ADBUsers-3 0%
> ADBUsers-4 0%
> ADBViews-2 0%
> ADBWorkSheets-2 0%
> ADBWorkSheetTasks-2 0%
> ADBWorkSheetTasks-3 0%
> ADBWorkSheetTasks-4 0%
> ADBWorkSheetTasks-5 0%
> ADBWorkSheetTasks-19 0%
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote
in
> message news:OQR6P%23igEHA.4092@.TK2MSFTNGP10.phx.gbl...
> > Shrinkdatabase does the same thing as shrinkfile, but it targets all the
> files for the database.
> >
> > --
> > Tibor Karaszi, SQL Server MVP
> > http://www.karaszi.com/sqlserver/default.asp
> > http://www.solidqualitylearning.com/
> >
> >
> > "Gene Black" <geblack@.hotmail.com> wrote in message
> news:%23o4Z58igEHA.596@.TK2MSFTNGP11.phx.gbl...
> > > It looks like shrinkdatabase does not have the same implications (this
> is
> > > what the built in maintenance plan uses as viewed through profiler)
> > >
> > >
> > > "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com>
wrote
> in
> > > message news:e3YE8oigEHA.2916@.TK2MSFTNGP12.phx.gbl...
> > > > I haven't seen any documentation per se, saying that shrink causes
> > > fragmentation. But if you read about what
> > > > shrink does and how it work, it is clear that you get fragmented
data
> > > after the shrink. I recommend below two
> > > > articles:
> > > > http://www.karaszi.com/SQLServer/info_dont_shrink.asp
> > > >
> > >
>
http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ss2kidbp.mspx
> > > >
> > > > --
> > > > Tibor Karaszi, SQL Server MVP
> > > > http://www.karaszi.com/sqlserver/default.asp
> > > > http://www.solidqualitylearning.com/
> > > >
> > > >
> > > > "Gene Black" <geblack@.cox.net> wrote in message
> > > news:%23LU8bhigEHA.1644@.tk2msftngp13.phx.gbl...
> > > > > We have a client who is running DBCC DBReindex (all tables)
followed
> by
> > > DBCC
> > > > > ShrinkFile on the log and data files with AutoShrink on their
> database
> > > > > turned on.
> > > > >
> > > > > We have already advised them to turn autoshrink off.
> > > > >
> > > > > In our tests running a show contig after each operation the index
> > > > > optimization is erased by running the ShrinkFile operation with
many
> > > indexes
> > > > > showing 100% fragmentation.
> > > > >
> > > > > Does anyone know of documentation which reflects this
circumstance?
> > > > >
> > > > >
> > > > >
> > > >
> > > >
> > >
> > >
> >
> >
>|||It looks like the difference is the TruncateOnly option
TRUNCATEONLY
Causes any unused space in the files to be released to the operating system
and shrinks the file to the last allocated extent, reducing the file size
without moving any data. No attempt is made to relocate rows to unallocated
pages. target_size is ignored when TRUNCATEONLY is used.
"Gene Black" <geblack@.hotmail.com> wrote in message
news:OnwTlHjgEHA.3944@.tk2msftngp13.phx.gbl...
> Evidently it doesn't since the results of showcontig don't reflect
> fragmentation after the dbcc shrinkdatabase
> I did
> 1. sp_msforeachtable 'dbcc dbreindex(''?'')'
> dbcc showcontig for all tables
> ADBForecasts-2 0%
> ADBProjects-1 0%
> ADBProjects-3 0%
> ADBReference-2 0%
> ADBTasks-1 0%
> ADBTasks-3 14%
> ADBTasks-4 0%
> ADBTasks-5 0%
> ADBUsers-2 0%
> ADBUsers-3 0%
> ADBUsers-4 0%
> ADBViews-2 0%
> ADBWorkSheets-2 0%
> ADBWorkSheetTasks-2 0%
> ADBWorkSheetTasks-3 0%
> ADBWorkSheetTasks-4 0%
> ADBWorkSheetTasks-5 0%
> ADBWorkSheetTasks-19 0%
> 2. DBCC shrinkfile (2,5) then showcontig
> results:
> ADBForecasts-2 98%
> ADBProjects-1 0%
> ADBProjects-3 0%
> ADBReference-2 0%
> ADBTasks-1 98%
> ADBTasks-3 0%
> ADBTasks-4 0%
> ADBTasks-5 0%
> ADBUsers-2 0%
> ADBUsers-3 0%
> ADBUsers-4 0%
> ADBViews-2 0%
> ADBWorkSheets-2 0%
> ADBWorkSheetTasks-2 98%
> ADBWorkSheetTasks-3 92%
> ADBWorkSheetTasks-4 96%
> ADBWorkSheetTasks-5 96%
> ADBWorkSheetTasks-19 98%
> 3. sp_msforeachtable 'dbcc dbreindex(''?'')' then showcontig
> ADBForecasts-2 0%
> ADBProjects-1 0%
> ADBProjects-3 0%
> ADBReference-2 0%
> ADBTasks-1 0%
> ADBTasks-3 14%
> ADBTasks-4 0%
> ADBTasks-5 0%
> ADBUsers-2 0%
> ADBUsers-3 0%
> ADBUsers-4 0%
> ADBViews-2 0%
> ADBWorkSheets-2 0%
> ADBWorkSheetTasks-2 0%
> ADBWorkSheetTasks-3 0%
> ADBWorkSheetTasks-4 0%
> ADBWorkSheetTasks-5 0%
> ADBWorkSheetTasks-19 0%
>
> 4. dbcc shrinkdatabase then showcontig
> ADBForecasts-2 0%
> ADBProjects-1 0%
> ADBProjects-3 0%
> ADBReference-2 0%
> ADBTasks-1 0%
> ADBTasks-3 14%
> ADBTasks-4 0%
> ADBTasks-5 0%
> ADBUsers-2 0%
> ADBUsers-3 0%
> ADBUsers-4 0%
> ADBViews-2 0%
> ADBWorkSheets-2 0%
> ADBWorkSheetTasks-2 0%
> ADBWorkSheetTasks-3 0%
> ADBWorkSheetTasks-4 0%
> ADBWorkSheetTasks-5 0%
> ADBWorkSheetTasks-19 0%
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote
in
> message news:OQR6P%23igEHA.4092@.TK2MSFTNGP10.phx.gbl...
> > Shrinkdatabase does the same thing as shrinkfile, but it targets all the
> files for the database.
> >
> > --
> > Tibor Karaszi, SQL Server MVP
> > http://www.karaszi.com/sqlserver/default.asp
> > http://www.solidqualitylearning.com/
> >
> >
> > "Gene Black" <geblack@.hotmail.com> wrote in message
> news:%23o4Z58igEHA.596@.TK2MSFTNGP11.phx.gbl...
> > > It looks like shrinkdatabase does not have the same implications (this
> is
> > > what the built in maintenance plan uses as viewed through profiler)
> > >
> > >
> > > "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com>
wrote
> in
> > > message news:e3YE8oigEHA.2916@.TK2MSFTNGP12.phx.gbl...
> > > > I haven't seen any documentation per se, saying that shrink causes
> > > fragmentation. But if you read about what
> > > > shrink does and how it work, it is clear that you get fragmented
data
> > > after the shrink. I recommend below two
> > > > articles:
> > > > http://www.karaszi.com/SQLServer/info_dont_shrink.asp
> > > >
> > >
>
http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ss2kidbp.mspx
> > > >
> > > > --
> > > > Tibor Karaszi, SQL Server MVP
> > > > http://www.karaszi.com/sqlserver/default.asp
> > > > http://www.solidqualitylearning.com/
> > > >
> > > >
> > > > "Gene Black" <geblack@.cox.net> wrote in message
> > > news:%23LU8bhigEHA.1644@.tk2msftngp13.phx.gbl...
> > > > > We have a client who is running DBCC DBReindex (all tables)
followed
> by
> > > DBCC
> > > > > ShrinkFile on the log and data files with AutoShrink on their
> database
> > > > > turned on.
> > > > >
> > > > > We have already advised them to turn autoshrink off.
> > > > >
> > > > > In our tests running a show contig after each operation the index
> > > > > optimization is erased by running the ShrinkFile operation with
many
> > > indexes
> > > > > showing 100% fragmentation.
> > > > >
> > > > > Does anyone know of documentation which reflects this
circumstance?
> > > > >
> > > > >
> > > > >
> > > >
> > > >
> > >
> > >
> >
> >
>|||> It looks like the difference is the Truncate option
Yep, as it only cuts of the files at the end, it doesn't move any data... :-)
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Gene Black" <geblack@.hotmail.com> wrote in message news:u3I6ELjgEHA.3916@.TK2MSFTNGP11.phx.gbl...
> It looks like the difference is the TruncateOnly option
> TRUNCATEONLY
> Causes any unused space in the files to be released to the operating system
> and shrinks the file to the last allocated extent, reducing the file size
> without moving any data. No attempt is made to relocate rows to unallocated
> pages. target_size is ignored when TRUNCATEONLY is used.
> "Gene Black" <geblack@.hotmail.com> wrote in message
> news:OnwTlHjgEHA.3944@.tk2msftngp13.phx.gbl...
> > Evidently it doesn't since the results of showcontig don't reflect
> > fragmentation after the dbcc shrinkdatabase
> >
> > I did
> >
> > 1. sp_msforeachtable 'dbcc dbreindex(''?'')'
> > dbcc showcontig for all tables
> > ADBForecasts-2 0%
> > ADBProjects-1 0%
> > ADBProjects-3 0%
> > ADBReference-2 0%
> > ADBTasks-1 0%
> > ADBTasks-3 14%
> > ADBTasks-4 0%
> > ADBTasks-5 0%
> > ADBUsers-2 0%
> > ADBUsers-3 0%
> > ADBUsers-4 0%
> > ADBViews-2 0%
> > ADBWorkSheets-2 0%
> > ADBWorkSheetTasks-2 0%
> > ADBWorkSheetTasks-3 0%
> > ADBWorkSheetTasks-4 0%
> > ADBWorkSheetTasks-5 0%
> > ADBWorkSheetTasks-19 0%
> >
> > 2. DBCC shrinkfile (2,5) then showcontig
> > results:
> > ADBForecasts-2 98%
> > ADBProjects-1 0%
> > ADBProjects-3 0%
> > ADBReference-2 0%
> > ADBTasks-1 98%
> > ADBTasks-3 0%
> > ADBTasks-4 0%
> > ADBTasks-5 0%
> > ADBUsers-2 0%
> > ADBUsers-3 0%
> > ADBUsers-4 0%
> > ADBViews-2 0%
> > ADBWorkSheets-2 0%
> > ADBWorkSheetTasks-2 98%
> > ADBWorkSheetTasks-3 92%
> > ADBWorkSheetTasks-4 96%
> > ADBWorkSheetTasks-5 96%
> > ADBWorkSheetTasks-19 98%
> >
> > 3. sp_msforeachtable 'dbcc dbreindex(''?'')' then showcontig
> > ADBForecasts-2 0%
> > ADBProjects-1 0%
> > ADBProjects-3 0%
> > ADBReference-2 0%
> > ADBTasks-1 0%
> > ADBTasks-3 14%
> > ADBTasks-4 0%
> > ADBTasks-5 0%
> > ADBUsers-2 0%
> > ADBUsers-3 0%
> > ADBUsers-4 0%
> > ADBViews-2 0%
> > ADBWorkSheets-2 0%
> > ADBWorkSheetTasks-2 0%
> > ADBWorkSheetTasks-3 0%
> > ADBWorkSheetTasks-4 0%
> > ADBWorkSheetTasks-5 0%
> > ADBWorkSheetTasks-19 0%
> >
> >
> > 4. dbcc shrinkdatabase then showcontig
> > ADBForecasts-2 0%
> > ADBProjects-1 0%
> > ADBProjects-3 0%
> > ADBReference-2 0%
> > ADBTasks-1 0%
> > ADBTasks-3 14%
> > ADBTasks-4 0%
> > ADBTasks-5 0%
> > ADBUsers-2 0%
> > ADBUsers-3 0%
> > ADBUsers-4 0%
> > ADBViews-2 0%
> > ADBWorkSheets-2 0%
> > ADBWorkSheetTasks-2 0%
> > ADBWorkSheetTasks-3 0%
> > ADBWorkSheetTasks-4 0%
> > ADBWorkSheetTasks-5 0%
> > ADBWorkSheetTasks-19 0%
> >
> > "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote
> in
> > message news:OQR6P%23igEHA.4092@.TK2MSFTNGP10.phx.gbl...
> > > Shrinkdatabase does the same thing as shrinkfile, but it targets all the
> > files for the database.
> > >
> > > --
> > > Tibor Karaszi, SQL Server MVP
> > > http://www.karaszi.com/sqlserver/default.asp
> > > http://www.solidqualitylearning.com/
> > >
> > >
> > > "Gene Black" <geblack@.hotmail.com> wrote in message
> > news:%23o4Z58igEHA.596@.TK2MSFTNGP11.phx.gbl...
> > > > It looks like shrinkdatabase does not have the same implications (this
> > is
> > > > what the built in maintenance plan uses as viewed through profiler)
> > > >
> > > >
> > > > "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com>
> wrote
> > in
> > > > message news:e3YE8oigEHA.2916@.TK2MSFTNGP12.phx.gbl...
> > > > > I haven't seen any documentation per se, saying that shrink causes
> > > > fragmentation. But if you read about what
> > > > > shrink does and how it work, it is clear that you get fragmented
> data
> > > > after the shrink. I recommend below two
> > > > > articles:
> > > > > http://www.karaszi.com/SQLServer/info_dont_shrink.asp
> > > > >
> > > >
> >
> http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ss2kidbp.mspx
> > > > >
> > > > > --
> > > > > Tibor Karaszi, SQL Server MVP
> > > > > http://www.karaszi.com/sqlserver/default.asp
> > > > > http://www.solidqualitylearning.com/
> > > > >
> > > > >
> > > > > "Gene Black" <geblack@.cox.net> wrote in message
> > > > news:%23LU8bhigEHA.1644@.tk2msftngp13.phx.gbl...
> > > > > > We have a client who is running DBCC DBReindex (all tables)
> followed
> > by
> > > > DBCC
> > > > > > ShrinkFile on the log and data files with AutoShrink on their
> > database
> > > > > > turned on.
> > > > > >
> > > > > > We have already advised them to turn autoshrink off.
> > > > > >
> > > > > > In our tests running a show contig after each operation the index
> > > > > > optimization is erased by running the ShrinkFile operation with
> many
> > > > indexes
> > > > > > showing 100% fragmentation.
> > > > > >
> > > > > > Does anyone know of documentation which reflects this
> circumstance?
> > > > > >
> > > > > >
> > > > > >
> > > > >
> > > > >
> > > >
> > > >
> > >
> > >
> >
> >
>|||Yes, truncate only does not move data. As Tibor pointed out Shrink Database
does exactly the same work as ShrinkFile with the exception that
ShrinkDatabase combines both the data and log file(s). And it does not give
you any control what so ever as to how much it shrinks one or the other.
That is why I always recommend using ShrinkFile. Srinking a file that was
just defragged will always refragment it to some degree. In most case a
great deal. DBA's who shrink their data or log files each night, especially
after a reindex should just be shot<g>.
--
Andrew J. Kelly SQL MVP
"Gene Black" <geblack@.hotmail.com> wrote in message
news:u3I6ELjgEHA.3916@.TK2MSFTNGP11.phx.gbl...
> It looks like the difference is the TruncateOnly option
> TRUNCATEONLY
> Causes any unused space in the files to be released to the operating
system
> and shrinks the file to the last allocated extent, reducing the file size
> without moving any data. No attempt is made to relocate rows to
unallocated
> pages. target_size is ignored when TRUNCATEONLY is used.
> "Gene Black" <geblack@.hotmail.com> wrote in message
> news:OnwTlHjgEHA.3944@.tk2msftngp13.phx.gbl...
> > Evidently it doesn't since the results of showcontig don't reflect
> > fragmentation after the dbcc shrinkdatabase
> >
> > I did
> >
> > 1. sp_msforeachtable 'dbcc dbreindex(''?'')'
> > dbcc showcontig for all tables
> > ADBForecasts-2 0%
> > ADBProjects-1 0%
> > ADBProjects-3 0%
> > ADBReference-2 0%
> > ADBTasks-1 0%
> > ADBTasks-3 14%
> > ADBTasks-4 0%
> > ADBTasks-5 0%
> > ADBUsers-2 0%
> > ADBUsers-3 0%
> > ADBUsers-4 0%
> > ADBViews-2 0%
> > ADBWorkSheets-2 0%
> > ADBWorkSheetTasks-2 0%
> > ADBWorkSheetTasks-3 0%
> > ADBWorkSheetTasks-4 0%
> > ADBWorkSheetTasks-5 0%
> > ADBWorkSheetTasks-19 0%
> >
> > 2. DBCC shrinkfile (2,5) then showcontig
> > results:
> > ADBForecasts-2 98%
> > ADBProjects-1 0%
> > ADBProjects-3 0%
> > ADBReference-2 0%
> > ADBTasks-1 98%
> > ADBTasks-3 0%
> > ADBTasks-4 0%
> > ADBTasks-5 0%
> > ADBUsers-2 0%
> > ADBUsers-3 0%
> > ADBUsers-4 0%
> > ADBViews-2 0%
> > ADBWorkSheets-2 0%
> > ADBWorkSheetTasks-2 98%
> > ADBWorkSheetTasks-3 92%
> > ADBWorkSheetTasks-4 96%
> > ADBWorkSheetTasks-5 96%
> > ADBWorkSheetTasks-19 98%
> >
> > 3. sp_msforeachtable 'dbcc dbreindex(''?'')' then showcontig
> > ADBForecasts-2 0%
> > ADBProjects-1 0%
> > ADBProjects-3 0%
> > ADBReference-2 0%
> > ADBTasks-1 0%
> > ADBTasks-3 14%
> > ADBTasks-4 0%
> > ADBTasks-5 0%
> > ADBUsers-2 0%
> > ADBUsers-3 0%
> > ADBUsers-4 0%
> > ADBViews-2 0%
> > ADBWorkSheets-2 0%
> > ADBWorkSheetTasks-2 0%
> > ADBWorkSheetTasks-3 0%
> > ADBWorkSheetTasks-4 0%
> > ADBWorkSheetTasks-5 0%
> > ADBWorkSheetTasks-19 0%
> >
> >
> > 4. dbcc shrinkdatabase then showcontig
> > ADBForecasts-2 0%
> > ADBProjects-1 0%
> > ADBProjects-3 0%
> > ADBReference-2 0%
> > ADBTasks-1 0%
> > ADBTasks-3 14%
> > ADBTasks-4 0%
> > ADBTasks-5 0%
> > ADBUsers-2 0%
> > ADBUsers-3 0%
> > ADBUsers-4 0%
> > ADBViews-2 0%
> > ADBWorkSheets-2 0%
> > ADBWorkSheetTasks-2 0%
> > ADBWorkSheetTasks-3 0%
> > ADBWorkSheetTasks-4 0%
> > ADBWorkSheetTasks-5 0%
> > ADBWorkSheetTasks-19 0%
> >
> > "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote
> in
> > message news:OQR6P%23igEHA.4092@.TK2MSFTNGP10.phx.gbl...
> > > Shrinkdatabase does the same thing as shrinkfile, but it targets all
the
> > files for the database.
> > >
> > > --
> > > Tibor Karaszi, SQL Server MVP
> > > http://www.karaszi.com/sqlserver/default.asp
> > > http://www.solidqualitylearning.com/
> > >
> > >
> > > "Gene Black" <geblack@.hotmail.com> wrote in message
> > news:%23o4Z58igEHA.596@.TK2MSFTNGP11.phx.gbl...
> > > > It looks like shrinkdatabase does not have the same implications
(this
> > is
> > > > what the built in maintenance plan uses as viewed through profiler)
> > > >
> > > >
> > > > "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com>
> wrote
> > in
> > > > message news:e3YE8oigEHA.2916@.TK2MSFTNGP12.phx.gbl...
> > > > > I haven't seen any documentation per se, saying that shrink causes
> > > > fragmentation. But if you read about what
> > > > > shrink does and how it work, it is clear that you get fragmented
> data
> > > > after the shrink. I recommend below two
> > > > > articles:
> > > > > http://www.karaszi.com/SQLServer/info_dont_shrink.asp
> > > > >
> > > >
> >
>
http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ss2kidbp.mspx
> > > > >
> > > > > --
> > > > > Tibor Karaszi, SQL Server MVP
> > > > > http://www.karaszi.com/sqlserver/default.asp
> > > > > http://www.solidqualitylearning.com/
> > > > >
> > > > >
> > > > > "Gene Black" <geblack@.cox.net> wrote in message
> > > > news:%23LU8bhigEHA.1644@.tk2msftngp13.phx.gbl...
> > > > > > We have a client who is running DBCC DBReindex (all tables)
> followed
> > by
> > > > DBCC
> > > > > > ShrinkFile on the log and data files with AutoShrink on their
> > database
> > > > > > turned on.
> > > > > >
> > > > > > We have already advised them to turn autoshrink off.
> > > > > >
> > > > > > In our tests running a show contig after each operation the
index
> > > > > > optimization is erased by running the ShrinkFile operation with
> many
> > > > indexes
> > > > > > showing 100% fragmentation.
> > > > > >
> > > > > > Does anyone know of documentation which reflects this
> circumstance?
> > > > > >
> > > > > >
> > > > > >
> > > > >
> > > > >
> > > >
> > > >
> > >
> > >
> >
> >
>|||Thanks for the help guys, even though it took a little time for me to 'see
the light'
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:OGyaU7kgEHA.3676@.TK2MSFTNGP12.phx.gbl...
> Yes, truncate only does not move data. As Tibor pointed out Shrink
> Database
> does exactly the same work as ShrinkFile with the exception that
> ShrinkDatabase combines both the data and log file(s). And it does not
> give
> you any control what so ever as to how much it shrinks one or the other.
> That is why I always recommend using ShrinkFile. Srinking a file that was
> just defragged will always refragment it to some degree. In most case a
> great deal. DBA's who shrink their data or log files each night,
> especially
> after a reindex should just be shot<g>.
> --
> Andrew J. Kelly SQL MVP
>
> "Gene Black" <geblack@.hotmail.com> wrote in message
> news:u3I6ELjgEHA.3916@.TK2MSFTNGP11.phx.gbl...
>> It looks like the difference is the TruncateOnly option
>> TRUNCATEONLY
>> Causes any unused space in the files to be released to the operating
> system
>> and shrinks the file to the last allocated extent, reducing the file size
>> without moving any data. No attempt is made to relocate rows to
> unallocated
>> pages. target_size is ignored when TRUNCATEONLY is used.
>> "Gene Black" <geblack@.hotmail.com> wrote in message
>> news:OnwTlHjgEHA.3944@.tk2msftngp13.phx.gbl...
>> > Evidently it doesn't since the results of showcontig don't reflect
>> > fragmentation after the dbcc shrinkdatabase
>> >
>> > I did
>> >
>> > 1. sp_msforeachtable 'dbcc dbreindex(''?'')'
>> > dbcc showcontig for all tables
>> > ADBForecasts-2 0%
>> > ADBProjects-1 0%
>> > ADBProjects-3 0%
>> > ADBReference-2 0%
>> > ADBTasks-1 0%
>> > ADBTasks-3 14%
>> > ADBTasks-4 0%
>> > ADBTasks-5 0%
>> > ADBUsers-2 0%
>> > ADBUsers-3 0%
>> > ADBUsers-4 0%
>> > ADBViews-2 0%
>> > ADBWorkSheets-2 0%
>> > ADBWorkSheetTasks-2 0%
>> > ADBWorkSheetTasks-3 0%
>> > ADBWorkSheetTasks-4 0%
>> > ADBWorkSheetTasks-5 0%
>> > ADBWorkSheetTasks-19 0%
>> >
>> > 2. DBCC shrinkfile (2,5) then showcontig
>> > results:
>> > ADBForecasts-2 98%
>> > ADBProjects-1 0%
>> > ADBProjects-3 0%
>> > ADBReference-2 0%
>> > ADBTasks-1 98%
>> > ADBTasks-3 0%
>> > ADBTasks-4 0%
>> > ADBTasks-5 0%
>> > ADBUsers-2 0%
>> > ADBUsers-3 0%
>> > ADBUsers-4 0%
>> > ADBViews-2 0%
>> > ADBWorkSheets-2 0%
>> > ADBWorkSheetTasks-2 98%
>> > ADBWorkSheetTasks-3 92%
>> > ADBWorkSheetTasks-4 96%
>> > ADBWorkSheetTasks-5 96%
>> > ADBWorkSheetTasks-19 98%
>> >
>> > 3. sp_msforeachtable 'dbcc dbreindex(''?'')' then showcontig
>> > ADBForecasts-2 0%
>> > ADBProjects-1 0%
>> > ADBProjects-3 0%
>> > ADBReference-2 0%
>> > ADBTasks-1 0%
>> > ADBTasks-3 14%
>> > ADBTasks-4 0%
>> > ADBTasks-5 0%
>> > ADBUsers-2 0%
>> > ADBUsers-3 0%
>> > ADBUsers-4 0%
>> > ADBViews-2 0%
>> > ADBWorkSheets-2 0%
>> > ADBWorkSheetTasks-2 0%
>> > ADBWorkSheetTasks-3 0%
>> > ADBWorkSheetTasks-4 0%
>> > ADBWorkSheetTasks-5 0%
>> > ADBWorkSheetTasks-19 0%
>> >
>> >
>> > 4. dbcc shrinkdatabase then showcontig
>> > ADBForecasts-2 0%
>> > ADBProjects-1 0%
>> > ADBProjects-3 0%
>> > ADBReference-2 0%
>> > ADBTasks-1 0%
>> > ADBTasks-3 14%
>> > ADBTasks-4 0%
>> > ADBTasks-5 0%
>> > ADBUsers-2 0%
>> > ADBUsers-3 0%
>> > ADBUsers-4 0%
>> > ADBViews-2 0%
>> > ADBWorkSheets-2 0%
>> > ADBWorkSheetTasks-2 0%
>> > ADBWorkSheetTasks-3 0%
>> > ADBWorkSheetTasks-4 0%
>> > ADBWorkSheetTasks-5 0%
>> > ADBWorkSheetTasks-19 0%
>> >
>> > "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com>
>> > wrote
>> in
>> > message news:OQR6P%23igEHA.4092@.TK2MSFTNGP10.phx.gbl...
>> > > Shrinkdatabase does the same thing as shrinkfile, but it targets all
> the
>> > files for the database.
>> > >
>> > > --
>> > > Tibor Karaszi, SQL Server MVP
>> > > http://www.karaszi.com/sqlserver/default.asp
>> > > http://www.solidqualitylearning.com/
>> > >
>> > >
>> > > "Gene Black" <geblack@.hotmail.com> wrote in message
>> > news:%23o4Z58igEHA.596@.TK2MSFTNGP11.phx.gbl...
>> > > > It looks like shrinkdatabase does not have the same implications
> (this
>> > is
>> > > > what the built in maintenance plan uses as viewed through profiler)
>> > > >
>> > > >
>> > > > "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com>
>> wrote
>> > in
>> > > > message news:e3YE8oigEHA.2916@.TK2MSFTNGP12.phx.gbl...
>> > > > > I haven't seen any documentation per se, saying that shrink
>> > > > > causes
>> > > > fragmentation. But if you read about what
>> > > > > shrink does and how it work, it is clear that you get fragmented
>> data
>> > > > after the shrink. I recommend below two
>> > > > > articles:
>> > > > > http://www.karaszi.com/SQLServer/info_dont_shrink.asp
>> > > > >
>> > > >
>> >
> http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ss2kidbp.mspx
>> > > > >
>> > > > > --
>> > > > > Tibor Karaszi, SQL Server MVP
>> > > > > http://www.karaszi.com/sqlserver/default.asp
>> > > > > http://www.solidqualitylearning.com/
>> > > > >
>> > > > >
>> > > > > "Gene Black" <geblack@.cox.net> wrote in message
>> > > > news:%23LU8bhigEHA.1644@.tk2msftngp13.phx.gbl...
>> > > > > > We have a client who is running DBCC DBReindex (all tables)
>> followed
>> > by
>> > > > DBCC
>> > > > > > ShrinkFile on the log and data files with AutoShrink on their
>> > database
>> > > > > > turned on.
>> > > > > >
>> > > > > > We have already advised them to turn autoshrink off.
>> > > > > >
>> > > > > > In our tests running a show contig after each operation the
> index
>> > > > > > optimization is erased by running the ShrinkFile operation with
>> many
>> > > > indexes
>> > > > > > showing 100% fragmentation.
>> > > > > >
>> > > > > > Does anyone know of documentation which reflects this
>> circumstance?
>> > > > > >
>> > > > > >
>> > > > > >
>> > > > >
>> > > > >
>> > > >
>> > > >
>> > >
>> > >
>> >
>> >
>>
>|||Hi Gene,
As Andrew and Tibor have explained, you're seeing expected behavior - just
not documented explicitly. I'll ensure that the behavior is explicitly
documented in SQL Server 2005 to avoid confusion.
Regards.
--
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Gene Black" <geblack@.hotmail.com> wrote in message
news:OJhEN9mgEHA.3964@.TK2MSFTNGP12.phx.gbl...
> Thanks for the help guys, even though it took a little time for me to 'see
> the light'
>
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:OGyaU7kgEHA.3676@.TK2MSFTNGP12.phx.gbl...
> > Yes, truncate only does not move data. As Tibor pointed out Shrink
> > Database
> > does exactly the same work as ShrinkFile with the exception that
> > ShrinkDatabase combines both the data and log file(s). And it does not
> > give
> > you any control what so ever as to how much it shrinks one or the other.
> > That is why I always recommend using ShrinkFile. Srinking a file that
was
> > just defragged will always refragment it to some degree. In most case a
> > great deal. DBA's who shrink their data or log files each night,
> > especially
> > after a reindex should just be shot<g>.
> >
> > --
> > Andrew J. Kelly SQL MVP
> >
> >
> > "Gene Black" <geblack@.hotmail.com> wrote in message
> > news:u3I6ELjgEHA.3916@.TK2MSFTNGP11.phx.gbl...
> >> It looks like the difference is the TruncateOnly option
> >>
> >> TRUNCATEONLY
> >>
> >> Causes any unused space in the files to be released to the operating
> > system
> >> and shrinks the file to the last allocated extent, reducing the file
size
> >> without moving any data. No attempt is made to relocate rows to
> > unallocated
> >> pages. target_size is ignored when TRUNCATEONLY is used.
> >>
> >> "Gene Black" <geblack@.hotmail.com> wrote in message
> >> news:OnwTlHjgEHA.3944@.tk2msftngp13.phx.gbl...
> >> > Evidently it doesn't since the results of showcontig don't reflect
> >> > fragmentation after the dbcc shrinkdatabase
> >> >
> >> > I did
> >> >
> >> > 1. sp_msforeachtable 'dbcc dbreindex(''?'')'
> >> > dbcc showcontig for all tables
> >> > ADBForecasts-2 0%
> >> > ADBProjects-1 0%
> >> > ADBProjects-3 0%
> >> > ADBReference-2 0%
> >> > ADBTasks-1 0%
> >> > ADBTasks-3 14%
> >> > ADBTasks-4 0%
> >> > ADBTasks-5 0%
> >> > ADBUsers-2 0%
> >> > ADBUsers-3 0%
> >> > ADBUsers-4 0%
> >> > ADBViews-2 0%
> >> > ADBWorkSheets-2 0%
> >> > ADBWorkSheetTasks-2 0%
> >> > ADBWorkSheetTasks-3 0%
> >> > ADBWorkSheetTasks-4 0%
> >> > ADBWorkSheetTasks-5 0%
> >> > ADBWorkSheetTasks-19 0%
> >> >
> >> > 2. DBCC shrinkfile (2,5) then showcontig
> >> > results:
> >> > ADBForecasts-2 98%
> >> > ADBProjects-1 0%
> >> > ADBProjects-3 0%
> >> > ADBReference-2 0%
> >> > ADBTasks-1 98%
> >> > ADBTasks-3 0%
> >> > ADBTasks-4 0%
> >> > ADBTasks-5 0%
> >> > ADBUsers-2 0%
> >> > ADBUsers-3 0%
> >> > ADBUsers-4 0%
> >> > ADBViews-2 0%
> >> > ADBWorkSheets-2 0%
> >> > ADBWorkSheetTasks-2 98%
> >> > ADBWorkSheetTasks-3 92%
> >> > ADBWorkSheetTasks-4 96%
> >> > ADBWorkSheetTasks-5 96%
> >> > ADBWorkSheetTasks-19 98%
> >> >
> >> > 3. sp_msforeachtable 'dbcc dbreindex(''?'')' then showcontig
> >> > ADBForecasts-2 0%
> >> > ADBProjects-1 0%
> >> > ADBProjects-3 0%
> >> > ADBReference-2 0%
> >> > ADBTasks-1 0%
> >> > ADBTasks-3 14%
> >> > ADBTasks-4 0%
> >> > ADBTasks-5 0%
> >> > ADBUsers-2 0%
> >> > ADBUsers-3 0%
> >> > ADBUsers-4 0%
> >> > ADBViews-2 0%
> >> > ADBWorkSheets-2 0%
> >> > ADBWorkSheetTasks-2 0%
> >> > ADBWorkSheetTasks-3 0%
> >> > ADBWorkSheetTasks-4 0%
> >> > ADBWorkSheetTasks-5 0%
> >> > ADBWorkSheetTasks-19 0%
> >> >
> >> >
> >> > 4. dbcc shrinkdatabase then showcontig
> >> > ADBForecasts-2 0%
> >> > ADBProjects-1 0%
> >> > ADBProjects-3 0%
> >> > ADBReference-2 0%
> >> > ADBTasks-1 0%
> >> > ADBTasks-3 14%
> >> > ADBTasks-4 0%
> >> > ADBTasks-5 0%
> >> > ADBUsers-2 0%
> >> > ADBUsers-3 0%
> >> > ADBUsers-4 0%
> >> > ADBViews-2 0%
> >> > ADBWorkSheets-2 0%
> >> > ADBWorkSheetTasks-2 0%
> >> > ADBWorkSheetTasks-3 0%
> >> > ADBWorkSheetTasks-4 0%
> >> > ADBWorkSheetTasks-5 0%
> >> > ADBWorkSheetTasks-19 0%
> >> >
> >> > "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com>
> >> > wrote
> >> in
> >> > message news:OQR6P%23igEHA.4092@.TK2MSFTNGP10.phx.gbl...
> >> > > Shrinkdatabase does the same thing as shrinkfile, but it targets
all
> > the
> >> > files for the database.
> >> > >
> >> > > --
> >> > > Tibor Karaszi, SQL Server MVP
> >> > > http://www.karaszi.com/sqlserver/default.asp
> >> > > http://www.solidqualitylearning.com/
> >> > >
> >> > >
> >> > > "Gene Black" <geblack@.hotmail.com> wrote in message
> >> > news:%23o4Z58igEHA.596@.TK2MSFTNGP11.phx.gbl...
> >> > > > It looks like shrinkdatabase does not have the same implications
> > (this
> >> > is
> >> > > > what the built in maintenance plan uses as viewed through
profiler)
> >> > > >
> >> > > >
> >> > > > "Tibor Karaszi"
<tibor_please.no.email_karaszi@.hotmail.nomail.com>
> >> wrote
> >> > in
> >> > > > message news:e3YE8oigEHA.2916@.TK2MSFTNGP12.phx.gbl...
> >> > > > > I haven't seen any documentation per se, saying that shrink
> >> > > > > causes
> >> > > > fragmentation. But if you read about what
> >> > > > > shrink does and how it work, it is clear that you get
fragmented
> >> data
> >> > > > after the shrink. I recommend below two
> >> > > > > articles:
> >> > > > > http://www.karaszi.com/SQLServer/info_dont_shrink.asp
> >> > > > >
> >> > > >
> >> >
> >>
> >
http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ss2kidbp.mspx
> >> > > > >
> >> > > > > --
> >> > > > > Tibor Karaszi, SQL Server MVP
> >> > > > > http://www.karaszi.com/sqlserver/default.asp
> >> > > > > http://www.solidqualitylearning.com/
> >> > > > >
> >> > > > >
> >> > > > > "Gene Black" <geblack@.cox.net> wrote in message
> >> > > > news:%23LU8bhigEHA.1644@.tk2msftngp13.phx.gbl...
> >> > > > > > We have a client who is running DBCC DBReindex (all tables)
> >> followed
> >> > by
> >> > > > DBCC
> >> > > > > > ShrinkFile on the log and data files with AutoShrink on their
> >> > database
> >> > > > > > turned on.
> >> > > > > >
> >> > > > > > We have already advised them to turn autoshrink off.
> >> > > > > >
> >> > > > > > In our tests running a show contig after each operation the
> > index
> >> > > > > > optimization is erased by running the ShrinkFile operation
with
> >> many
> >> > > > indexes
> >> > > > > > showing 100% fragmentation.
> >> > > > > >
> >> > > > > > Does anyone know of documentation which reflects this
> >> circumstance?
> >> > > > > >
> >> > > > > >
> >> > > > > >
> >> > > > >
> >> > > > >
> >> > > >
> >> > > >
> >> > >
> >> > >
> >> >
> >> >
> >>
> >>
> >
> >
>

Sunday, March 11, 2012

DBCC INDEXDEFRAG.. no impact?

DBCC INDEXDEFRAG.. no impact?

DBCC INDEXDEFRAG.. no impact?

Wednesday, March 7, 2012

DBCC DBREINDEX or DBCC INDEXDEFRAG

DBCC DBREINDEX or DBCC INDEXDEFRAG

DBCC DBREINDEX or DBCC INDEXDEFRAG

DBCC DBReindex follwed by DBCC ShrinkFile

DBCC DBReindex follwed by DBCC ShrinkFile

DBCC DBReindex follwed by DBCC ShrinkFile

DBfile usage

Blog Archive

About Me