patternsqlMinor
Does READ UNCOMMITTED make any performance difference on static data?
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
here my wait times after i have executed the below query
I wonder if i add this to each query, does that make any performance difference?
SQL server 2014
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTEDhere 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.
For more details read How to analyse SQL Server performance.
Context
StackExchange Database Administrators Q#145480, answer score: 4
Revisions (0)
No revisions yet.