![]() As OPTION (RECOMPILE) recompile and regenerate new query plan on each execution, there is additional CPU overhead involved. Both different parameter value provided perform as expected with the optimal duration seen previously. Now SQL Server optimizer generates an optimal query plan for parameter value ProductNumer = 'FR%' or = 'SO-B909-L' respectively regardless the initial value used. OPTION (RECOMPILE) is a query hint to discard query plan previously generated and force the query optimizer to recompile a new query plan the next time the same query is executed. Not good.Īs we have noticed previously that these two types of parameter value passed yield very different query plans to obtain the most optimal performance, it seems like it is best not to use the query plan cached from the initial parameter value, but rather utilize an optimal plan depending on the provided parameter values. It went from about 1 second to around 20 seconds! One scenario becomes better in the expense of the other one. It looks good on = 'FR%' performance, but the performance on = 'SO-B909-L' suffers with this plan. Now, execute the stored procedure with either = 'SO-B909-L' or = 'FR%' produces the same query plan.ĮXEC sp_ProductOrderDetail = 'SO-B909-L', = 0, = 1000 Since the parameter value provided yield a different query plan as noticed previously, could the OPTIMIZE FOR UNKNOWN query hint help here? It produces a consistent query plan regardless of what the initial value was used (sniffed). OPTIMIZE FOR UNKNOWN hint asks SQL Server optimizer to use statistical data instead of the initial parameter values (sniffed value) when the query is compiled and optimized. One of the query hint is OPTIMIZE FOR UNKNOWN. Since this post is about query hint and plan guide, let's explore those method. Often, the stored procedure may be rewritten in a way to allow different query plan to be used according to parameter value. There could be multiple options to address this. One for specific value that usually yield small result set, but another for pattern with a wildcard that usually return much larger result set. The query was designed to allow parameter value that have very different scope. Before the execution, we clear the query plan cache (not the data in memory) for better comparison later on.ĭBCC FREEPROCCACHE -Remove the query plan cache (not the data)ĮXEC sp_ProductOrderDetail = 'FR%', = 0, = 1000 The performance comparison below is tested against warm cache where the data already loaded in memory.Įxecute first query to return result from the first 1000 records of all the frame (FR% as ProductNumber). WHERE p.ProductNumber LIKE BY s.SalesOrderNumber ![]() SELECT s.SalesOrderNumber, p.Name, d.OrderQty, d.LineTotal It has a LIKE operator in the where condition to allows a specific product number or wildcard value. ![]() This blog post discuss an example of using a query hint as well as plan guide.įirst, this is a contrived stored procedure to retrieve some sales and product information based on the user input of the product number. There are multiple ways to address this problem, like modifying the way the query is written, or using query hint or even plan guides under certain circumstances. Sometimes, due to certain scenarios and limitation, the query plan generated may be suboptimal. Parameter sniffing, indexes, statistics and other factors are taken into consideration for SQL Server query optimizer to produce an optimal query plan for the execution wit hin a timely manner. Query execution plan generated for a SQL query significantly affect the query performance. ![]()
0 Comments
Leave a Reply. |