Nimsoft MSSQL checks
8:33 PM|
Check name
|
Active
|
Description
|
|
check_dbalive
|
Yes
|
Checks the connectivity to every configured server. In case no connection is possible an alert is issued.
|
|
database_size
|
No
|
This checkpoint
monitors the space used by the respective database files, data and log
files together. A notification will be issued whenever a particular
size is exceeded.
|
|
buf_cachehit_ratio
|
Yes
|
The buffer cache-hit
ratio should be = 80% most of the time. This checkpoint monitors the
percentage of pages found in the buffer cache without having to read
from disk. The ratio is the interval number of cache hits divided by the
interval number of cache lookups. Because reading from the cache is
much less expensive than reading from disk, you want this ratio to be
high. Generally, you can increase the buffer cache hit ratio by
increasing the amount of memory available to SQL Server.
|
|
database_state
|
Yes
|
Checks the current status for every database.
|
|
log_cachehit_ratio
|
No
|
The log cache-hit ratio
should be = 80%. This checkpoint monitors the percentage of pages found
in the log cache without having to read from disk. The ratio is the
interval number of cache hits divided by the interval number of cache
lookups. Because reading from the cache is much less expensive than
reading from disk, you want this ratio to be high. Generally, you can
increase the log cache hit ratio by increasing the amount of memory
available to SQL Server.
|
|
login_count
|
Yes
|
Monitors number of users having open connection to the server at the moment of the snapshot.
|
|
active_users
|
Yes
|
Monitors number of users processing a transaction at the moment of the snapshot.
|
|
lock_requests
|
Yes
|
Monitors number of lock
requests per second in interval. The number of lock requests helps you
to analyze timeout or deadlock situations.
|
|
deadlocks
|
Yes
|
Monitors number of
deadlocks per second in interval. Deadlocks should never occur. In case
of deadlocks check you application and database design to avoid
bottlenecks
|
|
lock_timeouts
|
Yes
|
Monitors number of
timeouts per second in interval. Timeouts should seldom occur. In case
of timeouts check you application and database design to avoid
bottlenecks
|
|
lock_waits
|
Yes
|
Monitors number of lock
waits per second in interval. High number can be a reason for
performance degradations. Check your application and database design to
avoid bottlenecks.
|
|
transactions
|
Yes
|
Monitors number of transactions per second in interval.
|
|
log_flush_waits
|
No
|
This checkpoint
monitors the number of commits per second waiting on the log flush in
interval. When commits are waiting for log flushes, the log device is
usually the bottleneck.
|
|
page_reads
|
Yes
|
This checkpoint
monitors the number of physical database page reads that are issued per
second in interval. Since physical I/O is expensive, you may be able to
minimize the cost, either by using a larger data cache, intelligent
indexes, more efficient queries, or by changing the database design.
|
|
page_writes
|
Yes
|
This checkpoint
monitors the number of database page writes that are issued per second
in interval. Page writes are generally expensive. Reducing page-write
activity is important for optimal tuning. One way to do this is to
ensure that you do not run out of free buffers in the free buffer pool.
If you do, page writes will occur while waiting for an unused cache
buffer to flush.
|
|
latch_waits
|
Yes
|
This checkpoint
monitors the number of latch requests in interval that could not be
granted immediately and had to wait before being granted. If this
number is high the system is generally experiencing a low cache hit
ratio and is being forced to perform physical I/Os. Add more memory or
increase bandwidth of your system.
|
|
full_scans
|
No
|
This checkpoint
monitors the number of full table or index scans per second in interval.
If this value is high (2-10) then you need to analyze your queries.
|
|
database_count
|
Yes
|
This checkpoint counts
the number of databases registered with the SQL Server. You may want to
get a notification whenever a database is added (or removed).
|
|
server_startup
|
Yes
|
Check for when the
instance was last started. If your company has a policy that a server
should be rebooted every 3 months, a warning can be generated e.g a week
before.
|
|
log_file_growths
|
No
|
This checkpoint
monitors the number of times in interval the transaction log for the
database has been expanded. If this happens more often you should
consider resizing your log files.
|
|
log_file_shrinks
|
No
|
This checkpoint
monitors the number of times in interval the transaction log for the
database has been decreased. If this happens more often you should
consider resizing your log files.
|
|
lock_memory
|
Yes
|
This checkpoint
monitors amount of allocated lock memory in Kb. Lock memory is usually
allocated automatically, but you can limit it by specifying parameter
'locks'. The amount of memory in Kb used is calculated by 'locks*96/1024
+ overhead' (about 1-2%).
|
|
connection_memory
|
Yes
|
This checkpoint monitor’s amount of memory in Kb used to maintain connections to SQL Server.
|
|
optimizer_memory
|
Yes
|
This checkpoint monitors amount of memory in Kb used for SQL optimizer.
|
|
sqlcache_memory
|
Yes
|
This checkpoint monitors amount of memory in Kb used for SQL statement cache.
|
|
total_memory
|
Yes
|
This checkpoint monitors total amount of dynamic memory (in kilobytes) the server is using currently.
|
|
locks_used
|
Yes
|
This checkpoint
monitors % of used lock and lock owner blocks used. Number of available
lock blocks can be limited by using parameter 'locks'.
|
|
workspace_memory
|
Yes
|
This checkpoint
monitors amount of memory in Kb used for executing processes such as
hash, sort, bulk copy, and index creation operations.
|
|
average_waittime
|
Yes
|
This checkpoint
monitors average lock wait time in ms in interval. High wait time will
cause performance degradation, consider increase number of locks
available or enlarge available computer memory.
|
|
server_cpu
|
Yes
|
This checkpoint monitors % of CPU usage by SQL Server instance in interval.
|
|
server_io
|
Yes
|
This checkpoint monitors % of I/O busy for SQL Server instance in interval.
|
|
free_connections
|
Yes
|
This checkpoint monitors % free connections to SQL Server instance, specified by parameter 'user connections' (max. 32676).
|
|
user_cpu
|
Yes
|
This checkpoint monitors % of CPU usage by user in interval.
|
|
user_waits
|
Yes
|
This checkpoint monitors the tile any request has to wait before SQL acknowledges it.
|
|
locked_users
|
Yes
|
This checkpoint
monitors the number of users suspended by locks at the moment of
snapshot. Also, the blocked user and its current SQL are used.
|
|
blocked_users
|
Yes
|
This checkpoint monitors the number of users blocked at the moment of snapshot.
|
|
av_fragmentation
|
No
|
This checkpoint monitor of index and table based on % set.
|
|
logic_fragment
|
No
|
This checkpoint
monitors the number of cluster index pages that are out of order. Any
number higher than 10% indicates external fragmentation, the index
should be rebuild.
|
|
scan_density
|
No
|
This checkpoint
monitors the ratio between the best number of extents to the actual
number of extents. It should be near 100%, lower number indicates
external fragmentation, and the object should be reorganized.
|
|
free_space
|
No
|
This checkpoint
monitors the amount of free space in database data files in %. If there
is at least one file with "unlimited" growth, the space in the whole
database is considered as 100% free. If you are using file groups, this
could be misleading; therefore you should deactivate this checkpoint and
use only the "fg_free_space" checkpoint.
|
|
alloc_space
|
No
|
This checkpoint
monitors the amount of free space in database in allocated data files in
%. This checkpoint doesn't consider "unlimited" growth.
|
|
fg_free_space
|
No
|
This checkpoint
monitors the amount of free space in database file groups in %. If there
is at least one file with "unlimited" growth in a file group, the space
in this file group is considered as 100% free.
|
|
table_space
|
No
|
This checkpoint monitor’s amount of space (in Kb) reserved for a particular table in a database.
|
|
logfile_usage
|
No
|
This checkpoint
monitors the amount of free space in transaction log in %. If there is
at least one transaction log file with "unlimited" growth in a database,
the space in its transaction log is considered as 100% free.
|
|
logfile_size
|
No
|
This checkpoint
monitors the size of transaction log in MB. If there is at least one
transaction log file with "unlimited" growth in a database.
|
|
backup_status
|
Yes
|
This checkpoint monitors the full backups status based on hours
|
|
transaction_backup_status
|
Yes
|
This checkpoint monitors the transaction log backups status based on min/hours
|
|
differential_backup_status
|
No
|
This checkpoint monitors the differential backups status based on hours
|
|
long_queries
|
No
|
This checkpoint monitors queries that are running longer than a predefined time limit
|
|
long_jobs
|
No
|
This checkpoint monitors jobs that are running longer than a predefined time limit
|
|
active_connection_ratio
|
Yes
|
This checkpoint monitors the avg connection to the instance
|
|
mirror_state
|
No
|
This checkpoint
monitors mirror state (Synchronizing, Synchronized, Suspended,
Pending_Failover and Disconnected) of mirrored databases.
|
|
mirror_witness_server
|
No
|
This checkpoint monitors state of the witness server in the database mirroring session of the database.
|
|
mirror_sqlinstance
|
No
|
This checkpoint monitors availability of SQL server instance hosting the mirror database.
|
0 comments