Remove all NOLOCK code from 3rd Party APP

1

What would happen if I remove all the NOLOCK statements from SProcs in a 3rd party app?

Will I get DEADLOCKS that I never had before? Or will I just get some blocking?

I'm wondering if I had two users:

User 1

SELECT * FROM TABLE_A WITH (NOLOCK) WHERE ColA=1

UDPATE TABLE_B SET ColB=2 WHERE ColB=1

User 2

SELECT * FROM TABLE_B WITH (NOLOCK) WHERE ColB=1

UDPATE TABLE_A SET ColA=2 WHERE ColA=1

whether that would cause no problem with NOLOCK but would cause DEADLOCK if I took the NOLOCK out?

2

You definitely could see some deadlocks you didn't see before. You will almost certainly see some blocking you didn't see before.

The main thing would be to get the best clustered indexes in place first. That will most reduce the chance of problems.

3

Good point. The code quality is nothing to write home about, so plenty of chance that the Clustered Index choices might be pants.

I wonder if I could record some activity and then play-back against a NOLOCK-free-code version to see how that shapes up?

4

You could also try putting the DB into RCSI.
As there is the potential for write-skew anomalies you would need to review all SPs and do a server side trace of any critical parts of the system. There would also be a greater load on tempdb.

5

Good point, thanks. I did try that some months back (in anticipation of NOLOCK being removed), the slow-down was dreadful. Not sure why that might be but my instinct is the amount of RBAR, rather than set-based SQL statements, in the 3rd party code ... worth having another go, maybe some things have been improved since then, but it would most likely help with the BLOCKING side.

6

If RCSI is causing a slow-down, it is worth double checking that the number of files in tempdb equals the number of cores available to the instance. I am sure you already have that one covered.

7

Absolutely. We always do that. But ... just in case ... I will double check :toast:

8

Good news :slight_smile: I've got 8 devices in TEMPDB.

I ran

WMIC CPU Get DeviceID,NumberOfCores,NumberOfLogicalProcessors

on the server and it output

DeviceID  NumberOfCores  NumberOfLogicalProcessors
CPU0      6              12

I had a look in SSMS under Properties for the server and it has Affinity set for all Processors (and shows checkboxes for 12 x CPUs) ...

So I ought to have 6 Devices, right? Dunno a) why I have 8 and b) if it is bad that it exceeds (rather than matches) the COREs?

9

Ah ... Tara's code to add additional devices http://weblogs.sqlteam.com/tarad/archive/2008/05/20/60604.aspx output a warning:

**** NOTE: Best Practice is to only use 8 tempdb files if you have more than 8 logical processors - unless you observe you have contention

so that's probably why I have 8 files. No idea if I should change that to 6 to match the number of cores though?