March 23, 2015 at 2:38 pm
I am trying to come up with some tuning recommendations for a design pattern I have seen in several places that is causing blocking. I have a simple table:
CREATE TABLE bad_blocking (
messageid INT identity(1, 1) NOT NULL
,message_body VARBINARY(max) NOT NULL
,attrib_1 INT NOT NULL
,attrib_2 INT NOT NULL
,attrib_3 INT NOT NULL
,startdate DATETIMEOFFSET(2)
,status_flag SMALLINT NOT NULL
)
There are well over 10 million records in this table. The attributes (attrib_1, attrib_2, and attrib_3) are all very low cardinality. The following update statement runs several hundred times per hour:
UPDATE bad_blocking
SET StartDate = getdate()
,attrib_1 = (attrib_1 + 1)
,Status_flag = 1
WHERE messageid IN (
SELECT TOP 1000 messageid
FROM bad_blocking WITH (UPDLOCK)
WHERE status_flag = 0
AND attrib_2 = 15 -- can vary
AND attrib_3 < 20 -- can vary
ORDER BY messageid
)
Because of the frequency of this update, in addition to several hundred inserts into the table every hour, we are seeing heavy blocking. Even adding an index on messageid, attrib_2, and attrib_3 helps only for a very short while - the execution plan will use the new index for 15 minutes or so, and then revert to the Clustered index (which is on the messageid). Because of the varbinary element, which averages 500 bytes, that is on the fat side.
Any suggestions would be appreciated. TIA.
March 23, 2015 at 4:13 pm
The READPAST hint is very helpful in those types of situations. I believed it was designed to help handle these types of "queue assignment" queries.
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
March 23, 2015 at 7:07 pm
tim_harkin (3/23/2015)
I am trying to come up with some tuning recommendations for a design pattern I have seen in several places that is causing blocking. I have a simple table:
CREATE TABLE bad_blocking (
messageid INT identity(1, 1) NOT NULL
,message_body VARBINARY(max) NOT NULL
,attrib_1 INT NOT NULL
,attrib_2 INT NOT NULL
,attrib_3 INT NOT NULL
,startdate DATETIMEOFFSET(2)
,status_flag SMALLINT NOT NULL
)
There are well over 10 million records in this table. The attributes (attrib_1, attrib_2, and attrib_3) are all very low cardinality. The following update statement runs several hundred times per hour:
UPDATE bad_blockingSET StartDate = getdate()
,attrib_1 = (attrib_1 + 1)
,Status_flag = 1
WHERE messageid IN (
SELECT TOP 1000 messageid
FROM bad_blocking WITH (UPDLOCK)
WHERE status_flag = 0
AND attrib_2 = 15 -- can vary
AND attrib_3 < 20 -- can vary
ORDER BY messageid
)
Because of the frequency of this update, in addition to several hundred inserts into the table every hour, we are seeing heavy blocking. Even adding an index on messageid, attrib_2, and attrib_3 helps only for a very short while - the execution plan will use the new index for 15 minutes or so, and then revert to the Clustered index (which is on the messageid). Because of the varbinary element, which averages 500 bytes, that is on the fat side.
Any suggestions would be appreciated. TIA.
What are the indexes and constraints for this table? Also, take a look at the second link under "Helpful Links" in my signature line below for how to post the execution plan so that we can do a deeper dive for you.
Last but not least, is the ORDER BY really important?
--Jeff Moden
Change is inevitable... Change for the better is not.
March 30, 2015 at 8:43 am
compare the 2 updates below.
From looking at the plan of the update on the windowed result set, it looks like there is one access to the table, whereas the "before" query has 2.
I tried to give the table a couple of rows, using numbers table but you would have to compare the result sets using of both queries, and then if they are the same, use Statsitcs io and Time on to see if there is any difference.
UPDATE a
SET StartDate = getdate()
,attrib_1 = (attrib_1 + 1)
,Status_flag = 1 --select *
from
bad_blocking a
WHERE messageid IN (
SELECT TOP 1000 messageid
FROM bad_blocking WITH (UPDLOCK)
WHERE status_flag = 0
AND attrib_2 = 15 -- can vary
AND attrib_3 < 20 -- can vary
ORDER BY messageid
)
UPDATE window
SET StartDate = getdate()
,attrib_1 = (attrib_1 + 1)
,Status_flag = 1 --select *
from (select top 1000 messageid,StartDate,attrib_1,Status_flag
FROM bad_blocking WITH (UPDLOCK)
WHERE status_flag = 0
AND attrib_2 = 15 -- can vary
AND attrib_3 < 20 -- can vary
ORDER BY messageid) as window
Compare the data to be updated with below
select messageid
from
bad_blocking a
WHERE messageid IN (
SELECT TOP 1000 messageid
FROM bad_blocking WITH (UPDLOCK)
WHERE status_flag = 0
AND attrib_2 = 15 -- can vary
AND attrib_3 < 20 -- can vary
ORDER BY messageid
)
Except
select messageid
from (select top 1000 messageid,StartDate,attrib_1,Status_flag
FROM bad_blocking WITH (UPDLOCK)
WHERE status_flag = 0
AND attrib_2 = 15 -- can vary
AND attrib_3 < 20 -- can vary
ORDER BY messageid) as window
--AND
select messageid
from (select top 1000 messageid,StartDate,attrib_1,Status_flag
FROM bad_blocking WITH (UPDLOCK)
WHERE status_flag = 0
AND attrib_2 = 15 -- can vary
AND attrib_3 < 20 -- can vary
ORDER BY messageid) as window
Except
select messageid
from
bad_blocking a
WHERE messageid IN (
SELECT TOP 1000 messageid
FROM bad_blocking WITH (UPDLOCK)
WHERE status_flag = 0
AND attrib_2 = 15 -- can vary
AND attrib_3 < 20 -- can vary
ORDER BY messageid
)
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply