2

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

  • Unfortunately, there is no such thing as "make this query faster" magic pixie dust. To optimize performance, query tuning, indexing and more is needed. – vonPryz Aug 1 '16 at 6:32
  • Run DBCC SQLPERF('sys.dm_os_wait_stats', CLEAR) before you run the measured query (as to clean up the wait stats), then run the query and collect the stats. As posted the waits stats represent aggregated data since the server started. The presence of a large CXPACKET is further indication that you're missing indexes. Read carefully the article linked in my answer. – Remus Rusanu Aug 1 '16 at 10:15
  • @RemusRusanu after running DBCC SQLPERF('sys.dm_os_wait_stats', CLEAR) - all results became 0. is that expected? – MonsterMMORPG Aug 1 '16 at 10:45
  • @RemusRusanu i see it clears cache. now i have to wait for more query data to be collected. how can i identify CXPACKET issue? – MonsterMMORPG Aug 1 '16 at 10:46
4

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.

  • remus data is static why? because only updated manually in offline mode. and there are proper indexes. so under these circumstances, does READ UNCOMMITTED make any difference? – MonsterMMORPG Aug 1 '16 at 9:44
1

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.

  • i already have the indexes. assume that these are static and updated only offline manually. so lets say nolock or READ UNCOMMITTED, would any of them make performance difference under these circumstances? – MonsterMMORPG Aug 1 '16 at 9:43
  • Certainly it would make a difference, but I couldn't tell you what those differences are. They might be microseconds of improvement, or milliseconds. You'll have to do some testing to find out. Use SET STATISTICS TIME ON to compare. – Randolph West Aug 1 '16 at 18:46

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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