HiveBrain v1.2.0
Get Started
← Back to all entries
patternsqlMinor

Does READ UNCOMMITTED make any performance difference on static data?

Submitted by: @import:stackexchange-dba··
0
Viewed 0 times
uncommittedreadanymakedifferenceperformancedoesdatastatic

Problem

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;

Solution

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.

Context

StackExchange Database Administrators Q#145480, answer score: 4

Revisions (0)

No revisions yet.