December 11, 2013 at 3:54 am
Hi All,
Lately we have been experiencing a fair amount of blocking issues in our DB and some of it down to queries constantly reading heavily updated tables for count tallies.
This happens every 10 mins or so, basically the queries are select count(*) for the day with different filters.
As this info is just for glancing at on a stat screen not used for any reporting, its fine for the tallies to be a few numbers out.
Is this a scenario where using NOLOCK, or rather READPAST would be okay to use?
Thanks
D
December 11, 2013 at 4:01 am
Have you considered one of the rowversion-based isolation levels?
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
December 11, 2013 at 4:03 am
Yes, since you're aware of the NOLOCK consequences, and as you're describing a bit for what you're going to use it for, than you can do that. Sometimes I do the same after I ensure there is no unexpected affecting.
Igor Micev,My blog: www.igormicev.com
December 11, 2013 at 4:09 am
GilaMonster (12/11/2013)
Have you considered one of the rowversion-based isolation levels?
I have considered this, but it will mean downtime to Enable READ_COMMITTED_SNAPSHOT ON, which unfortunately I cannot do anytime soon.
Also I have read enabling row version-ing may have a negative effect on performance due to more writes.As our hardware is fairly outdated I don't want to risk this.
December 11, 2013 at 4:16 am
As the NOLOCK would ridoff the Lockings on the Situation, and as per your statement
"Not Used in Any REPORTING"
It is Quite Good to use NOLOCK, but make sure you are expecting Data Consistency.
Thanks,
Prabhu
December 11, 2013 at 4:18 am
Correction:
It is Quite Good to use NOLOCK, but make sure you are NOTexpecting Data Consistency.
December 11, 2013 at 4:20 am
I think that use de NOLOCK is better, because I had the same problem and this solved
December 11, 2013 at 4:21 am
bugg (12/11/2013)
GilaMonster (12/11/2013)
Have you considered one of the rowversion-based isolation levels?I have considered this, but it will mean downtime to Enable READ_COMMITTED_SNAPSHOT ON, which unfortunately I cannot do anytime soon.
Also I have read enabling row version-ing may have a negative effect on performance due to more writes.As our hardware is fairly outdated I don't want to risk this.
Cool, as long as you have considered it.
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
December 11, 2013 at 4:31 am
Thanks for the input guys.
I think i will introduce READPAST on 1 or 2 of the queries and run them in parallel with the existing ones and see what the difference might be over a period of time.
Cheers
D
December 11, 2013 at 4:34 am
NOLOCK = gives you data May be Committed or Uncommitted,
READPAST = Does not lock, but gives you only committed data
December 11, 2013 at 4:35 am
bugg (12/11/2013)
GilaMonster (12/11/2013)
Have you considered one of the rowversion-based isolation levels?I have considered this, but it will mean downtime to Enable READ_COMMITTED_SNAPSHOT ON, which unfortunately I cannot do anytime soon.
Also I have read enabling row version-ing may have a negative effect on performance due to more writes.As our hardware is fairly outdated I don't want to risk this.
I think here Gail was thinking of enabling another isolation level (probably READ UNCOMMITED) in a programmable object, not on database level.
So instead of putting NOLOCK hint on every table you're using in a SP, you can define an isolation level at the same beginning of the SP and it's valid for all tables further.
Regards,
IgorMi
Igor Micev,My blog: www.igormicev.com
December 11, 2013 at 7:21 am
IgorMi (12/11/2013)
bugg (12/11/2013)
GilaMonster (12/11/2013)
Have you considered one of the rowversion-based isolation levels?I have considered this, but it will mean downtime to Enable READ_COMMITTED_SNAPSHOT ON, which unfortunately I cannot do anytime soon.
Also I have read enabling row version-ing may have a negative effect on performance due to more writes.As our hardware is fairly outdated I don't want to risk this.
I think here Gail was thinking of enabling another isolation level (probably READ UNCOMMITED) in a programmable object, not on database level.
So instead of putting NOLOCK hint on every table you're using in a SP, you can define an isolation level at the same beginning of the SP and it's valid for all tables further.
Regards,
IgorMi
No, when she said rowversion, she meant READ COMMITTED SNAPSHOT or SNAPSHOT.
"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
December 11, 2013 at 7:32 am
Grant Fritchey (12/11/2013)
IgorMi (12/11/2013)
bugg (12/11/2013)
GilaMonster (12/11/2013)
Have you considered one of the rowversion-based isolation levels?I have considered this, but it will mean downtime to Enable READ_COMMITTED_SNAPSHOT ON, which unfortunately I cannot do anytime soon.
Also I have read enabling row version-ing may have a negative effect on performance due to more writes.As our hardware is fairly outdated I don't want to risk this.
I think here Gail was thinking of enabling another isolation level (probably READ UNCOMMITED) in a programmable object, not on database level.
So instead of putting NOLOCK hint on every table you're using in a SP, you can define an isolation level at the same beginning of the SP and it's valid for all tables further.
Regards,
IgorMi
No, when she said rowversion, she meant READ COMMITTED SNAPSHOT or SNAPSHOT.
Correct. I connected it with the NOLOCK question.
Igor Micev,My blog: www.igormicev.com
December 11, 2013 at 7:53 am
IgorMi (12/11/2013)
I think here Gail was thinking of enabling another isolation level (probably READ UNCOMMITED) in a programmable object, not on database level.
No, I was not. I was asking whether he'd considered using either READ COMMITTED SNAPSHOT or SNAPSHOT isolation levels (the two rowversion-based isolation levels). READ UNCOMMITED is not a rowversion-based isolation level, it's lock-based.
With the exception of the READ COMMITTED SNAPSHOT, isolation levels aren't implemented on the database level.
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
December 11, 2013 at 9:49 am
bugg (12/11/2013)
GilaMonster (12/11/2013)
Have you considered one of the rowversion-based isolation levels?I have considered this, but it will mean downtime to Enable READ_COMMITTED_SNAPSHOT ON, which unfortunately I cannot do anytime soon.
Also I have read enabling row version-ing may have a negative effect on performance due to more writes.As our hardware is fairly outdated I don't want to risk this.
You do not need to have downtime to enable SNAPSHOT isolation.
alter database [MyDatabese] set allow_snapshot_isolation on;
You only need some momentary downtime to enable READ_COMMITTED_SNAPSHOT.
alter database [MyDatabese] set read_committed_snapshot on with rollback immediate;
I have never seen any issues with performance due to using a rowversion isolation level, and I have used it on more than 1000 databases.
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply