文章目录
- SQL Server性能分析
- 1 索引
- 1.1. 查询索引的情况
- 1.2. 重新生成或重新组织索引
- 1.3. 查找缺失索引组的缺失索引及其列详细信息
- 2 跟踪
- 2.1. 创建跟踪
- 2.2. 在跟踪中添加事件或事件列
- 2.3. 设置跟踪的过滤条件
- 2.4. 启动跟踪
- 2.5. 停止跟踪
- 2.6. 查询跟踪信息
- 3 收缩日志
- 3.1 查看影响日志无法收缩的原因
- 3.2 部分原因的解决方法
SQL Server性能分析
1 索引
索引的碎片率高或索引缺失都有可能造成SQL Server引擎的CPU使用率高。
1.1. 查询索引的情况
select as IndexName, -- 索引名称
d.database_id, -- 表或视图的数据库 ID
d.index_id, -- 索引的索引 ID,0 = 堆
d.partition_number, -- 所属对象内从 1 开始的分区号；表、视图或索引。1 =未分区的索引或堆
d.index_type_desc, -- 索引类型
d.avg_fragmentation_in_percent, -- 索引的逻辑碎片
d.avg_fragment_size_in_pages, --
d.*
from
sys.dm_db_index_physical_stats(DB_ID('数据库名'),OBJECT_ID(N'表或视图'),null,null,'LIMITED') d
left join sys.indexes i on
i.object_id = d.object_id and
i.index_id = d.index_id1.2. 重新生成或重新组织索引
/*以下示例将自动重新组织或重新生成数据库中平均碎片超过 10％ 的所有分区。
注意 请先修改DB_ID('这里写数据库名称')*/
SET NOCOUNT ON;
DECLARE @objectid int;
DECLARE @indexid int;
DECLARE @partitioncount bigint;
DECLARE @schemaname nvarchar(130);
DECLARE @objectname nvarchar(130);
DECLARE @indexname nvarchar(130);
DECLARE @partitionnum bigint;
DECLARE @partitions bigint;
DECLARE @frag float;
DECLARE @command nvarchar(4000);
-- Conditionally select tables and indexes from the sys.dm_db_index_physical_stats function
-- and convert object and index IDs to names.
SELECT
object_id AS objectid,
index_id AS indexid,
partition_number AS partitionnum,
avg_fragmentation_in_percent AS frag
INTO #work_to_do
FROM sys.dm_db_index_physical_stats (DB_ID('这里写数据库名称'), NULL, NULL , NULL, 'LIMITED')
WHERE avg_fragmentation_in_percent > 10.0 --碎片百分比
AND index_id > 0;
-- Select * From #work_to_do
-- Declare the cursor for the list of partitions to be processed.
DECLARE partitions CURSOR FOR SELECT * FROM #work_to_do;
-- Open the cursor.
OPEN partitions;
-- Loop through the partitions.
WHILE (1=1)
BEGIN;
FETCH NEXT
FROM partitions
INTO @objectid, @indexid, @partitionnum, @frag;
IF @@FETCH_STATUS < 0 BREAK;
SELECT @objectname = QUOTENAME(), @schemaname = QUOTENAME()
FROM sys.objects AS o
JOIN sys.schemas as s ON s.schema_id = o.schema_id
WHERE o.object_id = @objectid;
SELECT @indexname = QUOTENAME(name)
FROM sys.indexes
WHERE object_id = @objectid AND index_id = @indexid;
SELECT @partitioncount = count (*)
FROM sys.partitions
WHERE object_id = @objectid AND index_id = @indexid;
-- 30 is an arbitrary decision point at which to switch between reorganizing and rebuilding.
IF @frag < 30.0
SET @command = N'ALTER INDEX ' + @indexname + N' ON ' + @schemaname + N'.' + @objectname + N' REORGANIZE';
IF @frag >= 30.0
SET @command = N'ALTER INDEX ' + @indexname + N' ON ' + @schemaname + N'.' + @objectname + N' REBUILD';
IF @partitioncount > 1
SET @command = @command + N' PARTITION=' + CAST(@partitionnum AS nvarchar(10));
EXEC (@command);
PRINT N'Executed: ' + @command;
END;
-- Close and deallocate the cursor.
CLOSE partitions;
DEALLOCATE partitions;
-- Drop the temporary table.
DROP TABLE #work_to_do;
GO1.3. 查找缺失索引组的缺失索引及其列详细信息
注意：
返回的信息sys.dm_db_missing_index_group_stats由每次查询执行更新，而不是每次查询编译或重新编译更新。 使用情况统计信息不会持久保存，而只会在重新启动数据库引擎之前保存。 如果数据库管理员要在服务器回收后保留使用情况统计信息，则应该定期制作缺失索引信息的备份副本。 使用sqlserver_start_time sys.dm_os_sys_info中的列查找上次数据库引擎启动时间。
/* 查找缺失索引组的缺失索引及其列详细信息
此 DMV 的结果集限制为600行。 每一行都包含一个缺失索引。 如果缺少超过600个索引
*/
SELECT
-- DMV 信息
gStats.avg_total_user_cost * (gStats.avg_user_impact / 100.0) *(gStats.user_seeks + gStats.user_scans) AS 实现索引的收益指数,
gStats.avg_total_user_cost AS 缺失索引的查询成本, -- 可通过组中的索引减少的用户查询的平均成本
gStats.avg_user_impact AS [实现索引 用户的收益（%）], -- 实现此缺失索引组后，用户查询可能获得的平均百分比收益
gStats.avg_system_impact AS [实现索引 系统的收益（%）], -- 实现此缺失索引组后，系统查询可能获得的平均百分比收益。
gStats.user_seeks AS 查找次数, -- 由可能使用了组中建议索引的用户查询所导致的查找次数
gStats.user_scans AS 扫描次数, -- 由可能使用了组中建议索引的用户查询所导致的扫描次数
i.database_id AS 数据库ID,
DB_NAME( i.database_id ) AS 数据库,
i.[object_id] AS 缺失索引的表ID,
OBJECT_NAME( i.[object_id], i.database_id ) AS 缺失索引的表,
-- 创建索引的SQL语句
'CREATE INDEX [IX_' + OBJECT_NAME( i.[object_id], i.database_id ) + '_' + CONVERT (varchar, g.index_group_handle) + '_' + CONVERT (varchar, i.index_handle)
+ ']'
+ ' ON ' + i.statement
+ ' (' + ISNULL (i.equality_columns,'')
+ CASE WHEN i.equality_columns IS NOT NULL AND i.inequality_columns IS NOT
NULL THEN ',' ELSE '' END
+ ISNULL (i.inequality_columns, '')
+ ')'
+ ISNULL (' INCLUDE (' + i.included_columns + ')', '') AS [创建索引的SQL语句]
FROM sys.dm_db_missing_index_groups g -- 缺失索引组（一个索引组仅包含一个索引）
INNER JOIN sys.dm_db_missing_index_group_stats gStats ON -- 缺失索引组的摘要信息
gStats.group_handle = g.index_group_handle
INNER JOIN sys.dm_db_missing_index_details i ON -- 缺失索引的详细信息
g.index_handle = i.index_handle
ORDER BY gStats.avg_total_user_cost * gStats.avg_user_impact * (gStats.user_seeks + gStats.user_scans) DESC2 跟踪
跟踪可以由SQL Server工具 SQL Server Profiler 图形界面创建，也可以由T-SQL语句创建。后面小点介绍使用T-SQL创建跟踪。
创建常用的跟踪模板的T-SQL:
1.死锁跟踪
2.SQL执行耗时跟踪
2.1. 创建跟踪
/**************************************/
/* 1 新建跟踪（新的跟踪将处于停止状态） */
/**************************************/
/*返回代码 描述
0 没有错误。
1 未知错误。
10 无效选项。 指定的选项不兼容时返回此代码。
12 文件未创建。
13 内存不足。 在没有足够内存执行指定的操作时返回此代码。
14 无效停止时间。 在指定的停止时间已发生时返回此代码。
15 参数无效。 在用户已提供不兼容的参数时返回此代码。
*/
declare @rc int
declare @TraceID int
-- 生成的跟踪文件最大大小（MB），默认值为 5
declare @maxfilesize bigint
-- 指定跟踪将写入的位置和文件名。
-- 例如c:\MyFolder\MyTrace\test
-- SQL Server 会将 .trc 扩展名追加到所有跟踪文件名
declare @tracefile nvarchar(245)
set @maxfilesize = 5
exec @rc = sp_trace_create @TraceID output, 0, @tracefile, @maxfilesize, NULL
if (@rc != 0)
begin
select ErrorCode=@rc
return
end2.2. 在跟踪中添加事件或事件列
注意：下列事件模板为 SQL执行耗时跟踪。可根据实际情况增删事件或事件列。
详细事件ID和事件列ID见：MSDN文档： sp_trace_setevent (Transact-SQL)
/**********************************/
/* 2 在跟踪中添加事件或事件列 */
/* 注意：下列事件模板为 SQL执行耗时跟踪 */
/**********************************/
declare @on bit
set @on = 1
exec sp_trace_setevent @TraceID, 10, 10, @on
exec sp_trace_setevent @TraceID, 10, 3, @on
exec sp_trace_setevent @TraceID, 10, 8, @on
exec sp_trace_setevent @TraceID, 10, 12, @on
exec sp_trace_setevent @TraceID, 10, 13, @on
exec sp_trace_setevent @TraceID, 10, 14, @on
exec sp_trace_setevent @TraceID, 10, 15, @on
exec sp_trace_setevent @TraceID, 10, 16, @on
exec sp_trace_setevent @TraceID, 10, 17, @on
exec sp_trace_setevent @TraceID, 10, 18, @on
exec sp_trace_setevent @TraceID, 10, 31, @on
exec sp_trace_setevent @TraceID, 10, 35, @on
exec sp_trace_setevent @TraceID, 10, 48, @on
exec sp_trace_setevent @TraceID, 12, 1, @on
exec sp_trace_setevent @TraceID, 12, 3, @on
exec sp_trace_setevent @TraceID, 12, 8, @on
exec sp_trace_setevent @TraceID, 12, 10, @on
exec sp_trace_setevent @TraceID, 12, 12, @on
exec sp_trace_setevent @TraceID, 12, 13, @on
exec sp_trace_setevent @TraceID, 12, 14, @on
exec sp_trace_setevent @TraceID, 12, 15, @on
exec sp_trace_setevent @TraceID, 12, 16, @on
exec sp_trace_setevent @TraceID, 12, 17, @on
exec sp_trace_setevent @TraceID, 12, 18, @on
exec sp_trace_setevent @TraceID, 12, 31, @on
exec sp_trace_setevent @TraceID, 12, 35, @on
exec sp_trace_setevent @TraceID, 12, 48, @on2.3. 设置跟踪的过滤条件
/**********************************/
/* 3 设置跟踪的过滤条件 */
/**********************************/
/* 比较的类型
值 比较运算符
0 = (等于)
1 <> (不等于)
2 >（大于）
3 < (小于)
4 >= (大于或等于)
5 <= (小于或等于)
6 LIKE
7 不类似于
*/
declare @operator int
set @operator = 0
-- 数据库名
exec sp_trace_setfilter @TraceID, 35, @operator, 6, N'test'2.4. 启动跟踪
/**********************************/
/* 4 启动跟踪 */
/**********************************/
/*状态 说明
0 停止指定的跟踪。
1 启动指定的跟踪。
2 关闭指定的跟踪并从服务器中删除其定义。
*/
exec sp_trace_setstatus @TraceID, 1
select TraceID=@TraceID2.5. 停止跟踪
/**********************************/
/* 5 停止跟踪 */
/**********************************/
/*状态 说明
0 停止指定的跟踪。
1 启动指定的跟踪。
2 关闭指定的跟踪并从服务器中删除其定义。
*/
exec sp_trace_setstatus @TraceID, 0
exec sp_trace_setstatus @TraceID, 22.6. 查询跟踪信息
/**********************************/
/* 附录 查询正在运行的跟踪 */
/**********************************/
/* 参数：
指定 NULL、0 或 DEFAULT 可返回 SQL Server 实例中所有跟踪的信息
返回的表：
列名称 数据类型 说明
traceid int 跟踪的 ID。
property int 跟踪的属性：
1= 跟踪选项。 有关详细信息，请 @options 参阅 (transact-sql)sp_trace_create
2 = 文件名
3 = 最大大小
4 = 停止时间
5 = 当前跟踪状态。 0 = 停止。 1 = 正在运行。
value sql_variant 有关指定跟踪的属性的信息。
*/
SELECT * FROM sys.fn_trace_getinfo(@TraceID)3 收缩日志
SQL Server日志无法收缩的部分解决办法
3.1 查看影响日志无法收缩的原因
/**********************************/
/* 查看影响日志无法收缩的原因 */
/*********************************/
select log_reuse_wait_desc
from sys.databases
where name = '数据库名称'3.2 部分原因的解决方法
- NOTHING
说明：当前有一个或多个可重复使用的虚拟日志文件 (VLF)。
显示NOTHING时，一般情况都是可以收缩。
- LOG_BACKUP
说明：
在截断事务日志前，需要进行日志备份。 （仅限完整恢复模式或大容量日志恢复模式）
解决：
通常情况备份一下事务日志后，log_reuse_wait_desc = NOTHING，就可以收缩到日志文件了。如果无法备份事务日志，就需要先进行完整备份，再备份事务日志。 - REPLICATION
说明：在事务复制过程中，与发布相关的事务仍未传递到分发数据库。 （仅限完整恢复模式）
解决：
将日志中的所有复制的事务都被标记为已分发，执行后log_reuse_wait_desc = NOTHING，就可以收缩到日志文件。
注意
如果手动执行 sp_repldone，则可以使已传送的事务的次序和一致性无效。
MSDN文档： sp_repldone (Transact-SQL)
/**********************************/
/* 将日志中的所有复制的事务都被标记为已分发 */
/*********************************/
EXEC sp_repldone @xactid = NULL, @xact_seqno = NULL, @numtrans = 0, @time = 0, @reset = 1
本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。
