Four comments. First, if you must do this, arrange to have a backup of the database restored onto a test system where you can use DBCC DROPCLEANBUFFERS.
Second, use logical reads instead of physical reads. I don't like to rely upon physical reads and just duration for query optimization in most cases. If you focus on the logical reads and reduce them, then the physical reads will normally follow. In some cases, you really must read a lot of data and reducing the logical reads is not an option. Sometimes a gut check will help. If you are doing 10000 logical page reads on a 1000 page table to get 1 record, something is very wrong. (Seen bad stuff like this.) If you have a report on all the data, then 1000 logical reads on a 1000 page table is great.
Third, measure the performance. You can use the following SET statements on the connection used to test the query. It will give CPU time used and IO use for each table. These SETs only need to be executed once. They remain active until the connection closes or they are set OFF. This is fine for 1 or a few queries, but will be very noisy for some code.
SET STATICS IO ON
SET STATISTICS TIME ON
It will look something like below.
Table 'Workfile'. Scan count 0, logical reads 0, physical reads 0, page server reads 0, read-ahead reads 0, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.
Table 'Worktable'. Scan count 1, logical reads 22693, physical reads 0, page server reads 0, read-ahead reads 0, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.
Table 'syssingleobjrefs'. Scan count 1, logical reads 2, physical reads 0, page server reads 0, read-ahead reads 0, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.
Table 'sysschobjs'. Scan count 1, logical reads 51, physical reads 0, page server reads 0, read-ahead reads 0, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.
Table 'syspalnames'. Scan count 1, logical reads 2, physical reads 0, page server reads 0, read-ahead reads 0, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, page server reads 0, read-ahead reads 0, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.
Table 'sysobjvalues'. Scan count 1, logical reads 3, physical reads 0, page server reads 0, read-ahead reads 0, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.
Table 'syscolpars'. Scan count 1, logical reads 9, physical reads 0, page server reads 0, read-ahead reads 0, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.
SQL Server Execution Times:
CPU time = 859 ms, elapsed time = 913 ms.
If all you need is a summary of the total performance, something like the flowing might be good enough. SQL Server 2014 or later is required; otherwise, the elapsed time needs to be replaced with something that works (e.g., datediff). BTW, the request_id is included just in case MARS is enabled. Don't leave the above SET statements enabled for this one unless you want a good example of noise. (The results here and above are for different queries and will not match.)
DECLARE @time int, @cpu int, @logical bigint, @reads bigint, @writes bigint
SELECT @time = -total_elapsed_time, @cpu = -cpu_time, @logical = -logical_reads, @reads = -reads, @writes = -writes
FROM sys.dm_exec_requests WITH (NOLOCK) WHERE session_id = @@SPID AND request_id = CURRENT_REQUEST_ID()
WAITFOR DELAY '00:00:01.234';-- code to measure - wait
DECLARE @t1 int=0, @t2 float=0; while @t1<1000000 begin set @t2=@t2+.5*power(.5,@t1) set @t1+=1 end;-- code to measure - cpu
SELECT TOP 1000000 t1.*, t2.name as [x] INTO temp_xxxx FROM [sys].[all_objects] t1 CROSS JOIN [sys].[all_columns] t2; SELECT COUNT(*) FROM temp_xxxx; DROP TABLE temp_xxxx;-- code to measure - logical reads and writes
SELECT @time += total_elapsed_time, @cpu += cpu_time, @logical += logical_reads, @reads += reads, @writes += writes
FROM sys.dm_exec_requests WITH (NOLOCK) WHERE session_id = @@SPID AND request_id = CURRENT_REQUEST_ID()
RAISERROR('time %d ms, cpu %d ms, logical %I64d pages, reads %I64d pages, writes %I64d pages', 10, 1, @time, @cpu, @logical, @reads, @writes) WITH NOWAIT
(1000000 rows affected)
(1 row affected)
time 3228 ms, cpu 1964 ms, logical 71094 pages, reads 56 pages, writes 18303 pages
Fourth, if the code is already optimized there might be other issues. If you have 100GB of active data and only 8 GB of RAM.... (The past 20 years, I remember one case it was HW. The query really sucked too, but a vCPU issue pushed it over the edge. A performance issue is going to be likely an optimization issue with your query. However, that would be a separate topic.)
