ManageEngine® SQLDBManager Plus


Performance

<< Prev

Home

Next >>

 

SQL Server - Performance

 

Note: Performance metrics will not be displayed for SQL Server 2000.

SQLDBManager Plus presents a host of performance metrics for the SQL Server. They are:

Top Queries by CPU

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.

Top Slow Running Queries

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.

Top Queries by Lowest Plan Reuse

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.

Top Queries by I/O

Displays i nformation 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.

Top Queries by Most Frequently Executed

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.

Cost of Missing Indexes

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.

Top Queries by CLR

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.

Top Queries by most frequently blocked

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.

Top Components by Memory Usage

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.

<< Prev

Home

Next >>

Jobs and Backup

Query