DBCC SHOWCONTIG

When executing the DBCC SHOWCONTIG statement; will it block all inserts,
selects and updates to its designated table?
We have one table that is constantly being written to 27X7 that cannot
be locked.
I want to run DBCC SHOWCONTIG, to perform maintenance, but do not want
to if this will occur.
Thanks,
--
Ben MUse WITH FAST to avoidd blocking. See BOL for exact syntax
> We have one table that is constantly being written to 27X7
That's a lot of hours in a day :-)
--
HTH
Jasper Smith (SQL Server MVP)
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Ben" <ben@.brainspout.com> wrote in message
news:Or0Uspc4DHA.3416@.tk2msftngp13.phx.gbl...
> When executing the DBCC SHOWCONTIG statement; will it block all inserts,
> selects and updates to its designated table?
> We have one table that is constantly being written to 27X7 that cannot
> be locked.
> I want to run DBCC SHOWCONTIG, to perform maintenance, but do not want
> to if this will occur.
> Thanks,
> --
> Ben M
>|||Yeah that is alot of time:-) Thanks for the help.
Jasper Smith wrote:
> Use WITH FAST to avoidd blocking. See BOL for exact syntax
>
>>We have one table that is constantly being written to 27X7
>
> That's a lot of hours in a day :-)
>
--
Ben M

DBCC SHOWCONTIG

When executing the DBCC SHOWCONTIG statement; will it block all inserts,
selects and updates to its designated table?
We have one table that is constantly being written to 27X7 that cannot
be locked.
I want to run DBCC SHOWCONTIG, to perform maintenance, but do not want
to if this will occur.
Thanks,
--
Ben MUse WITH FAST to avoidd blocking. See BOL for exact syntax

quote:

---

> We have one table that is constantly being written to 27X7

---

That's a lot of hours in a day :-)
HTH
Jasper Smith (SQL Server MVP)
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Ben" <ben@.brainspout.com> wrote in message
news:Or0Uspc4DHA.3416@.tk2msftngp13.phx.gbl...

quote:

---

> When executing the DBCC SHOWCONTIG statement; will it block all inserts,
> selects and updates to its designated table?
> We have one table that is constantly being written to 27X7 that cannot
> be locked.
> I want to run DBCC SHOWCONTIG, to perform maintenance, but do not want
> to if this will occur.
> Thanks,
> --
> Ben M
>

---

|||Yeah that is alot of time:-) Thanks for the help.
Jasper Smith wrote:

quote:

---

> Use WITH FAST to avoidd blocking. See BOL for exact syntax
>
>
> That's a lot of hours in a day :-)
>

---

Ben M

dbcc inputbuffer: looking for the actual statement

Hello all,
Is there a way to display (using TSQL) the actual statement that
a SPID is executing?
When I use dbcc inputbuffer(SPID) all I ever get is "sp_executesql;1" :-(
Thanks
PS: I cannot use the profilerPossibly fn_get_sql can be of help. There are some things to think about when using this (it was
introduced in SQL2K sp3), so Google and KB search and read about it first.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Strider" <Strider@.discussions.microsoft.com> wrote in message
news:582B477A-97A3-447C-826F-270E784D84D9@.microsoft.com...
> Hello all,
> Is there a way to display (using TSQL) the actual statement that
> a SPID is executing?
> When I use dbcc inputbuffer(SPID) all I ever get is "sp_executesql;1" :-(
> Thanks
> PS: I cannot use the profiler|||Thank you Tibor.
This is just what I needed!
"Tibor Karaszi" wrote:
> Possibly fn_get_sql can be of help. There are some things to think about when using this (it was
> introduced in SQL2K sp3), so Google and KB search and read about it first.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Strider" <Strider@.discussions.microsoft.com> wrote in message
> news:582B477A-97A3-447C-826F-270E784D84D9@.microsoft.com...
> > Hello all,
> >
> > Is there a way to display (using TSQL) the actual statement that
> > a SPID is executing?
> >
> > When I use dbcc inputbuffer(SPID) all I ever get is "sp_executesql;1" :-(
> >
> > Thanks
> >
> > PS: I cannot use the profiler
>
>

DBCC INEDXDEFRAG cauing transaction log growth

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
>

DBCC INDEXDEFRAG.. no impact?

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

DBCC DBREINDEX / INDEXDEFRAG

Does SQL Server 2000 after executing DBCC DBREINDEX or
DBCC INDEXDEFRAG run the sp_updatestats?
Are the Logical Fragmentation statistics updated after
execute either the DBCC DBREINDEX or DBCC INDEXDEFRAG
procedures?
Thanks,
Dan
> Does SQL Server 2000 after executing DBCC DBREINDEX or
> DBCC INDEXDEFRAG run the sp_updatestats?
STATS_DATE returns the date that the statistics for the specified index
were last updated.
"Dan" <anonymous@.discussions.microsoft.com> wrote in message
news:1af2101c44f33$df425810$a101280a@.phx.gbl...
> Does SQL Server 2000 after executing DBCC DBREINDEX or
> DBCC INDEXDEFRAG run the sp_updatestats?
> Are the Logical Fragmentation statistics updated after
> execute either the DBCC DBREINDEX or DBCC INDEXDEFRAG
> procedures?
> Thanks,
> Dan
|||for dbcc DBREINDEX Stats are automatically updated.
for dbcc INDEXDEFRAG, they are not
Greg Jackson
PDX, Oregon
|||> Does SQL Server 2000 after executing DBCC DBREINDEX or
> DBCC INDEXDEFRAG run the sp_updatestats?
SQL Server doesn't "run" sp_updatestats. Create statistics is an inherent effect of creating an index, which
in the end is what DBCC DBREINDEX does. INDEXDEFRAG does not update the statistics information, though.

> Are the Logical Fragmentation statistics updated after
> execute either the DBCC DBREINDEX or DBCC INDEXDEFRAG
> procedures?
There's no such thing as "Logical Fragmentation *statistics*". The reason you defragment an index is to reduce
fragmentation. Logical Scan fragmentation is one type of fragmentation. So, yes, this value will be reduced.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Dan" <anonymous@.discussions.microsoft.com> wrote in message news:1af2101c44f33$df425810$a101280a@.phx.gbl...
> Does SQL Server 2000 after executing DBCC DBREINDEX or
> DBCC INDEXDEFRAG run the sp_updatestats?
> Are the Logical Fragmentation statistics updated after
> execute either the DBCC DBREINDEX or DBCC INDEXDEFRAG
> procedures?
> Thanks,
> Dan

DBCC DBREINDEX / INDEXDEFRAG

Does SQL Server 2000 after executing DBCC DBREINDEX or
DBCC INDEXDEFRAG run the sp_updatestats?
Are the Logical Fragmentation statistics updated after
execute either the DBCC DBREINDEX or DBCC INDEXDEFRAG
procedures?
Thanks,
Dan> Does SQL Server 2000 after executing DBCC DBREINDEX or
> DBCC INDEXDEFRAG run the sp_updatestats?
STATS_DATE returns the date that the statistics for the specified index
were last updated.
"Dan" <anonymous@.discussions.microsoft.com> wrote in message
news:1af2101c44f33$df425810$a101280a@.phx
.gbl...
> Does SQL Server 2000 after executing DBCC DBREINDEX or
> DBCC INDEXDEFRAG run the sp_updatestats?
> Are the Logical Fragmentation statistics updated after
> execute either the DBCC DBREINDEX or DBCC INDEXDEFRAG
> procedures?
> Thanks,
> Dan|||for dbcc DBREINDEX Stats are automatically updated.
for dbcc INDEXDEFRAG, they are not
Greg Jackson
PDX, Oregon|||> Does SQL Server 2000 after executing DBCC DBREINDEX or
> DBCC INDEXDEFRAG run the sp_updatestats?
SQL Server doesn't "run" sp_updatestats. Create statistics is an inherent ef
fect of creating an index, which
in the end is what DBCC DBREINDEX does. INDEXDEFRAG does not update the stat
istics information, though.

> Are the Logical Fragmentation statistics updated after
> execute either the DBCC DBREINDEX or DBCC INDEXDEFRAG
> procedures?
There's no such thing as "Logical Fragmentation *statistics*". The reason yo
u defragment an index is to reduce
fragmentation. Logical Scan fragmentation is one type of fragmentation. So,
yes, this value will be reduced.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Dan" <anonymous@.discussions.microsoft.com> wrote in message news:1af2101c44f33$df425810$a10
1280a@.phx.gbl...
> Does SQL Server 2000 after executing DBCC DBREINDEX or
> DBCC INDEXDEFRAG run the sp_updatestats?
> Are the Logical Fragmentation statistics updated after
> execute either the DBCC DBREINDEX or DBCC INDEXDEFRAG
> procedures?
> Thanks,
> Dan

DBCC DBREINDEX / INDEXDEFRAG

Does SQL Server 2000 after executing DBCC DBREINDEX or
DBCC INDEXDEFRAG run the sp_updatestats?
Are the Logical Fragmentation statistics updated after
execute either the DBCC DBREINDEX or DBCC INDEXDEFRAG
procedures?
Thanks,
Dan> Does SQL Server 2000 after executing DBCC DBREINDEX or
> DBCC INDEXDEFRAG run the sp_updatestats?
STATS_DATE returns the date that the statistics for the specified index
were last updated.
"Dan" <anonymous@.discussions.microsoft.com> wrote in message
news:1af2101c44f33$df425810$a101280a@.phx.gbl...
> Does SQL Server 2000 after executing DBCC DBREINDEX or
> DBCC INDEXDEFRAG run the sp_updatestats?
> Are the Logical Fragmentation statistics updated after
> execute either the DBCC DBREINDEX or DBCC INDEXDEFRAG
> procedures?
> Thanks,
> Dan|||for dbcc DBREINDEX Stats are automatically updated.
for dbcc INDEXDEFRAG, they are not
Greg Jackson
PDX, Oregon|||> Does SQL Server 2000 after executing DBCC DBREINDEX or
> DBCC INDEXDEFRAG run the sp_updatestats?
SQL Server doesn't "run" sp_updatestats. Create statistics is an inherent effect of creating an index, which
in the end is what DBCC DBREINDEX does. INDEXDEFRAG does not update the statistics information, though.
> Are the Logical Fragmentation statistics updated after
> execute either the DBCC DBREINDEX or DBCC INDEXDEFRAG
> procedures?
There's no such thing as "Logical Fragmentation *statistics*". The reason you defragment an index is to reduce
fragmentation. Logical Scan fragmentation is one type of fragmentation. So, yes, this value will be reduced.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Dan" <anonymous@.discussions.microsoft.com> wrote in message news:1af2101c44f33$df425810$a101280a@.phx.gbl...
> Does SQL Server 2000 after executing DBCC DBREINDEX or
> DBCC INDEXDEFRAG run the sp_updatestats?
> Are the Logical Fragmentation statistics updated after
> execute either the DBCC DBREINDEX or DBCC INDEXDEFRAG
> procedures?
> Thanks,
> Dan