ManageEngine® Applications Manager MS SQL DB Servers | ||
Applications Manager supports monitoring of MS SQL 2000, 2005, 2008, 2012 and 2014 versions.
Availability
tab, gives the Availability history for the past 24 hours or 30 days. Performance
tab gives the Health Status and events for the past 24 hours or 30 days. List
view enables you to perform bulk
admin configurations. Click on the individual monitors listed, to view the
following information.
Note: For MS SQL 2005, 2008 and 2012 user role, To grant VIEW SERVER STATE you can use any of the following methods : 1) Execute the following query, 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.
|
Parameters |
Description |
---|---|
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. |
Parameters |
Description |
---|---|
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. |
Parameters |
Description |
---|---|
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. |
Page Life Expectancy | The number of seconds a page will stay in the buffer pool without references. |
Parameters |
Description |
---|---|
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. |
Parameters |
Description |
---|---|
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 |
Parameters |
Description |
---|---|
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. |
Parameters |
Description |
---|---|
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. |
Parameters |
Description |
---|---|
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. |
Parameters |
Description |
---|---|
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. |
Parameters |
Description |
---|---|
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. |
Log Flush Details | Gives the number of Log Flush/minute, Log Flush waits/minute, and the Log Flush wait time. |
In addition, Applications Manager displays more in-depth performance metrics such as System Tables, Table Relationship, User Tables etc., of the selected database details page.
Parameters |
Description |
System Tables |
|
Table Relationship |
|
User Tables |
|
View Details |
|
Index Details |
|
Indexes that have not been used |
|
Fragmentation Details |
|
Indexes that have been used |
|
Parameters |
Description |
---|---|
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. |
Applications Manager presents a host of performance metrics for the SQL Server. They are:
Top Queries by CPU (displayed by default)
Top Queries by I/O (Input/Output)
Top Queries by CLR
Top Slow Running Queries
Top Queries by most frequently executed
Top Queries by most frequently blocked
Top Queries by Lowest Plan Reuse
Cost of Missing Indexes
Top Components by Memory Usage
Top Waits by Waiting Tasks
Top Waits by Waiting Tasks
Parameters |
Description |
Avg. CPU Time |
Average CPU Time taken to execute the query. |
Total CPU Time |
The cumulative total amount of CPU time that has been spent running this query since the plan was compiled. |
Query |
The SQL Query Text. |
DB Name |
The database against which this query has been executed. |
Last Execution Time |
The last time at which the query started executing. |
Average Execution Time |
Average time taken to execute an individual query. |
Displays information of Top 10 costly queries as measured by the Average Execution Time.
Parameters |
Description |
Avg. Execution Time |
Average time taken for complete execution of this query. |
Maximum Execution Time |
Maximum elapsed time for any complete execution of this query. |
Minimum Execution Time |
Minimum elapsed time for any complete execution of this query. |
No of Executions |
The number of times that the query has been executed since the plan was last compiled. |
Query |
The SQL query text. |
Last Execution Time |
The last time at which the query started executing. |
Displays information of Top 10 queries whose query plan are not cached by the SQL Server. Query plan will be cached by SQL Server for faster query execution.
Parameters |
Description |
Plan Usage |
Number of times this cache object has been used since its inception. |
Cache Object Type |
Type of object in the cache. (Possible Values : Compiled Plan / Compiled Plan Stub / Parse Tree / Extended Proc / CLR Compiled Func / CLR Compiled Proc). |
Query |
The SQL Query Text. |
DB Name |
The Database against which this query has been executed (will be empty for ad hoc and prepared batches). |
Last Execution Time |
The last time at which the query started executing. |
Average Execution Time |
Average time taken to execute an individual query. |
Displays information of the Top 10 costly queries as measured by Average I/O operation (logical read and logical write operations).
Parameters |
Description |
Avg. Logical I/O |
Average number of logical reads and logical writes performed by executions of this query. |
Total I/O |
The cumulative total number of logical reads and logical writes performed by executions of this query since the plan was compiled. |
Query |
The SQL query text. |
DB Name |
The Database against which this query has been executed (will be empty for ad hoc and prepared batches). |
Last Execution Time |
The last time at which the query started executing. |
Average Execution Time |
Average time taken to execute an individual query. |
Parameters |
Description |
Execution Count |
The number of times that the query has been executed since the plan was last compiled. |
Query |
The SQL query text. |
DB Name |
The Database against which this query has been executed (will be empty for ad hoc and prepared batches). |
Last Execution Time |
The last time at which the query started executing. |
Average Execution Time |
Average time taken to execute an individual query. |
Displays information of the Top 10 missing indexes in the SQL Server. These indexes will have varying levels of impact on query performance. From this table, you can find out the most costly missing indexes across all the databases on the server and thus help finding out which missing indexes are likely to have the most significant impact on performance.
Parameters |
Description |
Total Cost |
Overall cost of the queries executing without having the recommended indexes. It simply is a unit, the SQL Server Query Optimizer does its calculations with and is not associated with any type of known measurement unit like seconds or CPU time. Bigger the total cost number, more the resources could be saved after creating the recommended index. You need to check the query execution plan before creating the index for these particular columns and you need not check any other details. Also note that every index has a negative impact on the inserts/updates/deletes on that table and this impact can not be measured. |
Avg. User Impact |
Displays the average percentage of benefit that the user queries could experience if this missing index group was implemented. This value means that the query cost would on average drop by this percentage if this missing index group was implemented. |
Table Name |
Displays the name of table (along with Database Name and Schema Name) in which the index is missing. |
Equality Usage |
Displays the column names for which Index is missing. These column names are used in equality predicates of the form: table.column = constant_value |
Inequality Usage |
Displays the column names for which Index is missing. These column names are used in inequality predicates ( any comparison operator other than "=" ) of the form: table.column > constant_value. |
Include Columns |
Displays the column names for which Index is missing. These column names are Comma-separated list of columns needed as covering columns for the query. |
Displays information of the Top 10 queries having high CLR time (Common Language Runtime inside Microsoft .NET Framework).
Parameters |
Description |
Average CLR |
Average Time consumed inside Microsoft .NET Framework Common Language Runtime (CLR) for executions of this query. |
Total CLR |
The cumulative Total Time consumed inside Microsoft .NET Framework Common Language Runtime (CLR) objects by executions of this query since the plan was compiled. |
DB Name |
The Database against which this query has been executed. |
Last Execution Time |
The last time at which the query started executing. |
Average Execution Time |
Average time taken to execute an individual query. |
Parameters |
Description |
Average Time Blocked |
The average amount of time the query was blocked. |
Total Time Blocked |
The cumulative total amount of time for which the query was blocked. |
Query |
The SQL query text. |
DB Name |
The Database against which this query has been executed. |
Last Execution Time |
The last time at which the query started executing. |
Average Execution Time |
Average time taken to execute an individual query. |
Displays information about the various Memory clerks. Memory clerks allocates resource / memory to SQL memory object during times of need.
Parameters |
Description |
Component Type |
Displays the type of memory clerk. |
Single Pages(KB) |
Displays the Total amount of single page memory allocated in kilobytes (KB) directly from the buffer pool. Its is the memory consumed by this component from the Buffer pool. |
Multi Pages(KB) |
Displays the total amount of multipage memory allocated in (KB) from outside the buffer pool. |
Virtual Memory Reserved(KB) |
Displays the total amount of virtual memory reserved directly by the component that uses this clerk. |
Virtual Memory Committed(KB) |
Displays the total amount of virtual memory that is committed by the memory clerk. This should be always less than the amount of reserved memory. |
AWE Memory Allocated(KB) |
Displays total amount of memory that is allocated by the memory clerk by using Address Windowing Extensions (AWE). |
Shared Memory Allocated(KB) |
Displays the total amount of shared memory that is reserved by a memory clerk memory reserved for use by shared memory and file mapping. |
Shared Memory Committed(KB) |
Displays the total amount of shared memory that is committed by the memory clerk. |
Parameters |
Description |
Wait Type |
Indicates name of the wait type. |
Waiting Tasks |
Displays the number of waits on that particular wait type. |
Wait Time |
Total time in milliseconds for that particular type of wait. |
Average Wait Time |
Average time in milliseconds for that particular type of wait. |
Signal Time |
Displays the difference between the time the waiting thread was signaled and when it started running. |
See Also
Creating New Monitor - MS SQL Database Server
Oracle Coherence DB Servers |
IBM DB2 DB Servers |