October 2, 2012 at 3:08 pm
We have a proc that causes high blocking on inserts. The proc joins 3 tables (one of them is where the inserts get blocked) and does some aggregation. Having transaction isolation level set to read uncommitted does not help, blocking still occurs.
1. The fact that the transaction isolation level is set at the beginning of the proc, does it apply to this session when it is reused in the application? We reused sessions so is it possible that a different proc will "inherit" this isolation level when the session is reused?
2. To avoid blocking on the inserts (if this proc fails/times outs sometimes is acceptable), can Lock_Timeout be used? Would that be a session wide setting that could be "passed" to other procedure executions if sessions are re-used in our app?
Thanks
October 2, 2012 at 5:22 pm
Not sure that I quite followed, but it sounded like you were setting READ UNCOMMITTED on the stored procedure that was doing the writing, not the reading. Is that correct?
October 3, 2012 at 7:26 am
coronaride (10/2/2012)
Not sure that I quite followed, but it sounded like you were setting READ UNCOMMITTED on the stored procedure that was doing the writing, not the reading. Is that correct?
The procedure does reading, in a form of a 3 joins + aggregation. Read uncommitted was set to prevent blocking when the reading occurs on high volume data.
October 4, 2012 at 7:50 am
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?
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
October 4, 2012 at 9:14 am
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?
October 4, 2012 at 10:17 am
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?
1) Again, what are the exact objects and type of locks that are blocking?? Maybe there is something with schema stability going on here? I note that rebuilds take pretty strict locks, and partitioning can introduce other issues/features with lock types and escalations.
2) What else is in this sproc? It is possible that you are not getting proper information from ignite and something else is at fault.
3) I wonder if your use of between @begin_date and @end_date is leading to parameter sniffing and a bad cached query plan. I have seen that a LOT in cases where the date range can be widely varying.
4) Assuming you have a .NET app here (or other connectivity types as well I think), connection pooling is likely in play, and then you need to dig into what sp_reset_connection does to see if LOCK_TIMEOUT is one of the many things affected by that call.
From BOL: At the beginning of a connection, this setting has a value of -1. After it is changed, the new setting stays in effect for the remainder of the connection. So again the question becomes what happens to that connection if connection pooling is in play. If it isn't, then when the connection is severed, a new connection will again start with the default.
I note from this link (http://stackoverflow.com/questions/596365/what-does-sp-reset-connection-do) that SETs are returned to their default by sp_reset_connection, so it should go back to "infinite". Note I do not know the provenance of that post.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
October 4, 2012 at 10:49 am
TheSQLGuru (10/4/2012)
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?
1) Again, what are the exact objects and type of locks that are blocking?? Maybe there is something with schema stability going on here? I note that rebuilds take pretty strict locks, and partitioning can introduce other issues/features with lock types and escalations.
2) What else is in this sproc? It is possible that you are not getting proper information from ignite and something else is at fault.
3) I wonder if your use of between @begin_date and @end_date is leading to parameter sniffing and a bad cached query plan. I have seen that a LOT in cases where the date range can be widely varying.
4) Assuming you have a .NET app here (or other connectivity types as well I think), connection pooling is likely in play, and then you need to dig into what sp_reset_connection does to see if LOCK_TIMEOUT is one of the many things affected by that call.
From BOL: At the beginning of a connection, this setting has a value of -1. After it is changed, the new setting stays in effect for the remainder of the connection. So again the question becomes what happens to that connection if connection pooling is in play. If it isn't, then when the connection is severed, a new connection will again start with the default.
I note from this link (http://stackoverflow.com/questions/596365/what-does-sp-reset-connection-do) that SETs are returned to their default by sp_reset_connection, so it should go back to "infinite". Note I do not know the provenance of that post.
Thanks. I don't have all of the details at the moment about what are the exact objects and type of locks that are blocking, I do see that ALTER INDEX was waiting on LCK_M_SCH_M schema modification lock and the inserts waiting on LCK_M_IX. There isn't much else in the proc, just internal logging. We did add WITH RECOMPILE to the proc to avoid parameter sniffing as you indicated. It is a .NET app with connection pooling so I assume using LOCK_TIMEOUT isn't an option without some tweaking?
October 4, 2012 at 2:28 pm
Thanks. I don't have all of the details at the moment about what are the exact objects and type of locks that are blocking, I do see that ALTER INDEX was waiting on LCK_M_SCH_M schema modification lock and the inserts waiting on LCK_M_IX. There isn't much else in the proc, just internal logging. We did add WITH RECOMPILE to the proc to avoid parameter sniffing as you indicated. It is a .NET app with connection pooling so I assume using LOCK_TIMEOUT isn't an option without some tweaking?
I thought that would be the case (schema lock blocking). Can you not rearrange the timing of either process so things don't collide? Is REBUILD required? What are you using to deterime that this index needs to be rebuilt? Can't tell you the number of cilent's I have come across that simply use the SQL Server Maintenance Plan crap and suffer for it.
My take on connection pooling is that if you set the LOCK_TIMEOUT for a connection, when it gets sent to the pool and reused by the next call sp_reset_connection will be called and the LOCK_TIMEOUT will be reset to default. Note again I have done no testing to validate that and I don't know if that link represented reality or not.
But if you don't care if that sproc gets killed and rolled back for a lock timeout, why are you bothering to try to run it then - just schedule it for when index maintenance isn't running. Hmm, it is possible that the schema lock is just a short term thing and maybe you could put a check for that lock's existence in the process that kicks off the sproc?
Also, if you do use LOCK_TIMEOUT, note there is a specific error thrown and you might be able to iterate the things that get blocked. Actually, I don't think that setting will help - the temp table insert in the sproc is running, it wasn't actually waiting on a lock. And you likely cant/shouldn't mess with lock timeout for the index rebuild. And I doubt you want all your inserts to timeout and rollback (although you could loop them with a waitfor delay in place I suppose if they timed out for lock acquisition.
Just change the schedule ... 😎
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply