April 24, 2009 at 8:52 am
Hi,
Can we configure a database so that it always use row level locking for A PARTICULAR TABLE only??
oh btw its ss2k5.
Thanks,
Usman
April 24, 2009 at 9:10 am
I don't believe there's a way to hint table access. You can supply hints to the queries that access this database, but if you have ad hoc queries or client side queries, they'll be able to ignore that issue. I would be very careful about forcing locking in this manner. You might get more extensive blocking than you would if you let SQL Server handle the locking itself.
"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
April 24, 2009 at 9:20 am
Thanks for the reply Grant, i agree but i have no other choice to try this if possible, as the queries are coming from a dll (which i cannot modify) and i know that they will always effect a single row,but the problem is we are getting alot of concurrent such queries (all of these are delete queries, and each query deletes a different row) therefore resulting in alot of blockages.
Is there a better way to handle it?
Thanks,
Usman
April 24, 2009 at 9:29 am
You can specify using the CREATE and ALTER INDEX commands that it should not use page locks with the ALLOW_PAGE_LOCKS = OFF option. However this would still allow table locks, and you would need to apply this to all indexes on the table in question. I'm not sure what would happen if there was no clustered index on the table, and it performed a table scan, I would assume it would be allowed to do a page lock then.
April 24, 2009 at 9:38 am
The only thing I know of is enabling/disabling the following:
ALLOW_ROW_LOCKS
ALLOW_PAGE_LOCKS
By default, the above options are ON for the indexes. You could try disabling (OFF) the option to ALLOW_PAGE_LOCKS to see if that has any effect. You have to rebuild the index for this option - and once disable you will not be able to reorganize this index. It can only be rebuilt (e.g. ALTER INDEX ... ON ... REBUILD instead of ALTER INDEX ... ON ... REORGANIZE).
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
April 24, 2009 at 9:48 am
usman.tanveer (4/24/2009)
Thanks for the reply Grant, i agree but i have no other choice to try this if possible, as the queries are coming from a dll (which i cannot modify) and i know that they will always effect a single row,but the problem is we are getting alot of concurrent such queries (all of these are delete queries, and each query deletes a different row) therefore resulting in alot of blockages.
Have you checked that your indexes support the queries? If those queries only affect one row then, with good indexes, the locking should be minimal.
Jeffrey Williams (4/24/2009)
The only thing I know of is enabling/disabling the following:ALLOW_ROW_LOCKS
ALLOW_PAGE_LOCKS
By default, the above options are ON for the indexes. You could try disabling (OFF) the option to ALLOW_PAGE_LOCKS to see if that has any effect.
It'll prevent page locks, but it means that if SQL decides to escalate locks, it will escalate to table level.
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
April 24, 2009 at 9:59 am
GilaMonster (4/24/2009)It'll prevent page locks, but it means that if SQL decides to escalate locks, it will escalate to table level.
Gail - yeah, I probably should have mentioned that - but either way, the OP is going to have to test to see if that would make any difference. My gut feeling is that the only way this is going to be fixed is optimizing the delete query itself and redeploying the application.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
April 24, 2009 at 10:00 am
GilaMonster (4/24/2009)
Have you checked that your indexes support the queries? If those queries only affect one row then, with good indexes, the locking should be minimal.
Thanks Gail, yeah i have proper indexes, and yes you are right due these indexes blockage is minimal but due to alot of concurrent queries blockage time increases.
April 24, 2009 at 10:24 am
If your indexes are all optimal you should be getting row-level locking anyway.
Have you considered snapshot isolation? It'll hit TempDB hard if you have that much activity, but you'll have no blocking. It may be worth testing out on a test server.
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
April 24, 2009 at 10:38 am
GilaMonster (4/24/2009)
Have you considered snapshot isolation? It'll hit TempDB hard if you have that much activity, but you'll have no blocking. It may be worth testing out on a test server.
In my opinion this will be beneficial if all the queries are coming from a single transaction, but in my case all the queries come from different user connections therefore different transactions, do you think this will still help?
April 24, 2009 at 11:06 am
If all queries were coming from the same connection, there would be no blocking in the first place.
I don't understand your concern with snapshot. It's an optimistic concurrently level that was added in SQL 2005. In snapshot isolation readers don't block writers and vis versa.
Can you explain more your concerns with Snapshot and why you think it won't help?
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
April 24, 2009 at 11:42 am
Sorry I didn't back sooner. Gail has already suggested the two things I would have, snapshot isolation and tuning the query & indexes. I'm pretty sure this is the answer. I just don't think anything else will support what you need without making things a lot worse than they already are.
"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
April 24, 2009 at 12:11 pm
GilaMonster (4/24/2009)
Can you explain more your concerns with Snapshot and why you think it won't help?
The only concern i have is, its a very critical production db and i am afraid enabling Snapshot isolation will give me performance hit by adding more burdon on the server (pardon me if i have lack of knowledge).
Just an FYI we are very conservative about the performance of this server, other than this issue 99% of the queries response back under 1 second.
April 24, 2009 at 1:02 pm
usman.tanveer (4/24/2009)
The only concern i have is, its a very critical production db and i am afraid enabling Snapshot isolation will give me performance hit by adding more burdon on the server (pardon me if i have lack of knowledge)
That's why I suggested trying it out on a test server (with simulated production load) first. I would never suggest just putting a change like that straight onto a production server. It's just asking for trouble.
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
April 24, 2009 at 1:05 pm
The other thing I could suggest is that you dig out some of the queries that often get blocked as well as the queries that are typically the cause of the block and post them here, along with the table structure and indexes. It's often worth getting a second opinion.
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 15 (of 21 total)
You must be logged in to reply to this topic. Login to reply