John Liu Blog

Every drop counts

SQL Execution Plan

SQL Server generate execution plan based on available table column statistics. It’s important to have more accurate statistics for SQL to come up with a better execution plan. Memory grant is memory SQL Server thinks is required to hold the estimated data based on the statistics. If SQL Server estimated a large amount of data will be processed but actually only small amount is process, more memory grant will be required than actually required.

Posted by John Liu Sunday, November 21, 2021

Small table big performance impact

I recently came across an intersting case with a new stored procedure performance. The SP appeared to be run fast for one parameter value but very slow for another. My initial thought was parameter sniffing. However, a quick WITH RECOMPILE didn’t help. After turn on Live Query Statistics and look at the live data movements, I noticed that the process is doing a index scan on a small table with only 8 rows and then doing nested loop against a large table with out of wack estimates.

Posted by John Liu Tuesday, November 2, 2021