SQL Server 2005/2008 性能监控（待续）

妖狐艹你老母 2022-07-12 10:07 168阅读 0赞

\-- 查看性能记数器

SELECT \* FROM sys.dm\_os\_performance\_counters

\-- 执行过的线程所遇到的所有等待(不是当前正在运行的线程, 为自上次重置统计信息或启动服务器以来累积的数据),可分析靠前的几个等待较高的事件。

select\* from sys.dm\_os\_wait\_stats order by wait\_time\_ms desc

该动态视图的细节，请查看帮助文档.

\-- 重置该动态视图

DBCC SQLPERF ('sys.dm\_os\_wait\_stats', CLEAR);

\-- 正在等待某些资源的任务的等待队列

select\* from sys.dm\_os\_waiting\_tasks order by wait\_duration\_ms desc

内存使用：   
查看当前由 SQL Server 分配的内存对象(KB): select sum((page\_size\_in\_bytes/1024)\*max\_pages\_allocated\_count) from sys.dm\_os\_memory\_objects;  
查看系统内存当前信息: select \* from sys.dm\_os\_sys\_memory (这个动态视图只在sql 2008中才有)  
select  
cpu\_count,  
hyperthread\_ratio,  
scheduler\_count,  
physical\_memory\_in\_bytes / 1024 / 1024 as physical\_memory\_mb,  
virtual\_memory\_in\_bytes / 1024 / 1024 as virtual\_memory\_mb,  
bpool\_committed \* 8 / 1024 as bpool\_committed\_mb,  
bpool\_commit\_target \* 8 / 1024 as bpool\_target\_mb,  
bpool\_visible \* 8 / 1024 as bpool\_visible\_mb  
from sys.dm\_os\_sys\_info

限制SQL Server使用的最小,最大内存(MB)：  
sp\_configure 'show advanced options', 1;  
GO  
RECONFIGURE;  
GO  
sp\_configure 'min server memory', 300;  
GO  
sp\_configure 'max server memory', 850;  
GO  
RECONFIGURE;  
GO

CPU使用情况:

SELECTTOP 50

total\_worker\_time/execution\_count AS'每次执行占用CPU(微秒)',

execution\_count       as'执行次数',

total\_worker\_time     as'总共占用CPU(微秒)',

creation\_time         as'创建时间',

last\_execution\_time   as'最后执行时间',

min\_worker\_time       as'最低每次占用CPU',

max\_worker\_time       as'最高每次占用cpu',

total\_physical\_reads  as'总共io物理读取次数',

total\_logical\_reads   as'总共逻辑读取次数',

total\_logical\_writes  as'总共逻辑写次数',

total\_elapsed\_time    as'完成此计划的执行所占用的总时间(微秒)',

(SELECTSUBSTRING(text,statement\_start\_offset/2,(CASEWHEN statement\_end\_offset =\-1 thenLEN(CONVERT(nvarchar(max),text))\* 2 ELSE statement\_end\_offset end\-statement\_start\_offset)/2)FROM sys.dm\_exec\_sql\_text(sql\_handle))AS'SQL内容'

FROMsys.dm\_exec\_query\_stats

ORDERBY 1 DESC

\--下面的查询显示SQL 等待分析和前10 个等待的资源

selecttop 10 \*

fromsys.dm\_os\_wait\_stats

wherewait\_typenotin('CLR\_SEMAPHORE','LAZYWRITER\_SLEEP','RESOURCE\_QUEUE','SLEEP\_TASK','SLEEP\_SYSTEMTASK','WAITFOR')

orderbywait\_time\_msdesc;

SQL Server中的活动会话数:  
exec   sp\_who   'active'  
print @@rowcount

SQL Server等待情况  
select \* from sys.dm\_os\_waiting\_tasks ; --当前等待事件  
select \* from sys.dm\_os\_wait\_stats  --历史等待次数,是sqlserver启动后的累计值,需使用下一条语句清空  
DBCC SQLPERF ('sys.dm\_os\_wait\_stats', CLEAR);

找出进程阻塞:  
运行下面的查询可确定阻塞的会话  
select blocking\_session\_id, wait\_duration\_ms, session\_id from  
sys.dm\_os\_waiting\_tasks  
where blocking\_session\_id is not null

spid 正在阻塞另一个 spid，可在数据库中创建以下存储过程，然后执行该存储过程。此存储过程会报告此阻塞情况。键入 sp\_who 可找出 @spid；@spid 是可选参数。

create proc dbo.sp\_block (@spid bigint=NULL)  
as  
select  
    t1.resource\_type,  
    'database'=db\_name(resource\_database\_id),  
    'blk object' = t1.resource\_associated\_entity\_id,  
    t1.request\_mode,  
    t1.request\_session\_id,  
    t2.blocking\_session\_id     
from  
    sys.dm\_tran\_locks as t1,  
    sys.dm\_os\_waiting\_tasks as t2  
where  
    t1.lock\_owner\_address = t2.resource\_address and  
    t1.request\_session\_id = isnull(@spid,t1.request\_session\_id)

以下是使用此存储过程的示例。  
exec sp\_block  
exec sp\_block @spid = 7

select sum((page\_size\_in\_bytes/1024)\*max\_pages\_allocated\_count) from sys.dm\_os\_memory\_objects;  
select \* from sys.dm\_os\_sys\_info;  
select \* from sys.dm\_os\_performance\_counters

A. 获取有关按平均 CPU 时间排在最前面的五个查询的信息  
以下示例返回前五个查询的 SQL 语句文本和平均 CPU 时间。

复制代码  
SELECT TOP 5 total\_worker\_time/execution\_count AS \[Avg CPU Time\],  
    SUBSTRING(st.text, (qs.statement\_start\_offset/2)+1,  
        ((CASE qs.statement\_end\_offset  
          WHEN -1 THEN DATALENGTH(st.text)  
         ELSE qs.statement\_end\_offset  
         END - qs.statement\_start\_offset)/2) + 1) AS statement\_text  
FROM sys.dm\_exec\_query\_stats AS qs  
CROSS APPLY sys.dm\_exec\_sql\_text(qs.sql\_handle) AS st  
ORDER BY total\_worker\_time/execution\_count DESC;

B. 提供批处理执行统计信息  
以下示例返回按批执行的 SQL 查询的文本，并提供有关它们的统计信息。

复制代码  
SELECT s2.dbid,  
    s1.sql\_handle,   
    (SELECT TOP 1 SUBSTRING(s2.text,statement\_start\_offset / 2+1 ,  
      ( (CASE WHEN statement\_end\_offset = -1  
         THEN (LEN(CONVERT(nvarchar(max),s2.text)) \* 2)  
         ELSE statement\_end\_offset END)  - statement\_start\_offset) / 2+1))  AS sql\_statement,  
    execution\_count,  
    plan\_generation\_num,  
    last\_execution\_time,    
    total\_worker\_time,  
    last\_worker\_time,  
    min\_worker\_time,  
    max\_worker\_time,  
    total\_physical\_reads,  
    last\_physical\_reads,  
    min\_physical\_reads,   
    max\_physical\_reads,   
    total\_logical\_writes,  
    last\_logical\_writes,  
    min\_logical\_writes,  
    max\_logical\_writes   
FROM sys.dm\_exec\_query\_stats AS s1  
CROSS APPLY sys.dm\_exec\_sql\_text(sql\_handle) AS s2   
WHERE s2.objectid is null  
ORDER BY s1.sql\_handle, s1.statement\_start\_offset, s1.statement\_end\_offset;

\-- 数据库文件性能,文件io性能统计(必须重启sql server服务,才能清零该计数器)

select DB\_NAME(database\_id) DB\_NAME, file\_id,io\_stall\_read\_ms ,num\_of\_reads

,cast(io\_stall\_read\_ms/(1.0\+num\_of\_reads) as numeric(10,1)) as 'avg\_read\_stall\_ms'

,io\_stall\_write\_ms,num\_of\_writes

,cast(io\_stall\_write\_ms/(1.0\+num\_of\_writes) as numeric(10,1)) as 'avg\_write\_stall\_ms'

,io\_stall\_read\_ms \+ io\_stall\_write\_ms as io\_stalls

,num\_of\_reads \+ num\_of\_writes as total\_io

,cast((io\_stall\_read\_ms\+io\_stall\_write\_ms)/(1.0\+num\_of\_reads \+ num\_of\_writes) as numeric(10,1)) as 'avg\_io\_stall\_ms'

from sys.dm\_io\_virtual\_file\_stats(null,null)

orderby avg\_io\_stall\_ms desc;

<table style="width: 1120px; border-collapse: collapse;">                                  
 <tbody> 
  <tr style="height: 54pt; mso-height-source: userset;"> 
   <td style="border-bottom: windowtext 0.5pt solid; border-left: windowtext 0.5pt solid; background-color: #d8d8d8; width: 86pt; height: 54pt; border-top: windowtext 0.5pt solid; border-right: #ebe9ed;"><span style="font-family: 宋体; color: #c00000; font-size: small;">数据库名</span></td> 
   <td style="border-bottom: windowtext 0.5pt solid; border-left: #ebe9ed; background-color: #d8d8d8; width: 54pt; border-top: windowtext 0.5pt solid; border-right: #ebe9ed;"><span style="font-family: 宋体; color: #c00000; font-size: small;">文件ID</span></td> 
   <td style="border-bottom: windowtext 0.5pt solid; border-left: #ebe9ed; background-color: #d8d8d8; width: 90pt; border-top: windowtext 0.5pt solid; border-right: #ebe9ed;"> 
    <div> 
     <span style="color: #c00000; font-size: small;">用户等待文件中发出读取所用的总时间（毫秒）</span> 
    </div> </td> 
   <td style="border-bottom: windowtext 0.5pt solid; border-left: #ebe9ed; background-color: #d8d8d8; width: 70pt; border-top: windowtext 0.5pt solid; border-right: #ebe9ed;"><span style="font-family: 宋体; color: #c00000; font-size: small;">对文件发出的读取次数</span></td> 
   <td style="border-bottom: windowtext 0.5pt solid; border-left: #ebe9ed; background-color: #d8d8d8; width: 80pt; border-top: windowtext 0.5pt solid; border-right: #ebe9ed;"><span style="font-family: 宋体; color: #c00000; font-size: small;">平均读文件等待(毫秒)</span></td> 
   <td style="border-bottom: windowtext 0.5pt solid; border-left: #ebe9ed; background-color: #d8d8d8; width: 89pt; border-top: windowtext 0.5pt solid; border-right: #ebe9ed;"><span style="font-family: 宋体; color: #c00000; font-size: small;">用户等待在该文件中完成写入所用的总时间（毫秒）</span></td> 
   <td style="border-bottom: windowtext 0.5pt solid; border-left: #ebe9ed; background-color: #d8d8d8; width: 85pt; border-top: windowtext 0.5pt solid; border-right: #ebe9ed;"><span style="font-family: 宋体; color: #c00000; font-size: small;">在该文件中写入的次数</span></td> 
   <td style="border-bottom: windowtext 0.5pt solid; border-left: #ebe9ed; background-color: #d8d8d8; width: 68pt; border-top: windowtext 0.5pt solid; border-right: #ebe9ed;"><span style="font-family: 宋体; color: #c00000; font-size: small;">平均写文件等待毫秒</span></td> 
   <td style="border-bottom: windowtext 0.5pt solid; border-left: #ebe9ed; background-color: #d8d8d8; width: 90pt; border-top: windowtext 0.5pt solid; border-right: #ebe9ed;"><span style="font-family: 宋体; color: #c00000; font-size: small;">(读+写)等待毫秒</span></td> 
   <td style="border-bottom: windowtext 0.5pt solid; border-left: #ebe9ed; background-color: #d8d8d8; width: 77pt; border-top: windowtext 0.5pt solid; border-right: #ebe9ed;"><span style="font-family: 宋体; color: #c00000; font-size: small;">(读+写)次数</span></td> 
   <td style="border-bottom: windowtext 0.5pt solid; border-left: #ebe9ed; background-color: #d8d8d8; width: 54pt; border-top: windowtext 0.5pt solid; border-right: windowtext 0.5pt solid;"><span style="font-family: 宋体; color: #c00000; font-size: small;">(读+写)平均等待毫秒</span></td> 
  </tr> 
 </tbody> 
</table>

\-- 查看分区表money，各个分区的行数和边界值.

selectpartition = $partition.分区函数名(userid)

,rows      = count(\*)

,minval    = min(userid)

,maxval    = max(userid)

from dbo.money with(nolock)

group by $partition.分区函数名(userid)

orderby partition;