Deadlocks on Queries

Hi,
I have a situation where I have inherited a system that is being
stress tested at the moment. The main table used in the DB has a
number of indexes. The problem I am having is that under load, I keep
getting deadlocks on iwhat appears to be the indexes on the tables.
More specifically a row appears to be inserted / updated, and another
select query is being locked out when trying to query that table.
I haven't touched fill factors (they are still the default zero). Any
pointers / reading material would be appreciated.
Check if your query includes BEGIN TRAN and COMMIT TRAN at proper place
"Spondishy" wrote:

> Hi,
> I have a situation where I have inherited a system that is being
> stress tested at the moment. The main table used in the DB has a
> number of indexes. The problem I am having is that under load, I keep
> getting deadlocks on iwhat appears to be the indexes on the tables.
> More specifically a row appears to be inserted / updated, and another
> select query is being locked out when trying to query that table.
> I haven't touched fill factors (they are still the default zero). Any
> pointers / reading material would be appreciated.
>
sql

Deadlocks on Queries

Hi,
I have a situation where I have inherited a system that is being
stress tested at the moment. The main table used in the DB has a
number of indexes. The problem I am having is that under load, I keep
getting deadlocks on iwhat appears to be the indexes on the tables.
More specifically a row appears to be inserted / updated, and another
select query is being locked out when trying to query that table.
I haven't touched fill factors (they are still the default zero). Any
pointers / reading material would be appreciated.Check if your query includes BEGIN TRAN and COMMIT TRAN at proper place
"Spondishy" wrote:

> Hi,
> I have a situation where I have inherited a system that is being
> stress tested at the moment. The main table used in the DB has a
> number of indexes. The problem I am having is that under load, I keep
> getting deadlocks on iwhat appears to be the indexes on the tables.
> More specifically a row appears to be inserted / updated, and another
> select query is being locked out when trying to query that table.
> I haven't touched fill factors (they are still the default zero). Any
> pointers / reading material would be appreciated.
>

Deadlocking on indexes?

It seems that a common problem we have is deadlocks occurring when one
stored proc is updating a row and another store proc is running a read query
against the same table. They'll often get deadlocked on two of the indexes
for the table.
For example:
SP1 is updating a row in TableA, causing it to get an exclusive lock on
TableAIndex1 and attempts to get an exclusive lock on TableAIndex2.
SP2 is reading TableA, causing it to get a shared lock on TableAIndex2 and
attempts to get a shared lock on TableAIndex1.
Deadlock!
Of course, I can fix this by lowering the isolation level in SP2 to "read
uncommitted", but I'd rather not.
I can normally fix deadlocks by changing the order in which locks are
acquired, but I don't know how to influence the order in which locks on
indexes are acquired. Is it controlled by the order in which the fields are
listed in a select or an update statement? If I standardize the order in
which individual fields are listed whenever I perform a select or update
from a given table, will that effect thr order in which index locks are
acquired?
Thoughts?
Joel
The order in which the columns appear in the query are not tied directly to
how the engine decides to take locks. Why is the query trying to use two
indexes on the same table? Is this two separate queries or is it doing
index intersection? If it is the latter you might want to see if adding the
column to the first index will solve the problem.
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"Joel Lyons" <JoelL@.novarad.net> wrote in message
news:4E93C8A1-6EAC-4522-9CB9-1CECEC9687C3@.microsoft.com...
> It seems that a common problem we have is deadlocks occurring when one
> stored proc is updating a row and another store proc is running a read
> query against the same table. They'll often get deadlocked on two of the
> indexes for the table.
> For example:
> SP1 is updating a row in TableA, causing it to get an exclusive lock on
> TableAIndex1 and attempts to get an exclusive lock on TableAIndex2.
> SP2 is reading TableA, causing it to get a shared lock on TableAIndex2 and
> attempts to get a shared lock on TableAIndex1.
> Deadlock!
> Of course, I can fix this by lowering the isolation level in SP2 to "read
> uncommitted", but I'd rather not.
> I can normally fix deadlocks by changing the order in which locks are
> acquired, but I don't know how to influence the order in which locks on
> indexes are acquired. Is it controlled by the order in which the fields
> are listed in a select or an update statement? If I standardize the order
> in which individual fields are listed whenever I perform a select or
> update from a given table, will that effect thr order in which index locks
> are acquired?
> Thoughts?
> Joel
|||One way to solve this would be to put a copy of the same Select statement
used in the SP1 for the update at the beginning of the SP2. This way, SP2
will have to first acquire the shared lock in the same order as SP1; hence
solving (I hope!) your deadlocking problem.
It has been a long time since the last time that I had to work on a locking
problem but I remember a suggestion whose idea was to put at the beginning
of a SP one or more queries with the purpose of acquiring a list of locks in
the same order as for the other SPs. At first, you might think that making
these queries will have some negative impact on the overall performance but
don't forget that most (if not all) of this stuff must be read from the I/O
and put into memory/buffer anyway. Acquiring all the locks in the exact
right order is more important than to save a few I/O or a few CPU cycles.
Don't know if there is a better way of solving this kind of problem.
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: sylvain aei ca (fill the blanks, no spam please)
"Joel Lyons" <JoelL@.novarad.net> wrote in message
news:4E93C8A1-6EAC-4522-9CB9-1CECEC9687C3@.microsoft.com...
> It seems that a common problem we have is deadlocks occurring when one
> stored proc is updating a row and another store proc is running a read
> query against the same table. They'll often get deadlocked on two of the
> indexes for the table.
> For example:
> SP1 is updating a row in TableA, causing it to get an exclusive lock on
> TableAIndex1 and attempts to get an exclusive lock on TableAIndex2.
> SP2 is reading TableA, causing it to get a shared lock on TableAIndex2 and
> attempts to get a shared lock on TableAIndex1.
> Deadlock!
> Of course, I can fix this by lowering the isolation level in SP2 to "read
> uncommitted", but I'd rather not.
> I can normally fix deadlocks by changing the order in which locks are
> acquired, but I don't know how to influence the order in which locks on
> indexes are acquired. Is it controlled by the order in which the fields
> are listed in a select or an update statement? If I standardize the order
> in which individual fields are listed whenever I perform a select or
> update from a given table, will that effect thr order in which index locks
> are acquired?
> Thoughts?
> Joel
|||Joel
In addition to others , if you run SQL Server 2005 , let DTA (Tunning
Advisor) to reccomed you what indexes are missed.
"Joel Lyons" <JoelL@.novarad.net> wrote in message
news:4E93C8A1-6EAC-4522-9CB9-1CECEC9687C3@.microsoft.com...
> It seems that a common problem we have is deadlocks occurring when one
> stored proc is updating a row and another store proc is running a read
> query against the same table. They'll often get deadlocked on two of the
> indexes for the table.
> For example:
> SP1 is updating a row in TableA, causing it to get an exclusive lock on
> TableAIndex1 and attempts to get an exclusive lock on TableAIndex2.
> SP2 is reading TableA, causing it to get a shared lock on TableAIndex2 and
> attempts to get a shared lock on TableAIndex1.
> Deadlock!
> Of course, I can fix this by lowering the isolation level in SP2 to "read
> uncommitted", but I'd rather not.
> I can normally fix deadlocks by changing the order in which locks are
> acquired, but I don't know how to influence the order in which locks on
> indexes are acquired. Is it controlled by the order in which the fields
> are listed in a select or an update statement? If I standardize the order
> in which individual fields are listed whenever I perform a select or
> update from a given table, will that effect thr order in which index locks
> are acquired?
> Thoughts?
> Joel
|||Joe,
many things just have said by others like replications, keep only used index,
etc, but I've said one more: FILLFACTOR.
If you have a very busy OLTP database, you can controll lock at least to
minimun with appropriate fillfactor.
I administrate many OLTP servers anda database and after read many articles
and try to find a baseline to controll lock. Finally, I've used these
templates with excelent result : Recreate every clustered index with
fillfactor 80% and recreate every nonclustered index with 90%.
You can increase or decrease this value that depends if you have updated
tables that need more, but stay with a little less in clustered than
noclustered.
Try this !
Kris
Joel Lyons wrote:
>It seems that a common problem we have is deadlocks occurring when one
>stored proc is updating a row and another store proc is running a read query
>against the same table. They'll often get deadlocked on two of the indexes
>for the table.
>For example:
>SP1 is updating a row in TableA, causing it to get an exclusive lock on
>TableAIndex1 and attempts to get an exclusive lock on TableAIndex2.
>SP2 is reading TableA, causing it to get a shared lock on TableAIndex2 and
>attempts to get a shared lock on TableAIndex1.
>Deadlock!
>Of course, I can fix this by lowering the isolation level in SP2 to "read
>uncommitted", but I'd rather not.
>I can normally fix deadlocks by changing the order in which locks are
>acquired, but I don't know how to influence the order in which locks on
>indexes are acquired. Is it controlled by the order in which the fields are
>listed in a select or an update statement? If I standardize the order in
>which individual fields are listed whenever I perform a select or update
>from a given table, will that effect thr order in which index locks are
>acquired?
>Thoughts?
>Joel
Message posted via droptable.com
http://www.droptable.com/Uwe/Forums.aspx/sql-server/200804/1
|||Wow! Those are some great ideas! I'll look into them further. Thank you.
"Joel Lyons" <JoelL@.novarad.net> wrote in message
news:4E93C8A1-6EAC-4522-9CB9-1CECEC9687C3@.microsoft.com...
> It seems that a common problem we have is deadlocks occurring when one
> stored proc is updating a row and another store proc is running a read
> query against the same table. They'll often get deadlocked on two of the
> indexes for the table.
> For example:
> SP1 is updating a row in TableA, causing it to get an exclusive lock on
> TableAIndex1 and attempts to get an exclusive lock on TableAIndex2.
> SP2 is reading TableA, causing it to get a shared lock on TableAIndex2 and
> attempts to get a shared lock on TableAIndex1.
> Deadlock!
> Of course, I can fix this by lowering the isolation level in SP2 to "read
> uncommitted", but I'd rather not.
> I can normally fix deadlocks by changing the order in which locks are
> acquired, but I don't know how to influence the order in which locks on
> indexes are acquired. Is it controlled by the order in which the fields
> are listed in a select or an update statement? If I standardize the order
> in which individual fields are listed whenever I perform a select or
> update from a given table, will that effect thr order in which index locks
> are acquired?
> Thoughts?
> Joel
|||Here is, IMHO, the bible for deadlock troubleshooting:
Kevin G. Boles
Indicium Resources, Inc.
SQL Server MVP
kgboles a earthlink dt net
"Joel Lyons" <JoelL@.novarad.net> wrote in message
news:4E93C8A1-6EAC-4522-9CB9-1CECEC9687C3@.microsoft.com...
> It seems that a common problem we have is deadlocks occurring when one
> stored proc is updating a row and another store proc is running a read
> query against the same table. They'll often get deadlocked on two of the
> indexes for the table.
> For example:
> SP1 is updating a row in TableA, causing it to get an exclusive lock on
> TableAIndex1 and attempts to get an exclusive lock on TableAIndex2.
> SP2 is reading TableA, causing it to get a shared lock on TableAIndex2 and
> attempts to get a shared lock on TableAIndex1.
> Deadlock!
> Of course, I can fix this by lowering the isolation level in SP2 to "read
> uncommitted", but I'd rather not.
> I can normally fix deadlocks by changing the order in which locks are
> acquired, but I don't know how to influence the order in which locks on
> indexes are acquired. Is it controlled by the order in which the fields
> are listed in a select or an update statement? If I standardize the order
> in which individual fields are listed whenever I perform a select or
> update from a given table, will that effect thr order in which index locks
> are acquired?
> Thoughts?
> Joel
|||slip of the finger. here is the link:
http://blogs.msdn.com/bartd/archive/2006/09/09/Deadlock-Troubleshooting_2C00_-Part-1.aspx
Kevin G. Boles
Indicium Resources, Inc.
SQL Server MVP
kgboles a earthlink dt net
"TheSQLGuru" <kgboles@.earthlink.net> wrote in message
news:8aSdnTZNUZEe02vanZ2dnUVZ_jWdnZ2d@.earthlink.co m...
> Here is, IMHO, the bible for deadlock troubleshooting:
>
> --
> Kevin G. Boles
> Indicium Resources, Inc.
> SQL Server MVP
> kgboles a earthlink dt net
>
> "Joel Lyons" <JoelL@.novarad.net> wrote in message
> news:4E93C8A1-6EAC-4522-9CB9-1CECEC9687C3@.microsoft.com...
>
sql

Deadlocking on indexes?

It seems that a common problem we have is deadlocks occurring when one
stored proc is updating a row and another store proc is running a read query
against the same table. They'll often get deadlocked on two of the indexes
for the table.
For example:
SP1 is updating a row in TableA, causing it to get an exclusive lock on
TableAIndex1 and attempts to get an exclusive lock on TableAIndex2.
SP2 is reading TableA, causing it to get a shared lock on TableAIndex2 and
attempts to get a shared lock on TableAIndex1.
Deadlock!
Of course, I can fix this by lowering the isolation level in SP2 to "read
uncommitted", but I'd rather not.
I can normally fix deadlocks by changing the order in which locks are
acquired, but I don't know how to influence the order in which locks on
indexes are acquired. Is it controlled by the order in which the fields are
listed in a select or an update statement? If I standardize the order in
which individual fields are listed whenever I perform a select or update
from a given table, will that effect thr order in which index locks are
acquired?
Thoughts?
JoelThe order in which the columns appear in the query are not tied directly to
how the engine decides to take locks. Why is the query trying to use two
indexes on the same table? Is this two separate queries or is it doing
index intersection? If it is the latter you might want to see if adding the
column to the first index will solve the problem.
--
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"Joel Lyons" <JoelL@.novarad.net> wrote in message
news:4E93C8A1-6EAC-4522-9CB9-1CECEC9687C3@.microsoft.com...
> It seems that a common problem we have is deadlocks occurring when one
> stored proc is updating a row and another store proc is running a read
> query against the same table. They'll often get deadlocked on two of the
> indexes for the table.
> For example:
> SP1 is updating a row in TableA, causing it to get an exclusive lock on
> TableAIndex1 and attempts to get an exclusive lock on TableAIndex2.
> SP2 is reading TableA, causing it to get a shared lock on TableAIndex2 and
> attempts to get a shared lock on TableAIndex1.
> Deadlock!
> Of course, I can fix this by lowering the isolation level in SP2 to "read
> uncommitted", but I'd rather not.
> I can normally fix deadlocks by changing the order in which locks are
> acquired, but I don't know how to influence the order in which locks on
> indexes are acquired. Is it controlled by the order in which the fields
> are listed in a select or an update statement? If I standardize the order
> in which individual fields are listed whenever I perform a select or
> update from a given table, will that effect thr order in which index locks
> are acquired?
> Thoughts?
> Joel|||One way to solve this would be to put a copy of the same Select statement
used in the SP1 for the update at the beginning of the SP2. This way, SP2
will have to first acquire the shared lock in the same order as SP1; hence
solving (I hope!) your deadlocking problem.
It has been a long time since the last time that I had to work on a locking
problem but I remember a suggestion whose idea was to put at the beginning
of a SP one or more queries with the purpose of acquiring a list of locks in
the same order as for the other SPs. At first, you might think that making
these queries will have some negative impact on the overall performance but
don't forget that most (if not all) of this stuff must be read from the I/O
and put into memory/buffer anyway. Acquiring all the locks in the exact
right order is more important than to save a few I/O or a few CPU cycles.
Don't know if there is a better way of solving this kind of problem.
--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: sylvain aei ca (fill the blanks, no spam please)
"Joel Lyons" <JoelL@.novarad.net> wrote in message
news:4E93C8A1-6EAC-4522-9CB9-1CECEC9687C3@.microsoft.com...
> It seems that a common problem we have is deadlocks occurring when one
> stored proc is updating a row and another store proc is running a read
> query against the same table. They'll often get deadlocked on two of the
> indexes for the table.
> For example:
> SP1 is updating a row in TableA, causing it to get an exclusive lock on
> TableAIndex1 and attempts to get an exclusive lock on TableAIndex2.
> SP2 is reading TableA, causing it to get a shared lock on TableAIndex2 and
> attempts to get a shared lock on TableAIndex1.
> Deadlock!
> Of course, I can fix this by lowering the isolation level in SP2 to "read
> uncommitted", but I'd rather not.
> I can normally fix deadlocks by changing the order in which locks are
> acquired, but I don't know how to influence the order in which locks on
> indexes are acquired. Is it controlled by the order in which the fields
> are listed in a select or an update statement? If I standardize the order
> in which individual fields are listed whenever I perform a select or
> update from a given table, will that effect thr order in which index locks
> are acquired?
> Thoughts?
> Joel|||Joel
In addition to others , if you run SQL Server 2005 , let DTA (Tunning
Advisor) to reccomed you what indexes are missed.
"Joel Lyons" <JoelL@.novarad.net> wrote in message
news:4E93C8A1-6EAC-4522-9CB9-1CECEC9687C3@.microsoft.com...
> It seems that a common problem we have is deadlocks occurring when one
> stored proc is updating a row and another store proc is running a read
> query against the same table. They'll often get deadlocked on two of the
> indexes for the table.
> For example:
> SP1 is updating a row in TableA, causing it to get an exclusive lock on
> TableAIndex1 and attempts to get an exclusive lock on TableAIndex2.
> SP2 is reading TableA, causing it to get a shared lock on TableAIndex2 and
> attempts to get a shared lock on TableAIndex1.
> Deadlock!
> Of course, I can fix this by lowering the isolation level in SP2 to "read
> uncommitted", but I'd rather not.
> I can normally fix deadlocks by changing the order in which locks are
> acquired, but I don't know how to influence the order in which locks on
> indexes are acquired. Is it controlled by the order in which the fields
> are listed in a select or an update statement? If I standardize the order
> in which individual fields are listed whenever I perform a select or
> update from a given table, will that effect thr order in which index locks
> are acquired?
> Thoughts?
> Joel|||Joe,
many things just have said by others like replications, keep only used index,
etc, but I've said one more: FILLFACTOR.
If you have a very busy OLTP database, you can controll lock at least to
minimun with appropriate fillfactor.
I administrate many OLTP servers anda database and after read many articles
and try to find a baseline to controll lock. Finally, I've used these
templates with excelent result : Recreate every clustered index with
fillfactor 80% and recreate every nonclustered index with 90%.
You can increase or decrease this value that depends if you have updated
tables that need more, but stay with a little less in clustered than
noclustered.
Try this !
Kris
Joel Lyons wrote:
>It seems that a common problem we have is deadlocks occurring when one
>stored proc is updating a row and another store proc is running a read query
>against the same table. They'll often get deadlocked on two of the indexes
>for the table.
>For example:
>SP1 is updating a row in TableA, causing it to get an exclusive lock on
>TableAIndex1 and attempts to get an exclusive lock on TableAIndex2.
>SP2 is reading TableA, causing it to get a shared lock on TableAIndex2 and
>attempts to get a shared lock on TableAIndex1.
>Deadlock!
>Of course, I can fix this by lowering the isolation level in SP2 to "read
>uncommitted", but I'd rather not.
>I can normally fix deadlocks by changing the order in which locks are
>acquired, but I don't know how to influence the order in which locks on
>indexes are acquired. Is it controlled by the order in which the fields are
>listed in a select or an update statement? If I standardize the order in
>which individual fields are listed whenever I perform a select or update
>from a given table, will that effect thr order in which index locks are
>acquired?
>Thoughts?
>Joel
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200804/1|||Wow! Those are some great ideas! I'll look into them further. Thank you.
"Joel Lyons" <JoelL@.novarad.net> wrote in message
news:4E93C8A1-6EAC-4522-9CB9-1CECEC9687C3@.microsoft.com...
> It seems that a common problem we have is deadlocks occurring when one
> stored proc is updating a row and another store proc is running a read
> query against the same table. They'll often get deadlocked on two of the
> indexes for the table.
> For example:
> SP1 is updating a row in TableA, causing it to get an exclusive lock on
> TableAIndex1 and attempts to get an exclusive lock on TableAIndex2.
> SP2 is reading TableA, causing it to get a shared lock on TableAIndex2 and
> attempts to get a shared lock on TableAIndex1.
> Deadlock!
> Of course, I can fix this by lowering the isolation level in SP2 to "read
> uncommitted", but I'd rather not.
> I can normally fix deadlocks by changing the order in which locks are
> acquired, but I don't know how to influence the order in which locks on
> indexes are acquired. Is it controlled by the order in which the fields
> are listed in a select or an update statement? If I standardize the order
> in which individual fields are listed whenever I perform a select or
> update from a given table, will that effect thr order in which index locks
> are acquired?
> Thoughts?
> Joel|||Here is, IMHO, the bible for deadlock troubleshooting:
Kevin G. Boles
Indicium Resources, Inc.
SQL Server MVP
kgboles a earthlink dt net
"Joel Lyons" <JoelL@.novarad.net> wrote in message
news:4E93C8A1-6EAC-4522-9CB9-1CECEC9687C3@.microsoft.com...
> It seems that a common problem we have is deadlocks occurring when one
> stored proc is updating a row and another store proc is running a read
> query against the same table. They'll often get deadlocked on two of the
> indexes for the table.
> For example:
> SP1 is updating a row in TableA, causing it to get an exclusive lock on
> TableAIndex1 and attempts to get an exclusive lock on TableAIndex2.
> SP2 is reading TableA, causing it to get a shared lock on TableAIndex2 and
> attempts to get a shared lock on TableAIndex1.
> Deadlock!
> Of course, I can fix this by lowering the isolation level in SP2 to "read
> uncommitted", but I'd rather not.
> I can normally fix deadlocks by changing the order in which locks are
> acquired, but I don't know how to influence the order in which locks on
> indexes are acquired. Is it controlled by the order in which the fields
> are listed in a select or an update statement? If I standardize the order
> in which individual fields are listed whenever I perform a select or
> update from a given table, will that effect thr order in which index locks
> are acquired?
> Thoughts?
> Joel|||slip of the finger. here is the link:
http://blogs.msdn.com/bartd/archive/2006/09/09/Deadlock-Troubleshooting_2C00_-Part-1.aspx
--
Kevin G. Boles
Indicium Resources, Inc.
SQL Server MVP
kgboles a earthlink dt net
"TheSQLGuru" <kgboles@.earthlink.net> wrote in message
news:8aSdnTZNUZEe02vanZ2dnUVZ_jWdnZ2d@.earthlink.com...
> Here is, IMHO, the bible for deadlock troubleshooting:
>
> --
> Kevin G. Boles
> Indicium Resources, Inc.
> SQL Server MVP
> kgboles a earthlink dt net
>
> "Joel Lyons" <JoelL@.novarad.net> wrote in message
> news:4E93C8A1-6EAC-4522-9CB9-1CECEC9687C3@.microsoft.com...
>> It seems that a common problem we have is deadlocks occurring when one
>> stored proc is updating a row and another store proc is running a read
>> query against the same table. They'll often get deadlocked on two of the
>> indexes for the table.
>> For example:
>> SP1 is updating a row in TableA, causing it to get an exclusive lock on
>> TableAIndex1 and attempts to get an exclusive lock on TableAIndex2.
>> SP2 is reading TableA, causing it to get a shared lock on TableAIndex2
>> and attempts to get a shared lock on TableAIndex1.
>> Deadlock!
>> Of course, I can fix this by lowering the isolation level in SP2 to "read
>> uncommitted", but I'd rather not.
>> I can normally fix deadlocks by changing the order in which locks are
>> acquired, but I don't know how to influence the order in which locks on
>> indexes are acquired. Is it controlled by the order in which the fields
>> are listed in a select or an update statement? If I standardize the
>> order in which individual fields are listed whenever I perform a select
>> or update from a given table, will that effect thr order in which index
>> locks are acquired?
>> Thoughts?
>> Joel
>

Deadlock on TAB lock

I have a small database and a smalll table ( Table ID=565577053,with two
indexes on this table). when more than one user connected, I got the deadlock on the index KEY and PAGE lock. I setup index with "DisallowRowLock" and "DisallowPageLock" , seems kill the index KEY and PAGE lock problem, but I got this TAB lock situation instead as following:

2006-01-18 09:51:37.87 spid4 -----------
2006-01-18 09:51:37.87 spid4 Starting deadlock search 15

Deadlock encountered ... Printing deadlock information
2006-01-18 09:51:37.87 spid4
2006-01-18 09:51:37.87 spid4 Wait-for graph
2006-01-18 09:51:37.87 spid4
2006-01-18 09:51:37.87 spid4 Node:1
2006-01-18 09:51:37.87 spid4 TAB: 10:565577053 [] CleanCnt:3
Mode: S Flags: 0x0
2006-01-18 09:51:37.87 spid4 Grant List 0::
2006-01-18 09:51:37.87 spid4 Owner:0x42c03ba0 Mode: S Flg:0x0
Ref:2 Life:02000000 SPID:77 ECID:0
2006-01-18 09:51:37.87 spid4 SPID: 77 ECID: 0 Statement Type: DELETE
Line #: 1
2006-01-18 09:51:37.87 spid4 Input Buf: RPC Event: sp_executesql;1
2006-01-18 09:51:37.87 spid4 Requested By:
2006-01-18 09:51:37.87 spid4 ResType:LockOwner Stype:'OR' Mode: IX
SPID:64 ECID:0 Ec:(0x4AEAF530) Value:0x42c0df00 Cost:(0/D4)
2006-01-18 09:51:37.87 spid4
2006-01-18 09:51:37.87 spid4 Node:2
2006-01-18 09:51:37.87 spid4 TAB: 10:565577053 [] CleanCnt:3
Mode: S Flags: 0x0
2006-01-18 09:51:37.87 spid4 Grant List 0::
2006-01-18 09:51:37.87 spid4 Owner:0x42c03e00 Mode: S Flg:0x0
Ref:2 Life:02000000 SPID:64 ECID:0
2006-01-18 09:51:37.87 spid4 SPID: 64 ECID: 0 Statement Type: DELETE
Line #: 1
2006-01-18 09:51:37.87 spid4 Input Buf: RPC Event: sp_executesql;1
2006-01-18 09:51:37.87 spid4 Requested By:
2006-01-18 09:51:37.87 spid4 ResType:LockOwner Stype:'OR' Mode: IX
SPID:77 ECID:0 Ec:(0x4951D530) Value:0x42c03da0 Cost:(0/D4)
2006-01-18 09:51:37.87 spid4 Victim Resource Owner:
2006-01-18 09:51:37.87 spid4 ResType:LockOwner Stype:'OR' Mode: IX
SPID:77 ECID:0 Ec:(0x4951D530) Value:0x42c03da0 Cost:(0/D4)
2006-01-18 09:51:37.87 spid4
2006-01-18 09:51:37.87 spid4 End deadlock search 15 ... a deadlock was
found.
2006-01-18 09:51:37.87 spid4 -----------

How can I get rid of this deadlock without changing the application
code(without using set the isolation level or NOLOCK hint). When I load more
data, will this problem goes away?
Any kind of help will be appreciate.

HansonHow can I get rid of this deadlock without changing the application
code(without using set the isolation level or NOLOCK hint). When I load more
data, will this problem goes away?


The short answers ... You can't and no

More information:
It is the application causing the deadlock, but you only exacerbated the problem by disallowing the row and page locking mechanism. SQL Server will try to take the lowest level lock it needs to accomplish the task. If I have a table with 1 million rows and I need to update 1 row, SQL Server will lock the row (in most cases). If I disallow row locks, it will have to lock the page ... so if there are 100 rows on an 8K page, 100 rows are locked instead of 1. If I then disallow page locks, the next level is a TABLE lock (TAB). So you have really screwed yourself by doing that.

Now to the deadlock ... spid XX locks row 12345 in Table A and needs to lock row 23456 in Table B. spif YY has locked row 23456 in Table B and needs to lock row 12345 in Table A. Each spid is competing for the exact same resource. SQL Server resolves the problem by killing and rolling back one of the spids.

Now your problem could be a spid locking a resource longer than needed, or allowing a user to hold a lock while going off to lunch, or maybe the app accesses the tables in two different sequences ... whatever the problem, it's the app and not the database.

Long term solution ... allow sqlserver to determine the proper locking mechanism and fix the app!|||The short answers ... You can't and no

More information:
It is the application causing the deadlock, but you only exacerbated the problem by disallowing the row and page locking mechanism. SQL Server will try to take the lowest level lock it needs to accomplish the task. If I have a table with 1 million rows and I need to update 1 row, SQL Server will lock the row (in most cases). If I disallow row locks, it will have to lock the page ... so if there are 100 rows on an 8K page, 100 rows are locked instead of 1. If I then disallow page locks, the next level is a TABLE lock (TAB). So you have really screwed yourself by doing that.

Now to the deadlock ... spid XX locks row 12345 in Table A and needs to lock row 23456 in Table B. spif YY has locked row 23456 in Table B and needs to lock row 12345 in Table A. Each spid is competing for the exact same resource. SQL Server resolves the problem by killing and rolling back one of the spids.

Now your problem could be a spid locking a resource longer than needed, or allowing a user to hold a lock while going off to lunch, or maybe the app accesses the tables in two different sequences ... whatever the problem, it's the app and not the database.

Long term solution ... allow sqlserver to determine the proper locking mechanism and fix the app!

tomh53:

Your information is very helpful.
I agree with you that the proper fix should be done on the application rather than database. it is a third party application so I don't have any source code, now the only thing I can do is escalate to them and force them to update the code.

Another approch I like to do is load more data in, make the deadlock less chance happen.

Thanks,

HANSON|||Smells like Access and you're returning all the rows to a form...which should be a shared lock.

We need more background on the application and what you're doing...which doesn't sound good...|||it is a third party application so I don't have any source code, now the only thing I can do is escalate to them and force them to update the code.

You have a 3rd party code that you bought, and it causes deadlocks? Make them fix the damn code.

Can you let us know who they are? They got a home page?|||Why would using this ever be advantageous? I can see where DisAllowPageLock might be helpful, but not this one.|||hmmm...load more data so this won't happen...

You're hired!

Make sure you keep the deep fryers clean when you clock out|||DisAllowRowLock would use fewer lock resources. Systems that have correctly designed applications hitting them and don't have deadlock issues can really benefit from this.|||DisAllowRowLock would use fewer lock resources. Systems that have correctly designed applications hitting them and don't have deadlock issues can really benefit from this.

Really? Would you post your sources for review?|||I don't have current sources, or hard number, but some experience back when it was debated about row-level locking entering SQL Server, and when to use it. Search for "lock escalation" in BOL. Each lock is a small amount of memory, and is something for the server to manage. Normally, the server handles lock escalation in a fairly intelligent manner, but the option is there if you need it. In almost all circumstances letting the server manage the overhead is acceptable. Looking at my post, I shouldn't have implied a big gain. Still the post is correct in that that:

1) DisAllowRowLock would use fewer lock resources.

But

2) Correctly designed applications must be used, or you will have deadlocking issues.

You can easily (as the initial poster did) cause more problems by fiddling with the lock level.

Jay Grubb
Technical Consultant
OpenLink Software
Web: http://www.openlinksw.com:
Product Weblogs:
Virtuoso: http://www.openlinksw.com/weblogs/virtuoso
UDA: http://www.openlinksw.com/weblogs/uda
Universal Data Access & Virtual Database Technology Providers

Deadlock on TAB level

I have a small database and a smalll table ( Table ID=565577053,with two
indexes on this table). when more than one user connected, I got the deadlock
on the index KEY and PAGE lock. I setup index with "DisallowRowLock" and
"DisallowPageLock" , seems kill the index KEY and PAGE lock problem, but I
get to this TAB lock situation instead as following:
2006-01-18 09:51:37.87 spid4 --
2006-01-18 09:51:37.87 spid4 Starting deadlock search 15
Deadlock encountered ... Printing deadlock information
2006-01-18 09:51:37.87 spid4
2006-01-18 09:51:37.87 spid4 Wait-for graph
2006-01-18 09:51:37.87 spid4
2006-01-18 09:51:37.87 spid4 Node:1
2006-01-18 09:51:37.87 spid4 TAB: 10:565577053 [] CleanCnt:3
Mode: S Flags: 0x0
2006-01-18 09:51:37.87 spid4 Grant List 0::
2006-01-18 09:51:37.87 spid4 Owner:0x42c03ba0 Mode: S Flg:0x0
Ref:2 Life:02000000 SPID:77 ECID:0
2006-01-18 09:51:37.87 spid4 SPID: 77 ECID: 0 Statement Type: DELETE
Line #: 1
2006-01-18 09:51:37.87 spid4 Input Buf: RPC Event: sp_executesql;1
2006-01-18 09:51:37.87 spid4 Requested By:
2006-01-18 09:51:37.87 spid4 ResType:LockOwner Stype:'OR' Mode: IX
SPID:64 ECID:0 Ec:(0x4AEAF530) Value:0x42c0df00 Cost:(0/D4)
2006-01-18 09:51:37.87 spid4
2006-01-18 09:51:37.87 spid4 Node:2
2006-01-18 09:51:37.87 spid4 TAB: 10:565577053 [] CleanCnt:3
Mode: S Flags: 0x0
2006-01-18 09:51:37.87 spid4 Grant List 0::
2006-01-18 09:51:37.87 spid4 Owner:0x42c03e00 Mode: S Flg:0x0
Ref:2 Life:02000000 SPID:64 ECID:0
2006-01-18 09:51:37.87 spid4 SPID: 64 ECID: 0 Statement Type: DELETE
Line #: 1
2006-01-18 09:51:37.87 spid4 Input Buf: RPC Event: sp_executesql;1
2006-01-18 09:51:37.87 spid4 Requested By:
2006-01-18 09:51:37.87 spid4 ResType:LockOwner Stype:'OR' Mode: IX
SPID:77 ECID:0 Ec:(0x4951D530) Value:0x42c03da0 Cost:(0/D4)
2006-01-18 09:51:37.87 spid4 Victim Resource Owner:
2006-01-18 09:51:37.87 spid4 ResType:LockOwner Stype:'OR' Mode: IX
SPID:77 ECID:0 Ec:(0x4951D530) Value:0x42c03da0 Cost:(0/D4)
2006-01-18 09:51:37.87 spid4
2006-01-18 09:51:37.87 spid4 End deadlock search 15 ... a deadlock was
found.
2006-01-18 09:51:37.87 spid4 --
How can I get ride of this deadlock without changing the application
code(without using set the isolation level or NOLOCK hint). When I load more
data, will this problem goes away?
Anybody can help to solve this TAB deadlock will be appreciate.
HansenAnybody any suggestion please?
"HG" wrote:
> I have a small database and a smalll table ( Table ID=565577053,with two
> indexes on this table). when more than one user connected, I got the deadlock
> on the index KEY and PAGE lock. I setup index with "DisallowRowLock" and
> "DisallowPageLock" , seems kill the index KEY and PAGE lock problem, but I
> get to this TAB lock situation instead as following:
>
> 2006-01-18 09:51:37.87 spid4 --
> 2006-01-18 09:51:37.87 spid4 Starting deadlock search 15
> Deadlock encountered ... Printing deadlock information
> 2006-01-18 09:51:37.87 spid4
> 2006-01-18 09:51:37.87 spid4 Wait-for graph
> 2006-01-18 09:51:37.87 spid4
> 2006-01-18 09:51:37.87 spid4 Node:1
> 2006-01-18 09:51:37.87 spid4 TAB: 10:565577053 [] CleanCnt:3
> Mode: S Flags: 0x0
> 2006-01-18 09:51:37.87 spid4 Grant List 0::
> 2006-01-18 09:51:37.87 spid4 Owner:0x42c03ba0 Mode: S Flg:0x0
> Ref:2 Life:02000000 SPID:77 ECID:0
> 2006-01-18 09:51:37.87 spid4 SPID: 77 ECID: 0 Statement Type: DELETE
> Line #: 1
> 2006-01-18 09:51:37.87 spid4 Input Buf: RPC Event: sp_executesql;1
> 2006-01-18 09:51:37.87 spid4 Requested By:
> 2006-01-18 09:51:37.87 spid4 ResType:LockOwner Stype:'OR' Mode: IX
> SPID:64 ECID:0 Ec:(0x4AEAF530) Value:0x42c0df00 Cost:(0/D4)
> 2006-01-18 09:51:37.87 spid4
> 2006-01-18 09:51:37.87 spid4 Node:2
> 2006-01-18 09:51:37.87 spid4 TAB: 10:565577053 [] CleanCnt:3
> Mode: S Flags: 0x0
> 2006-01-18 09:51:37.87 spid4 Grant List 0::
> 2006-01-18 09:51:37.87 spid4 Owner:0x42c03e00 Mode: S Flg:0x0
> Ref:2 Life:02000000 SPID:64 ECID:0
> 2006-01-18 09:51:37.87 spid4 SPID: 64 ECID: 0 Statement Type: DELETE
> Line #: 1
> 2006-01-18 09:51:37.87 spid4 Input Buf: RPC Event: sp_executesql;1
> 2006-01-18 09:51:37.87 spid4 Requested By:
> 2006-01-18 09:51:37.87 spid4 ResType:LockOwner Stype:'OR' Mode: IX
> SPID:77 ECID:0 Ec:(0x4951D530) Value:0x42c03da0 Cost:(0/D4)
> 2006-01-18 09:51:37.87 spid4 Victim Resource Owner:
> 2006-01-18 09:51:37.87 spid4 ResType:LockOwner Stype:'OR' Mode: IX
> SPID:77 ECID:0 Ec:(0x4951D530) Value:0x42c03da0 Cost:(0/D4)
> 2006-01-18 09:51:37.87 spid4
> 2006-01-18 09:51:37.87 spid4 End deadlock search 15 ... a deadlock was
> found.
> 2006-01-18 09:51:37.87 spid4 --
> How can I get ride of this deadlock without changing the application
> code(without using set the isolation level or NOLOCK hint). When I load more
> data, will this problem goes away?
> Anybody can help to solve this TAB deadlock will be appreciate.
> Hansen
>|||Have you considered just disallowing page locks but allowing row locks? With
rowlocks disallowed, for your application to do an insert/update/delete it
seems that it would be left with no choice but to take out a table lock.
Question for others: when is a "good" time to disallow row locks? I can't
think of one.
"HG" wrote:
> Anybody any suggestion please?
> "HG" wrote:
> > I have a small database and a smalll table ( Table ID=565577053,with two
> > indexes on this table). when more than one user connected, I got the deadlock
> > on the index KEY and PAGE lock. I setup index with "DisallowRowLock" and
> > "DisallowPageLock" , seems kill the index KEY and PAGE lock problem, but I
> > get to this TAB lock situation instead as following:
> >
> >
> > 2006-01-18 09:51:37.87 spid4 --
> > 2006-01-18 09:51:37.87 spid4 Starting deadlock search 15
> >
> > Deadlock encountered ... Printing deadlock information
> > 2006-01-18 09:51:37.87 spid4
> > 2006-01-18 09:51:37.87 spid4 Wait-for graph
> > 2006-01-18 09:51:37.87 spid4
> > 2006-01-18 09:51:37.87 spid4 Node:1
> > 2006-01-18 09:51:37.87 spid4 TAB: 10:565577053 [] CleanCnt:3
> > Mode: S Flags: 0x0
> > 2006-01-18 09:51:37.87 spid4 Grant List 0::
> > 2006-01-18 09:51:37.87 spid4 Owner:0x42c03ba0 Mode: S Flg:0x0
> > Ref:2 Life:02000000 SPID:77 ECID:0
> > 2006-01-18 09:51:37.87 spid4 SPID: 77 ECID: 0 Statement Type: DELETE
> > Line #: 1
> > 2006-01-18 09:51:37.87 spid4 Input Buf: RPC Event: sp_executesql;1
> > 2006-01-18 09:51:37.87 spid4 Requested By:
> > 2006-01-18 09:51:37.87 spid4 ResType:LockOwner Stype:'OR' Mode: IX
> > SPID:64 ECID:0 Ec:(0x4AEAF530) Value:0x42c0df00 Cost:(0/D4)
> > 2006-01-18 09:51:37.87 spid4
> > 2006-01-18 09:51:37.87 spid4 Node:2
> > 2006-01-18 09:51:37.87 spid4 TAB: 10:565577053 [] CleanCnt:3
> > Mode: S Flags: 0x0
> > 2006-01-18 09:51:37.87 spid4 Grant List 0::
> > 2006-01-18 09:51:37.87 spid4 Owner:0x42c03e00 Mode: S Flg:0x0
> > Ref:2 Life:02000000 SPID:64 ECID:0
> > 2006-01-18 09:51:37.87 spid4 SPID: 64 ECID: 0 Statement Type: DELETE
> > Line #: 1
> > 2006-01-18 09:51:37.87 spid4 Input Buf: RPC Event: sp_executesql;1
> > 2006-01-18 09:51:37.87 spid4 Requested By:
> > 2006-01-18 09:51:37.87 spid4 ResType:LockOwner Stype:'OR' Mode: IX
> > SPID:77 ECID:0 Ec:(0x4951D530) Value:0x42c03da0 Cost:(0/D4)
> > 2006-01-18 09:51:37.87 spid4 Victim Resource Owner:
> > 2006-01-18 09:51:37.87 spid4 ResType:LockOwner Stype:'OR' Mode: IX
> > SPID:77 ECID:0 Ec:(0x4951D530) Value:0x42c03da0 Cost:(0/D4)
> > 2006-01-18 09:51:37.87 spid4
> > 2006-01-18 09:51:37.87 spid4 End deadlock search 15 ... a deadlock was
> > found.
> > 2006-01-18 09:51:37.87 spid4 --
> >
> > How can I get ride of this deadlock without changing the application
> > code(without using set the isolation level or NOLOCK hint). When I load more
> > data, will this problem goes away?
> > Anybody can help to solve this TAB deadlock will be appreciate.
> >
> > Hansen
> >

DDL Extraction

Does anyone know how to pull DDL create statements out of a SQL Server 2005 EE database for existing objects? I'm mainly concerned with indexes and constraints. If possible I can create the statements myself if I can get all of the information out of the databse.

Thanks in advance

Hi,

you can either use the SMO class libraries to use the Script() method on the objects or the GUI of SQL Server Managment Studio which does the same things behind the scenes.

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de|||

Thanks I'll check it out.

I was looking for something that is not using .NET code and is just SQL.

|||You can look in the system views and get all the information you need. INFORMATION_SCHEMA prefixed views are a more denormalized version of the sys.XXX views and will contain most everything you would need to script create statements.

dbTrace to find Index Non-Use - How To?

I read somewhere or heard you can trace for activity on table indexes to
ultimately find where indexes are NOT being used when users perform
searches...
Any idea how to set this up? I don't see events related to indexes...
TIA,
ChrisHi,
Easy method is "Use the Execution Plan" graphical option in Query
Analyzer -- Query option --"Show Execution plan"
Thanks
Hari
SQL Server MVP
"Chris" <Chris@.discussions.microsoft.com> wrote in message
news:F5880B81-77A8-4BC2-92A7-4A1B01BE6EE8@.microsoft.com...
> I read somewhere or heard you can trace for activity on table indexes to
> ultimately find where indexes are NOT being used when users perform
> searches...
> Any idea how to set this up? I don't see events related to indexes...
> TIA,
> Chris|||Chris,
You might try the Index Tuning Wizard.
HTH
Jerry
"Chris" <Chris@.discussions.microsoft.com> wrote in message
news:F5880B81-77A8-4BC2-92A7-4A1B01BE6EE8@.microsoft.com...
> I read somewhere or heard you can trace for activity on table indexes to
> ultimately find where indexes are NOT being used when users perform
> searches...
> Any idea how to set this up? I don't see events related to indexes...
> TIA,
> Chris|||I need to monitor ALL indexes on all db tables, then find those that are NOT
being used... can't do that w/ Query Analyzer show plan, can't do that w/ th
e
tuning wizard. I need to collect this activity either thru a dbTrace or
PerfMon counters...
Any ideas'
"Chris" wrote:

> I read somewhere or heard you can trace for activity on table indexes to
> ultimately find where indexes are NOT being used when users perform
> searches...
> Any idea how to set this up? I don't see events related to indexes...
> TIA,
> Chris|||Chris,
What about ITWIZ?
See:
http://msdn.microsoft.com/library/d...r />
_8p2x.asp
HTH
Jerry
"Chris" <Chris@.discussions.microsoft.com> wrote in message
news:34A64A78-9F16-4851-A5C3-1DB66D7B390A@.microsoft.com...[vbcol=seagreen]
> I need to monitor ALL indexes on all db tables, then find those that are
> NOT
> being used... can't do that w/ Query Analyzer show plan, can't do that w/
> the
> tuning wizard. I need to collect this activity either thru a dbTrace or
> PerfMon counters...
> Any ideas'
> "Chris" wrote:
>|||Capture the execution plan over a relevant time period, parse it, compare ag
ainst the indexes you
have in your tables. Anything in the trace that isn't in sysindexes? There y
ou have it, those
indexes wasn't used by the SQL submitted over that trace. There will be bett
er ways in 2005 to do
this...
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Chris" <Chris@.discussions.microsoft.com> wrote in message
news:34A64A78-9F16-4851-A5C3-1DB66D7B390A@.microsoft.com...[vbcol=seagreen]
> I need to monitor ALL indexes on all db tables, then find those that are N
OT
> being used... can't do that w/ Query Analyzer show plan, can't do that w/
the
> tuning wizard. I need to collect this activity either thru a dbTrace or
> PerfMon counters...
> Any ideas'
> "Chris" wrote:
>|||Hi Chris
I do it by using trace to capture a workload over as long a period of time
as I can, and then run that through the Index Tuning Wizard. ITW generates a
set of reports, one of which is a list of which of your current indexes are
being used what percent of the time.
--
HTH
Kalen Delaney, SQL Server MVP
www.solidqualitylearning.com
"Chris" <Chris@.discussions.microsoft.com> wrote in message
news:F5880B81-77A8-4BC2-92A7-4A1B01BE6EE8@.microsoft.com...
> I read somewhere or heard you can trace for activity on table indexes to
> ultimately find where indexes are NOT being used when users perform
> searches...
> Any idea how to set this up? I don't see events related to indexes...
> TIA,
> Chris
>

dbTrace to find Index Non-Use - How To?

I read somewhere or heard you can trace for activity on table indexes to
ultimately find where indexes are NOT being used when users perform
searches...
Any idea how to set this up? I don't see events related to indexes...
TIA,
Chris
Hi,
Easy method is "Use the Execution Plan" graphical option in Query
Analyzer -- Query option --"Show Execution plan"
Thanks
Hari
SQL Server MVP
"Chris" <Chris@.discussions.microsoft.com> wrote in message
news:F5880B81-77A8-4BC2-92A7-4A1B01BE6EE8@.microsoft.com...
> I read somewhere or heard you can trace for activity on table indexes to
> ultimately find where indexes are NOT being used when users perform
> searches...
> Any idea how to set this up? I don't see events related to indexes...
> TIA,
> Chris
|||Chris,
You might try the Index Tuning Wizard.
HTH
Jerry
"Chris" <Chris@.discussions.microsoft.com> wrote in message
news:F5880B81-77A8-4BC2-92A7-4A1B01BE6EE8@.microsoft.com...
> I read somewhere or heard you can trace for activity on table indexes to
> ultimately find where indexes are NOT being used when users perform
> searches...
> Any idea how to set this up? I don't see events related to indexes...
> TIA,
> Chris
|||I need to monitor ALL indexes on all db tables, then find those that are NOT
being used... can't do that w/ Query Analyzer show plan, can't do that w/ the
tuning wizard. I need to collect this activity either thru a dbTrace or
PerfMon counters...
Any ideas?
"Chris" wrote:

> I read somewhere or heard you can trace for activity on table indexes to
> ultimately find where indexes are NOT being used when users perform
> searches...
> Any idea how to set this up? I don't see events related to indexes...
> TIA,
> Chris
|||Chris,
What about ITWIZ?
See:
http://msdn.microsoft.com/library/de...isqlw_8p2x.asp
HTH
Jerry
"Chris" <Chris@.discussions.microsoft.com> wrote in message
news:34A64A78-9F16-4851-A5C3-1DB66D7B390A@.microsoft.com...[vbcol=seagreen]
> I need to monitor ALL indexes on all db tables, then find those that are
> NOT
> being used... can't do that w/ Query Analyzer show plan, can't do that w/
> the
> tuning wizard. I need to collect this activity either thru a dbTrace or
> PerfMon counters...
> Any ideas?
> "Chris" wrote:
|||Capture the execution plan over a relevant time period, parse it, compare against the indexes you
have in your tables. Anything in the trace that isn't in sysindexes? There you have it, those
indexes wasn't used by the SQL submitted over that trace. There will be better ways in 2005 to do
this...
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Chris" <Chris@.discussions.microsoft.com> wrote in message
news:34A64A78-9F16-4851-A5C3-1DB66D7B390A@.microsoft.com...[vbcol=seagreen]
> I need to monitor ALL indexes on all db tables, then find those that are NOT
> being used... can't do that w/ Query Analyzer show plan, can't do that w/ the
> tuning wizard. I need to collect this activity either thru a dbTrace or
> PerfMon counters...
> Any ideas?
> "Chris" wrote:
|||Hi Chris
I do it by using trace to capture a workload over as long a period of time
as I can, and then run that through the Index Tuning Wizard. ITW generates a
set of reports, one of which is a list of which of your current indexes are
being used what percent of the time.
HTH
Kalen Delaney, SQL Server MVP
www.solidqualitylearning.com
"Chris" <Chris@.discussions.microsoft.com> wrote in message
news:F5880B81-77A8-4BC2-92A7-4A1B01BE6EE8@.microsoft.com...
> I read somewhere or heard you can trace for activity on table indexes to
> ultimately find where indexes are NOT being used when users perform
> searches...
> Any idea how to set this up? I don't see events related to indexes...
> TIA,
> Chris
>

dbTrace to find Index Non-Use - How To?

I read somewhere or heard you can trace for activity on table indexes to
ultimately find where indexes are NOT being used when users perform
searches...
Any idea how to set this up? I don't see events related to indexes...
TIA,
ChrisHi,
Easy method is "Use the Execution Plan" graphical option in Query
Analyzer -- Query option --"Show Execution plan"
Thanks
Hari
SQL Server MVP
"Chris" <Chris@.discussions.microsoft.com> wrote in message
news:F5880B81-77A8-4BC2-92A7-4A1B01BE6EE8@.microsoft.com...
> I read somewhere or heard you can trace for activity on table indexes to
> ultimately find where indexes are NOT being used when users perform
> searches...
> Any idea how to set this up? I don't see events related to indexes...
> TIA,
> Chris|||Chris,
You might try the Index Tuning Wizard.
HTH
Jerry
"Chris" <Chris@.discussions.microsoft.com> wrote in message
news:F5880B81-77A8-4BC2-92A7-4A1B01BE6EE8@.microsoft.com...
> I read somewhere or heard you can trace for activity on table indexes to
> ultimately find where indexes are NOT being used when users perform
> searches...
> Any idea how to set this up? I don't see events related to indexes...
> TIA,
> Chris|||I need to monitor ALL indexes on all db tables, then find those that are NOT
being used... can't do that w/ Query Analyzer show plan, can't do that w/ the
tuning wizard. I need to collect this activity either thru a dbTrace or
PerfMon counters...
Any ideas'
"Chris" wrote:
> I read somewhere or heard you can trace for activity on table indexes to
> ultimately find where indexes are NOT being used when users perform
> searches...
> Any idea how to set this up? I don't see events related to indexes...
> TIA,
> Chris|||Chris,
What about ITWIZ?
See:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/coprompt/cp_isqlw_8p2x.asp
HTH
Jerry
"Chris" <Chris@.discussions.microsoft.com> wrote in message
news:34A64A78-9F16-4851-A5C3-1DB66D7B390A@.microsoft.com...
> I need to monitor ALL indexes on all db tables, then find those that are
> NOT
> being used... can't do that w/ Query Analyzer show plan, can't do that w/
> the
> tuning wizard. I need to collect this activity either thru a dbTrace or
> PerfMon counters...
> Any ideas'
> "Chris" wrote:
>> I read somewhere or heard you can trace for activity on table indexes to
>> ultimately find where indexes are NOT being used when users perform
>> searches...
>> Any idea how to set this up? I don't see events related to indexes...
>> TIA,
>> Chris|||Capture the execution plan over a relevant time period, parse it, compare against the indexes you
have in your tables. Anything in the trace that isn't in sysindexes? There you have it, those
indexes wasn't used by the SQL submitted over that trace. There will be better ways in 2005 to do
this...
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Chris" <Chris@.discussions.microsoft.com> wrote in message
news:34A64A78-9F16-4851-A5C3-1DB66D7B390A@.microsoft.com...
> I need to monitor ALL indexes on all db tables, then find those that are NOT
> being used... can't do that w/ Query Analyzer show plan, can't do that w/ the
> tuning wizard. I need to collect this activity either thru a dbTrace or
> PerfMon counters...
> Any ideas'
> "Chris" wrote:
>> I read somewhere or heard you can trace for activity on table indexes to
>> ultimately find where indexes are NOT being used when users perform
>> searches...
>> Any idea how to set this up? I don't see events related to indexes...
>> TIA,
>> Chris|||Hi Chris
I do it by using trace to capture a workload over as long a period of time
as I can, and then run that through the Index Tuning Wizard. ITW generates a
set of reports, one of which is a list of which of your current indexes are
being used what percent of the time.
--
HTH
Kalen Delaney, SQL Server MVP
www.solidqualitylearning.com
"Chris" <Chris@.discussions.microsoft.com> wrote in message
news:F5880B81-77A8-4BC2-92A7-4A1B01BE6EE8@.microsoft.com...
> I read somewhere or heard you can trace for activity on table indexes to
> ultimately find where indexes are NOT being used when users perform
> searches...
> Any idea how to set this up? I don't see events related to indexes...
> TIA,
> Chris
>

DBReindex on Clustered Idex

I'm trying to reconcile a difference of information
regarding the effect of running a DBReindex on clustered
index when non-clustered indexes exist.
According to the MSCE Training Kit (SQL Server Database
Design and Implementation), the following sentence
reads "To rebuild all indexes, instruct DBCC DBReindex to
rebuild the clustered index, thereby causing a rebuild of
all indexes on a table or view".
However according to Microsoft Knowledge Base Article
304519, the symptom of bug 354670 is that using either the
Create/Drop existing syntax or DBCC DNReindex syntax on a
clustered index results in both clustered and non-
clustered indexes being rebuilt. This is consistent with
the Training Kit, but here is is mentioned as being a bug
that has been corrected with the service pack. As the
article mentions, unless a non-unique clustered index is
being rebuilt, there should be no impact on non-clustered
indexes.
Can someone point out the correct result then of applying
a rebuild on a clustered index? From my perspective, if
the condition is a bug, why was it presented not so in the
Training Kit? And if the effect of applying a service pack
does change the behaviour, it would make answering any
questions on exams difficult unless one knew the service
pack level. After all, both publications are through
Microsoft.i believe what they're calling a "bug" is that when you rebuilt a unique
clustered index before sp2, it rebuilt all of the non-clustered indexes
when it didn't need to rebuild the non-clustered indexes. see the
"more info" section of
http://support.microsoft.com/default.aspx?scid=kb;en-us;304519
Baz Star wrote:
> I'm trying to reconcile a difference of information
> regarding the effect of running a DBReindex on clustered
> index when non-clustered indexes exist.
> According to the MSCE Training Kit (SQL Server Database
> Design and Implementation), the following sentence
> reads "To rebuild all indexes, instruct DBCC DBReindex to
> rebuild the clustered index, thereby causing a rebuild of
> all indexes on a table or view".
> However according to Microsoft Knowledge Base Article
> 304519, the symptom of bug 354670 is that using either the
> Create/Drop existing syntax or DBCC DNReindex syntax on a
> clustered index results in both clustered and non-
> clustered indexes being rebuilt. This is consistent with
> the Training Kit, but here is is mentioned as being a bug
> that has been corrected with the service pack. As the
> article mentions, unless a non-unique clustered index is
> being rebuilt, there should be no impact on non-clustered
> indexes.
> Can someone point out the correct result then of applying
> a rebuild on a clustered index? From my perspective, if
> the condition is a bug, why was it presented not so in the
> Training Kit? And if the effect of applying a service pack
> does change the behaviour, it would make answering any
> questions on exams difficult unless one knew the service
> pack level. After all, both publications are through
> Microsoft.|||That is not true.
Non-clustered indexes do not contain physical links back to the clustered
index, just logical links (i.e. the cluster keys). The root node is only
stored in database metadata and so is irrelevant to this discussion.
For a unique clustered index, the cluster keys are exactly as defined by the
table schema and rebuilding a clustered index does not change any of the
cluster keys so none of the non-clustered indexes must be rebuilt.
For a non-unique clustered index, an artificial uniquifier column is added
to the defined cluster keys to give a unique logical identifier for each row
in the clustered index. Non-clustered indexes over a non-unique clustered
index have to include the uniquifier column as part of the logical link back
to the clustered index. The uniquifier is regenerated when the non-unique
clustered index is rebuilt, so if any non-clustered indexes must also be
rebuilt to pick up the new uniquifier values.
SQL Server 2000 RTM'd with a 'bug' where all non-clustered indexes were
rebuilt no matter what kind of clustered index was rebuilt. Not a bug per
se, but a performance drain. The problem was fixed in SP2.
Regards.
--
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Sinisa Perovic" <anonymous@.discussions.microsoft.com> wrote in message
news:E493EEE6-A7EF-4BB9-8C49-08F6B54DB347@.microsoft.com...
> If the rebuild causes root node of clustered index to be changed then it
is a must for SQL Server to update(rebuild) nonclustered index no matter
wich kind!!!
> Example:
> Suppose that you're library (clustered index) is in Street One, and your
path to the library(nonclustered) is Way 1. If library moves to Street Two
there is no point to go through Way 1 as we never get to Street One, but Way
2.
> Eh, funny, isn't it?
> More info "Indexing Architecture" BOL
> Hope it Helps|||Paul,
if a non-unique clustered index contains no duplicate keys, will other
indexes still be reindexed, or is SQL-Server smart enough to only
reindex after finding a duplicate in the clustered index tree?
Thanks,
Gert-Jan
"Paul S Randal [MS]" wrote:
> That is not true.
> Non-clustered indexes do not contain physical links back to the clustered
> index, just logical links (i.e. the cluster keys). The root node is only
> stored in database metadata and so is irrelevant to this discussion.
> For a unique clustered index, the cluster keys are exactly as defined by the
> table schema and rebuilding a clustered index does not change any of the
> cluster keys so none of the non-clustered indexes must be rebuilt.
> For a non-unique clustered index, an artificial uniquifier column is added
> to the defined cluster keys to give a unique logical identifier for each row
> in the clustered index. Non-clustered indexes over a non-unique clustered
> index have to include the uniquifier column as part of the logical link back
> to the clustered index. The uniquifier is regenerated when the non-unique
> clustered index is rebuilt, so if any non-clustered indexes must also be
> rebuilt to pick up the new uniquifier values.
> SQL Server 2000 RTM'd with a 'bug' where all non-clustered indexes were
> rebuilt no matter what kind of clustered index was rebuilt. Not a bug per
> se, but a performance drain. The problem was fixed in SP2.
> Regards.
> --
> Paul Randal
> Dev Lead, Microsoft SQL Server Storage Engine
<snip>|||They're always rebuilt and with respect, it's not a question of smart
enough.
The problem is that rebuilding the clustered index will reset all the
uniquifiers - so if the clustered index _used_ to have duplicates but
doesn't any more, the uniquifier value for the remaining unique values will
change, making the non-clustered index out of date. You could do the
theoretically do the sort using the old uniquifier values but that's very
nasty.
The alternative is to track whether a duplicate exists which is very
difficult to do and keep perf in any way respectable (think of the checks
you'd have to run for a particular key value on each update or delete).
Regards.
--
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Gert-Jan Strik" <sorry@.toomuchspamalready.nl> wrote in message
news:3FFF035B.F8D22897@.toomuchspamalready.nl...
> Paul,
> if a non-unique clustered index contains no duplicate keys, will other
> indexes still be reindexed, or is SQL-Server smart enough to only
> reindex after finding a duplicate in the clustered index tree?
> Thanks,
> Gert-Jan
>
> "Paul S Randal [MS]" wrote:
> >
> > That is not true.
> >
> > Non-clustered indexes do not contain physical links back to the
clustered
> > index, just logical links (i.e. the cluster keys). The root node is only
> > stored in database metadata and so is irrelevant to this discussion.
> >
> > For a unique clustered index, the cluster keys are exactly as defined by
the
> > table schema and rebuilding a clustered index does not change any of the
> > cluster keys so none of the non-clustered indexes must be rebuilt.
> >
> > For a non-unique clustered index, an artificial uniquifier column is
added
> > to the defined cluster keys to give a unique logical identifier for each
row
> > in the clustered index. Non-clustered indexes over a non-unique
clustered
> > index have to include the uniquifier column as part of the logical link
back
> > to the clustered index. The uniquifier is regenerated when the
non-unique
> > clustered index is rebuilt, so if any non-clustered indexes must also be
> > rebuilt to pick up the new uniquifier values.
> >
> > SQL Server 2000 RTM'd with a 'bug' where all non-clustered indexes were
> > rebuilt no matter what kind of clustered index was rebuilt. Not a bug
per
> > se, but a performance drain. The problem was fixed in SP2.
> >
> > Regards.
> >
> > --
> > Paul Randal
> > Dev Lead, Microsoft SQL Server Storage Engine
> <snip>|||I see. I agree that it is not worth the trouble (and overhead) to do it
any other way.
Thanks for the information.
Gert-Jan
"Paul S Randal [MS]" wrote:
> They're always rebuilt and with respect, it's not a question of smart
> enough.
> The problem is that rebuilding the clustered index will reset all the
> uniquifiers - so if the clustered index _used_ to have duplicates but
> doesn't any more, the uniquifier value for the remaining unique values will
> change, making the non-clustered index out of date. You could do the
> theoretically do the sort using the old uniquifier values but that's very
> nasty.
> The alternative is to track whether a duplicate exists which is very
> difficult to do and keep perf in any way respectable (think of the checks
> you'd have to run for a particular key value on each update or delete).
> Regards.
> --
> Paul Randal
> Dev Lead, Microsoft SQL Server Storage Engine
> This posting is provided "AS IS" with no warranties, and confers no rights.
> "Gert-Jan Strik" <sorry@.toomuchspamalready.nl> wrote in message
> news:3FFF035B.F8D22897@.toomuchspamalready.nl...
> > Paul,
> >
> > if a non-unique clustered index contains no duplicate keys, will other
> > indexes still be reindexed, or is SQL-Server smart enough to only
> > reindex after finding a duplicate in the clustered index tree?
> >
> > Thanks,
> > Gert-Jan
> >
> >
> > "Paul S Randal [MS]" wrote:
> > >
> > > That is not true.
> > >
> > > Non-clustered indexes do not contain physical links back to the
> clustered
> > > index, just logical links (i.e. the cluster keys). The root node is only
> > > stored in database metadata and so is irrelevant to this discussion.
> > >
> > > For a unique clustered index, the cluster keys are exactly as defined by
> the
> > > table schema and rebuilding a clustered index does not change any of the
> > > cluster keys so none of the non-clustered indexes must be rebuilt.
> > >
> > > For a non-unique clustered index, an artificial uniquifier column is
> added
> > > to the defined cluster keys to give a unique logical identifier for each
> row
> > > in the clustered index. Non-clustered indexes over a non-unique
> clustered
> > > index have to include the uniquifier column as part of the logical link
> back
> > > to the clustered index. The uniquifier is regenerated when the
> non-unique
> > > clustered index is rebuilt, so if any non-clustered indexes must also be
> > > rebuilt to pick up the new uniquifier values.
> > >
> > > SQL Server 2000 RTM'd with a 'bug' where all non-clustered indexes were
> > > rebuilt no matter what kind of clustered index was rebuilt. Not a bug
> per
> > > se, but a performance drain. The problem was fixed in SP2.
> > >
> > > Regards.
> > >
> > > --
> > > Paul Randal
> > > Dev Lead, Microsoft SQL Server Storage Engine
> > <snip>

dbreindex causes fragmentation in other indexes

We have a report that uses dbcc showcontig to identify indexes with
fragmentation. It shows indexes that have a scan density under 85% or extent
fragmentation over 15%. I run dbcc dbreindex against the indexes identified
in the report to rebuild the indexes. When I run the report again a
completely different index shows up. During this time no other users or
processes are running against the database. Any ideas what may be causing
this?
--
OdellHi
You may want to build all indexes for the given table rather than specific
index, especially if the index is a clustered.
John
"Odell Edwards" wrote:
> We have a report that uses dbcc showcontig to identify indexes with
> fragmentation. It shows indexes that have a scan density under 85% or extent
> fragmentation over 15%. I run dbcc dbreindex against the indexes identified
> in the report to rebuild the indexes. When I run the report again a
> completely different index shows up. During this time no other users or
> processes are running against the database. Any ideas what may be causing
> this?
> --
> Odell|||Thanks for the post. We tried rebuilding all the indexes. It took several
hours but it didn't clean up the fragmentation.
--
Odell
"John Bell" wrote:
> Hi
> You may want to build all indexes for the given table rather than specific
> index, especially if the index is a clustered.
> John
> "Odell Edwards" wrote:
> > We have a report that uses dbcc showcontig to identify indexes with
> > fragmentation. It shows indexes that have a scan density under 85% or extent
> > fragmentation over 15%. I run dbcc dbreindex against the indexes identified
> > in the report to rebuild the indexes. When I run the report again a
> > completely different index shows up. During this time no other users or
> > processes are running against the database. Any ideas what may be causing
> > this?
> > --
> > Odell|||Hi
Did you specify the indexes individually or just the table?
John
"Odell Edwards" wrote:
> Thanks for the post. We tried rebuilding all the indexes. It took several
> hours but it didn't clean up the fragmentation.
> --
> Odell
>
> "John Bell" wrote:
> > Hi
> >
> > You may want to build all indexes for the given table rather than specific
> > index, especially if the index is a clustered.
> >
> > John
> >
> > "Odell Edwards" wrote:
> >
> > > We have a report that uses dbcc showcontig to identify indexes with
> > > fragmentation. It shows indexes that have a scan density under 85% or extent
> > > fragmentation over 15%. I run dbcc dbreindex against the indexes identified
> > > in the report to rebuild the indexes. When I run the report again a
> > > completely different index shows up. During this time no other users or
> > > processes are running against the database. Any ideas what may be causing
> > > this?
> > > --
> > > Odell|||We sepcified the table, not the individual indexes.
--
Odell
"John Bell" wrote:
> Hi
> Did you specify the indexes individually or just the table?
> John
> "Odell Edwards" wrote:
> > Thanks for the post. We tried rebuilding all the indexes. It took several
> > hours but it didn't clean up the fragmentation.
> > --
> > Odell
> >
> >
> > "John Bell" wrote:
> >
> > > Hi
> > >
> > > You may want to build all indexes for the given table rather than specific
> > > index, especially if the index is a clustered.
> > >
> > > John
> > >
> > > "Odell Edwards" wrote:
> > >
> > > > We have a report that uses dbcc showcontig to identify indexes with
> > > > fragmentation. It shows indexes that have a scan density under 85% or extent
> > > > fragmentation over 15%. I run dbcc dbreindex against the indexes identified
> > > > in the report to rebuild the indexes. When I run the report again a
> > > > completely different index shows up. During this time no other users or
> > > > processes are running against the database. Any ideas what may be causing
> > > > this?
> > > > --
> > > > Odell|||This is the format we used
dbcc dbreindex (<tablename>, '',0)
Thanks,
--
Odell
"Odell Edwards" wrote:
> We sepcified the table, not the individual indexes.
> --
> Odell
>
> "John Bell" wrote:
> > Hi
> >
> > Did you specify the indexes individually or just the table?
> >
> > John
> >
> > "Odell Edwards" wrote:
> >
> > > Thanks for the post. We tried rebuilding all the indexes. It took several
> > > hours but it didn't clean up the fragmentation.
> > > --
> > > Odell
> > >
> > >
> > > "John Bell" wrote:
> > >
> > > > Hi
> > > >
> > > > You may want to build all indexes for the given table rather than specific
> > > > index, especially if the index is a clustered.
> > > >
> > > > John
> > > >
> > > > "Odell Edwards" wrote:
> > > >
> > > > > We have a report that uses dbcc showcontig to identify indexes with
> > > > > fragmentation. It shows indexes that have a scan density under 85% or extent
> > > > > fragmentation over 15%. I run dbcc dbreindex against the indexes identified
> > > > > in the report to rebuild the indexes. When I run the report again a
> > > > > completely different index shows up. During this time no other users or
> > > > > processes are running against the database. Any ideas what may be causing
> > > > > this?
> > > > > --
> > > > > Odell|||Scan density is meaningless if you have several database files (search the archives). And there's
little you can do about extent scan fragmentation (I tend to ignore it). Look at Logical
fragmentation...
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Odell Edwards" <OdellEdwards@.discussions.microsoft.com> wrote in message
news:74893251-4530-4105-BAEA-B99923C81693@.microsoft.com...
> This is the format we used
> dbcc dbreindex (<tablename>, '',0)
> Thanks,
> --
> Odell
>
> "Odell Edwards" wrote:
>> We sepcified the table, not the individual indexes.
>> --
>> Odell
>>
>> "John Bell" wrote:
>> > Hi
>> >
>> > Did you specify the indexes individually or just the table?
>> >
>> > John
>> >
>> > "Odell Edwards" wrote:
>> >
>> > > Thanks for the post. We tried rebuilding all the indexes. It took several
>> > > hours but it didn't clean up the fragmentation.
>> > > --
>> > > Odell
>> > >
>> > >
>> > > "John Bell" wrote:
>> > >
>> > > > Hi
>> > > >
>> > > > You may want to build all indexes for the given table rather than specific
>> > > > index, especially if the index is a clustered.
>> > > >
>> > > > John
>> > > >
>> > > > "Odell Edwards" wrote:
>> > > >
>> > > > > We have a report that uses dbcc showcontig to identify indexes with
>> > > > > fragmentation. It shows indexes that have a scan density under 85% or extent
>> > > > > fragmentation over 15%. I run dbcc dbreindex against the indexes identified
>> > > > > in the report to rebuild the indexes. When I run the report again a
>> > > > > completely different index shows up. During this time no other users or
>> > > > > processes are running against the database. Any ideas what may be causing
>> > > > > this?
>> > > > > --
>> > > > > Odell|||Is this a clustered index or a HEAP? If it is a HEAP then you can reindex
all you want and nothing will happen to reduce fragmentation. Can you post
the results of DBCC SHOWCONTIG?
--
Andrew J. Kelly SQL MVP
"Odell Edwards" <OdellEdwards@.discussions.microsoft.com> wrote in message
news:74893251-4530-4105-BAEA-B99923C81693@.microsoft.com...
> This is the format we used
> dbcc dbreindex (<tablename>, '',0)
> Thanks,
> --
> Odell
>
> "Odell Edwards" wrote:
>> We sepcified the table, not the individual indexes.
>> --
>> Odell
>>
>> "John Bell" wrote:
>> > Hi
>> >
>> > Did you specify the indexes individually or just the table?
>> >
>> > John
>> >
>> > "Odell Edwards" wrote:
>> >
>> > > Thanks for the post. We tried rebuilding all the indexes. It took
>> > > several
>> > > hours but it didn't clean up the fragmentation.
>> > > --
>> > > Odell
>> > >
>> > >
>> > > "John Bell" wrote:
>> > >
>> > > > Hi
>> > > >
>> > > > You may want to build all indexes for the given table rather than
>> > > > specific
>> > > > index, especially if the index is a clustered.
>> > > >
>> > > > John
>> > > >
>> > > > "Odell Edwards" wrote:
>> > > >
>> > > > > We have a report that uses dbcc showcontig to identify indexes
>> > > > > with
>> > > > > fragmentation. It shows indexes that have a scan density under
>> > > > > 85% or extent
>> > > > > fragmentation over 15%. I run dbcc dbreindex against the indexes
>> > > > > identified
>> > > > > in the report to rebuild the indexes. When I run the report
>> > > > > again a
>> > > > > completely different index shows up. During this time no other
>> > > > > users or
>> > > > > processes are running against the database. Any ideas what may
>> > > > > be causing
>> > > > > this?
>> > > > > --
>> > > > > Odell

dbreindex causes fragmentation in other indexes

We have a report that uses dbcc showcontig to identify indexes with
fragmentation. It shows indexes that have a scan density under 85% or exten
t
fragmentation over 15%. I run dbcc dbreindex against the indexes identified
in the report to rebuild the indexes. When I run the report again a
completely different index shows up. During this time no other users or
processes are running against the database. Any ideas what may be causing
this?
--
OdellHi
You may want to build all indexes for the given table rather than specific
index, especially if the index is a clustered.
John
"Odell Edwards" wrote:

> We have a report that uses dbcc showcontig to identify indexes with
> fragmentation. It shows indexes that have a scan density under 85% or ext
ent
> fragmentation over 15%. I run dbcc dbreindex against the indexes identifi
ed
> in the report to rebuild the indexes. When I run the report again a
> completely different index shows up. During this time no other users or
> processes are running against the database. Any ideas what may be causing
> this?
> --
> Odell|||Thanks for the post. We tried rebuilding all the indexes. It took several
hours but it didn't clean up the fragmentation.
--
Odell
"John Bell" wrote:
[vbcol=seagreen]
> Hi
> You may want to build all indexes for the given table rather than specific
> index, especially if the index is a clustered.
> John
> "Odell Edwards" wrote:
>|||Hi
Did you specify the indexes individually or just the table?
John
"Odell Edwards" wrote:
[vbcol=seagreen]
> Thanks for the post. We tried rebuilding all the indexes. It took several
> hours but it didn't clean up the fragmentation.
> --
> Odell
>
> "John Bell" wrote:
>|||We sepcified the table, not the individual indexes.
--
Odell
"John Bell" wrote:
[vbcol=seagreen]
> Hi
> Did you specify the indexes individually or just the table?
> John
> "Odell Edwards" wrote:
>|||This is the format we used
dbcc dbreindex (<tablename>, '',0)
Thanks,
--
Odell
"Odell Edwards" wrote:
[vbcol=seagreen]
> We sepcified the table, not the individual indexes.
> --
> Odell
>
> "John Bell" wrote:
>|||Scan density is meaningless if you have several database files (search the a
rchives). And there's
little you can do about extent scan fragmentation (I tend to ignore it). Loo
k at Logical
fragmentation...
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Odell Edwards" <OdellEdwards@.discussions.microsoft.com> wrote in message
news:74893251-4530-4105-BAEA-B99923C81693@.microsoft.com...[vbcol=seagreen]
> This is the format we used
> dbcc dbreindex (<tablename>, '',0)
> Thanks,
> --
> Odell
>
> "Odell Edwards" wrote:
>|||Is this a clustered index or a HEAP? If it is a HEAP then you can reindex
all you want and nothing will happen to reduce fragmentation. Can you post
the results of DBCC SHOWCONTIG?
Andrew J. Kelly SQL MVP
"Odell Edwards" <OdellEdwards@.discussions.microsoft.com> wrote in message
news:74893251-4530-4105-BAEA-B99923C81693@.microsoft.com...[vbcol=seagreen]
> This is the format we used
> dbcc dbreindex (<tablename>, '',0)
> Thanks,
> --
> Odell
>
> "Odell Edwards" wrote:
>

DBREINDEX at threshold for all databases

There is a proc in Books Online that allows you to execute a INDEXDEFRAG on
all indexes in a database that have a logical fragmentation percentage above
a specific limit. IT useds SHOWCONTIG and a temporary table. I want to run
this proc with DBREINDEX instead and I want to schedule it wly for all
exising user databases. (The Database Maintenance Wizard is too
inefficient.) If a new database gets added to the environment, I want the
proc to dynamically pick up that new database.
The problem is that this proc has to be executed within the database to be
defragged. I'm having trouble modifying it to loop for each existing
database.
I've tried some "EXEC ('USE ' + @.dbname + ' DBCC SHOW..." but am still
having some problems. This is probably a pretty basic type of maintenance
procedure. Does anyone already have this coded that they would share?I actually have something that may work for you - but not without a little
work. I was trying to do the same thing - a wly job that would run on an
y
existing user db's. If you run this, it will pick up all user databases. I
use PRINT @.SQL instead of EXEC because I was unable to get it to execute the
DBREINDEX without error. And right now I'm taking the results of that and
driving the job. If you're able to get around that, please advise.
DECLARE @.SQL NVarchar(4000)
SET @.SQL = ''
SELECT @.SQL = @.SQL + 'EXEC ' + NAME + '..sp_MSforeachtable @.command1=''DBCC
DBREINDEX (''''*'''')'', @.replacechar=''*''' + Char(13)
FROM MASTER..Sysdatabases
WHERE dbid >6
PRINT @.SQL
-- Lynn
"Stephanie" wrote:

> There is a proc in Books Online that allows you to execute a INDEXDEFRAG o
n
> all indexes in a database that have a logical fragmentation percentage abo
ve
> a specific limit. IT useds SHOWCONTIG and a temporary table. I want to r
un
> this proc with DBREINDEX instead and I want to schedule it wly for all
> exising user databases. (The Database Maintenance Wizard is too
> inefficient.) If a new database gets added to the environment, I want the
> proc to dynamically pick up that new database.
> The problem is that this proc has to be executed within the database to be
> defragged. I'm having trouble modifying it to loop for each existing
> database.
> I've tried some "EXEC ('USE ' + @.dbname + ' DBCC SHOW..." but am still
> having some problems. This is probably a pretty basic type of maintenance
> procedure. Does anyone already have this coded that they would share?
>|||Maybe this helps:
http://milambda.blogspot.com/2005/0...in-current.html
It needs a wrapper that will execute it for each database, and you need to
propagate the db_id value through to the dbcc call (currently the value of
db_id is 0 - current database).
ML