SQLServer性能分析

SQL Server性能分析如何来做

SQLServer 2008-10-15 07:21:07 阅读487 评论2 字号：大中小

当您怀疑计算机硬件是影响SQL Server运行性能的主要原因时，可以通过SQL Server Performance Monitor监视相应硬件的负载，以证实您的猜测并找出系统瓶颈。下文将介绍一些常用的分析对象及其参数。 Memory: Page Faults / sec

如果该值偶尔走高，表明当时有线程竞争内存。如果持续很高，则内存可能是瓶颈。 Process: Working Set

SQL Server的该参数应该非常接近分配给SQL Server的内存值。在SQL Server设定中，如果将\置为0, 则Windows NT会决定SQL Server的工作集的大小。如果将\置为1，则强制工作集大小为SQLServer的分配内存大小。一般情况下，最好不要改变\的缺省值。 Process:%Processor Time

如果该参数值持续超过95%，表明瓶颈是CPU。可以考虑增加一个处理器或换一个更快的处理器。 Processor:%Privileged Time

如果该参数值和\参数值一直很高，表明I/O有问题。可考虑更换更快的硬盘系统。另外设置Tempdb in RAM，减低\，\等措施都会降低该值。 Processor:%User Time

表示耗费CPU的数据库操作，如排序，执行aggregate functions等。如果该值很高，可考虑增加索引，尽量使用简单的表联接，水平分割大表格等方法来降低该值。 Physical Disk:Avg.Disk Queue Length

该值应不超过磁盘数的1.5~2倍。要提高性能，可增加磁盘。 注意：一个Raid Disk实际有多个磁盘。 SQLServer:Cache Hit Ratio

该值越高越好。如果持续低于80%，应考虑增加内存。 注意该参数值是从SQL Server启动后，就一直累加记数，所以运行经过一段时间后，该值将不能反映系统当前值。 读到这里可能有读者会好奇，SQL Server Performance Monitor是什么，如何来打开，样子有是什么样子的？

其实SQL Server Performance Monitor说的就是中文版SQL Server中的性能监视器，这里介绍一种打开SQL Server Performance Monitor的方法，如下：

首先打开SQL 事件探测器，这个通过企业管理器打开或者从开始菜单打开都可以，然后从事件探测器的工具-性能监视器就可以打开SQL Server Performance Monitor了。如下图所示（碍于网易博客本身的原因，可能图片显示比较小，请读者见谅）：

如果本文中提到的参数，在SQL Server Performance Monitor中没有显示，请不要着急，按快捷键Ctrl+I或者最上面一排的“+”号按钮，从弹出的窗口中添加更多的参数或删除我

们认为无用的参数。如下图所示：

检查SQL Server 2005的索引密度和碎片信息(转)

查询数据库中所有表的索引密度和碎片信息，以便为索引的重建和整理提供依据，也可以参考DBCC SHOWCONTIG，通常FRAGMENTATIOIN在30%以上建议重建，否则建议整理

SELECT i.name AS indexname, o.name AS tablename, s.name AS schemaname, f.index_type_desc AS indextype,

f.avg_page_space_used_in_percent AS indexdensity, f.avg_fragmentation_in_percent AS indexfragmentation, f.page_count AS pages

FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, 'SAMPLED') f INNER JOIN sys.objects o ON o.object_id = f.object_id INNER JOIN sys.schemas s ON o.schema_id = s.schema_id

INNER JOIN sys.indexes i ON f.object_id = i.object_id AND f.index_id = i.index_id WHERE page_count > 50 AND f.index_id > 0 ORDER BY o.name, i.index_id

LIMITED | SAMPLED | DETAILED | NULL | DEFAULT

这些模式影响了如何收集碎片数据。LIMITED模式扫描堆所有的页，但对于索引，则只扫描叶级上面的父级别页。SAMPLED收集在堆或索引中1%采样率的数据。DETAILED模式扫描所有页（堆或索引）。DETAILED是执行最慢的，但也是最精确的选项。指定NULL或DEFAULT的效果与LIMITED模式的相同。

SQL Server 查看数据库基本信息

整理了一下如何查看数据库信息，记得有一次，在客户生产环境中，通过 print @@Version 查看，发现没有安装Sql server Sp2 就直接给他安装上sp2，后来发现是Windows 2003的sp1，而不是Sql server ，下面语句就可以查看数据库的版本和补丁信息

select

CONVERT(sysname, SERVERPROPERTY('servername')) N'数据库实例名', SERVERPROPERTY('ProductVersion') N'数据库版本', SERVERPROPERTY ('Edition') N'数据库类型', SERVERPROPERTY('ProductLevel') N'数据库补丁'

sql server性能分析--定时收集系统运行情况

最近要回到公司，暂时离开客户的生产数据库，不能及时得到下列数据库信息:

1,系统运行sql语句的执行情况 2，统计每天全部表的数据变化（数据每天的增量)。

特意写了3个job线程，在晚上业务不繁忙的时候执行，来收集系统运行情况信息，等我回来再来分析sql，同时为系统优化提供参考。

create table tbSql( [语句编译时间] datetime,

[物理读取总次数] int, [每次逻辑读次数] int, [逻辑写入总次数] int, [执行次数] int,

[所用的CPU总时间ms] numeric(30,3), [总花费时间ms] numeric(30,3), [平均时间ms] numeric(30,3), [执行语句] text, [收集时间] datetime, )

insert into tbSql

SELECT creation_time N'语句编译时间'

,total_physical_reads N'物理读取总次数'

,total_logical_reads/execution_count N'每次逻辑读次数'

,total_logical_writes N'逻辑写入总次数' , execution_count N'执行次数'

, total_worker_time/1000 N'所用的CPU总时间ms' , total_elapsed_time/1000 N'总花费时间ms'

, (total_elapsed_time / execution_count)/1000 N'平均时间ms' ,SUBSTRING(st.text, (qs.statement_start_offset/2) + 1, ((CASE statement_end_offset

WHEN -1 THEN DATALENGTH(st.text) ELSE qs.statement_end_offset END

- qs.statement_start_offset)/2) + 1) N'执行语句',

getdate()

FROM sys.dm_exec_query_stats AS qs

CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) st

where SUBSTRING(st.text, (qs.statement_start_offset/2) + 1, ((CASE statement_end_offset

WHEN -1 THEN DATALENGTH(st.text) ELSE qs.statement_end_offset END

- qs.statement_start_offset)/2) + 1) not like 'ttch%' ORDER BY total_elapsed_time / execution_count DESC;

dbcc freeProcCache; --清理执行计划缓存 job每天收集表行数：

select object_name(id),max(rows) from sysindexes group by object_name(id) order by 2 desc

sql server性能分析--执行sql次数和逻辑次数

目前在做一个项目优化时，想通过数据库层分析sql server系统性能，查了一下网上代码，修改了一下标题和DMVs代码，以下代码可以用来分析系统运行一段时间后，那些语句是系统忙的sql语句。做为参考。 另类使用：

一次在分析一个对账功能时，查看系统代码，看了半天，写得太不规范，又不写注释，看不明白。最后用了下面一个小技巧，和大家一起分享：

就是在测量功能时，先以下命令清除sql server的缓存：

dbcc freeProcCache

在点击某个按钮，执行完后，在执行下面语句，就可以知道系统运行什么sql和多少次，其主要慢的语句是那些了。

SELECT creation_time N'语句编译时间'

,last_execution_time N'上次执行时间' ,total_physical_reads N'物理读取总次数'

,total_logical_reads/execution_count N'每次逻辑读次数' ,total_logical_reads N'逻辑读取总次数' ,total_logical_writes N'逻辑写入总次数' , execution_count N'执行次数'

, total_worker_time/1000 N'所用的CPU总时间ms' , total_elapsed_time/1000 N'总花费时间ms'

, (total_elapsed_time / execution_count)/1000 N'平均时间ms' ,SUBSTRING(st.text, (qs.statement_start_offset/2) + 1, ((CASE statement_end_offset

WHEN -1 THEN DATALENGTH(st.text) ELSE qs.statement_end_offset END

- qs.statement_start_offset)/2) + 1) N'执行语句' FROM sys.dm_exec_query_stats AS qs

CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) st

where SUBSTRING(st.text, (qs.statement_start_offset/2) + 1, ((CASE statement_end_offset

WHEN -1 THEN DATALENGTH(st.text) ELSE qs.statement_end_offset END

- qs.statement_start_offset)/2) + 1) not like 'ttch%' ORDER BY total_elapsed_time / execution_count DESC;

sql server性能分析--执行计划重用次数

分析sql执行计划重用次数，查看绑定变量使用情况

select total_elapsed_time / execution_count 平均时间,total_logical_reads/execution_count 逻辑读, usecounts 重用次数,SUBSTRING(d.text, (statement_start_offset/2) + 1, ((CASE statement_end_offset WHEN -1 THEN DATALENGTH(text) ELSE statement_end_offset END

- statement_start_offset)/2) + 1) 语句执行 from sys.dm_exec_cached_plans a cross apply sys.dm_exec_query_plan(a.plan_handle) c ,sys.dm_exec_query_stats b

cross apply sys.dm_exec_sql_text(b.sql_handle) d

where a.plan_handle=b.plan_handle and total_logical_reads/execution_count>4000 ORDER BY total_elapsed_time / execution_count DESC;

sql server性能分析--索引使用效率评估

dmv分析索引的使用情况，包括扫描次数，更新次数，有利于你评估设计的索引使用效率，sql server 2005的确比2000在性能分析上提高很多。

user_seeks : 通过用户查询执行的搜索次数。 个人理解： 此统计索引seek的次数

user_scans: 通过用户查询执行的扫描次数。 个人理解：此统计表扫描的次数，无索引配合 user_lookups: 通过用户查询执行的查找次数。

个人理解：用户通过索引查找，在使用RID或聚集索引查找数据的次数，对于堆表或聚集表数据而言 和索引配合使用次数

user_updates: 通过用户查询执行的更新次数。 个人理解：索引或表的更新次数

--- 使用很少的索引排在最先 declare @dbid int

select @dbid = db_id()

select objectname=object_name(s.object_id), s.object_id, indexname=i.name, i.index_id , user_seeks, user_scans, user_lookups, user_updates from sys.dm_db_index_usage_stats s, sys.indexes i

where database_id = @dbid and objectproperty(s.object_id,'IsUserTable') = 1

and i.object_id = s.object_id and i.index_id = s.index_id

order by (user_seeks + user_scans + user_lookups + user_updates) asc

sql server性能分析--查询死锁和阻塞的sql语句

--查询死锁和阻塞的sql语句 while 1>0 begin

insert into tb(sqlt,ios)

select SUBSTRING(st.text, (qs.statement_start_offset/2) + 1, ((CASE statement_end_offset

WHEN -1 THEN DATALENGTH(st.text) ELSE qs.statement_end_offset END

- qs.statement_start_offset)/2) + 1),a.physical_io from master..sysprocesses a cross apply sys.dm_exec_sql_text(a.sql_handle) b

where (b.text like '%select%' or b.text like '%insert%' or b.text like '%update%' or

b.text like 'Tlete%') and a.blocked=0 and b.text not like '%master..sysprocesses%' and b.text not like '%SERVERPROPERTY%' end

CREATE TABLE [dbo].[tb](

[id] [int] IDENTITY(1,1) NOT NULL, [sqlT] [varchar](8000) NULL, [sdate] [datetime] NULL, [ios] [int] NULL ) ON [PRIMARY]

select * from tb order by ios desc where sqlt like '%update workflowstep set isfinished=1%'

sql server性能分析--检测数据库阻塞语句

create PROCEDURE [dbo].[auto_checkblocks] AS set nocount on

if exists ( select * from master..sysprocesses where blocked <> 0 )

begin

/* show top blockers, but no duplicates */

select '请尝试使用KILL [SPID] 来杀进程'

-- select '请尝试使用SP_LOCK [SPID]来显示锁信息，用OBJECT_NAME(ID)来显示锁对象名称或用sp_who [SPID] 来显示信息' -- select '在使用OBJECT_NAME显示对象名称时请注意对应的db_id' select '以下是引起阻塞的语句'

select distinct

'进程ID' = str( a.spid, 4 ),

'进程ID状态' = convert( char(10), a.status ), '分块进程的进程ID' = str( a.blocked, 2 ),

'工作站名称' = convert( char(10), a.hostname ),

'执行命令的用户' = convert( char(10), suser_name( a.uid ) ), '数据库名' = convert( char(10), db_name(a.dbid ) ), '应用程序名' = convert( char(10), a.program_name ), '正在执行的命令' = convert( char(16), a.cmd ), '累计CPU时间' = str( a.cpu, 7 ), 'IO' = str( a.physical_io, 7 ), '登录名' = a.loginame, '执行语句'=b.text

from master..sysprocesses a

cross apply sys.dm_exec_sql_text(a.sql_handle) b

where spid in ( select blocked from master..sysprocesses ) and blocked = 0 order by str(spid,4)

/* 显示阻塞牺牲品 */

select '以下是被阻塞的等待执行的语句' select

'进程ID[SPID]' = str( a.spid, 4 ),

'进程ID状态' = convert( char(10), a.status ), '分块进程的进程ID' = str( a.blocked, 2 ),

'工作站名称' = convert( char(10), a.hostname ),

'执行命令的用户' = convert( char(10), suser_name( a.uid ) ), '数据库名' = convert( char(10), db_name( a.dbid ) ), '应用程序名' = convert( char(10), a.program_name ), '正在执行的命令' = convert( char(16), a.cmd ), '累计CPU时间' = str( a.cpu, 7 ), 'IO' = str( a.physical_io, 7 ), '登录名' = a.loginame, '执行语句'=b.text

from master..sysprocesses a

cross apply sys.dm_exec_sql_text(a.sql_handle) b where blocked <> 0 order by spid end

else begin

select '恭喜！当前没有阻塞，当前的进程信息如下.', convert (char(24),getdate(),13) select

'进程ID' = str( spid, 4 ),

'进程ID状态' = convert( char(10), status ), '分块进程的进程ID' = str( blocked, 2 ),

'工作站名称' = convert( char(10), hostname ),

'执行命令的用户' = convert( char(10), suser_name( uid ) ), '数据库名' = convert( char(10), db_name( dbid ) ), '应用程序名' = convert( char(10), program_name ), '正在执行的命令' = convert( char(16), cmd ), '累计CPU时间' = str( cpu, 7 ), 'IO' = str( physical_io, 7 ), '登录名' = loginame from master..sysprocesses where blocked = 0 order by spid end

return

1、使用forfiles命令删除陈旧的数据库备份文件

从Windows Server 2003开始forfiles命令就是Windows的一个自带命令行工具，它主要用于对文件的批处理，利用SQL Server代理作业，加上这个工具，可以删除SQL Server陈旧的数据库备份文件，以往这个工作一般都依赖于SQL Server维护计划、

xp_cmdshell扩展存储过程，或VBScript对象。下面的forfiles命令删除了E:\\sqlbackup文件夹下两天及两天以前的所有.bak文件。 forfiles /p \

关于forfiles的具体用法请查看其命令行帮助说明，或参考微软的官方文档：

http://technet2.microsoft.com/WindowsServer/en/Library/9660fea1-65c7-48cf-b466-204ba159381e1033.mspx。

2、使用ALTER USER WITH LOGIN修复孤儿登录

从SQL Server 2005 SP2开始，T-SQL的ALTER USER命令就包含了WITH LOGIN子句，这个子句通过修改数据库用户的SID为服务器登录的安全标识符修复孤儿登录，它可以同时修复Windows和SQL Server登录的功能。从另一个服务器恢复数据库，登录是独立的创建的(不是从其它服务器复制过来的)，这个时候创建的就是孤儿用户。关于ALTER USER WITH LOGIN的详细情况，请参考Laurentiu Cristofor的博客文章\：SP2中的一些新特性\，或者参考SQL Server在线电子书中关于ALTER USER命令的页面。

3、使用sp_addsrvrolemember将自己提升为sysadmin角色

在SQL Server 2005中，默认情况下，Windows内置的Administrators组没有授予它sysadmin角色，作为Windows系统管理员，你可以讲SQL Server启动到单用户模式(即维护模式)，然后在Sqlcmd命令行环境中运行sp_addsrvrolemember系统存储过程，将你的Windows登录用户添加到sysadmin角色。更多信息请参考Raul Garcia的博客文章\灾难恢复：SQL Server 2005中SA账号密码丢失时该怎么办\

4、使用PortQryUI排除连接故障

为了解决TCP/IP连接问题，可以选择微软提供的PortQryUI工具，这个工具和PortQry一样好用，其实它就是在PortQry外面套了一层外衣，它内置了一些服务，如一组端口扫描，包括UDP 1434和TCP 1433端口。因此，要检查这些端口的话，只需要输入目标SQL Server实例的IP地址或完全限定域名(FQDN)即可，如果检查到端口可访问，PortQryUI就会告诉你端口正在监听，否则，它会告诉你端口可能被过滤掉了，或者没有监听。可以从

http://www.microsoft.com/downloads/details.aspx?FamilyID=8355e537-1ea6-4569-aabb-f248f4bd91d0&displaylang=en下载这个好用的工具。

5、在大型数据库上运行DBCC CHECKDB时采用不同的策略

由于数据库越变越大，使用诸如T-SQL的DBCC CHECKDB命令行工具检查数据库的完整性将会耗费很长时间。如果执行DBCC CHECKDB时超出了分配的维护窗口周期该怎么办?Paul S. Randal在他的博客中提供了几种方法(博客链接：

http://sqlskills.com/BLOGS/PAUL/post/CHECKDB-From-Every-Angle-Consistency-Checking-Options-for-a-VLDB.aspx)，其中一个解决办法就是一台独立的服务器上使用备份还原数据库，然后再在这个服务器上运行DBCC CHECKDB，另一个解决办法就是设置数据库的页面验证选项(从CHECKSUM改为PHYSICAL_ONLY)，然后再运行DBCC CHECKDB，这样运行DBCC CHECKDB的时间就大大减小了，但它仍然会影响I/O子系统和页面腐化。

6、使用导入包选项将SQL Server集成服务(SSIS)部署到msdb数据库上

SSIS有多种部署方法，最简单的方法是将SSIS部署到SQL Server的msdb数据库下，在In SQL Server Management Studio (SSMS)中，连接到集成服务，在存储包下，在MSDB文件夹上点击右键，然后选择导入包，在弹出的对话框中，选择SSIS包的当前位置和名字，根据服务器存储和访问控制角色设置保护级别，然后点击确定按钮。手动拷贝与SSIS包相关的所有XML配置文件到目标服务器，如果这个包使用SQL Server配置，在导入之前，可能需要使用正确的服务器名更新连接SQL Server的连接字符串。详细信息请参考SQL Server在线电子书中的\如何使用集成服务服务导入包\页面。 7、使用SQL Server 2008的本地服务器组和中央管理服务器同时查询多个服务器

在SQL Server 2008中，你可以使用本地服务器组快速连接到那些你经常管理的服务器，也可以使用中央管理服务器来存储服务器的注册元数据，这样团队成员就可以使用相同的注册信息，可以包括SQL Server 2008、SQL Server 2005和SQL Server 2000服务器。在SSMS中，可以查询本地服务器组或中央管理服务器中的服务器，并可以合并查询结果，进入SSMS已注册服务器窗口，在本地服务器组或中央管理服务器上点击右键，选择新建查询，在查询编辑器中，输入T-SQL命令，点击执行，将会得到一个包含两列的结果集，第一列包括每个服务器的名字，第二列就是从该服务器的命令输出内容。详细信息请参考SQL Server在线电子书中的\如何对多个服务器同时执行语句(SQL Server管理控制台)\。

本文来源：https://www.bwwdw.com/article/xttg.html