Sql-server – Running a SQL Server query with as little impact as possible

scriptingsql server

I'm looking to create some basic scripts that are aimed at running on production systems.

I want them to run with as little impact as possible.

They shouldn't affect performance whenever possible, and should be safe to run.

So far I've done this by:

  1. Keeping them small
  2. Keeping them simple
  3. Using WITH (nolock)
  4. Using OPTION (MAXDOP 1)

Is there anything else I could add to limit their impact?

An example of a script I'm looking into is: 

SELECT 
    cast(SQLProcessUtilization as nvarchar) AS [SQL Server Process CPU Utilization], 
    cast(SystemIdle as nvarchar) AS [System Idle Process], 
    100 - SystemIdle - SQLProcessUtilization AS [Other Process CPU Utilization], 
    DATEADD(ms, -1 * (@ts_now - [timestamp]), GETDATE()) AS [Event Time] 
FROM 
    (SELECT
         record.value('(./Record/@id)[1]', 'int') AS record_id, 
         record.value('(./Record/SchedulerMonitorEvent/SystemHealth/SystemIdle)[1]', 'int') AS [SystemIdle], 
         record.value('(./Record/SchedulerMonitorEvent/SystemHealth/ProcessUtilization)[1]', 'int') AS [SQLProcessUtilization], [timestamp] 
     FROM 
         (SELECT 
              [timestamp], CONVERT(xml, record) AS [record] 
          FROM 
              sys.dm_os_ring_buffers WITH (nolock)
          WHERE 
              (ring_buffer_type = N'RING_BUFFER_SCHEDULER_MONITOR' 
              AND record LIKE '%<SystemHealth>%')
         ) AS x 
      ) AS y 
ORDER BY 
    [Event Time] asc;

Most of these scripts will be used to allow our first line help desk to gather information in a consistent way when requested.

Best Answer

It rather depends on what the scripts are intended to do...

If you can cope with the occasional dirty read or unrepeatable read issue (or they are truly simple enough that these are unlikely/impossible), also set your transaction handling to the most lenient mode possible with SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED. This is actually equivalent to WITH(NOLOCK) but covers the entire connection going forward instead of just the one table. There is also SET TRANSACTION ISOLATION LEVEL SNAPSHOT which generally stops your query from blocking other concurrent transactions without the risk of dirty or unrepeatable reads, but that does have some potential IO impact on tempdb.

If your queries are making any updates to the database then note that WITH(NOLOCK) support is going to be removed from UPDATE and DELETE statements at some point (see https://technet.microsoft.com/en-us/library/ms187373.aspx).

Related Question