Sql Server 7 - Performance Monitor 2

Front 1

Describe Cache Manager Object

Back 1

monitors how sql server manages memory for stored procedures, prepared statements and tsql
Instances: ad hoc sql plans, misc. normalized trees, prepared sql plans, procedure plans, rep. procedure plans, trigger plans

Front 2

Cache Manager Object Counters:
1. cache object counts
2. cache pages
3. cache use counts/sec

Back 2

1. number of ojbects found in cache
2. number of pages used by objects in cache
3. times/sec each type of object in the cache has been used

Front 3

Cache Manager Object Counters:
cache hit ration

Back 3

percent of plan pages found in cache

Front 4

Describe Buffer Manager Object

Back 4

counters to monitor sql servers use of memory to store data pages, internal data structures and the proc cache
counter to monitor physical i/o as pages are read and written

Front 5

Buffer Manager Object Counters:
1. readahead pages/sec
2. reserved page count
3. stolen page count

Back 5

1. number of asynchronously prefetched pages before they are encountered
2. number of buffer cach reserved pages
3. number of buffer cache pages stolen to satisfy other requests

Front 6

Buffer Manager Object Counters:
1. page reads/sec
2. page requests/sec
3. page writes/sec

Back 6

1. phystical db page reads/sec
2. number of requests for buffered pages/sec
3. number of db page writes/sec

Front 7

Buffer Manager Object Counters:
1. free buffers
2. lazy writer buffers/sec
3. lazy writes/sec

Back 7

1. number free buffers available
2. number of buffers examined by the lazy writter/sec
3. number of buffers written/sec by the buffer manager lazy writer

Front 8

Buffer Manager Object Counters:
1. extended mem cache hit ratio
2. extended mem cache migrations/sec
3. extended mem requests/sec

Back 8

1. percent of page requests satisfied from the extended memory cache
2. number of pages migrated to extended memory cache/sec
3. number of requests for pages form the extended memory cache

Front 9

Buffer Manager Object Counters:
1. checkpoint writes/sec
2. committed pages

Back 9

1. pages flushed to disk by a checkpoint/sec
2. number of buffer pages committed

Front 10

Buffer Manager Object Counters:
cache size (pages)

Back 10

size of the procudure cache in pages (compound of stuff in cache manager object)

Front 11

Buffer Manager Object Counters:
buffer cache hit ratio

Back 11

percent of pages found in memory without having to go to disk

Front 12

Describe Access Method Object Counters

Back 12

counters to monitor how the logical pages written to a database are accessed

Front 13

Access Method Object Counters:
1. extent deallocations/sec
2. extent allocations/sec

Back 13

1 and 2: number of extents de/allocated/sec from db objects used for storing index or data records

Front 14

Access Method Object Counters:
forward records/sec

Back 14

number of records/sec fetched through forward record pointers

Front 15

Access Method Object Counters:
full scans/sec

Back 15

number of unrestricted full table scans /sec. base table or full index

Front 16

Access Method Object Counters:
index searches/sec

Back 16

number of index searches/sec. used to start range scans, single index record fetches, reposition an index.

Front 17

Access Method Object Counters:
mixed page allocations/sec

Back 17

number of page allocations/sec from mixed extents. used for first 8 pages in an index or table

Front 18

Access Method Object Counters:
1. page deallocations/sec
2. page splits/sec
3. page allocations/sec

Back 18

number of pages allocated, deallocated or split per sec

Front 19

Access Method Object Counters:
1. probe scans/sec
2. range scans/sec

Back 19

1. used to find rows in an index or base table directly
2. number qualified range scans through indexes/sec

Front 20

Access Method Object Counters:
1. scan point revalidations/sec
2. skipped chosted records/sec

Back 20

1. number of times/sec a scan point had to be revalidated to continue to scan
2. chosted records/sec skipped during scan

Front 21

Access Method Object Counters:
table lock escalations/sec

Back 21

number of times locks on a table were escalated

Front 22

Access Method Object Counters:
1. workfiles created/sec
2. worktables created/sec
3. worktables from cache ratio

Back 22

1. number of work files created/sec
2. number of work tables created/sec
3. percent work tables where the initial pages were immediately available in the work table cache

Front 23

Access Method Object Counters:
freespace page fetches/sec

Back 23

number of pages returned/sec by free space scans used to satisfy requests to insert record fragments

Front 24

Access Method Object Counters:
freespace scans/sec

Back 24

number of scans/sec that were initiated to search for free space in which to insert a new record fragment

Front 25

Describe Backup Device Object
1. device throughput bytes/sec

Back 25

counters to monitor devices for backup and restore operations
1. thoughput of read and write operations for backup devices when doing a bacup or a restore

Front 26

NT Counter:
Memory: pages/sec

Back 26

number of pages either retrieved from disk due to faults or written to disk to free space in working set due to page faults. caused by paging

Front 27

NT Counters:
Memory: available bytes

Back 27

number of bytes of memory currently available for use by processes

Front 28

NT Counters:
System: processor queue length

Back 28

number of threads waiting for processor time.

Front 29

NT Counters:
processor: % user time

Back 29

% of time executing user processes (sql server included)

Front 30

NT Counters:
processor: % privledge time

Back 30

% time spent executing kernel commands suck as sql server i/o requests

Front 31

NT Counters:
PhysicalDisk: avg.disk queue length

Back 31

number of system requests on average waiting for disk access.

Front 32

NT Counters:
memory: page faults/sec

Back 32

monitor disk activity caused by memory paging

Front 33

NT Counters:
processor: % processor time

Back 33

amount of time the cpu spends processing a non-idle thread 80% to 90% consistently is bad

Front 34

NT Counters:
spstem: % total processor time

Back 34

average time for processing a thread on all cups in a multi-processor system

Front 35

NT Counters:
physical disk: current disk queue length

Back 35

number of system requests waiting for disk access. should be no more than 1.5-2 x per harddrive spindle.

Front 36

NT Counters:
physical disk: % disk time

Back 36

measures % of time the disk is busy with read/write activity