I executed the following locally in SSMS for each of two instances of SQL Server 2012 SP4-OD (11.0.7469.6) that live in separate Azure VMs and get very different execution plans:
SET STATISTICS IO, TIME ON;
exec sp_executesql N'SELECT * FROM (SELECT DISTINCT TOP 5000 sysjobhistory.* FROM sysjobhistory WITH (NOLOCK)
LEFT OUTER JOIN
(
SELECT DISTINCT sysjobs.job_id
FROM sysjobs WITH (NOLOCK)
INNER JOIN sysjobsteps WITH (NOLOCK) ON sysjobsteps.job_id = sysjobs.job_id
WHERE (sysjobs.name NOT LIKE ''SQL Sentry% Queue Monitor'' AND sysjobs.name NOT LIKE ''SQL Sentry% Alert Trap'' AND sysjobsteps.command NOT LIKE ''%.AddEvent @EventType%'')
) NonSystemJobs
ON sysjobhistory.job_id = NonSystemJobs.job_id
WHERE (NonSystemJobs.job_id IS NOT NULL OR run_status = 0) AND instance_id > @instance_id ORDER BY instance_id DESC) RemoteHistory ORDER BY instance_id ASC',N'@instance_id int',@instance_id=0
In terms of the data, the two instances have the same jobs, job steps, and schedules, but one of the jobs is disabled on Instance One, so it has fewer rows in sysjobhistory. There is a significant difference in stats and execution plan for the query, with the instance with less data getting a worse plan:
Plan (Instance One, 27792 rows in sysjobhistory, https://www.brentozar.com/pastetheplan/?id=ryKHl2Bwm:
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 83444, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Workfile'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'sysjobhistory'. Scan count 1, logical reads 25022, physical reads 0, read-ahead reads 0, lob logical reads 1151, lob physical reads 0, lob read-ahead reads 0.
Table 'sysjobsteps'. Scan count 11, logical reads 26, physical reads 0, read-ahead reads 0, lob logical reads 2, lob physical reads 0, lob read-ahead reads 0.
Table 'sysjobs'. Scan count 1, logical reads 3, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
(1 row affected)
SQL Server Execution Times:
CPU time = 1235 ms, elapsed time = 90826 ms.
Plan (Instance Two, 38391 rows in sysjobhistory, https://www.brentozar.com/pastetheplan/?id=rklbW3Hw7
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 383, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'sysjobsteps'. Scan count 5001, logical reads 11624, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'sysjobs'. Scan count 0, logical reads 10002, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'sysjobhistory'. Scan count 1, logical reads 387, physical reads 0, read-ahead reads 0, lob logical reads 374, lob physical reads 293, lob read-ahead reads 0.
(1 row affected)
SQL Server Execution Times:
CPU time = 296 ms, elapsed time = 17878 ms.
They seem to both be identically configured in ways that the optimizer cares about: Standard A2m v2: 2 vcpus, 16 GB memory.
However, MaxMem is 12000 for Instance One (it is only using 2726 currently according to the Processes tab in SentryOne) and only 4915 for Instance Two (using 4991). The instance using more memory gets the better plan, but the other one has more memory available. The disk setup on Instance Two is better, so ignore the elapsed times and just look at the CPU times and Logical Reads counts.
Why does Instance One get the hash match instead of the more efficient nested loops in Instance Two, when both plans are using the same indexes? Is there a way to force Instance One to use a different plan without changing the query?
(I tried flushing the plan in Instance Two and running the query again and got the same plan as before. I don't have another instance with such an anemic configuration. However, I looked at a third instance, and it got the better plan but has more cores and RAM than these two, so I don't know that means anything.)
