Study your flashcards anywhere!

Download the official Cram app for free >

  • Shuffle
    Toggle On
    Toggle Off
  • Alphabetize
    Toggle On
    Toggle Off
  • Front First
    Toggle On
    Toggle Off
  • Both Sides
    Toggle On
    Toggle Off
  • Read
    Toggle On
    Toggle Off

How to study your flashcards.

Right/Left arrow keys: Navigate between flashcards.right arrow keyleft arrow key

Up/Down arrow keys: Flip the card between the front and back.down keyup key

H key: Show hint (3rd side).h key

A key: Read text to speech.a key


Play button


Play button




Click to flip

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