Sql Server 7 - Performance Monitor

Front 1

sql statistics object

Back 1

counters to monitor query compilation and the type of request sent to sql server

Front 2

SQL Statistics Counters:
1. SQL Compilations/sec
2. SQL Re-Compilations/sec

Back 2

1. number of times the compile code path is entered-- should be steady
2. number of times recompiles are triggered

Front 3

SQL Statistics Counters:
1. failed auto-params/sec
2. safe auto-params/sec
3. unsafe auto-params/sec

Back 3

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

Front 4

SQL Statistics Counters:
1. auto-param attempts/sec
2. batch requests/sec

Back 4

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

Front 5

Replication Snapshot Object:
2 counters

Back 5

1. snapshot: delivered cmds/sec
2. snapshot: delivered trans/sec

both delivered to distributor

Front 6

Replication Merge Object
3 counters

Back 6

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

Front 7

Replication Logreader Object
3 counters

Back 7

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

Front 8

Replication Distribution Object
3 counters

Back 8

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

Front 9

Replication Agents Object
1 counter

Back 9

number of instances of a given agent currently running

Front 10

Describe Memory Manager Object Counters

Back 10

counters to monitor overall server memory usage.

Front 11

Memory Manager Object Counters:
1. Optimizer Memory
2. SQL Cache memory
3. targer server memory
4. total server memory

Back 11

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.

Front 12

Memory Manager Object Counters:
1. maximum workspace memory
2. memory grants outstanding
3. memory grants pending

Back 12

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.

Front 13

Memory Manager Object Counters:
1. lock owner blocks
2. lock owner blocks allocated

Back 13

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

Front 14

Memory Manager Object Counters:
1. lock blocks
2. lock blocks allocated
3. lock memory

Back 14

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

Front 15

Memory Manager Object Counters:
1. connection memory
2. granted workspace memory

Back 15

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

Front 16

Describe Latches Object Counters

Back 16

counters to monitor latches-- internal sql server resource locks

Front 17

Latches Object Counters:
1. average latch wait time.
2. latch waits/sec
3. total latch wait time

Back 17

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

Front 18

Describe Locks Object
6 applicable resource types

Back 18

coutners for info about sql server locks on individual resources:
1. RID
2. key
3. page
4. extent
5. table
6. database

Front 19

Locks Object Counters:
1. lock requests/sec
2. lock timeouts/sec
3. lock wait time
4. lock waits/sec

Back 19

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

Front 20

Locks Object Counters:
1. Average Wait Time
2. number of deadlocks/sec

Back 20

1. average wait for each lock that resulted in a wait
2. number of lock requests/sec that resulted in a deadlock

Front 21

Describe General Statistics Object Counters

Back 21

counters for general server-wide activity

Front 22

General Stistics Counters:
1. logins/sec
2. logouts/sec
3. user connections

Back 22

1. logins per sec
2. logouts per sec
3. current number of user connections

Front 23

Describe Database Object Counters

Back 23

counters to monitor bulk insert, backup & restore throughput and transaction log activities
instances of this counter exist for each database

Front 24

Database Object Counters:
1. Shrink Data Movement Byptes/sec
2. Transactions/sec

Back 24

1. amount of data moved/sec for autoshrink or dbcc operations
2. number of transactions started/sec

Front 25

Database Object Counters:
1. repl.pending xacts
2. repl trans. rate

Back 25

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

Front 26

Database Object Counters:
10 counters for the transaction log

Back 26

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

Front 27

Database Object Counters:
1. data file(s) size
2. dbcc logical scan bytes/sec

Back 27

1. kb of all files in db-- good for tracking tempdb
2. logical read scan bytes/sec resulting from dbcc

Front 28

Database Object Counters:
1. bulk copy rows/sec
2. bulk copy throughput/sec

Back 28

1. bcp number of rows/sec
2. bcp data transfered

Front 29

Database Object Counters:
1. active transactions
2. backup/restore throughput/sec

Back 29

1. number of active trasactions/sec
2. read/write throughput for backup/restore operations

Front 30

user setable object - 10 counters

Back 30

monitors single integer values returned by sp_user_counter1 through sp_user_counter10. good for monitoring record counts, sums, server variables