Trying to understand a concept, not sure where to look for answers. I have been researching cardinality estimates and their affects on memory. In the process of optimizing a query to get better cardinality estimates for a stored procedure I discovered some interesting data.
There was a stored procedure that the team lead of our DBAs asked that I optimize. This particular proc was causing significant issues with our server the DBA was not sure why, he had attempted to optimize it but without any success. The issue was the simplicity of the procedure. It was JOINing 4 tables and returning about 20 columns.
He provided me a spreadsheet that had the data of over 13,000 executions from a particular day. This data included the parameters for each execution, the duration, CPU, and number of reads. I optimized it and it went live 3 days ago. Yesterday I asked for similar data for the day after it went into production so I could see if the optimization was working. The real issue was the original proc ran really great in test, even using the parameters of the worst runs from the spreadsheet, it only had the issue when it was in production and running so many executions in a day along side all the other procedures that were running. This procedure and the mystery (for us) was why I starting looking into the cardinality as all the JOINs were giving me some huge discrepancies on estimated and actual rows. This lead to me asking this question to try understand more about how statistics work and yesterday I asked this question about memory grant.
My question today stems from the data on the executions from before and after optimization. I have tested the old procedure on the test server and if 3 of the 4 tables' indices had bigger sample sizes then the procedure would not have needed more optimization. I spent a lot of time manipulating JOINs with additional tables and creating several temp tables and indices on temp tables in order to get better cardinality estimates.
The new optimized procedure ran great. It ran over 54k times in an 11 hour time span for the day in question. 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. This is why I put in the previous story. Like I mentioned, the original proc ran fine in test for a one-off execution and when compared to the new procedure I was getting 15-20% improvement on CPU and reads for some of the better executions, several executions had the same CPU and a few had worse CPU, reads or duration.
The question is why, in a production environment with heavy executions, would cardinality estimates affect CPU and reads so much? I am looking for technical reasons and this is going to be included, if there is a correlation, in a presentation I am doing next week to try and promote better sample sizes on our indices. If there is no correlation I can accept that as an answer, but it would be nice to still know what could possible cause such a change when all that was really improved was the cardinality.