Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I want to understand why there would be such a huge difference in execution of the same query on UAT (runs in 3 sec) vs PROD (run in 23 secs).

Both UAT and PROD are having exactly data and indexes.

QUERY:

set statistics io on;
set statistics time on;

SELECT CONF_NO,
       'DE',
       'Duplicate Email Address ''' + RTRIM(EMAIL_ADDRESS) + ''' in Maintenance',
       CONF_TARGET_NO
FROM   CONF_TARGET ct
WHERE  CONF_NO = 161
       AND LEFT(INTERNET_USER_ID, 6) != 'ICONF-'
       AND ( ( REGISTRATION_TYPE = 'I'
               AND (SELECT COUNT(1)
                    FROM   PORTFOLIO
                    WHERE  EMAIL_ADDRESS = ct.EMAIL_ADDRESS
                           AND DEACTIVATED_YN = 'N') > 1 )
              OR ( REGISTRATION_TYPE = 'K'
                   AND (SELECT COUNT(1)
                        FROM   CAPITAL_MARKET
                        WHERE  EMAIL_ADDRESS = ct.EMAIL_ADDRESS
                               AND DEACTIVATED_YN = 'N') > 1 ) ) 

ON UAT :

SQL Server parse and compile time: 
   CPU time = 0 ms, elapsed time = 0 ms.

 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 0 ms.
SQL Server parse and compile time: 
   CPU time = 11 ms, elapsed time = 11 ms.

 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 0 ms.

 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 0 ms.

(3 row(s) affected)
Table 'Worktable'. Scan count 256, logical reads 1304616, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'PORTFOLIO'. Scan count 1, logical reads 84761, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'CAPITAL_MARKET'. Scan count 256, logical reads 9472, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'CONF_TARGET'. Scan count 1, logical reads 100, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

(1 row(s) affected)

 SQL Server Execution Times:
   CPU time = 2418 ms,  elapsed time = 2442 ms.
SQL Server parse and compile time: 
   CPU time = 0 ms, elapsed time = 0 ms.

 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 0 ms.

enter image description here

On PROD :

SQL Server parse and compile time: 
   CPU time = 0 ms, elapsed time = 0 ms.

 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 0 ms.
SQL Server parse and compile time: 
   CPU time = 0 ms, elapsed time = 0 ms.

 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 0 ms.

 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 0 ms.

(3 row(s) affected)
Table 'PORTFOLIO'. Scan count 256, logical reads 21698816, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'CAPITAL_MARKET'. Scan count 256, logical reads 9472, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'CONF_TARGET'. Scan count 1, logical reads 100, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

(1 row(s) affected)

 SQL Server Execution Times:
   CPU time = 23937 ms,  elapsed time = 23935 ms.
SQL Server parse and compile time: 
   CPU time = 0 ms, elapsed time = 0 ms.

 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 0 ms.

enter image description here

Note that on PROD the query suggests a missing index and that is beneficial as I have tested, but that is not the point of discussion.

I just want to understand that : ON UAT - why does sql server create a worker table and on PROD it does not ? It creates a table spool on UAT and not on PROD. Also, why are the execution times so different on UAT vs PROD ?

Note :

I am running sql server 2008 R2 RTM on both servers (pretty soon going to patch with latest SP).

UAT : Max memory 8GB. MaxDop, processor affinity and max worker threads is 0.

Logical to Physical Processor Map:
*-------  Physical Processor 0
-*------  Physical Processor 1
--*-----  Physical Processor 2
---*----  Physical Processor 3
----*---  Physical Processor 4
-----*--  Physical Processor 5
------*-  Physical Processor 6
-------*  Physical Processor 7

Logical Processor to Socket Map:
****----  Socket 0
----****  Socket 1

Logical Processor to NUMA Node Map:
********  NUMA Node 0

PROD : max memory 60GB. MaxDop, processor affinity and max worker threads is 0.

Logical to Physical Processor Map:
**--------------  Physical Processor 0 (Hyperthreaded)
--**------------  Physical Processor 1 (Hyperthreaded)
----**----------  Physical Processor 2 (Hyperthreaded)
------**--------  Physical Processor 3 (Hyperthreaded)
--------**------  Physical Processor 4 (Hyperthreaded)
----------**----  Physical Processor 5 (Hyperthreaded)
------------**--  Physical Processor 6 (Hyperthreaded)
--------------**  Physical Processor 7 (Hyperthreaded)

Logical Processor to Socket Map:
********--------  Socket 0
--------********  Socket 1

Logical Processor to NUMA Node Map:
********--------  NUMA Node 0
--------********  NUMA Node 1

UPDATE :

UAT Execution Plan XML :

http://pastebin.com/z0PWvw8m

PROD Execution Plan XML :

http://pastebin.com/GWTY16YY

UAT Execution Plan XML - with Plan generated fro PROD:

http://pastebin.com/74u3Ntr0

Server Configuration :

PROD: PowerEdge R720xd - Intel(R) Xeon(R) CPU E5-2637 v2 @ 3.50GHz.

UAT: PowerEdge 2950 - Intel(R) Xeon(R) CPU X5460 @ 3.16GHz

I have posted at answers.sqlperformance.com


UPDATE :

Thanks to @swasheck for suggestion

Changing the max memory on PROD from 60GB to 7680 MB, I am able to generate the same plan in PROD. The query completes in same time as UAT.

Now I need to understand - WHY ? Also, by this, I wont be able to justify this monster server to replace the old server !

share|improve this question
    
So PROD suggests an index that doesn't exist in PROD, and the other system doesn't suggest that index, even though it doesn't also exist there? You say that data and indexes are identical, is this really true? (Not just number of rows and names of indexes, but everything about the data such as cardinality/skew etc.)? How about stats, compatibility level, trace flags, sp_configure options, amount of memory, resource governor, etc.? –  Aaron Bertrand Nov 21 '13 at 17:21
    
@AaronBertrand I just checked and everything is same. What happended is that PROD is a brand new server and we moved this database to this new server as the OLD PROD server was running at its max capacity. But on the new server, the performance has not improved. The suggested index improves performance bringing down the execution time to 3 secs on PROD, but that is a separate topic. I have just recently done a restore on UAT and ran the same query on UAT and it takes around 10 sec. But the same on PROD takes 23 secs. –  Kin Nov 21 '13 at 17:25
2  
You need to update stats and recompile plans so that they are determined based on the current data on the current hardware. Especially if the new server has vastly different internal hardware, external I/O, or a different version (even build #) of SQL Server, in addition to some of the other variances I mentioned above. SQL Server just doesn't pick random plans for fun; something is different. Impossible for us to guess what the exact difference is if you keep saying "everything is the same." –  Aaron Bertrand Nov 21 '13 at 17:38
1  
I would guess that stats on portfolio needs to be updated in prod. The time difference is because of the spool you only do one scan of the portfolio table in UAT instead of 256 as it does in prod. –  Mikael Eriksson Nov 21 '13 at 17:47
2  
@Kin - How about with the USE PLAN hint? The costing for scans under a semi join / anti semi join is discussed in this answer BTW –  Martin Smith Nov 22 '13 at 14:56

3 Answers 3

up vote 18 down vote accepted
+50

The potential size of the buffer pool affects plan selection by the query optimizer in a number of ways. As far as I know, hyper-threading does not affect plan choice (though the number of potentially available schedulers certainly can).

Workspace Memory

For plans that contain memory-consuming iterators like sorts and hashes, the size of the buffer pool (among other things) determines the maximum amount of memory grant that might be available to the query at runtime.

In SQL Server 2012 (all versions) this number is reported on the root node of a query plan, in the Optimizer Hardware Dependencies section, shown as Estimated Available Memory Grant. Versions prior to 2012 do not report this number in show plan.

The estimated available memory grant is an input to the cost model used by the query optimizer. As a result, a plan alternative that requires a large sorting or hashing operation is more likely to be chosen on a machine with a large buffer pool setting than on a machine with a lower setting. For installations with a very large amount of memory, the cost model can go too far with this sort of thinking - choosing plans with very large sorts or hashes where an alternative strategy would be preferable (KB2413549 - Using large amounts of memory can result in an inefficient plan in SQL Server - TF2335).

Workspace memory grant is not a factor in your case, but it is something worth knowing about.

Data Access

The potential size of the buffer pool also affects the optimizer's cost model for data access. One of the assumptions made in the model is that every query starts with a cold cache - so the first access to a page is assumed to incur a physical I/O. The model does attempt to account for the chance that repeated access will come from cache, a factor that depends on the potential size of the buffer pool among other things.

The Clustered Index Scans in the query plans shown in the question are one example of repeated access; the scans are rewound (repeated, without a change of correlated parameter) for each iteration of the nested loops semi join. The outer input to the semi join estimates 28.7874 rows, and the query plan properties for these scans shows estimated rewinds at 27.7874 as a result.

Again, in SQL Server 2012 only, the root iterator of the plan shows the number of Estimated Pages Cached in the Optimizer Hardware Dependencies section. This number reports one of the inputs to the costing algorithm that looks to account for the chance of repeated page access coming from cache.

The effect is that an installation with a higher configured maximum buffer pool size will tend to reduce the cost of scans (or seeks) that read the same pages more than once more than an installation with a smaller maximum buffer pool size.

In simple plans, the cost reduction on a rewound scan can be seen by comparing (estimated number of executions) * (estimated CPU + estimated I/O) with the estimated operator cost, which will be lower. The calculation is more complex in the example plans due to the effect of the semi join and union.

Nevertheless, the plans in the question appear to show a case where the choice between repeating the scans and creating a temporary index in quite finely balanced. On the machine with a larger buffer pool, repeating the scans is costed slightly lower than creating the index. On the machine with a smaller buffer pool, the scan cost is reduced by a smaller amount, meaning the index spool plan looks slightly cheaper to the optimizer.

Plan Choices

The optimizer's cost model makes a number of assumptions, and contains a great number of detailed calculations. It is not always (or even usually) possible to follow all the details because not all the numbers we would need are exposed, and the algorithms can change between releases. In particular, the scaling formula applied to take account of the chance of encountering a cached page is not well known.

More to the point in this particular case, the optimizer's plan choices are based on incorrect numbers anyway. The estimated number of rows from the Clustered Index Seek is 28.7874, whereas 256 rows are encountered at runtime - almost an order of magnitude out. We cannot directly see the information the optimizer has about the expected distribution of values within those 28.7874 rows, but it is very likely to be horribly wrong as well.

When estimates are this wrong, plan selection and runtime performance are essentially no better than chance. The plan with the index spool happens to perform better than repeating the scan, but it is quite wrong to think that increasing the size of the buffer pool was the cause of the anomaly.

Where the optimizer has correct information, the chances are much better that it will produce a decent execution plan. An instance with more memory will generally perform better on a workload than another instance with less memory, but there are no guarantees, especially when plan selection is based on incorrect data.

Both instances suggested a missing index in their own way. One reported an explicit missing index, and the other used an index spool with the same characteristics. If the index provides good performance and plan stability, that might be enough. My inclination would be to rewrite the query as well, but that's probably another story.

share|improve this answer
    
Highly appreciate your detailed explanation. I have opened a case with MS as well. Will also post what MS comes up with, so that the community can benefit from it. Your answer makes a lot of sense. Now I am in a dilemma that moving other databases on this server might have same effect - and the business thinks that since it is a brand new server with much much higher specs, the performance should be at least good than UAT - the business don't understand the internals, they just want the result. –  Kin Nov 26 '13 at 23:48
    
@Kin - how did you find this anomaly? Are you seeing this behavior with a lot of queries, or is this one a special case that somehow became very visible? –  Max Vernon Nov 27 '13 at 0:07
    
Paul - do you have any opinion on this query using far memory by virtue of the MAXDOP = 0 setting? Could far memory access be causing a visible slowdown, or am I totally barking up the wrong tree with that line of thought? –  Max Vernon Nov 27 '13 at 0:09
    
@MaxVernon If you mean NUMA foreign memory, then yes, wrong tree. –  Paul White Nov 27 '13 at 0:49
    
@Kin There is also a chance that the different plans were caused by slight differences in statistics on any of the three tables involved. My money is on the reason I gave in my answer, but I'd need access to your systems to be certain. –  Paul White Nov 27 '13 at 0:52

Paul White has explained in an excellent lucid manner the reason behind - sql server behaviour when running on servers with more memory.

Also, a huge thanks to @swasheck for first spotting the issue.

Opened a case with microsoft and below is what was suggested.

The problem is resolved by using trace flag T2335 as a startup parameter.

The KB2413549 - Using large amounts of memory can result in an inefficient plan in SQL Server describes it in more details.

This trace flag will cause SQL Server to generate a plan that is more conservative in terms of memory consumption when executing the query. It does not limit how much memory SQL Server can use. The memory configured for SQL Server will still be used by data cache, query execution and other consumers. Please ensure that you thoroughly test this option, before rolling it into a production environment.

share|improve this answer

Max memory settings and hyperthreading can both affect the plan choice.

Additionally, I notice your "set" options are different in each environment:

StatementSetOptions on UAT:

ANSI_NULLS="true" 
ANSI_PADDING="true" 
ANSI_WARNINGS="true" 
ARITHABORT="true" 
CONCAT_NULL_YIELDS_NULL="true" 
NUMERIC_ROUNDABORT="false" 
QUOTED_IDENTIFIER="true" 

StatementSetOptions on Prod:

ANSI_NULLS="true" 
ANSI_PADDING="true" 
ANSI_WARNINGS="true" 
ARITHABORT="false" 
CONCAT_NULL_YIELDS_NULL="true"
NUMERIC_ROUNDABORT="false"
QUOTED_IDENTIFIER="true" 

SQL can generate different plans based on the SET options. This frequently happens if you are capturing the plan from different SSMS sessions, or from different executions from the app.

Make sure the developers are using consistent connection strings.

share|improve this answer
2  
You are correct in stating that Max Memory and Hyperthreading can affect plan cache, but I want to know in detail as to what and why this happened. Appreciate your answer. –  Kin Nov 25 '13 at 20:08
2  
As Amanda said, if SET options differ in ARITHABORT, may be you should look at dba.stackexchange.com/questions/9840/… –  Alain R. Nov 30 '13 at 12:14

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.