2

I have a quick question: Is it safe to run IndexOptimize for stats update under SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED to avoid blocking as below?

USE [DBA]
GO

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED; -- turn it on

EXECUTE [dbo].[IndexOptimize] 
@Databases = 'XYZ', 
@FragmentationLow = NULL, 
@FragmentationMedium = NULL, 
@FragmentationHigh = NULL, 
@UpdateStatistics = 'ALL', 
@Indexes = 'XYZ.dbo.XYZ',  
@StatisticsSample = 30, 
@MaxDOP = 8, 
@LogToTable = 'Y';
GO

SET TRANSACTION ISOLATION LEVEL READ COMMITTED; -- turn it off
0

1 Answer 1

4

probably not

Ola already uses SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED in the queries that get run to gather data. Adding the outer SET statement will not do much of anything.

Though there have been some bugs in SQL Server that result in isolation level hints not being honored, it's not clear from your question if that's what's happening here.

The blocking likely comes from actually updating the statistics, which won't be helped by adding an UNCOMMITTED anything to anywhere, because it takes a Sch-M lock, which cannot be prevented.

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.