Lexa (10/4/2012)

---

TheSQLGuru (10/4/2012)

---

Please show the code for the sproc. Does the sproc reference any UDFs by any chance?

Also, what tool/mechanism did you use to determine that this sproc was doing the blocking and what the actual locked pages/objects were?

Kevin, I used Ignite's blocking analysis which showed the procedure at the top of the blocking chain. The procedure does not use any UDFs or cursors. I recall from your session at a SQL Saturday that those could contribute significantly to a poor performing procedure.

Here is the statement that caused the blocking (according to Ignite)

insert into #temp

(fied1_id,

fied2_id,

fied3_id,

fied4_id,

create_date,

fied5_id,

fied6_id,

select

c.fied1_id,

c.fied2_id,

ci.fied3_id,

ci.fied4_id,

ci.create_date,

count(ci.field10)as fied5_id,

sum(case when field13 is not null and field11 = 0 then 1 else 0 end)as fied6_id

from

table1 c

inner join table2 ci on

c.fied2_id = ci.fied2_id

inner join #table3 cta on

c.fied2_id = cta.fied2_id

inner join #table4 td on

ci.fied2_id = td.fied2_id and

ci.fied4_id = td.fied4_id

where

ci.create_date between @begin_date and @end_date and

ci.fied4_id is not null

group by

c.fied1_id,

c.fied2_id,

ci.fied3_id,

ci.fied4_id,

ci.create_date

While doing more digging, above statement is blocking an "ALTER INDEX ix on table2 REBUILD Partition xx" which in return blocks inserts into table2 but into partition y which is the active partition. Hence next question, is that a normal SQL Server behavior for ALTER INDEX ix on table2 partition x to block inserts that occur into table2 but partition y?

Also, would be great to know whether setting LOCK_TIMEOUT on a statement within a procedure would be inherited by the session executing that store procedure. Since our sessions are re-used by the application, would setting LOCK_TIMEOUT would unintentionally get transferred to another proc?