当前位置：Gxlcms > 数据库问题 > SQLdiag-配置文件

SQLdiag-配置文件

时间：2021-07-01 10:21:17 帮助过：12人阅读

\Tools\BinnSQLdiag /I F:\TroubleShooting\SQLdiag\input\SD_Detailed.XML /O F:\TroubleShooting\SQLdiag\LocalOutput /E +00:05:00

生成的文件如下：
技术分享
SQLDIAG：收集PerfMon数据，对应PerfmonCollector元素
WORK_SQLDIAG_SQL08R2_sp_trace：收集的跟踪数据，对应ProfilerCollector元素
其他文件的说明见上一篇，在我本地收集5分钟性能监视器文件达到19Mb、跟踪数据文件为7Mb(没有做任何其他操作)。
技术分享
性能计数器数据，与收集计数器数量、轮询时间间隔有关。与收集时长是线性增长关系。
跟踪数据，与添加的跟踪事件，系统繁忙程度有关。收集的大小可能不成比例地膨胀。
建议在高峰时段短暂地运行SQLdiag约10-15分钟，来估计Profiler跟踪事件的生成率和耗费的磁盘空间。这使你能够在数据被改写之前，对将要捕获的活动的时间跨度形成一个粗略的近似。PerfMon文件并不像Profiler跟踪文件一样迅速地增大。Profiler跟踪收集到目前为止是SQLdiag中最消耗资源的数据收集组件。
PerfmonCollector
虽说收集PerfMon数据的开销相比其他数据收集器是最低的，但也不必要将所有性能计数据器都启用。本节将按照下面步骤测试：
1>禁用EventlogCollector、BlockingCollector、ProfilerCollector、CustomDiagnostics元素收集器；PerfmonCollector、SqldiagCollector元素保持启用
2>参照之前配置的数据收集器模板PerfmonLine启用/禁用PerfmonCollector元素下对应的计数器

技术分享 
<?xml version="1.0" encoding="UTF-16"?>
<DataCollectorSet>
    <Status>1</Status>
    <Duration>86100</Duration>
    <Description>
    </Description>
    <DescriptionUnresolved>
    </DescriptionUnresolved>
    <DisplayName>
    </DisplayName>
    <DisplayNameUnresolved>
    </DisplayNameUnresolved>
    <SchedulesEnabled>-1</SchedulesEnabled>
    <LatestOutputLocation>D:\Perfmon\DataCollector</LatestOutputLocation>
    <Name>PLAYER</Name>
    <OutputLocation>D:\Perfmon\DataCollector</OutputLocation>
    <RootPath>D:\Perfmon</RootPath>
    <Segment>0</Segment>
    <SegmentMaxDuration>0</SegmentMaxDuration>
    <SegmentMaxSize>0</SegmentMaxSize>
    <SerialNumber>15</SerialNumber>
    <Server>
    </Server>
    <Subdirectory>DataCollector</Subdirectory>
    <SubdirectoryFormat>1</SubdirectoryFormat>
    <SubdirectoryFormatPattern>
    </SubdirectoryFormatPattern>
    <Task>
    </Task>
    <TaskRunAsSelf>0</TaskRunAsSelf>
    <TaskArguments>
    </TaskArguments>
    <TaskUserTextArguments>
    </TaskUserTextArguments>
    <UserAccount>SYSTEM</UserAccount>
    <Security>O:BAG:S-1-5-21-2109620463-1702659591-2666464273-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>DataCollector</Name>
        <FileName>PLAYER</FileName>
        <FileNameFormat>1</FileNameFormat>
        <FileNameFormatPattern>\-yyyyMMdd</FileNameFormatPattern>
        <LogAppend>0</LogAppend>
        <LogCircular>0</LogCircular>
        <LogOverwrite>0</LogOverwrite>
        <LatestOutputLocation>D:\Perfmon\DataCollector\PLAYER-20151105.blg</LatestOutputLocation>
        <DataSourceName>
        </DataSourceName>
        <SampleInterval>30</SampleInterval>
        <SegmentMaxRecords>0</SegmentMaxRecords>
        <LogFileFormat>3</LogFileFormat>
        <Counter>\Memory\Available MBytes</Counter>
        <Counter>\Memory\Cache Bytes</Counter>
        <Counter>\Memory\Commit Limit</Counter>
        <Counter>\Memory\Committed Bytes</Counter>
        <Counter>\Memory\Free System Page Table Entries</Counter>
        <Counter>\Memory\Pages Input/sec</Counter>
        <Counter>\Memory\Pages/sec</Counter>
        <Counter>\Memory\System Cache Resident Bytes</Counter>
        <Counter>\MSSQL$PLAYER:Access Methods\FreeSpace Scans/sec</Counter>
        <Counter>\MSSQL$PLAYER:Access Methods\Full Scans/sec</Counter>
        <Counter>\MSSQL$PLAYER:Access Methods\Table Lock Escalations/sec</Counter>
        <Counter>\MSSQL$PLAYER:Access Methods\Worktables Created/sec</Counter>
        <Counter>\MSSQL$PLAYER:Access Methods\Worktables From Cache Ratio</Counter>
        <Counter>\MSSQL$PLAYER:Buffer Manager\Buffer cache hit ratio</Counter>
        <Counter>\MSSQL$PLAYER:Buffer Manager\Checkpoint pages/sec</Counter>
        <Counter>\MSSQL$PLAYER:Buffer Manager\Database pages</Counter>
        <Counter>\MSSQL$PLAYER:Buffer Manager\Free list stalls/sec</Counter>
        <Counter>\MSSQL$PLAYER:Buffer Manager\Free pages</Counter>
        <Counter>\MSSQL$PLAYER:Buffer Manager\Lazy writes/sec</Counter>
        <Counter>\MSSQL$PLAYER:Buffer Manager\Page life expectancy</Counter>
        <Counter>\MSSQL$PLAYER:Buffer Manager\Page lookups/sec</Counter>
        <Counter>\MSSQL$PLAYER:Buffer Manager\Page reads/sec</Counter>
        <Counter>\MSSQL$PLAYER:Buffer Manager\Page writes/sec</Counter>
        <Counter>\MSSQL$PLAYER:Buffer Manager\Readahead pages/sec</Counter>
        <Counter>\MSSQL$PLAYER:Buffer Manager\Stolen pages</Counter>
        <Counter>\MSSQL$PLAYER:Buffer Manager\Target pages</Counter>
        <Counter>\MSSQL$PLAYER:Buffer Manager\Total pages</Counter>
        <Counter>\MSSQL$PLAYER:Databases(_Total)\Transactions/sec</Counter>
        <Counter>\MSSQL$PLAYER:General Statistics\Connection Reset/sec</Counter>
        <Counter>\MSSQL$PLAYER:General Statistics\Logins/sec</Counter>
        <Counter>\MSSQL$PLAYER:General Statistics\Logouts/sec</Counter>
        <Counter>\MSSQL$PLAYER:General Statistics\Processes blocked</Counter>
        <Counter>\MSSQL$PLAYER:General Statistics\User Connections</Counter>
        <Counter>\MSSQL$PLAYER:Latches\Average Latch Wait Time (ms)</Counter>
        <Counter>\MSSQL$PLAYER:Latches\Latch Waits/sec</Counter>
        <Counter>\MSSQL$PLAYER:Latches\Total Latch Wait Time (ms)</Counter>
        <Counter>\MSSQL$PLAYER:Locks(_Total)\Average Wait Time (ms)</Counter>
        <Counter>\MSSQL$PLAYER:Locks(_Total)\Lock Requests/sec</Counter>
        <Counter>\MSSQL$PLAYER:Locks(_Total)\Lock Timeouts (timeout &gt; 0)/sec</Counter>
        <Counter>\MSSQL$PLAYER:Locks(_Total)\Lock Timeouts/sec</Counter>
        <Counter>\MSSQL$PLAYER:Locks(_Total)\Lock Wait Time (ms)</Counter>
        <Counter>\MSSQL$PLAYER:Locks(_Total)\Lock Waits/sec</Counter>
        <Counter>\MSSQL$PLAYER:Locks(_Total)\Number of Deadlocks/sec</Counter>
        <Counter>\MSSQL$PLAYER:Memory Manager\Granted Workspace Memory (KB)</Counter>
        <Counter>\MSSQL$PLAYER:Memory Manager\Maximum Workspace Memory (KB)</Counter>
        <Counter>\MSSQL$PLAYER:Memory Manager\Memory Grants Outstanding</Counter>
        <Counter>\MSSQL$PLAYER:Memory Manager\Memory Grants Pending</Counter>
        <Counter>\MSSQL$PLAYER:Memory Manager\Stolen Server Memory (KB)</Counter>
        <Counter>\MSSQL$PLAYER:Memory Manager\Target Server Memory (KB)</Counter>
        <Counter>\MSSQL$PLAYER:Memory Manager\Total Server Memory (KB)</Counter>
        <Counter>\MSSQL$PLAYER:Plan Cache(_Total)\Cache Hit Ratio</Counter>
        <Counter>\MSSQL$PLAYER:SQL Statistics\Auto-Param Attempts/sec</Counter>
        <Counter>\MSSQL$PLAYER:SQL Statistics\Batch Requests/sec</Counter>
        <Counter>\MSSQL$PLAYER:SQL Statistics\Failed Auto-Params/sec</Counter>
        <Counter>\MSSQL$PLAYER:SQL Statistics\Safe Auto-Params/sec</Counter>
        <Counter>\MSSQL$PLAYER:SQL Statistics\SQL Compilations/sec</Counter>
        <Counter>\MSSQL$PLAYER:SQL Statistics\SQL Re-Compilations/sec</Counter>
        <Counter>\MSSQL$PLAYER:SQL Statistics\Unsafe Auto-Params/sec</Counter>
        <Counter>\MSSQL$PLAYER:Transactions\Longest Transaction Running Time</Counter>
        <Counter>\Network Interface(*)\Bytes Sent/sec</Counter>
        <Counter>\Paging File(_Total)\% Usage Peak</Counter>
        <Counter>\Paging File(_Total)\% Usage</Counter>
        <Counter>\PhysicalDisk(1 D:)\% Idle Time</Counter>
        <Counter>\PhysicalDisk(1 D:)\Avg. Disk Queue Length</Counter>
        <Counter>\PhysicalDisk(1 D:)\Avg. Disk sec/Read</Counter>
        <Counter>\PhysicalDisk(1 D:)\Avg. Disk sec/Transfer</Counter>
        <Counter>\PhysicalDisk(1 D:)\Avg. Disk sec/Write</Counter>
        <Counter>\PhysicalDisk(1 D:)\Disk Bytes/sec</Counter>
        <Counter>\PhysicalDisk(1 D:)\Disk Reads/sec</Counter>
        <Counter>\PhysicalDisk(1 D:)\Disk Writes/sec</Counter>
        <Counter>\Process(sqlservr)\% Processor Time</Counter>
        <Counter>\Process(sqlservr)\Private Bytes</Counter>
        <Counter>\Process(sqlservr)\% User Time</Counter>
        <Counter>\Processor(_Total)\% Privileged Time</Counter>
        <Counter>\Processor(_Total)\% Processor Time</Counter>
        <Counter>\Processor(_Total)\% User Time</Counter>
        <Counter>\System\Context Switches/sec</Counter>
        <Counter>\System\Processor Queue Length</Counter>
        <CounterDisplayName>\Memory\Available MBytes</CounterDisplayName>
        <CounterDisplayName>\Memory\Cache Bytes</CounterDisplayName>
        <CounterDisplayName>\Memory\Commit Limit</CounterDisplayName>
        <CounterDisplayName>\Memory\Committed Bytes</CounterDisplayName>
        <CounterDisplayName>\Memory\Free System Page Table Entries</CounterDisplayName>
        <CounterDisplayName>\Memory\Pages Input/sec</CounterDisplayName>
        <CounterDisplayName>\Memory\Pages/sec</CounterDisplayName>
        <CounterDisplayName>\Memory\System Cache Resident Bytes</CounterDisplayName>
        <CounterDisplayName>\MSSQL$PLAYER:Access Methods\FreeSpace Scans/sec</CounterDisplayName>
        <CounterDisplayName>\MSSQL$PLAYER:Access Methods\Full Scans/sec</CounterDisplayName>
        <CounterDisplayName>\MSSQL$PLAYER:Access Methods\Table Lock Escalations/sec</CounterDisplayName>
        <CounterDisplayName>\MSSQL$PLAYER:Access Methods\Worktables Created/sec</CounterDisplayName>
        <CounterDisplayName>\MSSQL$PLAYER:Access Methods\Worktables From Cache Ratio</CounterDisplayName>
        <CounterDisplayName>\MSSQL$PLAYER:Buffer Manager\Buffer cache hit ratio</CounterDisplayName>
        <CounterDisplayName>\MSSQL$PLAYER:Buffer Manager\Checkpoint pages/sec</CounterDisplayName>
        <CounterDisplayName>\MSSQL$PLAYER:Buffer Manager\Database pages</CounterDisplayName>
        <CounterDisplayName>\MSSQL$PLAYER:Buffer Manager\Free list stalls/sec</CounterDisplayName>
        <CounterDisplayName>\MSSQL$PLAYER:Buffer Manager\Free pages</CounterDisplayName>
        <CounterDisplayName>\MSSQL$PLAYER:Buffer Manager\Lazy writes/sec</CounterDisplayName>
        <CounterDisplayName>\MSSQL$PLAYER:Buffer Manager\Page life expectancy</CounterDisplayName>
        <CounterDisplayName>\MSSQL$PLAYER:Buffer Manager\Page lookups/sec</CounterDisplayName>
        <CounterDisplayName>\MSSQL$PLAYER:Buffer Manager\Page reads/sec</CounterDisplayName>
        <CounterDisplayName>\MSSQL$PLAYER:Buffer Manager\Page writes/sec</CounterDisplayName>
        <CounterDisplayName>\MSSQL$PLAYER:Buffer Manager\Readahead pages/sec</CounterDisplayName>
        <CounterDisplayName>\MSSQL$PLAYER:Buffer Manager\Stolen pages</CounterDisplayName>
        <CounterDisplayName>\MSSQL$PLAYER:Buffer Manager\Target pages</CounterDisplayName>
        <CounterDisplayName>\MSSQL$PLAYER:Buffer Manager\Total pages</CounterDisplayName>
        <CounterDisplayName>\MSSQL$PLAYER:Databases(_Total)\Transactions/sec</CounterDisplayName>
        <CounterDisplayName>\MSSQL$PLAYER:General Statistics\Connection Reset/sec</CounterDisplayName>
        <CounterDisplayName>\MSSQL$PLAYER:General Statistics\Logins/sec</CounterDisplayName>
        <CounterDisplayName>\MSSQL$PLAYER:General Statistics\Logouts/sec</CounterDisplayName>
        <CounterDisplayName>\MSSQL$PLAYER:General Statistics\Processes blocked</CounterDisplayName>
        <CounterDisplayName>\MSSQL$PLAYER:General Statistics\User Connections</CounterDisplayName>
        <CounterDisplayName>\MSSQL$PLAYER:Latches\Average Latch Wait Time (ms)</CounterDisplayName>
        <CounterDisplayName>\MSSQL$PLAYER:Latches\Latch Waits/sec</CounterDisplayName>
        <CounterDisplayName>\MSSQL$PLAYER:Latches\Total Latch Wait Time (ms)</CounterDisplayName>
        <CounterDisplayName>\MSSQL$PLAYER:Locks(_Total)\Average Wait Time (ms)</CounterDisplayName>
        <CounterDisplayName>\MSSQL$PLAYER:Locks(_Total)\Lock Requests/sec</CounterDisplayName>
        <CounterDisplayName>\MSSQL$PLAYER:Locks(_Total)\Lock Timeouts (timeout &gt; 0)/sec</CounterDisplayName>
        <CounterDisplayName>\MSSQL$PLAYER:Locks(_Total)\Lock Timeouts/sec</CounterDisplayName>
        <CounterDisplayName>\MSSQL$PLAYER:Locks(_Total)\Lock Wait Time (ms)</CounterDisplayName>
        <CounterDisplayName>\MSSQL$PLAYER:Locks(_Total)\Lock Waits/sec</CounterDisplayName>
        <CounterDisplayName>\MSSQL$PLAYER:Locks(_Total)\Number of Deadlocks/sec</CounterDisplayName>
        <CounterDisplayName>\MSSQL$PLAYER:Memory Manager\Granted Workspace Memory (KB)</CounterDisplayName>
        <CounterDisplayName>\MSSQL$PLAYER:Memory Manager\Maximum Workspace Memory (KB)</CounterDisplayName>
        <CounterDisplayName>\MSSQL$PLAYER:Memory Manager\Memory Grants Outstanding</CounterDisplayName>
        <CounterDisplayName>\MSSQL$PLAYER:Memory Manager\Memory Grants Pending</CounterDisplayName>
        <CounterDisplayName>\MSSQL$PLAYER:Memory Manager\Stolen Server Memory (KB)</CounterDisplayName>
        <CounterDisplayName>\MSSQL$PLAYER:Memory Manager\Target Server Memory (KB)</CounterDisplayName>
        <CounterDisplayName>\MSSQL$PLAYER:Memory Manager\Total Server Memory (KB)</CounterDisplayName>
        <CounterDisplayName>\MSSQL$PLAYER:Plan Cache(_Total)\Cache Hit Ratio</CounterDisplayName>
        <CounterDisplayName>\MSSQL$PLAYER:SQL Statistics\Auto-Param Attempts/sec</CounterDisplayName>
        <CounterDisplayName>\MSSQL$PLAYER:SQL Statistics\Batch Requests/sec</CounterDisplayName>
        <CounterDisplayName>\MSSQL$PLAYER:SQL Statistics\Failed Auto-Params/sec</CounterDisplayName>
        <CounterDisplayName>\MSSQL$PLAYER:SQL Statistics\Safe Auto-Params/sec</CounterDisplayName>
        <CounterDisplayName>\MSSQL$PLAYER:SQL Statistics\SQL Compilations/sec</CounterDisplayName>
        <CounterDisplayName>\MSSQL$PLAYER:SQL Statistics\SQL Re-Compilations/sec</CounterDisplayName>
        <CounterDisplayName>\MSSQL$PLAYER:SQL Statistics\Unsafe Auto-Params/sec</CounterDisplayName>
        <CounterDisplayName>\MSSQL$PLAYER:Transactions\Longest Transaction Running Time</CounterDisplayName>
        <CounterDisplayName>\Network Interface(*)\Bytes Sent/sec</CounterDisplayName>
        <CounterDisplayName>\Paging File(_Total)\% Usage Peak</CounterDisplayName>
        <CounterDisplayName>\Paging File(_Total)\% Usage</CounterDisplayName>
        <CounterDisplayName>\PhysicalDisk(1 D:)\% Idle Time</CounterDisplayName>
        <CounterDisplayName>\PhysicalDisk(1 D:)\Avg. Disk Queue Length</CounterDisplayName>
        <CounterDisplayName>\PhysicalDisk(1 D:)\Avg. Disk sec/Read</CounterDisplayName>
        <CounterDisplayName>\PhysicalDisk(1 D:)\Avg. Disk sec/Transfer</CounterDisplayName>
        <CounterDisplayName>\PhysicalDisk(1 D:)\Avg. Disk sec/Write</CounterDisplayName>
        <CounterDisplayName>\PhysicalDisk(1 D:)\Disk Bytes/sec</CounterDisplayName>
        <CounterDisplayName>\PhysicalDisk(1 D:)\Disk Reads/sec</CounterDisplayName>
        <CounterDisplayName>\PhysicalDisk(1 D:)\Disk Writes/sec</CounterDisplayName>
        <CounterDisplayName>\Process(sqlservr)\% Processor Time</CounterDisplayName>
        <CounterDisplayName>\Process(sqlservr)\Private Bytes</CounterDisplayName>
        <CounterDisplayName>\Process(sqlservr)\% User Time</CounterDisplayName>
        <CounterDisplayName>\Processor(_Total)\% Privileged Time</CounterDisplayName>
        <CounterDisplayName>\Processor(_Total)\% Processor Time</CounterDisplayName>
        <CounterDisplayName>\Processor(_Total)\% User Time</CounterDisplayName>
        <CounterDisplayName>\System\Context Switches/sec</CounterDisplayName>
        <CounterDisplayName>\System\Processor Queue Length</CounterDisplayName>
    </PerformanceCounterDataCollector>
    <Schedule>
        <StartDate>5/20/2015</StartDate>
        <EndDate>11/14/2015</EndDate>
        <StartTime>9:30:00 AM</StartTime>
        <Days>127</Days>
    </Schedule>
    <DataManager>
        <Enabled>0</Enabled>
        <CheckBeforeRunning>0</CheckBeforeRunning>
        <MinFreeD

人气教程排行

本站所有资源全部来源于网络，若本站发布的内容侵害到您的隐私或者利益，请联系我们删除！