SQLServer 监视：多维数据分析和性能优化的关键步骤。-猿码集

1. 监视SQLServer的重要性

在现代信息技术中，SQLServer是作为关系型数据库管理系统的高级技术应用，充分保证了数据的存储和管理效率。然而，优化SQLServer以提高性能也变得越来越重要。因此，SQLServer监视应成为任何企业和组织的必备环节。

监控SQLServer的性能和状态，可以帮助管理员和运维团队确认：

服务器是否正常运转

性能问题的源头是什么

如何优化SQLServer及其相关程序的性能

以下是SQLServer监视的关键步骤。

2. 监视索引

索引对于SQLServer的性能非常关键。索引的优化路径通常包括以下几个部分：

2.1 确定索引性能问题

在进行索引优化之前，务必确认哪些索引对SQLServer的性能有负面影响。可以使用SQLServer提供的动态管理视图，如sys.dm_db_index_operational_stats来跟踪索引的使用情况。


SELECT
  OBJECT_NAME(i.OBJECT_ID) AS TableName,
  i.name AS IndexName,
  i.index_id,
  ios.user_updates,
  ios.user_seeks,
  ios.user_scans,
  ios.user_lookups
FROM sys.indexes i
INNER JOIN sys.dm_db_index_operational_stats(DB_ID(),NULL,NULL,NULL) ios
ON ios.OBJECT_ID = i.OBJECT_ID
AND ios.index_id = i.index_id
WHERE i.name IS NOT NULL
AND OBJECTPROPERTY(i.OBJECT_ID,'IsUserTable') = 1

此脚本返回包含每个表的每个索引的统计信息，包括索引名称、索引ID和使用情况。由此可以了解哪些索引需要进行优化。

2.2 确定索引的完整性

SQLServer通过执行聚簇索引和非聚簇索引来管理数据的方式是相似的。在对表进行修改（例如增加、删除或修改行）时，SQLServer会对表中的每个索引执行相同的操作。

如果表中某个索引的完整性受到破坏，就可能会引起诸如索引扫描和缺少数据的问题。

可以使用以下SQL查询语句来扫描表中的每个索引，以确认索引的完整性是否受到破坏。


DBCC CHECKDB ('AdventureWorks2012')
GO

3. 监视存储过程

存储过程是一组SQL语句的集合，可用于对数据进行操作。由于存储过程可以提高性能并减少网络流量，因此许多应用程序都使用它们来操作SQLServer的数据。

3.1 确认存储过程的完整性

检查存储过程的完整性是否受到破坏，使用以下SQLServer命令。


DBCC CHECKDB ('AdventureWorks2012')
GO

3.2 监视存储过程执行计划的缓存

存储过程执行计划的缓存包含SQLServer用于评估和优化查询性能的信息。能够监视存储过程执行计划的缓存，可以了解哪些存储过程正在处理大量数据或哪些存储过程需要被修复和优化。

以下代码返回按照最多执行的次数降序排列并缓存大小超过200K字节的存储过程


SELECT TOP 50
    cp.usecounts,
    cp.exactaggregates,
    cp.loops,
    cp.size_in_bytes,
    p.name,
    p.object_id,
    cp.cacheobjtype,
    plan_handle
FROM sys.dm_exec_cached_plans AS cp
    CROSS APPLY sys.dm_exec_query_plan(cp.plan_handle) AS qp
    INNER JOIN sys.procedures AS p
        ON qp.objectid = p.object_id
WHERE cp.objtype = 'proc'
AND cp.size_in_bytes > 200000
ORDER BY usecounts DESC

4. 监视活动连接和查询

在SQLServer管理中，活动的连接和查询是SQLServer性能和健康的重要指标。通过监视活动连接和查询，可以对SQLServer的工作负载和性能进行基本的测试和分析。

4.1 确认运行中的查询

通过以下SQL查询语句，可以确定当前正在运行的查询。


SELECT sqltext.TEXT,
    req.session_id,
    req.status,
    req.command,
    req.cpu_time,
    req.total_elapsed_time
FROM sys.dm_exec_requests req
CROSS APPLY sys.dm_exec_sql_text(req.sql_handle) AS sqltext
GO

4.2 监视活动连接

以下SQL脚本可以用于监视所有活动连接到SQLServer实例的客户端。


SELECT 
    DB_NAME(dbid) as [database],
    COUNT(dbid) as [connections],
    loginame as [login]
FROM 
    sys.sysprocesses
WHERE 
    dbid > 0
GROUP BY 
    dbid, loginame
ORDER BY 
    connections DESC
GO

5. 监测磁盘I/O

磁盘I/O是SQLServer处理数据时的重要资源，其劣化会严重影响SQLServer的性能和健康状况。

5.1 确认I/O性能问题

确认有磁盘I/O性能问题最好的方法是使用SQLServer动态管理视图来查看I/O的性能统计信息。


SELECT *
FROM sys.dm_io_virtual_file_stats(DB_ID(), NULL)
GO

5.2 监视磁盘空间

使用以下代码可以确定SQLServer磁盘空间的使用情况。


USE master;
GO
EXEC xp_fixeddrives;
GO

还可以查询文件大小及其位置。


SELECT 
    name,
    physical_name,
    size / 128 / 1024.0 SizeGB
FROM 
    sys.master_files
WHERE 
    state = 0
ORDER BY 
    size DESC
GO

总结

对于SQLServer管理，监视是至关重要的。通过SQLServer监视，可以减少运维团队的工作负担并提高企业数据的完整性、可用性和安全性。

重点监视索引、存储过程和活动连接及查询，以确保SQLServer的健康状态。同时，监视磁盘I/O和磁盘空间也是必不可少的。

SQLServer 监视：多维数据分析和性能优化的关键步骤。