Does READ UNCOMMITTED make any performance difference on static data?

Posted on

Question :

I have some static tables that gets a lot of select queries

I wonder if i add this to each query, does that make any performance difference?

SQL server 2014

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

here my wait times after i have executed the below query

    select * 
from sys.dm_os_wait_stats
WHERE [wait_type] NOT IN (
        N'CLR_SEMAPHORE',    N'LAZYWRITER_SLEEP',
        N'RESOURCE_QUEUE',   N'SQLTRACE_BUFFER_FLUSH',
        N'SLEEP_TASK',       N'SLEEP_SYSTEMTASK',
        N'WAITFOR',          N'HADR_FILESTREAM_IOMGR_IOCOMPLETION',
        N'CHECKPOINT_QUEUE', N'REQUEST_FOR_DEADLOCK_SEARCH',
        N'XE_TIMER_EVENT',   N'XE_DISPATCHER_JOIN',
        N'LOGMGR_QUEUE',     N'FT_IFTS_SCHEDULER_IDLE_WAIT',
        N'BROKER_TASK_STOP', N'CLR_MANUAL_EVENT',
        N'CLR_AUTO_EVENT',   N'DISPATCHER_QUEUE_SEMAPHORE',
        N'TRACEWRITE',       N'XE_DISPATCHER_WAIT',
        N'BROKER_TO_FLUSH',  N'BROKER_EVENTHANDLER',
        N'FT_IFTSHC_MUTEX',  N'SQLTRACE_INCREMENTAL_FLUSH_SLEEP',
        N'DIRTY_PAGE_POLL',  N'SP_SERVER_DIAGNOSTICS_SLEEP')
order by wait_time_ms desc;

enter image description here

Answer :

If your data is immutable mark the database as read only. The query execution engine recognizes this and skips locking. However the benefits are marginal. If locking is of concern it means that a) you have locking conflicts, which implies data is not immutable or b) you acquire too many locks, which implies large scans which needs to be addressed by adding an index as needed.

For more details read How to analyse SQL Server performance.

READ UNCOMMITTED is the same as using NOLOCK, and if the data isn’t changing, it will provide the same performance benefits as NOLOCK.

This is NOT a go-faster button for everything, though. Uncommitted data is uncommitted data, and should not be trusted if the underlying data does change often. Make very sure you use it in the right context.

I’d also look at covering indexes, meaning indexes that satisfy your queries. You may also find joy with filtered indexes. I’d sooner look there before turning on NOLOCK / READ UNCOMMITTED.

Leave a Reply

Your email address will not be published. Required fields are marked *