{"id":2397,"date":"2020-10-26T07:00:00","date_gmt":"2020-10-26T04:00:00","guid":{"rendered":"https:\/\/www.dataplatform.gr\/?p=2397"},"modified":"2024-07-01T17:51:46","modified_gmt":"2024-07-01T14:51:46","slug":"pos-kanoyme-force-ena-plano-ston-sql-server-kai-giat","status":"publish","type":"post","link":"https:\/\/www.dataplatform.gr\/en\/pos-kanoyme-force-ena-plano-ston-sql-server-kai-giat\/","title":{"rendered":"How to force a plan in SQL Server and why not"},"content":{"rendered":"

Sometimes a query may run with a different value depending on the value we have given to a parameter plan <\/strong>(execution plan<\/strong>) and have a different duration. This phenomenon is called parameter sniffing<\/strong>. This may be due to different number of records<\/strong> of the result, in outdated statistics<\/strong> or in not using a correct one non-clustered index<\/strong>. In the article we will see how we can do it force <\/strong>a shot on a procedure or a sql statement and why we usually shouldn't do it.<\/p>\n\n\n\n

What is parameter sniffing?<\/h2>\n\n\n\n

Parameter sniffing<\/strong> is called the SQL Server process that when we run a procedure or an sql statement it accepts an unknown parameter<\/strong>, reads it in order to create a shot <\/strong>according to the price we have set. Where is the problem; That depending on the parameter, the query can return 1 row or 100,000 and SQL Server will use the plan that was made with the first parameter it received. This will cause the second query to run with non-efficient (optimized) way<\/strong>.<\/p>\n\n\n\n

What is the solution<\/h2>\n\n\n\n

The solution could be to do recompile<\/strong> the plan every time but it would lead to high usage cpu <\/strong>along with high memory usage. This would happen because with each parameter another plan would be created which would take up space in cache<\/strong>. We could also use query hint<\/strong> with the command to optimize according to another parameter so that the plan of the specific parameter is always used.<\/p>\n\n\n\n

Sometimes the problem is due to statistics<\/strong>, as when statistics were taken on the table, the table had a different number of records than when the query was executed.<\/p>\n\n\n\n

Finally, the problem could be the lack of a correct one non-clustered index<\/strong>.<\/p>\n\n\n\n

Force it hard way aka Plan Guides!<\/h2>\n\n\n\n

If for some reason we can't mess with our code in the application or we don't have permission to add our own indexes and we want a solution right away there is a way. The use Plan Guides<\/strong>!<\/p>\n\n\n\n

Plan Guides allow us to do optimize <\/strong>queries without messing with their code. <\/p>\n\n\n\n

We have 3 categories of Plan Guides:<\/p>\n\n\n\n