MS SQL DB Servers

Note:
Minimum User Privileges : User should be permitted to access MASTER and MSDB database.
Roles : public + db_datareader should be selected in MASTER and MSDB database

For MS SQL 2005, 2008 and 2012 user role,
Database Accessed: Master
Permit in Database Role : db_datareader and requires VIEW SERVER STATE permission on the server

To grant VIEW SERVER STATE you can use any of the following methods :

1) Execute the following query,
GRANT VIEW SERVER STATE TO username;

2) In SQL management studio for user choose Properties -> Securables -> Click Add ( under securables ) -> choose "All objects of the Types..." -> choose Servers -> choose Grant for "View server state" permission.

Name

Specifies the name of MS SQL server monitor.

Health

Specifies the health (Clear, Warning, Critical) of the MS SQL server.

Type

Specifies the type you are monitoring.

Version

Specifies the version of the database server.

Port

Specifies the port number at which the database server is running.

ODBC Driver Version

Specifies the ODBC driver version used.

Host Name

Specifies the host at which the database server is running.

Host OS

Specifies the OS of the host where the database server is running.

Last Alarm

Specifies the last alarm that was generated for the database server.

Last Polled at

Specifies the time at which the last poll was performed.

Next Poll at

Specifies the time at which the next poll is scheduled.

Availability

Shows the current status of the server - available or not available.

Total Memory

Total amount of dynamic memory the server is currently consuming.

SQL Cache Memory

Total amount of dynamic memory the server is using for the dynamic SQL cache.

Lock Memory

Total amount of dynamic memory the server is using for locks.

Optimizer Memory

Total amount of dynamic memory the server is using for query optimization.

Connection Memory

Total amount of dynamic memory the server is using for maintaining connections.

Granted WorkSpace Memory

Total amount of memory granted to executing processes. This memory is used for hash, sort and create index operations.

Memory Grants Pending

Current number of processes waiting for a workspace memory grant.

Memory Grants Success

Current number of processes that have successfully acquired a workspace memory grant.

Buffer Hit Ratio

Percentage of pages that were found in the buffer pool without having to incur a read from disk.

Page LookUps/Min

Number of requests to find a page in the buffer pool.

Page Reads/Min

Number of physical database page reads issued.

Page Writes/Min

Number of physical database page writes issued.

Total Pages

Number of pages in the buffer pool (includes database, free, and stolen).

Database Pages

Number of pages in the buffer pool with database content.

Free Pages

Total number of pages on all free lists.

Connection Time

Time taken to get connected to the MS SQL database server.

Active Connections

Number of users connected to the system.

Logins/Min

Total number of logins started per minute.

Logouts/Min

Total number of logouts started per minute.

Cache Hit Ratio

Ratio between cache hits and lookups

Cache Used/Min

Times each type of cache object has been used

Cache Count

Number of cache objects in the cache

Cache Pages

Number of 8k pages used by cache objects

Lock Requests/Min

Number of new locks and lock conversions requested from the lock manager.

Lock Waits/Min

Total wait time for locks in the last minute.

Lock Timeouts/Min

Number of lock requests that timed out. This includes internal requests for NOWAIT locks.

Deadlocks/Min

Number of lock requests that resulted in a deadlock.

Average Lock Wait Time

The average amount of wait time for each lock request that resulted in a wait.

Batch Requests/Min

Number of SQL batch requests received by server.

SQL Compilations/Min

Number of SQL compilations.

SQL Recompilations/Min

Number of SQL re-compiles.

AutoParams/Min

Number of auto-parameterization attempts.

Failed AutoParams/Min

Number of failed auto-parameterizations.

Latch Waits/Min

Number of latch requests that could not be granted immediately and had to wait before being granted.

Average Latch Wait Time

Average latch wait time for latch requests that had to wait.

Full Scans/Min

Number of unrestricted full scans. These can either be base table or full index scans.

Range Scans/Min

Number of qualified range scans through indexes.

Probe Scans/Min

Number of probe scans. A probe scan is used to directly look up rows in an index or base table.

DataFile Details

Gives the DataFile size.

Log File Size

Gives the Size of the Log File, used size of the Log File.

Transaction Details

Gives the number of transaction per minute, replication transaction per minute, and the active transactions.

Job Status

Gives the Status of the job

Run date & time

Gives the date & time for which the jobs are scheduled to run.

Job Time

Gives the time taken by the job.

Retries Attempted

Gives the number of times the scheduled jobs attempted to run.