Use LEFT and RIGHT arrow keys to navigate between flashcards;
Use UP and DOWN arrow keys to flip the card;
H to show hint;
A reads text to speech;
36 Cards in this Set
- Front
- Back
Describe Cache Manager Object
|
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 |
|
Cache Manager Object Counters:
1. cache object counts 2. cache pages 3. cache use counts/sec |
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 |
|
Cache Manager Object Counters:
cache hit ration |
percent of plan pages found in cache
|
|
Describe Buffer Manager Object
|
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 |
|
Buffer Manager Object Counters:
1. readahead pages/sec 2. reserved page count 3. stolen page count |
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 |
|
Buffer Manager Object Counters:
1. page reads/sec 2. page requests/sec 3. page writes/sec |
1. phystical db page reads/sec
2. number of requests for buffered pages/sec 3. number of db page writes/sec |
|
Buffer Manager Object Counters:
1. free buffers 2. lazy writer buffers/sec 3. lazy writes/sec |
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 |
|
Buffer Manager Object Counters:
1. extended mem cache hit ratio 2. extended mem cache migrations/sec 3. extended mem requests/sec |
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 |
|
Buffer Manager Object Counters:
1. checkpoint writes/sec 2. committed pages |
1. pages flushed to disk by a checkpoint/sec
2. number of buffer pages committed |
|
Buffer Manager Object Counters:
cache size (pages) |
size of the procudure cache in pages (compound of stuff in cache manager object)
|
|
Buffer Manager Object Counters:
buffer cache hit ratio |
percent of pages found in memory without having to go to disk
|
|
Describe Access Method Object Counters
|
counters to monitor how the logical pages written to a database are accessed
|
|
Access Method Object Counters:
1. extent deallocations/sec 2. extent allocations/sec |
1 and 2: number of extents de/allocated/sec from db objects used for storing index or data records
|
|
Access Method Object Counters:
forward records/sec |
number of records/sec fetched through forward record pointers
|
|
Access Method Object Counters:
full scans/sec |
number of unrestricted full table scans /sec. base table or full index
|
|
Access Method Object Counters:
index searches/sec |
number of index searches/sec. used to start range scans, single index record fetches, reposition an index.
|
|
Access Method Object Counters:
mixed page allocations/sec |
number of page allocations/sec from mixed extents. used for first 8 pages in an index or table
|
|
Access Method Object Counters:
1. page deallocations/sec 2. page splits/sec 3. page allocations/sec |
number of pages allocated, deallocated or split per sec
|
|
Access Method Object Counters:
1. probe scans/sec 2. range scans/sec |
1. used to find rows in an index or base table directly
2. number qualified range scans through indexes/sec |
|
Access Method Object Counters:
1. scan point revalidations/sec 2. skipped chosted records/sec |
1. number of times/sec a scan point had to be revalidated to continue to scan
2. chosted records/sec skipped during scan |
|
Access Method Object Counters:
table lock escalations/sec |
number of times locks on a table were escalated
|
|
Access Method Object Counters:
1. workfiles created/sec 2. worktables created/sec 3. worktables from cache ratio |
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 |
|
Access Method Object Counters:
freespace page fetches/sec |
number of pages returned/sec by free space scans used to satisfy requests to insert record fragments
|
|
Access Method Object Counters:
freespace scans/sec |
number of scans/sec that were initiated to search for free space in which to insert a new record fragment
|
|
Describe Backup Device Object
1. device throughput bytes/sec |
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 |
|
NT Counter:
Memory: pages/sec |
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
|
|
NT Counters:
Memory: available bytes |
number of bytes of memory currently available for use by processes
|
|
NT Counters:
System: processor queue length |
number of threads waiting for processor time.
|
|
NT Counters:
processor: % user time |
% of time executing user processes (sql server included)
|
|
NT Counters:
processor: % privledge time |
% time spent executing kernel commands suck as sql server i/o requests
|
|
NT Counters:
PhysicalDisk: avg.disk queue length |
number of system requests on average waiting for disk access.
|
|
NT Counters:
memory: page faults/sec |
monitor disk activity caused by memory paging
|
|
NT Counters:
processor: % processor time |
amount of time the cpu spends processing a non-idle thread 80% to 90% consistently is bad
|
|
NT Counters:
spstem: % total processor time |
average time for processing a thread on all cups in a multi-processor system
|
|
NT Counters:
physical disk: current disk queue length |
number of system requests waiting for disk access. should be no more than 1.5-2 x per harddrive spindle.
|
|
NT Counters:
physical disk: % disk time |
measures % of time the disk is busy with read/write activity
|