Server Overview 2 (PI_SRV2)
- Organization of this page
Function
The Server Overview 2(PI_SRV2) record indicates overall information related to Microsoft SQL Server, such as rate of cache hits and I/O. The Server Overview 2(PI_SRV2) record acquires from the Microsoft SQL Server the values of the fields which store the cumulative values for "/sec" counters and uses those values to calculate the per-sec value. Only one record is created.
Default and changeable values
Item |
Default value |
Changeable |
---|---|---|
Collection Interval |
60 |
Y |
Collection Offset |
0 |
Y |
Log |
No |
Y |
LOGIF |
(Blank) |
Y |
Over 10 Sec Collection Time |
No |
N |
ODBC key field
None
Lifetime
From the start until stop of a Microsoft SQL Server instance
Record size
-
Fixed part: 1,169 bytes
-
Variable part: 0 bytes
Fields
View name (Manager name) |
Description |
Summary |
Format |
Delta |
Data source |
|
---|---|---|---|---|---|---|
Microsoft SQL Server version 2014 or earlier |
Microsoft SQL Server version 2016 or later |
|||||
Avg Cache Scan (CACHE_AVG_FREE_PAGE_SCAN) |
This field is reserved and cannot be used. |
AVG |
-- |
-- |
-- |
|
Avg Latch Waits Time (AVG_LATCH_WAIT_TIME) |
Average latch wait time (milliseconds)#2 |
AVG |
double |
Yes#5 |
LATCH_WAIT_TIME / LATCH_WAIT_SEC |
|
Cache Buffers Free (CACHE_NUMBER_OF_FREE_BUFFERS) |
Number of data cache buffers inside a free pool at the time of data collection. This item is always 0.#2 |
AVG |
ulong |
No |
master..sysperfinfo.free pages |
sys.dm_os_performance_counters.free pages |
Cache Hit % (CACHE_HIT_RATIO) |
Rate at which data pages were found inside the data cache#2 |
AVG |
double |
No |
master..sysperfinfo.buffer cache hit ratio / buffer cache hit ratio base * 100 |
sys.dm_os_performance_counters.buffer cache hit ratio / buffer cache hit ratio base * 100 |
Command Queue Length (NET_COMMAND_QUEUE_LENGTH) |
This field is reserved and cannot be used. |
AVG |
-- |
-- |
-- |
|
Compilations/sec (COMPILE_SEC) |
Number of SQL compilations per second#2 |
AVG |
double |
Yes#5 |
master..sysperfinfo.sql compilations/sec |
sys.dm_os_performance_counters.sql compilations/sec |
Compilations % (COMPILE_RATIO) |
Ratio of SQL compilation processes to Transact-SQL command batches executed#2 |
AVG |
double |
Yes#5 |
COMPILE_SEC / IO_TRANSACTIONS_PER_SEC * 100 |
|
Full Scans/sec (FULL_SCAN_SEC) |
Number of full scans per second (base table full scan or full index full scan)#2 |
AVG |
double |
Yes#5 |
master..sysperfinfo.full scans/sec |
sys.dm_os_performance_counters.full scans/sec |
Index Searches/sec (INDEX_SEARCH_SEC) |
Number of index searches per second#2 |
AVG |
double |
Yes#5 |
master..sysperfinfo.index searches/sec |
sys.dm_os_performance_counters.index searches/sec |
Index Searches Efficiency (INDEX_EFFICIENCY) |
Number of index scans executed during one full scan#2 |
AVG |
double |
Yes#5 |
INDEX_SEARCH_SEC / FULL_SCAN_SEC |
|
Latch Waits/sec (LATCH_WAIT_SEC) |
Number of latch waits per second#2 |
AVG |
double |
Yes#5 |
master..sysperfinfo.latch waits/sec |
sys.dm_os_performance_counters.latch waits/sec |
Latch Wait Time (LATCH_WAIT_TIME) |
Latch wait time within an interval (milliseconds)#2 |
AVG |
double |
Yes#5 |
master..sysperfinfo.total latch wait time (ms) |
sys.dm_os_performance_counters.total latch wait time (ms) |
Lazy Writes/sec (IO_LAZY_WRITES_PER_SEC) |
Number of pages per second flushed by Lazy Writer to a disk within an interval (1 page equals 8 kilobytes)#2 |
AVG |
double |
Yes#5 |
master..sysperfinfo.Lazy writes/sec |
sys.dm_os_performance_counters.lazy writer buffers/sec |
Log Writes/sec (IO_LOG_WRITES_PER_SEC) |
Number of log pages per second physically written onto a disk within an interval#2 |
AVG |
double |
Yes#5 |
master..sysperfinfo.log flushes/sec |
sys.dm_os_performance_counters.log flushes/sec |
Max Cache Scan (CACHE_MAX_FREE_PAGE_SCAN) |
This field is reserved and cannot be used. |
HI |
-- |
-- |
-- |
|
Max Tempdb Space Used Mbytes (MAX_TEMPDB_SPACE_USED_MB) |
Maximum value of the space used in tempdb within an interval (in megabytes) |
HI |
double |
No |
master..sysperfinfo.data file(s) size (kb) / 1024 |
sys.dm_os_performance_counters.data file(s) size (kb) / 1024 |
Max Users Conn'd (MAX_USERS_CONNECTED) |
Maximum number of user connections established within an interval |
HI |
ulong |
No |
master..sysperfinfo.user connections |
sys.dm_os_performance_counters.User connections |
Net Reads/sec (NET_NETWORK_READS_PER_SEC) |
This field is reserved and cannot be used. |
AVG |
-- |
-- |
-- |
|
Net Writes/sec (NET_NETWORK_WRITES_PER_SEC) |
This field is reserved and cannot be used. |
AVG |
-- |
-- |
-- |
|
Page Reads/sec (IO_PAGE_READS_PER_SEC) |
Number of physical pages read per second within an interval#2 |
AVG |
double |
Yes#5 |
master..sysperfinfo.page reads/sec |
sys.dm_os_performance_counters.page reads/sec |
Page Writes/sec (IO_PAGE_WRITES_PER_SEC) |
Number of physical pages written per second within an interval#2 |
AVG |
double |
Yes#5 |
master..sysperfinfo.page writes/sec |
sys.dm_os_performance_counters.page writes/sec |
RA Pages Fetched into Cache/sec (RA_PAGES_FETCHED_INTO_CACHE_PER_SEC) |
Number of pages per second prefetched into the cache by Read Ahead Manager within an interval#2 |
AVG |
double |
Yes#5 |
master..sysperfinfo.readahead pages/sec |
sys.dm_os_performance_counters.readahead pages/sec |
RA Pages Found in Cache/sec (RA_PAGES_FOUND_IN_CACHE_PER_SEC) |
This field is reserved and cannot be used. |
AVG |
-- |
-- |
-- |
|
RA Physical Reads/sec (RA_PHYSICAL_READS_PER_SEC) |
Number of physical read operations per second issued by Read Ahead Manager within an interval. (A single read operation involves 8 pages, each of which is 8 kilobytes)#2 |
AVG |
double |
Yes#5 |
master..sysperfinfo.page reads/sec |
sys.dm_os_performance_counters.page reads/sec |
RA Slots Used (RA_SLOTS_USED) |
This field is reserved and cannot be used. |
COPY |
-- |
-- |
-- |
|
Reads Pending (IO_OUTSTANDING_READS) |
This field is reserved and cannot be used. |
AVG |
-- |
-- |
-- |
|
Record Time (RECORD_TIME) |
Interval end time (GMT format)#1 |
COPY |
time_t |
No |
Agent Collector |
|
Record Type (INPUT_RECORD_TYPE) |
Record type (always SERV)#1 |
COPY |
char(8) |
No |
Agent Collector |
|
Start Time (START_TIME) |
Interval start time (GMT format)#1 |
COPY |
time_t |
No |
Agent Collector |
|
Trans/sec (IO_TRANSACTIONS_PER_SEC) |
Number of Transact-SQL command batches per second executed within an interval#2 |
AVG |
double |
Yes#5 |
master..sysperfinfo.batch requests/sec |
sys.dm_os_performance_counters.batch requests/sec |
User Conns (USER_CONNECTIONS) |
Number of client connections#1 |
COPY |
ulong |
No |
master..sysperfinfo.user connections |
sys.dm_os_performance_counters.User connections |
Writes Pending (IO_OUTSTANDING_WRITES) |
This field is reserved and cannot be used. |
AVG |
-- |
-- |
-- |
- #5
-
The delta is calculated based on the changed amount of data acquired from Microsoft SQL Server data base.