Microsoft – Follow best practices – Part 2

In addition to my previous post, another best practice is to not use NOLOCK and READ UNCOMMITTED transaction isolation level.

Here’s an excerpt from a profiler trace

BEGIN TRAN UpdateMediaTables

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

DECLARE @BackupSetId int, @MediaSetId int, @LogDevName varchar(512)

SELECT @MediaSetId = media_set_id
FROM msdb..backupmediafamily AS bmf WITH (NOLOCK)
WHERE substring(bmf.physical_device_name,5,36) = '80A2E6DE-3E95-4645-B476-09E37306FF8C'

SELECT @BackupSetId = backup_set_id
FROM msdb..backupset WITH (NOLOCK)
WHERE media_set_id = @MediaSetId

So, not only do we have no consistency, but also a non–SARGable lookup.

I’ve updated my previous connect item to reflect this also.