SQL Performance利用效能监视器了解SQL Server运作状况

作者:五餅二魚工作室  发布日期:2012-12-08 13:24:03

对 SQL Server 的 DBA 来说,利用效能监视器了解 SQL Server 运作状况应该不是一件新奇的事情,甚至很多的研讨会和资料上来说都有说明要查看那些信息,对大家来说应该都不陌生才对。而最近刚好遇到一些效能调教的案件,但因为不是很方便联机进行处理,因此把相关处理步骤整理一下,让一些在前线的同事们可以很快地来进行第一手的判断,加速问题的处理。因此我将效能监视器上的相关设定方式和查看方式给整理一下,这样以后有需要的朋友就请他按图施工啰。

设定


一般来说效能监视器有两种设定的方式,一种是透过启动效能监视器透过 GUI 的方式进行设定,而另一种则是透过命令列,利用 Logman 进行设定。两种能达到的方式都相同,利用 GUI 虽然看起来动作比较多,但可以利用建立模板 ( Template ) 的方式将设定储存起来,这样下次要设定的时候就不用一个一个挑选,用汇入的方式就可以了;而透过 Logman 也是很不错的方法,只要将相关指令存成批次文件,那下次需要到不同环境或是不同监控数据的时候,则只要修改批次档后就可以快速使用了。首先我们先确定要收集的项目,这里我参考一些之前研讨会和相关的网站数据,列出一些比较常用的收集项目 :

 


 

因此可以在启动效能监视器之后,选择「数据收集器集合工具」的「使用者定义」,在上面按下鼠标右键选择「新增」→「数据收集集合工具」,看是要汇入之前已经定义好的模板还是手动建立

 


 

如果不嫌麻烦可以按造前面的表格将所需要的计数器一个一个给加入,或者是将以下的设定存成 XML 档案,当成模板给汇入也可以

 

<?xml version="1.0" encoding="UTF-16"?>
<DataCollectorSet>
    <Status>0</Status>
    <Duration>0</Duration>
    <Description>
    </Description>
    <DescriptionUnresolved>
    </DescriptionUnresolved>
    <DisplayName>
    </DisplayName>
    <DisplayNameUnresolved>
    </DisplayNameUnresolved>
    <SchedulesEnabled>-1</SchedulesEnabled>
    <LatestOutputLocation>
    </LatestOutputLocation>
    <Name>TEST</Name>
    <OutputLocation>C:\PerfLogs\Admin\SQLServer</OutputLocation>
    <RootPath>C:\PerfLogs\Admin</RootPath>
    <Segment>0</Segment>
    <SegmentMaxDuration>0</SegmentMaxDuration>
    <SegmentMaxSize>0</SegmentMaxSize>
    <SerialNumber>1</SerialNumber>
    <Server>
    </Server>
    <Subdirectory>
    </Subdirectory>
    <SubdirectoryFormat>3</SubdirectoryFormat>
    <SubdirectoryFormatPattern>yyyyMMdd\-NNNNNN</SubdirectoryFormatPattern>
    <Task>
    </Task>
    <TaskRunAsSelf>0</TaskRunAsSelf>
    <TaskArguments>
    </TaskArguments>
    <TaskUserTextArguments>
    </TaskUserTextArguments>
    <UserAccount>SYSTEM</UserAccount>
    <Security>O:BAG:S-1-5-21-687209553-3516051733-1982557323-513D:AI(A;;FA;;;SY)(A;;FA;;;BA)(A;;FR;;;LU)(A;;0x1301ff;;;S-1-5-80-2661322625-712705077-2999183737-3043590567-590698655)(A;ID;FA;;;SY)(A;ID;FA;;;BA)(A;ID;0x1200ab;;;LU)(A;ID;FR;;;AU)(A;ID;FR;;;LS)(A;ID;FR;;;NS)</Security>
    <StopOnCompletion>0</StopOnCompletion>
    <PerformanceCounterDataCollector>
        <DataCollectorType>0</DataCollectorType>
        <Name>DataCollector01</Name>
        <FileName>Performance</FileName>
        <FileNameFormat>1</FileNameFormat>
        <FileNameFormatPattern>MMdd</FileNameFormatPattern>
        <LogAppend>0</LogAppend>
        <LogCircular>0</LogCircular>
        <LogOverwrite>0</LogOverwrite>
        <LatestOutputLocation>
        </LatestOutputLocation>
        <DataSourceName>
        </DataSourceName>
        <SampleInterval>30</SampleInterval>
        <SegmentMaxRecords>0</SegmentMaxRecords>
        <LogFileFormat>3</LogFileFormat>
        <Counter>\Memory\Available MBytes</Counter>
        <Counter>\Memory\Page Reads/sec</Counter>
        <Counter>\PhysicalDisk(_Total)\Avg. Disk Read Queue Length</Counter>
        <Counter>\PhysicalDisk(_Total)\Avg. Disk Write Queue Length</Counter>
        <Counter>\Processor Information(_Total)\% Processor Time</Counter>
        <Counter>\SQLServer:Access Methods\Page Splits/sec</Counter>
        <Counter>\SQLServer:Buffer Manager\Buffer cache hit ratio</Counter>
        <Counter>\SQLServer:General Statistics\User Connections</Counter>
        <Counter>\SQLServer:Memory Manager\Target Server Memory (KB)</Counter>
        <Counter>\SQLServer:Memory Manager\Total Server Memory (KB)</Counter>
        <Counter>\System\Processor Queue Length</Counter>
        <CounterDisplayName>\Memory\Available MBytes</CounterDisplayName>
        <CounterDisplayName>\Memory\Page Reads/sec</CounterDisplayName>
        <CounterDisplayName>\PhysicalDisk(_Total)\Avg. Disk Read Queue Length</CounterDisplayName>
        <CounterDisplayName>\PhysicalDisk(_Total)\Avg. Disk Write Queue Length</CounterDisplayName>
        <CounterDisplayName>\Processor Information(_Total)\% Processor Time</CounterDisplayName>
        <CounterDisplayName>\SQLServer:Access Methods\Page Splits/sec</CounterDisplayName>
        <CounterDisplayName>\SQLServer:Buffer Manager\Buffer cache hit ratio</CounterDisplayName>
        <CounterDisplayName>\SQLServer:General Statistics\User Connections</CounterDisplayName>
        <CounterDisplayName>\SQLServer:Memory Manager\Target Server Memory (KB)</CounterDisplayName>
        <CounterDisplayName>\SQLServer:Memory Manager\Total Server Memory (KB)</CounterDisplayName>
        <CounterDisplayName>\System\Processor Queue Length</CounterDisplayName>
    </PerformanceCounterDataCollector>
    <DataManager>
        <Enabled>0</Enabled>
        <CheckBeforeRunning>0</CheckBeforeRunning>
        <MinFreeDisk>0</MinFreeDisk>
        <MaxSize>0</MaxSize>
        <MaxFolderCount>0</MaxFolderCount>
        <ResourcePolicy>0</ResourcePolicy>
        <ReportFileName>report.html</ReportFileName>
        <RuleTargetFileName>report.xml</RuleTargetFileName>
        <EventsFileName>
        </EventsFileName>
    </DataManager>
</DataCollectorSet>


 

在这里我选择用模板汇入之前的设定后,就可以很快的完成。设定完后比较要注意的是纪录格式,可以在「效能计数器」上面按下鼠标右键,选择「内容」来做调整,预设是采用二进制格式,也可以调整成为逗点分隔将档案存成 CSV 格式,这样后续可以用 Excel 汇入制作图表;亦或者是将这些资料存放到 SQL Server 上面,后续可以配合 SQL 指令或者是 Report Service 来查看数据。

 


 

LOGMAN
如果需要利用指令来处理的话,那么 Logman 跟 GUI 设定方式一样提供给我们两种方式,可以直接利用 Logman 去建立相关的计数器,或者是利用汇入 XML 模板的方式来做建立。此时我先启动一个「以系统管理员身分执行」的命令提示字符环静,使用指令: www.it165.net

logman import "SQL Performance" -xml SQL_Performance_Templete.xml
 

就可以将之前所产生的模板给汇入。但如果要用 Logman 建立的话,可以参考模板文件里面的 Counter 信息,取出来放在文本文件里面,这样建立的时候比较方便,如果想知道全部有哪些计数器可以使用的话,可以透过 "typeperf –q" 的方式列出来,在这里我先整理出此次要用的计数器信息如下:

\Memory\Available MBytes
\Memory\Page Reads/sec
\PhysicalDisk(_Total)\Avg. Disk Read Queue Length
\PhysicalDisk(_Total)\Avg. Disk Write Queue Length
\Processor Information(_Total)\% Processor Time
\SQLServer:Access Methods\Page Splits/sec
\SQLServer:Buffer Manager\Buffer cache hit ratio
\SQLServer:General Statistics\User Connections
\SQLServer:Memory Manager\Target Server Memory (KB)
\SQLServer:Memory Manager\Total Server Memory (KB)
 

我将这些资料存成 SQL_Performance_List.TXT 的档案,因此接下来就可以利用指令来建立了

logman create counter "Logman Test" -s %computername% -cf SQL_Performance_List.txt
 

因为模板档案里面设定的信息比较多,因此当使用 create counter 的参数的时候,还可以另外再多加上其它的参数去指定档案大小,起始时间,档案格式等信息。这个部分可以参考 MSDN 网站上的说明。

报表制作与分析
透过效能监视器的设定,我们可以很方便的取得相关检测资料,但那么多的资料要怎么去判断哪些地方有问题呢 ? 以往笔者自己的做法多半是把数据储存为 CSV 档案,然后使用 Excel 来制作图表,然而在寻找相关数据的时候,正巧看到一套不错的软件 - Performance Analysis of Logs (PAL) Tool ( 网址 )。他主要是透过 PowerShell , LogParse 和 Chart Control 组件,帮你将透过效能监视器所收集到的数据转换成为图表的样式,并且加上建议处理,就算您不知道要用效能监视器去收集那些信息,他也可以产生出效能监视器的 Template 档案,让你可以很快速的就设定好,算是非常方便的工具。在网站上有区分 32 位和 64 位的版本,看您要在甚么样的环境去执行,挑选回来安装就好。

执行的画面

 


 

如果要进行分析,可以在「Counter Log」这页中去指定你效能分析的文件名称

 


 

如果不知道要收集那些资料,可以透过「Threshold File」这页中去找相关的模板来使用,预计就有 Exange Server , SQL Server , Project Server , Hyper-V 等的样板可以来使用了,选择好之后可以透过「Export to Perfmon template file…」的按钮去指定样板档案汇出的名称,就可以将该档案透过前面的设定过程或者是 Logman,在要收集信息的主机上来进行数据收集了。

 


 

如果要进行报表制作,在目前这个版本中,可以在「Execute」这页中去指定是否要同时 Multi-Thread 来进行处理,以我个人的经验,一个小时大约 12MB 的 Log 档案,差不多 10mins 就可以完成报表制作了。

 


 

以下是我节录部分的 HTML 图表,以这个数据来看就可以很容易数据说这台数据库主机的硬盘读取有蛮大的瓶颈,因此如果有机会在这个部分做改善,应该对效能的提升会有不少的帮助。

 


 

 

后记

不论是透过效能监视器或者是 PAL,都只是提供数据协助 DBA 方便来查出问题,并不能真正解决问题,虽然 SQL Server 安装之后并不需要有太多的调整就可以有蛮不错的效能,但如果更能善用这些便利的工具,相信会让您在管理上更能得心应手。

 

Tag标签: SQL   Performance   效能监视器  
  • 专题推荐

About IT165 - 广告服务 - 隐私声明 - 版权申明 - 免责条款 - 网站地图 - 网友投稿 - 联系方式
本站内容来自于互联网,仅供用于网络技术学习,学习中请遵循相关法律法规