Sql-server – Cardinality Estimator Question after 2014 SP1 + CU4 Upgrade

What files do I need to install SQL 2014 SP1 CU6?

You would need

• SQL Server 2014 RTM setup

• SQL Server 2014 SP1 Setup. This can be downloaded from here

• You would need SQL Server 2014 Sp1 CU6 setup. This can be downloaded from here.

Note: Quite a few times you would find SP1 embedded in SQL Server 2014 installation files in that case you dont need to download Sp1 you just need to download CU6. When SP1 is embedded it wuld be mentioned in product software like SQL Server 2014 Sp1 Developer edition. If you have option go for this one.

Is there a slipstream SQL 2014 SP1 ISO file available for download or do I install SQL 2014 RTM then SQL 2014 SP1 and then SP1 CU6.Which RTM CU6 file should be used?

There is no Slipsteam file available for download its a way to install updates with SQL Server. Yes you can install SQL Server 2014, SP1 and Sp1 CU6 all together

Microsoft blogs already has information about Create a merged (slipstream) drop containing SQL Server 2008, Server Pack 1 and a Cumulative Update (CU) based on Server Pack 1.

I also suggest you read SQL Server 2008 Slipstreaming FAQ

Boris Hristov has also written good article see This link. This uses command line to install SQL Server and the updates.

Here is what we should do to slipstream SQL Server 2012 and 2014:

• Download SQL Server 2012 or SQL Server 2014 media

• Download all the needed SPs, CUs and Hotfixes that you want to “embed”

• Put all of the .exe files in one directory

• Launch the setup.exe from CMD by issuing the command:

  Setup.exe /Action=Install /UpdateEnabled=TRUE /UpdateSource=”path_to_the_directroy_where_the_hotfixes_are”

Place all the Sp and CU on one location after extraction and give the location in /UpdateSource

For example if location is C:\SQLServerFixes the command would be

Setup.exe /Action=Install /UpdateEnabled=TRUE /UpdateSource=”C:\SQLServerFixes” 

Is there a separate download required for SSMS 2014 etc.

No, while installing the update you can select the features you want to update.

EDIT:

Do I have to download a different file for developer edition Or download EE and provide developer license information.

Developer edition is same as enterprise in functionality but you can ONLY use developer edition for development. Starting from 31 March 2016 Dev edition is free to download for Visual studio DEV essential members. So if you are grab it. Their is no point in going for EE edition.

SQL 2014 RTM download version without CU as SP1 would cover it? What filename / size would this be - if this information is easily available.

You have to just download as I have said in starting

The question of why one cardinality estimation model produces closer results than the other in this case is actually not that interesting. The original CE estimates that not finding a matching row has a very small probability; the new CE calculates that it is almost certain. Both are 'correct', just based on different modelling assumptions. Fundamentally, multi-column semi joins are tricky to evaluate based on single-column statistical information.

It is much more interesting to think about what the query is trying to do, and how we can write it in a way that is more compatible with the statistical information available to SQL Server.

A key observation is that the query will return row(s) with one value per group. In the case of the original query, that is row(s) with the minimum HistoryId value for each Transactionid. In the repro, it is row(s) with the minimum c1 value for each different value of c2. The NOT EXISTS query is just one way of expressing that requirement.

SQL Server has good statistical information about distinct values (density) so all we need to do is write the query in a way that makes it clear we want one value per group. There are many ways to do this, for example (using your repro):

SELECT * 
FROM dbo.nat AS N
WHERE N.c1 =
(
    SELECT MIN(N2.c1) 
    FROM dbo.nat AS N2
    WHERE N2.c2 = N.c2
);

or, equivalently:

SELECT N.* 
FROM dbo.nat AS N
JOIN
(
    SELECT 
        N.c2,
        MIN(N.c1) AS c1
    FROM dbo.nat AS N
    GROUP BY 
        N.c2
) AS J
    ON J.c2 = N.c2
    AND J.c1 = N.c1;

This produces an exactly correct estimate of 9999 rows in 2008 R2, 2012, and 2014 (both CE models):

With a natural index (which would probably be unique as well):

CREATE INDEX i ON dbo.nat (c2, c1);

The plan is even simpler:

You may not always be able to get this very simple plan form, depending on indexes, and other factors. The point I am making is that using basic grouping and joining operations often gets better results from the optimizer (and its cardinality estimation component) than more complex alternatives.

Final notes to clear some misconceptions in the question: the 'new CE' was introduced in 2014. TF 4199 enables plan-affecting optimizer fixes. TF 9481 specifies the original ('legacy') CE, and is only effective on 2014 and later versions.