DBfile usage: non-clustered

Showing posts with label non-clustered. Show all posts

Tuesday, March 27, 2012

DBCC SHRINKDATABASE

We previously had a problem (due to the fact the previous DBA made every
index a non-clustered instead of clustered) that caused our database file
size to grow out of control. We had databases that should have been around
10gb growing to well over 50gb. I made everything a clustered index and
reindex every table and the database size dropped to 30gb but with roughly
20gb of free space. I'm not trying to use the DBCC SHRINKDATABASE command to
reclain that free space and on some database it's dropping the size from 30gb
down to 10gb with a few gb of free space which is great, but on some it's not
reclaiming that space. I've tried using the command with the truncate only
option, specifying like 5 percent free space and I just can't find why on
some databases it will reclaim that space (more success witht he truncate
only option than the others) and other databases it's retaining like 80% of
the database size as free space. I'm using the update usage command as well
to get things in line. Any help would be appreciated. Thanks.
Hi
Try using DBCC SHRINKFILE instead
http://msdn.microsoft.com/library/de..._dbcc_8b51.asp
You may also want to check out other posts where SHRINKDATABASE has not
changed the size such as http://tinyurl.com/uz2om
John
"brogers5884" wrote:

> We previously had a problem (due to the fact the previous DBA made every
> index a non-clustered instead of clustered) that caused our database file
> size to grow out of control. We had databases that should have been around
> 10gb growing to well over 50gb. I made everything a clustered index and
> reindex every table and the database size dropped to 30gb but with roughly
> 20gb of free space. I'm not trying to use the DBCC SHRINKDATABASE command to
> reclain that free space and on some database it's dropping the size from 30gb
> down to 10gb with a few gb of free space which is great, but on some it's not
> reclaiming that space. I've tried using the command with the truncate only
> option, specifying like 5 percent free space and I just can't find why on
> some databases it will reclaim that space (more success witht he truncate
> only option than the others) and other databases it's retaining like 80% of
> the database size as free space. I'm using the update usage command as well
> to get things in line. Any help would be appreciated. Thanks.

DBCC SHRINKDATABASE

We previously had a problem (due to the fact the previous DBA made every
index a non-clustered instead of clustered) that caused our database file
size to grow out of control. We had databases that should have been around
10gb growing to well over 50gb. I made everything a clustered index and
reindex every table and the database size dropped to 30gb but with roughly
20gb of free space. I'm not trying to use the DBCC SHRINKDATABASE command to
reclain that free space and on some database it's dropping the size from 30gb
down to 10gb with a few gb of free space which is great, but on some it's not
reclaiming that space. I've tried using the command with the truncate only
option, specifying like 5 percent free space and I just can't find why on
some databases it will reclaim that space (more success witht he truncate
only option than the others) and other databases it's retaining like 80% of
the database size as free space. I'm using the update usage command as well
to get things in line. Any help would be appreciated. Thanks.Hi
Try using DBCC SHRINKFILE instead
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_dbcc_8b51.asp
You may also want to check out other posts where SHRINKDATABASE has not
changed the size such as http://tinyurl.com/uz2om
John
"brogers5884" wrote:
> We previously had a problem (due to the fact the previous DBA made every
> index a non-clustered instead of clustered) that caused our database file
> size to grow out of control. We had databases that should have been around
> 10gb growing to well over 50gb. I made everything a clustered index and
> reindex every table and the database size dropped to 30gb but with roughly
> 20gb of free space. I'm not trying to use the DBCC SHRINKDATABASE command to
> reclain that free space and on some database it's dropping the size from 30gb
> down to 10gb with a few gb of free space which is great, but on some it's not
> reclaiming that space. I've tried using the command with the truncate only
> option, specifying like 5 percent free space and I just can't find why on
> some databases it will reclaim that space (more success witht he truncate
> only option than the others) and other databases it's retaining like 80% of
> the database size as free space. I'm using the update usage command as well
> to get things in line. Any help would be appreciated. Thanks.

DBCC SHRINKDATABASE

We previously had a problem (due to the fact the previous DBA made every
index a non-clustered instead of clustered) that caused our database file
size to grow out of control. We had databases that should have been around
10gb growing to well over 50gb. I made everything a clustered index and
reindex every table and the database size dropped to 30gb but with roughly
20gb of free space. I'm not trying to use the DBCC SHRINKDATABASE command t
o
reclain that free space and on some database it's dropping the size from 30g
b
down to 10gb with a few gb of free space which is great, but on some it's no
t
reclaiming that space. I've tried using the command with the truncate only
option, specifying like 5 percent free space and I just can't find why on
some databases it will reclaim that space (more success witht he truncate
only option than the others) and other databases it's retaining like 80% of
the database size as free space. I'm using the update usage command as well
to get things in line. Any help would be appreciated. Thanks.Hi
Try using DBCC SHRINKFILE instead
http://msdn.microsoft.com/library/d...
b51.asp
You may also want to check out other posts where SHRINKDATABASE has not
changed the size such as http://tinyurl.com/uz2om
John
"brogers5884" wrote:

> We previously had a problem (due to the fact the previous DBA made every
> index a non-clustered instead of clustered) that caused our database file
> size to grow out of control. We had databases that should have been aroun
d
> 10gb growing to well over 50gb. I made everything a clustered index and
> reindex every table and the database size dropped to 30gb but with roughly
> 20gb of free space. I'm not trying to use the DBCC SHRINKDATABASE command
to
> reclain that free space and on some database it's dropping the size from 3
0gb
> down to 10gb with a few gb of free space which is great, but on some it's
not
> reclaiming that space. I've tried using the command with the truncate onl
y
> option, specifying like 5 percent free space and I just can't find why on
> some databases it will reclaim that space (more success witht he truncate
> only option than the others) and other databases it's retaining like 80% o
f
> the database size as free space. I'm using the update usage command as we
ll
> to get things in line. Any help would be appreciated. Thanks.

Sunday, March 11, 2012

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
>>
>>
>

Thursday, March 8, 2012

dbcc error 2511

We had a dbcc error 2511 on a user table - non-clustered
index.
>>There are 0 rows in 1 pages for
object 'sched_order_item'.
Msg 2511, Level 16, State 1, Server DCECANP1, Procedure ,
Line 1
[Microsoft][ODBC SQL Server Driver][SQL Server]Table
Corrupt: Object
ID 1421716613, Index ID 5. Keys out of order on page
(1:758631), slots
120 and 121.<<
dbcc checktable(table_name,repair_rebuild) in single user
mode was run, but the error is back the night after. By
the way, table doesn't have any data in it. if anybody
have any previous experiance with it and could share it
with me, I would appreciate it. Thanks very muchBOL shows it to be an index out of order problem. Strange
as you have no rows. I think I have come across this where
table changes are made and not reflected in the index
because there is no data.
I would try scripting out the table, drop it and then
recreate it.
Regards
John

Wednesday, March 7, 2012

DBCC DBREINDEX unexpected results

Normally, after I use DBCC DBREINDEX, I can be sure that Scan Density on a clustered or non-clustered index is very good - eg. 99% or 100%. However, I have one database where there are a number of indexes that are not showing any improvement in Scan Density after running DBCC DBREINDEX. In on case, a clustered index, I run it on two days in succession and Scan Density actually go worse! Can anyone give me a reason for this? Can anyone suggest how to fix it?

CliveI think I found the problem. Look like a newly modified maintenance proc wasn't doing what it should have been.

Clive

DBCC DBREINDEX Behaviour

Folks,
I have a table with a clustered index and 6 non-clustered indexes. When I
issue a DBCC DBREINDEX without specifying a specific index all indexes are
rebuilt including the clustered index - this takes 3 hours to complete and
consumes 9gb of TLog space. When I issue a DBCC DBREINDEX specifying only the
clustered index (which happens to be supporting the primary key) I've read
that all non-clustered indexes are also rebuilt - this takes on 1.5 hours and
consumes 6gb of Tlog space. I have 3 questions:
1) If you use DBCC DBREINDEX without specifying an index, are the
non-clustered indexes rebuilt twice on a table with a clustered index?
2) I would like to confirm that all indexes are being rebuilt. Is the time
the index is created captured, if so, how can I view the time?
3) Why is this a logged transaction? I would have thought that SQL Server
would create the indexes without dropping the originals, and then simply swap
and drop once the index has completed.
Thanks in advance for your help.
--
Scott H.It really helps to specify what version and service pack you are using. In
this case it can make a big difference. If I remember correctly in the RTM
and maybe SP1 versions of SQL2000 it worked like this:
If you rebuild the clustered index ( CI ) all non-clustered indexes ( NCI)
were always rebuilt as well. This was due to the way in which the CI key was
appended to the end of all NCI's and would change during a rebuild.
With one of the SP's (I think SP2) that behavior changed in that if the CI
was unique and you rebuilt the CI by specifying only that index it did not
rebuild the NCI's. But if the CI was not unique they would add a uniquifer
(4 byte code) to the CI which got regenerated each time the CI was rebuilt.
Since the CI (including the uniqueifier) was appended to the end of all
NCI's they in turn needed to be rebuilt as well.
IN SQL2005 they changed the way they generated the uniqifier and it no
longer changes when the CI is rebuilt. So there is no need to rebuild all
the NCI's just because you rebuild the CI.
But to answer your specific questions a little more:
> 1) If you use DBCC DBREINDEX without specifying an index, are the
> non-clustered indexes rebuilt twice on a table with a clustered index?
No. SQL Server was smart enough in all versions to only rebuild the NCI's
once.
> 2) I would like to confirm that all indexes are being rebuilt. Is the time
> the index is created captured, if so, how can I view the time?
No but you can look at DBCC SHOWCONTIG or SHOWSTATISTICS before and after
you can see the differences and will see where to look to see if work was
done.
> 3) Why is this a logged transaction? I would have thought that SQL Server
> would create the indexes without dropping the originals, and then simply
> swap
> and drop once the index has completed.
If you are in FULL recovery mode everything is always fully logged. If you
are in Bulk-Logged or Simple mode some index create or rebuild operations
can be minimally logged. So your transaction log file may not grow very much
but when you backup the log file (if in Bulk-logged) the backup will include
all the extents changed byt he operations.
--
Andrew J. Kelly SQL MVP
"Scott H." <ScottH@.discussions.microsoft.com> wrote in message
news:A76DD6A4-4D1D-40F2-AC8E-F94C9042F42C@.microsoft.com...
> Folks,
> I have a table with a clustered index and 6 non-clustered indexes. When I
> issue a DBCC DBREINDEX without specifying a specific index all indexes are
> rebuilt including the clustered index - this takes 3 hours to complete and
> consumes 9gb of TLog space. When I issue a DBCC DBREINDEX specifying only
> the
> clustered index (which happens to be supporting the primary key) I've read
> that all non-clustered indexes are also rebuilt - this takes on 1.5 hours
> and
> consumes 6gb of Tlog space. I have 3 questions:
> 1) If you use DBCC DBREINDEX without specifying an index, are the
> non-clustered indexes rebuilt twice on a table with a clustered index?
> 2) I would like to confirm that all indexes are being rebuilt. Is the time
> the index is created captured, if so, how can I view the time?
> 3) Why is this a logged transaction? I would have thought that SQL Server
> would create the indexes without dropping the originals, and then simply
> swap
> and drop once the index has completed.
> Thanks in advance for your help.
> --
> Scott H.|||Thanks Andrew.
SQL Server 2000 SP3
The CI is unique, so it would appear to be rebuilding only the CI.
I re-ran the test in simple recovery mode, and the TLOG grew to only 190mb.
I guess in production I could put the server into single user mode, change to
simple recovery mode, run the reorg, full backup, return to full rcovery
mode. Seems like overkill. The reason I may follow this route is that we are
having disk capacity issues.
I have an application team thinking that rebuilding (DBREINDEX) this 17
million record table is a good thing to do nightly 7 days/week. I need to dig
up supporting evidence one way or the other. I'll make use of SHOWCONTIG
prior to the execution over the next few days to determine. If there is
anything else you think could be beneficial feel free to offer. I've recently
inherited the support of this instaance and there is much work to do.
Thanks for your help.
--
Thanks,
Scott H.
"Andrew J. Kelly" wrote:
> It really helps to specify what version and service pack you are using. In
> this case it can make a big difference. If I remember correctly in the RTM
> and maybe SP1 versions of SQL2000 it worked like this:
> If you rebuild the clustered index ( CI ) all non-clustered indexes ( NCI)
> were always rebuilt as well. This was due to the way in which the CI key was
> appended to the end of all NCI's and would change during a rebuild.
> With one of the SP's (I think SP2) that behavior changed in that if the CI
> was unique and you rebuilt the CI by specifying only that index it did not
> rebuild the NCI's. But if the CI was not unique they would add a uniquifer
> (4 byte code) to the CI which got regenerated each time the CI was rebuilt.
> Since the CI (including the uniqueifier) was appended to the end of all
> NCI's they in turn needed to be rebuilt as well.
> IN SQL2005 they changed the way they generated the uniqifier and it no
> longer changes when the CI is rebuilt. So there is no need to rebuild all
> the NCI's just because you rebuild the CI.
> But to answer your specific questions a little more:
> > 1) If you use DBCC DBREINDEX without specifying an index, are the
> > non-clustered indexes rebuilt twice on a table with a clustered index?
> No. SQL Server was smart enough in all versions to only rebuild the NCI's
> once.
> > 2) I would like to confirm that all indexes are being rebuilt. Is the time
> > the index is created captured, if so, how can I view the time?
> No but you can look at DBCC SHOWCONTIG or SHOWSTATISTICS before and after
> you can see the differences and will see where to look to see if work was
> done.
> > 3) Why is this a logged transaction? I would have thought that SQL Server
> > would create the indexes without dropping the originals, and then simply
> > swap
> > and drop once the index has completed.
> If you are in FULL recovery mode everything is always fully logged. If you
> are in Bulk-Logged or Simple mode some index create or rebuild operations
> can be minimally logged. So your transaction log file may not grow very much
> but when you backup the log file (if in Bulk-logged) the backup will include
> all the extents changed byt he operations.
> --
> Andrew J. Kelly SQL MVP
> "Scott H." <ScottH@.discussions.microsoft.com> wrote in message
> news:A76DD6A4-4D1D-40F2-AC8E-F94C9042F42C@.microsoft.com...
> > Folks,
> >
> > I have a table with a clustered index and 6 non-clustered indexes. When I
> > issue a DBCC DBREINDEX without specifying a specific index all indexes are
> > rebuilt including the clustered index - this takes 3 hours to complete and
> > consumes 9gb of TLog space. When I issue a DBCC DBREINDEX specifying only
> > the
> > clustered index (which happens to be supporting the primary key) I've read
> > that all non-clustered indexes are also rebuilt - this takes on 1.5 hours
> > and
> > consumes 6gb of Tlog space. I have 3 questions:
> >
> > 1) If you use DBCC DBREINDEX without specifying an index, are the
> > non-clustered indexes rebuilt twice on a table with a clustered index?
> >
> > 2) I would like to confirm that all indexes are being rebuilt. Is the time
> > the index is created captured, if so, how can I view the time?
> >
> > 3) Why is this a logged transaction? I would have thought that SQL Server
> > would create the indexes without dropping the originals, and then simply
> > swap
> > and drop once the index has completed.
> >
> > Thanks in advance for your help.
> > --
> > Scott H.
>
>|||Hi Scott
You might want to consider switching to bulk_logged mode instead of simple.
The logging should be about the same, and you won't have to do a full db
backup after, on a tlog backup. Situations like this are exactly what
bulk_logged mode is intended for, i.e. so that you can do large bulk
operations, like data loads or index rebuilds, that normally are log
intensive, and make then less log intensive. Switching to bulk_logged mode
allows your chain of tlog backups to remain intact and again, no full backup
is required.
You might want to read more details about the different recovery models in
the Books Online and also this KB article might help:
A transaction log grows unexpectedly or becomes full on a computer that is
running SQL Server
http://support.microsoft.com/kb/317375/en-us
--
HTH
Kalen Delaney, SQL Server MVP
http://sqlblog.com
"Scott H." <ScottH@.discussions.microsoft.com> wrote in message
news:6355DB27-5D19-4A8F-A32D-5A2588D275D7@.microsoft.com...
> Thanks Andrew.
> SQL Server 2000 SP3
> The CI is unique, so it would appear to be rebuilding only the CI.
> I re-ran the test in simple recovery mode, and the TLOG grew to only
> 190mb.
> I guess in production I could put the server into single user mode, change
> to
> simple recovery mode, run the reorg, full backup, return to full rcovery
> mode. Seems like overkill. The reason I may follow this route is that we
> are
> having disk capacity issues.
> I have an application team thinking that rebuilding (DBREINDEX) this 17
> million record table is a good thing to do nightly 7 days/week. I need to
> dig
> up supporting evidence one way or the other. I'll make use of SHOWCONTIG
> prior to the execution over the next few days to determine. If there is
> anything else you think could be beneficial feel free to offer. I've
> recently
> inherited the support of this instaance and there is much work to do.
> Thanks for your help.
> --
> Thanks,
> Scott H.
>
> "Andrew J. Kelly" wrote:
>> It really helps to specify what version and service pack you are using.
>> In
>> this case it can make a big difference. If I remember correctly in the
>> RTM
>> and maybe SP1 versions of SQL2000 it worked like this:
>> If you rebuild the clustered index ( CI ) all non-clustered indexes (
>> NCI)
>> were always rebuilt as well. This was due to the way in which the CI key
>> was
>> appended to the end of all NCI's and would change during a rebuild.
>> With one of the SP's (I think SP2) that behavior changed in that if the
>> CI
>> was unique and you rebuilt the CI by specifying only that index it did
>> not
>> rebuild the NCI's. But if the CI was not unique they would add a
>> uniquifer
>> (4 byte code) to the CI which got regenerated each time the CI was
>> rebuilt.
>> Since the CI (including the uniqueifier) was appended to the end of all
>> NCI's they in turn needed to be rebuilt as well.
>> IN SQL2005 they changed the way they generated the uniqifier and it no
>> longer changes when the CI is rebuilt. So there is no need to rebuild all
>> the NCI's just because you rebuild the CI.
>> But to answer your specific questions a little more:
>> > 1) If you use DBCC DBREINDEX without specifying an index, are the
>> > non-clustered indexes rebuilt twice on a table with a clustered index?
>> No. SQL Server was smart enough in all versions to only rebuild the NCI's
>> once.
>> > 2) I would like to confirm that all indexes are being rebuilt. Is the
>> > time
>> > the index is created captured, if so, how can I view the time?
>> No but you can look at DBCC SHOWCONTIG or SHOWSTATISTICS before and after
>> you can see the differences and will see where to look to see if work was
>> done.
>> > 3) Why is this a logged transaction? I would have thought that SQL
>> > Server
>> > would create the indexes without dropping the originals, and then
>> > simply
>> > swap
>> > and drop once the index has completed.
>> If you are in FULL recovery mode everything is always fully logged. If
>> you
>> are in Bulk-Logged or Simple mode some index create or rebuild operations
>> can be minimally logged. So your transaction log file may not grow very
>> much
>> but when you backup the log file (if in Bulk-logged) the backup will
>> include
>> all the extents changed byt he operations.
>> --
>> Andrew J. Kelly SQL MVP
>> "Scott H." <ScottH@.discussions.microsoft.com> wrote in message
>> news:A76DD6A4-4D1D-40F2-AC8E-F94C9042F42C@.microsoft.com...
>> > Folks,
>> >
>> > I have a table with a clustered index and 6 non-clustered indexes. When
>> > I
>> > issue a DBCC DBREINDEX without specifying a specific index all indexes
>> > are
>> > rebuilt including the clustered index - this takes 3 hours to complete
>> > and
>> > consumes 9gb of TLog space. When I issue a DBCC DBREINDEX specifying
>> > only
>> > the
>> > clustered index (which happens to be supporting the primary key) I've
>> > read
>> > that all non-clustered indexes are also rebuilt - this takes on 1.5
>> > hours
>> > and
>> > consumes 6gb of Tlog space. I have 3 questions:
>> >
>> > 1) If you use DBCC DBREINDEX without specifying an index, are the
>> > non-clustered indexes rebuilt twice on a table with a clustered index?
>> >
>> > 2) I would like to confirm that all indexes are being rebuilt. Is the
>> > time
>> > the index is created captured, if so, how can I view the time?
>> >
>> > 3) Why is this a logged transaction? I would have thought that SQL
>> > Server
>> > would create the indexes without dropping the originals, and then
>> > simply
>> > swap
>> > and drop once the index has completed.
>> >
>> > Thanks in advance for your help.
>> > --
>> > Scott H.
>>|||Scott,
I second Kalen's advise about using Bulklogged vs Simple if you need to go
that way. Just remember that the Log backup files will be large regardless
if you have space issues. Hopefully you are not backing up to the same drive
array as the data is on anyway. But most systems rarely require an index
rebuild nightly and certainly not for all tables in the db. There is a
sample script in BooksOnLine under DBCC SHOWCONTIG that will allow you to
only reindex or Defrag the indexes that are above a certain fragmentation
level anyway. This should dramatically cut down the tlog space requirements
as well. These articles (especially the first one) are worth reading.
http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ss2kidbp.mspx
Index Defrag Best Practices 2000
http://www.sql-server-performance.com/dt_dbcc_showcontig.asp
Understanding DBCC SHOWCONTIG
http://www.sqlservercentral.com/columnists/jweisbecker/amethodologyfordeterminingfillfactors.asp
Fill Factors
http://www.sql-server-performance.com/gv_clustered_indexes.asp
Clustered Indexes
--
Andrew J. Kelly SQL MVP
"Scott H." <ScottH@.discussions.microsoft.com> wrote in message
news:6355DB27-5D19-4A8F-A32D-5A2588D275D7@.microsoft.com...
> Thanks Andrew.
> SQL Server 2000 SP3
> The CI is unique, so it would appear to be rebuilding only the CI.
> I re-ran the test in simple recovery mode, and the TLOG grew to only
> 190mb.
> I guess in production I could put the server into single user mode, change
> to
> simple recovery mode, run the reorg, full backup, return to full rcovery
> mode. Seems like overkill. The reason I may follow this route is that we
> are
> having disk capacity issues.
> I have an application team thinking that rebuilding (DBREINDEX) this 17
> million record table is a good thing to do nightly 7 days/week. I need to
> dig
> up supporting evidence one way or the other. I'll make use of SHOWCONTIG
> prior to the execution over the next few days to determine. If there is
> anything else you think could be beneficial feel free to offer. I've
> recently
> inherited the support of this instaance and there is much work to do.
> Thanks for your help.
> --
> Thanks,
> Scott H.
>
> "Andrew J. Kelly" wrote:
>> It really helps to specify what version and service pack you are using.
>> In
>> this case it can make a big difference. If I remember correctly in the
>> RTM
>> and maybe SP1 versions of SQL2000 it worked like this:
>> If you rebuild the clustered index ( CI ) all non-clustered indexes (
>> NCI)
>> were always rebuilt as well. This was due to the way in which the CI key
>> was
>> appended to the end of all NCI's and would change during a rebuild.
>> With one of the SP's (I think SP2) that behavior changed in that if the
>> CI
>> was unique and you rebuilt the CI by specifying only that index it did
>> not
>> rebuild the NCI's. But if the CI was not unique they would add a
>> uniquifer
>> (4 byte code) to the CI which got regenerated each time the CI was
>> rebuilt.
>> Since the CI (including the uniqueifier) was appended to the end of all
>> NCI's they in turn needed to be rebuilt as well.
>> IN SQL2005 they changed the way they generated the uniqifier and it no
>> longer changes when the CI is rebuilt. So there is no need to rebuild all
>> the NCI's just because you rebuild the CI.
>> But to answer your specific questions a little more:
>> > 1) If you use DBCC DBREINDEX without specifying an index, are the
>> > non-clustered indexes rebuilt twice on a table with a clustered index?
>> No. SQL Server was smart enough in all versions to only rebuild the NCI's
>> once.
>> > 2) I would like to confirm that all indexes are being rebuilt. Is the
>> > time
>> > the index is created captured, if so, how can I view the time?
>> No but you can look at DBCC SHOWCONTIG or SHOWSTATISTICS before and after
>> you can see the differences and will see where to look to see if work was
>> done.
>> > 3) Why is this a logged transaction? I would have thought that SQL
>> > Server
>> > would create the indexes without dropping the originals, and then
>> > simply
>> > swap
>> > and drop once the index has completed.
>> If you are in FULL recovery mode everything is always fully logged. If
>> you
>> are in Bulk-Logged or Simple mode some index create or rebuild operations
>> can be minimally logged. So your transaction log file may not grow very
>> much
>> but when you backup the log file (if in Bulk-logged) the backup will
>> include
>> all the extents changed byt he operations.
>> --
>> Andrew J. Kelly SQL MVP
>> "Scott H." <ScottH@.discussions.microsoft.com> wrote in message
>> news:A76DD6A4-4D1D-40F2-AC8E-F94C9042F42C@.microsoft.com...
>> > Folks,
>> >
>> > I have a table with a clustered index and 6 non-clustered indexes. When
>> > I
>> > issue a DBCC DBREINDEX without specifying a specific index all indexes
>> > are
>> > rebuilt including the clustered index - this takes 3 hours to complete
>> > and
>> > consumes 9gb of TLog space. When I issue a DBCC DBREINDEX specifying
>> > only
>> > the
>> > clustered index (which happens to be supporting the primary key) I've
>> > read
>> > that all non-clustered indexes are also rebuilt - this takes on 1.5
>> > hours
>> > and
>> > consumes 6gb of Tlog space. I have 3 questions:
>> >
>> > 1) If you use DBCC DBREINDEX without specifying an index, are the
>> > non-clustered indexes rebuilt twice on a table with a clustered index?
>> >
>> > 2) I would like to confirm that all indexes are being rebuilt. Is the
>> > time
>> > the index is created captured, if so, how can I view the time?
>> >
>> > 3) Why is this a logged transaction? I would have thought that SQL
>> > Server
>> > would create the indexes without dropping the originals, and then
>> > simply
>> > swap
>> > and drop once the index has completed.
>> >
>> > Thanks in advance for your help.
>> > --
>> > Scott H.
>>|||If your team still doesn't believe you, send me email (through the blog
below) and I'll have a con-call with you/them and convince them (I wrote
DBCC INDEXDEFRAG and SHOWCONTIG).
Cheers
--
Paul Randal
Principal Lead Program Manager
Microsoft SQL Server Core Storage Engine,
http://blogs.msdn.com/sqlserverstorageengine/default.aspx
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:eHLY5EwbHHA.4176@.TK2MSFTNGP02.phx.gbl...
> Scott,
> I second Kalen's advise about using Bulklogged vs Simple if you need to go
> that way. Just remember that the Log backup files will be large regardless
> if you have space issues. Hopefully you are not backing up to the same
> drive array as the data is on anyway. But most systems rarely require an
> index rebuild nightly and certainly not for all tables in the db. There is
> a sample script in BooksOnLine under DBCC SHOWCONTIG that will allow you
> to only reindex or Defrag the indexes that are above a certain
> fragmentation level anyway. This should dramatically cut down the tlog
> space requirements as well. These articles (especially the first one) are
> worth reading.
>
> http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ss2kidbp.mspx
> Index Defrag Best Practices 2000
> http://www.sql-server-performance.com/dt_dbcc_showcontig.asp Understanding
> DBCC SHOWCONTIG
> http://www.sqlservercentral.com/columnists/jweisbecker/amethodologyfordeterminingfillfactors.asp
> Fill Factors
> http://www.sql-server-performance.com/gv_clustered_indexes.asp Clustered
> Indexes
> --
> Andrew J. Kelly SQL MVP
> "Scott H." <ScottH@.discussions.microsoft.com> wrote in message
> news:6355DB27-5D19-4A8F-A32D-5A2588D275D7@.microsoft.com...
>> Thanks Andrew.
>> SQL Server 2000 SP3
>> The CI is unique, so it would appear to be rebuilding only the CI.
>> I re-ran the test in simple recovery mode, and the TLOG grew to only
>> 190mb.
>> I guess in production I could put the server into single user mode,
>> change to
>> simple recovery mode, run the reorg, full backup, return to full rcovery
>> mode. Seems like overkill. The reason I may follow this route is that we
>> are
>> having disk capacity issues.
>> I have an application team thinking that rebuilding (DBREINDEX) this 17
>> million record table is a good thing to do nightly 7 days/week. I need to
>> dig
>> up supporting evidence one way or the other. I'll make use of SHOWCONTIG
>> prior to the execution over the next few days to determine. If there is
>> anything else you think could be beneficial feel free to offer. I've
>> recently
>> inherited the support of this instaance and there is much work to do.
>> Thanks for your help.
>> --
>> Thanks,
>> Scott H.
>>
>> "Andrew J. Kelly" wrote:
>> It really helps to specify what version and service pack you are using.
>> In
>> this case it can make a big difference. If I remember correctly in the
>> RTM
>> and maybe SP1 versions of SQL2000 it worked like this:
>> If you rebuild the clustered index ( CI ) all non-clustered indexes (
>> NCI)
>> were always rebuilt as well. This was due to the way in which the CI key
>> was
>> appended to the end of all NCI's and would change during a rebuild.
>> With one of the SP's (I think SP2) that behavior changed in that if the
>> CI
>> was unique and you rebuilt the CI by specifying only that index it did
>> not
>> rebuild the NCI's. But if the CI was not unique they would add a
>> uniquifer
>> (4 byte code) to the CI which got regenerated each time the CI was
>> rebuilt.
>> Since the CI (including the uniqueifier) was appended to the end of all
>> NCI's they in turn needed to be rebuilt as well.
>> IN SQL2005 they changed the way they generated the uniqifier and it no
>> longer changes when the CI is rebuilt. So there is no need to rebuild
>> all
>> the NCI's just because you rebuild the CI.
>> But to answer your specific questions a little more:
>> > 1) If you use DBCC DBREINDEX without specifying an index, are the
>> > non-clustered indexes rebuilt twice on a table with a clustered index?
>> No. SQL Server was smart enough in all versions to only rebuild the
>> NCI's
>> once.
>> > 2) I would like to confirm that all indexes are being rebuilt. Is the
>> > time
>> > the index is created captured, if so, how can I view the time?
>> No but you can look at DBCC SHOWCONTIG or SHOWSTATISTICS before and
>> after
>> you can see the differences and will see where to look to see if work
>> was
>> done.
>> > 3) Why is this a logged transaction? I would have thought that SQL
>> > Server
>> > would create the indexes without dropping the originals, and then
>> > simply
>> > swap
>> > and drop once the index has completed.
>> If you are in FULL recovery mode everything is always fully logged. If
>> you
>> are in Bulk-Logged or Simple mode some index create or rebuild
>> operations
>> can be minimally logged. So your transaction log file may not grow very
>> much
>> but when you backup the log file (if in Bulk-logged) the backup will
>> include
>> all the extents changed byt he operations.
>> --
>> Andrew J. Kelly SQL MVP
>> "Scott H." <ScottH@.discussions.microsoft.com> wrote in message
>> news:A76DD6A4-4D1D-40F2-AC8E-F94C9042F42C@.microsoft.com...
>> > Folks,
>> >
>> > I have a table with a clustered index and 6 non-clustered indexes.
>> > When I
>> > issue a DBCC DBREINDEX without specifying a specific index all indexes
>> > are
>> > rebuilt including the clustered index - this takes 3 hours to complete
>> > and
>> > consumes 9gb of TLog space. When I issue a DBCC DBREINDEX specifying
>> > only
>> > the
>> > clustered index (which happens to be supporting the primary key) I've
>> > read
>> > that all non-clustered indexes are also rebuilt - this takes on 1.5
>> > hours
>> > and
>> > consumes 6gb of Tlog space. I have 3 questions:
>> >
>> > 1) If you use DBCC DBREINDEX without specifying an index, are the
>> > non-clustered indexes rebuilt twice on a table with a clustered index?
>> >
>> > 2) I would like to confirm that all indexes are being rebuilt. Is the
>> > time
>> > the index is created captured, if so, how can I view the time?
>> >
>> > 3) Why is this a logged transaction? I would have thought that SQL
>> > Server
>> > would create the indexes without dropping the originals, and then
>> > simply
>> > swap
>> > and drop once the index has completed.
>> >
>> > Thanks in advance for your help.
>> > --
>> > Scott H.
>>
>|||Just to piggy back on this thread. I didn't realise switching from
full to bulk logged would keep the transaction log chain active.
Makes interesting reading considering we do reindexing once a week.
We then have to feed 5 reporting servers via a log shipping type of
system (written internally).
Cheers,
Clive|||Andrew/Kalen/Paul,
Thank you all for your responses. What a great user group (I'd almost
forgotten). I've been off on other assignements these past few years and am
now just getting my hands "dirty" once again with SQL Server. I have
forgotten much, but am having a great time getting back into it. I spent the
entire weekend reading/testing/playing.
Andrew, I did find that script in the BOL. I do plan on using it in its
entirely, but also stole a chunk out of it and modified it slightly. I'm
going to use it to collect and archive these stats for all
instances/databases daily.
Paul, great offer. I work for a large outsourcing company, this particular
client can be a little difficult at times to convince. I hope the information
I collect using procAutoIndex is enough, if it's not I may just take you up
on the offer. I'll share my interpretation of the results (for one or two
tables) once I get them. Perhaps you can tell me if I'm correct or not.
Kalen, I'm going to work towards automating this procedure including putting
the database into bulk-logged mode. Hopefuly this will become a weekly
procedure and can be done during the weekend where impact is reduced.
By the way, I very much enjoy your articles in SSM. Thank-you.
--
Thanks,
Scott H.
"Scott H." wrote:
> Folks,
> I have a table with a clustered index and 6 non-clustered indexes. When I
> issue a DBCC DBREINDEX without specifying a specific index all indexes are
> rebuilt including the clustered index - this takes 3 hours to complete and
> consumes 9gb of TLog space. When I issue a DBCC DBREINDEX specifying only the
> clustered index (which happens to be supporting the primary key) I've read
> that all non-clustered indexes are also rebuilt - this takes on 1.5 hours and
> consumes 6gb of Tlog space. I have 3 questions:
> 1) If you use DBCC DBREINDEX without specifying an index, are the
> non-clustered indexes rebuilt twice on a table with a clustered index?
> 2) I would like to confirm that all indexes are being rebuilt. Is the time
> the index is created captured, if so, how can I view the time?
> 3) Why is this a logged transaction? I would have thought that SQL Server
> would create the indexes without dropping the originals, and then simply swap
> and drop once the index has completed.
> Thanks in advance for your help.
> --
> Scott H.|||Thank-you Kalen.
--
Scott H.
"Kalen Delaney" wrote:
> Hi Scott
> You might want to consider switching to bulk_logged mode instead of simple.
> The logging should be about the same, and you won't have to do a full db
> backup after, on a tlog backup. Situations like this are exactly what
> bulk_logged mode is intended for, i.e. so that you can do large bulk
> operations, like data loads or index rebuilds, that normally are log
> intensive, and make then less log intensive. Switching to bulk_logged mode
> allows your chain of tlog backups to remain intact and again, no full backup
> is required.
> You might want to read more details about the different recovery models in
> the Books Online and also this KB article might help:
> A transaction log grows unexpectedly or becomes full on a computer that is
> running SQL Server
> http://support.microsoft.com/kb/317375/en-us
> --
> HTH
> Kalen Delaney, SQL Server MVP
> http://sqlblog.com
>
> "Scott H." <ScottH@.discussions.microsoft.com> wrote in message
> news:6355DB27-5D19-4A8F-A32D-5A2588D275D7@.microsoft.com...
> > Thanks Andrew.
> >
> > SQL Server 2000 SP3
> >
> > The CI is unique, so it would appear to be rebuilding only the CI.
> >
> > I re-ran the test in simple recovery mode, and the TLOG grew to only
> > 190mb.
> > I guess in production I could put the server into single user mode, change
> > to
> > simple recovery mode, run the reorg, full backup, return to full rcovery
> > mode. Seems like overkill. The reason I may follow this route is that we
> > are
> > having disk capacity issues.
> >
> > I have an application team thinking that rebuilding (DBREINDEX) this 17
> > million record table is a good thing to do nightly 7 days/week. I need to
> > dig
> > up supporting evidence one way or the other. I'll make use of SHOWCONTIG
> > prior to the execution over the next few days to determine. If there is
> > anything else you think could be beneficial feel free to offer. I've
> > recently
> > inherited the support of this instaance and there is much work to do.
> >
> > Thanks for your help.
> > --
> > Thanks,
> >
> > Scott H.
> >
> >
> > "Andrew J. Kelly" wrote:
> >
> >> It really helps to specify what version and service pack you are using.
> >> In
> >> this case it can make a big difference. If I remember correctly in the
> >> RTM
> >> and maybe SP1 versions of SQL2000 it worked like this:
> >>
> >> If you rebuild the clustered index ( CI ) all non-clustered indexes (
> >> NCI)
> >> were always rebuilt as well. This was due to the way in which the CI key
> >> was
> >> appended to the end of all NCI's and would change during a rebuild.
> >>
> >> With one of the SP's (I think SP2) that behavior changed in that if the
> >> CI
> >> was unique and you rebuilt the CI by specifying only that index it did
> >> not
> >> rebuild the NCI's. But if the CI was not unique they would add a
> >> uniquifer
> >> (4 byte code) to the CI which got regenerated each time the CI was
> >> rebuilt.
> >> Since the CI (including the uniqueifier) was appended to the end of all
> >> NCI's they in turn needed to be rebuilt as well.
> >>
> >> IN SQL2005 they changed the way they generated the uniqifier and it no
> >> longer changes when the CI is rebuilt. So there is no need to rebuild all
> >> the NCI's just because you rebuild the CI.
> >>
> >> But to answer your specific questions a little more:
> >>
> >> > 1) If you use DBCC DBREINDEX without specifying an index, are the
> >> > non-clustered indexes rebuilt twice on a table with a clustered index?
> >>
> >> No. SQL Server was smart enough in all versions to only rebuild the NCI's
> >> once.
> >>
> >> > 2) I would like to confirm that all indexes are being rebuilt. Is the
> >> > time
> >> > the index is created captured, if so, how can I view the time?
> >>
> >> No but you can look at DBCC SHOWCONTIG or SHOWSTATISTICS before and after
> >> you can see the differences and will see where to look to see if work was
> >> done.
> >>
> >> > 3) Why is this a logged transaction? I would have thought that SQL
> >> > Server
> >> > would create the indexes without dropping the originals, and then
> >> > simply
> >> > swap
> >> > and drop once the index has completed.
> >>
> >> If you are in FULL recovery mode everything is always fully logged. If
> >> you
> >> are in Bulk-Logged or Simple mode some index create or rebuild operations
> >> can be minimally logged. So your transaction log file may not grow very
> >> much
> >> but when you backup the log file (if in Bulk-logged) the backup will
> >> include
> >> all the extents changed byt he operations.
> >>
> >> --
> >> Andrew J. Kelly SQL MVP
> >>
> >> "Scott H." <ScottH@.discussions.microsoft.com> wrote in message
> >> news:A76DD6A4-4D1D-40F2-AC8E-F94C9042F42C@.microsoft.com...
> >> > Folks,
> >> >
> >> > I have a table with a clustered index and 6 non-clustered indexes. When
> >> > I
> >> > issue a DBCC DBREINDEX without specifying a specific index all indexes
> >> > are
> >> > rebuilt including the clustered index - this takes 3 hours to complete
> >> > and
> >> > consumes 9gb of TLog space. When I issue a DBCC DBREINDEX specifying
> >> > only
> >> > the
> >> > clustered index (which happens to be supporting the primary key) I've
> >> > read
> >> > that all non-clustered indexes are also rebuilt - this takes on 1.5
> >> > hours
> >> > and
> >> > consumes 6gb of Tlog space. I have 3 questions:
> >> >
> >> > 1) If you use DBCC DBREINDEX without specifying an index, are the
> >> > non-clustered indexes rebuilt twice on a table with a clustered index?
> >> >
> >> > 2) I would like to confirm that all indexes are being rebuilt. Is the
> >> > time
> >> > the index is created captured, if so, how can I view the time?
> >> >
> >> > 3) Why is this a logged transaction? I would have thought that SQL
> >> > Server
> >> > would create the indexes without dropping the originals, and then
> >> > simply
> >> > swap
> >> > and drop once the index has completed.
> >> >
> >> > Thanks in advance for your help.
> >> > --
> >> > Scott H.
> >>
> >>
> >>
>
>

Tuesday, March 27, 2012

DBCC SHRINKDATABASE

DBCC SHRINKDATABASE

DBCC SHRINKDATABASE

Sunday, March 11, 2012

DBCC INDEXDEFRAG aquires exclusive locks on a table

Thursday, March 8, 2012

dbcc error 2511

Wednesday, March 7, 2012

DBCC DBREINDEX unexpected results

DBCC DBREINDEX Behaviour

DBCC DBREINDEX Behaviour

DBCC DBREINDEX Behaviour

DBfile usage

Blog Archive

About Me