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:-
DECLARE @city_name nvarchar(30);
DECLARE @postal_code nvarchar(15);
SET @city_name = 'Ascheim';
SET @postal_code = 86171;
SELECT * FROM Person.Address
WHERE City = @city_name AND PostalCode = @postal_code
OPTION ( OPTIMIZE FOR (@city_name = 'Seattle', @postal_code UNKNOWN) );
I hope this has been informative to all. Happy Coding
Please feel free to correct me by comments. Mention your approach if it differs from mine. If you like the article, do Like & Share.