February 17, 2017 at 3:24 am
Hello,
I am troubleshooting lock waits and timeouts in our application. I found that one cause for the lock waits are page locks on some indexes, and tried to improve the situation by disallowing page locks. However, this has no effect - I see almost the same number of exclusive (X) page locks held!
So my question is: Is ALLOW_PAGE_LOCKS = OFF just a hint? Do I need to take additional steps to make this change effective?
Here is the SQL I'm using to set the page locks off:alter index PK_MY_TABLE on MY_TABLE set (ALLOW_PAGE_LOCKS = OFF)
I verified with this query:select
t.object_id, t.name, t.lock_escalation_desc, i.index_id, i.name, i.type_desc, i.is_unique, i.allow_page_locks
from sys.indexes i
join sys.tables t on i.object_id = t.object_id
where t.type = 'U'
order by t.name, i.index_id
This one I use to get the number of page locks grouped by index:SELECT
wl.request_session_id, pl.object_id, tb.name as table_name, ix.index_id, ix.name as index_name, wl.request_mode, count(1) as count
from sys.dm_tran_locks wl
join sys.partitions pl on wl.resource_associated_entity_id = pl.hobt_id
join sys.tables tb on pl.object_id = tb.object_id
join sys.indexes ix on ix.object_id = tb.object_id and pl.index_id = ix.index_id
WHERE resource_type in ('PAGE')
AND request_status = 'GRANT'
and request_mode not in ('IX' , 'IS', 'IU')
group by wl.request_session_id, pl.object_id, tb.name, ix.index_id, ix.name, wl.request_mode
Your help is much appreciated!
Matthias
February 17, 2017 at 6:21 am
So you 're getting page locks because the engine has determined that there will be too many rows to use row locking. Trying to improve performance by forcing the locking of rows over pages won't improve performance. Instead, you'll see lots more memory use in order to lock rows. What you really need to do is reduce the number of locks necessary for your queries. Generally that means tuning the queries, applying indexes appropriate, etc..
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
February 17, 2017 at 6:44 am
Grant Fritchey - Friday, February 17, 2017 6:21 AMSo you 're getting page locks because the engine has determined that there will be too many rows to use row locking. Trying to improve performance by forcing the locking of rows over pages won't improve performance. Instead, you'll see lots more memory use in order to lock rows.
Thanks, Grant. I'm aware of these problems. However, my actual question was: Is this setting just a hint to the server to "stretch" the use of row / key locks a little longer, but when a hard memory limit is reached, page locks are still used? Is this somewhere documented?
We already solved concurrency issues by disabling page locks on some selected indexes. In my experience it can be a meaningful way, especially when rewriting the SQL is not an option.
February 17, 2017 at 7:25 am
It's a bool. It should simply turn off page locking. In fact, when it's turned off, you'll get an error when you try to reorganize the index. Check the INDEXPROPERTY function to be sure that type of locking is disabled.
I'm totally not a fan of this type of tuning. SQL Server is generally great an managing locking, memory, etc.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
February 17, 2017 at 7:56 am
I checked again with the INDEXPROPERTY function you mentioned. The 'IsPageLockDisallowed' property shows the expected values:
1 when ALLOW_PAGE_LOCKS = OFF
0 when ALLOW_PAGE_LOCKS = ON
I still get page locks.
Regarding being a fan of "this type" of tuning: I'm not a fan either, and we try to get along with the defaults as much as we can get. But sometimes weird stuff happens, like strange choices of the query optimizer in Oracle, lock hash collisions in SQL Server, and page locks with plenty of RAM being available.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply