4
Stabilizing Performance
The next report to look at for baselines is the Regressed Queries report, which will show
you queries that have regressed in performance. For each of these, you should look at
why the performance has degraded. This report will show how each query plan for a
specific query has performed over the last month by default.
From the Regressed Query report or Top Consuming Queries report, you can easily
see what plans should be forced based on how they are performing. Forcing a plan is
when you take an execution plan and based on the performance you see in the report
decide that you want that plan to be the one the SQL Server engine uses going forward.
Before Query Store, you had to use plan guides and those are discussed later in this
chapter. You would force a plan when you notice plan regression. Plan regression is
when the SQL Server Query Plan Optimizer compiles a new execution plan for a query
that was previously running and theperformance isworse than the previous plan. When
a plan is forced manually, you should be aware that no other execution plan will be used
for that query going forward. Also, if there is an index change or table schema change,
the forced plan could start failing causing the SQL Server Query Optimizer to take extra
steps to get an execution plan each time the query is executed. The reports provide
a button by which you can force the plans, but you can also for change management
purposes use T-SQL to force plans with the stored procedure sys.sp_force_plan.
The High Variation report shows you which queries are inconsistent in performance
which gives you a way to tell which queries are performing differently at times and may
need to be tuned to perform consistently.
SSMS has a Forced Plan report where you track and verify that any plans that were
forced are performing as expected.
Tip Older query plans will be deleted from Query Store based on your settings
for retaining query plans, so you may want to keep separate documentation of the
performance you expect to see going forward for any forced plans.
Reports will be discussed in greater detail in Chapter 4.
CHAPTER 1 WHAT IS QUERY STORE?
剩余223页未读，继续阅读
precker
- 粉丝: 1
- 资源: 1
我的内容管理
展开
最新资源
- zlib-1.2.12压缩包解析与技术要点
- 微信小程序滑动选项卡源码模版发布
- Unity虚拟人物唇同步插件Oculus Lipsync介绍
- Nginx 1.18.0版本WinSW自动安装与管理指南
- Java Swing和JDBC实现的ATM系统源码解析
- 掌握Spark Streaming与Maven集成的分布式大数据处理
- 深入学习推荐系统：教程、案例与项目实践
- Web开发者必备的取色工具软件介绍
- C语言实现李春葆数据结构实验程序
- 超市管理系统开发：asp+SQL Server 2005实战
- Redis伪集群搭建教程与实践
- 掌握网络活动细节：Wireshark v3.6.3网络嗅探工具详解
- 全面掌握美赛：建模、分析与编程实现教程
- Java图书馆系统完整项目源码及SQL文件解析
- PCtoLCD2002软件：高效图片和字符取模转换
- Java开发的体育赛事在线购票系统源码分析
资源上传下载、课程学习等过程中有任何疑问或建议，欢迎提出宝贵意见哦~我们会及时处理！
点击此处反馈
