February 29, 2012 at 1:26 am
Hello, I have one table which is highly frequently used [usually insert statement within every few seconds]. I can see some blocking from the spid from where this insert is happening. I found that in primary index ALLOW_ROW_LOCKS and ALLOW_PAGE_LOCKS were ON. Now that I have put it off there is no blocking at all.
So on most frequently used tables should we put these options off ?
Second, I saw the blockings on this table was efecting other processes which were not dependent on this table. However once I did the above changes in index, the rest of the processes started working properly.
So can blocking on one table get escalated to other tables/processes ? If yes, how ?
February 29, 2012 at 1:40 am
So on most frequently used tables should we put these options off ?
So can blocking on one table get escalated to other tables/processes ? If yes, how ?
To the first question the answer is it depends . turning off the the feature affect the ability of the database engine to use the index during select operations. specially when dealing with large table. In other words there might be a tradeoff between better insert performance over better select performance. It could also impact the way lock escalation happens within the DB.
I haven't ever come across a scenario where a table level lock on one table impacts others table which are not related. There must be something in common that you have not yet found.
February 29, 2012 at 1:52 am
sqlnaive (2/29/2012)
I found that in primary index ALLOW_ROW_LOCKS and ALLOW_PAGE_LOCKS were ON. Now that I have put it off there is no blocking at all.So on most frequently used tables should we put these options off ?
If you have turned both of those off, the only lock that can be taken is a full table lock, so if SQL even wants one row in that table, it will have to lock the entire table. I'd say for frequently used tables that's the last thing that you want
So can blocking on one table get escalated to other tables/processes ? If yes, how ?
No.
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
February 29, 2012 at 1:55 am
February 29, 2012 at 2:09 am
Jayanth_Kurup (2/29/2012)
@Gail , what if we use a lock hint ?
What do your tests say?
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
February 29, 2012 at 2:33 am
February 29, 2012 at 3:39 am
If you have turned both of those off, the only lock that can be taken is a full table lock, so if SQL even wants one row in that table, it will have to lock the entire table. I'd say for frequently used tables that's the last thing that you want.
@Gail: Thanks, even I thought so. However I am not getting any blockages after turing both the options OFF. Could you please explain this behaviour for my understanding ? For the most frequently used tables, which level lock is required ?[/quote]
February 29, 2012 at 6:14 am
sqlnaive (2/29/2012)
If you have turned both of those off, the only lock that can be taken is a full table lock, so if SQL even wants one row in that table, it will have to lock the entire table. I'd say for frequently used tables that's the last thing that you want.
@Gail: Thanks, even I thought so. However I am not getting any blockages after turing both the options OFF.
Absolutely sure about that? Then something else is causing the queries to run one at a time.
For the most frequently used tables, which level lock is required ?
[/quote]
99% of the time the default one that SQL uses.
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 1, 2012 at 12:58 am
Msg 651, Level 16, State 1, Line 4
Cannot use the PAGE granularity hint on the table "dbo.datachurn" because locking at the specified granularity is inhibited.
Hmm interesting , I got the above error when I disabled the use of row and page locks at the index and performed a lock hint while selecting rows using the index column.
I assumed the hints are SQLs way of over ridding properties but looks like it is not case for all scenarios.
All other queries behaved as expected.
March 1, 2012 at 2:52 am
There you go.
Hints override the default behaviour, so if SQL would by default have started with a table lock, specifying a page lock hint means that the locks start at page level (note: start. Escalation is still possible). However when you disable locking modes on an index, you're not saying 'I prefer if page locks were't used', you're saying 'Page locks may NEVER be taken on this index ever.
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 1, 2012 at 3:53 am
Thanks for the detailed test and clarification. However I've changed only that thing [turning off the option] and monitoring it from past 2-3 days and there is absolutly no locking. Is there anything else which I cna check ?
March 1, 2012 at 4:24 am
Then something else is causing the queries to run serially, or you have no queries that would block each other. Nothing to do with the lock modes whic, if both of those options are off, is table lock only.
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 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply