Netherlands: Software

Introductie van Micorosoft SQL Server 2016

Issue link:

Contents of this Issue


Page 66 of 212

55 C H A P T E R 4 | Improved database engine Figure 4-6: Configuring a Query Store dashboard. Alternatively, you can query a DMV directly, which is a powerful approach for quickly isolating poorly performing queries. Example 4-2 shows a T-SQL statement to return the poorest performing queries over the last hour. Example 4-2: Finding the poorest performing queries over the last hour SELECT TOP 10 rs.avg_duration, qt.query_sql_text, q.query_id, qt.query_text_id, p.plan_id, GETUTCDATE() AS CurrentUTCTime, rs.last_execution_time FROM sys.query_store_query_text AS qt JOIN sys.query_store_query AS q ON qt.query_text_id = q.query_text_id JOIN sys.query_store_plan AS p ON q.query_id = p.query_id JOIN sys.query_store_runtime_stats AS rs ON p.plan_id = rs.plan_id WHERE rs.last_execution_time > DATEADD(hour, -1, GETUTCDATE()) ORDER BY rs.avg_duration DESC; Using Force Plan The generation of an execution plan is CPU intensive. To reduce the workload on the database engine, SQL Server generates a plan once and stores it in a cache. Generally, caching the plan is good for database performance, but it can also lead to a situation known as parameter sniffing. The query optimizer uses parameter sniffing to minimize the number of recompiled queries. This situation occurs when a stored procedure is initially run with a given parameter against a table having a skewed number of values. You can use the query store's Force Plan option to address this problem. To better understand parameter sniffing, consider an example in which you create a stored procedure like the one shown in Example 4-3.

Articles in this issue

Archives of this issue

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