Hitachi

JP1 Version 12 JP1/Performance Management - Agent Option for Microsoft(R) SQL Server


Server Overview (PI_SERV)

Organization of this page

Function

The Server Overview (PI_SERV) record indicates overall information related to Microsoft SQL Server, such as rate of cache hits and I/O. 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

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

No

LATCH_WAIT_TIME / LATCH_WAIT

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

(COMPILE)

Total number of SQL compilations#2

HI

double

No

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

No

COMPILE / IO_TRANSACTIONS_PER_SEC * 100

Full Scans

(FULL_SCAN)

Total number of full scans (base table full scan or full index full scan)#2

HI

double

No

master..sysperfinfo.full scans/sec

sys.dm_os_performance_counters.full scans/sec

Index Searches

(INDEX_SEARCH)

Total number of index searches#2

HI

double

No

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

No

INDEX_SEARCH / FULL_SCAN

Latch Waits

(LATCH_WAIT)

Total number of latch waits#2

HI

double

No

master..sysperfinfo.latch waits/sec

sys.dm_os_performance_counters.latch waits/sec

Latch Wait Time

(LATCH_WAIT_TIME)

Total latch wait time (milliseconds)#2

HI

double

No

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)

Total number of pages flushed by Lazy Writer to a disk (1 page equals 8 kilobytes)#2

AVG

double

No

master..sysperfinfo.Lazy writes/sec

sys.dm_os_performance_counters.lazy writer buffers/sec

Log Writes/sec

(IO_LOG_WRITES_PER_SEC)

Total number of log pages physically written onto a disk#2

AVG

double

No

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)

Total number of physical page read operations executed#2

AVG

double

No

master..sysperfinfo.page reads/sec

sys.dm_os_performance_counters.page reads/sec

Page Writes/sec

(IO_PAGE_WRITES_PER_SEC)

Total number of physical page write operations executed#2

AVG

double

No

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)

Total number of pages prefetched into the cache by Read Ahead Manager#2

AVG

double

No

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)

Total number of physical read operations issued by Read Ahead Manager. (A single read operation involves 8 pages, each of which is 8 kilobytes) #2

AVG

double

No

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)

Total number of Transact-SQL command batches executed#2

AVG

double

No

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

--

--

--