0

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

  • 4
    Just to make sure: You're looking for an answer other than a difference of 10k rows and 7GB of RAM? – Erik Darling Aug 30 '18 at 18:47
  • See if this helps - dba.stackexchange.com/a/55254/8783 – Kin Shah Aug 30 '18 at 19:51
  • @sp_BlitzErik The memory is interesting, in that the inefficient plan is generated on the instance with the most RAM available and the fewest rows in the table. – Mark Freeman Aug 30 '18 at 20:04
  • @Kin: Luckily, Instance One is a test environment that is not being used at the moment. So I dropped MaxMem to 4915 to match Instance Two, and it still got the same inefficient plan as when it had more memory to play with. The instance has 5 GB of RAM in use. No matter how much is available to it, SQL Server apparently doesn't think it needs to use more RAM because the instance is mostly idle. – Mark Freeman Aug 30 '18 at 20:18
  • can you restart (if it is a test machine and not used) and then test out ? – Kin Shah Aug 30 '18 at 20:19
2

Why do two SQL Server instances give different plans for the same query

This is by design. You can get a different plan due to hardware differences, stats differences, data differences, configuration differences, and so on. Suppose you upgraded your server to have 1.6 TB of RAM instead of 16 GB. It would be disappointing if SQL Server used the exact same query plans with the new hardware as it did with 16 GB of RAM, right?

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?

Instance one either did not consider the nested loop plan during optimization or there's something different about the hardware/stats/etc that gives the nested loop plan a higher cost than the hash match plan. When I have questions like this I use query hints to force the plan that I think SQL Server should have picked and compare the overall costs and the costs of individual operators. That usually provides a lot of clues as to why the query optimizer picked one plan over the other.

Is there a way to force Instance One to use a different plan without changing the query?

Sure, you can use a plan guide if you refuse to change the query. With that said, when I see an elapsed time that's 50X-80X greater than CPU time I don't immediately think that there's a query plan problem. To me, that looks like a problem with hardware or with the load on the server. What do your wait stats look like while these queries are executing? For the query with the "good plan", does it really take 17 second to spill 383 pages to disk? Frankly, those queries are doing a tiny amount of work and it's crazy that they're taking 17-90 seconds to finish.

  • Thanks for jumping in. Giving them the same amount of RAM didn't help. Both instances are running in Azure VMs with very low IO throughput (due to low budget), so the executions are going to be slow. That's not the problem I'm trying to solve. I'm trying to determine the cause for the plan difference on identical "hardware" with very similar data. – Mark Freeman Aug 31 '18 at 12:56

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.