November 5, 2013 at 12:14 pm
Hi All,
SQL nightly jobs were hung because one of the users forgot to close the application or log off from the application which was using following query
select sum (hours)
from table_name (NOLOCK)
where …….
When nightly jobs started jobs were blocked by the select statement as jobs update the same table as in select statement. What the jobs do is update a series of tables that are used for reporting purposes
And monitoring tool showed that Select statement blocked jobs from running.
But I am not convinced just leaving the application open would cause jobs to hung ..any thoughts?
November 5, 2013 at 2:15 pm
If it was just the query you show and no other statements, no other processing updating tables or anything else, yeah, I'm a little surprised that query all by itself would block anything. But, I suspect that query is probably part of a larger process with other statements and leaving it open cause the 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
November 11, 2013 at 8:23 am
Thanks. But using NOLOCK is good?
November 11, 2013 at 8:31 am
Sqlsavy (11/11/2013)
Thanks. But using NOLOCK is good?
No. Just google its use and/or search these forums.
The only time I've seen a SELECT statement block a job was when it was against a logshipped database via a view residing in another database. The connection wasn't killed by the restore job.
November 11, 2013 at 8:36 am
1. The select statement could block if it is part of either Repeatable Read or Serializable isolation level. So, I would first check to see what isolation level it is in.
2. I would check how the application is using transactions. It is generally not a good idea to have a transaction start and then wait for user input or anything that can leave you with an open transaction due to an application being left open.
3. NOLOCK is fine if you don't care that the results aren't necessarily correct. If you want them to be correct then I wouldn't use it.
If my guess is right on #1 and you want to get rid of NOLOCK then I would look into Snapshot Isolation.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply