Running a SQL Server query with as little impact as possible

Question

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.

• Shanky: Some more information could come if you share sample script. Are these monitoring scripts ?
• Reaces: @Shanky I added an example. The purpose behind it is to allow our helpdesk to give consistent answers to questions coming from other departments. The question often arises to check cpu usage or memory usage on our instances. And as our current monitoring system does not have these statistics readily available for our helpdesk, I was tasked with creating some scripts to allow them to give consistent replies before we set up a way for them to access our MDW.
• Colin 't Hart: Seems to me your current monitoring system is inadequate if it doesn't have such basic metrics readily available.
• Reaces: @Colin'tHart I agree. I'm very new at my current job and it's the first thing I want to change. Consistent and comprehensive statistic gathering. But you don't change everything over night, and I want first to get them using a template to respond to queries about the statistics, some of the responses I've seen through our mailbox have been lackluster.
• Aaron Bertrand: Wouldn't perfmon or resource monitor or procmon be better ways to capture CPU metrics than the system_health session?

Answer 1

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

• Reaces: Currently the scripts are meant to gather information on the servers and the databases on them.