March 27, 2015 at 3:57 pm
We have a vendor's product database we found out nightly during 11-12 it spikes for the wait time on the SAN.
By using a monitor tool DPA, we found out it is a nightly job that caused this. And it nails down an update query - many sessions are executing the query, and caused intensive lock during that time.
The wait type is:
LCK_M_IX
it explains like this:
Intent-Exclusive locks are used to indicate the intention of a transaction to modify resources lower in the hierarchy by placing exclusive locks on those individual resources.
This is a vendor product, we really cannot do much about the query, plus I don't see if the query can be tuned much. since it is just an update statement and in the where clause using the primary key.
What could cause the lock, how can I resolve it?
I know the server has low RAM, could that be a problem?
Thanks,
March 27, 2015 at 4:35 pm
The lock is caused by a process needing to update, and requiring these locks. However because there are existing locks (shared or exclusive) on the pages/table, you get the intent lock. Once the other process(es) finish, this will escalate to an exclusive lock to perform the update.
No good way to fix this for the vendor. One way is to ensure your indexes make this a very, very fast update. The other is reset clustered index or keys to prevent many sessions from hitting the same pages. Another is to get more resources so this goes faster. Might be more CPU/RAM, or disk IO.
March 27, 2015 at 5:02 pm
How to reset clustered index or keys ?
This update query is using a where clause that use Primary key.
Does a rebuild of the primary key index reset the index?
Thanks,
March 30, 2015 at 2:53 am
Steve Jones - SSC Editor (3/27/2015)
However because there are existing locks (shared or exclusive) on the pages/table, you get the intent lock. Once the other process(es) finish, this will escalate to an exclusive lock to perform the update.
Err, no.
SQL will Always take intent locks (only case where it won't is if it's taking a table lock) and intent locks don't escalate into locks other than intent locks.
The way the locking process works, if SQL is trying to take an exclusive row lock is:
Take an Intent-Exclusive lock on the table to indicate the intention to lock one or more sub resources (pages within the table).
Take an Intent-Exclusive lock on the page to indicate the intention to lock one or more sub resources (rows within the page).
Take an Exclusive lock on the row and perform the operation
Release all locks.
Intent locks are compatible with all other intent locks and most other locks as well. If something is blocking on waiting for an intent lock, it suggests that the blocking process (whatever it is) is taking locks which are too small a granularity (taking locks at the table or page level). The blocked process is less likely to be the problem.
The solution would be to tune the queries involved (like with most blocking problems) and ensure that indexes support the operation. How much of that can be done in a vendor DB is another matter.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
March 30, 2015 at 9:54 am
That query is an update query that has passed parameters which are going to be set.
The where clause is based on primarykey, by using the passed ID.
I don't see anything that can be tune for the query. The update is based on a table that has a million records.
So maybe a data purge of some historical data may help?
Thanks,
March 30, 2015 at 10:17 am
GilaMonster (3/30/2015)
If something is blocking on waiting for an intent lock, it suggests that the blocking process (whatever it is) is taking locks which are too small a granularity (taking locks at the table or page level). The blocked process is less likely to be the problem.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
March 30, 2015 at 10:46 am
We are using Solarwind DPA to do the monitoring. See attached for the query.
If blocking is not the issue, what could it be?
How can I find the root of this by looking at the chart?
Thanks,
March 31, 2015 at 10:11 am
Any suggestions about the solution according to the chart I attached?
Thanks!
March 31, 2015 at 10:53 am
sqlfriends (3/30/2015)
If blocking is not the issue, what could it be?
Where did I say that blocking is not the issue?
It's lock-related waits, that's blocking by definition.
I said "the blocking process (whatever it is) is taking locks which are too small a granularity (taking locks at the table or page level). The blocked process is less likely to be the problem."
So looking at the process which is waiting for the lock is not going to be very useful, you need to identify what is holding the incompatible lock and investigate that.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
March 31, 2015 at 11:29 am
Thanks, what I found is the session is blocked by other sessions using the same query.
I checked with the application admin, and found that in the application server that scheduled a windows task nightly that delete historical data and calculate some summary data for reports. This is a vendor setup task.
It called a procedure that uses this update queries.
I think at that time period of 10pm -12:am there are many sessions are running this update and caused the blocks.
March 31, 2015 at 11:32 am
sqlfriends (3/31/2015)
Any suggestions about the solution according to the chart I attached?Thanks!
Post the text for the actual UPDATE statement and also for the statement that is blocking.
Am I right in suspecting that the blocking statement is also a similar update only with a differently keyed parameter?
A handful of reasons why single row update could result in an abnormally high level of blocking would be:
- updating a variable width character or binary column (change of row size and page split)
- an update trigger contains additional DML operations
- update a column in the primary key (cascading updates on foreign keys in other tables)
- updating a column in the clustered key (requires updates to bookmark row IDs in non-clustered indexes and possibly table page splits)
- updating a column contained in multiple indexes or an indexed view (requires updating indexes)
- someone set ALLOW_ROW_LOCKS = OFF or ALLOW_PAGE_LOCK = OFF on the table or index, interfering with normal lock escation
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
March 31, 2015 at 11:42 am
Thank you, yes the session is blocked by other sessions doing the same query with different parameters
The query is:
(@p0 datetime,@p1 datetime,@p2 bigint,@p3 datetime,@p4 datetime,@p5 bigint,@p6 int,@p7 varbinary(
max) ,@p8 int,@p9 int)
UPDATE CompressedDeviceEvents
SET StartTimeStampUtc = @p0,
StartTimeStampLocal = @p1,
StartDeviceSerial = @p2,
EndTimeStampUtc = @p3,
EndTimeStampLocal = @p4,
EndDeviceSerial = @p5,
AgentBuildNumber = @p6,
EventsBlob = @p7,
DeviceId = @p8
WHERE CompressedDeviceEventsId = @p9 -- this is the PK of this table
I do see there is a blob field. And also DeviceID is a forignkey field that is also a primary key of another table.
Also there is no trigger in this table updated, and yes, the DeviceID is in two of combined indexes.
If so, they caused the huge blocks, there is not really much we can do, correct?
Thanks
March 31, 2015 at 12:03 pm
sqlfriends (3/31/2015)
Thank you, yes the session is blocked by other sessions doing the same query with different parametersThe query is:
(@p0 datetime,@p1 datetime,@p2 bigint,@p3 datetime,@p4 datetime,@p5 bigint,@p6 int,@p7 varbinary(
max) ,@p8 int,@p9 int)
UPDATE CompressedDeviceEvents
SET StartTimeStampUtc = @p0,
StartTimeStampLocal = @p1,
StartDeviceSerial = @p2,
EndTimeStampUtc = @p3,
EndTimeStampLocal = @p4,
EndDeviceSerial = @p5,
AgentBuildNumber = @p6,
EventsBlob = @p7,
DeviceId = @p8
WHERE CompressedDeviceEventsId = @p9 -- this is the PK of this table
I do see there is a blob field. And also DeviceID is a forignkey field that is also a primary key of another table.
It looks like it's updating all non-key columns on the row. If the application is first inserting a stub row, and then it's coming back later to update EventsBlob and other columns, and there are multiple sessions doing this stuff concurrently, then in that situation it's understandable there would be an excessive amount of blocking. You would probably struggle with index fragmentation too.
If that's what's going on, the evidence would be high fragmentation, then you should consider setting a higher FILLFACTOR and turn on PAD_INDEX. First check with the application vendor; they should already be aware of this potential situation and have reccomendations for mitigating it.
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
March 31, 2015 at 12:24 pm
Thank you, yes you are right they are updating all non-primary key of the table.
Your answer is very helpful, thanks.
I will check into index fragmentation. and contact vendor if there is a better solution.
March 31, 2015 at 1:07 pm
If that was my system, I'd start by looking at the execution plan and seeing if the index is optimal. Good chance it isn't.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply