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
)