February 21, 2011 at 7:29 am
I have looked into this and I am sure it is a design issue either with my indexes or maybe with the query and I suspect someone out there may be able to point me in the right direction a lot quicker than I could do it myself as I have already tried.
I have a schema that is a bit more complicated than the one attached but the one attached does kind of reproduce the problem. Though in my case, because of the data in my tables I actually get a problem further down than you get in this script but the principle is similar. It is still a key wait.
In my actual scenario I do not get a problem when doing this:
select * from vwtblGroup g
join vwtblOrder o
on o.record_id in (select distinct co.order_record_id
from vwtblCustomerOrder co
join vwtblCustomer c
on c.record_id = co.customer_record_id
where c.group_record_id = g.record_id)
where g.record_id = 206
BUT do get a problem when doing this:
select * from vwtblGroup g
join vwtblOrder o
on o.record_id in (select distinct co.order_record_id
from vwtblCustomerOrder co
join vwtblCustomer c
on c.record_id = co.customer_record_id
where c.group_record_id = g.record_id)
join vwtblOrderAmount oa
on oa.order_record_id = o.record_id
where g.record_id = 206
but I am sure it is the same issue just different data distribution. But I am not sure of the best way to get round it.
Any help please - preferably one that doesn't suggest wholesale design changes to the tables?
thanks
February 21, 2011 at 11:03 am
I'm sure I'm missing something, but what specifically is the problem? What's going wrong?
"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 21, 2011 at 4:38 pm
the problem is that although I am not updating or inserting the record I am trying to select, I am still being blocked due to this key lock (in the attached sample I try to select group record 3 which was committed and has not been affected by the transaction that is in progress). So even though none of the records in my select have been updated in the transaction, it is preventing me from selecting the ones I want to see.
This is most inconvenient! I would obviously expect to not be able to select a committed read of uncommitted data but would normally expect to select a committed read of committed data (for example doing a select of vwtblgroup by itself is ok). So I realise that this is a less straightforward example of locking where some records are locked as a result of others being locked even though they are not directly involved in the transaction.
But I am not totally sure of the solution. It's locking on a slightly more complex level and I don;t know if rewriting the query with index hints or something would help?
Have I made it any clearer?
cheers
February 21, 2011 at 8:04 pm
Ah, the leading edge of your clustered index, at least in the sample, is always zero. That's going to make for some pretty horrific statistics, probably leading to scans of the table. So even though you're inserting individual rows, you must be hitting blocking. Possibly switching the order on the cluster key columns might help. Might not since the search is for those values... I think you might need to look at a possible redesign. If you have to retrieve your records most of the time on a value that is common across most of the table... you're just going to be looking at lots of scans, and scans aren't friendly with blocks.
"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 22, 2011 at 3:46 am
you see I thought that I would be retrieving most of the time using where recordstatus_id = 0, and that because this was a clustered index then all the recordstatus_id = 0 would be next to each other so it would be more efficient because it actually does a Clustered Index Seek not a scan. I can see this in the Execution Plan. Even on this small sample of data it does a CI seek.
I am not totally familiar with the term "leading edge" but if this refers to the recordstatus_id column then in this case it is all of the rows yes, but then the record_id criteria will pinpoint the required row.
So I am not sure how relevant this is. It isn't doing a scan on the table it does a CI seek. But gets blocked. Why? Or have I misunderstood you?
thanks for your help
February 22, 2011 at 6:06 am
phancey (2/22/2011)
you see I thought that I would be retrieving most of the time using where recordstatus_id = 0, and that because this was a clustered index then all the recordstatus_id = 0 would be next to each other so it would be more efficient because it actually does a Clustered Index Seek not a scan. I can see this in the Execution Plan. Even on this small sample of data it does a CI seek.I am not totally familiar with the term "leading edge" but if this refers to the recordstatus_id column then in this case it is all of the rows yes, but then the record_id criteria will pinpoint the required row.
So I am not sure how relevant this is. It isn't doing a scan on the table it does a CI seek. But gets blocked. Why? Or have I misunderstood you?
thanks for your help
By leading edge, I mean the start of the index, the first column. That is the one used to create the histogram for the statistics, so it usually needs to be fairly selective in order for the optimizer to use the index effectively.
You're getting a seek... I'm not sure then, not seeing everything you are, it's hard to know. I'd say, based on what you've said, that you're getting reads across the indexes at the same time as the inserts are occurring, classic blocking, but I would have anticipated a scan that lead to it.
"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 22, 2011 at 7:05 am
if you use the scripts that I attached to the first post you whould be able to see for yourself. Yes in this small sample of data 100% of rows match the leading edge column but in reality I expect this to be much lower as a number of edits take place.
But anyway, I didn't think this would be relevant because I am selecting in this instance using the whole of the key for the main table ie recordstatus_id and record_id, and then joining using the record_id of the view (which itself filters on recordstatus_id = 0).
So it should know that none of the pending records fall into the selected dataset and just show me the ones that do.
Any more ideas? LCK_M_S is the information on the Key Wait.
cheers
February 22, 2011 at 7:53 am
All your views have a filter on recordstatus_id=0.
When you run the INSERT statements in a transaction and don't commit, an eXclusive lock will be held on the new rows, and these new rows all have recordstatus_id=0.
Then when you try to run the SELECT in another session, this filter will cause a seek+scan (with a Shared lock) on the
clustered (or perhaps the nonclustered) index, and when the scan reaches the rows with the X lock on it can't place a S lock because they are of course incompatible.
February 22, 2011 at 8:10 am
I know you don't want to reengineer, although I'd consider it, but have you looked at going with Read Committed Snapshot for your isolation level? That will allow you much more flexibility on reads against previously committed data. It'll just put more of a load on the tempdb.
"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 22, 2011 at 4:11 pm
ok I have it now. I saw Clustered Index Seek and without looking deeply enough was assuming it was using both columns in the Seek predicate but of course it was only using the first. When I changed the query to use an index hint to not use the CI then it worked ok.
so instead of
select * from vwtblGroup g
join vwtblCustomer c
on c.group_record_id = g.record_id
where g.record_id = 3
which would be blocked for example
select * from vwtblGroup g
join tblCustomer c WITH (INDEX(ix_Customer))
on c.group_record_id = g.record_id
and c.recordstatus_id = 0
where g.record_id = 3
would work ok.
I need to relook at my indexes and decide whether to reorganise them or just rewrite certain queries where appropriate.
thanks for your help
Viewing 10 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply