Self-blocking
-
Hi,
I am running relatively simple query:
select
sum(isnull(BalAmt,0)),
sum(isnull(BalIntRate,0))
from DebtCurrBal c with(nolock)
join DebtBase d with(nolock) on c.DebtBaseKey = d.DebtBaseKey
where d.filekey = 775
These tables are large, they have 100 mln for DebtBase and 800 mln records for DebtCurrBal. These join keys are both indexed - as clustered index on parent side and non-clustered index on child side. FileKey is indexed too, but it has relatively small cardinality (selectivety), there are approx. 1000 records in DebtBase for each fileKey. So execution plan chooses both indexes.
But my problem is when I select * from sysProcesses, it shows that one of my threads (kpid column) is blocking itself ( SQL Server has 5 threads for this same SPID). How this can happen ? As you can see, I have nolock hints in my query. Where should I look into ?
Thanks
-
select
sum(BalAmt), -- sum does not use columns that have a null-status
sum(BalIntRate)
from DebtCurrBal c with(nolock)
join DebtBase d with(nolock)
on c.DebtBaseKey = d.DebtBaseKey
where d.filekey = 775
Maybe this way, you avoid and extra step in tempdb
Johan
Learn to play, play to learn !Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:- How to post Performance Problems
- How to post data/code to get the best help[/url]- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
-
when you look at sysprocesses do you see something like: "pagelatch_IO"
If so, this is SQL server writing pages to disk and is a normal operation. I'm not an expert but my understanding is that SQL server has done some work on the particular data or index page and now the Lazywriter process has seen that the page needs writing to disk. The blocking you're seeing is SQL temporarily locking the page until the write operation completes.
Also, look at the "waittime" column. The units on this are milliseconds. If you see that number grow where the process is taking a few seconds (even 1 second), you *might* be disk I/O bound.
HTH
Joe
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply