Sql-server – Row estimates always too low

The last two left joins are not used and should be deleted

Have index on all ON conditions

I would try materialize in a #temp with a PK

SELECT IDPolozka 
FROM SA_NapZisk_Popis2Zmeny 
WHERE Zpracovano <> 1 OR Zpracovano IS NULL 

-

SELECT TabPohybyZbozi.ID
      ,TabPohybyZbozi.NazevSozNa2
      ,TabZakazka.Nazev
      ,TabDokladyZbozi.DatPorizeni
      ,TabPohybyZbozi.IDDoklad
FROM TabPohybyZbozi 
INNER JOIN TabDokladyZbozi 
        ON TabDokladyZbozi.ID          = TabPohybyZbozi.IDDoklad
       AND TabDokladyZbozi.DruhPohybuZbo IN (13,14,18,19)
       AND TabDokladyZbozi.DatPorizeni >= DATEADD(DAY,-365,GETDATE()) 
       AND TabDokladyZbozi.DatPorizeni <= GETDATE()
LEFT  JOIN TabZakazka 
        ON TabZakazka.CisloZakazky     = TabPohybyZbozi.CisloZakazky
LEFT  JOIN TabPohybyZbozi_Ext 
        ON TabPohybyZbozi_Ext.ID       = TabPohybyZbozi.ID 
       AND (    TabPohybyZbozi.NazevSozNa2 <> TabZakazka.Nazev 
            OR (TabPohybyZbozi.NazevSozNa2 = N'' AND TabZakazka.Nazev IS NULL))
       AND TabPohybyZbozi.NazevSozNa2 <> N'' 
       AND TabPohybyZbozi.NazevSozNa2 <> N'0'
LEFT  JOIN TabDruhDokZbo 
        ON TabDruhDokZbo.DruhPohybuZbo = TabDokladyZbozi.DruhPohybuZbo 
       AND TabDruhDokZbo.RadaDokladu   = TabDokladyZbozi.RadaDokladu
LEFT  JOIN TabDruhDokZbo_Ext 
        ON TabDruhDokZbo.ID            = TabDruhDokZbo_Ext.ID
       AND (   TabDruhDokZbo_EXT._SA_ZalohovaFa = 0 
            OR TabDruhDokZbo_EXT._SA_ZalohovaFa IS NULL)
WHERE NOT EXISTS (SELECT * 
                  FROM SA_NapZisk_Popis2Zmeny 
                  WHERE (Zpracovano <> 1 OR Zpracovano IS NULL) 
                  AND IDPolozka = TabPohybyZbozi.ID
                 )

It's true that selecting more columns implies that SQL Server may need to work harder to get the requested results of the query. If the query optimizer was able to come up with the perfect query plan for both queries then it would be reasonable to expect the SELECT * query to run longer than the query that selects all columns from all tables. You have observed the opposite for your pair of queries. You need to be careful when comparing costs, but the slow query has a total estimated cost of 1090.08 optimizer units and the fast query has a total estimated cost of 6823.11 optimizer units. In this case, it could be said that the optimizer does a poor job with estimating total query costs. It did pick a different plan for your SELECT * query and it expected that plan to be more expensive, but that wasn't the case here. That type of mismatch can happen for many reasons and one of the most common causes is cardinality estimate problems. Operator costs are largely determined by cardinality estimates. If a cardinality estimate at a key point in a plan is inaccurate then the total cost of the plan may not reflect reality. This is a gross oversimplification but I hope that it will be helpful for understanding what's going on here.

Let's start by discussing why a SELECT * query might be more expensive than selecting a single column. The SELECT * query may turn some covering indexes into noncovering indexes, which might mean that the optimizer needs to do addition work to get all of the columns it needs or it may need to read from a larger index. SELECT * may also result in larger intermediate result sets which need to be processed during query execution. You can see this in action by looking at the estimated row sizes in both queries. In the fast query your row sizes range from 664 bytes to 3019 bytes. In the slow query your row sizes range from 19 to 36 bytes. Blocking operators such as sorts or hash builds will have higher costs for data with a larger row size because SQL Server knows it's more expensive to sort larger amounts of data or to turn it into a hash table.

Looking at the fast query, the optimizer estimates that it needs to do 2.4 million index seeks on Database1.Schema1.Object5.Index3. That is where most of the plan cost comes from. Yet the actual plan reveals that only 1332 index seeks were done on that operator. If you compare the actual to the estimated rows for the outer parts of those loop joins you'll see large differences. The optimizer thinks that many more index seeks will be needed to find the first 1000 rows needed for the query's results. That's why the query has a relatively high cost plan but finishes so quickly: the operator that was predicted to be the most expensive did less than 0.1% of its expected work.

Looking at the slow query, you get a plan with mostly hash joins (I believe the loop join is there just to deal with the local variable). Cardinality estimates definitely aren't perfect, but the only real estimate problem is right at the end with the sort. I suspect most of the time is spent on the scans of the tables with hundreds of millions of rows.

You may find it helpful to add query hints to both versions of the query to force the query plan associated with the other version. Query hints can be a good tool to figure out why the optimizer made some of its choices. If you add OPTION (RECOMPILE, HASH JOIN) to the SELECT * query I expect you'll see a similar query plan to the hash join query. I also expect that query costs will be much higher for the hash join plan because your row sizes are much bigger. So that could be why the hash join query wasn't chosen for the SELECT * query. If you add OPTION (LOOP JOIN) to the query that selects just one column I expect you'll see a query plan similar to the one for the SELECT * query. In this case, reducing the row size shouldn't have much of an impact on the overall query cost. You might skip the key lookups but that's a small percentage of the estimated cost.

In summary, I expect that the larger row sizes needed to satisfy the SELECT * query push the optimizer towards a loop join plan instead of a hash join plan. The loop join plan is costed higher than it should be due to cardinality estimate issues. Reducing the row sizes by selecting just one column greatly reduces the cost of a hash join plan but probably won't have much of an effect on the cost for a loop join plan, so you end up with the less efficient hash join plan. It's hard to say more than this for an anonymized plan.