Today, I was working on an incident of slow running procedure. Procedure was running fine for most of the parameter values but, was getting stuck for few values. I could not find anything significant from wait stats. The session id was showing in runnable state. Also, as the procedure was using WITH RECOMPILE hint, it eliminated any suspicion of Parameter sniffing. So as usual, I checked it’s estimated execution plan. There I found something peculiar. Even though estimated number of output rows were only 13, query was trying to do 19 millions logical reads. It clearly indicated memory grant issue due to wrong estimation of rows.
So, in order to resolve the issue, I tried updating the stats for entire database, but it did not help. So, finally I had to use query hint OPTIMIZE FOR with parameter value that was generating good Estimated number of rows as part of estimated execution plan in order to resolve our issue. Below is the methodology for using this option taken out from MSDN:-
I am Microsoft Certified Professional having 10+ years of experience in SQL Server Querying, Database Design, and Administration. I am fond of Query Tuning and like to automate things using TSQL & PowerShell. I also have experience of implementing end-to-end Data Warehouse solution, and Data Migration using ETL tools SQL Server Integration Services (SSIS), Pentaho Business Analytics, and have designed Database Inventory through PowerShell, Python, and Django etc.