SQL Server Counters With AG

* Counter Kaldırmak için;

unlodctr MSSQL$TEST

* Counter Eklemek için;

lodctr "C:Program FilesMicrosoft SQL ServerMSSQL14.TESTMSSQLBinnperf-MSSQL$TESTsqlctr.ini"

* Counter Kontrol Etmek için;

select * from sys.dm_os_performance_counters order by object_name

Aşağıdaki template’i data collector içerine xml uzantılı olarak eklenebilir.

<?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>SQLServerCountersWithAG</Name>

<OutputLocation>C:perflogServerName</OutputLocation>

<RootPath>C:perflogServerName</RootPath>

<Segment>0</Segment>

<SegmentMaxDuration>0</SegmentMaxDuration>

<SegmentMaxSize>2048</SegmentMaxSize>

<SerialNumber>1</SerialNumber>

<Server> </Server>

<Subdirectory> </Subdirectory>

<SubdirectoryFormat>1</SubdirectoryFormat>

<SubdirectoryFormatPattern>yyyyMMdd-NNNNNN </SubdirectoryFormatPattern>

<Task> </Task>

<TaskRunAsSelf>0</TaskRunAsSelf>

<TaskArguments> </TaskArguments>

<TaskUserTextArguments> </TaskUserTextArguments>

<UserAccount>SYSTEM</UserAccount>

<StopOnCompletion>0</StopOnCompletion>

-<PerformanceCounterDataCollector>

<DataCollectorType>0</DataCollectorType>

<Name>DataCollector01</Name>

<FileName>Test</FileName>

<FileNameFormat>1</FileNameFormat>

<FileNameFormatPattern>N </FileNameFormatPattern>

<LogAppend>0</LogAppend>

<LogCircular>-1</LogCircular>

<LogOverwrite>0</LogOverwrite>

<LatestOutputLocation> </LatestOutputLocation>

<DataSourceName> </DataSourceName>

<SampleInterval>10</SampleInterval>

<SegmentMaxRecords>0</SegmentMaxRecords>

<LogFileFormat>3</LogFileFormat>

<Counter>CacheDirty Pages</Counter>

<Counter>CacheLazy Write Flushes/sec</Counter>

<Counter>LogicalDisk(*)% Free Space</Counter>

<Counter>LogicalDisk(*)% Idle Time</Counter>

<Counter>LogicalDisk(*)Avg. Disk Bytes/Read</Counter>

<Counter>LogicalDisk(*)Avg. Disk Bytes/Transfer</Counter>

<Counter>LogicalDisk(*)Avg. Disk Bytes/Write</Counter>

<Counter>LogicalDisk(*)Avg. Disk Queue Length</Counter>

<Counter>LogicalDisk(*)Avg. Disk sec/Read</Counter>

<Counter>LogicalDisk(*)Avg. Disk sec/Transfer</Counter>

<Counter>LogicalDisk(*)Avg. Disk sec/Write</Counter>

<Counter>LogicalDisk(*)Current Disk Queue Length</Counter>

<Counter>LogicalDisk(*)Disk Bytes/sec</Counter>

<Counter>LogicalDisk(*)Disk Reads/sec</Counter>

<Counter>LogicalDisk(*)Disk Transfers/sec</Counter>

<Counter>LogicalDisk(*)Disk Writes/sec</Counter>

<Counter>LogicalDisk(*)Free Megabytes</Counter>

<Counter>Memory% Committed Bytes In Use</Counter>

<Counter>MemoryAvailable MBytes</Counter>

<Counter>MemoryCommit Limit</Counter>

<Counter>MemoryCommitted Bytes</Counter>

<Counter>MemoryFree & Zero Page List Bytes</Counter>

<Counter>MemoryFree System Page Table Entries</Counter>

<Counter>MemoryLong-Term Average Standby Cache Lifetime (s)</Counter>

<Counter>MemoryPages Input/sec</Counter>

<Counter>MemoryPages Output/sec</Counter>

<Counter>MemoryPages/sec</Counter>

<Counter>MemoryPool Nonpaged Bytes</Counter>

<Counter>MemoryPool Paged Bytes</Counter>

<Counter>MemoryPool Paged Resident Bytes</Counter>

<Counter>MemorySystem Cache Resident Bytes</Counter>

<Counter>MemoryTransition Pages RePurposed/sec</Counter>

<Counter>Network Inspection SystemAverage inspection latency (sec/bytes)</Counter>

<Counter>Network Interface(*)Bytes Received/sec</Counter>

<Counter>Network Interface(*)Bytes Sent/sec</Counter>

<Counter>Network Interface(*)Bytes Total/sec</Counter>

<Counter>Network Interface(*)Current Bandwidth</Counter>

<Counter>Network Interface(*)Output Queue Length</Counter>

<Counter>Network Interface(*)Packets Outbound Errors</Counter>

<Counter>Network Interface(*)Packets Received/sec</Counter>

<Counter>Network Interface(*)Packets Sent/sec</Counter>

<Counter>Network Interface(*)Packets/sec</Counter>

<Counter>Paging File(*)% Usage</Counter>

<Counter>Paging File(*)% Usage Peak</Counter>

<Counter>PhysicalDisk(*)Avg. Disk Queue Length</Counter>

<Counter>PhysicalDisk(*)Avg. Disk sec/Read</Counter>

<Counter>PhysicalDisk(*)Avg. Disk sec/Write</Counter>

<Counter>PhysicalDisk(*)Current Disk Queue Length</Counter>

<Counter>PhysicalDisk(*)Disk Bytes/sec</Counter>

<Counter>Process(*)% Privileged Time</Counter>

<Counter>Process(*)% Processor Time</Counter>

<Counter>Process(*)Handle Count</Counter>

<Counter>Process(*)ID Process</Counter>

<Counter>Process(*)IO Data Operations/sec</Counter>

<Counter>Process(*)IO Other Operations/sec</Counter>

<Counter>Process(*)IO Read Operations/sec</Counter>

<Counter>Process(*)IO Write Operations/sec</Counter>

<Counter>Process(*)Private Bytes</Counter>

<Counter>Process(*)Thread Count</Counter>

<Counter>Process(*)Virtual Bytes</Counter>

<Counter>Process(*)Working Set</Counter>

<Counter>Processor Information(*)% DPC Time</Counter>

<Counter>Processor Information(*)% Interrupt Time</Counter>

<Counter>Processor Information(*)% of Maximum Frequency</Counter>

<Counter>Processor Information(*)% Privileged Time</Counter>

<Counter>Processor Information(*)% Processor Time</Counter>

<Counter>Processor Information(*)% User Time</Counter>

<Counter>Processor Information(*)DPC Rate</Counter>

<Counter>Processor Information(*)Parking Status</Counter>

<Counter>Processor(*)% DPC Time</Counter>

<Counter>Processor(*)% Interrupt Time</Counter>

<Counter>Processor(*)% Privileged Time</Counter>

<Counter>Processor(*)% Processor Time</Counter>

<Counter>Processor(*)% User Time</Counter>

<Counter>Processor(*)DPC Rate</Counter>

<Counter>ServerPool Nonpaged Failures</Counter>

<Counter>ServerPool Paged Failures</Counter>

<Counter>SystemContext Switches/sec</Counter>

<Counter>SystemProcessor Queue Length</Counter>

<Counter>SystemSystem Calls/sec</Counter>

<Counter>TCPv4Connection Failures</Counter>

<Counter>MSRS 2011 Web ServiceCache Misses/Sec</Counter>

<Counter>MSRS 2011 Web ServiceReport Requests</Counter>

<Counter>MSRS 2011 Web ServiceTotal Memory Cache Misses</Counter>

<Counter>MSRS 2011 Web ServiceTotal Requests</Counter>

<Counter>Process(sqlservr)% Privileged Time</Counter>

<Counter>Process(sqlservr)% Processor Time</Counter>

<Counter>ReportServer:ServiceErrors Total</Counter>

<Counter>ReportServer:ServiceErrors/sec</Counter>

<Counter>ReportServer:ServiceMemory Pressure State</Counter>

<Counter>ReportServer:ServiceMemory Shrink Amount</Counter>

<Counter>ReportServer:ServiceMemory Shrink Notifications/sec</Counter>

<Counter>SQLAgent:JobsActive jobs</Counter>

<Counter>SQLAgent:JobsFailed jobs</Counter>

<Counter>SQLAgent:JobsJob success rate</Counter>

<Counter>SQLAgent:JobsSuccessful jobs</Counter>

<Counter>SQLAgent:JobStepsActive steps</Counter>

<Counter>SQLAgent:JobStepsTotal step retries</Counter>

<Counter>SQLServer:Access MethodsForwarded Records/sec</Counter>

<Counter>SQLServer:Access MethodsFreeSpace Scans/sec</Counter>

<Counter>SQLServer:Access MethodsFull Scans/sec</Counter>

<Counter>SQLServer:Access MethodsIndex Searches/sec</Counter>

<Counter>SQLServer:Access MethodsMixed page allocations/sec</Counter>

<Counter>SQLServer:Access MethodsPage Splits/sec</Counter>

<Counter>SQLServer:Access MethodsRange scans/sec</Counter>

<Counter>SQLServer:Access MethodsScan Point Revalidations/sec</Counter>

<Counter>SQLServer:Access MethodsTable Lock Escalations/sec</Counter>

<Counter>SQLServer:Access MethodsWorkfiles Created/sec</Counter>

<Counter>SQLServer:Access MethodsWorktables Created/sec</Counter>

<Counter>SQLServer:Access MethodsWorktables From Cache Ratio</Counter>

<Counter>SQLServer:Availability Replica(*)Bytes Received from Replica/sec</Counter>

<Counter>SQLServer:Availability Replica(*)Bytes Sent to Replica/sec</Counter>

<Counter>SQLServer:Availability Replica(*)Bytes Sent to Transport/sec</Counter>

<Counter>SQLServer:Availability Replica(_Total)Receives from Replica/sec</Counter>

<Counter>SQLServer:Availability Replica(_Total)Resent Messages/sec</Counter>

<Counter>SQLServer:Availability Replica(_Total)Sends to Replica/sec</Counter>

<Counter>SQLServer:Buffer ManagerBackground writer pages/sec</Counter>

<Counter>SQLServer:Buffer ManagerBuffer cache hit ratio</Counter>

<Counter>SQLServer:Buffer ManagerCheckpoint pages/sec</Counter>

<Counter>SQLServer:Buffer ManagerFree list stalls/sec</Counter>

<Counter>SQLServer:Buffer ManagerFree pages</Counter>

<Counter>SQLServer:Buffer ManagerLazy writes/sec</Counter>

<Counter>SQLServer:Buffer ManagerPage life expectancy</Counter>

<Counter>SQLServer:Buffer ManagerPage lookups/sec</Counter>

<Counter>SQLServer:Buffer ManagerPage reads/sec</Counter>

<Counter>SQLServer:Buffer ManagerPage writes/sec</Counter>

<Counter>SQLServer:Buffer ManagerReadahead pages/sec</Counter>

<Counter>SQLServer:Buffer ManagerTarget pages</Counter>

<Counter>SQLServer:Buffer Node(*)Database pages</Counter>

<Counter>SQLServer:Buffer Node(*)Foreign pages</Counter>

<Counter>SQLServer:Buffer Node(*)Local node page lookups/sec</Counter>

<Counter>SQLServer:Buffer Node(*)Page life expectancy</Counter>

<Counter>SQLServer:Buffer Node(*)Remote node page lookups/sec</Counter>

<Counter>SQLServer:Cursor Manager by TypeCursor Requests/sec</Counter>

<Counter>SQLServer:Database Replica(*)Log Bytes Received/sec</Counter>

<Counter>SQLServer:Database Replica(*)Mirrored Write Transactions/sec</Counter>

<Counter>SQLServer:Database Replica(*)Recovery Queue</Counter>

<Counter>SQLServer:Database Replica(_Total)Log remaining for undo</Counter>

<Counter>SQLServer:Database Replica(_Total)Log Send Queue</Counter>

<Counter>SQLServer:Database Replica(_Total)Redo blocked/sec</Counter>

<Counter>SQLServer:Database Replica(_Total)Redo Bytes Remaining</Counter>

<Counter>SQLServer:Database Replica(_Total)Redone Bytes/sec</Counter>

<Counter>SQLServer:Database Replica(_Total)Total Log requiring undo</Counter>

<Counter>SQLServer:Database Replica(_Total)Transaction Delay</Counter>

<Counter>SQLServer:Databases(*)Active Transactions</Counter>

<Counter>SQLServer:Databases(*)Backup/Restore Throughput/sec</Counter>

<Counter>SQLServer:Databases(*)Bulk Copy Throughput/sec</Counter>

<Counter>SQLServer:Databases(*)Data File(s) Size (KB)</Counter>

<Counter>SQLServer:Databases(*)Log Bytes Flushed/sec</Counter>

<Counter>SQLServer:Databases(*)Log File(s) Size (KB)</Counter>

<Counter>SQLServer:Databases(*)Log File(s) Used Size (KB)</Counter>

<Counter>SQLServer:Databases(*)Log Flush Wait Time</Counter>

<Counter>SQLServer:Databases(*)Log Flush Waits/sec</Counter>

<Counter>SQLServer:Databases(*)Log Flushes/sec</Counter>

<Counter>SQLServer:Databases(*)Log Growths</Counter>

<Counter>SQLServer:Databases(*)Log Shrinks</Counter>

<Counter>SQLServer:Databases(*)Log Truncations</Counter>

<Counter>SQLServer:Databases(*)Percent Log Used</Counter>

<Counter>SQLServer:Deprecated Features(*)Usage</Counter>

<Counter>SQLServer:General StatisticsActive Temp Tables</Counter>

<Counter>SQLServer:General StatisticsLogins/sec</Counter>

<Counter>SQLServer:General StatisticsLogouts/sec</Counter>

<Counter>SQLServer:General StatisticsProcesses Blocked</Counter>

<Counter>SQLServer:General StatisticsTemp Tables Creation Rate</Counter>

<Counter>SQLServer:General StatisticsTemp Tables For Destruction</Counter>

<Counter>SQLServer:General StatisticsUser Connections</Counter>

<Counter>SQLServer:LatchesAverage Latch Wait Time (ms)</Counter>

<Counter>SQLServer:LatchesLatch Waits/sec</Counter>

<Counter>SQLServer:LatchesTotal Latch Wait Time (ms)</Counter>

<Counter>SQLServer:Locks(*)Average Wait Time (ms)</Counter>

<Counter>SQLServer:Locks(*)Lock Requests/sec</Counter>

<Counter>SQLServer:Locks(*)Lock Timeouts/sec</Counter>

<Counter>SQLServer:Locks(*)Lock Wait Time (ms)</Counter>

<Counter>SQLServer:Locks(*)Lock Waits/sec</Counter>

<Counter>SQLServer:Locks(*)Number of Deadlocks/sec</Counter>

<Counter>SQLServer:Memory ManagerGranted Workspace Memory (KB)</Counter>

<Counter>SQLServer:Memory ManagerMaximum Workspace Memory (KB)</Counter>

<Counter>SQLServer:Memory ManagerMemory Grants Outstanding</Counter>

<Counter>SQLServer:Memory ManagerMemory Grants Pending</Counter>

<Counter>SQLServer:Memory ManagerOptimizer Memory (KB)</Counter>

<Counter>SQLServer:Memory ManagerStolen Server Memory (KB)</Counter>

<Counter>SQLServer:Memory ManagerTarget Server Memory (KB)</Counter>

<Counter>SQLServer:Memory ManagerTarget Server Memory(KB)</Counter>

<Counter>SQLServer:Memory ManagerTotal Server Memory (KB)</Counter>

<Counter>SQLServer:Memory Node(*)Database Node Memory (KB)</Counter>

<Counter>SQLServer:Memory Node(*)Foreign Node Memory (KB)</Counter>

<Counter>SQLServer:Memory Node(*)Stolen Node Memory (KB)</Counter>

<Counter>SQLServer:Memory Node(*)Target Node Memory (KB)</Counter>

<Counter>SQLServer:Memory Node(000)Total Node Memory (KB)</Counter>

<Counter>SQLServer:Plan Cache(*)Cache Hit Ratio</Counter>

<Counter>SQLServer:Query Store(*)Query Store CPU Usage</Counter>

<Counter>SQLServer:Query Store(*)Query Store logical reads</Counter>

<Counter>SQLServer:Query Store(*)Query Store logical writes</Counter>

<Counter>SQLServer:Query Store(*)Query Store physical reads</Counter>

<Counter>SQLServer:Resource Pool Stats(*)CPU usage %</Counter>

<Counter>SQLServer:Resource Pool Stats(*)Max memory (KB)</Counter>

<Counter>SQLServer:Resource Pool Stats(*)Target memory (KB)</Counter>

<Counter>SQLServer:Resource Pool Stats(*)Used memory (KB)</Counter>

<Counter>SQLServer:SQL Errors(*)Errors/sec</Counter>

<Counter>SQLServer:SQL StatisticsAuto-Param Attempts/sec</Counter>

<Counter>SQLServer:SQL StatisticsBatch Requests/sec</Counter>

<Counter>SQLServer:SQL StatisticsFailed Auto-Params/sec</Counter>

<Counter>SQLServer:SQL StatisticsSafe Auto-Params/sec</Counter>

<Counter>SQLServer:SQL StatisticsSQL Attention rate</Counter>

<Counter>SQLServer:SQL StatisticsSQL Compilations/sec</Counter>

<Counter>SQLServer:SQL StatisticsSQL Re-Compilations/sec</Counter>

<Counter>SQLServer:SQL StatisticsUnsafe Auto-Params/sec</Counter>

<Counter>SQLServer:TransactionsFree Space in tempdb (KB)</Counter>

<Counter>SQLServer:TransactionsLongest Transaction Running Time</Counter>

<Counter>SQLServer:TransactionsNonSnapshot Version Transactions</Counter>

<Counter>SQLServer:TransactionsSnapshot Transactions</Counter>

<Counter>SQLServer:TransactionsTransactions</Counter>

<Counter>SQLServer:TransactionsVersion Cleanup rate (KB/s)</Counter>

<Counter>SQLServer:TransactionsVersion Generation rate (KB/s)</Counter>

<Counter>SQLServer:TransactionsVersion Store Size (KB)</Counter>

<Counter>SQLServer:User Settable(*)Query</Counter>

<Counter>SQLServer:Wait Statistics(*)Lock waits</Counter>

<Counter>SQLServer:Wait Statistics(*)Log write waits</Counter>

<Counter>SQLServer:Wait Statistics(*)Network IO waits</Counter>

<Counter>SQLServer:Wait Statistics(*)Non-Page latch waits</Counter>

<Counter>SQLServer:Wait Statistics(*)Page IO latch waits</Counter>

<Counter>SQLServer:Wait Statistics(*)Page latch waits</Counter>

<Counter>SQLServer:Wait Statistics(*)Wait for the worker</Counter>

<Counter>SQLServer:Workload Group Stats(*)Active parallel threads</Counter>

<Counter>SQLServer:Workload Group Stats(*)Active requests</Counter>

<Counter>SQLServer:Workload Group Stats(*)CPU usage %</Counter>

<Counter>SQLServer:Workload Group Stats(*)Queued requests</Counter>

<Counter>SQLServer:Workload Group Stats(*)Reduced memory grants/sec</Counter>

<Counter>SQLServer:Workload Group Stats(*)Requests completed/sec</Counter>

<Counter>SQLServer:Workload Group Stats(*)Suboptimal plans/sec</Counter>

</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>