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;
30 Cards in this Set
- Front
- Back
sql statistics object
|
counters to monitor query compilation and the type of request sent to sql server
|
|
SQL Statistics Counters:
1. SQL Compilations/sec 2. SQL Re-Compilations/sec |
1. number of times the compile code path is entered-- should be steady
2. number of times recompiles are triggered |
|
SQL Statistics Counters:
1. failed auto-params/sec 2. safe auto-params/sec 3. unsafe auto-params/sec |
1. failed attempts to reuse cashed plan for a different query.
2. same as above for safe(success) 3. table has characteristics that prevent the plan from being shared |
|
SQL Statistics Counters:
1. auto-param attempts/sec 2. batch requests/sec |
1. total number of auto-param attempts /sec. happens when sql server attempts to use a cached plan for a previously executed query that is similar to the current query.
2. number of batches recieved per sec. high value means good throughput |
|
Replication Snapshot Object:
2 counters |
1. snapshot: delivered cmds/sec
2. snapshot: delivered trans/sec both delivered to distributor |
|
Replication Merge Object
3 counters |
1. conflicts/sec --> conflicts/sec that occured in the publisher/subscriber upload/download process
2. downloaded changes/sec --> number of rows per sec (ins, update, delete) from publisher to subscriber 3. uploaded changes /sec --> rows/sec merged from subscriber to publisher |
|
Replication Logreader Object
3 counters |
1. Logreader: Delivered cmds/sec --> cmds delivered to distributor
2. Logreader: Delivered trans/sec --> trans delivered to distributor 3. Logreader: Delivery Latency --> latency between when a trans is applied an the publisher and delivered to the distributor |
|
Replication Distribution Object
3 counters |
1. Dist: Delivered cmds/sec --> dist. commands delivered/sec to the subscriber
2. Dist: Delivered trans/sec --> same as above for transactions 3. Dist: Deilvery Latency --> time elapsed between transaction is delivered to distributor and applied at subscriber |
|
Replication Agents Object
1 counter |
number of instances of a given agent currently running
|
|
Describe Memory Manager Object Counters
|
counters to monitor overall server memory usage.
|
|
Memory Manager Object Counters:
1. Optimizer Memory 2. SQL Cache memory 3. targer server memory 4. total server memory |
1. total memory used for query optimization
2. total memory used for dynamic sql cache 3. total memory server is willing to consume. 4. total memory server is currently using. |
|
Memory Manager Object Counters:
1. maximum workspace memory 2. memory grants outstanding 3. memory grants pending |
1. max memory available for executing workspace projects.
2. number of processes/sec with a workspace memory grant 3. number of processes/sec waiting for a workspace memory grant. |
|
Memory Manager Object Counters:
1. lock owner blocks 2. lock owner blocks allocated |
1. number lock owner blocks on a server. a lock owner block represents ownership of a lock on an object by an individual thread. if 3 threads have shared locks on the same record this counter is three
3. number of lock blocks allocated |
|
Memory Manager Object Counters:
1. lock blocks 2. lock blocks allocated 3. lock memory |
1. current number of lock blocks in use. lock block = an individual locked resource (table, lage, row etc)
2. number of allocated lock blocks 3. amount of memory used for locks |
|
Memory Manager Object Counters:
1. connection memory 2. granted workspace memory |
1. total amount of dynamic memory sql server is using for connections
2. total amount of memory granted to executing processes such as hash matches, sorts, bcp and index creation |
|
Describe Latches Object Counters
|
counters to monitor latches-- internal sql server resource locks
|
|
Latches Object Counters:
1. average latch wait time. 2. latch waits/sec 3. total latch wait time |
1. how long (average in ms) latch had to wait
2. number batch requests that had to wait 3. total wait time for all latches that had to wait in the last sec |
|
Describe Locks Object
6 applicable resource types |
coutners for info about sql server locks on individual resources:
1. RID 2. key 3. page 4. extent 5. table 6. database |
|
Locks Object Counters:
1. lock requests/sec 2. lock timeouts/sec 3. lock wait time 4. lock waits/sec |
1. number of new locks and lock conversions/sec requested by the lock manager
2. number of lock requests per sec that timeout 3. total wait time for locks last second 4. number of locks per second that had to wait |
|
Locks Object Counters:
1. Average Wait Time 2. number of deadlocks/sec |
1. average wait for each lock that resulted in a wait
2. number of lock requests/sec that resulted in a deadlock |
|
Describe General Statistics Object Counters
|
counters for general server-wide activity
|
|
General Stistics Counters:
1. logins/sec 2. logouts/sec 3. user connections |
1. logins per sec
2. logouts per sec 3. current number of user connections |
|
Describe Database Object Counters
|
counters to monitor bulk insert, backup & restore throughput and transaction log activities
instances of this counter exist for each database |
|
Database Object Counters:
1. Shrink Data Movement Byptes/sec 2. Transactions/sec |
1. amount of data moved/sec for autoshrink or dbcc operations
2. number of transactions started/sec |
|
Database Object Counters:
1. repl.pending xacts 2. repl trans. rate |
1. number of transactions in the transaction log marked for replication and not yet sent to the distributor
2. number of transactions read out of the transaction log and sent to the distribution database |
|
Database Object Counters:
10 counters for the transaction log |
1. Log Bytes per flush
2. log cach hit ration 3. log cach reads/sec 4. log file(s) size 5. log flush wait time 6. log flush waits/sec 7. log flushes/sec 8. log growths 9. log shrinks 10. log truncations 11. percent log used |
|
Database Object Counters:
1. data file(s) size 2. dbcc logical scan bytes/sec |
1. kb of all files in db-- good for tracking tempdb
2. logical read scan bytes/sec resulting from dbcc |
|
Database Object Counters:
1. bulk copy rows/sec 2. bulk copy throughput/sec |
1. bcp number of rows/sec
2. bcp data transfered |
|
Database Object Counters:
1. active transactions 2. backup/restore throughput/sec |
1. number of active trasactions/sec
2. read/write throughput for backup/restore operations |
|
user setable object - 10 counters
|
monitors single integer values returned by sp_user_counter1 through sp_user_counter10. good for monitoring record counts, sums, server variables
|