Deadlock updating different rows

Here is the condensed version of my question: why do I get a deadlock
when multiple threads are updating different rows in the same table?
Details: I have a table with a clustered index spread across multiple
columns. I run multiple threads, each of which accesses a separate row
in the table. Nevertheless, I see deadlocks.
SPID 61 is granted KEY: 10:240719910:1 (83033c6fb2c1) Mode: X and
is
requesting KEY: 10:240719910:1 (84031b0a9740) Mode: U
SPID 60 is granted KEY: 10:240719910:1 (84031b0a9740) Mode: X and
is
requesting KEY: 10:240719910:1 (83033c6fb2c1) Mode: S
It is my understanding that the KEY locks are essentially row-level
locks because with clustered indexes the data are leaf nodes of the
index. As you can see, each thread is requesting a lock held by the
other. The locks are on the same index but different rows (the long
hex numbers are hashes related to rows, e.g. 83033c6fb2c1).
I can't understand why different threads updating distinct rows would
ever want to lock the same rows. Granted, the rows may be adjacent,
but should that cause the acquisition of locks on rows other than the
one being updated? I could understand a broader locking if INSERTs
were happening, but that is not the case.
Thanks for any help you can offer.Is the index unique? Please post the complete table DDL and UPDATE
statement.
Hope this helps.
Dan Guzman
SQL Server MVP
"rand" <randclark2005@.yahoo.com> wrote in message
news:1135189033.769214.220820@.f14g2000cwb.googlegroups.com...
> Here is the condensed version of my question: why do I get a deadlock
> when multiple threads are updating different rows in the same table?
> Details: I have a table with a clustered index spread across multiple
> columns. I run multiple threads, each of which accesses a separate row
> in the table. Nevertheless, I see deadlocks.
> SPID 61 is granted KEY: 10:240719910:1 (83033c6fb2c1) Mode: X and
> is
> requesting KEY: 10:240719910:1 (84031b0a9740) Mode: U
> SPID 60 is granted KEY: 10:240719910:1 (84031b0a9740) Mode: X and
> is
> requesting KEY: 10:240719910:1 (83033c6fb2c1) Mode: S
> It is my understanding that the KEY locks are essentially row-level
> locks because with clustered indexes the data are leaf nodes of the
> index. As you can see, each thread is requesting a lock held by the
> other. The locks are on the same index but different rows (the long
> hex numbers are hashes related to rows, e.g. 83033c6fb2c1).
> I can't understand why different threads updating distinct rows would
> ever want to lock the same rows. Granted, the rows may be adjacent,
> but should that cause the acquisition of locks on rows other than the
> one being updated? I could understand a broader locking if INSERTs
> were happening, but that is not the case.
> Thanks for any help you can offer.
>|||Dan,
Thanks for your reply. Index is unique. Here is the table definition:
create TABLE [Sum_Item_Revenue] (
[tendered_business_period_dim_id] [int] NOT NULL ,
[posted_business_period_dim_id] [int] NOT NULL ,
[event_dim_id] [int] NOT NULL CONSTRAINT
[DF__Sum_Item___event__5B78929E] DEFAULT (0),
[profit_center_dim_id] [int] NOT NULL ,
[misc_period_dim_id] [int] NOT NULL ,
[pay_type_dim_id] [int] NOT NULL ,
[emp_dim_id] [int] NOT NULL ,
[item_dim_id] [int] NOT NULL ,
[total_sales_gross_amount] [decimal](18, 4) NULL ,
[total_discount_amount] [decimal](18, 4) NULL ,
[ordered_profit_center_dim_id] [int] NOT NULL CONSTRAINT
[DF__Sum_Item___order__45FE52CB] DEFAULT (0),
CONSTRAINT [Sum_Item_Revenue_PK] PRIMARY KEY CLUSTERED
(
[tendered_business_period_dim_id],
[posted_business_period_dim_id],
[event_dim_id],
[profit_center_dim_id],
[misc_period_dim_id],
[pay_type_dim_id],
[emp_dim_id],
[item_dim_id],
[ordered_profit_center_dim_id]
) WITH FILLFACTOR = 90 ON [PRIMARY]
) ON [PRIMARY]
It turns out it's not just a straight UPDATE but a stored procedure. I
realize the stored procedure has the potential to do INSERTs but for
the testing I've been doing, it's all been updates, because the records
already exist. Here is the stored proc:
create procedure InsertUpdate_Sum_Item_Revenue
@.tendered_business_period_dim_id int ,
@.posted_business_period_dim_id int ,
@.profit_center_dim_id int ,
@.ordered_profit_center_dim_id int = 0,
@.misc_period_dim_id int ,
@.pay_type_dim_id int ,
@.emp_dim_id int ,
@.item_dim_id int ,
@.total_sales_gross_amount decimal(18, 4),
@.total_discount_amount decimal(18, 4)
AS
Declare @.count int
SELECT @.count = count(*)
FROM Sum_Item_Revenue
WHERE
tendered_business_period_dim_id =
@.tendered_business_period_dim_id AND
posted_business_period_dim_id = @.posted_business_period_dim_id AND
profit_center_dim_id = @.profit_center_dim_id AND
misc_period_dim_id = @.misc_period_dim_id AND
pay_type_dim_id = @.pay_type_dim_id AND
emp_dim_id = @.emp_dim_id AND
item_dim_id = @.item_dim_id AND
ordered_profit_center_dim_id = @.ordered_profit_center_dim_id
IF @.count = 0
INSERT INTO Sum_Item_Revenue
( tendered_business_period_dim_id ,
posted_business_period_dim_id ,
profit_center_dim_id ,
ordered_profit_center_dim_id ,
misc_period_dim_id ,
pay_type_dim_id ,
emp_dim_id ,
item_dim_id ,
total_sales_gross_amount ,
total_discount_amount
)
VALUES
( @.tendered_business_period_dim_id ,
@.posted_business_period_dim_id ,
@.profit_center_dim_id ,
@.ordered_profit_center_dim_id ,
@.misc_period_dim_id ,
@.pay_type_dim_id ,
@.emp_dim_id ,
@.item_dim_id ,
@.total_sales_gross_amount ,
@.total_discount_amount
)
ELSE
UPDATE Sum_Item_Revenue SET
total_sales_gross_amount = total_sales_gross_amount +
@.total_sales_gross_amount ,
total_discount_amount = total_discount_amount +
@.total_discount_amount
WHERE
tendered_business_period_dim_id =
@.tendered_business_period_dim_id AND
posted_business_period_dim_id = @.posted_business_period_dim_id AND
profit_center_dim_id = @.profit_center_dim_id AND
misc_period_dim_id = @.misc_period_dim_id AND
pay_type_dim_id = @.pay_type_dim_id AND
emp_dim_id = @.emp_dim_id AND
item_dim_id = @.item_dim_id AND
ordered_profit_center_dim_id = @.ordered_profit_center_dim_id
I've removed some non-essential columns from the table for the purposes
of this posting, to remove clutter.
I call this routine from multiple threads, where each thread passes in
a unique profit_center_dim_id. Other values of the key are similar.
So, when I call this it is doing SELECTs and UPDATEs. I'm assuming the
SELECT is manifested by one of my SPIDs above attempting to obtain a
shared lock.
Thanks, rand|||Try this:
BEGIN TRAN
IF EXISTS(SELECT 1 FROM...WITH(UPDLOCK, HOLDLOCK) WHERE...)
BEGIN
UPDATE...
--error handling here
END
ELSE
BEGIN
INSERT...
--error handling here
END
COMMIT TRAN
WITH(UPDLOCK,HOLDLOCK) places an update range-lock on the table that is
about to be modified. This does not affect select concurrency, because
other transactions can obtain shared locks on rows that already have an
update lock. It only affects insert/update concurrency and not by much. It
will eliminate the deadlock that you're encountering.
The construct below doesn't take into account the fact that another
transaction can obtain a lock on the row to be updated between the SELECT
and the UPDATE or INSERT.
"rand" <randclark2005@.yahoo.com> wrote in message
news:1135212067.893743.113210@.z14g2000cwz.googlegroups.com...
> Dan,
> Thanks for your reply. Index is unique. Here is the table definition:
> create TABLE [Sum_Item_Revenue] (
> [tendered_business_period_dim_id] [int] NOT NULL ,
> [posted_business_period_dim_id] [int] NOT NULL ,
> [event_dim_id] [int] NOT NULL CONSTRAINT
> [DF__Sum_Item___event__5B78929E] DEFAULT (0),
> [profit_center_dim_id] [int] NOT NULL ,
> [misc_period_dim_id] [int] NOT NULL ,
> [pay_type_dim_id] [int] NOT NULL ,
> [emp_dim_id] [int] NOT NULL ,
> [item_dim_id] [int] NOT NULL ,
> [total_sales_gross_amount] [decimal](18, 4) NULL ,
> [total_discount_amount] [decimal](18, 4) NULL ,
> [ordered_profit_center_dim_id] [int] NOT NULL CONSTRAINT
> [DF__Sum_Item___order__45FE52CB] DEFAULT (0),
> CONSTRAINT [Sum_Item_Revenue_PK] PRIMARY KEY CLUSTERED
> (
> [tendered_business_period_dim_id],
> [posted_business_period_dim_id],
> [event_dim_id],
> [profit_center_dim_id],
> [misc_period_dim_id],
> [pay_type_dim_id],
> [emp_dim_id],
> [item_dim_id],
> [ordered_profit_center_dim_id]
> ) WITH FILLFACTOR = 90 ON [PRIMARY]
> ) ON [PRIMARY]
> It turns out it's not just a straight UPDATE but a stored procedure. I
> realize the stored procedure has the potential to do INSERTs but for
> the testing I've been doing, it's all been updates, because the records
> already exist. Here is the stored proc:
> create procedure InsertUpdate_Sum_Item_Revenue
> @.tendered_business_period_dim_id int ,
> @.posted_business_period_dim_id int ,
> @.profit_center_dim_id int ,
> @.ordered_profit_center_dim_id int = 0,
> @.misc_period_dim_id int ,
> @.pay_type_dim_id int ,
> @.emp_dim_id int ,
> @.item_dim_id int ,
> @.total_sales_gross_amount decimal(18, 4),
> @.total_discount_amount decimal(18, 4)
> AS
> Declare @.count int
> SELECT @.count = count(*)
> FROM Sum_Item_Revenue
> WHERE
> tendered_business_period_dim_id =
> @.tendered_business_period_dim_id AND
> posted_business_period_dim_id = @.posted_business_period_dim_id AND
> profit_center_dim_id = @.profit_center_dim_id AND
> misc_period_dim_id = @.misc_period_dim_id AND
> pay_type_dim_id = @.pay_type_dim_id AND
> emp_dim_id = @.emp_dim_id AND
> item_dim_id = @.item_dim_id AND
> ordered_profit_center_dim_id = @.ordered_profit_center_dim_id
> IF @.count = 0
> INSERT INTO Sum_Item_Revenue
> ( tendered_business_period_dim_id ,
> posted_business_period_dim_id ,
> profit_center_dim_id ,
> ordered_profit_center_dim_id ,
> misc_period_dim_id ,
> pay_type_dim_id ,
> emp_dim_id ,
> item_dim_id ,
> total_sales_gross_amount ,
> total_discount_amount
> )
> VALUES
> ( @.tendered_business_period_dim_id ,
> @.posted_business_period_dim_id ,
> @.profit_center_dim_id ,
> @.ordered_profit_center_dim_id ,
> @.misc_period_dim_id ,
> @.pay_type_dim_id ,
> @.emp_dim_id ,
> @.item_dim_id ,
> @.total_sales_gross_amount ,
> @.total_discount_amount
> )
>
> ELSE
> UPDATE Sum_Item_Revenue SET
> total_sales_gross_amount = total_sales_gross_amount +
> @.total_sales_gross_amount ,
> total_discount_amount = total_discount_amount +
> @.total_discount_amount
> WHERE
> tendered_business_period_dim_id =
> @.tendered_business_period_dim_id AND
> posted_business_period_dim_id = @.posted_business_period_dim_id AND
> profit_center_dim_id = @.profit_center_dim_id AND
> misc_period_dim_id = @.misc_period_dim_id AND
> pay_type_dim_id = @.pay_type_dim_id AND
> emp_dim_id = @.emp_dim_id AND
> item_dim_id = @.item_dim_id AND
> ordered_profit_center_dim_id = @.ordered_profit_center_dim_id
> I've removed some non-essential columns from the table for the purposes
> of this posting, to remove clutter.
> I call this routine from multiple threads, where each thread passes in
> a unique profit_center_dim_id. Other values of the key are similar.
> So, when I call this it is doing SELECTs and UPDATEs. I'm assuming the
> SELECT is manifested by one of my SPIDs above attempting to obtain a
> shared lock.
> Thanks, rand
>|||Brian, Thanks I'll give it a try. I should add that the lack of
transaction semantics within my stored procedure is because this
procedure is invoked from .NET code within BeginTransaction() and
Commit() using the default isolation level of Read Committed. Slightly
bigger picture: I'm multi-threading code that has heretofore been
single-threaded. The thing that has me baffled is why there is any
lock contention at all, given that different threads should be
accessing different rows. Unless my assumption is incorrect and the
locks I see are really not row-level, but are table- or page-level.|||First, I prefer to handle transaction processing within the stored
procedure. This makes it a lot easier to troubleshoot deadlocks and to
change code--for example, to implement optimistic concurrency.
Second, READ COMMITTED is good for reporting; for modifications, it is a
disaster waiting to happen. You should use REPEATABLE READ or preferably
SERIALIZABLE if the information you're reading will be used in a subsequent
modification within the same transaction. As a rule, Rows selected that may
be updated should have an update lock applied and held until the transaction
commits; rows selected that will not be updated but whose value will be used
either directly or indirectly as values that will be inserted or updated
should have a shared lock applied and held. This is extremely important to
keep garbage out of your database. Any change to the source data between
the SELECT and the UPDATE/INSERT renders the results you've just read out
stale, which can introduce incorrect information into the database. If the
update involves inserting or updating summary information, then you should
use SERIALIZABLE because an INSERT will cause the results to become stale.
READ COMMITTED doesn't prevent changes from occuring between the SELECT and
the UPDATE/INSERT, and REPEATABLE READ doesn't prevent new rows that meet
the criteria used for summarization from being inserted.
"rand" <randclark2005@.yahoo.com> wrote in message
news:1135217308.843942.88810@.z14g2000cwz.googlegroups.com...
> Brian, Thanks I'll give it a try. I should add that the lack of
> transaction semantics within my stored procedure is because this
> procedure is invoked from .NET code within BeginTransaction() and
> Commit() using the default isolation level of Read Committed. Slightly
> bigger picture: I'm multi-threading code that has heretofore been
> single-threaded. The thing that has me baffled is why there is any
> lock contention at all, given that different threads should be
> accessing different rows. Unless my assumption is incorrect and the
> locks I see are really not row-level, but are table- or page-level.
>|||I see that the event_dim_id column is part of the primary key but is not
included in the where clause of the SELECT or UPDATE. This could increase
the likelihood of your deadlocks.
Brian pointed out that you are vulnerable to changes between the SELECT and
INSERT/UPDATE. Since you run the proc is run as part of a transaction,
below is another 'UPSERT' technique that I like to use. I hard-coded a zero
value for event_dim_id in this example.
alter procedure InsertUpdate_Sum_Item_Revenue
@.tendered_business_period_dim_id int ,
@.posted_business_period_dim_id int ,
@.profit_center_dim_id int ,
@.ordered_profit_center_dim_id int = 0,
@.misc_period_dim_id int ,
@.pay_type_dim_id int ,
@.emp_dim_id int ,
@.item_dim_id int ,
@.total_sales_gross_amount decimal(18, 4),
@.total_discount_amount decimal(18, 4)
AS
SET NOCOUNT ON
INSERT INTO Sum_Item_Revenue
(
tendered_business_period_dim_id,
posted_business_period_dim_id,
profit_center_dim_id,
ordered_profit_center_dim_id,
misc_period_dim_id,
pay_type_dim_id,
emp_dim_id,
item_dim_id,
total_sales_gross_amount,
total_discount_amount
)
SELECT
@.tendered_business_period_dim_id,
@.posted_business_period_dim_id,
@.profit_center_dim_id,
@.ordered_profit_center_dim_id,
@.misc_period_dim_id,
@.pay_type_dim_id,
@.emp_dim_id,
@.item_dim_id,
@.total_sales_gross_amount,
@.total_discount_amount
WHERE NOT EXISTS
(
SELECT *
FROM Sum_Item_Revenue WITH (UPDLOCK, HOLDLOCK)
WHERE
tendered_business_period_dim_id =@.tendered_business_period_dim_id
AND
posted_business_period_dim_id = @.posted_business_period_dim_id
AND
profit_center_dim_id = @.profit_center_dim_id
AND
misc_period_dim_id = @.misc_period_dim_id
AND
pay_type_dim_id = @.pay_type_dim_id AND
emp_dim_id = @.emp_dim_id AND
item_dim_id = @.item_dim_id AND
ordered_profit_center_dim_id = @.ordered_profit_center_dim_id AND
event_dim_id = 0
)
IF @.@.ROWCOUNT = 0
BEGIN
UPDATE Sum_Item_Revenue
SET
total_sales_gross_amount = total_sales_gross_amount +
@.total_sales_gross_amount,
total_discount_amount = total_discount_amount +
@.total_discount_amount
WHERE
tendered_business_period_dim_id
=@.tendered_business_period_dim_id AND
posted_business_period_dim_id = @.posted_business_period_dim_id
AND
profit_center_dim_id = @.profit_center_dim_id
AND
misc_period_dim_id = @.misc_period_dim_id
AND
pay_type_dim_id = @.pay_type_dim_id
AND
emp_dim_id = @.emp_dim_id AND
item_dim_id = @.item_dim_id AND
ordered_profit_center_dim_id = @.ordered_profit_center_dim_id AND
event_dim_id = 0
END
GO
Hope this helps.
Dan Guzman
SQL Server MVP
"rand" <randclark2005@.yahoo.com> wrote in message
news:1135212067.893743.113210@.z14g2000cwz.googlegroups.com...
> Dan,
> Thanks for your reply. Index is unique. Here is the table definition:
> create TABLE [Sum_Item_Revenue] (
> [tendered_business_period_dim_id] [int] NOT NULL ,
> [posted_business_period_dim_id] [int] NOT NULL ,
> [event_dim_id] [int] NOT NULL CONSTRAINT
> [DF__Sum_Item___event__5B78929E] DEFAULT (0),
> [profit_center_dim_id] [int] NOT NULL ,
> [misc_period_dim_id] [int] NOT NULL ,
> [pay_type_dim_id] [int] NOT NULL ,
> [emp_dim_id] [int] NOT NULL ,
> [item_dim_id] [int] NOT NULL ,
> [total_sales_gross_amount] [decimal](18, 4) NULL ,
> [total_discount_amount] [decimal](18, 4) NULL ,
> [ordered_profit_center_dim_id] [int] NOT NULL CONSTRAINT
> [DF__Sum_Item___order__45FE52CB] DEFAULT (0),
> CONSTRAINT [Sum_Item_Revenue_PK] PRIMARY KEY CLUSTERED
> (
> [tendered_business_period_dim_id],
> [posted_business_period_dim_id],
> [event_dim_id],
> [profit_center_dim_id],
> [misc_period_dim_id],
> [pay_type_dim_id],
> [emp_dim_id],
> [item_dim_id],
> [ordered_profit_center_dim_id]
> ) WITH FILLFACTOR = 90 ON [PRIMARY]
> ) ON [PRIMARY]
> It turns out it's not just a straight UPDATE but a stored procedure. I
> realize the stored procedure has the potential to do INSERTs but for
> the testing I've been doing, it's all been updates, because the records
> already exist. Here is the stored proc:
> create procedure InsertUpdate_Sum_Item_Revenue
> @.tendered_business_period_dim_id int ,
> @.posted_business_period_dim_id int ,
> @.profit_center_dim_id int ,
> @.ordered_profit_center_dim_id int = 0,
> @.misc_period_dim_id int ,
> @.pay_type_dim_id int ,
> @.emp_dim_id int ,
> @.item_dim_id int ,
> @.total_sales_gross_amount decimal(18, 4),
> @.total_discount_amount decimal(18, 4)
> AS
> Declare @.count int
> SELECT @.count = count(*)
> FROM Sum_Item_Revenue
> WHERE
> tendered_business_period_dim_id =
> @.tendered_business_period_dim_id AND
> posted_business_period_dim_id = @.posted_business_period_dim_id AND
> profit_center_dim_id = @.profit_center_dim_id AND
> misc_period_dim_id = @.misc_period_dim_id AND
> pay_type_dim_id = @.pay_type_dim_id AND
> emp_dim_id = @.emp_dim_id AND
> item_dim_id = @.item_dim_id AND
> ordered_profit_center_dim_id = @.ordered_profit_center_dim_id
> IF @.count = 0
> INSERT INTO Sum_Item_Revenue
> ( tendered_business_period_dim_id ,
> posted_business_period_dim_id ,
> profit_center_dim_id ,
> ordered_profit_center_dim_id ,
> misc_period_dim_id ,
> pay_type_dim_id ,
> emp_dim_id ,
> item_dim_id ,
> total_sales_gross_amount ,
> total_discount_amount
> )
> VALUES
> ( @.tendered_business_period_dim_id ,
> @.posted_business_period_dim_id ,
> @.profit_center_dim_id ,
> @.ordered_profit_center_dim_id ,
> @.misc_period_dim_id ,
> @.pay_type_dim_id ,
> @.emp_dim_id ,
> @.item_dim_id ,
> @.total_sales_gross_amount ,
> @.total_discount_amount
> )
>
> ELSE
> UPDATE Sum_Item_Revenue SET
> total_sales_gross_amount = total_sales_gross_amount +
> @.total_sales_gross_amount ,
> total_discount_amount = total_discount_amount +
> @.total_discount_amount
> WHERE
> tendered_business_period_dim_id =
> @.tendered_business_period_dim_id AND
> posted_business_period_dim_id = @.posted_business_period_dim_id AND
> profit_center_dim_id = @.profit_center_dim_id AND
> misc_period_dim_id = @.misc_period_dim_id AND
> pay_type_dim_id = @.pay_type_dim_id AND
> emp_dim_id = @.emp_dim_id AND
> item_dim_id = @.item_dim_id AND
> ordered_profit_center_dim_id = @.ordered_profit_center_dim_id
> I've removed some non-essential columns from the table for the purposes
> of this posting, to remove clutter.
> I call this routine from multiple threads, where each thread passes in
> a unique profit_center_dim_id. Other values of the key are similar.
> So, when I call this it is doing SELECTs and UPDATEs. I'm assuming the
> SELECT is manifested by one of my SPIDs above attempting to obtain a
> shared lock.
> Thanks, rand
>|||Thanks Dan & Brian. I will experiment. Any idea why different threads
accessing different rows should even be contending for resources at
all? Dan, event_dim_id is not significant since it is not used and
always has a default value of 0.|||Look at the lock information in your original post. It's not enough that
you're only modifying one row at a time. Your procedure also reads rows:
that's why you get deadlocks. Both SPIDs have exclusive locks on one row,
but before the transactions commit, they also are trying to obtain shared or
update locks on the other transaction's row. What's strange is that the
locks represented in the original post don't match what you would get from
your procedure. I suspect that there is another procedure involved. You
have an update lock, but the procedure you posted doesn't have
WITH(UPDLOCK). It is my understanding that update locks are only obtained
when an explicit locking hint is specified.
Is it possible that other statements are issued by the application. There
are many other things that could be causing the deadlocks. That's why I
prefer to encapsulate database updates in procedures, and whenever possible,
to handle any transaction processing within those procedures. It makes
troubleshooting much, MUCH easier.
"rand" <randclark2005@.yahoo.com> wrote in message
news:1135276930.354270.55180@.g49g2000cwa.googlegroups.com...
> Thanks Dan & Brian. I will experiment. Any idea why different threads
> accessing different rows should even be contending for resources at
> all? Dan, event_dim_id is not significant since it is not used and
> always has a default value of 0.
>|||> Dan, event_dim_id is not significant since it is not used and
> always has a default value of 0.
The event_dim_id column might not be significant from your perspective but
SQL Server can't make the assumption that only one row will be returned
unless you include it in your WHERE clause. Also, the column is badly
needed to use the primary key index effectively. Check out the details of
the SEEK operator in the query plan without and with event_dim_id:
--without event_dim_id: scans all values with specified
tendered_business_period_dim_id
--and posted_business_period_dim_id
SEEK:([Sum_Item_Revenue]. [tendered_business_period_dim_id]=[@.tend
ered_busine
ss_period_dim_id]
AND
[Sum_Item_Revenue]. [posted_business_period_dim_id]=[@.posted
_business_period_
dim_id]),
WHERE:((((([Sum_Item_Revenue]. [profit_center_dim_id]=[@.profit_center_d
im_id]
AND
[Sum_Item_Revenue]. [misc_period_dim_id]=[@.misc_period_dim_i
d]) AND
[Sum_Item_Revenue].[pay_type_dim_id]=[@.pay_type_dim_id]) AND
[Sum_Item_Revenue].[emp_dim_id]=[@.emp_dim_id]) AND
[Sum_Item_Revenue].[item_dim_id]=[@.item_dim_id]) AND
[Sum_Item_Revenue]. [ordered_profit_center_dim_id]=[@.ordered
_profit_center_di
m_id])
ORDERED FORWARD)
--without event_dim_id: single row retrieved via s
SEEK:([Sum_Item_Revenue]. [tendered_business_period_dim_id]=[@.tend
ered_busine
ss_period_dim_id]
AND
[Sum_Item_Revenue]. [posted_business_period_dim_id]=[@.posted
_business_period_
dim_id]
AND
[Sum_Item_Revenue].[event_dim_id]=0 AND
[Sum_Item_Revenue]. [profit_center_dim_id]=[@.profit_center_d
im_id] AND
[Sum_Item_Revenue]. [misc_period_dim_id]=[@.misc_period_dim_i
d] AND
[Sum_Item_Revenue].[pay_type_dim_id]=[@.pay_type_dim_id] AND
[Sum_Item_Revenue].[emp_dim_id]=[@.emp_dim_id] AND
[Sum_Item_Revenue].[item_dim_id]=[@.item_dim_id] AND
[Sum_Item_Revenue]. [ordered_profit_center_dim_id]=[@.ordered
_profit_center_di
m_id])
ORDERED FORWARD)
Not only will the inefficient plan hurt performance, it can contribute to
the likelihood of deadlocks.
Hope this helps.
Dan Guzman
SQL Server MVP
"rand" <randclark2005@.yahoo.com> wrote in message
news:1135276930.354270.55180@.g49g2000cwa.googlegroups.com...
> Thanks Dan & Brian. I will experiment. Any idea why different threads
> accessing different rows should even be contending for resources at
> all? Dan, event_dim_id is not significant since it is not used and
> always has a default value of 0.
>