DBCC SHOWCONTIG and SQL2k Maintenance Plan Question

When performing the DBCC SHOWCONTIG will this perform table locks? If
I use the WITH FAST option will this allow data to be written to the file?
Also when using the maintenance plan wizard to perform maintenance, will
tables be locked when the indexes are rebuilt and data is reorganized?
Thanks,
-Ben(I'm assuming you're using SQL Server 2000 as you mention the WITH FAST
option I added in that release)
DBCC SHOWCONTIG ... WITH FAST takes a table IS lock - thus allowing updates.
Without WITH FAST is takes a table S lock. Look in BOL for more details
here.
Rebuilding an index requires a table S lock if you're rebuilding a
non-clustered index, and a table X lock for rebuilding a clustered index.
Running DBCC INDEXDEFRAG only requires a table IX lock - so allows updates.
You should read the whitepaper below 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.
"Ben" <ben@.brainspout.com> wrote in message
news:uoGzfuO5DHA.1368@.TK2MSFTNGP10.phx.gbl...

quote:

---

> When performing the DBCC SHOWCONTIG will this perform table locks? If
> I use the WITH FAST option will this allow data to be written to the file?
> Also when using the maintenance plan wizard to perform maintenance, will
> tables be locked when the indexes are rebuilt and data is reorganized?
> Thanks,
> -Ben
>

---

DBCC SHOWCONTIG and SQL2k Maintenance Plan Question

When performing the DBCC SHOWCONTIG will this perform table locks? If
I use the WITH FAST option will this allow data to be written to the file?
Also when using the maintenance plan wizard to perform maintenance, will
tables be locked when the indexes are rebuilt and data is reorganized?
Thanks,
-Ben(I'm assuming you're using SQL Server 2000 as you mention the WITH FAST
option I added in that release)
DBCC SHOWCONTIG ... WITH FAST takes a table IS lock - thus allowing updates.
Without WITH FAST is takes a table S lock. Look in BOL for more details
here.
Rebuilding an index requires a table S lock if you're rebuilding a
non-clustered index, and a table X lock for rebuilding a clustered index.
Running DBCC INDEXDEFRAG only requires a table IX lock - so allows updates.
You should read the whitepaper below 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.
"Ben" <ben@.brainspout.com> wrote in message
news:uoGzfuO5DHA.1368@.TK2MSFTNGP10.phx.gbl...
> When performing the DBCC SHOWCONTIG will this perform table locks? If
> I use the WITH FAST option will this allow data to be written to the file?
> Also when using the maintenance plan wizard to perform maintenance, will
> tables be locked when the indexes are rebuilt and data is reorganized?
> Thanks,
> -Ben
>

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

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

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

dbcc freeproccache only solution??

Recently upgranded to SQL2005 sp1
Daily we have times of very poor performance. We tracked down to full table
scans occuring, indexes not used.
Perform a dbcc freeproccache to solve. Any idea why this is occurring?
Happening more often since upgrade - did notice same issue when on sql2000...
Any ideas?
tia
chris
On Fri, 11 May 2007 15:55:00 -0700, Chris
<Chris@.discussions.microsoft.com> wrote:

>Recently upgranded to SQL2005 sp1
>Daily we have times of very poor performance. We tracked down to full table
>scans occuring, indexes not used.
>Perform a dbcc freeproccache to solve. Any idea why this is occurring?
>Happening more often since upgrade - did notice same issue when on sql2000...
>Any ideas?
Many ideas.
But they all start with isolating the problem code, the DDL, and the
stats involved.
J.
|||Sounds like parameter sniffing issues. Did you update the stats after the
upgrade? Have a look at these:
http://www.microsoft.com/technet/prodtechnol/sql/2005/tsprfprb.mspx
http://www.microsoft.com/technet/prodtechnol/sql/2005/recomp.mspx
Andrew J. Kelly SQL MVP
"Chris" <Chris@.discussions.microsoft.com> wrote in message
news:E65671C4-696D-44BC-AB87-BD3103AB5298@.microsoft.com...
> Recently upgranded to SQL2005 sp1
> Daily we have times of very poor performance. We tracked down to full
> table
> scans occuring, indexes not used.
> Perform a dbcc freeproccache to solve. Any idea why this is occurring?
> Happening more often since upgrade - did notice same issue when on
> sql2000...
> Any ideas?
> tia
> chris
|||"Chris" <Chris@.discussions.microsoft.com> wrote in message
news:E65671C4-696D-44BC-AB87-BD3103AB5298@.microsoft.com...
> Recently upgranded to SQL2005 sp1
> Daily we have times of very poor performance. We tracked down to full
> table
> scans occuring, indexes not used.
> Perform a dbcc freeproccache to solve. Any idea why this is occurring?
> Happening more often since upgrade - did notice same issue when on
> sql2000...
> Any ideas?
> tia
> chris
My guess is that most likely your stored proc query plans are becoming
invalid.
A simple test would be to find one called a lot and do a recompile on it and
see if the performance gets better.
If it does, there's a few things to loook at.
Are you doing a lot of updates/inserts/deletes that invalidate the stats for
your indices? If so, you can update stats more often (among other things).
Also lok for stuff like a "IF @.FOO then X else Y" Typically the first time
the procedure is called, a particular path is followed (say @.FOO Is true,
then X is followed.) This is the query plan that will be cashed. If later
the call has @.FOO is false and Y is followed the query plan will be invalid.
So that's something else to look at.
Greg Moore
SQL Server DBA Consulting Remote and Onsite available!
Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html

dbcc freeproccache only solution??

Recently upgranded to SQL2005 sp1
Daily we have times of very poor performance. We tracked down to full table
scans occuring, indexes not used.
Perform a dbcc freeproccache to solve. Any idea why this is occurring?
Happening more often since upgrade - did notice same issue when on sql2000..
.
Any ideas?
tia
chrisOn Fri, 11 May 2007 15:55:00 -0700, Chris
<Chris@.discussions.microsoft.com> wrote:

>Recently upgranded to SQL2005 sp1
>Daily we have times of very poor performance. We tracked down to full table
>scans occuring, indexes not used.
>Perform a dbcc freeproccache to solve. Any idea why this is occurring?
>Happening more often since upgrade - did notice same issue when on sql2000.
.
>Any ideas?
Many ideas.
But they all start with isolating the problem code, the DDL, and the
stats involved.
J.|||Sounds like parameter sniffing issues. Did you update the stats after the
upgrade? Have a look at these:
http://www.microsoft.com/technet/pr...5/tsprfprb.mspx
http://www.microsoft.com/technet/pr...005/recomp.mspx
Andrew J. Kelly SQL MVP
"Chris" <Chris@.discussions.microsoft.com> wrote in message
news:E65671C4-696D-44BC-AB87-BD3103AB5298@.microsoft.com...
> Recently upgranded to SQL2005 sp1
> Daily we have times of very poor performance. We tracked down to full
> table
> scans occuring, indexes not used.
> Perform a dbcc freeproccache to solve. Any idea why this is occurring?
> Happening more often since upgrade - did notice same issue when on
> sql2000...
> Any ideas?
> tia
> chris|||"Chris" <Chris@.discussions.microsoft.com> wrote in message
news:E65671C4-696D-44BC-AB87-BD3103AB5298@.microsoft.com...
> Recently upgranded to SQL2005 sp1
> Daily we have times of very poor performance. We tracked down to full
> table
> scans occuring, indexes not used.
> Perform a dbcc freeproccache to solve. Any idea why this is occurring?
> Happening more often since upgrade - did notice same issue when on
> sql2000...
> Any ideas?
> tia
> chris
My guess is that most likely your stored proc query plans are becoming
invalid.
A simple test would be to find one called a lot and do a recompile on it and
see if the performance gets better.
If it does, there's a few things to loook at.
Are you doing a lot of updates/inserts/deletes that invalidate the stats for
your indices? If so, you can update stats more often (among other things).
Also lok for stuff like a "IF @.FOO then X else Y" Typically the first time
the procedure is called, a particular path is followed (say @.FOO Is true,
then X is followed.) This is the query plan that will be cashed. If later
the call has @.FOO is false and Y is followed the query plan will be invalid.
So that's something else to look at.
Greg Moore
SQL Server DBA Consulting Remote and Onsite available!
Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html