ASP.NET站点性能提升-加速数据库访问
SQL Server本身就是个很大的题目。这里不会涉及到SQL Server数据库访问的方方面面，而是重点关注于可能获得最大性能提升的领域。
查明瓶颈
缺少索引和昂贵查询
可以通过减少查询执行的读操作极大地提高查询性能。执行的读操作越多，对磁盘、CPU和内存的压力就可能越大。第二，进行读操作的查询可能阻塞其它进行更新的查询。如果更新查询在持有锁时必须进行等待，它可能会延迟一系列其它查询。最后，除非整个数据库都在内存中，每次从磁盘上读取数据，都需要从内存中删除其它数据。如果被删除的数据后来被用到，需要重新从磁盘上读取。
减少读操作最有效的方法是在表上创建有效索引。SQL Server索引允许查询不需要扫描整个表，而只读取需要的部分。但是，索引会有额外的开销并减慢更新操作，所以必须小心使用。
缺少索引
SQL Server允许在表字段上加索引，提高对这些字段进行操作的WHERE和JOIN语句的速度。当查询优化器优化查询时，会存储似乎应该有但没有的索引的信息。可能使用Dynamic Management View(DVM)访问这些信息：
select d.name AS DatabaseName, mid.* from sys.dm_db_missing_index_details mid join sys.databases d ON mid.database_id=d.database_id
这个查询返回的最重要列是：
| 列 | 描述 |
| DatabaseName | 这一行属于的数据库 |
| equality_columns | 具有等于操作符的逗号分割的列的列表，例如： column=value |
| inequality_columns | 具有比较操作符的逗号分割的列的列表，例如： column>value |
| included_columns | 如果包括在索引中可能会有收益的逗号分割的列的列表 |
| statement | 缺失索引的表名 |
这些信息会在重启服务器后清空。
另一个方法是使用SQL Server 2008包含的Data Engine Tuning Advisor。这个工具会分析数据操作跟踪数据，根据所有查询识别出最佳的索引集。它甚至给出了创建识别出的缺失索引的SQL语句。
第一步是得到一段时间内的数据库操作的跟踪数据。在数据库最繁忙的时间段内，打开跟踪：
- 开启SQL Profiler。选择Start | Programs | Mircrosoft SQL Server 2008 | Performance Tools | SQL Server Profiler。
- 在SQL Profiler中，选择File | New Trace。
- 选择Events Selection选项页。
- 只保留SQL:BatchCompleted和RPC:Completed事件。确保选择了事件的TextData列。
- 单击Column Filters按钮。选择Database Name列，展开Like，输入需要监控的数据库名称。
- 选择ApplicationName，过滤需要监控的程序。
- 单击Runt按键，监控结束后，保存到文件。
- 保存为模板，下次不用再创建。选择File | Save As | Trace Template。下次创建新跟踪时，可能从Use the template下拉框选择模板。
发送这些事件到屏幕会占用很多服务器资源。解决方法是保存跟踪为脚本，然后使用脚本进行后台跟踪。 - 选择File | Export | Script Trace Definition | For SQL Server 2005-2008。现在可以关闭SQL Server Profiler，这会关闭跟踪。
- 在SQL Server Management Studio中，打开刚才创建的.sql文件。搜索字符串“InsertFileNameHere”，替换成你想要日志存储的文件的完整路径。保存。
- 开始跟踪，F5运行.sql文件。它会显示跟踪的trace ID。
- 查看系统中的跟踪状态，在查询窗口执行命令：
select * from ::fn_trace_getinfo(default)
查找执行的trace ID行中property列为5的行。如果这行的value列值是1，跟踪正在运行。trace ID为1的跟踪的系统跟踪。 - 跟踪一段时间后，假设trace ID是2，运行以下命令停止跟踪：
exec sp_trace_setstatus 2,0
重启跟踪，运行：exec sp_trace_setstatus 2,1
停止并关闭跟踪，这样才能访问跟踪文件，运行：
exec sp_trace_setstatus 2,0 exec sp_trace_setstatus 2,2
运行Database Engine Tuning Advisor:
选择Start | Programs | Microsoft SQL Server 2008 | Performance Tools | Database Engine Tuning Advisor。
在Workload区域，选择trace文件。在Database for workload analysis下拉框，选择需要分析的第一个数据库。
在Select databases and table to tune，选择需要索引建议的数据库。
如果跟踪文件很大，Database Engine Tuning Advisor会花费很长时间进行分析。在Tuning Options选项页，可以选择何时停止分析。
点击Start Analysis按键开始分析。
注意Database Engine Tuning Advisor只是个程序。可以考虑这些建议，但自己做决定。确保在典型时段进行跟踪，否则这些建议可能会使事情更糟。例如，如果提供晚上抓取的跟踪文件，那时只处理少量事务，但生成大量报表，那么这些建议就会去优化报表而不是事务。
昂贵查询
如果使用SQL Server 2008或更高版本，可以使用活动监视器查找最近执行的昂贵查询。在SSMS中，右击数据库服务器，选择Activity Monitor。
可以通过使用DMV dm_exec_query_stats可以获取更多的信息。当查询优化器为查询创建执行计划时，它会缓存计划进行重用。每次使用查询计划执行查询时，性能统计会被保留。可以使用dm_exec_query_stats查看这些统计。
SELECT
est.text AS batchtext,
SUBSTRING(est.text, (eqs.statement_start_offset/2)+1,
(CASE eqs.statement_end_offset WHEN -1
THEN DATALENGTH(est.text)
ELSE eqs.statement_end_offset END -
((eqs.statement_start_offset/2) + 1))) AS querytext,
eqs.creation_time, eqs.last_execution_time, eqs.execution_count,
eqs.total_worker_time, eqs.last_worker_time,
eqs.min_worker_time, eqs.max_worker_time,
eqs.total_physical_reads, eqs.last_physical_reads,
eqs.min_physical_reads, eqs.max_physical_reads,
eqs.total_elapsed_time, eqs.last_elapsed_time,
eqs.min_elapsed_time, eqs.max_elapsed_time,
eqs.total_logical_writes, eqs.last_logical_writes,
eqs.min_logical_writes, eqs.max_logical_writes,
eqs.query_plan_hash
FROM
sys.dm_exec_query_stats AS eqs
CROSS APPLY sys.dm_exec_sql_text(eqs.sql_handle) AS est
ORDER BY eqs.total_physical_reads DESC
DMV有一个限制：当运行它时，自从上次服务器重启后运行的查询在缓存不是都有查询计划。一些计划因为使用次数少会过期。那些生成开销很小，但运行开销又不够小的计划，根本就不会存储。如果计划被重新编译了，那统计数据是从上次重新编译开始的。
另一个限制是查询只适用于存储过程。如果使用临时查询，参数是嵌入在查询中的。这会导致查询优化器为每一组参数生成一个计划，除非查询已经是参数化的。这会在查询计划重用部分进一步讨论。
为了解决这个问题，dm_exec_query_stats返回query_plan_hash列，如果查询的执行计划是相同的，这列的值就是相同的。通过使用GROUP BY聚合这一列，可以得到使用相同逻辑的查询的总性能数据。
这个查询返回下信息：
| 列 | 描述 |
| batchtext | Text of the entire batch or stored procedure containing the query. |
| querytext | Text of the actual query. |
| creation_time | Time that the execution plan was created. |
| last_execution_time | Last time the plan was executed. |
| execution_count | Number of times the plan was executed after it was created. This is not the number of times the query itself was executed; its plan may have been recompiled at some statge. |
| total_worker_time | Total amount of CPU time in microseconds that was consumed by executions of this plan since it was created. |
| last_worker_time | CPU time in microseconds that was consumed the last time the plan was executed. |
| min_worker_time | Minimum CPU time in microseconds that this plan has ever consumed during a single execution. |
| max_worker_time | Maximum CPU time in microseconds that this plan has ever consumed during a single execution. |
| total_physical_reads | Total number of physical reads performed by executions of this plan since it was compiled. |
| last_physical_reads | Number of physical reads performed the last time the plan was executed. |
| min_physical_reads | Minimum number of physical reads that this plan has ever performed during a single execution. |
| max_physical_reads | Maximum number of physical reads that this plan has ever performed during a single execution. |
| total_logical_writes | Total number of logical writes performed by executions of this plan since it was compiled. |
| last_logical_writes | Number of logical writes performed the last time the plan was executed. |
| min_logical_writes | Minimum number of logical writes that this plan has ever performed during a single execution. |
| max_logical_writes | Maximum number of logical writes that this plan has ever performed during a single execution. |
| total_elapsed_time | Total elapsed time in microseconds for completed executions of this plan. |
| last_elapsed_time | Elapsed time in microseconds for the most recently completed execution of this plan. |
| min_elapsed_time | Minimum elapsed time in microseconds for any completed execution of this plan. |
| max_elapsed_time | Maximum elapsed time in microseconds for any completed execution of this plan. |
另一种方法是分析SQL Server Profiler生成的跟踪文件。
为了更好的分析，可以将跟踪文件保存为表格：
- 打开SQL Profiler。
- 打开跟踪文件：File | Open | Trace File。
- 保存跟踪为表格：File | Save As | Trace Table。
还可以使用fn_trace_gettable：
SELECT * INTO newtracetable FROM ::fn_trace_gettable('c:\trace.trc', default)
找到最昂贵查询或存储过程的最容易的方法是使用GROUP BY，根据查询或存储过程聚合性能数据。但是，如果查看表中的TextData列，就会发现所有的查询或存储过程调用都包括参数值。如果想要聚合它们，必须过滤掉这些参数。
如果调用的是存储过程，删除参数还不是很难，因为它们总是是存储过程名后面。
如果调用的是临时查询，删除参数就比较困难了，因为它们在每个查询中的位置都是不一样的。有一些工具可以是工作变得容易些：
- SQL Nexus Tool at http://sqlnexus.codeplex.com/.
- Trace-scrubbing tools at http://msdn.microsoft.com/en-us/library/aa175800(sql.80).aspx.
一旦定位了最昂贵的查询，就可以判断添加索引是否可以加速执行：
- 在SMMS中打开一个查询窗口。
- 在“Query”菜单，选择“Include Actual Execution Plan”或者使用快捷键Ctrl+M。
- 复制昂贵查询到查询窗口并执行，打开“Execution plan”选项页。
- 如果查询优化器发现缺失索引，就会显示绿色的消息。
- 查询更多信息，可以右键单击执行计划窗口，选择“显示查询计划XML”。在XML中，查找MissingIndexes元素。
如果发现是缺失索引，参考修复瓶颈节的缺失索引子节。
如果发现昂贵查询，参考修复瓶颈节的昂贵查询子节。
未使用索引
索引的一个缺点是当数据更新时，它们也需要更新，这就导致了延迟。它们也会占用磁盘空间。如果一个索引拖慢更新，并且几乎不使用，最好删除它。
使用DMV dm_db_index_usage_stats可以获得每个索引的使用信息：
SELECT d.name, t.name, i.name, ius.* FROM sys.dm_db_index_usage_stats ius JOIN sys.databases d ON d.database_id = ius.database_id JOIN sys.tables t ON t.object_id = ius.object_id JOIN sys.indexes i ON i.object_id = ius.object_id AND i.index_id = ius.index_id ORDER BY user_updates DESC
这个查询会显示每个上次服务器启动后有活动的索引的名称、表和数据库，和自从上次服务器启动后更新和读的数量。
user_updates列，显示由INSERT、UPDATE和DELETE操作引起的更新的数量。如果这个数字相对于读的数量很高，考虑删除这个索引：
DROP INDEX IX_TITLE ON dbo.Book
锁
如果数据库有很多查询在同时执行，一些查询会访问相同的资源，例如一张表或索引。在一个查询更新资源时，另一个查询是不能读的；否则会导致不一致的结果。
为了阻止查询访问资源，SQL Server会锁资源。等待锁释放的查询会有一些延迟。如果想要确定这些延迟是否过度，在数据库服务器上使用perfmon检查以下计数器：
分类：SQL Server:Latches
Total Latch Wait Time (ms): Total wait time in milliseconds for latches in the last second.
Lock Timeouts/sec: Number of lock requests per second that timed out. This includes requests for NOWAIT locks.
Lock Wait Time (ms): Total wait time in milliseconds for locks in the last second.
Number of Deadlocks/sec: Number of lock requests per second that resulted in a deadlock.
如果Total Latch Wait Time (ms)的数值很高，表示SQL Server使用它自己的同步机制等待的时间很长。通常情况下，Lock Timeouts/sec应该为0，Lock Wait Time (ms)应该很低。如果不是，查询等待锁释放的时间太长了。
最后，Number of Deadlocks/sec应该为0。否则，存在查询互相等待对方释放锁，阻止它们访问资源。SQL Server最后会检测到这个情况，通过回滚其中一个查询，但是这会浪费时间和已经完成的工作。
如果发现锁的问题，参考修复瓶颈节的锁子节，查找出哪些查询导致过长的锁等待时间。
执行计划重用
在执行查询时，SQL Server查询优化器会编译一个成本最低的查询计划。这会使用很多CPU周期，所以，SQL Server会在内存中缓存查询计划。当接收查询时，会试图与缓存的查询计划进行匹配。
性能计数器
分类：Processor(_Total)
% Processor Time: The percentage of elapsed time that the processor is busy.
类型：SQL Server: SQL Statistics
SQL Compilations/sec: Number of batch compiles and statement compiles per second. Expected to be very high initially after server startup.
SQL Re-Compilations/sec: Number of recompiles per second.
这些计数器在服务器刚启动时，会显示很高的数值，因为每一个收到的查询都需要编译。执行计划缓存是在内存中的，所以每次重启都需要重新编译。在正常情况下，每秒编译次数应该小于100，重新编译次数应该接近0。
dm_exec_query_optimizer_info
另一种方法是查看服务器优化查询花费的时间。因为查询优化是CPU密集型操作，所以几乎所有的CPU时间都花费在这上面了。
Dynamic Management View (DMV) sys.dm_exec_query_optimizer_info显示上次服务器重启后查询优化次数和平均时间（单位秒）。
SELECT
occurrence AS [Query optimizations since server restart],
value AS [Avg time per optimization in seconds],
occurrence * value AS [Time spend optimizing since server
restart in seconds]
FROM sys.dm_exec_query_optimizer_info
WHERE counter='elapsed time'
运行这个查询，等待一段时间，再运行了一次。这样就能得到这段时间优化查询的时间。
sys.dm_exec_cached_plans
DMV sys.dm_exec_cached_plans提供计划缓存中所有执行计划的信息。可能与DMV sys.dm_exec_sql_text组合使用找出给定查询的查询计划的重用频率。如果一个查询或存储过程的执行计划的重用比率很低，那么从计划缓存中可以得到的好处也是很有限的。
SELECT ecp.objtype, ecp.usecounts, ecp.size_in_bytes, REPLACE(REPLACE(est.text, char(13), ''), char(10), ' ') AS querytext FROM sys.dm_exec_cached_plans ecp cross apply sys.dm_exec_sql_text(ecp.plan_handle) est WHERE cacheobjtype='Compiled Plan'
objtype列的值是Proc表示是存储过程，Adhoc表示是临时查询，usecounts显示计划的使用次数。
碎片
数据库中数据和索引在磁盘中是以8KB页的大小组织的。页是SQL Server与磁盘中交换数据的最小单位。
当插入或更新数据时，一个页的空间可能不够了，SQL Server创建一个新页，将原来页上的一半内容移动到新页上。这使新页和老页上都留下了空闲空间。这样，如果在老页上不停地插入和更新数据，就不会持续得分割数据。
这样，在很多次更新、插入和删除数据后，就会有很多半满的页。这会占用更多的磁盘空间，更重要的是会拖慢数据读取。这些页和物理顺序与SQL Server需要读取的逻辑顺序也可能不一样。结果，SQL Server需要等待磁盘头到达下一页，而不是顺序读取，这样，就会有更多的延迟。
使用dm_db_index_physical_stats DMV查询表和索引的碎片程度：
DECLARE @DatabaseName sysname SET @DatabaseName = 'mydatabase' --use your own database name SELECT o.name AS TableName, i.name AS IndexName, ips.index_type_desc, ips.avg_fragmentation_in_percent, ips.page_count, ips.fragment_count, ips.avg_page_space_used_in_percent FROM sys.dm_db_index_physical_stats( DB_ID(@DatabaseName), NULL, NULL, NULL, 'Sampled') ips JOIN sys.objects o ON ips.object_id = o.object_id JOIN sys.indexes i ON (ips.object_id = i.object_id) AND (ips.index_id = i.index_id) WHERE (ips.page_count >= 7) AND (ips.avg_fragmentation_in_percent > 20) ORDER BY o.name, i.name
这会统计所有使用超过7个页并且这些页的碎片超过20%的表和索引。
如果索引类型是CLUSTERED INDEX，实际上指的是表，因为表是聚集索引的一部分。索引类型HEAP指的是没有聚集索引的表。
内存
在perfmon中使用以下数据器查询是否内存不中拖慢数据库服务器：
分类：Memory
Pages/sec: When the server runs out of memory, it stores information temporarily on disk, and then later reads it back when needed, which is very expensive. This counter indicates how often this happens.
分类：SQL Server: Buffer Manager
Page Life Expectancy: Number of seconds a page will stay in the buffer pool without being used. The greater the life expenctancy, the greater the change that SQL Server will be able to get a page from memory instead of having to read it from disk.
Buffer cache hit ratio: Percentage of pages that were found in the buffer pool, without having to read from disk.
如果Pages/sec一直很高或者Page Life Expectancy一直很低，低于300，或Buffer cache hit ratio一直很低，低于90%，SQL Server可能没有足够的内存。这会导致过度的磁盘I/O，造成更大的CPU和磁盘压力。
磁盘
如果在上一节发现内存问题，首先修复它，因为内存不足会导致更多的磁盘使用。否则，检查以下计数器：
分类：PhysicalDisk and LogicalDisk
% Disk Time: Percentage of elapsed time that the selected disk was busy reading or writing.
Avg. Disk Queue Length: Average number of read and write requests queued during the sample interval.
Current Disk Queue Length: Current number of request queued.
如果Disk Time持续保持在85%以上，磁盘系统的压力就比较大了。
Avg. Disk Queue Length和Current Disk Queue Length指磁盘控制器排队的任务数和正在处理的任务数。正常的数字应该是2以下。如果使用了磁盘阵列，控制器被附加到多个磁盘，计数器值是磁盘数量的两倍或更少。
CPU
如果发现内存或磁盘问题，先解决它们，因为它们会增加磁盘的压力。CPU计数器：
分类：Processor
% Processor Time: Proportion of time that the processor is busy.
分类：System
Processor Queue Length: Number of threads waiting to be processed.
如果% Processor Time持续高于75%，或Processor Queue Length持续高于2，CPU可能压力过大。
修复瓶颈
缺失索引
聚集索引
考察这张表：
CREATE TABLE [dbo].[Book]( [BookId] [int] IDENTITY(1,1) NOT NULL, [Title] [nvarchar](50) NULL, [Author] [nvarchar](50) NULL, [Price] [decimal](4, 2) NULL)
因为这张表没有聚集索引，所以称为堆表。它的记录是无序的。如果需要查找标题包含某一关键字的所有书籍，必须读取所有的记录。这张表的结构非常简单：
我们可以测试在这张表中定位一条记录需要多少时间，然后与有索引的表进行对比。
告诉SQL Server显示I/O和计算查询的时间：
SET STATISTICS IO ON SET STATISTICS TIME ON
清空内存缓存：
CHECKPOINT DBCC DROPCLEANBUFFERS
在有一百万条记录的表中运行查询：
SELECT Title, Author, Price FROM dbo.Book WHERE BookId = 5000
测试机器上的结果是：9564, CPU time: 109 ms, elapsed time: 808 ms。
SQL Server使用8KB的页存储所有数据。结果显示读取了9564个页，也就是整张表。
现在，加入聚集索引：
ALTER TABLE Book ADD CONSTRAINT [PK_Book] PRIMARY KEY CLUSTERED ([BookId] ASC)
这会在列BookId上创建一个索引，使得BookId上的WHERE和JOIN语句更快。索引会根据BookId排序表，并增加一个称为B-树的结构加速访问：
现在，运行同样的查询，结果是：
reads: 2, CPU time: 0 ms, elapsed time: 32 ms。
非聚集索引
现在，我们使用Title替代BookId进行查询：
SELECT Title, Author FROM dbo.Book WHERE Title = 'Don Quixote'
结果是：reads: 9146, CPU time: 156 ms, elapsed time: 1653 ms。
这和堆表的结果差不多。
解决方法是在Title列中创建索引。然而，因为聚集索引会使得表也按照索引字段排序，所以只能有一个聚集索引。因为已经在BookId上创建了聚集索引，所以只能创建非聚集索引。
非聚集索引创建了表记录的备份，这次是按照Title排序的。为了节省空间，SQL Server排除了聚集索引字段以外的其它列。一张表上可以创建249个非聚集索引。
因为我们需要在查询中访问其它字段，我们需要聚集索引可以链接到表记录。方法是在非聚集索引记录中加入BookId。因为BookId有聚集索引，一旦通过非聚集索引找到BookId，就可以使用聚集索引得到真正的表记录。这个方法中的第二步骤称作键查找。
为什么要通过聚集索引，而不在非聚集索引中使用表记录的物理地址？因为当更新表记录时，记录可能会变大，SQL Server需要移动后面的记录腾出空间。如果非聚集索引包括物理地址，每次移动记录时，都需要更新地址。在更慢的更新和更慢的读之间有一个平衡。如果没有聚集索引或聚集索引没有唯一约束，非聚集索引记录就包括物理地址。
查看非聚集索引的效果，首先创建它：
CREATE NONCLUSTERED INDEX [IX_Title] ON [dbo].[Book]([Title] ASC)
运行查询：
SELECT Title, Author FROM dbo.Book WHERE Title = 'Don Quixote'
结果是： reads: 4, CPU time: 0 ms, elapsed time: 46 ms。
包含列
再一次检查测试查询，它只是返回Title和Author。Title已经在非聚集索引记录中了。如果在索引中加入Author，就不需要等待SQL Server访问表记录了，跳过了键查找步骤。
可以通过在非聚集索引中包括Author：
CREATE NONCLUSTERED INDEX [IX_Title] ON [dbo].[Book]([Title] ASC) INCLUDE(Author) WITH drop_existing
现在再运行查询：
SELECT Title, Author FROM dbo.Book WHERE Title = 'Don Quixote'
结果：reads: 2, CPU time: 0 ms, elapsed time: 26 ms。
读从4降到了2，使用时间从46ms降到了26ms，有50%的提升。从绝对值来看，提升不多，但如果查询执行非常频繁，这样做还是很有意义的。但也不做过了头，聚集索引记录越大，8KB页上存储的记录就越少，SQL Server就需要读更多的页。
选择合适的列创建索引
因为创建和维护索引都需要开销，所以必须选择合适的列创建索引。
- 在列上创建主键，默认会在这些列上创建聚集索引。
- 在一列上创建索引，会影响使用这张表的所有查询。所以不要只关注一个查询。
- 在数据库上创建索引前，必须先做测试，确定这样做真会改善性能。
何时使用索引
当选择创建索引时，可以使用以下决策过程：
- 找出最昂贵查询。可以看到Database Engine Tuning Advisor生成的索引建议。
- 在每个JOIN的至少一列上创建一个索引。
- 考虑ORDER BY和GROUP BY子句中使用的列。
- 考虑使用WHERE子句中的列，特别是如果WHERE选择的记录数较少。但是，需要注意：
-
- 使用函数的WHERE子句不能使用索引，因为函数输出不在索引中。例如：
SELECT Title, Author FROM dbo.Book WHERE LEFT(Title, 3) = 'Don'
在Title列中放置索引不会使这个查询更快。 - 如果在WHERE子句中使用查询字符串开关是通配符的LIKE语句，SQL Server不会使用索引：
SELECT Title, Author FROM dbo.Book WHERE Title LIKE '%Quixote'
但是如果查询字符串是以文本常量开头的，能够使用索引：SELECT Title, Author FROM dbo.Book WHERE Title LIKE 'Don%'
- 使用函数的WHERE子句不能使用索引，因为函数输出不在索引中。例如：
考虑使用有唯一约束的列。这会有助于检查新值是否是唯一的。
MIN和MAX函数可以从索引中获益，因为值是排序的，就不需要查找整张表确定最大或最小值。
使用占用很多空间的字段创建索引要三思。如果是非聚集索引，列值会在索引中重复存储。如果是聚集索引，列值会在所有非聚集索引中重复存储。这会增加索引记录的大小，这样，在8KB页上只能存放更少的索引记录，这会使得SQL Server读取更多的页。
何时不使用索引
实际上创建过多的索引会降低性能，不在列上放置索引的主要原因：
- 列经常更新。
- 列低特殊性，也就是列上的值有很多重复。
列经常更新
当更新没有索引的列时，如果没有页分割，SQL Server需要向磁盘写入一个8KB的页。
但是，如果列上有一个非聚集索引，或者包含上非聚集索引中，SQL Server也需要更新索引。所以至少需要写入至少一个额外页。它还需要更新索引使用的B-树结构，潜在地需要更新更多的页。
如果更新了聚集索引的列，使用了旧值的非聚集索引记录也需要更新，因为非聚集索引中使用聚集索引键，导航到真正的数据库记录。第二，数据库记录也是根据聚集索引排序的，如果更新导致排序顺序改变了，就需要更多的写。最后，聚集索引需要B-树。
低特殊性
即使列上有索引，查询优化器也不是使用它。每一次SQL Server通过索引访问一条记录，必须使用索引结构。在非聚集索引中，可能还需要进行键查找。例如，如果选择所有价格为20元的书，恰好也有很多书是这个价格，有可能简单地读取所有书记录反而更快一点。在这种情况下，20元价格就是低特殊性。
可以使用一个简单的查询计算一列中的值的平均选择性。例如，计算Book表中的Price列的平均选择性：
SELECT
COUNT(DISTINCT Price) AS 'Unique prices',
COUNT(*) AS 'Number of rows',
CAST((100 * COUNT(DISTINCT Price) / CAST(COUNT(*) AS REAL))
AS nvarchar(10)) + '%' AS 'Selectivity'
FROM Book
如果每本书都有不同的价格，选择性就是100%。如果选择性低于85%，索引增加开销会比节省的开销更大。
有一些价格会比其它价格出现的次数更多。查看每一个价格的选择性，运行：
DECLARE @c real
SELECT @c = CAST(COUNT(*) AS real) FROM Book
SELECT
Price,
COUNT(BookId) AS 'Number of rows',
CAST((1 - (100 * COUNT(BookId) / @c))
AS nvarchar(20)) + '%' AS 'Selectivity'
FROM Book
GROUP BY Price
ORDER BY COUNT(BookId)
查询优化器不太可能使用选择性低于85%的索引。
何时使用聚集索引
聚集索引和非聚集索引特性的比较：
| 特性 | 聚集索引与非聚集索引对比 |
| 读 | 更快。因为不需要键查找。如果需要的列包含中非聚集索引中，没有区别。 |
| 更新 | 更慢。不仅是表记录，所有非聚集索引也需要更新。 |
| 插入/删除 | 更快。对于非聚集索引，在表中插入新记录意味着在非聚集索引中也要插入新记录。对于聚集索引，表就是索引的一部分，所以不需要二次插入。删除记录也是一样的。 另一方面，当记录不是插入表的非常后部，插入可能导致页分割，这样页的一半内容就需要转移到另一个页上。非聚集索引上的页分割可能性更低，因为它们的记录更小。 当记录插入到表的后部，不需要进行页分割。 |
| 列大小 | 需要保持短小和快速。因为每一个非聚集索引都包含聚集索引值，进行键查找。使用int类型要比使用nvarchar(50)要好得多。 |
如果多个列需要索引，最好将聚集索引改在主键上：
- 读：主键会包含在很多JOIN子句中，使得读性能很重要。
- 更新：主键不应该或很少更新，否则就需要更新外键。
- 插入/删除：大多数情况上，会将主键设为标识列，这样，每条记录就分配了一个唯一的，自增长的数字。这样，如果在主键上创建聚集索引，新记录始终加到表的结尾。当记录加到有聚集索引的表结尾，并且当前页没有空间时，新记录保存到新页上，当前页的数据依然保存在当前页上。这样，就避免了昂贵的页分割。
- 大小：大多数情况下，主键是int类型。它是短而快的。
实际上，如果在SSMS表设计器中设置一列为主键，SSMS默认设置这列为聚集索引，除非其它列已经设置了聚集索引。
维护索引
以下方法可以保持索引效率：
- 索引碎片整理。不断地更新导致索引和表碎片增多，降低了性能。测量碎片程序，参考查明瓶颈节的碎片子节。
- 保持统计更新。SQL Server维护统计数据以决定针对一个查询是否使用索引。这些统计数据一般情况是自动更新的，但这个功能可以关闭。如果关闭了，确保统计数据是最新的。
- 删除未使用的索引。索引加速读访问，但减慢了更新。辨别未使用的索引，参考查明瓶颈节的缺失索引和昂贵查询。
昂贵查询
缓存聚集查询
聚集语句，例如COUNT和AVG是很昂贵的，因为它们需要访问很多记录。如果一个网页需要聚集数据，考虑在一个表中缓存聚集结果，而不是每一次页面请求都重新查询一次。例如，以下代码在Aggreates表中保存一个COUNT聚集数据：
DECLARE @n int SELECT @n = COUNT(*) FROM dbo.Book UPDATE Aggregates SET BookCount = @n
当底层数据改变时，可以使用触发器或存储过程更新聚集数据。也可以使用SQL Server作业重新计算聚集结果。创建作业，参考：How to: Create a Transact-SQL Job Step (SQL Server Management Studio) http://msdn.microsoft.com/en-us/library/ms187910.aspx。
保持记录短小
减少表记录占用的空间可以加速访问。记录在磁盘上存储在8KB的页中。一个页中存储的记录越多，SQL Server获取给定结果集需要读取的页就越少。
保持记录短小的方法：
- 使用短数据类型。如果值能放在1个字节的TinyInt中，不要使用四个字节的Int。如果只是存储ASCII字符，使用varchar(n)，每个字符使用一个字节，不要使用nvarchar(n)，使用两个字节存储一个字符。如果存储固定长度的字符串，使用char(n)或nchar(n)，不要使用varchar(n)或nvarchar(n)，节省两个字节的长度空间。
- 考虑使用行外存储大的，很少使用的列。大对象字段，例如nvarchar(max)，varchar(max)，varbinary(max)，和XML字段如果小于8000字节通常存在行中，如果大于8000字节就会中行中存储一个2字节的指针，指向行外区域。在行外存储意味着访问这个字段至少需要读取两次，而不是一次。对于小得多的记录，如果这个列很少访问，也可以将它存放到行外。强制表中的大对象始终保存在行外：使用
EXEC sp_tableoption 'mytable', 'large value types out of row', '1'
考虑垂直分区。如果表中一些列访问的比其它列频繁地多，把很少使用的列存放在另一张表中。访问频繁访问的列会更快，代价是当访问不经常访问的列时，需要使用JOIN。
避免重复列。例如，不要这样做：
这个方案不仅创建了长记录，也使得更新书名很困难，一个作者也不能有多于两本的书。将书名存储在一张单独的book表中，其中包括AuthorId列人心如向书的作者。避免重复值。例如，不要这样做：
用户名和国家重复了。除了长记录，更新作者信息需要更新多个记录，并且增加了不一致的风险。在单独的表中存储使用作者和书籍，在书记录中保存作者的主键。
考虑反规范化
反规范化是上节两个观点的对立—避免重复列和重复值。
问题是这些建议提升了更新速度，一致性和记录大小，但使得数据分散在不同的表中，这意味着更多的JOIN。
例如，假设有100个地址分散在50个地址中，城市存储在一个单独的表中。这使得地址记录更短，并且更新城市名称更容易，但这也意味着每一次获取地址都需要JOIN。如果城市名称不太可能改变，并且获取城市时，都会获取地址的其它部分。那么，在地址记录中包括城市名称会更好。这个解决方案包含了重复内容（城市名称），但是，会少一次JOIN。
小心触发器
触发器是非常方便的。但它们隐藏在开发者的视野外，所以开发者可能没有意识到触发器的额外开销。
保持触发器的短小。它们运行中触发它们的事务中。所以当触发器运行时，持有锁的那个事务会一直持有锁。注意，即使没有使用BIGIN TRAN显式创建事务，每一个INSERT，UPDATE，或DELETE在操作期间创建它们自己的事务。
在决定使用哪些索引时，不要忘记和存储过程和函数一样，查看触发器。
对小而且临时的结果集使用表变量
考虑在存储过程中使用表变量代替临时表。例如，不要这样写：
CREATE TABLE #temp (Id INT, Name nvarchar(100)) INSERT INTO #temp ...
可以这样写：
DECLARE @temp TABLE(Id INT, Name nvarchar(100)) INSERT INTO @temp ...
相对于临时表，表变量有这些好处：
- SQL Server更可能把它们存储在内存中，而不是tempdb中。这意味着更少的通信量和对tempdb的锁。
- 没有事务日志开销。
- 更少地存储过程重编译。
然而，它们也有缺点：
- 在表变量创建后，不能加索引或约束。如果需要加索引，必须作为DECLARE语句的一部分：
DECLARE @temp TABLE(Id INT primary key, Name nvarchar(100))
当超过100条记录后，表变量的效率要比临时表低，因为不会为表变量创建统计信息。使得查询优化器创建优化的执行计划更困难。
使用全文搜索代替LIKE
你可能使用LIKE在文本列中搜索子串：
SELECT Title, Author FROM dbo.Book WHERE Title LIKE '%Quixote'
但是，除非查询字符串以常量文本开关，SQL Server不能使用列上的索引，就需要做全表扫描。
考虑使用SQL Server全文搜索。这会为文本列中的所有单词创建一个索引，这样搜索就会更快。使用全文搜索，参考：
- Getting Started with Full-Text Search http://msdn.microsoft.com/en-us/library/ms142497.aspx。
使用基于集合的代码代替游标
考虑使用基于集合的代码代替游标，这样性能提高1000倍也是很常见的。基于集合的代码使用的内部算法相比游标，被极大的优化了。
更多信息，访问：
- How Developers Can Avoid Transact-SQL Cursors http://www.code-magazine.com/Article.aspx?quickid=060113。
- Increase your SQL Server performance by replacing cursors with set operations http://blogs.msdn.com/b/sqlprogrammability/archive/2008/03/18/increase-your-sql-server-performance-by-replacing-cursors-with-set-operations.aspx。
- Cursors for T-SQL Beginners http://www.sqlservercentral.com/articles/cursors/65136/。
最小化SQL服务器到Web服务器的流量
不要使用SELECT *。这会返回所有的行。只返回需要的列。
如果网站只需要长文本的一部分，只发送这部分。例如：
SELECT LEFT(longtext, 100) AS excerpt FROM Articles WHERE ...
对象命名
存储过程名不要以sp_开头。SQL Server假设以sp_开头的是系统存储过程，即使以应用数据库开头，也会在master数据库中查找这些存储过程。
对象名应该以schema所有都开头。这样会节省SQL Server辨别对象的时间，提高执行计划重新性。例如：
SELECT Title, Author FROM dbo.Book
而不要使用
SELECT Title, Author FROM Book
使用SET NOCOUNT ON
在存储过程和触发器的开发加入命令SET NOCOUNT ON。会这禁止SQL Server在每个SQL语句后发送影响的行数。
对超过1M的值使用FILESTREAM
在FILESTRAM列中存储超过1M的BLOB类型的值。这会直接使用NTFS文件系统存储对象，而不使用数据库数据文件。实现方法：
- FILESTREAM Overview http://msdn.microsoft.com/en-us/library/bb933993.aspx。
WHERE子句中的列避免使用函数
WHERE子句中的列使用函数会使得SQL Server不使用这个列上的索引。
例如，下面的查询：
SELECT Title, Author FROM dbo.Book WHERE LEFT(Title, 1)='D'
SQL Server不知道LEFT函数返回的值，所以只能扫描整张表，对Title列的每一个值执行LEFT函数。
但是，它知道如何处理LIKE。重写查询：
SELECT Title, Author FROM dbo.Book WHERE Title LIKE 'D%'
SQL Server现在可以使用Title上的索引，因为LIKE字符串以文本常量开头。
使用UNION ALL替代UNION
UNION子句合并两个SELECT语句的结果集，从最终结果集中去除重复数据。这个操作很昂贵，它使用一张工作表，执行DISTINCT选择实现这个功能。
如果不介意重复，或者知道不会有重复，使用UNION ALL。
如果优化器检查到不会有重复，它会选择UNION ALL，即使使用了UNION。例如，下面的语句永远不会有重复的记录，优化器会使用UNION替代UNION ALL：
SELECT BookId, Title, Author FROM dbo.Book WHERE Author LIKE 'J%' UNION SELECT BookId, Title, Author FROM dbo.Book WHERE Author LIKE 'M%'
使用EXISTS替代COUNT查找重复记录
如果需要检查结果集中是否有记录，不要使用COUNT：
DECLARE @n int SELECT @n = COUNT(*) FROM dbo.Book IF @n > 0 print 'Records found'
这会读整张表获取记录数量。使用EXISTS：
IF EXISTS(SELECT * FROM dbo.Book) print 'Records found'
这样，SQL Server找到一条记录后，就会停止读取。
组合SELECT和UPDATE
有时候，需要SELECT和UPDATE同一条记录。例如，需要在访问记录时，更新“LastAccessed”列。可以使用SELECT和UPDATE：
UPDATE dbo.Book SET LastAccess = GETDATE() WHERE BookId=@BookId SELECT Title, Author FROM dbo.Book WHERE BookId=@BookId
但是，也可以组合SELECT到UPDATE中：
DECLARE @title nvarchar(50) DECLARE @author nvarchar(50) UPDATE dbo.Book SET LastAccess = GETDATE(), @title = Title, @author = Author WHERE BookId=@BookId SELECT @title, @author
这可以节省一些时间，并且减少记录持有的锁的时间。
锁
收集锁详细信息
可以通过跟踪SQL Server Profiler的“Blocked process report”事件查找哪些查询包含在严重的锁延迟中。
这个事件的触发发件是查询的锁等待时间超过“锁进程阈值”。使用以下查询设置这个阈值：
EXEC sp_configure 'show advanced options', 1 RECONFIGURE EXEC sp_configure 'blocked process threshold', 30 RECONFIGURE
然后，在Profiler中打开跟踪：
- 开启SQL Profiler。
- 在SQL Profiler，点击File | New Trace。
- 点击Events Selection选项卡。
- 选择Show all events checkbox查看所有事件。选择Show all columns查看所有数据列。
- 在主窗口中，展开Errors and Warnings，并选择Blocked process report事件，确保TextData列中的复选框被选中。
- 如果需要跟踪死锁，展开Locks，选择Deadlock graph事件。如果要得到死锁的额外信息，让SQL Server将每个死锁的信息写入到它的错误日志中，执行：
DBCC TRACEON(1222,-1)
- 反选其它选择事件。
- 点击Run启动跟踪。
- 保存模板，这样下次就不需要重新创建。点击File | Save As | Trace Template。
- 一旦捕捉到数据后，点击File | Save保存跟踪数据到跟踪文件中，用于今后的分析。可以点击File | Open加载一个跟踪文件。
当在Profiler中点击一个Block process report时，可以在下面的窗口中看到事件的信息，包括加锁的查询和被阻塞的查询。使用同样的方式可以得到死锁图的详细信息。
检查SQL Server死锁事件的错误日志：
- 在SSMS中展开数据库服务器，展开Management并展开SQL Server Logs。双击一条日志。
- 在日志文档查看器中，点击窗口顶部的Search，查找“deadlock-list”。在死锁列表事件后，可以找到死锁包含的查询语句的更多信息。
减少锁延迟
最有效的减少锁延迟的方法是减少持有锁的时间：
- 优化查询。查询时间超短，持有锁的时间超短。
- 存储过程优于临时查询。减少编译执行计划的时间和在网络上传输单个查询的时间。
- 如果必须使用游标，频繁提交更新。游标处理要比集合处理慢得多。
- 在持有锁的时候不要处理长操作，例如发送邮件。在打开事务时，不要等待用户输入。使用乐观锁：
-
- Optimistic Locking in SQL Server using the ROWVERSION Data Type http://www.mssqltips.com/tip.asp?tip=1501
第二个減少锁等待时间的方法是减少锁住的资源：
- 不要在频繁更新的列上放置聚集索引。这会要求聚集索引和非聚集索引上都要锁，因为它们的行上包含需要更新的值。
- 考虑在非聚集索引上包括列。这会防止查询读表记录，所以它不会阻塞其它查询更新同一条记录上不相关的列。
-
考虑使用行版本控制。SQL Server的这个特性防止读数据表行的查询阻塞更新相同行的查询，或相反。更新相同行的查询仍然相互阻塞。
行版本控制在更新前将行存储在临时区域（tempdb数据库），所以读操作可以在进行更新的同时访问临时存储的版本。这会产生额外的开销用来维护行版本，在使用进行测试。并且，如果设置了事务的隔离级别，行版本控制只能工作在读提交隔离模式下，这个模式是默认的模式。
实现行版本控制，设置READ_COMMITTED_SNAPSHOT选项。当进行设置时，只能有一个连接打开。可以通过将数据库切换到单用户模式。
ALTER DATABASE mydatabase SET SINGLE_USER WITH ROLLBACK IMMEDIATE; ALTER DATABASE mydatabase SET READ_COMMITTED_SNAPSHOT ON; ALTER DATABASE mydatabase SET MULTI_USER;
检查数据库是否开启了行版本控制，运行：
select is_read_committed_snapshot_on from sys.databases where name='mydatabase'
最后，可以设置锁超时时间。例如，中止等待时间超过5秒的语句：
SET LOCK_TIMEOUT 5000
使用1无限制等待。使用0不等待。
減少死锁
死锁是两个事务都在等待对方释放一个锁。事务1有一个资源A的锁，试图获得资源B的锁，同时，事务2有一个资源B的锁，试图获得资源A的锁。现在，两个事务都不能继续。
一个减少死锁的方法是减少锁延迟，上节已经论述了。这会减少死锁可能发生的时间窗。
第二个方法是始终以相同的顺序锁资源。如果事务2与事务1以相同的顺序锁资源（先A后B），那么，事务2就不会在等待资源A前锁住资源B，也就不会阻塞事务1了。
最后，小心使用HOLDLOCK或Repeatable Read或Serializable Read隔离级别。例如，以下代码：
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ BEGIN TRAN SELECT Title FROM dbo.Book UPDATE dbo.Book SET Author='Charles Dickens' WHERE Title='Oliver Twist' COMMIT
假如有两个事务同时运行这段代码。当它们执行SELECT时，都获得了Book表中的行的选择锁。因为Repeatable Read隔离级别，它们都会持有锁。现在，两者都试图请求Book表中的一行的更新锁，以执行UPDATE。每一个事务现在都被另一个事务持有的选择锁阻塞了。
在SELECT语句中使用UPDLOCK防止这种情况。这会使得SELECT获得更新锁，这样，只有一个事务可以执行SELECT。获得锁的事务可以执行UPDATE，然后释放锁，另一个事务也可以执行了。代码如下：
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ BEGIN TRAN SELECT Title FROM dbo.Book WITH(UPDLOCK) UPDATE dbo.Book SET Author='Charles Dickens' WHERE Title='Oliver Twist' COMMIT
执行计划重用
临时查询
考虑这个临时查询：
SELECT b.Title, a.AuthorName FROM dbo.Book b JOIN dbo.Author a ON b.LeadAuthorId=a.Authorid WHERE BookId=5
当SQL Server第一次接收到这个查询时，它会编译一个执行计划，在计划缓存中存储计划，然后执行计划。
如果SQL Server再一次接收到查询，重用它执行计划的条件是执行计划还在计划缓存中，并且：
- 查询中的对象引用至少使用schema名称限定。使用dbo.Book，不要使用Book。加上数据库会更好。
- 查询文本精确匹配。匹配时是区分大小写的，任何空白字符都会影响精确匹配。
作为第二个规则的结果，如果使用了相同的查询，但不同的BookId，也不能匹配：
SELECT b.Title, a.AuthorName FROM dbo.Book b JOIN dbo.Author a ON b.LeadAuthorId=a.Authorid WHERE BookId=9 -- Doesn't match query above, uses 9 instead of 5
简单参数化
为了使得临时查询重用缓存的执行计划容易一些，SQL Server支持简单参数化。它会自动辨别查询中的变量。因为这个功能很难做对，但是很容易做错，SQL Server只会对单张表的非常简单的查询使用，例如
SELECT Title, Author FROM dbo.Book WHERE BookId=5
它可以使用以下查询生成的执行计划：
SELECT Title, Author FROM dbo.Book WHERE BookId=9
sp_executesql
为了不让SQL Server去猜测查询的哪一部分可以转换为参数，可以使用系统存储过程sp_executesql告诉SQL Server。调用sp_executesql的方式：
sp_executesql @query, @parameter_definitions, @parameter1, @parameter2, ...
例如：
EXEC sp_executesql N'SELECT b.Title, a.AuthorName FROM dbo.Book b JOIN dbo.Author a ON b.LeadAuthorId=a.Authorid WHERE BookId=@BookId', N'@BookId int', @BookId=5
注意sp_executesql接收的前两个参数是nvarchar值，所以需要使用以N为前缀的字符串。
存储过程
除了向数据库发送单个的查询，还可以将它们打包在一个存储过程中，永久地存储在数据库中。这有以下好处：
- 和sp_executesql一样，存储过程也允许显式地定义参数，使得SQL Server更容易地重用执行计划。
- 存储过程可以包含一系列查询和T-SQL控制语句，例如IF…THEN。使用者只需要发送存储过程名和参数到服务器，不需要发送单独的查询语句，节省了网络开销。
- 存储过程对网站代码隔离了数据库细节。当表定义变化了，只需要更新一个或多个存储过程，不需要修改网站。
- 只允许通过存储过程访问数据库，可以实现更好的安全性。这样，可以允许用户只访问他们需要的信息，而不能做计划之外的操作。
创建存储过程的代码：
CREATE PROCEDURE GetBook @BookId int AS BEGIN SET NOCOUNT ON; SELECT Title, Author FROM dbo.Book WHERE BookId=@BookId END GO
在开头加入SET NOCOUNT ON，可以通过阻止SQL Server发送存储过程影响的行数的消息，提高性能。
在查询窗口执行存储过程：
EXEC dbo.GetBook @BookId=5
或者，更简单的方式
EXEC dbo.GetBook 5
在C#代码中使用存储过程也很简单：
string connectionString = "...";
using (SqlConnection connection = new SqlConnection(connectionString))
{
string sql = "dbo.GetBook";
using (SqlCommand cmd = new SqlCommand(sql, connection))
{
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add(new SqlParameter("@BookId", bookId));
connection.Open();
// Execute database command ...
}
}
阻止重用
有时，我们不想重用一个执行计划。当编译存储过程的执行计划时，计划是基于那时使用的参数的。当计划使用不同的参数重用时，使用第一组参数生成的计划，在使用第二组参数时进行了重用。但是，我们并不希望这样。
例如，考虑以下查询：
SELECT SupplierName FROM dbo.Supplier WHERE City=@City
假设Supplier表在City列上有一个索引。假设Supplier中的一半记录的City字段值是“New York”。对于“New York”进行优化的执行计划会使用全表扫描。但是，如果“San Diego”只有几条记录，对于“San Diego”的优化查询计划应该使用索引。对于一个参数好的计划可能对于另一个计划就是一个坏的计划。如果使用次优查询计划的代价要比重新编译查询的代价高，最好是告诉SQL Server为每个查询生成一个新计划。
当创建存储过程时，可以使用WITH RECOMPILE选项告诉SQL Server不要缓存执行计划。
CREATE PROCEDURE dbo.GetSupplierByCity @City nvarchar(100) WITH RECOMPILE AS BEGIN ... END
也可以对于特定的执行过程生成一个新的计划：
EXEC dbo.GetSupplierByCity 'New York' WITH RECOMPILE
最后，可以设置存储过程在下次调用时重新编译：
EXEC sp_recompile 'dbo.GetSupplierByCity'
设置使用某张表的存储过程在下次调用时都重新编译：
EXEC sp_recompile 'dbo.Book'
碎片
SQL Server提供两种方法对表和索引进行碎片整理：重建(rebuild)和重组(reorganize)。
索引重建
重建索引是对索引或表进行碎片整理最有效的方法。
ALTER INDEX myindex ON mytable REBUILD
这会使用更新页方式物理地重建索引，最大限度地减少碎片。
如果重建的是聚集索引，实际上重建的是数据表，因为表是聚集索引的一部分。
重新一张表的所有索引：
ALTER INDEX ALL ON mytable REBUILD
索引重建有一个缺点是会阻塞所有访问表和它的索引的查询。它也可能阻塞所有正在访问的查询。可以使用ONLINE选择减少这种情况：
ALTER INDEX myindex ON mytable REBUILD WITH (ONLINE=ON)
但是，这会导致重新时间更长。
另一个问题是重建是一个原子操作。如果有它完成前停止，所有已经完成的碎片整理工作都会丢失。
索引重组
与索引重建不同，索引重组不会阻塞表和它的索引，并且当它中途停止后，已完成的工作也不会丢失。但是，这是以降低效果为代价的。
重组索引，使用命令：
ALTER INDEX myindex ON mytable REORGANIZE
使用LOB_COMPACTION选项压缩大对象(Large Object, LOB)数据，例如image、text、ntext、varchar(max)、nvarchar(max)、varbinary(max)和xml：
ALTER INDEX myindex ON mytable REORGANIZE WITH (LOB_COMPACTION=ON)
在一个繁忙的系统中，索引重组要比索引重建好更好。它不是原子性的，所以如果操作失败了，不会导致所有的工作丢失。当它执行时，它只需要少量的持续较短的时间的锁，而不是锁住整张表和它的索引。如果发现一个页正在使用，它只是跳过这个页，并不再重试。
索引重组的缺点是它的效果更差，因为它会跳过页，而且它不会创建新页以达到更好地物理组织表或索引的目的。
堆表碎片整理
堆表是没有聚集索引的表，因为没有聚集索引，所以不能使用ALTER INDEX REBUILD或ALTER INDEX REORGANIZE进行碎片整理。
堆表碎片不是个大问题，因为表中的记录根本就是无序的。当插入记录时，SQL Server检查表中是否还有空间，如果有，在那儿插入记录。如果总是插入记录，而更新或删除记录，所有记录都会写在表的结尾。如果更新或删除记录，堆表中就依然可能有间隙。
因为堆表碎片整理通常不是个问题，所以这里不讨论。但是，也有一些方法：
- 创建一个聚集索引，然后删除它。
- 将堆表中的记录插入到一个新表中。
- 导出数据，truncate表，再导回数据到那张表中。
内存
缓解内存压力最常用的方法：
- 增加物理内存。
- 增加分配给SQL Server的内存。查看当前分配的内存，运行：
EXEC sp_configure 'show advanced option', '1' RECONFIGURE EXEC sp_configure 'max server memory (MB)'
如果服务器上的物理内存更多，增加分配。例如，增加到3000MB，运行：
EXEC sp_configure 'show advanced option', '1' RECONFIGURE EXEC sp_configure 'max server memory (MB)', 3000 RECONFIGURE WITH OVERRIDE
不要分配所有的物理内存。留几百MB给操作系统和其它软件。
-
减少从磁盘读取的数据。从磁盘读取的每一页都需要存储在内存中，并在内存中处理。全表扫描、聚集查询和表连接都会读取大量的数据。参考查明瓶颈的索引缺失和昂贵查询小节，減少从磁盘读取的数据。
-
尽量重用执行计划，减少计划缓存需要的内存。参考查明瓶颈的执行计划重用小节。
磁盘
一些减少磁盘系统压力的常用方法：
- 优化查询处理。
- 将日志文件移动到一个专用的物理磁盘上。
- 减少NTFS文件系统的碎片。
- 移动tempdb数据库到专用磁盘上。
- 将数据分散到两个或多个磁盘上，分散负载。
- 移动负载大的数据库对象到另一个磁盘上。
- 使用正确的RAID配置
优化查询处理
确保正确的索引和优化最昂贵的查询。
将日志文件移动到一个专用的物理磁盘上
移动磁盘的读/写头是相同较慢的过程。日志文件是顺序写的，它本身需要很少的磁盘头移动。但是如果日志文件和数据文件在同一磁盘上，是没有用的，因为磁盘头必须在日志文本和数据文件间移动。
如果将日志文件放在它自己的磁盘上，那个磁盘上磁盘头移动会很小，结果是更快的访问日志文件。修改操作，例如更新、插入和删除等修改操作会更快。
移动一个已存在的数据库的日志文件到另一个磁盘，首先分离数据库，移动日志文件到专用磁盘。然后重新附加数据库，指定日志文件的新位置。
减少NTFS文件系统的碎片
如果NTFS数据库文件有碎片了，磁盘头在读文件时，必须不停地移动磁盘头。为了减少碎片，为数据库和日志文件设置一个比较大的初始文件大小和较大的增长大小。设置足够大，保证文件不会增长到那么大，会更好。这样做的目的就是避免文件增长和收缩。
如果需要增长和收缩数据库或日志文件，考虑使用64-KB的NTFS簇大小，以匹配SQL Server读的模式。
移动tempdb数据库到专用磁盘上
tempdb用来排序、子查询、临时表、聚集、游标等。它可能非常繁忙。这使得将它移动到它专属的磁盘或不是很忙的磁盘会比较好。检查服务器上的tempdb和其它数据库的数据库和日志文件的活动信息，使用 dm_io_virtual_file_stats DMV：
SELECT d.name, mf.physical_name, mf.type_desc, vfs.* FROM sys.dm_io_virtual_file_stats(NULL,NULL) vfs JOIN sys.databases d ON vfs.database_id = d.database_id JOIN sys.master_files mf ON mf.database_id=vfs.database_id AND mf.file_id=vfs.file_id
移动tempdb数据和日志文件到G:盘，设置它们大小为10MB和1MB，运行并重启服务器：
ALTER DATABASE tempdb MODIFY FILE (NAME = tempdev, FILENAME = 'G:\ tempdb.mdf', SIZE = 10MB) GO ALTER DATABASE tempdb MODIFY FILE (NAME = templog, FILENAME = 'G:\ templog.ldf', SIZE = 1MB) GO
为了减少碎片，防止tempdb数据和日志增长和收缩，可以给它们可能需要的最大空间。
将数据分散到两个或多个磁盘上
增加文件到数据库的PRIMARY文件组。SQL Server会将数据分散到已存在的和新文件。将新文件放到新磁盘或负载不是很大的磁盘。如果可以，设置初始大小足够大，这会减少碎片。
例如，为数据uneUp数据在G:盘上增加一个初始大小为20GB的文件，运行：
ALTER DATABASE TuneUp ADD FILE (NAME = TuneUp_2, FILENAME = N'G:\TuneUp_2.ndf', SIZE = 20GB)
注意文件扩展名.ndf，这是推荐的第二文件的扩展名。
移动负载大的数据库对象到另一个磁盘上
你可以移动负载大的数据对象，例如索引，到一个新磁盘，或不太繁忙的磁盘上。使用查明瓶颈的索引缺失和昂贵查询小节介绍的dm_db_index_usage_stats DMV可以查看每个索引上执行的读和写的数量。
如果服务器有多个磁盘，在查明瓶颈的磁盘小节有度量磁盘使用情况的方法。使用这个信息决定对象移动到哪个磁盘。
将索引移动到另一个磁盘，首先创建一个新的用户自定义文件组。例如，以下语句创建文件组FG2：
ALTER DATABASE TuneUp ADD FILEGROUP FG2
然后，在文件组中加入文件：
ALTER DATABASE TuneUp ADD FILE (NAME = TuneUp_Fg2, FILENAME = N'G:\TuneUp_Fg2.ndf', SIZE = 200MB) TO FILEGROUP FG2
最后，移动对象到文件组中，例如，将表Book的Title列上的非聚集索引IX_Title移动到文件组FG2中：
CREATE NONCLUSTERED INDEX [IX_Title] ON [dbo].[Book]([Title] ASC) WITH DROP_EXISTING ON FG2
可以分配多个对象给一个文件组。可以在一个文件组中加入多个文件，这将允许将一个非常繁忙的表或索引分散到多个磁盘。
将表和它们的非聚集索引到不同的磁盘，这样一个任务可以读索引，另一个任务可以在表中进行键查找。
使用正确的RAID配置
为了提高性能和容错性，很多数据库服务器使用RAID子系统替代单独的驱动器。RAID子系统有不同的配置。为数据文件、日志文件和tempdb文件选择正确的配置可能极大地影响性能。
最常用的RAID配置是：
| RAID配置 | 描述 |
| RAID 0 | Each fle is spread ("striped") over each disk in the array. When reading or writing a fle, all disks are accessed in parallel, leading to high transfer rates. |
| RAID 5 | Each file is striped over all disks. Parity information for each disk is stored on the other disks, providing fault tolerance. File writes are slow—a single fle write requires 1 data read + 1 parity read + 1 data write + 1 parity write = 4 accesses. |
| RAID 10 | Each fle is striped over half the disks. Those disks are mirrored by the other half, providing excellent fault tolerance. A fle write requires 1 data write to a main disk + 1 data write to a mirror disk. |
| RAID 1 | This is RAID 10 but with just 2 disks, a main disk and a mirror disk. That gives you fault tolerance but no striping. |
下表是RAID与单个磁盘性能比较，N表示磁盘阵列中的磁盘个数：
| 读速度 | 写速度 | 容错 | |
| 单个磁盘 | 1 | 1 | no |
| RAID 0 | N | N | no |
| RAID 5 | N | N/4 | yes |
| RAID 10 | N | N/2 | yes |
| RAID 1 | 2 | 1 | yes |
下表是对tempdb、数据和日志文件合理的RAID配置：
| 文件 | 性能相关属性 | 建议的RAID配置 |
| tempdb | Requires good read and write performance for random access. Relatively small. Losing temporary data may be acceptable. | RAID 0, RAID 1, RAID 10 |
| log | Requires very good write performance, and fault tolerance. Uses sequential access, so striping is no beneft. | RAID 1, RAID 10 |
|
data (writes make up less than 10 percent of accesses) |
Requires fault tolerance. Random access means striping is benefcial. Large data volume. | RAID 5, RAID 10 |
|
data (writes make up over 10 percent of accesses) |
Same as above, plus good write performance. | RAID 10 |
有电池后备电源缓存的RAID控制器能很大地提高写性能，因为这允许SQL Server将写请求交付给缓存，需要等待物理磁盘访问完成。控制器在后台执行缓存的写请求。
CPU
解决处理器瓶颈的一般方法包括：
- 优化CPU密集查询。在查明瓶颈的索引缺失和昂贵查询小节中可以找到最昂贵查询的方法。DMV可以列出每个查询的CPU使用率。
- 创建查询计划是高CPU密集的。提高执行计划重用。
- 安装更多更快的处理器、L2/L3缓存，或更有效的驱动器。
更多资源
- General Index Design Guidelines
http://msdn.microsoft.com/en-us/library/ms191195.aspx - About the Missing Indexes Feature
http://msdn.microsoft.com/en-au/library/ms345524.aspx - SQLdiag Utility
http://msdn.microsoft.com/en-us/library/ms162833.aspx - Parameterized Queries
http://www.databasejournal.com/features/mssql/article.php/3834501/Parameterized-Queries.htm - The Curse and Blessings of Dynamic SQL
http://www.sommarskog.se/dynamic_sql.html - How to specify output parameters when you use the sp_executesql stored procedure in SQL Server
http://support.microsoft.com/kb/262499 - Creating Stored Procedures (Database Engine)
http://msdn.microsoft.com/en-us/library/ms190669.aspx - Activity Monitor
http://msdn.microsoft.com/en-us/library/cc879320.aspx - Batch Compilation, Recompilation, and Plan Caching Issues in SQL Server 2005
http://technet.microsoft.com/en-us/library/cc966425.aspx - Temporary Tables versus Table Variables and Their Effect on SQL Server Performance
http://www.sql-server-performance.com/articles/per/temp_tables_vs_variables_p1.aspx - Understanding Row Versioning-Based Isolation Levels
http://msdn.microsoft.com/en-us/library/ms189050.aspx - Getting Started with Full-Text Search
http://msdn.microsoft.com/en-us/library/ms142497.aspx - Minding Memory
http://www.windowsitpro.com/article/internals-and-architecture/minding-memory.aspx - SQL Server 2005 Waits and Queues
http://technet.microsoft.com/en-us/library/cc966413.aspx - Server Memory Options
http://msdn.microsoft.com/en-us/library/aa196734(SQL.80).aspx - Confguring SQL Server memory settings
http://searchsqlserver.techtarget.com/tip/Configuring-SQL-Server-memory-settings - Move SQL Server transaction log fles to a different location via T-SQL and SSMS
http://www.mssqltips.com/tip.asp?tip=1774
SQL SERVER – 2005 – Forced Parameterization and Simple Parameterization – T-SQL and SSMS
http://blog.sqlauthority.com/2007/10/25/sql-server-2005-forced-parameterization-and-simple-parameterization-t-sql-and-ssms/
