Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Could I use NOLOCK in this situation Expand / Collapse
Author
Message
Posted Wednesday, December 11, 2013 3:54 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, March 31, 2014 4:47 AM
Points: 171, Visits: 600
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
Post #1521809
Posted Wednesday, December 11, 2013 4:01 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 4:42 PM
Points: 41,516, Visits: 34,431
Have you considered one of the rowversion-based isolation levels?


Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1521812
Posted Wednesday, December 11, 2013 4:03 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 10:22 AM
Points: 2,725, Visits: 2,635
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.
Post #1521813
Posted Wednesday, December 11, 2013 4:09 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, March 31, 2014 4:47 AM
Points: 171, Visits: 600
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.
Post #1521818
Posted Wednesday, December 11, 2013 4:16 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, April 01, 2014 2:04 AM
Points: 48, Visits: 130
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
Post #1521819
Posted Wednesday, December 11, 2013 4:18 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, April 01, 2014 2:04 AM
Points: 48, Visits: 130
Correction:

It is Quite Good to use NOLOCK, but make sure you are NOTexpecting Data Consistency.
Post #1521820
Posted Wednesday, December 11, 2013 4:20 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Friday, April 11, 2014 6:21 AM
Points: 488, Visits: 37
I think that use de NOLOCK is better, because I had the same problem and this solved
Post #1521822
Posted Wednesday, December 11, 2013 4:21 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 4:42 PM
Points: 41,516, Visits: 34,431
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 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1521823
Posted Wednesday, December 11, 2013 4:31 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, March 31, 2014 4:47 AM
Points: 171, Visits: 600
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
Post #1521826
Posted Wednesday, December 11, 2013 4:34 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, April 01, 2014 2:04 AM
Points: 48, Visits: 130
NOLOCK = gives you data May be Committed or Uncommitted,
READPAST = Does not lock, but gives you only committed data
Post #1521827
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse