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.
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:-
|
1 2 3 4 5 6 7 8 |
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) ); GO |
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.
Related Posts
About The Author
Ajay Dwivedi
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.