如何调整Microsoft SQL Server的性能

本文概述

• #1发现罪魁祸首
• #2查找有问题的查询
• #3微调查询
• #4执行计划重用
• #5删除不必要的索引
• #6 SQL Server安装和数据库设置
• #7不要超载SQL Server
• #8事务日志, tempdb和内存
• 总结

为了保留其用户, 任何应用程序或网站都必须快速运行。对于关键任务环境, 获取信息的几毫秒延迟可能会带来大问题。随着数据库规模的日益增长, 我们需要尽快获取数据, 并尽快将数据写回到数据库中。为了确保所有操作都能顺利执行, 我们必须调整数据库服务器的性能。

在本文中, 我将介绍在市场上最顶级的数据库服务器之一上进行基本性能调优的分步过程：Microsoft SQL Server(简称SQL Server)。

#1发现罪魁祸首

与任何其他软件一样, 我们需要了解SQL Server是一个复杂的计算机程序。如果我们有问题, 我们需要找出为什么它没有按预期运行。

在SQL Server中, 我们需要尽可能快且准确地提取和推送数据。如果存在问题, 则有两个基本原因以及要检查的前两件事：

• 硬件和安装设置, 由于SQL Server需要是特定的, 因此可能需要更正
• 如果我们提供了正确的T-SQL代码以供SQL Server实施

即使SQL Server是专有软件, Microsoft也提供了许多理解和有效使用它的方法。

如果硬件正常, 并且安装已正确完成, 但是SQL Server仍运行缓慢, 则首先我们需要确定是否存在与软件相关的错误。要检查发生了什么, 我们需要观察不同线程的执行情况。这是通过计算不同线程的等待统计信息来实现的。 SQL Server为每个用户请求使用线程, 而该线程不过是我们复杂程序SQL Server中的另一个程序。重要的是要注意, 该线程不是安装SQL Server的操作系统线程。它与SQLOS线程有关, 后者是SQL Server的伪操作系统。

可以使用sys.dm_os_wait_stats动态管理视图(DMV)计算等待统计信息, 该视图提供有关其当前状态的其他信息。在线上有很多脚本可以查询此视图, 但是我最喜欢Paul Randal的脚本, 因为它易于理解, 并且具有观察等待统计信息的所有重要参数：

WITH [Waits] AS
(SELECT
[wait_type], [wait_time_ms] / 1000.0 AS [WaitS], ([wait_time_ms] - [signal_wait_time_ms]) / 1000.0 AS [ResourceS], [signal_wait_time_ms] / 1000.0 AS [SignalS], [waiting_tasks_count] AS [WaitCount], 100.0 * [wait_time_ms] / SUM ([wait_time_ms]) OVER() AS [Percentage], ROW_NUMBER() OVER(ORDER BY [wait_time_ms] DESC) AS [RowNum]
FROM sys.dm_os_wait_stats
WHERE [wait_type] NOT IN (
N'BROKER_EVENTHANDLER', N'BROKER_RECEIVE_WAITFOR', N'BROKER_TASK_STOP', N'BROKER_TO_FLUSH', N'BROKER_TRANSMITTER', N'CHECKPOINT_QUEUE', N'CHKPT', N'CLR_AUTO_EVENT', N'CLR_MANUAL_EVENT', N'CLR_SEMAPHORE', N'DBMIRROR_DBM_EVENT', N'DBMIRROR_EVENTS_QUEUE', N'DBMIRROR_WORKER_QUEUE', N'DBMIRRORING_CMD', N'DIRTY_PAGE_POLL', N'DISPATCHER_QUEUE_SEMAPHORE', N'EXECSYNC', N'FSAGENT', N'FT_IFTS_SCHEDULER_IDLE_WAIT', N'FT_IFTSHC_MUTEX', N'HADR_CLUSAPI_CALL', N'HADR_FILESTREAM_IOMGR_IOCOMPLETION', N'HADR_LOGCAPTURE_WAIT', N'HADR_NOTIFICATION_DEQUEUE', N'HADR_TIMER_TASK', N'HADR_WORK_QUEUE', N'KSOURCE_WAKEUP', N'LAZYWRITER_SLEEP', N'LOGMGR_QUEUE', N'ONDEMAND_TASK_QUEUE', N'PWAIT_ALL_COMPONENTS_INITIALIZED', N'QDS_PERSIST_TASK_MAIN_LOOP_SLEEP', N'QDS_CLEANUP_STALE_QUERIES_TASK_MAIN_LOOP_SLEEP', N'REQUEST_FOR_DEADLOCK_SEARCH', N'RESOURCE_QUEUE', N'SERVER_IDLE_CHECK', N'SLEEP_BPOOL_FLUSH', N'SLEEP_DBSTARTUP', N'SLEEP_DCOMSTARTUP', N'SLEEP_MASTERDBREADY', N'SLEEP_MASTERMDREADY', N'SLEEP_MASTERUPGRADED', N'SLEEP_MSDBSTARTUP', N'SLEEP_SYSTEMTASK', N'SLEEP_TASK', N'SLEEP_TEMPDBSTARTUP', N'SNI_HTTP_ACCEPT', N'SP_SERVER_DIAGNOSTICS_SLEEP', N'SQLTRACE_BUFFER_FLUSH', N'SQLTRACE_INCREMENTAL_FLUSH_SLEEP', N'SQLTRACE_WAIT_ENTRIES', N'WAIT_FOR_RESULTS', N'WAITFOR', N'WAITFOR_TASKSHUTDOWN', N'WAIT_XTP_HOST_WAIT', N'WAIT_XTP_OFFLINE_CKPT_NEW_LOG', N'WAIT_XTP_CKPT_CLOSE', N'XE_DISPATCHER_JOIN', N'XE_DISPATCHER_WAIT', N'XE_TIMER_EVENT')
AND [waiting_tasks_count] > 0
)
SELECT
MAX ([W1].[wait_type]) AS [WaitType], CAST (MAX ([W1].[WaitS]) AS DECIMAL (16, 2)) AS [Wait_S], CAST (MAX ([W1].[ResourceS]) AS DECIMAL (16, 2)) AS [Resource_S], CAST (MAX ([W1].[SignalS]) AS DECIMAL (16, 2)) AS [Signal_S], MAX ([W1].[WaitCount]) AS [WaitCount], CAST (MAX ([W1].[Percentage]) AS DECIMAL (5, 2)) AS [Percentage], CAST ((MAX ([W1].[WaitS]) / MAX ([W1].[WaitCount])) AS DECIMAL (16, 4)) AS [AvgWait_S], CAST ((MAX ([W1].[ResourceS]) / MAX ([W1].[WaitCount])) AS DECIMAL (16, 4)) AS [AvgRes_S], CAST ((MAX ([W1].[SignalS]) / MAX ([W1].[WaitCount])) AS DECIMAL (16, 4)) AS [AvgSig_S]
FROM [Waits] AS [W1]
INNER JOIN [Waits] AS [W2]
ON [W2].[RowNum] <= [W1].[RowNum]
GROUP BY [W1].[RowNum]
HAVING SUM ([W2].[Percentage]) - MAX ([W1].[Percentage]) < 95; -- percentage threshold
GO

执行此脚本时, 我们需要集中精力于结果的最上面几行, 因为它们首先设置并代表最大等待类型。

我们需要了解等待类型, 以便做出正确的决定。要了解不同的等待类型, 我们可以转到出色的Microsoft文档。

让我们举一个例子, 我们有太多的PAGEIOLATCH_XX。这意味着线程正在等待数据页从磁盘读入缓冲区, 缓冲区不过是内存块。我们必须确保我们了解发生了什么。这并不一定意味着I / O子系统较差或内存不足, 增加I / O子系统和内存将解决问题, 但这只是暂时的。为了找到一个永久性的解决方案, 我们需要看看为什么要从磁盘读取这么多的数据：导致这种情况的是哪种SQL命令？通过使用诸如where子句之类的过滤器, 我们读取的数据太多而不是读取的数据更少吗？是否由于表扫描或索引扫描而发生过多的数据读取？我们可以通过实现或修改现有索引将它们转换为索引查找吗？我们是否正在编写被SQL Optimizer(SQL Server程序内部的另一个程序)误解的SQL查询？

我们需要从不同角度进行思考, 并使用不同的测试案例来提出解决方案。以上每种等待类型都需要不同的解决方案。数据库管理员需要对它们进行彻底研究, 然后再采取任何措施。但是大多数时候, 查找有问题的T-SQL查询并对其进行调整将解决60％至70％的问题。

#2查找有问题的查询

如上所述, 我们可以做的第一件事是搜索有问题的查询。以下T-SQL代码将查找20个性能最差的查询：

SELECT TOP 20
total_worker_time/execution_count AS Avg_CPU_Time
, Execution_count
, total_elapsed_time/execution_count as AVG_Run_Time
, total_elapsed_time
, (SELECT
SUBSTRING(text, statement_start_offset/2+1, statement_end_offset
) FROM sys.dm_exec_sql_text(sql_handle)
) AS Query_Text
FROM sys.dm_exec_query_stats
ORDER BY Avg_CPU_Time DESC

我们需要谨慎对待结果；即使查询可以具有最大的平均运行时间, 但是如果查询仅运行一次, 则与平均运行时间中等且一天要运行多次的查询相比, 对服务器的总体影响较低。

#3微调查询

T-SQL查询的微调是一个重要的概念。要了解的基本知识是, 我们可以编写T-SQL查询和实现索引的程度如何, 以便SQL优化器可以找到优化的计划来完成我们想要的工作。随着SQL Server的每个新版本的推出, 我们将获得更完善的优化器, 该优化器将涵盖我们在编写未经优化的SQL查询时所犯的错误, 并且还将修复与以前的优化器有关的所有错误。但是, 无论优化器的智能程度如何, 如果我们无法告诉我们想要什么(通过编写适当的T-SQL查询), 则SQL优化器将无法完成其工作。

SQL Server使用高级搜索和排序算法。如果我们擅长搜索和排序算法, 那么大多数时候我们都可以猜测为什么SQL Server会采取特定的措施。学习和理解此类算法的最佳书籍是Donald Knuth撰写的《计算机编程的艺术》。

当我们检查需要微调的查询时, 我们需要使用这些查询的执行计划, 以便我们可以找到SQL Server如何解释它们。

我无法在这里介绍执行计划的所有方面, 但是在基本层面上, 我可以解释我们需要考虑的事项。

• 首先, 我们需要找出哪些运营商承担了大部分查询费用。
• 如果运营商要花很多钱, 我们需要了解原因。在大多数情况下, 扫描所花费的成本要高于寻找所花费的成本。我们需要检查为什么发生特定的扫描(表扫描或索引扫描)而不是索引搜索。我们可以通过在表列上实现适当的索引来解决此问题, 但是与任何复杂程序一样, 没有固定的解决方案。例如, 如果表很小, 则扫描比查找要快。
• 大约有78个运算符, 它们代表SQL Server执行计划的各种操作和决策。我们需要通过参考Microsoft文档来深入研究它们, 以便我们可以更好地理解它们并采取适当的措施。

#4执行计划重用

即使我们在表上实现适当的索引并编写良好的T-SQL代码, 如果不重用执行计划, 也会遇到性能问题。在对查询进行微调之后, 我们需要确保在必要时可以重新使用执行计划。如果我们重复使用计划, 则大部分CPU时间将用于计算可以取消的执行计划。

我们可以使用下面的查询来找出执行计划被重用了多少次, 其中usecounts表示计划被重用了多少次：

SELECT [ecp].[refcounts]
, [ecp].[usecounts]
, [ecp].[objtype]
, DB_NAME([est].[dbid]) AS [db_name]
, [est].[objectid]
, [est].[text] as [query_ext]
, [eqp].[query_plan]
FROM sys.dm_exec_cached_plans ecp
CROSS APPLY sys.dm_exec_sql_text ( ecp.plan_handle ) est
CROSS APPLY sys.dm_exec_query_plan ( ecp.plan_handle ) eqp

重用执行计划的最佳方法是实现参数化的存储过程。当我们无法实现存储过程时, 可以使用sp_executesql, 当对SQL语句的唯一更改是参数值时, 可以将其用于执行T-SQL语句。 SQL Server很可能会重用它在第一次执行中生成的执行计划。

同样, 与任何复杂的计算机程序一样, 没有固定的解决方案。有时最好重新编译该计划。

让我们研究以下两个示例查询：

• 从表中选择名称, 其中name =’sri’;
• 从表中选择名称, 其中name =’pal’;

假设我们在名称列上有一个非聚集索引, 并且表的一半具有值sri, 而在名称列中只有几行具有pal。对于第一个查询, SQL Server将使用表扫描, 因为表的一半具有相同的值。但是对于第二个查询, 最好使用索引扫描, 因为只有几行具有pal值。

即使查询相似, 相同的执行计划也可能不是一个好的解决方案。在大多数情况下, 情况会有所不同, 因此我们需要在决定之前仔细分析所有内容。如果我们不想重复使用执行计划, 则可以随时在存储过程中使用”重新编译”选项。

请记住, 即使在使用存储过程或sp_executesql之后, 有时也不会重复使用执行计划。他们是：

• 查询使用的索引何时更改或删除
• 查询使用的表的统计信息, 结构或架构更改时
• 当我们使用”重新编译”选项时
• 大量插入, 更新或删除时
• 当我们在单个查询中混合DDL和DML时

#5删除不必要的索引

优化查询后, 我们需要检查索引的使用方式。索引维护需要大量的CPU和I / O。每次我们将数据插入数据库时​​, SQL Server都还需要更新索引, 因此如果不使用索引, 最好将其删除。

SQL Server为我们提供了dm_db_index_usage_stats DMV来查找索引统计信息。当我们运行下面的T-SQL代码时, 我们将获得不同索引的使用情况统计信息。如果发现根本不使用或很少使用的索引, 则可以删除它们以获得性能。

SELECT 
OBJECT_NAME(IUS.[OBJECT_ID]) AS [OBJECT NAME], DB_NAME(IUS.database_id) AS [DATABASE NAME], I.[NAME] AS [INDEX NAME], USER_SEEKS, USER_SCANS, USER_LOOKUPS, USER_UPDATES
FROM SYS.DM_DB_INDEX_USAGE_STATS AS IUS
INNER JOIN SYS.INDEXES AS I
ON I.[OBJECT_ID] = IUS.[OBJECT_ID]
AND I.INDEX_ID = IUS.INDEX_ID

#6 SQL Server安装和数据库设置

设置数据库时, 我们需要分别保存数据和日志文件。这样做的主要原因是, 写入和访问数据文件不是顺序的, 而写入和访问日志文件是顺序的。如果将它们放在同一驱动器上, 我们将无法以最佳方式使用它们。

当我们购买存储区域网络(SAN)时, 供应商可能会向我们提供一些有关如何设置它的建议, 但是此信息并不总是有用。我们需要与我们的硬件和网络工程师进行详细讨论, 以了解如何分别以优化的方式保留数据和日志文件。

#7不要超载SQL Server

任何数据库管理员的主要任务是确保生产服务器运行平稳并尽可能为客户提供服务。为此, 我们需要为以下环境维护单独的数据库(如果可能, 在不同的计算机上)：

• 生产
• 发展历程
• 测试中
• 分析型

对于生产数据库, 我们需要一个具有完全恢复模式的数据库, 而对于其他数据库, 简单的恢复模式就足够了。

在生产数据库上进行测试将使事务日志, 索引, CPU和I / O承受大量负担。因此, 我们需要使用单独的数据库进行生产, 开发, 测试和分析。如果可能, 请为每个数据库使用单独的计算机, 因为这将减少CPU和I / O的负载。

#8事务日志, tempdb和内存

日志文件需要具有足够的可用空间来进行正常操作, 因为对日志文件进行自动增长操作非常耗时, 并且可能迫使其他操作等待其完成。为了找出每个数据库的日志文件大小以及使用了多少日志文件, 我们可以使用DBCC SQLPERF(logspace)。

设置tempdb的最佳方法是将其放在单独的磁盘上。我们需要保持尽可能大的初始大小, 因为当达到自动增长的情况时, 性能将下降。

如前所述, 我们需要确保SQL Server在单独的机器上运行, 最好是在上面没有任何其他应用程序的机器上运行。我们需要为操作系统保留一些内存, 如果它是集群的一部分, 则还需要保留更多的内存, 因此在大多数情况下, 应该有大约2GB的内存。

对于关键任务环境, 获取信息的毫秒延迟可能会破坏交易。

鸣叫

总结

此处讨论的过程和建议仅用于基本性能调整。如果遵循这些步骤, 则平均而言, 我们可以将性能提高40％到50％。若要进行高级SQL Server性能调整, 我们需要更深入地研究此处介绍的每个步骤。

相关：从Oracle到SQL Server和从SQL Server到Oracle的迁移指南