How do cardinality estimates affect CPU and Reads in SQL Server?
First of all, it would be really great if you could provide an actual execution plan for the query you're working on tuning.  Otherwise, all of the answers you will get will be somewhat general, and may not perfectly apply to your situation.


  The question is why, in a production environment with heavy executions, would cardinality estimates affect CPU and reads so much?


The effect is indirect.  In general, better cardinality estimates will allow the query optimizer to make better choices about the execution plan - which can result in less reads and CPU.

Consider this simple query:

SELECT * 
FROM dbo.TableA a
ORDER BY a.[high];




The results should be ordered by high, and there is a nonclustered index on the high column.  Since SQL Server only expects to see 50 rows come out of the index scan, it decides to do read the data from the pre-sorted index, and do 50 key lookups to get the rest of the columns (to avoid sorting the data).

If SQL Server instead expects 10,050 rows to come out of the index scan, we get this plan instead:



It scans the clustered index, which is sorted by the number column.  It then has to sort that data by high in order to satisfy the ORDER BY part of the query.  This query has the following CPU and IO characteristics:


CPU time = 0 ms,  elapsed time = 326 ms.
Table 'TableA'. Scan count 1, logical reads 95


If I force the key lookup plan when there are 10,000 rows (in order to simulate a "bad cardinality estimate"), the query has these characteristics:


CPU time = 15 ms,  elapsed time = 305 ms.
Table 'TableA'. Scan count 1, logical reads 20768


As you can see, the bad cardinality estimate caused I/O and CPU to increase.

This is a really simplified example, but hopefully it helps to illustrate the general point.


  The average duration of each run was much better but 'only' 34% faster. I say 'only' because the CPU was 98% less usage and the reads were 95.5% fewer


If reads and CPU both dropped, but duration didn't drop as much as you expected, the cause is probably related to concurrency in the live environment.

David Browne makes this point well:


  Often this indicates a reduction in the degree of parallelism used in the query. A parallel scan across multiple cores generates a lot more CPU and Reads per second than a single-threaded plan. This is one of the problems with focusing on duration for query tuning.


If this is a parallel query, you may be getting a lower degree of parallelism (DOP) in the live environment than in your test.  Check the execution plan XML for the DegreeOfParallelism property (e.g., &lt;QueryPlan DegreeOfParallelism="8") to see if it's different between the two environments.

The stored procedure you've tuned might be blocked waiting on other queries to release locks.  If you are able to measure wait statistics, this problem would show up as LCK_* waits.  This is a typical cause of "low CPU, high duration" problems.

Or maybe it takes a while for the stored procedure to even begin running in some cases.  If other queries are running that need large memory grants, you could be encountering RESOURCE_SEMAPHORE waits.  If there are lots of parallel queries running at once, especially if they are running for a long time and waiting on locks, you might be encountering THREADPOOL waits.

Here are some resources that might help track down what's going on in production for this situation:


Use the SQL Server First Responder Kit


run sp_Blitz to get a general sense of what's going on on the server, and see if there are any of those really bad waits
run sp_BlitzFirst during representative times of the day (when issues are occurring) to get info on waits, what queries are running, etc





If you want to reproduce the query results I showed in order to play around with this yourself, here are the definitions:

CREATE TABLE [dbo].[TableA]
(
    [name] [nvarchar](35) NULL,
    [number] [int] NOT NULL,
    [type] [nchar](3) NOT NULL,
    [low] [int] NULL,
    [high] [int] NULL,
    [status] [int] NULL
);
CREATE CLUSTERED INDEX CX_number 
ON dbo.TableA ([number]);

CREATE NONCLUSTERED INDEX IX_high 
ON dbo.TableA ([high]);

INSERT INTO dbo.TableA
SELECT TOP (10000) v1.*
FROM master.dbo.spt_values v1
CROSS JOIN master.dbo.spt_values v2;

SET STATISTICS TIME, IO ON;

SELECT * 
FROM dbo.TableA a
ORDER BY a.[high];

SELECT * 
FROM dbo.TableA a WITH (INDEX (IX_high)) -- force the key lookup plan
ORDER BY a.[high];