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>