March 28, 2008 at 3:09 am
I should also say that the Execution plan says it is going to do a Clustered Index Seek.
March 28, 2008 at 3:31 am
This isn't so much how the optimizer works but how locking works.
It all depends on how many rows your query returns. If it returns too many rows then SQL Server won't have the resources to place a shared lock on the row (or key) so it escalates it up to a page lock. And of course, if there still aren't enough resources it will escalate it further.
I did a simple test (using the code below) on my workstation and here's what I found.
The query returned 300 rows or so and the lock used was a shared table lock.
I modified the query to return less rows (around 30) and the locks used were a combination of row and key locks.
I also tried a similar query using the LIKE keyword, as you did. Didn't make a difference. The execution plan, whether it's an index seek or a scan doesn't matter so much. It's how much resource SQL Server has available in order to place granular locks. On my workstation, for this table, it's around 70 rows. Admittedly that seems a little low but I'm working with a fairly wide table with a couple of text columns and that makes a difference. If I just selected an integer column I was able to return over 5000 rows before it went from row/key locks to table locks (I'm sure that it would go to page locks somewhere in between but I didn't bother going further with this).
begin tran
select * from my_table with (holdlock) where id > 1500 and id < 2000
exec sp_lock 55
rollback tran
March 28, 2008 at 3:41 am
I just realised that my previous post didn't necessarily give you a solution.
You could try and filter the number of rows you're returning even further. This will have two effects. It will make the query run faster and it would possibly result in SQL Server using more granular locks.
You could try and optimize the query further (and/or add indexes if appropriate). This won't solve the locking issue itself but it will result in SQL Server holding the locks in place for less time.
You could use the NOLOCK hint if the business logic allows for this and you're comfortable with the implications.
Add memory, which will give SQL Server more resources to allocate towards granular locking. Not sure how effective this would be though - you'd have to test this.
Improve overall system performance (e.g. more striping, placing indexes in seperate file group on dedicated disk, etc...)
That's all I can think of for now.
March 28, 2008 at 3:59 am
Thanks Karl,
I think you have answered my question in terms of the cause of the problem. Sorting an overall solution may be more tricky for us.
I am still slightly puzzled though as on our test database we were getting PAGE level locking, until we re-built the Unique Clustered Index on the CONTACT table. Once this was done we got TABLE level locking. Now I would have thought that more resources would have been required before the index was rebuilt?
Ben
March 28, 2008 at 4:20 am
ben.ashton (3/28/2008)
I am still slightly puzzled though as on our test database we were getting PAGE level locking, until we re-built the Unique Clustered Index on the CONTACT table. Once this was done we got TABLE level locking.
Rebuilding your indexes automatically updates the statistics. Please see my previous post for the possible implications of that.
John
March 28, 2008 at 4:22 am
You could force a page lock by using the paglock hint. I'm not sure on the implications on doing this but Kalen Delaney wrote a good article in SQL Server Magazine about using locking hints and the implications. Problem is I can't get access to it.
This doesn't answer why the system is behaving so differently since rebuilding the indexes. Maybe something else changed at the same time you rebuilt the indexes. More data, reduction in memory available to SQL Server, or something else.
March 28, 2008 at 5:21 am
Yes, the conclusion we have come to is that we either use the (PAGLOCK) hint or change the Transaction Isolation Level to READ UNCOMMITTED when we process SELECTS. Obviously changing the isolation level is a big step.
Is there any way of changing these globally in SQL server or does it have to be done at the transaction level?
March 28, 2008 at 5:28 am
I don't think you want to do it globally if it's just for SELECT statements. Just use the NOLOCK hint in queries that you don't want to lock the tables. Make sure you understand what dirty reads are and their implications.
John
Viewing 8 posts - 16 through 22 (of 22 total)
You must be logged in to reply to this topic. Login to reply