Total Pageviews

Monday, April 24, 2017

List of Performance Counters for every SQL Server folks

The Remote PerfCollector Agent collects more than 200+ Windows and SQL Server metrics which are crucial for any SQL Server operation. The agent has the ability to collect data within every few seconds from one or more remote servers continuously. The collected data are in two categories: one is Windows specific and the other is SQL Server specific which is seamlessly stored in a Time Series database known as influxDB.

The following table provides each counter name and measurement units. Please note that all SQL Server counters are prefixed with the “SQL” keyword, whereas Windows counters are prefixed with either “cpu”, “disk”, “sys”, “network” or “memory”.

You might be interested in reading the following two articles for easy installation and configuration of “PerfCollector”:

PerfCollector Process:

InfluxDB and Grafana Installation:

Windows Performance Counter:
#
Performance Counter
Collected As
Unit
1
% Processor Time
cpu_PercentProcessorTime
Numeric
2
% User Time
cpu_PercentUserTime
Numeric
3
% Privilege Time
cpu_PercentPrivilegedTime
Numeric
4
% Idle Time
cpu_PercentIdleTime
Numeric
5
Interrupts/sec
cpu_InterruptsPersec
Numeric
6
Processor Queue Length
sys_ProcessorQueueLength
Numeric
7
Context Switches/sec
sys_ContextSwitchesPersec
Numeric
8
Avg. Disk sec/Read
disk_AvgDisksecPerRead
Seconds
9
Avg. Disk sec/Write
disk_AvgDisksecPerWrite
Seconds
10
Avg. Disk sec/Transfer
disk_AvgDisksecPerTransfer
Seconds
11
Disk Writes/sec
disk_DiskWritesPersec
Numeric
12
Disk Reads/sec
disk_DiskReadsPersec
Numeric
13
Disk Transfers/sec
disk_DiskTransfersPersec
Numeric
14
Disk Read Bytes/sec
disk_DiskReadBytesPersec
Bytes
15
Disk Write Bytes/sec
disk_DiskWriteBytesPersec
Bytes
16
Disk Bytes/sec
disk_DiskBytesPersec
Bytes
17
Split IO/sec
disk_SplitIOPerSec
Numeric
18
Bytes Received/sec
network_BytesReceivedPersec
Bytes
19
Bytes Sent/sec
network_BytesSentPersec
Bytes
20
Bytes Total/sec
network_BytesTotalPersec
Bytes
21
Packets Received Errors
network_PacketsReceivedErrors
Numeric
22
Packets Outbound Errors
network_PacketsOutboundErrors
Numeric
23
Output Queue Length
network_OutputQueueLength
Numeric
24
Free Physical Memory
memory_FreePhysicalMemory
KiloBytes
25
Free Virtual Memory
memory_FreeVirtualMemory
KiloBytes
26
Total Virtual Memory
memory_TotalVirtualMemorySize
KiloBytes
27
Total Visible Memory
memory_TotalVisibleMemorySize
KiloBytes
28
Physical Memory Used
memory_PhysicalMemoryUsed
KiloBytes
29
Virtual Memory Used
memory_VirtualMemoryUsed
KiloBytes
30
Physical Memory
memory_PhysicalMemoryGB
GigaBytes
31
% Physical Memory Used
memory_PctPhysicalMemoryUsed
Numeric
32
% Virtual Memory Used
memory_PctVirtualMemoryUsed
Numeric
33
% Virtual Memory Used
memory_PctVirtualMemoryUsed
Numeric
34
Pool Non-paged Bytes
memory_PoolNonpagedBytes
Bytes
35
Pool Paged Bytes
memory_PoolPagedBytes
Bytes
36
Available Bytes
memory_AvailableBytes
Bytes
37
Cache Bytes
memory_CacheBytes
Bytes
38
Commit Limit
memory_CommitLimit
Bytes
39
Committed Bytes
memory_CommittedBytes
Bytes
40
% Committed Bytes in Use
memory_PercentCommittedBytesInUse
Numeric
41
Free And Zero Page List Bytes
memory_FreeAndZeroPageListBytes
Bytes
42
Free System Page Table Entries
memory_FreeSystemPageTableEntries
Numeric
43
Modified Page List Bytes
memory_ModifiedPageListBytes
Bytes
44
Page Faults/sec
memory_PageFaultsPersec
Numeric
45
Page Reads/sec
memory_PageReadsPersec
Numeric
46
Pages Input/sec
memory_PagesInputPersec
Numeric
47
Pages Output/sec
memory_PagesOutputPersec
Numeric
48
Pages/sec
memory_PagesPersec
Numeric
49
Page Writes/sec
memory_PageWritesPersec
Numeric
50
Cache Faults/sec
memory_CacheFaultsPersec
Numeric
51
Page File Size
memory_PageFileSize
Megabytes
52
Page File Current Usage
memory_PageFileCurrentUsage
Megabytes
53
% Page File Usage
memory_PageFilePctUsage
Numeric
54
Page File Free
memory_PageFileFree
Megabytes
55
Avg. Disk sec/Read
disk_[drive]_AvgDisksecPerRead
Seconds
56
Avg. Disk sec/Write
disk_[drive]_AvgDisksecPerWrite
Seconds
57
Avg. Disk sec/Transfer
disk_[drive]_AvgDisksecPerTransfer
Seconds
58
Disk Writes/sec
disk_[drive]_DiskWritesPersec
Numeric
59
Disk Reads/sec
disk_[drive]_DiskReadsPersec
Numeric
60
Disk Transfers/sec
disk_[drive]_DiskTransfersPersec
Numeric
61
Disk Read Bytes/sec
disk_[drive]_DiskReadBytesPersec
Bytes
62
Disk Write Bytes/sec
disk_[drive]_DiskWriteBytesPersec
Bytes
63
Disk Bytes/sec
disk_[drive]_DiskBytesPersec
Bytes

SQL Server Performance Counter:
#
Performance Counter
Collected As
Unit
1
Total Connections
SQL_TotalConnectionCount
Numeric
2
Total Blocked Sessions
SQL_TotalBlockedSessionCount
Numeric
3
Total User Sessions
SQL_TotalUserSessionCount
Numeric
4
Total Running User Sessions
SQL_TotalRunningUserSessionCount
Numeric
5
Total Client Machines
SQL_TotalClientMachineCount
Numeric
6
Total Running Sessions
SQL_TotalRunningSessionCount
Numeric
7
Total DBCC Sessions
SQL_TotalDBCCSessionCount
Numeric
8
Max blocking Time
SQL_Max_blocking_time_ms
Milliseconds
9
%Signal Wait
SQL_Pct_Signal_Wait
Numeric
10
Waiting Tasks
SQL_Waiting_task_count
Numeric
11
Running Tasks
SQL_Running_task_count
Numeric
12
Pending Disk I/O
SQL_Pending_disk_io_count
Numeric
13
Open Transaction
SQL_Open_transaction
Numeric
14
Tempdb free space
SQL_tempdb_free_space_mb
Megabytes
15
Tempdb used space
SQL_tempdb_used_space_mb
Megabytes
16
Tempdb current size
SQL_tempdb_current_size_mb
Megabytes
17
% tempdb used
SQL_Pct_tempdb_used
Numeric
18
Number of Deadlocks/sec
SQL_Number_of_Deadlocks_sec
Cumulative
19
Processes blocked
SQL_Processes_blocked
Numeric
20
Total Lead Blockers
SQL_TotalLeadBlockerCount
Numeric
21
Active SQL Agent Jobs
SQL_Active_Jobs
Numeric
22
User Database size
SQL_User_db_size_mb
Megabytes
23
Log size
SQL_Log_size_mb
Megabytes
24
Log used
SQL_Log_used_mb
Megabytes
25
% Log Used
SQL_Pct_Log_used
Numeric
26
Physical Memory Used by SQL Server
SQL_Physical_Memory_in_use_kb
Kilobytes
27
% Processor Time
SQL_PercentProcessorTime
Numeric
28
% Priviledged Time
SQL_PercentPrivilegedTime
Numeric
29
% User Time
SQL_PercentUserTime
Numeric
30
Working Set of SQL Server
SQL_WorkingSet
Bytes
31
Working Set Private of SQL Server
SQL_WorkingSetPrivate
Bytes
32
Read Byte/sec
SQL_Read_Byte_Per_Sec
Numeric
33
Write Byte/sec
SQL_Write_Byte_Per_Sec
Numeric
34
Page Faults/sec
SQL_Page_Faults_Per_Sec
Numeric
35
Page File Bytes
SQL_PageFileBytes
Bytes
36
Private Bytes
SQL_PrivateBytes
Bytes
37
Pool Non-paged Bytes
SQL_PoolNonpagedBytes
Bytes
38
Pool Paged Bytes
SQL_PoolPagedBytes
Bytes
39
Thread Count
SQL_ThreadCount
Numeric
40
Handle Count
SQL_HandleCount
Numeric
41
Checkpoint Pages/sec
SQL_CheckpointpagesPersec
Numeric
42
Free list stalls/sec
SQL_FreeliststallsPersec
Numeric
43
Lazy writes/sec
SQL_LazywritesPersec
Numeric
44
Page life expectancy
SQL_Pagelifeexpectancy
Seconds
45
Page lookups/sec
SQL_PagelookupsPersec
Numeric
46
Page reads/sec
SQL_PagereadsPersec
Numeric
47
Page writes/sec
SQL_PagewritesPersec
Numeric
48
Read ahead pages/sec
SQL_ReadaheadpagesPersec
Numeric
49
Full Scans/sec
SQL_FullScansPersec
Numeric
50
Index Searches/sec
SQL_IndexSearchesPersec
Numeric
51
Pages compressed/sec
SQL_PagescompressedPersec
Numeric
52
Page Splits/sec
SQL_PageSplitsPersec
Numeric
53
Table Lock Escalations/sec
SQL_TableLockEscalationsPersec
Numeric
54
Work files Created/sec
SQL_WorkfilesCreatedPersec
Numeric
55
Work tables Created/sec
SQL_WorktablesCreatedPersec
Numeric
56
Forwarded Records/sec
SQL_ForwardedRecordsPersec
Numeric
57
Extent Deallocations/sec
SQL_ExtentDeallocationsPersec
Numeric
58
Extents Allocated/sec
SQL_ExtentsAllocatedPersec
Numeric
59
Free Space Page Fetches/sec
SQL_FreeSpacePageFetchesPersec
Numeric
60
Free Space Scans/sec
SQL_FreeSpaceScansPersec
Numeric
61
Range Scans/sec
SQL_RangeScansPersec
Numeric
62
Probe Scans/sec
SQL_ProbeScansPersec
Numeric
63
Mixed page allocations/sec
SQL_MixedpageallocationsPersec
Numeric
64
Lock Average Wait Time
SQL_LockAverageWaitTimems
Milliseconds
65
Lock Requests/sec
SQL_LockRequestsPersec
Numeric
66
Lock Timeouts/sec
SQL_LockTimeoutsPersec
Numeric
67
Lock Waits/sec
SQL_LockWaitsPersec
Numeric
68
Latch Average Latch Wait Time
SQL_LatchAverageLatchWaitTimems
Milliseconds
69
Latch Waits/sec
SQL_LatchWaitsPersec
Numeric
70
Number of Super Latches
SQL_LatchNumberofSuperLatches
Numeric
71
Super Latch Demotions/sec
SQL_LatchSuperLatchDemotionsPersec
Numeric
72
Latch Super Latch Promotions/sec
SQL_LatchSuperLatchPromotionsPersec
Numeric
73
Batch Requests/sec
SQL_SQLBatchRequestsPersec
Numeric
74
SQL Compilations/sec
SQL_SQLCompilationsPersec
Numeric
75
SQL ReCompilations/sec
SQL_SQLReCompilationsPersec
Numeric
76
Auto Param Attempts/sec
SQL_AutoParamAttemptsPersec
Numeric
77
Failed Auto Params/sec
SQL_FailedAutoParamsPersec
Numeric
78
Forced Parameterizations/sec
SQL_ForcedParameterizationsPersec
Numeric
79
Safe Auto Params/sec
SQL_SafeAutoParamsPersec
Numeric
80
Un safe Auto Params/sec
SQL_UnsafeAutoParamsPersec
Numeric
81
SQL Attention rate
SQL_ERR_SQLAttentionrate
Numeric
82
Errors/sec
SQL_ERR_ErrorsPersec
Numeric
83
Backup Restore Throughput/sec
SQL_BackupPerRestoreThroughputPersec
Numeric
84
Bulk Copy Throughput/sec
SQL_BulkCopyThroughputPersec
Bytes
85
DBCC Logical Scan Bytes/sec
SQL_DBCCLogicalScanBytesPersec
Bytes
86
Log Bytes Flushed/sec
SQL_LogBytesFlushedPersec
Bytes
87
Log Flushes/sec
SQL_LogFlushesPersec
Numeric
88
Log Flush Waits/sec
SQL_LogFlushWaitsPersec
Numeric
89
Transactions/sec
SQL_TransactionsPersec
Numeric
90
Granted Workspace Memory
SQL_GrantedWorkspaceMemoryKB
Kilobytes
91
Lock Memory
SQL_LockMemoryKB
Kilobytes
92
Maximum Workspace Memory
SQL_MaximumWorkspaceMemoryKB
Kilobytes
93
Memory Grants Outstanding
SQL_MemoryGrantsOutstanding
Numeric
94
Memory Grants Pending
SQL_MemoryGrantsPending
Numeric
95
Optimizer Memory
SQL_OptimizerMemoryKB
Kilobytes
96
Connection Memory
SQL_ConnectionMemoryKB
Kilobytes
97
Cache Memory
SQL_SQLCacheMemoryKB
Kilobytes
98
Target Server Memory
SQL_TargetServerMemoryKB
Kilobytes
99
Total Server Memory
SQL_TotalServerMemoryKB
Kilobytes
100
% Memory Used by SQL Server
SQL_MemoryPctUsed
Numeric
101
Buffer Free
SQL_BufferFreeKB
Kilobytes
102
Buffer Used
SQL_BufferUsedKB
Kilobytes
103
% Buffer Used
SQL_BufferPctUsed
Numeric
104
Active Temp Tables
SQL_ActiveTempTables
Numeric
105
Transactions
SQL_Transactions
Numeric
106
Temp Tables For Destruction
SQL_TempTablesForDestruction
Numeric
107
Lock waits
SQL_WAIT_Lockwaits
Milliseconds
108
Log Buffer waits
SQL_WAIT_LogBufferwaits
Milliseconds
109
Log Write waits
SQL_WAIT_LogWritewaits
Milliseconds
110
Memory Grant Queue waits
SQL_WAIT_MemoryGrantQueuewaits
Milliseconds
111
Network IO waits
SQL_WAIT_NetworkIOwaits
Milliseconds
112
Non-Page Latch waits
SQL_WAIT_NonPageLatchwaits
Milliseconds
113
Page IO Latch waits
SQL_WAIT_PageIOLatchwaits
Milliseconds
114
Page Latch waits
SQL_WAIT_PageLatchwaits
Milliseconds
115
Thread Safe memory Objects waits
SQL_WAIT_ThreadSafememoryObjectswaits
Milliseconds
116
Transaction Ownership waits
SQL_WAIT_TransactionOwnershipwaits
Milliseconds
117
Wait for the Worker
SQL_WAIT_WaitfortheWorker
Milliseconds
118
Workspace Synchronization waits
SQL_WAIT_WorkspaceSynchronizationwaits
Milliseconds
119
Bytes Received from Replica/sec
SQL_AON_BytesReceivedfromReplicaPersec
Bytes
120
Bytes Sent to Replica/sec
SQL_AON_BytesSenttoReplicaPersec
Bytes
121
Bytes Sent to Transport/sec
SQL_AON_BytesSenttoTransportPersec
Bytes
122
Flow Control/sec
SQL_AON_FlowControlPersec
Milliseconds
123
Flow Control Time ms/sec
SQL_AON_FlowControlTimemsPersec
Numeric
124
Receives from Replica/sec
SQL_AON_ReceivesfromReplicaPersec
Numeric
125
Sends to Replica/sec
SQL_AON_SendstoReplicaPersec
Numeric
126
Sends to Transport/sec
SQL_AON_SendstoTransportPersec
Numeric
127
Resent Messages/sec
SQL_AON_ResentMessagesPersec
Numeric
128
Average Time/Wait
SQL_AON_AverageTimePerWait
Milliseconds
129
Transaction Delay
SQL_AON_TransactionDelay
Milliseconds
130
Mirrored Write Transactions/sec
SQL_AON_MirroredWriteTransactionsPersec
Numeric
131
File Bytes Received/sec
SQL_AON_REP_FileBytesReceivedPersec
Bytes
132
Log Bytes Received/sec
SQL_AON_REP_LogBytesReceivedPersec
Bytes
133
Redo blocked/sec
SQL_AON_REP_RedoblockedPersec
Numeric
134
Log Remaining For Undo
SQL_AON_REP_LogRemainingForUndo
Bytes
135
Log Send Queue
SQL_AON_REP_LogSendQueue
Bytes
136
Redo Bytes Remaining
SQL_AON_REP_RedoBytesRemaining
Bytes
137
Total Log Requiring Undo
SQL_AON_REP_TotalLogRequiringUndo
Bytes
138
SQL Query Response Time
SQL_Response_Time_ms
Milliseconds
139
Avg. Execution Time
SQL_Avg_Excecution_Time_ms
Milliseconds
140
Avg. Worker Time
SQL_AVG_Worker_Time_ms
Milliseconds






3 comments:

  1. Thanks for sharing the very useful info about SQL Server and please keep updating........

    ReplyDelete
  2. Thanks for posting such an interesting inforamtion, Please keep updates us and make us up to date....

    ReplyDelete