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;
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
.