Netherlands: Software

Introductie van Micorosoft SQL Server 2016

Issue link:

Contents of this Issue


Page 67 of 212

56 C H A P T E R 4 | Improved database engine Example 4-3: Understanding parameter sniffing CREATE PROCEDURE sniff_demo @PARAMETER1 INT AS UPDATE SNIFF_TABLE SET value=2 WHERE ID=@PARAMETER1; Now let's assume that you have a table such as the one shown here: ID Value 1 3 1 4 1 5 1 6 1 7 1 8 1 9 2 9 In this simple example of skewed values in a table, seven values have an ID of 1, and one value has an ID of 2. If you first run this procedure with a parameter value of 2, the execution plan generated by the database optimizer is likely to be less than optimal. Then, when you later execute the procedure with a parameter value of 1, SQL Server reuses the suboptimal plan. Because skewed data might force your procedures into plans that are less than optimal for many queries, you have the opportunity to force the plan that is best optimized for all executions of a given stored procedure. While this approach might not offer the best performance for all values of a procedure's parameter, forcing a plan can give you more consistent overall performance and better performance on average. SQL Server honors plan forcing during recompilation for in-memory, natively compiled procedures, but the same is not true for disk-based modules. You can also unforce a plan by using either the Query Store interface in SSMS or the sp_query_store_unforce_plan stored procedure. You might unforce a plan after your data changes significantly or when the underlying code changes enough to render the existing plan invalid. Managing the query store The query store is extremely helpful, but it does require some management. As we explained earlier in this chapter, the query store is not enabled by default. You must enable it on each user database individually. In addition, a best practice is to enable it on the model database. Note At the time of this writing, Query Store is not currently included in the Database Properties dialog box in SSMS for the model database. To add it, you must enable Query Store by using the following code: ALTER DATABASE MODEL SET QUERY_STORE=ON After enabling the query store, you might need to change the space allocated to the query store from the default of 100 MB per database. If you have a busy database, this allocation might not be large

Articles in this issue

Archives of this issue

view archives of Netherlands: Software - Introductie van Micorosoft SQL Server 2016