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
>