September 17, 2010 at 2:44 pm
Hello,
I am executing few Select statements. I have done this previously and the queries were running fine.
Today, I noticed that its taking really long time. When I looked at activity monitor, the spid was being blocked by itself.
I also used NOLOCK hint but it did not help.
The queries executed finally after a long time, however, I am curious that why is a SELECT with NOLOCK hint, blocking another SELECT with NOLOCK hint.
Thanks in advance!
September 17, 2010 at 2:57 pm
Not unusual.
The query's running in parallel, multiple threads executing a single query under a single spid. Nolock's not changing anything, because it's not blocking on a lock. If you look at the wait types, it'll be CXPacket for all the threads except (at least) one.
p.s. you know what no lock does, right?
http://sqlblog.com/blogs/andrew_kelly/archive/2009/04/10/how-dirty-are-your-reads.aspx
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
September 17, 2010 at 3:21 pm
Gail,
Thanks for the link. And yes, you are right about the wait type as well.
So is there a solution to the problem ?
As far as I know, SELECT uses SHARED locks and it should not block.
Out of curiosity, How are applications with 1000's of users take care of blocking ?
I did read somewhere that SQL server does the unlocking after a while and we need not do anything. I also read that we can Kill one of the spids but in this case, its the same spid.
September 17, 2010 at 3:39 pm
touchmeknot (9/17/2010)
Thanks for the link. And yes, you are right about the wait type as well.So is there a solution to the problem ?
It's not a problem.
SQL runs queries in parallel to get them faster. If it's not been running parallel before and it's slower as a parallel query, you may have a bad query plan that's made SQL think parallel is better. Or maybe the data volumes have reached a 'tipping point'. Try an update statistics, see if that helps. If not, post the query and execution plan here and we'll look.
As far as I know, SELECT uses SHARED locks and it should not block.
As I said, it's not blocking on a lock, so shared/exclusive is irrelevant here. A CXpacket wait means that one thread is waiting for another to catch up. It is not a lock.
Out of curiosity, How are applications with 1000's of users take care of blocking ?
Well written queries, good indexing
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
September 17, 2010 at 3:46 pm
You could use the maxdop option in your query.
Typically this is not a serious issue. There could be some tuning in the query to help it run faster and minimize the exposure or risk of this issue. You could also look for other pain points in the database (such as indexes and fragmentation) that could help correct some of these issues.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
September 17, 2010 at 4:21 pm
Thank you Gail and Jason.
September 17, 2010 at 4:30 pm
You're welcome
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
September 17, 2010 at 4:53 pm
I've also seen PAGEIOLATCH's block themselves, when they're waiting for drive data, and not seen any multi-threading when I was looking at sysprocesses.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply