Optimize for Value – TSQL Query Hint

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.

Optimize for TSQL Query Hint Optimize for TSQL Query Hint

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 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.

Leave a Reply