Hello!
I have noticed that when executing DBCC INDEXDEFRAG on big table (SQL
Server 2000 SP4), transaction log starts growing significantly. I was hoping
that INDEXDEFRAG is implemented as a serious of short transactions and
shouldn't cause significant transaction log growth. Our database is in
SIMPLE recovery mode. We have the same database/defragmentation process
running on SQL Server 2005 and never experienced this problem.
Any thoughts on this matter are greatly appreciated.
Thanks,
IgorMake sure you didn't wrap it in a transaction or have another long running
transaction open.
--
Andrew J. Kelly SQL MVP
"imarchenko" <igormarchenko@.hotmail.com> wrote in message
news:%233eJTLz5GHA.3952@.TK2MSFTNGP04.phx.gbl...
> Hello!
> I have noticed that when executing DBCC INDEXDEFRAG on big table (SQL
> Server 2000 SP4), transaction log starts growing significantly. I was
> hoping that INDEXDEFRAG is implemented as a serious of short transactions
> and shouldn't cause significant transaction log growth. Our database is in
> SIMPLE recovery mode. We have the same database/defragmentation process
> running on SQL Server 2005 and never experienced this problem.
> Any thoughts on this matter are greatly appreciated.
>
> Thanks,
> Igor
>
Showing posts with label indexdefrag. Show all posts
Showing posts with label indexdefrag. 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
>
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
>
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
>
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
>
DBCC INDEXDEFRAG shows more fragmentation but better performance - Why?
am a bit confused about something. I am trying to solve a problem. My
developers wrote a piece of code that inserted 2million rows of data and
then updated the data . .this obviously resulted in significant
fragmentation . . . . scan density reported was quite low . . resulting in
pagiolatch_sh waittypes(this slowed down performance) during reporting
processes (this is partly also to do with the fact that the data and log
files reside on a mirrored array) - pls don't ask, this is a system I have
inherited and as a result I have to live with it for now , I have to work
around these constraints taking into consideration that these export
processes have always completed in less that 5minutes in the past.. .
..obviously something has changed . .I have advised the developers about
re-writing the query so that the inserts are just inserts and not 1)inserts
then 2)updates. . . what I am trying to find an explanation for is
1) I advised for the code to be re-written to limit fragmentation - and it
did 98% scan density
2) logical frag and extent frag was low as well
3) developer runs query and query takes forever!!
a) I advise to run indexdefrag on clustered index
b) extent frag and logical frag still low
c) scan density is now worse
d) BUT query is excuted in 5mins
PS. I am not 'hands-on' just advising so I am waiting for exec plan for the
two scenarios.
I know that the exec plan would be similar as stats has not changed post
index defrag.
any ideas why indxdefrag will report worse scan density?
Olu Adedeji
Do you have multiple files in the filegroup by any chance? If so the only
figure to trust is the Logical fragmentation. Have a look here for more
details:
http://www.microsoft.com/technet/pro.../ss2kidbp.mspx
Sounds like you are getting a lot of memory pressure and the data on the
mirrored array with the log files does not help at all. It sounds like this
routine probably does table scans but you can verify that by looking at the
query plans. Before you did the defrag what was the total page count? And
after what was it? I am willing to bet the large amount of fragmentation
caused the page count to be extremely large and not very full resulting in a
lot of I/O's. The fact the pages were out of order didn't help any either.
Sounds like you would have been better to do a DBCC DBREINDEX instead due to
the large amount of fragmentation.
Andrew J. Kelly SQL MVP
"Olu Adedeji" <anonymous@.email.com> wrote in message
news:uOCXQA2nEHA.4068@.tk2msftngp13.phx.gbl...
> am a bit confused about something. I am trying to solve a problem. My
> developers wrote a piece of code that inserted 2million rows of data and
> then updated the data . .this obviously resulted in significant
> fragmentation . . . . scan density reported was quite low . . resulting in
> pagiolatch_sh waittypes(this slowed down performance) during reporting
> processes (this is partly also to do with the fact that the data and log
> files reside on a mirrored array) - pls don't ask, this is a system I have
> inherited and as a result I have to live with it for now , I have to work
> around these constraints taking into consideration that these export
> processes have always completed in less that 5minutes in the past.. .
> .obviously something has changed . .I have advised the developers about
> re-writing the query so that the inserts are just inserts and not
1)inserts
> then 2)updates. . . what I am trying to find an explanation for is
> 1) I advised for the code to be re-written to limit fragmentation - and it
> did 98% scan density
> 2) logical frag and extent frag was low as well
> 3) developer runs query and query takes forever!!
> a) I advise to run indexdefrag on clustered index
> b) extent frag and logical frag still low
> c) scan density is now worse
> d) BUT query is excuted in 5mins
>
> PS. I am not 'hands-on' just advising so I am waiting for exec plan for
the
> two scenarios.
> I know that the exec plan would be similar as stats has not changed post
> index defrag.
> any ideas why indxdefrag will report worse scan density?
>
> --
> Olu Adedeji
>
developers wrote a piece of code that inserted 2million rows of data and
then updated the data . .this obviously resulted in significant
fragmentation . . . . scan density reported was quite low . . resulting in
pagiolatch_sh waittypes(this slowed down performance) during reporting
processes (this is partly also to do with the fact that the data and log
files reside on a mirrored array) - pls don't ask, this is a system I have
inherited and as a result I have to live with it for now , I have to work
around these constraints taking into consideration that these export
processes have always completed in less that 5minutes in the past.. .
..obviously something has changed . .I have advised the developers about
re-writing the query so that the inserts are just inserts and not 1)inserts
then 2)updates. . . what I am trying to find an explanation for is
1) I advised for the code to be re-written to limit fragmentation - and it
did 98% scan density
2) logical frag and extent frag was low as well
3) developer runs query and query takes forever!!
a) I advise to run indexdefrag on clustered index
b) extent frag and logical frag still low
c) scan density is now worse
d) BUT query is excuted in 5mins
PS. I am not 'hands-on' just advising so I am waiting for exec plan for the
two scenarios.
I know that the exec plan would be similar as stats has not changed post
index defrag.
any ideas why indxdefrag will report worse scan density?
Olu Adedeji
Do you have multiple files in the filegroup by any chance? If so the only
figure to trust is the Logical fragmentation. Have a look here for more
details:
http://www.microsoft.com/technet/pro.../ss2kidbp.mspx
Sounds like you are getting a lot of memory pressure and the data on the
mirrored array with the log files does not help at all. It sounds like this
routine probably does table scans but you can verify that by looking at the
query plans. Before you did the defrag what was the total page count? And
after what was it? I am willing to bet the large amount of fragmentation
caused the page count to be extremely large and not very full resulting in a
lot of I/O's. The fact the pages were out of order didn't help any either.
Sounds like you would have been better to do a DBCC DBREINDEX instead due to
the large amount of fragmentation.
Andrew J. Kelly SQL MVP
"Olu Adedeji" <anonymous@.email.com> wrote in message
news:uOCXQA2nEHA.4068@.tk2msftngp13.phx.gbl...
> am a bit confused about something. I am trying to solve a problem. My
> developers wrote a piece of code that inserted 2million rows of data and
> then updated the data . .this obviously resulted in significant
> fragmentation . . . . scan density reported was quite low . . resulting in
> pagiolatch_sh waittypes(this slowed down performance) during reporting
> processes (this is partly also to do with the fact that the data and log
> files reside on a mirrored array) - pls don't ask, this is a system I have
> inherited and as a result I have to live with it for now , I have to work
> around these constraints taking into consideration that these export
> processes have always completed in less that 5minutes in the past.. .
> .obviously something has changed . .I have advised the developers about
> re-writing the query so that the inserts are just inserts and not
1)inserts
> then 2)updates. . . what I am trying to find an explanation for is
> 1) I advised for the code to be re-written to limit fragmentation - and it
> did 98% scan density
> 2) logical frag and extent frag was low as well
> 3) developer runs query and query takes forever!!
> a) I advise to run indexdefrag on clustered index
> b) extent frag and logical frag still low
> c) scan density is now worse
> d) BUT query is excuted in 5mins
>
> PS. I am not 'hands-on' just advising so I am waiting for exec plan for
the
> two scenarios.
> I know that the exec plan would be similar as stats has not changed post
> index defrag.
> any ideas why indxdefrag will report worse scan density?
>
> --
> Olu Adedeji
>
DBCC INDEXDEFRAG shows more fragmentation but better performance - Why?
am a bit confused about something. I am trying to solve a problem. My
developers wrote a piece of code that inserted 2million rows of data and
then updated the data . .this obviously resulted in significant
fragmentation . . . . scan density reported was quite low . . resulting in
pagiolatch_sh waittypes(this slowed down performance) during reporting
processes (this is partly also to do with the fact that the data and log
files reside on a mirrored array) - pls don't ask, this is a system I have
inherited and as a result I have to live with it for now , I have to work
around these constraints taking into consideration that these export
processes have always completed in less that 5minutes in the past.. .
.obviously something has changed . .I have advised the developers about
re-writing the query so that the inserts are just inserts and not 1)inserts
then 2)updates. . . what I am trying to find an explanation for is
1) I advised for the code to be re-written to limit fragmentation - and it
did 98% scan density
2) logical frag and extent frag was low as well
3) developer runs query and query takes forever!!
a) I advise to run indexdefrag on clustered index
b) extent frag and logical frag still low
c) scan density is now worse
d) BUT query is excuted in 5mins
PS. I am not 'hands-on' just advising so I am waiting for exec plan for the
two scenarios.
I know that the exec plan would be similar as stats has not changed post
index defrag.
any ideas why indxdefrag will report worse scan density?
--
Olu AdedejiDo you have multiple files in the filegroup by any chance? If so the only
figure to trust is the Logical fragmentation. Have a look here for more
details:
http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ss2kidbp.mspx
Sounds like you are getting a lot of memory pressure and the data on the
mirrored array with the log files does not help at all. It sounds like this
routine probably does table scans but you can verify that by looking at the
query plans. Before you did the defrag what was the total page count? And
after what was it? I am willing to bet the large amount of fragmentation
caused the page count to be extremely large and not very full resulting in a
lot of I/O's. The fact the pages were out of order didn't help any either.
Sounds like you would have been better to do a DBCC DBREINDEX instead due to
the large amount of fragmentation.
--
Andrew J. Kelly SQL MVP
"Olu Adedeji" <anonymous@.email.com> wrote in message
news:uOCXQA2nEHA.4068@.tk2msftngp13.phx.gbl...
> am a bit confused about something. I am trying to solve a problem. My
> developers wrote a piece of code that inserted 2million rows of data and
> then updated the data . .this obviously resulted in significant
> fragmentation . . . . scan density reported was quite low . . resulting in
> pagiolatch_sh waittypes(this slowed down performance) during reporting
> processes (this is partly also to do with the fact that the data and log
> files reside on a mirrored array) - pls don't ask, this is a system I have
> inherited and as a result I have to live with it for now , I have to work
> around these constraints taking into consideration that these export
> processes have always completed in less that 5minutes in the past.. .
> .obviously something has changed . .I have advised the developers about
> re-writing the query so that the inserts are just inserts and not
1)inserts
> then 2)updates. . . what I am trying to find an explanation for is
> 1) I advised for the code to be re-written to limit fragmentation - and it
> did 98% scan density
> 2) logical frag and extent frag was low as well
> 3) developer runs query and query takes forever!!
> a) I advise to run indexdefrag on clustered index
> b) extent frag and logical frag still low
> c) scan density is now worse
> d) BUT query is excuted in 5mins
>
> PS. I am not 'hands-on' just advising so I am waiting for exec plan for
the
> two scenarios.
> I know that the exec plan would be similar as stats has not changed post
> index defrag.
> any ideas why indxdefrag will report worse scan density?
>
> --
> Olu Adedeji
>
developers wrote a piece of code that inserted 2million rows of data and
then updated the data . .this obviously resulted in significant
fragmentation . . . . scan density reported was quite low . . resulting in
pagiolatch_sh waittypes(this slowed down performance) during reporting
processes (this is partly also to do with the fact that the data and log
files reside on a mirrored array) - pls don't ask, this is a system I have
inherited and as a result I have to live with it for now , I have to work
around these constraints taking into consideration that these export
processes have always completed in less that 5minutes in the past.. .
.obviously something has changed . .I have advised the developers about
re-writing the query so that the inserts are just inserts and not 1)inserts
then 2)updates. . . what I am trying to find an explanation for is
1) I advised for the code to be re-written to limit fragmentation - and it
did 98% scan density
2) logical frag and extent frag was low as well
3) developer runs query and query takes forever!!
a) I advise to run indexdefrag on clustered index
b) extent frag and logical frag still low
c) scan density is now worse
d) BUT query is excuted in 5mins
PS. I am not 'hands-on' just advising so I am waiting for exec plan for the
two scenarios.
I know that the exec plan would be similar as stats has not changed post
index defrag.
any ideas why indxdefrag will report worse scan density?
--
Olu AdedejiDo you have multiple files in the filegroup by any chance? If so the only
figure to trust is the Logical fragmentation. Have a look here for more
details:
http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ss2kidbp.mspx
Sounds like you are getting a lot of memory pressure and the data on the
mirrored array with the log files does not help at all. It sounds like this
routine probably does table scans but you can verify that by looking at the
query plans. Before you did the defrag what was the total page count? And
after what was it? I am willing to bet the large amount of fragmentation
caused the page count to be extremely large and not very full resulting in a
lot of I/O's. The fact the pages were out of order didn't help any either.
Sounds like you would have been better to do a DBCC DBREINDEX instead due to
the large amount of fragmentation.
--
Andrew J. Kelly SQL MVP
"Olu Adedeji" <anonymous@.email.com> wrote in message
news:uOCXQA2nEHA.4068@.tk2msftngp13.phx.gbl...
> am a bit confused about something. I am trying to solve a problem. My
> developers wrote a piece of code that inserted 2million rows of data and
> then updated the data . .this obviously resulted in significant
> fragmentation . . . . scan density reported was quite low . . resulting in
> pagiolatch_sh waittypes(this slowed down performance) during reporting
> processes (this is partly also to do with the fact that the data and log
> files reside on a mirrored array) - pls don't ask, this is a system I have
> inherited and as a result I have to live with it for now , I have to work
> around these constraints taking into consideration that these export
> processes have always completed in less that 5minutes in the past.. .
> .obviously something has changed . .I have advised the developers about
> re-writing the query so that the inserts are just inserts and not
1)inserts
> then 2)updates. . . what I am trying to find an explanation for is
> 1) I advised for the code to be re-written to limit fragmentation - and it
> did 98% scan density
> 2) logical frag and extent frag was low as well
> 3) developer runs query and query takes forever!!
> a) I advise to run indexdefrag on clustered index
> b) extent frag and logical frag still low
> c) scan density is now worse
> d) BUT query is excuted in 5mins
>
> PS. I am not 'hands-on' just advising so I am waiting for exec plan for
the
> two scenarios.
> I know that the exec plan would be similar as stats has not changed post
> index defrag.
> any ideas why indxdefrag will report worse scan density?
>
> --
> Olu Adedeji
>
DBCC INDEXDEFRAG script not working
From the script found here
(http://msdn2.microsoft.com/en-us/library/ms175008.aspx):
USE db
-- Declare variables
SET NOCOUNT ON;
DECLARE @.tablename varchar(128);
DECLARE @.execstr varchar(255);
DECLARE @.objectid int;
DECLARE @.indexid int;
DECLARE @.frag decimal;
DECLARE @.maxfrag decimal;
-- Decide on the maximum fragmentation to allow for.
SELECT @.maxfrag = 30.0;
-- Declare a cursor.
DECLARE tables CURSOR FOR
SELECT TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE';
-- Create the table.
CREATE TABLE #fraglist (
ObjectName char(255),
ObjectId int,
IndexName char(255),
IndexId int,
Lvl int,
CountPages int,
CountRows int,
MinRecSize int,
MaxRecSize int,
AvgRecSize int,
ForRecCount int,
Extents int,
ExtentSwitches int,
AvgFreeBytes int,
AvgPageDensity int,
ScanDensity decimal,
BestCount int,
ActualCount int,
LogicalFrag decimal,
ExtentFrag decimal);
-- Open the cursor.
OPEN tables;
-- Loop through all the tables in the database.
FETCH NEXT
FROM tables
INTO @.tablename;
WHILE @.@.FETCH_STATUS = 0
BEGIN;
-- Do the showcontig of all indexes of the table
INSERT INTO #fraglist
EXEC ('DBCC SHOWCONTIG (''' + @.tablename + ''')
WITH FAST, TABLERESULTS, ALL_INDEXES, NO_INFOMSGS');
FETCH NEXT
FROM tables
INTO @.tablename;
END;
-- Close and deallocate the cursor.
CLOSE tables;
DEALLOCATE tables;
-- Declare the cursor for the list of indexes to be defragged.
DECLARE indexes CURSOR FOR
SELECT ObjectName, ObjectId, IndexId, LogicalFrag
FROM #fraglist
WHERE LogicalFrag >= @.maxfrag
AND INDEXPROPERTY (ObjectId, IndexName, 'IndexDepth') > 0;
-- Open the cursor.
OPEN indexes;
-- Loop through the indexes.
FETCH NEXT
FROM indexes
INTO @.tablename, @.objectid, @.indexid, @.frag;
WHILE @.@.FETCH_STATUS = 0
BEGIN;
PRINT 'Executing DBCC INDEXDEFRAG (0, ' + RTRIM(@.tablename) + ',
' + RTRIM(@.indexid) + ') - fragmentation currently '
+ RTRIM(CONVERT(varchar(15),@.frag)) + '%';
SELECT @.execstr = 'DBCC INDEXDEFRAG (0, ' + RTRIM(@.objectid) + ',
' + RTRIM(@.indexid) + ')';
EXEC (@.execstr);
FETCH NEXT
FROM indexes
INTO @.tablename, @.objectid, @.indexid, @.frag;
END;
-- Close and deallocate the cursor.
CLOSE indexes;
DEALLOCATE indexes;
-- Delete the temporary table.
DROP TABLE #fraglist;
GO
I get the following errors:
Server: Msg 170, Level 15, State 1, Line 52
Line 52: Incorrect syntax near ';'.
Server: Msg 156, Level 15, State 1, Line 60
Incorrect syntax near the keyword 'END'.
Server: Msg 170, Level 15, State 1, Line 82
Line 82: Incorrect syntax near ';'.
Server: Msg 156, Level 15, State 1, Line 93
Incorrect syntax near the keyword 'END'.
I thought I had completed this successfully before but obviously I hadn't.
Does anyone have any tips?If this is run on sql server 2000, "Begin" cannot be terminated with a
semi-colon.
(http://msdn2.microsoft.com/en-us/library/ms175008.aspx):
USE db
-- Declare variables
SET NOCOUNT ON;
DECLARE @.tablename varchar(128);
DECLARE @.execstr varchar(255);
DECLARE @.objectid int;
DECLARE @.indexid int;
DECLARE @.frag decimal;
DECLARE @.maxfrag decimal;
-- Decide on the maximum fragmentation to allow for.
SELECT @.maxfrag = 30.0;
-- Declare a cursor.
DECLARE tables CURSOR FOR
SELECT TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE';
-- Create the table.
CREATE TABLE #fraglist (
ObjectName char(255),
ObjectId int,
IndexName char(255),
IndexId int,
Lvl int,
CountPages int,
CountRows int,
MinRecSize int,
MaxRecSize int,
AvgRecSize int,
ForRecCount int,
Extents int,
ExtentSwitches int,
AvgFreeBytes int,
AvgPageDensity int,
ScanDensity decimal,
BestCount int,
ActualCount int,
LogicalFrag decimal,
ExtentFrag decimal);
-- Open the cursor.
OPEN tables;
-- Loop through all the tables in the database.
FETCH NEXT
FROM tables
INTO @.tablename;
WHILE @.@.FETCH_STATUS = 0
BEGIN;
-- Do the showcontig of all indexes of the table
INSERT INTO #fraglist
EXEC ('DBCC SHOWCONTIG (''' + @.tablename + ''')
WITH FAST, TABLERESULTS, ALL_INDEXES, NO_INFOMSGS');
FETCH NEXT
FROM tables
INTO @.tablename;
END;
-- Close and deallocate the cursor.
CLOSE tables;
DEALLOCATE tables;
-- Declare the cursor for the list of indexes to be defragged.
DECLARE indexes CURSOR FOR
SELECT ObjectName, ObjectId, IndexId, LogicalFrag
FROM #fraglist
WHERE LogicalFrag >= @.maxfrag
AND INDEXPROPERTY (ObjectId, IndexName, 'IndexDepth') > 0;
-- Open the cursor.
OPEN indexes;
-- Loop through the indexes.
FETCH NEXT
FROM indexes
INTO @.tablename, @.objectid, @.indexid, @.frag;
WHILE @.@.FETCH_STATUS = 0
BEGIN;
PRINT 'Executing DBCC INDEXDEFRAG (0, ' + RTRIM(@.tablename) + ',
' + RTRIM(@.indexid) + ') - fragmentation currently '
+ RTRIM(CONVERT(varchar(15),@.frag)) + '%';
SELECT @.execstr = 'DBCC INDEXDEFRAG (0, ' + RTRIM(@.objectid) + ',
' + RTRIM(@.indexid) + ')';
EXEC (@.execstr);
FETCH NEXT
FROM indexes
INTO @.tablename, @.objectid, @.indexid, @.frag;
END;
-- Close and deallocate the cursor.
CLOSE indexes;
DEALLOCATE indexes;
-- Delete the temporary table.
DROP TABLE #fraglist;
GO
I get the following errors:
Server: Msg 170, Level 15, State 1, Line 52
Line 52: Incorrect syntax near ';'.
Server: Msg 156, Level 15, State 1, Line 60
Incorrect syntax near the keyword 'END'.
Server: Msg 170, Level 15, State 1, Line 82
Line 82: Incorrect syntax near ';'.
Server: Msg 156, Level 15, State 1, Line 93
Incorrect syntax near the keyword 'END'.
I thought I had completed this successfully before but obviously I hadn't.
Does anyone have any tips?If this is run on sql server 2000, "Begin" cannot be terminated with a
semi-colon.
DBCC IndexDefrag Or DBCC REINDEX
Hi ,
Do the IndexDefrag or DBCC REINDEX auto truncate of shrink the files
after process ?
What is the properly method after perform IndexDefrag or REINDEX ?
Currently , I shrink the log , restart the server and perform either index
derag command. It is the right procedure to go ?
Thanks ,
Travis
DBCC INDEXDEFRAG and DBREINDEX operate at page level in your database files,
the first reorganising the leaf level of your indexes (and effectively
freeing some page but NOT releasing them) and the second dropping and
rebuilding one or more indexes. Anyway those two statements will never shrink
your database or part of it.
About the transaction log shrinking, i suppose you are strongly searching
for stporage space, otherwise you are spending a lot of administrative effort
for poor results: what you are shrinking now, will grow (automatically or
manually) in the next few hours.
Gilberto Zampatti
"Travis" wrote:
> Hi ,
> Do the IndexDefrag or DBCC REINDEX auto truncate of shrink the files
> after process ?
> What is the properly method after perform IndexDefrag or REINDEX ?
> Currently , I shrink the log , restart the server and perform either index
> derag command. It is the right procedure to go ?
> Thanks ,
> Travis
>
|||In addition to the other posts, related to shrink, check out:
http://www.karaszi.com/SQLServer/info_dont_shrink.asp
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Travis" <Travis@.discussions.microsoft.com> wrote in message
news:469457A9-7C68-4857-8ADA-83DAD7AFF7B4@.microsoft.com...
> Hi ,
> Do the IndexDefrag or DBCC REINDEX auto truncate of shrink the files
> after process ?
> What is the properly method after perform IndexDefrag or REINDEX ?
> Currently , I shrink the log , restart the server and perform either index
> derag command. It is the right procedure to go ?
> Thanks ,
> Travis
>
|||Hi Travis
"Travis" wrote:
> Hi ,
> Do the IndexDefrag or DBCC REINDEX auto truncate of shrink the files
> after process ?
> What is the properly method after perform IndexDefrag or REINDEX ?
> Currently , I shrink the log , restart the server and perform either index
> derag command. It is the right procedure to go ?
> Thanks ,
> Travis
You don't say what the recovery model is, but I would assume it is full or
bulk logged?
If your system can handle the maximum size that the transaction log reaches
then it is probably better not to shrink the file see
http://www.karaszi.com/sqlserver/info_dont_shrink.asp and the links if
references.
If you are running the defragmentation for all indexes look at the example E
for DBCC SHOWCONTIG in books online or at
http://msdn2.microsoft.com/en-us/library/aa258803(SQL.80).aspx and reduce the
amount of work and growth of the log file size.
I am not sure why you wish to stop/start the server?
John
Do the IndexDefrag or DBCC REINDEX auto truncate of shrink the files
after process ?
What is the properly method after perform IndexDefrag or REINDEX ?
Currently , I shrink the log , restart the server and perform either index
derag command. It is the right procedure to go ?
Thanks ,
Travis
DBCC INDEXDEFRAG and DBREINDEX operate at page level in your database files,
the first reorganising the leaf level of your indexes (and effectively
freeing some page but NOT releasing them) and the second dropping and
rebuilding one or more indexes. Anyway those two statements will never shrink
your database or part of it.
About the transaction log shrinking, i suppose you are strongly searching
for stporage space, otherwise you are spending a lot of administrative effort
for poor results: what you are shrinking now, will grow (automatically or
manually) in the next few hours.
Gilberto Zampatti
"Travis" wrote:
> Hi ,
> Do the IndexDefrag or DBCC REINDEX auto truncate of shrink the files
> after process ?
> What is the properly method after perform IndexDefrag or REINDEX ?
> Currently , I shrink the log , restart the server and perform either index
> derag command. It is the right procedure to go ?
> Thanks ,
> Travis
>
|||In addition to the other posts, related to shrink, check out:
http://www.karaszi.com/SQLServer/info_dont_shrink.asp
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Travis" <Travis@.discussions.microsoft.com> wrote in message
news:469457A9-7C68-4857-8ADA-83DAD7AFF7B4@.microsoft.com...
> Hi ,
> Do the IndexDefrag or DBCC REINDEX auto truncate of shrink the files
> after process ?
> What is the properly method after perform IndexDefrag or REINDEX ?
> Currently , I shrink the log , restart the server and perform either index
> derag command. It is the right procedure to go ?
> Thanks ,
> Travis
>
|||Hi Travis
"Travis" wrote:
> Hi ,
> Do the IndexDefrag or DBCC REINDEX auto truncate of shrink the files
> after process ?
> What is the properly method after perform IndexDefrag or REINDEX ?
> Currently , I shrink the log , restart the server and perform either index
> derag command. It is the right procedure to go ?
> Thanks ,
> Travis
You don't say what the recovery model is, but I would assume it is full or
bulk logged?
If your system can handle the maximum size that the transaction log reaches
then it is probably better not to shrink the file see
http://www.karaszi.com/sqlserver/info_dont_shrink.asp and the links if
references.
If you are running the defragmentation for all indexes look at the example E
for DBCC SHOWCONTIG in books online or at
http://msdn2.microsoft.com/en-us/library/aa258803(SQL.80).aspx and reduce the
amount of work and growth of the log file size.
I am not sure why you wish to stop/start the server?
John
DBCC IndexDefrag Or DBCC REINDEX
Hi ,
Do the IndexDefrag or DBCC REINDEX auto truncate of shrink the files
after process ?
What is the properly method after perform IndexDefrag or REINDEX ?
Currently , I shrink the log , restart the server and perform either index
derag command. It is the right procedure to go ?
Thanks ,
TravisHI,
Do the IndexDefrag or DBCC REINDEX auto truncate of shrink the files after
process ?
NO
I did see any valid reason to restart the server, what I would do is
backup the database before indexing
reindex the db
backup transaction log
shrink log file
Regards
VT
Knowledge is power, share it...
http://oneplace4sql.blogspot.com/
"Travis" <Travis@.discussions.microsoft.com> wrote in message
news:469457A9-7C68-4857-8ADA-83DAD7AFF7B4@.microsoft.com...
> Hi ,
> Do the IndexDefrag or DBCC REINDEX auto truncate of shrink the files
> after process ?
> What is the properly method after perform IndexDefrag or REINDEX ?
> Currently , I shrink the log , restart the server and perform either index
> derag command. It is the right procedure to go ?
> Thanks ,
> Travis
>|||DBCC INDEXDEFRAG and DBREINDEX operate at page level in your database files,
the first reorganising the leaf level of your indexes (and effectively
freeing some page but NOT releasing them) and the second dropping and
rebuilding one or more indexes. Anyway those two statements will never shrin
k
your database or part of it.
About the transaction log shrinking, i suppose you are strongly searching
for stporage space, otherwise you are spending a lot of administrative effor
t
for poor results: what you are shrinking now, will grow (automatically or
manually) in the next few hours.
Gilberto Zampatti
"Travis" wrote:
> Hi ,
> Do the IndexDefrag or DBCC REINDEX auto truncate of shrink the files
> after process ?
> What is the properly method after perform IndexDefrag or REINDEX ?
> Currently , I shrink the log , restart the server and perform either index
> derag command. It is the right procedure to go ?
> Thanks ,
> Travis
>|||In addition to the other posts, related to shrink, check out:
http://www.karaszi.com/SQLServer/info_dont_shrink.asp
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Travis" <Travis@.discussions.microsoft.com> wrote in message
news:469457A9-7C68-4857-8ADA-83DAD7AFF7B4@.microsoft.com...
> Hi ,
> Do the IndexDefrag or DBCC REINDEX auto truncate of shrink the files
> after process ?
> What is the properly method after perform IndexDefrag or REINDEX ?
> Currently , I shrink the log , restart the server and perform either index
> derag command. It is the right procedure to go ?
> Thanks ,
> Travis
>|||Hi Travis
"Travis" wrote:
> Hi ,
> Do the IndexDefrag or DBCC REINDEX auto truncate of shrink the files
> after process ?
> What is the properly method after perform IndexDefrag or REINDEX ?
> Currently , I shrink the log , restart the server and perform either index
> derag command. It is the right procedure to go ?
> Thanks ,
> Travis
You don't say what the recovery model is, but I would assume it is full or
bulk logged?
If your system can handle the maximum size that the transaction log reaches
then it is probably better not to shrink the file see
http://www.karaszi.com/sqlserver/info_dont_shrink.asp and the links if
references.
If you are running the defragmentation for all indexes look at the example E
for DBCC SHOWCONTIG in books online or at
http://msdn2.microsoft.com/en-us/library/aa258803(SQL.80).aspx and reduce th
e
amount of work and growth of the log file size.
I am not sure why you wish to stop/start the server?
John
Do the IndexDefrag or DBCC REINDEX auto truncate of shrink the files
after process ?
What is the properly method after perform IndexDefrag or REINDEX ?
Currently , I shrink the log , restart the server and perform either index
derag command. It is the right procedure to go ?
Thanks ,
TravisHI,
Do the IndexDefrag or DBCC REINDEX auto truncate of shrink the files after
process ?
NO
I did see any valid reason to restart the server, what I would do is
backup the database before indexing
reindex the db
backup transaction log
shrink log file
Regards
VT
Knowledge is power, share it...
http://oneplace4sql.blogspot.com/
"Travis" <Travis@.discussions.microsoft.com> wrote in message
news:469457A9-7C68-4857-8ADA-83DAD7AFF7B4@.microsoft.com...
> Hi ,
> Do the IndexDefrag or DBCC REINDEX auto truncate of shrink the files
> after process ?
> What is the properly method after perform IndexDefrag or REINDEX ?
> Currently , I shrink the log , restart the server and perform either index
> derag command. It is the right procedure to go ?
> Thanks ,
> Travis
>|||DBCC INDEXDEFRAG and DBREINDEX operate at page level in your database files,
the first reorganising the leaf level of your indexes (and effectively
freeing some page but NOT releasing them) and the second dropping and
rebuilding one or more indexes. Anyway those two statements will never shrin
k
your database or part of it.
About the transaction log shrinking, i suppose you are strongly searching
for stporage space, otherwise you are spending a lot of administrative effor
t
for poor results: what you are shrinking now, will grow (automatically or
manually) in the next few hours.
Gilberto Zampatti
"Travis" wrote:
> Hi ,
> Do the IndexDefrag or DBCC REINDEX auto truncate of shrink the files
> after process ?
> What is the properly method after perform IndexDefrag or REINDEX ?
> Currently , I shrink the log , restart the server and perform either index
> derag command. It is the right procedure to go ?
> Thanks ,
> Travis
>|||In addition to the other posts, related to shrink, check out:
http://www.karaszi.com/SQLServer/info_dont_shrink.asp
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Travis" <Travis@.discussions.microsoft.com> wrote in message
news:469457A9-7C68-4857-8ADA-83DAD7AFF7B4@.microsoft.com...
> Hi ,
> Do the IndexDefrag or DBCC REINDEX auto truncate of shrink the files
> after process ?
> What is the properly method after perform IndexDefrag or REINDEX ?
> Currently , I shrink the log , restart the server and perform either index
> derag command. It is the right procedure to go ?
> Thanks ,
> Travis
>|||Hi Travis
"Travis" wrote:
> Hi ,
> Do the IndexDefrag or DBCC REINDEX auto truncate of shrink the files
> after process ?
> What is the properly method after perform IndexDefrag or REINDEX ?
> Currently , I shrink the log , restart the server and perform either index
> derag command. It is the right procedure to go ?
> Thanks ,
> Travis
You don't say what the recovery model is, but I would assume it is full or
bulk logged?
If your system can handle the maximum size that the transaction log reaches
then it is probably better not to shrink the file see
http://www.karaszi.com/sqlserver/info_dont_shrink.asp and the links if
references.
If you are running the defragmentation for all indexes look at the example E
for DBCC SHOWCONTIG in books online or at
http://msdn2.microsoft.com/en-us/library/aa258803(SQL.80).aspx and reduce th
e
amount of work and growth of the log file size.
I am not sure why you wish to stop/start the server?
John
DBCC IndexDefrag Or DBCC REINDEX
Hi ,
Do the IndexDefrag or DBCC REINDEX auto truncate of shrink the files
after process ?
What is the properly method after perform IndexDefrag or REINDEX ?
Currently , I shrink the log , restart the server and perform either index
derag command. It is the right procedure to go ?
Thanks ,
TravisHI,
Do the IndexDefrag or DBCC REINDEX auto truncate of shrink the files after
process ?
NO
I did see any valid reason to restart the server, what I would do is
backup the database before indexing
reindex the db
backup transaction log
shrink log file
Regards
--
VT
Knowledge is power, share it...
http://oneplace4sql.blogspot.com/
"Travis" <Travis@.discussions.microsoft.com> wrote in message
news:469457A9-7C68-4857-8ADA-83DAD7AFF7B4@.microsoft.com...
> Hi ,
> Do the IndexDefrag or DBCC REINDEX auto truncate of shrink the files
> after process ?
> What is the properly method after perform IndexDefrag or REINDEX ?
> Currently , I shrink the log , restart the server and perform either index
> derag command. It is the right procedure to go ?
> Thanks ,
> Travis
>|||DBCC INDEXDEFRAG and DBREINDEX operate at page level in your database files,
the first reorganising the leaf level of your indexes (and effectively
freeing some page but NOT releasing them) and the second dropping and
rebuilding one or more indexes. Anyway those two statements will never shrink
your database or part of it.
About the transaction log shrinking, i suppose you are strongly searching
for stporage space, otherwise you are spending a lot of administrative effort
for poor results: what you are shrinking now, will grow (automatically or
manually) in the next few hours.
Gilberto Zampatti
"Travis" wrote:
> Hi ,
> Do the IndexDefrag or DBCC REINDEX auto truncate of shrink the files
> after process ?
> What is the properly method after perform IndexDefrag or REINDEX ?
> Currently , I shrink the log , restart the server and perform either index
> derag command. It is the right procedure to go ?
> Thanks ,
> Travis
>|||In addition to the other posts, related to shrink, check out:
http://www.karaszi.com/SQLServer/info_dont_shrink.asp
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Travis" <Travis@.discussions.microsoft.com> wrote in message
news:469457A9-7C68-4857-8ADA-83DAD7AFF7B4@.microsoft.com...
> Hi ,
> Do the IndexDefrag or DBCC REINDEX auto truncate of shrink the files
> after process ?
> What is the properly method after perform IndexDefrag or REINDEX ?
> Currently , I shrink the log , restart the server and perform either index
> derag command. It is the right procedure to go ?
> Thanks ,
> Travis
>|||Hi Travis
"Travis" wrote:
> Hi ,
> Do the IndexDefrag or DBCC REINDEX auto truncate of shrink the files
> after process ?
> What is the properly method after perform IndexDefrag or REINDEX ?
> Currently , I shrink the log , restart the server and perform either index
> derag command. It is the right procedure to go ?
> Thanks ,
> Travis
You don't say what the recovery model is, but I would assume it is full or
bulk logged?
If your system can handle the maximum size that the transaction log reaches
then it is probably better not to shrink the file see
http://www.karaszi.com/sqlserver/info_dont_shrink.asp and the links if
references.
If you are running the defragmentation for all indexes look at the example E
for DBCC SHOWCONTIG in books online or at
http://msdn2.microsoft.com/en-us/library/aa258803(SQL.80).aspx and reduce the
amount of work and growth of the log file size.
I am not sure why you wish to stop/start the server?
John
Do the IndexDefrag or DBCC REINDEX auto truncate of shrink the files
after process ?
What is the properly method after perform IndexDefrag or REINDEX ?
Currently , I shrink the log , restart the server and perform either index
derag command. It is the right procedure to go ?
Thanks ,
TravisHI,
Do the IndexDefrag or DBCC REINDEX auto truncate of shrink the files after
process ?
NO
I did see any valid reason to restart the server, what I would do is
backup the database before indexing
reindex the db
backup transaction log
shrink log file
Regards
--
VT
Knowledge is power, share it...
http://oneplace4sql.blogspot.com/
"Travis" <Travis@.discussions.microsoft.com> wrote in message
news:469457A9-7C68-4857-8ADA-83DAD7AFF7B4@.microsoft.com...
> Hi ,
> Do the IndexDefrag or DBCC REINDEX auto truncate of shrink the files
> after process ?
> What is the properly method after perform IndexDefrag or REINDEX ?
> Currently , I shrink the log , restart the server and perform either index
> derag command. It is the right procedure to go ?
> Thanks ,
> Travis
>|||DBCC INDEXDEFRAG and DBREINDEX operate at page level in your database files,
the first reorganising the leaf level of your indexes (and effectively
freeing some page but NOT releasing them) and the second dropping and
rebuilding one or more indexes. Anyway those two statements will never shrink
your database or part of it.
About the transaction log shrinking, i suppose you are strongly searching
for stporage space, otherwise you are spending a lot of administrative effort
for poor results: what you are shrinking now, will grow (automatically or
manually) in the next few hours.
Gilberto Zampatti
"Travis" wrote:
> Hi ,
> Do the IndexDefrag or DBCC REINDEX auto truncate of shrink the files
> after process ?
> What is the properly method after perform IndexDefrag or REINDEX ?
> Currently , I shrink the log , restart the server and perform either index
> derag command. It is the right procedure to go ?
> Thanks ,
> Travis
>|||In addition to the other posts, related to shrink, check out:
http://www.karaszi.com/SQLServer/info_dont_shrink.asp
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Travis" <Travis@.discussions.microsoft.com> wrote in message
news:469457A9-7C68-4857-8ADA-83DAD7AFF7B4@.microsoft.com...
> Hi ,
> Do the IndexDefrag or DBCC REINDEX auto truncate of shrink the files
> after process ?
> What is the properly method after perform IndexDefrag or REINDEX ?
> Currently , I shrink the log , restart the server and perform either index
> derag command. It is the right procedure to go ?
> Thanks ,
> Travis
>|||Hi Travis
"Travis" wrote:
> Hi ,
> Do the IndexDefrag or DBCC REINDEX auto truncate of shrink the files
> after process ?
> What is the properly method after perform IndexDefrag or REINDEX ?
> Currently , I shrink the log , restart the server and perform either index
> derag command. It is the right procedure to go ?
> Thanks ,
> Travis
You don't say what the recovery model is, but I would assume it is full or
bulk logged?
If your system can handle the maximum size that the transaction log reaches
then it is probably better not to shrink the file see
http://www.karaszi.com/sqlserver/info_dont_shrink.asp and the links if
references.
If you are running the defragmentation for all indexes look at the example E
for DBCC SHOWCONTIG in books online or at
http://msdn2.microsoft.com/en-us/library/aa258803(SQL.80).aspx and reduce the
amount of work and growth of the log file size.
I am not sure why you wish to stop/start the server?
John
dbcc indexdefrag not doing anything
sql2k sp3
I keep running dbcc indexdefrag on a small(236,492 rows)
table and it doesnt change the output of the Scan Density
(best count:actual count) from dbcc showcontig at all.
Any ideas?
TIA, ChrisDoes your table have a clustered index?
--
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
"chris" <anonymous@.discussions.microsoft.com> wrote in message
news:556301c3e508$94bae040$a501280a@.phx.gbl...
> sql2k sp3
> I keep running dbcc indexdefrag on a small(236,492 rows)
> table and it doesnt change the output of the Scan Density
> (best count:actual count) from dbcc showcontig at all.
>
> Any ideas?
> TIA, Chris
>|||Yes. Thats the index Im trying to defragment.
>--Original Message--
>Does your table have a clustered index?
>--
>Geoff N. Hiten
>Microsoft SQL Server MVP
>Senior Database Administrator
>Careerbuilder.com
>"chris" <anonymous@.discussions.microsoft.com> wrote in
message
>news:556301c3e508$94bae040$a501280a@.phx.gbl...
>> sql2k sp3
>> I keep running dbcc indexdefrag on a small(236,492 rows)
>> table and it doesnt change the output of the Scan
Density
>> (best count:actual count) from dbcc showcontig at all.
>>
>> Any ideas?
>> TIA, Chris
>
>.
>|||Ok, DBCC IndexDefrag does an in-place defrag thus there are no new
allocations. Therefore the physical page/extent fragmentation will remain,
even though the rows are now in physical order on the pages. One of the
hidden limitations of INDEXDEFRAG vs. DBREINDEX.
--
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
"chris" <anonymous@.discussions.microsoft.com> wrote in message
news:583c01c3e50b$62b95560$a601280a@.phx.gbl...
> Yes. Thats the index Im trying to defragment.
> >--Original Message--
> >Does your table have a clustered index?
> >
> >--
> >Geoff N. Hiten
> >Microsoft SQL Server MVP
> >Senior Database Administrator
> >Careerbuilder.com
> >
> >"chris" <anonymous@.discussions.microsoft.com> wrote in
> message
> >news:556301c3e508$94bae040$a501280a@.phx.gbl...
> >> sql2k sp3
> >>
> >> I keep running dbcc indexdefrag on a small(236,492 rows)
> >> table and it doesnt change the output of the Scan
> Density
> >> (best count:actual count) from dbcc showcontig at all.
> >>
> >>
> >> Any ideas?
> >>
> >> TIA, Chris
> >>
> >
> >
> >.
> >|||Just to clarify, this means that the table is actually de-
fraged, it just appears otherwise?
>--Original Message--
>Ok, DBCC IndexDefrag does an in-place defrag thus there
are no new
>allocations. Therefore the physical page/extent
fragmentation will remain,
>even though the rows are now in physical order on the
pages. One of the
>hidden limitations of INDEXDEFRAG vs. DBREINDEX.
>--
>Geoff N. Hiten
>Microsoft SQL Server MVP
>Senior Database Administrator
>Careerbuilder.com
>"chris" <anonymous@.discussions.microsoft.com> wrote in
message
>news:583c01c3e50b$62b95560$a601280a@.phx.gbl...
>> Yes. Thats the index Im trying to defragment.
>> >--Original Message--
>> >Does your table have a clustered index?
>> >
>> >--
>> >Geoff N. Hiten
>> >Microsoft SQL Server MVP
>> >Senior Database Administrator
>> >Careerbuilder.com
>> >
>> >"chris" <anonymous@.discussions.microsoft.com> wrote in
>> message
>> >news:556301c3e508$94bae040$a501280a@.phx.gbl...
>> >> sql2k sp3
>> >>
>> >> I keep running dbcc indexdefrag on a small(236,492
rows)
>> >> table and it doesnt change the output of the Scan
>> Density
>> >> (best count:actual count) from dbcc showcontig at
all.
>> >>
>> >>
>> >> Any ideas?
>> >>
>> >> TIA, Chris
>> >>
>> >
>> >
>> >.
>> >
>
>.
>|||The index rows are in physical order on each page. Page allocations are not
altered so if the pages are scattered around the database, that will not
change.
--
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
"chris" <anonymous@.discussions.microsoft.com> wrote in message
news:557401c3e50e$bf0d2b40$a401280a@.phx.gbl...
> Just to clarify, this means that the table is actually de-
> fraged, it just appears otherwise?
> >--Original Message--
> >Ok, DBCC IndexDefrag does an in-place defrag thus there
> are no new
> >allocations. Therefore the physical page/extent
> fragmentation will remain,
> >even though the rows are now in physical order on the
> pages. One of the
> >hidden limitations of INDEXDEFRAG vs. DBREINDEX.
> >
> >--
> >Geoff N. Hiten
> >Microsoft SQL Server MVP
> >Senior Database Administrator
> >Careerbuilder.com
> >
> >"chris" <anonymous@.discussions.microsoft.com> wrote in
> message
> >news:583c01c3e50b$62b95560$a601280a@.phx.gbl...
> >> Yes. Thats the index Im trying to defragment.
> >>
> >> >--Original Message--
> >> >Does your table have a clustered index?
> >> >
> >> >--
> >> >Geoff N. Hiten
> >> >Microsoft SQL Server MVP
> >> >Senior Database Administrator
> >> >Careerbuilder.com
> >> >
> >> >"chris" <anonymous@.discussions.microsoft.com> wrote in
> >> message
> >> >news:556301c3e508$94bae040$a501280a@.phx.gbl...
> >> >> sql2k sp3
> >> >>
> >> >> I keep running dbcc indexdefrag on a small(236,492
> rows)
> >> >> table and it doesnt change the output of the Scan
> >> Density
> >> >> (best count:actual count) from dbcc showcontig at
> all.
> >> >>
> >> >>
> >> >> Any ideas?
> >> >>
> >> >> TIA, Chris
> >> >>
> >> >
> >> >
> >> >.
> >> >
> >
> >
> >.
> >|||In addition to Geoff's remarks: DBCC INDEXDEFRAG will skip locked rows.
So if your database has much locking activity during defragmentation,
the DBCC command will be less effective.
Gert-Jan|||And in addition to that Indexdefrag only works on 1 file at a time. This
means extent fragmentation is useless on multiple files.
--
Andrew J. Kelly SQL MVP
"Gert-Jan Strik" <sorry@.toomuchspamalready.nl> wrote in message
news:4016C868.AD86E998@.toomuchspamalready.nl...
> In addition to Geoff's remarks: DBCC INDEXDEFRAG will skip locked rows.
> So if your database has much locking activity during defragmentation,
> the DBCC command will be less effective.
> Gert-Jan|||And in addition to that <g>, scan density is useless in the first place if > 1 data file. Chris,
look at Logical Scan Fragmentation if you have > 1 data file.
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Andrew J. Kelly" <sqlmvpnoooospam@.shadhawk.com> wrote in message
news:e8DQroU5DHA.360@.TK2MSFTNGP12.phx.gbl...
> And in addition to that Indexdefrag only works on 1 file at a time. This
> means extent fragmentation is useless on multiple files.
> --
> Andrew J. Kelly SQL MVP
>
> "Gert-Jan Strik" <sorry@.toomuchspamalready.nl> wrote in message
> news:4016C868.AD86E998@.toomuchspamalready.nl...
> > In addition to Geoff's remarks: DBCC INDEXDEFRAG will skip locked rows.
> > So if your database has much locking activity during defragmentation,
> > the DBCC command will be less effective.
> >
> > Gert-Jan
>|||BTW, all of this information is documented very clearly in BOL for DBCC
SHOWCONTIG and DBCC INDEXDFRAG. You can also read the whitepaper nelow for
more details:
http://www.microsoft.com/technet/treeview/default.asp?url=/technet/prodtechnol/sql/maintain/optimize/ss2kidbp.asp
Regards
--
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:OYWte4Y5DHA.2412@.TK2MSFTNGP11.phx.gbl...
> And in addition to that <g>, scan density is useless in the first place if
> 1 data file. Chris,
> look at Logical Scan Fragmentation if you have > 1 data file.
> --
> Tibor Karaszi, SQL Server MVP
> Archive at:
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
>
> "Andrew J. Kelly" <sqlmvpnoooospam@.shadhawk.com> wrote in message
> news:e8DQroU5DHA.360@.TK2MSFTNGP12.phx.gbl...
> > And in addition to that Indexdefrag only works on 1 file at a time.
This
> > means extent fragmentation is useless on multiple files.
> >
> > --
> > Andrew J. Kelly SQL MVP
> >
> >
> > "Gert-Jan Strik" <sorry@.toomuchspamalready.nl> wrote in message
> > news:4016C868.AD86E998@.toomuchspamalready.nl...
> > > In addition to Geoff's remarks: DBCC INDEXDEFRAG will skip locked
rows.
> > > So if your database has much locking activity during defragmentation,
> > > the DBCC command will be less effective.
> > >
> > > Gert-Jan
> >
> >
>
I keep running dbcc indexdefrag on a small(236,492 rows)
table and it doesnt change the output of the Scan Density
(best count:actual count) from dbcc showcontig at all.
Any ideas?
TIA, ChrisDoes your table have a clustered index?
--
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
"chris" <anonymous@.discussions.microsoft.com> wrote in message
news:556301c3e508$94bae040$a501280a@.phx.gbl...
> sql2k sp3
> I keep running dbcc indexdefrag on a small(236,492 rows)
> table and it doesnt change the output of the Scan Density
> (best count:actual count) from dbcc showcontig at all.
>
> Any ideas?
> TIA, Chris
>|||Yes. Thats the index Im trying to defragment.
>--Original Message--
>Does your table have a clustered index?
>--
>Geoff N. Hiten
>Microsoft SQL Server MVP
>Senior Database Administrator
>Careerbuilder.com
>"chris" <anonymous@.discussions.microsoft.com> wrote in
message
>news:556301c3e508$94bae040$a501280a@.phx.gbl...
>> sql2k sp3
>> I keep running dbcc indexdefrag on a small(236,492 rows)
>> table and it doesnt change the output of the Scan
Density
>> (best count:actual count) from dbcc showcontig at all.
>>
>> Any ideas?
>> TIA, Chris
>
>.
>|||Ok, DBCC IndexDefrag does an in-place defrag thus there are no new
allocations. Therefore the physical page/extent fragmentation will remain,
even though the rows are now in physical order on the pages. One of the
hidden limitations of INDEXDEFRAG vs. DBREINDEX.
--
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
"chris" <anonymous@.discussions.microsoft.com> wrote in message
news:583c01c3e50b$62b95560$a601280a@.phx.gbl...
> Yes. Thats the index Im trying to defragment.
> >--Original Message--
> >Does your table have a clustered index?
> >
> >--
> >Geoff N. Hiten
> >Microsoft SQL Server MVP
> >Senior Database Administrator
> >Careerbuilder.com
> >
> >"chris" <anonymous@.discussions.microsoft.com> wrote in
> message
> >news:556301c3e508$94bae040$a501280a@.phx.gbl...
> >> sql2k sp3
> >>
> >> I keep running dbcc indexdefrag on a small(236,492 rows)
> >> table and it doesnt change the output of the Scan
> Density
> >> (best count:actual count) from dbcc showcontig at all.
> >>
> >>
> >> Any ideas?
> >>
> >> TIA, Chris
> >>
> >
> >
> >.
> >|||Just to clarify, this means that the table is actually de-
fraged, it just appears otherwise?
>--Original Message--
>Ok, DBCC IndexDefrag does an in-place defrag thus there
are no new
>allocations. Therefore the physical page/extent
fragmentation will remain,
>even though the rows are now in physical order on the
pages. One of the
>hidden limitations of INDEXDEFRAG vs. DBREINDEX.
>--
>Geoff N. Hiten
>Microsoft SQL Server MVP
>Senior Database Administrator
>Careerbuilder.com
>"chris" <anonymous@.discussions.microsoft.com> wrote in
message
>news:583c01c3e50b$62b95560$a601280a@.phx.gbl...
>> Yes. Thats the index Im trying to defragment.
>> >--Original Message--
>> >Does your table have a clustered index?
>> >
>> >--
>> >Geoff N. Hiten
>> >Microsoft SQL Server MVP
>> >Senior Database Administrator
>> >Careerbuilder.com
>> >
>> >"chris" <anonymous@.discussions.microsoft.com> wrote in
>> message
>> >news:556301c3e508$94bae040$a501280a@.phx.gbl...
>> >> sql2k sp3
>> >>
>> >> I keep running dbcc indexdefrag on a small(236,492
rows)
>> >> table and it doesnt change the output of the Scan
>> Density
>> >> (best count:actual count) from dbcc showcontig at
all.
>> >>
>> >>
>> >> Any ideas?
>> >>
>> >> TIA, Chris
>> >>
>> >
>> >
>> >.
>> >
>
>.
>|||The index rows are in physical order on each page. Page allocations are not
altered so if the pages are scattered around the database, that will not
change.
--
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
"chris" <anonymous@.discussions.microsoft.com> wrote in message
news:557401c3e50e$bf0d2b40$a401280a@.phx.gbl...
> Just to clarify, this means that the table is actually de-
> fraged, it just appears otherwise?
> >--Original Message--
> >Ok, DBCC IndexDefrag does an in-place defrag thus there
> are no new
> >allocations. Therefore the physical page/extent
> fragmentation will remain,
> >even though the rows are now in physical order on the
> pages. One of the
> >hidden limitations of INDEXDEFRAG vs. DBREINDEX.
> >
> >--
> >Geoff N. Hiten
> >Microsoft SQL Server MVP
> >Senior Database Administrator
> >Careerbuilder.com
> >
> >"chris" <anonymous@.discussions.microsoft.com> wrote in
> message
> >news:583c01c3e50b$62b95560$a601280a@.phx.gbl...
> >> Yes. Thats the index Im trying to defragment.
> >>
> >> >--Original Message--
> >> >Does your table have a clustered index?
> >> >
> >> >--
> >> >Geoff N. Hiten
> >> >Microsoft SQL Server MVP
> >> >Senior Database Administrator
> >> >Careerbuilder.com
> >> >
> >> >"chris" <anonymous@.discussions.microsoft.com> wrote in
> >> message
> >> >news:556301c3e508$94bae040$a501280a@.phx.gbl...
> >> >> sql2k sp3
> >> >>
> >> >> I keep running dbcc indexdefrag on a small(236,492
> rows)
> >> >> table and it doesnt change the output of the Scan
> >> Density
> >> >> (best count:actual count) from dbcc showcontig at
> all.
> >> >>
> >> >>
> >> >> Any ideas?
> >> >>
> >> >> TIA, Chris
> >> >>
> >> >
> >> >
> >> >.
> >> >
> >
> >
> >.
> >|||In addition to Geoff's remarks: DBCC INDEXDEFRAG will skip locked rows.
So if your database has much locking activity during defragmentation,
the DBCC command will be less effective.
Gert-Jan|||And in addition to that Indexdefrag only works on 1 file at a time. This
means extent fragmentation is useless on multiple files.
--
Andrew J. Kelly SQL MVP
"Gert-Jan Strik" <sorry@.toomuchspamalready.nl> wrote in message
news:4016C868.AD86E998@.toomuchspamalready.nl...
> In addition to Geoff's remarks: DBCC INDEXDEFRAG will skip locked rows.
> So if your database has much locking activity during defragmentation,
> the DBCC command will be less effective.
> Gert-Jan|||And in addition to that <g>, scan density is useless in the first place if > 1 data file. Chris,
look at Logical Scan Fragmentation if you have > 1 data file.
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Andrew J. Kelly" <sqlmvpnoooospam@.shadhawk.com> wrote in message
news:e8DQroU5DHA.360@.TK2MSFTNGP12.phx.gbl...
> And in addition to that Indexdefrag only works on 1 file at a time. This
> means extent fragmentation is useless on multiple files.
> --
> Andrew J. Kelly SQL MVP
>
> "Gert-Jan Strik" <sorry@.toomuchspamalready.nl> wrote in message
> news:4016C868.AD86E998@.toomuchspamalready.nl...
> > In addition to Geoff's remarks: DBCC INDEXDEFRAG will skip locked rows.
> > So if your database has much locking activity during defragmentation,
> > the DBCC command will be less effective.
> >
> > Gert-Jan
>|||BTW, all of this information is documented very clearly in BOL for DBCC
SHOWCONTIG and DBCC INDEXDFRAG. You can also read the whitepaper nelow for
more details:
http://www.microsoft.com/technet/treeview/default.asp?url=/technet/prodtechnol/sql/maintain/optimize/ss2kidbp.asp
Regards
--
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:OYWte4Y5DHA.2412@.TK2MSFTNGP11.phx.gbl...
> And in addition to that <g>, scan density is useless in the first place if
> 1 data file. Chris,
> look at Logical Scan Fragmentation if you have > 1 data file.
> --
> Tibor Karaszi, SQL Server MVP
> Archive at:
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
>
> "Andrew J. Kelly" <sqlmvpnoooospam@.shadhawk.com> wrote in message
> news:e8DQroU5DHA.360@.TK2MSFTNGP12.phx.gbl...
> > And in addition to that Indexdefrag only works on 1 file at a time.
This
> > means extent fragmentation is useless on multiple files.
> >
> > --
> > Andrew J. Kelly SQL MVP
> >
> >
> > "Gert-Jan Strik" <sorry@.toomuchspamalready.nl> wrote in message
> > news:4016C868.AD86E998@.toomuchspamalready.nl...
> > > In addition to Geoff's remarks: DBCC INDEXDEFRAG will skip locked
rows.
> > > So if your database has much locking activity during defragmentation,
> > > the DBCC command will be less effective.
> > >
> > > Gert-Jan
> >
> >
>
dbcc indexdefrag not doing anything
sql2k sp3
I keep running dbcc indexdefrag on a small(236,492 rows)
table and it doesnt change the output of the Scan Density
(best count:actual count) from dbcc showcontig at all.
Any ideas?
TIA, ChrisDoes your table have a clustered index?
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
"chris" <anonymous@.discussions.microsoft.com> wrote in message
news:556301c3e508$94bae040$a501280a@.phx.gbl...
message
allocations. Therefore the physical page/extent fragmentation will remain,
even though the rows are now in physical order on the pages. One of the
hidden limitations of INDEXDEFRAG vs. DBREINDEX.
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
"chris" <anonymous@.discussions.microsoft.com> wrote in message
news:583c01c3e50b$62b95560$a601280a@.phx.gbl...[QUOTE]
> Yes. Thats the index Im trying to defragment.
>
> message
> Density|||Just to clarify, this means that the table is actually de-
fraged, it just appears otherwise?
are no new
fragmentation will remain,
pages. One of the
message
altered so if the pages are scattered around the database, that will not
change.
--
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
"chris" <anonymous@.discussions.microsoft.com> wrote in message
news:557401c3e50e$bf0d2b40$a401280a@.phx.gbl...[QUOTE]
> Just to clarify, this means that the table is actually de-
> fraged, it just appears otherwise?
>
> are no new
> fragmentation will remain,
> pages. One of the
> message
> rows)
> all.|||Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit
Lines: 5
NNTP-Posting-Date: 27 Jan 2004 21:22:04 CET
NNTP-Posting-Host: 194.109.252.208
X-Trace: 1075234924 news.xs4all.nl 316 [::ffff:194.109.252.208]:2369
X-Complaints-To: abuse@.xs4all.nl
Path: TK2MSFTNGP08.phx.gbl!newsfeed00.sul.t-online.de!t-online.de!npeer.de.k
pn-eurorings.net!news.cambrium.nl!news.cambrium.nl!news.cambrium.nl!newsgate
.cistron.nl!transit.news.xs4all.nl!newsfeed.xs4all.nl!xs4all!not-for-mail
Xref: TK2MSFTNGP08.phx.gbl microsoft.public.sqlserver.server:327667
In addition to Geoff's remarks: DBCC INDEXDEFRAG will skip locked rows.
So if your database has much locking activity during defragmentation,
the DBCC command will be less effective.
Gert-Jan|||And in addition to that Indexdefrag only works on 1 file at a time. This
means extent fragmentation is useless on multiple files.
Andrew J. Kelly SQL MVP
"Gert-Jan Strik" <sorry@.toomuchspamalready.nl> wrote in message
news:4016C868.AD86E998@.toomuchspamalready.nl...
1 data file. Chris,
look at Logical Scan Fragmentation if you have > 1 data file.
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=...ls
erver
"Andrew J. Kelly" <sqlmvpnoooospam@.shadhawk.com> wrote in message
news:e8DQroU5DHA.360@.TK2MSFTNGP12.phx.gbl...
SHOWCONTIG and DBCC INDEXDFRAG. You can also read the whitepaper nelow for
more details:
http://www.microsoft.com/technet/tr...ze/ss2kidbp.asp
Regards
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:OYWte4Y5DHA.2412@.TK2MSFTNGP11.phx.gbl...
http://groups.google.com/groups?oi=...ublic.sqlserver
I keep running dbcc indexdefrag on a small(236,492 rows)
table and it doesnt change the output of the Scan Density
(best count:actual count) from dbcc showcontig at all.
Any ideas?
TIA, ChrisDoes your table have a clustered index?
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
"chris" <anonymous@.discussions.microsoft.com> wrote in message
news:556301c3e508$94bae040$a501280a@.phx.gbl...
quote:|||Yes. Thats the index Im trying to defragment.
> sql2k sp3
> I keep running dbcc indexdefrag on a small(236,492 rows)
> table and it doesnt change the output of the Scan Density
> (best count:actual count) from dbcc showcontig at all.
>
> Any ideas?
> TIA, Chris
>
quote:
>--Original Message--
>Does your table have a clustered index?
>--
>Geoff N. Hiten
>Microsoft SQL Server MVP
>Senior Database Administrator
>Careerbuilder.com
>"chris" <anonymous@.discussions.microsoft.com> wrote in
message
quote:|||Ok, DBCC IndexDefrag does an in-place defrag thus there are no new
>news:556301c3e508$94bae040$a501280a@.phx.gbl...
Density[QUOTE]
>
>.
>
allocations. Therefore the physical page/extent fragmentation will remain,
even though the rows are now in physical order on the pages. One of the
hidden limitations of INDEXDEFRAG vs. DBREINDEX.
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
"chris" <anonymous@.discussions.microsoft.com> wrote in message
news:583c01c3e50b$62b95560$a601280a@.phx.gbl...[QUOTE]
> Yes. Thats the index Im trying to defragment.
>
> message
> Density|||Just to clarify, this means that the table is actually de-
fraged, it just appears otherwise?
quote:
>--Original Message--
>Ok, DBCC IndexDefrag does an in-place defrag thus there
are no new
quote:
>allocations. Therefore the physical page/extent
fragmentation will remain,
quote:
>even though the rows are now in physical order on the
pages. One of the
quote:
>hidden limitations of INDEXDEFRAG vs. DBREINDEX.
>--
>Geoff N. Hiten
>Microsoft SQL Server MVP
>Senior Database Administrator
>Careerbuilder.com
>"chris" <anonymous@.discussions.microsoft.com> wrote in
message
quote:|||The index rows are in physical order on each page. Page allocations are not
>news:583c01c3e50b$62b95560$a601280a@.phx.gbl...
rows)[QUOTE]
all.[QUOTE]
>
>.
>
altered so if the pages are scattered around the database, that will not
change.
--
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
"chris" <anonymous@.discussions.microsoft.com> wrote in message
news:557401c3e50e$bf0d2b40$a401280a@.phx.gbl...[QUOTE]
> Just to clarify, this means that the table is actually de-
> fraged, it just appears otherwise?
>
> are no new
> fragmentation will remain,
> pages. One of the
> message
> rows)
> all.|||Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit
Lines: 5
NNTP-Posting-Date: 27 Jan 2004 21:22:04 CET
NNTP-Posting-Host: 194.109.252.208
X-Trace: 1075234924 news.xs4all.nl 316 [::ffff:194.109.252.208]:2369
X-Complaints-To: abuse@.xs4all.nl
Path: TK2MSFTNGP08.phx.gbl!newsfeed00.sul.t-online.de!t-online.de!npeer.de.k
pn-eurorings.net!news.cambrium.nl!news.cambrium.nl!news.cambrium.nl!newsgate
.cistron.nl!transit.news.xs4all.nl!newsfeed.xs4all.nl!xs4all!not-for-mail
Xref: TK2MSFTNGP08.phx.gbl microsoft.public.sqlserver.server:327667
In addition to Geoff's remarks: DBCC INDEXDEFRAG will skip locked rows.
So if your database has much locking activity during defragmentation,
the DBCC command will be less effective.
Gert-Jan|||And in addition to that Indexdefrag only works on 1 file at a time. This
means extent fragmentation is useless on multiple files.
Andrew J. Kelly SQL MVP
"Gert-Jan Strik" <sorry@.toomuchspamalready.nl> wrote in message
news:4016C868.AD86E998@.toomuchspamalready.nl...
quote:|||And in addition to that <g>, scan density is useless in the first place if >
> In addition to Geoff's remarks: DBCC INDEXDEFRAG will skip locked rows.
> So if your database has much locking activity during defragmentation,
> the DBCC command will be less effective.
> Gert-Jan
1 data file. Chris,
look at Logical Scan Fragmentation if you have > 1 data file.
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=...ls
erver
"Andrew J. Kelly" <sqlmvpnoooospam@.shadhawk.com> wrote in message
news:e8DQroU5DHA.360@.TK2MSFTNGP12.phx.gbl...
quote:|||BTW, all of this information is documented very clearly in BOL for DBCC
> And in addition to that Indexdefrag only works on 1 file at a time. This
> means extent fragmentation is useless on multiple files.
> --
> Andrew J. Kelly SQL MVP
>
> "Gert-Jan Strik" <sorry@.toomuchspamalready.nl> wrote in message
> news:4016C868.AD86E998@.toomuchspamalready.nl...
>
SHOWCONTIG and DBCC INDEXDFRAG. You can also read the whitepaper nelow for
more details:
http://www.microsoft.com/technet/tr...ze/ss2kidbp.asp
Regards
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:OYWte4Y5DHA.2412@.TK2MSFTNGP11.phx.gbl...
quote:
> And in addition to that <g>, scan density is useless in the first place if
> 1 data file. Chris,
> look at Logical Scan Fragmentation if you have > 1 data file.
> --
> Tibor Karaszi, SQL Server MVP
> Archive at:
http://groups.google.com/groups?oi=...ublic.sqlserver
quote:
>
> "Andrew J. Kelly" <sqlmvpnoooospam@.shadhawk.com> wrote in message
> news:e8DQroU5DHA.360@.TK2MSFTNGP12.phx.gbl...
This[QUOTE]
rows.[QUOTE]
>
DBCC INDEXDEFRAG fills the log
Hi:
Does anyone know any alternatives on how addressing the log filling up issue
when DBCC INDEXDEFRAG is done on large tables. The two ways this can be
addressed i see is. First way is:
1). Turn the recovery mode of the database from Full to simple.
2). Perform DBCC INDEXDEFRAG.
3). Turn the recovery mode of the database to Full again.
The second way is to backup the log on a regular basis (like every 30 mts)
and shrink the file when the reindexing is carried out. Other than these two
i dont seem to come up with a alternative good idea.
MVPs -- in your experience anything that you have done which was like an out
of box solution to address a problem please let me know. Your suggestions are
highly appreciated.
Thanks
Turning the recovery model from FULL to Simple will not reduce the amount of
data logged for INDEXDEFRAG. Unlike DBREINDEX or CREATE INDEX this is
always a fully logged mode regardless of the recovery model. Doing regular
or extra log backups while you are defragging is usually the best way. I
don't know which file you are referring to when you suggest to shrink but
you should not shrink any of them. Always leave plenty of free space in the
data and log files. If it grows that is an indication it wasn't big enough
to begin with.
http://www.karaszi.com/SQLServer/info_dont_shrink.asp
http://www.microsoft.com/technet/pro.../ss2kidbp.mspx
Andrew J. Kelly SQL MVP
"Meher" <Meher@.discussions.microsoft.com> wrote in message
news:8213F737-56C8-4F89-8E37-BAFF05AD878A@.microsoft.com...
> Hi:
> Does anyone know any alternatives on how addressing the log filling up
> issue
> when DBCC INDEXDEFRAG is done on large tables. The two ways this can be
> addressed i see is. First way is:
> 1). Turn the recovery mode of the database from Full to simple.
> 2). Perform DBCC INDEXDEFRAG.
> 3). Turn the recovery mode of the database to Full again.
> The second way is to backup the log on a regular basis (like every 30 mts)
> and shrink the file when the reindexing is carried out. Other than these
> two
> i dont seem to come up with a alternative good idea.
> MVPs -- in your experience anything that you have done which was like an
> out
> of box solution to address a problem please let me know. Your suggestions
> are
> highly appreciated.
> Thanks
Does anyone know any alternatives on how addressing the log filling up issue
when DBCC INDEXDEFRAG is done on large tables. The two ways this can be
addressed i see is. First way is:
1). Turn the recovery mode of the database from Full to simple.
2). Perform DBCC INDEXDEFRAG.
3). Turn the recovery mode of the database to Full again.
The second way is to backup the log on a regular basis (like every 30 mts)
and shrink the file when the reindexing is carried out. Other than these two
i dont seem to come up with a alternative good idea.
MVPs -- in your experience anything that you have done which was like an out
of box solution to address a problem please let me know. Your suggestions are
highly appreciated.
Thanks
Turning the recovery model from FULL to Simple will not reduce the amount of
data logged for INDEXDEFRAG. Unlike DBREINDEX or CREATE INDEX this is
always a fully logged mode regardless of the recovery model. Doing regular
or extra log backups while you are defragging is usually the best way. I
don't know which file you are referring to when you suggest to shrink but
you should not shrink any of them. Always leave plenty of free space in the
data and log files. If it grows that is an indication it wasn't big enough
to begin with.
http://www.karaszi.com/SQLServer/info_dont_shrink.asp
http://www.microsoft.com/technet/pro.../ss2kidbp.mspx
Andrew J. Kelly SQL MVP
"Meher" <Meher@.discussions.microsoft.com> wrote in message
news:8213F737-56C8-4F89-8E37-BAFF05AD878A@.microsoft.com...
> Hi:
> Does anyone know any alternatives on how addressing the log filling up
> issue
> when DBCC INDEXDEFRAG is done on large tables. The two ways this can be
> addressed i see is. First way is:
> 1). Turn the recovery mode of the database from Full to simple.
> 2). Perform DBCC INDEXDEFRAG.
> 3). Turn the recovery mode of the database to Full again.
> The second way is to backup the log on a regular basis (like every 30 mts)
> and shrink the file when the reindexing is carried out. Other than these
> two
> i dont seem to come up with a alternative good idea.
> MVPs -- in your experience anything that you have done which was like an
> out
> of box solution to address a problem please let me know. Your suggestions
> are
> highly appreciated.
> Thanks
DBCC INDEXDEFRAG fills the log
Hi:
Does anyone know any alternatives on how addressing the log filling up issue
when DBCC INDEXDEFRAG is done on large tables. The two ways this can be
addressed i see is. First way is:
1). Turn the recovery mode of the database from Full to simple.
2). Perform DBCC INDEXDEFRAG.
3). Turn the recovery mode of the database to Full again.
The second way is to backup the log on a regular basis (like every 30 mts)
and shrink the file when the reindexing is carried out. Other than these two
i dont seem to come up with a alternative good idea.
MVPs -- in your experience anything that you have done which was like an out
of box solution to address a problem please let me know. Your suggestions ar
e
highly appreciated.
ThanksTurning the recovery model from FULL to Simple will not reduce the amount of
data logged for INDEXDEFRAG. Unlike DBREINDEX or CREATE INDEX this is
always a fully logged mode regardless of the recovery model. Doing regular
or extra log backups while you are defragging is usually the best way. I
don't know which file you are referring to when you suggest to shrink but
you should not shrink any of them. Always leave plenty of free space in the
data and log files. If it grows that is an indication it wasn't big enough
to begin with.
http://www.karaszi.com/SQLServer/info_dont_shrink.asp
http://www.microsoft.com/technet/pr...n/ss2kidbp.mspx
--
Andrew J. Kelly SQL MVP
"Meher" <Meher@.discussions.microsoft.com> wrote in message
news:8213F737-56C8-4F89-8E37-BAFF05AD878A@.microsoft.com...
> Hi:
> Does anyone know any alternatives on how addressing the log filling up
> issue
> when DBCC INDEXDEFRAG is done on large tables. The two ways this can be
> addressed i see is. First way is:
> 1). Turn the recovery mode of the database from Full to simple.
> 2). Perform DBCC INDEXDEFRAG.
> 3). Turn the recovery mode of the database to Full again.
> The second way is to backup the log on a regular basis (like every 30 mts)
> and shrink the file when the reindexing is carried out. Other than these
> two
> i dont seem to come up with a alternative good idea.
> MVPs -- in your experience anything that you have done which was like an
> out
> of box solution to address a problem please let me know. Your suggestions
> are
> highly appreciated.
> Thanks
Does anyone know any alternatives on how addressing the log filling up issue
when DBCC INDEXDEFRAG is done on large tables. The two ways this can be
addressed i see is. First way is:
1). Turn the recovery mode of the database from Full to simple.
2). Perform DBCC INDEXDEFRAG.
3). Turn the recovery mode of the database to Full again.
The second way is to backup the log on a regular basis (like every 30 mts)
and shrink the file when the reindexing is carried out. Other than these two
i dont seem to come up with a alternative good idea.
MVPs -- in your experience anything that you have done which was like an out
of box solution to address a problem please let me know. Your suggestions ar
e
highly appreciated.
ThanksTurning the recovery model from FULL to Simple will not reduce the amount of
data logged for INDEXDEFRAG. Unlike DBREINDEX or CREATE INDEX this is
always a fully logged mode regardless of the recovery model. Doing regular
or extra log backups while you are defragging is usually the best way. I
don't know which file you are referring to when you suggest to shrink but
you should not shrink any of them. Always leave plenty of free space in the
data and log files. If it grows that is an indication it wasn't big enough
to begin with.
http://www.karaszi.com/SQLServer/info_dont_shrink.asp
http://www.microsoft.com/technet/pr...n/ss2kidbp.mspx
--
Andrew J. Kelly SQL MVP
"Meher" <Meher@.discussions.microsoft.com> wrote in message
news:8213F737-56C8-4F89-8E37-BAFF05AD878A@.microsoft.com...
> Hi:
> Does anyone know any alternatives on how addressing the log filling up
> issue
> when DBCC INDEXDEFRAG is done on large tables. The two ways this can be
> addressed i see is. First way is:
> 1). Turn the recovery mode of the database from Full to simple.
> 2). Perform DBCC INDEXDEFRAG.
> 3). Turn the recovery mode of the database to Full again.
> The second way is to backup the log on a regular basis (like every 30 mts)
> and shrink the file when the reindexing is carried out. Other than these
> two
> i dont seem to come up with a alternative good idea.
> MVPs -- in your experience anything that you have done which was like an
> out
> of box solution to address a problem please let me know. Your suggestions
> are
> highly appreciated.
> Thanks
DBCC INDEXDEFRAG fills the log
Hi:
Does anyone know any alternatives on how addressing the log filling up issue
when DBCC INDEXDEFRAG is done on large tables. The two ways this can be
addressed i see is. First way is:
1). Turn the recovery mode of the database from Full to simple.
2). Perform DBCC INDEXDEFRAG.
3). Turn the recovery mode of the database to Full again.
The second way is to backup the log on a regular basis (like every 30 mts)
and shrink the file when the reindexing is carried out. Other than these two
i dont seem to come up with a alternative good idea.
MVPs -- in your experience anything that you have done which was like an out
of box solution to address a problem please let me know. Your suggestions are
highly appreciated.
ThanksTurning the recovery model from FULL to Simple will not reduce the amount of
data logged for INDEXDEFRAG. Unlike DBREINDEX or CREATE INDEX this is
always a fully logged mode regardless of the recovery model. Doing regular
or extra log backups while you are defragging is usually the best way. I
don't know which file you are referring to when you suggest to shrink but
you should not shrink any of them. Always leave plenty of free space in the
data and log files. If it grows that is an indication it wasn't big enough
to begin with.
http://www.karaszi.com/SQLServer/info_dont_shrink.asp
http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ss2kidbp.mspx
--
Andrew J. Kelly SQL MVP
"Meher" <Meher@.discussions.microsoft.com> wrote in message
news:8213F737-56C8-4F89-8E37-BAFF05AD878A@.microsoft.com...
> Hi:
> Does anyone know any alternatives on how addressing the log filling up
> issue
> when DBCC INDEXDEFRAG is done on large tables. The two ways this can be
> addressed i see is. First way is:
> 1). Turn the recovery mode of the database from Full to simple.
> 2). Perform DBCC INDEXDEFRAG.
> 3). Turn the recovery mode of the database to Full again.
> The second way is to backup the log on a regular basis (like every 30 mts)
> and shrink the file when the reindexing is carried out. Other than these
> two
> i dont seem to come up with a alternative good idea.
> MVPs -- in your experience anything that you have done which was like an
> out
> of box solution to address a problem please let me know. Your suggestions
> are
> highly appreciated.
> Thanks
Does anyone know any alternatives on how addressing the log filling up issue
when DBCC INDEXDEFRAG is done on large tables. The two ways this can be
addressed i see is. First way is:
1). Turn the recovery mode of the database from Full to simple.
2). Perform DBCC INDEXDEFRAG.
3). Turn the recovery mode of the database to Full again.
The second way is to backup the log on a regular basis (like every 30 mts)
and shrink the file when the reindexing is carried out. Other than these two
i dont seem to come up with a alternative good idea.
MVPs -- in your experience anything that you have done which was like an out
of box solution to address a problem please let me know. Your suggestions are
highly appreciated.
ThanksTurning the recovery model from FULL to Simple will not reduce the amount of
data logged for INDEXDEFRAG. Unlike DBREINDEX or CREATE INDEX this is
always a fully logged mode regardless of the recovery model. Doing regular
or extra log backups while you are defragging is usually the best way. I
don't know which file you are referring to when you suggest to shrink but
you should not shrink any of them. Always leave plenty of free space in the
data and log files. If it grows that is an indication it wasn't big enough
to begin with.
http://www.karaszi.com/SQLServer/info_dont_shrink.asp
http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ss2kidbp.mspx
--
Andrew J. Kelly SQL MVP
"Meher" <Meher@.discussions.microsoft.com> wrote in message
news:8213F737-56C8-4F89-8E37-BAFF05AD878A@.microsoft.com...
> Hi:
> Does anyone know any alternatives on how addressing the log filling up
> issue
> when DBCC INDEXDEFRAG is done on large tables. The two ways this can be
> addressed i see is. First way is:
> 1). Turn the recovery mode of the database from Full to simple.
> 2). Perform DBCC INDEXDEFRAG.
> 3). Turn the recovery mode of the database to Full again.
> The second way is to backup the log on a regular basis (like every 30 mts)
> and shrink the file when the reindexing is carried out. Other than these
> two
> i dont seem to come up with a alternative good idea.
> MVPs -- in your experience anything that you have done which was like an
> out
> of box solution to address a problem please let me know. Your suggestions
> are
> highly appreciated.
> Thanks
DBCC INDEXDEFRAG causing blocking
I am using DBCC INDEXDEFRAG to defragment indexes. According to BOL
DBCC INDEXDEFRAG "does not hold locks long term and thus will not
block running queries or updates." But several SPIDs are blocked by
the INDEXDEFRAG SPID. The blocked SPIDs have a waitresource of TAB:
5:436352769 [] (436352769 is the table whose index I am defragmenting)
and waittype of LCK_M_IS. These SPIDs are being blocked for a long
time (30+ minutes).
Any help would be appreciated.
This is on SQL Server 2000, sp4
Thanks!Known 'feature', with hotfix available.
http://support.microsoft.com/kb/907250
--
TheSQLGuru
President
Indicium Resources, Inc.
<sfglossolalia@.gmail.com> wrote in message
news:1179179563.268238.322690@.u30g2000hsc.googlegroups.com...
>I am using DBCC INDEXDEFRAG to defragment indexes. According to BOL
> DBCC INDEXDEFRAG "does not hold locks long term and thus will not
> block running queries or updates." But several SPIDs are blocked by
> the INDEXDEFRAG SPID. The blocked SPIDs have a waitresource of TAB:
> 5:436352769 [] (436352769 is the table whose index I am defragmenting)
> and waittype of LCK_M_IS. These SPIDs are being blocked for a long
> time (30+ minutes).
> Any help would be appreciated.
> This is on SQL Server 2000, sp4
> Thanks!
>
DBCC INDEXDEFRAG "does not hold locks long term and thus will not
block running queries or updates." But several SPIDs are blocked by
the INDEXDEFRAG SPID. The blocked SPIDs have a waitresource of TAB:
5:436352769 [] (436352769 is the table whose index I am defragmenting)
and waittype of LCK_M_IS. These SPIDs are being blocked for a long
time (30+ minutes).
Any help would be appreciated.
This is on SQL Server 2000, sp4
Thanks!Known 'feature', with hotfix available.
http://support.microsoft.com/kb/907250
--
TheSQLGuru
President
Indicium Resources, Inc.
<sfglossolalia@.gmail.com> wrote in message
news:1179179563.268238.322690@.u30g2000hsc.googlegroups.com...
>I am using DBCC INDEXDEFRAG to defragment indexes. According to BOL
> DBCC INDEXDEFRAG "does not hold locks long term and thus will not
> block running queries or updates." But several SPIDs are blocked by
> the INDEXDEFRAG SPID. The blocked SPIDs have a waitresource of TAB:
> 5:436352769 [] (436352769 is the table whose index I am defragmenting)
> and waittype of LCK_M_IS. These SPIDs are being blocked for a long
> time (30+ minutes).
> Any help would be appreciated.
> This is on SQL Server 2000, sp4
> Thanks!
>
DBCC INDEXDEFRAG causing blocking
I am using DBCC INDEXDEFRAG to defragment indexes. According to BOL
DBCC INDEXDEFRAG "does not hold locks long term and thus will not
block running queries or updates." But several SPIDs are blocked by
the INDEXDEFRAG SPID. The blocked SPIDs have a waitresource of TAB:
5:436352769 [] (436352769 is the table whose index I am defragmenting)
and waittype of LCK_M_IS. These SPIDs are being blocked for a long
time (30+ minutes).
Any help would be appreciated.
This is on SQL Server 2000, sp4
Thanks!Known 'feature', with hotfix available.
http://support.microsoft.com/kb/907250
TheSQLGuru
President
Indicium Resources, Inc.
<sfglossolalia@.gmail.com> wrote in message
news:1179179563.268238.322690@.u30g2000hsc.googlegroups.com...
>I am using DBCC INDEXDEFRAG to defragment indexes. According to BOL
> DBCC INDEXDEFRAG "does not hold locks long term and thus will not
> block running queries or updates." But several SPIDs are blocked by
> the INDEXDEFRAG SPID. The blocked SPIDs have a waitresource of TAB:
> 5:436352769 [] (436352769 is the table whose index I am defragmenting)
> and waittype of LCK_M_IS. These SPIDs are being blocked for a long
> time (30+ minutes).
> Any help would be appreciated.
> This is on SQL Server 2000, sp4
> Thanks!
>
DBCC INDEXDEFRAG "does not hold locks long term and thus will not
block running queries or updates." But several SPIDs are blocked by
the INDEXDEFRAG SPID. The blocked SPIDs have a waitresource of TAB:
5:436352769 [] (436352769 is the table whose index I am defragmenting)
and waittype of LCK_M_IS. These SPIDs are being blocked for a long
time (30+ minutes).
Any help would be appreciated.
This is on SQL Server 2000, sp4
Thanks!Known 'feature', with hotfix available.
http://support.microsoft.com/kb/907250
TheSQLGuru
President
Indicium Resources, Inc.
<sfglossolalia@.gmail.com> wrote in message
news:1179179563.268238.322690@.u30g2000hsc.googlegroups.com...
>I am using DBCC INDEXDEFRAG to defragment indexes. According to BOL
> DBCC INDEXDEFRAG "does not hold locks long term and thus will not
> block running queries or updates." But several SPIDs are blocked by
> the INDEXDEFRAG SPID. The blocked SPIDs have a waitresource of TAB:
> 5:436352769 [] (436352769 is the table whose index I am defragmenting)
> and waittype of LCK_M_IS. These SPIDs are being blocked for a long
> time (30+ minutes).
> Any help would be appreciated.
> This is on SQL Server 2000, sp4
> Thanks!
>
DBCC INDEXDEFRAG aquires exclusive locks on a table
Hello!
I have a scheduled jobs that runs DBCC INDEXDEFRAG on a regular basis. I
have noticed that when indexing one of the non-clustered indexes on
particular table, process acquired exclusive(X) lock on the entire table
thus blocking any SELECTs etc.
When I was running my sample tests, I was observing IX lock on table in
question. I am not sure why exclusive lock was acquired by SQL Server job.
I was wondering if anybody experienced similar behavior.
Thanks,
Igorimarchenko wrote:
> Hello!
> I have a scheduled jobs that runs DBCC INDEXDEFRAG on a regular
> basis. I have noticed that when indexing one of the non-clustered
> indexes on particular table, process acquired exclusive(X) lock on
> the entire table thus blocking any SELECTs etc.
> When I was running my sample tests, I was observing IX lock on
> table in question. I am not sure why exclusive lock was acquired by
> SQL Server job. I was wondering if anybody experienced similar
> behavior.
> Thanks,
> Igor
IX is just an Intent lock. Form BOL: "Indicates the intention of a
transaction to modify some (but not all) resources lower in the
hierarchy by placing X locks on those individual resources. IX is a
superset of IS." Indexing places exclusive locks on a table, whereas
indexdefrag does not. Am I understanding your question correctly?
--
David Gugick
Quest Software
www.imceda.com
www.quest.com|||David,
Sorry if I didn't make myself clear.
The problem is that IX lock is eventually being escalated into X lock. I
was under impression that DBCC INDEXDEFRAG issues series of short
transactions and never places X lock on entire table. Any thoughts are
greatly appreciated.
Igor
"David Gugick" <david.gugick-nospam@.quest.com> wrote in message
news:%23OdFlDc1FHA.2072@.TK2MSFTNGP14.phx.gbl...
> imarchenko wrote:
>> Hello!
>> I have a scheduled jobs that runs DBCC INDEXDEFRAG on a regular
>> basis. I have noticed that when indexing one of the non-clustered
>> indexes on particular table, process acquired exclusive(X) lock on
>> the entire table thus blocking any SELECTs etc.
>> When I was running my sample tests, I was observing IX lock on
>> table in question. I am not sure why exclusive lock was acquired by
>> SQL Server job. I was wondering if anybody experienced similar
>> behavior.
>> Thanks,
>> Igor
> IX is just an Intent lock. Form BOL: "Indicates the intention of a
> transaction to modify some (but not all) resources lower in the hierarchy
> by placing X locks on those individual resources. IX is a superset of IS."
> Indexing places exclusive locks on a table, whereas indexdefrag does not.
> Am I understanding your question correctly?
> --
> David Gugick
> Quest Software
> www.imceda.com
> www.quest.com|||imarchenko wrote:
> David,
> Sorry if I didn't make myself clear.
> The problem is that IX lock is eventually being escalated into X
> lock. I was under impression that DBCC INDEXDEFRAG issues series of
> short transactions and never places X lock on entire table. Any
> thoughts are greatly appreciated.
My understanding is that DBCC INDEXDEFRAG is an online operation whereas
CREATE/ALTER INDEX is an offline operation. Despite INDEXDEFRAG using
short transactions to make its changes, those changes could require
varying levels of locks on the underlying table.
Maybe someone else can offer additional information on lock escalation
with the command.
David Gugick
Quest Software
www.imceda.com
www.quest.com|||Thanks, David. It appears that DBCC DBREINDEX acquires Sch-M (Schema
modification) lock that is incompatible with any other locks whereas DBCC
INDEXDEFRAG starts with X lock on row/page level (IX on table level) that is
being escalated into X table lock under certain circumstances (I suspect it
is table size/defragmantation level related).
Igor
"David Gugick" <david.gugick-nospam@.quest.com> wrote in message
news:%23P9Zwtc1FHA.1032@.TK2MSFTNGP12.phx.gbl...
> imarchenko wrote:
>> David,
>> Sorry if I didn't make myself clear.
>> The problem is that IX lock is eventually being escalated into X
>> lock. I was under impression that DBCC INDEXDEFRAG issues series of
>> short transactions and never places X lock on entire table. Any
>> thoughts are greatly appreciated.
> My understanding is that DBCC INDEXDEFRAG is an online operation whereas
> CREATE/ALTER INDEX is an offline operation. Despite INDEXDEFRAG using
> short transactions to make its changes, those changes could require
> varying levels of locks on the underlying table.
> Maybe someone else can offer additional information on lock escalation
> with the command.
>
> --
> David Gugick
> Quest Software
> www.imceda.com
> www.quest.com|||I haven't seen anywhere that INDEXDEFRAG should escalate X lock to table level. AFAIK, there should
only be IX lock at the table level. I'll ask around and will post back if I get any reply.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"imarchenko" <igormarchenko@.hotmail.com> wrote in message
news:%23IrtMJd1FHA.612@.TK2MSFTNGP10.phx.gbl...
> Thanks, David. It appears that DBCC DBREINDEX acquires Sch-M (Schema modification) lock that is
> incompatible with any other locks whereas DBCC INDEXDEFRAG starts with X lock on row/page level
> (IX on table level) that is being escalated into X table lock under certain circumstances (I
> suspect it is table size/defragmantation level related).
> Igor
> "David Gugick" <david.gugick-nospam@.quest.com> wrote in message
> news:%23P9Zwtc1FHA.1032@.TK2MSFTNGP12.phx.gbl...
>> imarchenko wrote:
>> David,
>> Sorry if I didn't make myself clear.
>> The problem is that IX lock is eventually being escalated into X
>> lock. I was under impression that DBCC INDEXDEFRAG issues series of
>> short transactions and never places X lock on entire table. Any
>> thoughts are greatly appreciated.
>> My understanding is that DBCC INDEXDEFRAG is an online operation whereas CREATE/ALTER INDEX is an
>> offline operation. Despite INDEXDEFRAG using short transactions to make its changes, those
>> changes could require varying levels of locks on the underlying table.
>> Maybe someone else can offer additional information on lock escalation with the command.
>>
>> --
>> David Gugick
>> Quest Software
>> www.imceda.com
>> www.quest.com
>|||Thanks a lor Tibor for looking into this. Please let me know if you will
need more details. Computer is running SQL Server 2000 SP4 on Windows 2003
EE.
Igor
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:%23n1GHog1FHA.2540@.TK2MSFTNGP09.phx.gbl...
>I haven't seen anywhere that INDEXDEFRAG should escalate X lock to table
>level. AFAIK, there should only be IX lock at the table level. I'll ask
>around and will post back if I get any reply.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "imarchenko" <igormarchenko@.hotmail.com> wrote in message
> news:%23IrtMJd1FHA.612@.TK2MSFTNGP10.phx.gbl...
>> Thanks, David. It appears that DBCC DBREINDEX acquires Sch-M (Schema
>> modification) lock that is incompatible with any other locks whereas DBCC
>> INDEXDEFRAG starts with X lock on row/page level (IX on table level) that
>> is being escalated into X table lock under certain circumstances (I
>> suspect it is table size/defragmantation level related).
>> Igor
>> "David Gugick" <david.gugick-nospam@.quest.com> wrote in message
>> news:%23P9Zwtc1FHA.1032@.TK2MSFTNGP12.phx.gbl...
>> imarchenko wrote:
>> David,
>> Sorry if I didn't make myself clear.
>> The problem is that IX lock is eventually being escalated into X
>> lock. I was under impression that DBCC INDEXDEFRAG issues series of
>> short transactions and never places X lock on entire table. Any
>> thoughts are greatly appreciated.
>> My understanding is that DBCC INDEXDEFRAG is an online operation whereas
>> CREATE/ALTER INDEX is an offline operation. Despite INDEXDEFRAG using
>> short transactions to make its changes, those changes could require
>> varying levels of locks on the underlying table.
>> Maybe someone else can offer additional information on lock escalation
>> with the command.
>>
>> --
>> David Gugick
>> Quest Software
>> www.imceda.com
>> www.quest.com
>>
>|||I got it confirmed that the escalation does occur, which is a bug. Here's a quote from my contact at
MS:
"Its a bug in the lock manager in SP4 that makes INDEXDEFRAG retain NL locks
and eventually escalate to a table lock. The KB article number is 907250 but
it hasn't been released yet. There is a hotfix available already."
So, I'd contact PSS on this, so you can get the hotfix if you find you need it. Or wait for the KB
to be released to you can read more details about it.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"imarchenko" <igormarchenko@.hotmail.com> wrote in message
news:%23pCxAdn1FHA.2924@.TK2MSFTNGP15.phx.gbl...
> Thanks a lor Tibor for looking into this. Please let me know if you will need more details.
> Computer is running SQL Server 2000 SP4 on Windows 2003 EE.
> Igor
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in message
> news:%23n1GHog1FHA.2540@.TK2MSFTNGP09.phx.gbl...
>>I haven't seen anywhere that INDEXDEFRAG should escalate X lock to table level. AFAIK, there
>>should only be IX lock at the table level. I'll ask around and will post back if I get any reply.
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>> Blog: http://solidqualitylearning.com/blogs/tibor/
>>
>> "imarchenko" <igormarchenko@.hotmail.com> wrote in message
>> news:%23IrtMJd1FHA.612@.TK2MSFTNGP10.phx.gbl...
>> Thanks, David. It appears that DBCC DBREINDEX acquires Sch-M (Schema modification) lock that
>> is incompatible with any other locks whereas DBCC INDEXDEFRAG starts with X lock on row/page
>> level (IX on table level) that is being escalated into X table lock under certain circumstances
>> (I suspect it is table size/defragmantation level related).
>> Igor
>> "David Gugick" <david.gugick-nospam@.quest.com> wrote in message
>> news:%23P9Zwtc1FHA.1032@.TK2MSFTNGP12.phx.gbl...
>> imarchenko wrote:
>> David,
>> Sorry if I didn't make myself clear.
>> The problem is that IX lock is eventually being escalated into X
>> lock. I was under impression that DBCC INDEXDEFRAG issues series of
>> short transactions and never places X lock on entire table. Any
>> thoughts are greatly appreciated.
>> My understanding is that DBCC INDEXDEFRAG is an online operation whereas CREATE/ALTER INDEX is
>> an offline operation. Despite INDEXDEFRAG using short transactions to make its changes, those
>> changes could require varying levels of locks on the underlying table.
>> Maybe someone else can offer additional information on lock escalation with the command.
>>
>> --
>> David Gugick
>> Quest Software
>> www.imceda.com
>> www.quest.com
>>
>|||There's a bug in SP4 in the lock manager that makes INDEXDEFRAG retain NL
locks on pages its moved - this eventually causes the next requested X page
lock to escalate to an X table lock.
A hotfix is available through PSS - it's not made it to the web yet. There
will also be a KB article but it hasn't made it out yet either. You should
be able to reference case SRX050805601805 with PSS and the fix will be
provided free of charge.
Thanks
--
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"imarchenko" <igormarchenko@.hotmail.com> wrote in message
news:%23pCxAdn1FHA.2924@.TK2MSFTNGP15.phx.gbl...
> Thanks a lor Tibor for looking into this. Please let me know if you will
> need more details. Computer is running SQL Server 2000 SP4 on Windows
> 2003 EE.
> Igor
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote
> in message news:%23n1GHog1FHA.2540@.TK2MSFTNGP09.phx.gbl...
>>I haven't seen anywhere that INDEXDEFRAG should escalate X lock to table
>>level. AFAIK, there should only be IX lock at the table level. I'll ask
>>around and will post back if I get any reply.
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>> Blog: http://solidqualitylearning.com/blogs/tibor/
>>
>> "imarchenko" <igormarchenko@.hotmail.com> wrote in message
>> news:%23IrtMJd1FHA.612@.TK2MSFTNGP10.phx.gbl...
>> Thanks, David. It appears that DBCC DBREINDEX acquires Sch-M (Schema
>> modification) lock that is incompatible with any other locks whereas
>> DBCC INDEXDEFRAG starts with X lock on row/page level (IX on table
>> level) that is being escalated into X table lock under certain
>> circumstances (I suspect it is table size/defragmantation level
>> related).
>> Igor
>> "David Gugick" <david.gugick-nospam@.quest.com> wrote in message
>> news:%23P9Zwtc1FHA.1032@.TK2MSFTNGP12.phx.gbl...
>> imarchenko wrote:
>> David,
>> Sorry if I didn't make myself clear.
>> The problem is that IX lock is eventually being escalated into X
>> lock. I was under impression that DBCC INDEXDEFRAG issues series of
>> short transactions and never places X lock on entire table. Any
>> thoughts are greatly appreciated.
>> My understanding is that DBCC INDEXDEFRAG is an online operation
>> whereas CREATE/ALTER INDEX is an offline operation. Despite INDEXDEFRAG
>> using short transactions to make its changes, those changes could
>> require varying levels of locks on the underlying table.
>> Maybe someone else can offer additional information on lock escalation
>> with the command.
>>
>> --
>> David Gugick
>> Quest Software
>> www.imceda.com
>> www.quest.com
>>
>|||Tibor,
Thanks a lot for finding the answer so promptly!
Igor
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:%23CXN4vo1FHA.2880@.TK2MSFTNGP12.phx.gbl...
>I got it confirmed that the escalation does occur, which is a bug. Here's a
>quote from my contact at MS:
> "Its a bug in the lock manager in SP4 that makes INDEXDEFRAG retain NL
> locks
> and eventually escalate to a table lock. The KB article number is 907250
> but
> it hasn't been released yet. There is a hotfix available already."
> So, I'd contact PSS on this, so you can get the hotfix if you find you
> need it. Or wait for the KB to be released to you can read more details
> about it.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "imarchenko" <igormarchenko@.hotmail.com> wrote in message
> news:%23pCxAdn1FHA.2924@.TK2MSFTNGP15.phx.gbl...
>> Thanks a lor Tibor for looking into this. Please let me know if you will
>> need more details. Computer is running SQL Server 2000 SP4 on Windows
>> 2003 EE.
>> Igor
>> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote
>> in message news:%23n1GHog1FHA.2540@.TK2MSFTNGP09.phx.gbl...
>>I haven't seen anywhere that INDEXDEFRAG should escalate X lock to table
>>level. AFAIK, there should only be IX lock at the table level. I'll ask
>>around and will post back if I get any reply.
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>> Blog: http://solidqualitylearning.com/blogs/tibor/
>>
>> "imarchenko" <igormarchenko@.hotmail.com> wrote in message
>> news:%23IrtMJd1FHA.612@.TK2MSFTNGP10.phx.gbl...
>> Thanks, David. It appears that DBCC DBREINDEX acquires Sch-M (Schema
>> modification) lock that is incompatible with any other locks whereas
>> DBCC INDEXDEFRAG starts with X lock on row/page level (IX on table
>> level) that is being escalated into X table lock under certain
>> circumstances (I suspect it is table size/defragmantation level
>> related).
>> Igor
>> "David Gugick" <david.gugick-nospam@.quest.com> wrote in message
>> news:%23P9Zwtc1FHA.1032@.TK2MSFTNGP12.phx.gbl...
>> imarchenko wrote:
>> David,
>> Sorry if I didn't make myself clear.
>> The problem is that IX lock is eventually being escalated into X
>> lock. I was under impression that DBCC INDEXDEFRAG issues series of
>> short transactions and never places X lock on entire table. Any
>> thoughts are greatly appreciated.
>> My understanding is that DBCC INDEXDEFRAG is an online operation
>> whereas CREATE/ALTER INDEX is an offline operation. Despite
>> INDEXDEFRAG using short transactions to make its changes, those
>> changes could require varying levels of locks on the underlying table.
>> Maybe someone else can offer additional information on lock escalation
>> with the command.
>>
>> --
>> David Gugick
>> Quest Software
>> www.imceda.com
>> www.quest.com
>>
>>
>|||Paul,
Thanks a lot!
Igor
"Paul S Randal [MS]" <prandal@.online.microsoft.com> wrote in message
news:uUJuIwo1FHA.1032@.TK2MSFTNGP12.phx.gbl...
> There's a bug in SP4 in the lock manager that makes INDEXDEFRAG retain NL
> locks on pages its moved - this eventually causes the next requested X
> page lock to escalate to an X table lock.
> A hotfix is available through PSS - it's not made it to the web yet. There
> will also be a KB article but it hasn't made it out yet either. You should
> be able to reference case SRX050805601805 with PSS and the fix will be
> provided free of charge.
> Thanks
> --
> Paul Randal
> Dev Lead, Microsoft SQL Server Storage Engine
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
> "imarchenko" <igormarchenko@.hotmail.com> wrote in message
> news:%23pCxAdn1FHA.2924@.TK2MSFTNGP15.phx.gbl...
>> Thanks a lor Tibor for looking into this. Please let me know if you will
>> need more details. Computer is running SQL Server 2000 SP4 on Windows
>> 2003 EE.
>> Igor
>> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote
>> in message news:%23n1GHog1FHA.2540@.TK2MSFTNGP09.phx.gbl...
>>I haven't seen anywhere that INDEXDEFRAG should escalate X lock to table
>>level. AFAIK, there should only be IX lock at the table level. I'll ask
>>around and will post back if I get any reply.
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>> Blog: http://solidqualitylearning.com/blogs/tibor/
>>
>> "imarchenko" <igormarchenko@.hotmail.com> wrote in message
>> news:%23IrtMJd1FHA.612@.TK2MSFTNGP10.phx.gbl...
>> Thanks, David. It appears that DBCC DBREINDEX acquires Sch-M (Schema
>> modification) lock that is incompatible with any other locks whereas
>> DBCC INDEXDEFRAG starts with X lock on row/page level (IX on table
>> level) that is being escalated into X table lock under certain
>> circumstances (I suspect it is table size/defragmantation level
>> related).
>> Igor
>> "David Gugick" <david.gugick-nospam@.quest.com> wrote in message
>> news:%23P9Zwtc1FHA.1032@.TK2MSFTNGP12.phx.gbl...
>> imarchenko wrote:
>> David,
>> Sorry if I didn't make myself clear.
>> The problem is that IX lock is eventually being escalated into X
>> lock. I was under impression that DBCC INDEXDEFRAG issues series of
>> short transactions and never places X lock on entire table. Any
>> thoughts are greatly appreciated.
>> My understanding is that DBCC INDEXDEFRAG is an online operation
>> whereas CREATE/ALTER INDEX is an offline operation. Despite
>> INDEXDEFRAG using short transactions to make its changes, those
>> changes could require varying levels of locks on the underlying table.
>> Maybe someone else can offer additional information on lock escalation
>> with the command.
>>
>> --
>> David Gugick
>> Quest Software
>> www.imceda.com
>> www.quest.com
>>
>>
>|||Paul,
Could you please explain what NL is?
Thanks,
Igor
"Paul S Randal [MS]" <prandal@.online.microsoft.com> wrote in message
news:uUJuIwo1FHA.1032@.TK2MSFTNGP12.phx.gbl...
> There's a bug in SP4 in the lock manager that makes INDEXDEFRAG retain NL
> locks on pages its moved - this eventually causes the next requested X
> page lock to escalate to an X table lock.
> A hotfix is available through PSS - it's not made it to the web yet. There
> will also be a KB article but it hasn't made it out yet either. You should
> be able to reference case SRX050805601805 with PSS and the fix will be
> provided free of charge.
> Thanks
> --
> Paul Randal
> Dev Lead, Microsoft SQL Server Storage Engine
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
> "imarchenko" <igormarchenko@.hotmail.com> wrote in message
> news:%23pCxAdn1FHA.2924@.TK2MSFTNGP15.phx.gbl...
>> Thanks a lor Tibor for looking into this. Please let me know if you will
>> need more details. Computer is running SQL Server 2000 SP4 on Windows
>> 2003 EE.
>> Igor
>> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote
>> in message news:%23n1GHog1FHA.2540@.TK2MSFTNGP09.phx.gbl...
>>I haven't seen anywhere that INDEXDEFRAG should escalate X lock to table
>>level. AFAIK, there should only be IX lock at the table level. I'll ask
>>around and will post back if I get any reply.
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>> Blog: http://solidqualitylearning.com/blogs/tibor/
>>
>> "imarchenko" <igormarchenko@.hotmail.com> wrote in message
>> news:%23IrtMJd1FHA.612@.TK2MSFTNGP10.phx.gbl...
>> Thanks, David. It appears that DBCC DBREINDEX acquires Sch-M (Schema
>> modification) lock that is incompatible with any other locks whereas
>> DBCC INDEXDEFRAG starts with X lock on row/page level (IX on table
>> level) that is being escalated into X table lock under certain
>> circumstances (I suspect it is table size/defragmantation level
>> related).
>> Igor
>> "David Gugick" <david.gugick-nospam@.quest.com> wrote in message
>> news:%23P9Zwtc1FHA.1032@.TK2MSFTNGP12.phx.gbl...
>> imarchenko wrote:
>> David,
>> Sorry if I didn't make myself clear.
>> The problem is that IX lock is eventually being escalated into X
>> lock. I was under impression that DBCC INDEXDEFRAG issues series of
>> short transactions and never places X lock on entire table. Any
>> thoughts are greatly appreciated.
>> My understanding is that DBCC INDEXDEFRAG is an online operation
>> whereas CREATE/ALTER INDEX is an offline operation. Despite
>> INDEXDEFRAG using short transactions to make its changes, those
>> changes could require varying levels of locks on the underlying table.
>> Maybe someone else can offer additional information on lock escalation
>> with the command.
>>
>> --
>> David Gugick
>> Quest Software
>> www.imceda.com
>> www.quest.com
>>
>>
>
I have a scheduled jobs that runs DBCC INDEXDEFRAG on a regular basis. I
have noticed that when indexing one of the non-clustered indexes on
particular table, process acquired exclusive(X) lock on the entire table
thus blocking any SELECTs etc.
When I was running my sample tests, I was observing IX lock on table in
question. I am not sure why exclusive lock was acquired by SQL Server job.
I was wondering if anybody experienced similar behavior.
Thanks,
Igorimarchenko wrote:
> Hello!
> I have a scheduled jobs that runs DBCC INDEXDEFRAG on a regular
> basis. I have noticed that when indexing one of the non-clustered
> indexes on particular table, process acquired exclusive(X) lock on
> the entire table thus blocking any SELECTs etc.
> When I was running my sample tests, I was observing IX lock on
> table in question. I am not sure why exclusive lock was acquired by
> SQL Server job. I was wondering if anybody experienced similar
> behavior.
> Thanks,
> Igor
IX is just an Intent lock. Form BOL: "Indicates the intention of a
transaction to modify some (but not all) resources lower in the
hierarchy by placing X locks on those individual resources. IX is a
superset of IS." Indexing places exclusive locks on a table, whereas
indexdefrag does not. Am I understanding your question correctly?
--
David Gugick
Quest Software
www.imceda.com
www.quest.com|||David,
Sorry if I didn't make myself clear.
The problem is that IX lock is eventually being escalated into X lock. I
was under impression that DBCC INDEXDEFRAG issues series of short
transactions and never places X lock on entire table. Any thoughts are
greatly appreciated.
Igor
"David Gugick" <david.gugick-nospam@.quest.com> wrote in message
news:%23OdFlDc1FHA.2072@.TK2MSFTNGP14.phx.gbl...
> imarchenko wrote:
>> Hello!
>> I have a scheduled jobs that runs DBCC INDEXDEFRAG on a regular
>> basis. I have noticed that when indexing one of the non-clustered
>> indexes on particular table, process acquired exclusive(X) lock on
>> the entire table thus blocking any SELECTs etc.
>> When I was running my sample tests, I was observing IX lock on
>> table in question. I am not sure why exclusive lock was acquired by
>> SQL Server job. I was wondering if anybody experienced similar
>> behavior.
>> Thanks,
>> Igor
> IX is just an Intent lock. Form BOL: "Indicates the intention of a
> transaction to modify some (but not all) resources lower in the hierarchy
> by placing X locks on those individual resources. IX is a superset of IS."
> Indexing places exclusive locks on a table, whereas indexdefrag does not.
> Am I understanding your question correctly?
> --
> David Gugick
> Quest Software
> www.imceda.com
> www.quest.com|||imarchenko wrote:
> David,
> Sorry if I didn't make myself clear.
> The problem is that IX lock is eventually being escalated into X
> lock. I was under impression that DBCC INDEXDEFRAG issues series of
> short transactions and never places X lock on entire table. Any
> thoughts are greatly appreciated.
My understanding is that DBCC INDEXDEFRAG is an online operation whereas
CREATE/ALTER INDEX is an offline operation. Despite INDEXDEFRAG using
short transactions to make its changes, those changes could require
varying levels of locks on the underlying table.
Maybe someone else can offer additional information on lock escalation
with the command.
David Gugick
Quest Software
www.imceda.com
www.quest.com|||Thanks, David. It appears that DBCC DBREINDEX acquires Sch-M (Schema
modification) lock that is incompatible with any other locks whereas DBCC
INDEXDEFRAG starts with X lock on row/page level (IX on table level) that is
being escalated into X table lock under certain circumstances (I suspect it
is table size/defragmantation level related).
Igor
"David Gugick" <david.gugick-nospam@.quest.com> wrote in message
news:%23P9Zwtc1FHA.1032@.TK2MSFTNGP12.phx.gbl...
> imarchenko wrote:
>> David,
>> Sorry if I didn't make myself clear.
>> The problem is that IX lock is eventually being escalated into X
>> lock. I was under impression that DBCC INDEXDEFRAG issues series of
>> short transactions and never places X lock on entire table. Any
>> thoughts are greatly appreciated.
> My understanding is that DBCC INDEXDEFRAG is an online operation whereas
> CREATE/ALTER INDEX is an offline operation. Despite INDEXDEFRAG using
> short transactions to make its changes, those changes could require
> varying levels of locks on the underlying table.
> Maybe someone else can offer additional information on lock escalation
> with the command.
>
> --
> David Gugick
> Quest Software
> www.imceda.com
> www.quest.com|||I haven't seen anywhere that INDEXDEFRAG should escalate X lock to table level. AFAIK, there should
only be IX lock at the table level. I'll ask around and will post back if I get any reply.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"imarchenko" <igormarchenko@.hotmail.com> wrote in message
news:%23IrtMJd1FHA.612@.TK2MSFTNGP10.phx.gbl...
> Thanks, David. It appears that DBCC DBREINDEX acquires Sch-M (Schema modification) lock that is
> incompatible with any other locks whereas DBCC INDEXDEFRAG starts with X lock on row/page level
> (IX on table level) that is being escalated into X table lock under certain circumstances (I
> suspect it is table size/defragmantation level related).
> Igor
> "David Gugick" <david.gugick-nospam@.quest.com> wrote in message
> news:%23P9Zwtc1FHA.1032@.TK2MSFTNGP12.phx.gbl...
>> imarchenko wrote:
>> David,
>> Sorry if I didn't make myself clear.
>> The problem is that IX lock is eventually being escalated into X
>> lock. I was under impression that DBCC INDEXDEFRAG issues series of
>> short transactions and never places X lock on entire table. Any
>> thoughts are greatly appreciated.
>> My understanding is that DBCC INDEXDEFRAG is an online operation whereas CREATE/ALTER INDEX is an
>> offline operation. Despite INDEXDEFRAG using short transactions to make its changes, those
>> changes could require varying levels of locks on the underlying table.
>> Maybe someone else can offer additional information on lock escalation with the command.
>>
>> --
>> David Gugick
>> Quest Software
>> www.imceda.com
>> www.quest.com
>|||Thanks a lor Tibor for looking into this. Please let me know if you will
need more details. Computer is running SQL Server 2000 SP4 on Windows 2003
EE.
Igor
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:%23n1GHog1FHA.2540@.TK2MSFTNGP09.phx.gbl...
>I haven't seen anywhere that INDEXDEFRAG should escalate X lock to table
>level. AFAIK, there should only be IX lock at the table level. I'll ask
>around and will post back if I get any reply.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "imarchenko" <igormarchenko@.hotmail.com> wrote in message
> news:%23IrtMJd1FHA.612@.TK2MSFTNGP10.phx.gbl...
>> Thanks, David. It appears that DBCC DBREINDEX acquires Sch-M (Schema
>> modification) lock that is incompatible with any other locks whereas DBCC
>> INDEXDEFRAG starts with X lock on row/page level (IX on table level) that
>> is being escalated into X table lock under certain circumstances (I
>> suspect it is table size/defragmantation level related).
>> Igor
>> "David Gugick" <david.gugick-nospam@.quest.com> wrote in message
>> news:%23P9Zwtc1FHA.1032@.TK2MSFTNGP12.phx.gbl...
>> imarchenko wrote:
>> David,
>> Sorry if I didn't make myself clear.
>> The problem is that IX lock is eventually being escalated into X
>> lock. I was under impression that DBCC INDEXDEFRAG issues series of
>> short transactions and never places X lock on entire table. Any
>> thoughts are greatly appreciated.
>> My understanding is that DBCC INDEXDEFRAG is an online operation whereas
>> CREATE/ALTER INDEX is an offline operation. Despite INDEXDEFRAG using
>> short transactions to make its changes, those changes could require
>> varying levels of locks on the underlying table.
>> Maybe someone else can offer additional information on lock escalation
>> with the command.
>>
>> --
>> David Gugick
>> Quest Software
>> www.imceda.com
>> www.quest.com
>>
>|||I got it confirmed that the escalation does occur, which is a bug. Here's a quote from my contact at
MS:
"Its a bug in the lock manager in SP4 that makes INDEXDEFRAG retain NL locks
and eventually escalate to a table lock. The KB article number is 907250 but
it hasn't been released yet. There is a hotfix available already."
So, I'd contact PSS on this, so you can get the hotfix if you find you need it. Or wait for the KB
to be released to you can read more details about it.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"imarchenko" <igormarchenko@.hotmail.com> wrote in message
news:%23pCxAdn1FHA.2924@.TK2MSFTNGP15.phx.gbl...
> Thanks a lor Tibor for looking into this. Please let me know if you will need more details.
> Computer is running SQL Server 2000 SP4 on Windows 2003 EE.
> Igor
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in message
> news:%23n1GHog1FHA.2540@.TK2MSFTNGP09.phx.gbl...
>>I haven't seen anywhere that INDEXDEFRAG should escalate X lock to table level. AFAIK, there
>>should only be IX lock at the table level. I'll ask around and will post back if I get any reply.
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>> Blog: http://solidqualitylearning.com/blogs/tibor/
>>
>> "imarchenko" <igormarchenko@.hotmail.com> wrote in message
>> news:%23IrtMJd1FHA.612@.TK2MSFTNGP10.phx.gbl...
>> Thanks, David. It appears that DBCC DBREINDEX acquires Sch-M (Schema modification) lock that
>> is incompatible with any other locks whereas DBCC INDEXDEFRAG starts with X lock on row/page
>> level (IX on table level) that is being escalated into X table lock under certain circumstances
>> (I suspect it is table size/defragmantation level related).
>> Igor
>> "David Gugick" <david.gugick-nospam@.quest.com> wrote in message
>> news:%23P9Zwtc1FHA.1032@.TK2MSFTNGP12.phx.gbl...
>> imarchenko wrote:
>> David,
>> Sorry if I didn't make myself clear.
>> The problem is that IX lock is eventually being escalated into X
>> lock. I was under impression that DBCC INDEXDEFRAG issues series of
>> short transactions and never places X lock on entire table. Any
>> thoughts are greatly appreciated.
>> My understanding is that DBCC INDEXDEFRAG is an online operation whereas CREATE/ALTER INDEX is
>> an offline operation. Despite INDEXDEFRAG using short transactions to make its changes, those
>> changes could require varying levels of locks on the underlying table.
>> Maybe someone else can offer additional information on lock escalation with the command.
>>
>> --
>> David Gugick
>> Quest Software
>> www.imceda.com
>> www.quest.com
>>
>|||There's a bug in SP4 in the lock manager that makes INDEXDEFRAG retain NL
locks on pages its moved - this eventually causes the next requested X page
lock to escalate to an X table lock.
A hotfix is available through PSS - it's not made it to the web yet. There
will also be a KB article but it hasn't made it out yet either. You should
be able to reference case SRX050805601805 with PSS and the fix will be
provided free of charge.
Thanks
--
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"imarchenko" <igormarchenko@.hotmail.com> wrote in message
news:%23pCxAdn1FHA.2924@.TK2MSFTNGP15.phx.gbl...
> Thanks a lor Tibor for looking into this. Please let me know if you will
> need more details. Computer is running SQL Server 2000 SP4 on Windows
> 2003 EE.
> Igor
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote
> in message news:%23n1GHog1FHA.2540@.TK2MSFTNGP09.phx.gbl...
>>I haven't seen anywhere that INDEXDEFRAG should escalate X lock to table
>>level. AFAIK, there should only be IX lock at the table level. I'll ask
>>around and will post back if I get any reply.
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>> Blog: http://solidqualitylearning.com/blogs/tibor/
>>
>> "imarchenko" <igormarchenko@.hotmail.com> wrote in message
>> news:%23IrtMJd1FHA.612@.TK2MSFTNGP10.phx.gbl...
>> Thanks, David. It appears that DBCC DBREINDEX acquires Sch-M (Schema
>> modification) lock that is incompatible with any other locks whereas
>> DBCC INDEXDEFRAG starts with X lock on row/page level (IX on table
>> level) that is being escalated into X table lock under certain
>> circumstances (I suspect it is table size/defragmantation level
>> related).
>> Igor
>> "David Gugick" <david.gugick-nospam@.quest.com> wrote in message
>> news:%23P9Zwtc1FHA.1032@.TK2MSFTNGP12.phx.gbl...
>> imarchenko wrote:
>> David,
>> Sorry if I didn't make myself clear.
>> The problem is that IX lock is eventually being escalated into X
>> lock. I was under impression that DBCC INDEXDEFRAG issues series of
>> short transactions and never places X lock on entire table. Any
>> thoughts are greatly appreciated.
>> My understanding is that DBCC INDEXDEFRAG is an online operation
>> whereas CREATE/ALTER INDEX is an offline operation. Despite INDEXDEFRAG
>> using short transactions to make its changes, those changes could
>> require varying levels of locks on the underlying table.
>> Maybe someone else can offer additional information on lock escalation
>> with the command.
>>
>> --
>> David Gugick
>> Quest Software
>> www.imceda.com
>> www.quest.com
>>
>|||Tibor,
Thanks a lot for finding the answer so promptly!
Igor
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:%23CXN4vo1FHA.2880@.TK2MSFTNGP12.phx.gbl...
>I got it confirmed that the escalation does occur, which is a bug. Here's a
>quote from my contact at MS:
> "Its a bug in the lock manager in SP4 that makes INDEXDEFRAG retain NL
> locks
> and eventually escalate to a table lock. The KB article number is 907250
> but
> it hasn't been released yet. There is a hotfix available already."
> So, I'd contact PSS on this, so you can get the hotfix if you find you
> need it. Or wait for the KB to be released to you can read more details
> about it.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "imarchenko" <igormarchenko@.hotmail.com> wrote in message
> news:%23pCxAdn1FHA.2924@.TK2MSFTNGP15.phx.gbl...
>> Thanks a lor Tibor for looking into this. Please let me know if you will
>> need more details. Computer is running SQL Server 2000 SP4 on Windows
>> 2003 EE.
>> Igor
>> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote
>> in message news:%23n1GHog1FHA.2540@.TK2MSFTNGP09.phx.gbl...
>>I haven't seen anywhere that INDEXDEFRAG should escalate X lock to table
>>level. AFAIK, there should only be IX lock at the table level. I'll ask
>>around and will post back if I get any reply.
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>> Blog: http://solidqualitylearning.com/blogs/tibor/
>>
>> "imarchenko" <igormarchenko@.hotmail.com> wrote in message
>> news:%23IrtMJd1FHA.612@.TK2MSFTNGP10.phx.gbl...
>> Thanks, David. It appears that DBCC DBREINDEX acquires Sch-M (Schema
>> modification) lock that is incompatible with any other locks whereas
>> DBCC INDEXDEFRAG starts with X lock on row/page level (IX on table
>> level) that is being escalated into X table lock under certain
>> circumstances (I suspect it is table size/defragmantation level
>> related).
>> Igor
>> "David Gugick" <david.gugick-nospam@.quest.com> wrote in message
>> news:%23P9Zwtc1FHA.1032@.TK2MSFTNGP12.phx.gbl...
>> imarchenko wrote:
>> David,
>> Sorry if I didn't make myself clear.
>> The problem is that IX lock is eventually being escalated into X
>> lock. I was under impression that DBCC INDEXDEFRAG issues series of
>> short transactions and never places X lock on entire table. Any
>> thoughts are greatly appreciated.
>> My understanding is that DBCC INDEXDEFRAG is an online operation
>> whereas CREATE/ALTER INDEX is an offline operation. Despite
>> INDEXDEFRAG using short transactions to make its changes, those
>> changes could require varying levels of locks on the underlying table.
>> Maybe someone else can offer additional information on lock escalation
>> with the command.
>>
>> --
>> David Gugick
>> Quest Software
>> www.imceda.com
>> www.quest.com
>>
>>
>|||Paul,
Thanks a lot!
Igor
"Paul S Randal [MS]" <prandal@.online.microsoft.com> wrote in message
news:uUJuIwo1FHA.1032@.TK2MSFTNGP12.phx.gbl...
> There's a bug in SP4 in the lock manager that makes INDEXDEFRAG retain NL
> locks on pages its moved - this eventually causes the next requested X
> page lock to escalate to an X table lock.
> A hotfix is available through PSS - it's not made it to the web yet. There
> will also be a KB article but it hasn't made it out yet either. You should
> be able to reference case SRX050805601805 with PSS and the fix will be
> provided free of charge.
> Thanks
> --
> Paul Randal
> Dev Lead, Microsoft SQL Server Storage Engine
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
> "imarchenko" <igormarchenko@.hotmail.com> wrote in message
> news:%23pCxAdn1FHA.2924@.TK2MSFTNGP15.phx.gbl...
>> Thanks a lor Tibor for looking into this. Please let me know if you will
>> need more details. Computer is running SQL Server 2000 SP4 on Windows
>> 2003 EE.
>> Igor
>> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote
>> in message news:%23n1GHog1FHA.2540@.TK2MSFTNGP09.phx.gbl...
>>I haven't seen anywhere that INDEXDEFRAG should escalate X lock to table
>>level. AFAIK, there should only be IX lock at the table level. I'll ask
>>around and will post back if I get any reply.
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>> Blog: http://solidqualitylearning.com/blogs/tibor/
>>
>> "imarchenko" <igormarchenko@.hotmail.com> wrote in message
>> news:%23IrtMJd1FHA.612@.TK2MSFTNGP10.phx.gbl...
>> Thanks, David. It appears that DBCC DBREINDEX acquires Sch-M (Schema
>> modification) lock that is incompatible with any other locks whereas
>> DBCC INDEXDEFRAG starts with X lock on row/page level (IX on table
>> level) that is being escalated into X table lock under certain
>> circumstances (I suspect it is table size/defragmantation level
>> related).
>> Igor
>> "David Gugick" <david.gugick-nospam@.quest.com> wrote in message
>> news:%23P9Zwtc1FHA.1032@.TK2MSFTNGP12.phx.gbl...
>> imarchenko wrote:
>> David,
>> Sorry if I didn't make myself clear.
>> The problem is that IX lock is eventually being escalated into X
>> lock. I was under impression that DBCC INDEXDEFRAG issues series of
>> short transactions and never places X lock on entire table. Any
>> thoughts are greatly appreciated.
>> My understanding is that DBCC INDEXDEFRAG is an online operation
>> whereas CREATE/ALTER INDEX is an offline operation. Despite
>> INDEXDEFRAG using short transactions to make its changes, those
>> changes could require varying levels of locks on the underlying table.
>> Maybe someone else can offer additional information on lock escalation
>> with the command.
>>
>> --
>> David Gugick
>> Quest Software
>> www.imceda.com
>> www.quest.com
>>
>>
>|||Paul,
Could you please explain what NL is?
Thanks,
Igor
"Paul S Randal [MS]" <prandal@.online.microsoft.com> wrote in message
news:uUJuIwo1FHA.1032@.TK2MSFTNGP12.phx.gbl...
> There's a bug in SP4 in the lock manager that makes INDEXDEFRAG retain NL
> locks on pages its moved - this eventually causes the next requested X
> page lock to escalate to an X table lock.
> A hotfix is available through PSS - it's not made it to the web yet. There
> will also be a KB article but it hasn't made it out yet either. You should
> be able to reference case SRX050805601805 with PSS and the fix will be
> provided free of charge.
> Thanks
> --
> Paul Randal
> Dev Lead, Microsoft SQL Server Storage Engine
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
> "imarchenko" <igormarchenko@.hotmail.com> wrote in message
> news:%23pCxAdn1FHA.2924@.TK2MSFTNGP15.phx.gbl...
>> Thanks a lor Tibor for looking into this. Please let me know if you will
>> need more details. Computer is running SQL Server 2000 SP4 on Windows
>> 2003 EE.
>> Igor
>> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote
>> in message news:%23n1GHog1FHA.2540@.TK2MSFTNGP09.phx.gbl...
>>I haven't seen anywhere that INDEXDEFRAG should escalate X lock to table
>>level. AFAIK, there should only be IX lock at the table level. I'll ask
>>around and will post back if I get any reply.
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>> Blog: http://solidqualitylearning.com/blogs/tibor/
>>
>> "imarchenko" <igormarchenko@.hotmail.com> wrote in message
>> news:%23IrtMJd1FHA.612@.TK2MSFTNGP10.phx.gbl...
>> Thanks, David. It appears that DBCC DBREINDEX acquires Sch-M (Schema
>> modification) lock that is incompatible with any other locks whereas
>> DBCC INDEXDEFRAG starts with X lock on row/page level (IX on table
>> level) that is being escalated into X table lock under certain
>> circumstances (I suspect it is table size/defragmantation level
>> related).
>> Igor
>> "David Gugick" <david.gugick-nospam@.quest.com> wrote in message
>> news:%23P9Zwtc1FHA.1032@.TK2MSFTNGP12.phx.gbl...
>> imarchenko wrote:
>> David,
>> Sorry if I didn't make myself clear.
>> The problem is that IX lock is eventually being escalated into X
>> lock. I was under impression that DBCC INDEXDEFRAG issues series of
>> short transactions and never places X lock on entire table. Any
>> thoughts are greatly appreciated.
>> My understanding is that DBCC INDEXDEFRAG is an online operation
>> whereas CREATE/ALTER INDEX is an offline operation. Despite
>> INDEXDEFRAG using short transactions to make its changes, those
>> changes could require varying levels of locks on the underlying table.
>> Maybe someone else can offer additional information on lock escalation
>> with the command.
>>
>> --
>> David Gugick
>> Quest Software
>> www.imceda.com
>> www.quest.com
>>
>>
>
DBCC INDEXDEFRAG and Transaction Log
Xref: TK2MSFTNGP08.phx.gbl microsoft.public.sqlserver.server:393199
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
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/pro.../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:
> 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/pro.../ss2kidbp.mspx
>
> --
> David Gugick
> Quest Software
> www.imceda.com
> www.quest.com
>
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
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/pro.../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:
> 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/pro.../ss2kidbp.mspx
>
> --
> David Gugick
> Quest Software
> www.imceda.com
> www.quest.com
>
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
>
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
>
Subscribe to:
Posts (Atom)