为了留住用户，任何应用程序或网站都必须快速运行. For mission critical environments, 获取信息的几毫秒延迟可能会造成大问题. As database sizes grow day by day, 我们需要尽可能快地获取数据, 并尽可能快地将数据写回数据库. 确保所有操作顺利进行, 我们必须调优数据库服务器的性能.

在本文中，我将逐步描述basic的过程 SQL Server performance tuning..

#1 Finding The Culprits

As with any other software, 我们需要了解Microsoft SQL Server(简称SQL Server或MSSQL)是一个复杂的计算机程序. 如果它有问题，我们需要发现为什么它没有像我们期望的那样运行. 因此，让我们看看如何提高SQL Server的性能.

从SQL Server中，我们需要尽可能快速和准确地提取和推送数据. 如果出现问题，有几个基本原因，首先要检查的两件事是:

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

即使SQL Server是专有软件, 微软提供了很多理解和有效使用它的方法.

如果硬件没有问题，并且安装已经正确完成, 但SQL Server的性能仍然不足, 然后为了提高SQL Server的运行速度, 我们需要查明是否有软件相关的错误. 为了检查发生了什么，我们需要观察不同线程的执行情况. 这是通过计算不同线程的等待统计数据来实现的. SQL server为每个用户请求使用线程, 而线程只不过是我们复杂的程序SQL Server中的另一个程序. It is important to note that this thread is not an operating system thread on which SQL server is installed; it is related to the SQLOS thread, 这是SQL Server的伪操作系统.

等待统计信息可以使用 sys.dm_os_wait_stats 动态管理视图(DMV)，它提供有关其当前状态的附加信息. 网上有很多脚本可以查询这个视图，但我最喜欢的是 Paul Randal’s script 因为它很容易理解，并且具有观察等待统计的所有重要参数:

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],
[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].[等待])AS DECIMAL (16,2)) AS[等待]，
CAST (MAX ([W1].[资源])AS (16,2)) AS[资源]，
CAST (MAX ([W1].[信号])AS DECIMAL (16,2)
MAX ([W1].[WaitCount]) AS [WaitCount],
CAST (MAX ([W1].[百分比])(十进制(5,2))(百分比)，
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

When we execute this script, 我们需要关注结果的顶部行，因为它们首先被设置，并表示最大等待类型.

我们需要了解等待类型，这样我们才能做出正确的决定. 要了解不同的等待类型，我们可以去优秀的 Microsoft documentation.

让我们举一个例子，我们有太多 PAGEIOLATCH_XX. 这意味着线程正在等待从磁盘读取数据页到缓冲区, 这只是一个内存块. 我们必须确定我们明白发生了什么. 这并不一定意味着I/O子系统很差或者内存不够, 增加I/O子系统和内存可以解决这个问题, but only temporarily. 要找到一个永久的解决方案，我们需要了解为什么要从磁盘读取这么多数据:什么类型的SQL命令导致了这种情况? 我们是否读取了太多的数据，而不是通过使用过滤器读取更少的数据，例如 where clauses? 由于表扫描或索引扫描，是否发生了太多的数据读取? 我们能否通过实现或修改现有索引将它们转换为索引查找? 我们编写的SQL查询是否被SQL优化器(SQL server程序中的另一个程序)误解了??

我们需要从不同的角度思考，并使用不同的测试用例来提出解决方案. 上述每种等待类型都需要不同的解决方案. 数据库管理员在采取任何行动之前需要对它们进行彻底的研究. But most of the time, 找到有问题的T-SQL查询并对其进行调优可以解决60%到70%的问题.

#2 Finding Problematic Queries

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

SELECT TOP 20
total_worker_time/execution_count AS Avg_CPU_Time
,Execution_count
，total_elapsed_time/execution_count为AVG_Run_Time
,total_elapsed_time
,(SELECT
SUBSTRING(文本、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

We need to be careful with the results; even though a query can have a maximum average run time, if it runs only once, 与平均运行时间中等且每天运行很多次的查询相比，对服务器的总体影响较低.

#3 Fine Tuning Queries

T-SQL查询的微调是一个重要的概念. 要理解的最基本的事情是我们如何编写T-SQL查询和实现索引, 这样SQL优化器就可以找到一个优化的计划来做我们想让它做的事情. 随着SQL Server的每一个新版本, 我们得到了一个更复杂的优化器，它将覆盖我们在编写未优化的SQL查询时所犯的错误, 并且还将修复与以前的优化器相关的任何错误. But, 不管优化器有多智能, 如果我们不能告诉它我们想要什么(通过编写适当的T-SQL查询), SQL优化器将无法完成它的工作.

SQL Server uses advanced search and sorting algorithms. 如果我们擅长搜索和排序算法, 那么大多数时候，我们可以猜测为什么SQL Server正在采取特定的行动. 学习和理解这类算法的最佳书籍是 The Art of Computer Programming by Donald Knuth.

当我们检查需要微调的查询时, 我们需要使用这些查询的执行计划，这样我们就可以发现SQL server是如何解释它们的.

我无法在这里涵盖执行计划的所有方面, 但在基本层面上，我可以解释我们需要考虑的事情.

• 首先，我们需要找出哪些运算符占用了大部分查询开销.
• 如果运营商花费了大量的成本，我们需要了解其中的原因. 大多数情况下，扫描将比搜索花费更多的成本. 我们需要检查为什么发生特定的扫描(表扫描或索引扫描)而不是索引查找. 我们可以通过在表列上实现适当的索引来解决这个问题, but as with any complex program, there is no fixed solution. 例如，如果表很小，则扫描速度比查找速度快.
• 大约有78家运营商, 代表SQL Server执行计划的各种动作和决策. 我们需要通过咨询 Microsoft documentation，以便我们更好地了解他们，并采取适当的行动.

#4 Execution Plan Re-use

即使我们在表上实现了适当的索引，并编写了良好的T-SQL代码, 如果执行计划没有被重用, we will have performance issues. After fine-tuning the queries, 我们需要确保在必要时可以重用执行计划. 大部分CPU时间将用于计算可以消除的执行计划, if we re-use the plan.

我们可以使用下面的查询找出执行计划被重用了多少次，在哪里 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 = 'sri'的表
• 查询表中name = 'pal'的表名

的非聚类索引 name 列和表的一半具有值 sri and few rows have pal in the name column. For the first query, SQL Server将使用表扫描，因为表的一半具有相同的值. 但是对于第二个查询，最好使用索引扫描，因为只有少数行进行了索引扫描 pal value.

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

请记住，即使在使用存储过程或 sp_executesql，有时执行计划不会被重用. They are:

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

#5 Removing Unnecessary Indexes

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

SQL server provides us dm_db_index_usage_stats DMV to find index statistics. 当我们运行下面的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

SQL Server安装和数据库设置

在建立数据库时，我们需要分别保存数据和日志文件. 主要原因是写入和访问数据文件不是顺序的, 而写入和访问日志文件是顺序的. 如果我们把它们放在同一个驱动器上，我们就不能以优化的方式使用它们.

当我们购买存储区域网络时, 供应商可能会给我们一些关于如何安装的建议, 但这些信息并不总是有用的. 我们需要与硬件和网络人员详细讨论如何以优化的方式分别保存数据和日志文件.

#7 Don’t Overload SQL Server

任何数据库管理员的主要任务都是确保生产服务器顺利运行并尽可能地为客户提供服务. 为了实现这一点，我们需要维护单独的数据库(如果可能的话), 在不同的机器上)，适用于以下环境:

• Production
• Development
• Testing
• Analytical

对于生产数据库，我们需要一个完整的数据库 recovery mode对于其他数据库，一个简单的恢复模式就足够了.

Testing on a production database 会给事务日志、索引、CPU和I/O带来很多负载. 这就是为什么我们需要为生产、开发、测试和分析使用不同的数据库. If possible, 为每个数据库使用单独的机器, 因为它将减少CPU和I/O的负载.

事务日志、tempdb和内存

日志文件需要有足够的空闲空间进行正常操作，因为对日志文件进行自动增长操作非常耗时，并且可能会迫使其他操作等待该操作完成. 要查找每个数据库的日志文件大小和使用量，可以使用 DBCC SQLPERF(logspace).

设置tempdb的最佳方法是将其放在单独的磁盘上. 我们需要保持初始规模尽可能大，因为当它达到一个自动增长的情况, performance will decrease.

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

SQL Server性能调优:值得投资

这里讨论的过程和建议仅适用于基本的SQL Server性能调优. 如果我们遵循这些步骤，我们可能，平均而言，在性能上得到大约40%到50%的提高. 执行高级SQL Server性能调优, 我们需要更深入地研究这里所涉及的每个步骤.