如何调整 Microsoft SQL Server 的性能

已发表: 2022-03-11

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

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

#1 寻找罪魁祸首

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

sql服务器性能

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

  • 硬件和安装设置，可能需要更正，因为 SQL Server 需要是特定的
  • 如果我们为 SQL Server 提供了正确的 T-SQL 代码来实现

尽管 SQL Server 是专有软件，但 Microsoft 提供了很多方法来理解它并有效地使用它。

如果硬件没问题，安装已经正常，但是SQL Server还是运行缓慢，那么首先我们需要看看是否有软件相关的错误。 要检查发生了什么，我们需要观察不同线程的执行情况。 这是通过计算不同线程的等待统计来实现的。 SQL Server 对每个用户请求都使用线程，而线程只是我们复杂程序中的另一个程序，称为 SQL Server。 需要注意的是，该线程不是安装 SQL Server 的操作系统线程； 它与 SQLOS 线程有关，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 服务器程序中的另一个程序）误解的 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 执行计划的各种操作和决策。 我们需要通过查阅微软文档来深入研究它们，以便更好地理解它们并采取适当的行动。
相关： SQL 索引解释，Pt。 1

#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 很可能会重用它在第一次执行时生成的执行计划。

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

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

  • select name from table where name = 'sri';
  • select name from table where name = 'pal';

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

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

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

  • 当查询使用的索引更改或被删除时
  • 当查询使用的表的统计信息、结构或模式发生变化时
  • 当我们使用“重新编译”选项时
  • 当有大量的插入、更新或删除时
  • 当我们在单个查询中混合 DDL 和 DML

#5 删除不必要的索引

在对查询进行微调之后，我们需要检查索引是如何使用的。 索引维护需要大量 CPU 和 I/O。 每次我们向数据库中插入数据时，SQL Server 也需要更新索引，所以如果不使用，最好将其删除。

sql服务器性能

SQL 服务器为我们提供了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 事务日志、临时数据库和内存

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

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

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

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

结论：

此处讨论的过程和建议仅用于基本性能调整。 如果我们遵循这些步骤，平均而言，我们可能会获得大约 40% 到 50% 的性能改进。 要进行高级 SQL Server 性能调整，我们需要更深入地研究此处介绍的每个步骤。

进一步阅读 Toptal 工程博客：

  • 使用 SQL 索引和分区解决瓶颈
  • Oracle 到 SQL Server 和 SQL Server 到 Oracle 迁移指南