ccdb怎么调？终于找到答案了DBA大神成长轨迹（二）|SQLSERVER数据库性能优化实践总结

首先，性能优化工具

1.1日志分析

1.1.1 PerforMonitor

1、内存。

page life expectancy lazy writes/sec分支项目2，磁盘

avg . disk au eue length avg . disk sec/read write % disk timedisk reads writes/sec 3，CPU

处理器% privilege timesql compilations/sec SQL re-compilations/se C4、SQL Server

SQL server general : user connections SQL server buffer : average latch wait time(ms)SQL server buffer 3360 latch waits/sec ses

日志性能分析(PAL)工具读取性能监视器计数器日志(所有已知格式)，并使用复杂但已知的阈值(提供)进行分析。

该工具生成基于 HTML 的报告，以图形方式绘制重要的性能计数器并在超过阈值时发出警报。这些阈值最初基于 Microsoft 产品团队（包括 BizTalk Server）和 Microsoft 支持成员定义的阈值。该工具不能替代传统的性能分析，但它可以自动分析性能计数器日志，足以帮助您节省时间。PAL 工具：

• 分析性能计数器日志的阈值
• 对大型 Perfmon 日志很有帮助
• 通过分析阈值识别 BizTalk Server 和操作系统性能计数器瓶颈
• 可扩展以对任何性能计数器进行分析
• 可用于帮助编写您自己的计数器

1.2、DMV&DMO

• 等待类型分析
• 索引DMV分析

Microsoft SQL Server 在2005以后提供了一些工具来监控数据库，这对我们进行数据库方面的调优提供了极大的便利，下面我们就来了解一下DMO和他的孩子DMV[动态管理视图]。

首先我们先要看下DMO都能做些什么？

1. 执行相关的DMO（*）：提供与执行相关的统计信息。可以用于监控与缓存查询、执行计划、活动连接/会话和带有执行计划的当前运行的查询的相关统计信息。
2. 索引相关的DMO（*和*）：提供关于索引的统计信息。这些DMO可以用于监控和分析因为丢失索引、无效索引而导致的性能问题，也可以用来检查索引的使用情况。
3. 数据库相关DMO（*）：提供数据库相关统计信息。可以用于监控和分析数据库的性能问题，分析数据库相关文件的统计信息、会话统计信息和任务统计信息。
4. I/O相关DMO（*）：提供I/O操作的统计信息，用于监控和分析SQLServer的I/O性能问题。
5. OS相关DMO（*）：提供关于sqlos内部统计信息，用于监控和分析服务器配置问题。
6. 事务相关的DMO（*）：提供事务相关的统计信息，用于监控和分析长时间运行的事务的锁定、死锁问题。

1.3、DBCC

DBCC是SQL Server提供的一组控制台命令，功能很强大，掌握一些必要的语句，对操作数据库有不少帮助。

• DBCC SQLPERF(LOGSPACE)查看SQL日志文件大小命令：dbcc sqlperf(logspace)
• DBCC INPUTBUFFER（n）显示从客户机发送到服务器的最后一个语句
• DBCC LOGINFO()一个数据库可以有多个日志文件。但是，在许多情况下，单个日志文件就足够了。从逻辑上讲，一个日志文件被划分为多个虚拟日志文件。虚拟日志文件概念允许 SQL Server 管理对日志文件的各种操作，包括检查点、恢复的各个阶段，如分析、撤消和重做。在虚拟日志文件 (VLF) 的帮助下，日志文件的部分被标记为活动、可恢复、可重用和未使用。VLF 的数量可以达到数百（但不可取）。当日志文件增长和缩小时，它始终以 VLF 为单位。我们可以使用 SQL Server DBCC LOGINFO 语句观察虚拟日志文件。

1.4 Extended Events

DBCC SQLPERF("sys. dm os wait stats".CLEAR)

1.5 执行计划分析工具

SOL Sentry Plan Explorer

1.6 Trace分析工具

• Clear TraceClearTrace for Trace 将 SQL Server 2016、SQL Server 2016、SQL Server 2014、SQL Server 2012、SQL Server 2008、SQL Server 2005、SQL Server 2000 跟踪和 Profiler 文件导入 SQL Server 并显示摘要性能信息。（最新版本的 SMO 不再支持读取跟踪文件。这意味着无法支持从 SQL Server 2019 读取跟踪文件。）
• SQL ProfilerMicrosoft SQL Server Profiler 是 SQL 跟踪的图形用户界面，用于监视数据库引擎或 Analysis Services 的实例。 您可以捕获有关每个事件的数据并将其保存到文件或表中供以后分析。RPC：CompletedSQL：StmtCompleted

1.7 Idera Tools

• 实时监测工具IderaSOL CheckSQL Server 数据库对组织至关重要。确保本地和云中 SQL Server 数据库的可用性、运行状况和性能。通过使用免费的 SQL Server 监控工具在单个概览仪表板中实时显示关键性能指标，监控 SQL Server 的性能。监控 20 个关键绩效指标查看不同类型的吞吐量查看不同时间间隔的“心跳”统计信息使用简单的安装向导在几分钟内启动并运行无需代理连接到云端并在云端运行（临时）
• IderaSQL job managerSQL 作业管理器为管理整个企业的 SQL 代理作业提供了一种节省时间的解决方案。SQL 作业管理器简化了跨服务器 SQL Server 作业管理的过程，显着降低了作业失败率和在繁琐的作业管理活动上花费的时间。查看和管理跨多台服务器的 SQL Server 作业 轻松查找和修复作业争用 跨服务器拖放作业以调整工作负载 通过作业历史的“一目了然”视图识别失败或失控的作业 不需要代理

1.8 Redgate Tools

Redgate 的 SQL Toolbelt 包含用于 SQL Server 开发、部署、备份和监控的行业标准产品。它们共同提高了您的工作效率、团队的敏捷性和数据的安全性。每天都有数以千计的 SQL Server 专业人员依赖 SQL Toolbelt，因为它可靠、易于使用，并且可以插入他们已经用于应用程序开发的工具中。主要包括以下产品：

• SQL Compare
• SQL Data Compare
• SQL Backup
• SQL Source Control
• SQL Monitor Installer
• SQL Prompt 9.0
• SQL Dependency Tracker
• SQL Data Generator
• SQL Multi Script
• SQL Doc
• SQL Comparison SDK
• SQL Test
• SQL Search
• SSMS Integration Pack
• DLM Dashboard
• DLM Automation
• ReadyRoll

二、索引优化

• 重复索引
• 无效、低效索引
• 丢失索引
• 无聚集索引或主键

三、查询优化

• Top 100（SQLIProc）DurationCPUexecution countelapsed timelogicalwritesworker_timelogicalreads
• SQL执行计划分析
• 隐式转化
• 参数嗅探
• 条件列类型转化
• 统计分析
• 表读写率

四、数据库配置

• 内存大小配置
• 最大并行度配置
• Adhoc配置

五、TempDB优化

5.1 TempDB是什么?

• TempDB是一个系统数据库从SQL Server 2000开始就一直存在。

• 只有Simple恢复模式，自动截断模式。

• 存放局部变量/全局临时表/表变量/临时用法（如hash表等）。

• 机器重启或SQL Server服务重启后，都会按照Model库的配置重新创建。

• 如果临时对象是在会话或存储过程范围内产生的，在会话结束后就会自动回收，不能再查询或使用。

• 默认情况下都具有访问权限。

5.2 TempDB优化

1.配置文件的大小

默认配置：

初始大小8M

自动增长10%，不限制增长。

这个配置可以修改，要视生产环境的情况而修改。

建议如下配置

2.存放文件的地方

一般要将TempDB的文件单独放到一个磁盘中。

3.文件的个数

TempDB只有一个primary文件组，所有的数据文件都会存放到这个文件组中。常规建议是4个文件开始，并且需要进行监控，如果发现不够，可以再增加4个。依次类推。建议将文件个数控制在两位数以内。