Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Scripts          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

SQL Server Performance and Operational Monitoring for DBAs with ManageEngine Applications Manager


By:   |   Last Updated: 2019-05-28   |   Comments   |   Related Tips: More > Monitoring

Problem

Monitoring SQL Server performance and daily operational items has become time consuming for my team supporting servers from SQL Server 2000 through 2017.  We have been tinkering with Extended Events, Profiler, SQL Trace, Performance Monitor, T-SQL scripts and PowerShell, but that too has detracted us from solving unexpected issues as well as SQL Server versioning issues.  I need my team to focus on performance and operational best practices as opposed to building home grown monitoring.  We need a solution we can install on-premises with the ability to monitor servers in our data center and in the cloud, to proactively notify my team of issues and can scale as we grow beyond having to monitor just SQL Server.

Solution

Performance and operational monitoring are critical for every organization supporting SQL Server.  Although, some might consider this a lofty list of requirements, these are necessary for a responsive database team supporting business critical applications.  As you mentioned, there are numerous native options available with Extended Events, Performance Monitor, etc.  But going down this path can be time consuming to build and manage over time especially with numerous SQL Server versions, operating systems and cloud providers.  Beyond the data collection, building a cohesive interface to manage all of the servers is generally not what a DBA wants to spend their time doing.

One solution that I would like to introduce is ManageEngine Applications Manager to address your SQL Server performance and daily operational tasks.  ManageEngine Applications Manager supports SQL Server 2000 to 2017 running on Windows, , VMs and in the cloud.  The solution is easy to download, install and configure to begin monitoring without the need for a consultant or technician.  The installation is on-premises with the ability to manage applications in your data center and in the cloud.  ManageEngine Applications Manager leverages agentless monitoring with recurring polling for data collection as well as real time data for certain portions of the application.  A single web interface enables DBAs to manage all instances and provide insight into performance metrics including notification via Alarms.  Let’s dive in.

SQL Server Environment Overview with ManageEngine Applications Manager

Getting an overview of your SQL Server environment is generally one of the first items DBAs address each morning.  ManageEngine Applications Manager provides the ability to see the overall environment in terms of availability for the last 24 hours or 30 days, performance (Buffer Hit Ratio, User Connections, Total Memory, Connection Time) and a list view that is beneficial for a large environment.  In the image below, we can see the downtime either over the last 24 hours or 30 days which can serve as a launching pad to begin troubleshooting.

ManageEngine Applications Manager Availability over the last 24 hours

As an example, if you click on the server from the prior screen you land on the main interface to manage SQL Servers with the following tabs:

  • Overview
  • Performance
  • Database
  • Sessions
  • Jobs
  • Backup/Restore
  • Replication
  • Users
  • Configuration

Let’s review each of these tabs to understand the value from a SQL Server DBA perspective.

Overview Tab

On the Overview tab, there is a summary of the Health and Availability status for the SQL Server instance in addition to the following:

  • Basic SQL Server Edition, Port, etc.
  • Operating System Information
  • Buffer Cache Hit Ratio and Plan Cache Hit Ratio
  • Memory Usage and Buffer Manager Statistics
  • Access Methods and Cache Details
  • Lock and Latch Details
ManageEngine Applications Manager SQL Server Overview Tab with general statistics and performance metrics

SQL Server Performance Monitoring with ManageEngine Applications Manager

Performance is always critical for SQL Server DBAs.  ManageEngine Applications Manager recognizes these report types:

  • Top Queries by CPU, I/O, CLR, Waiting Tasks, Slow Running Queries, Lowest Plan Reuse
  • Most Frequently Executed Queries, Blocked Queries
  • Cost of Missing Indexes
  • Top Components by Memory Usage

In this example, the Top Queries by CPU report is run on a per SQL Server instance basis.  When you select the report type, you have the ability to filter by database, polling interval and execution time.  In this circumstance a custom period with a calendar is displayed to review historical data.  These reports can be saved to PDF, Excel or emailed to your team to streamline the troubleshooting process.

ManageEngine Applications Manager Top Queries by CPU

Depending on the report, after the graphical reports are displayed, then ManageEngine Applications Manager will drill down into the query to see the code with the corresponding Average CPU Time, Total CPU Time, Average Execution Time, Code, Database Name and Last Execution Time.  This enables you to troubleshoot the issue much faster having all of the pertinent data residing on a single interface.

ManageEngine Applications Manager Top Queries by CPU

Beyond the 10 reports provided on the Performance tab, you also have the ability to build custom queries unique to your environment from the Database Query Monitor interface as shown below.

Configure Custom Query in ManageEngine Applications Manager

Below is output from a sample custom query where alarms can be configured and the overall health can be reported not only at this detailed level but also at the SQL Server instance level.

Output from ManageEngine Applications Manager Custom Query

SQL Server Daily Monitoring with ManageEngine Applications Manager

Although performance is a major focus and gets a tremendous amount of attention for SQL Server DBAs, daily operational tasks can impede the business as well opening up the business to potential risks. ManageEngine Applications Manager enables DBAs to streamline the following daily operations:

  • Daily Operations - Database usage metrics, database and log file statistics, database management activities, DBCC-CHECKDB, indexing command statistics, Database Mirroring and more
  • Sessions - Connection statistics, SQL Server statistics, connections per host and user, session details along with locking and blocked sessions
  • Jobs - All Jobs configured in SQL Server Agent, current status, last run status, last run time, health and availability, history reports and more
  • Backup and Restore - Latest backups and restore of databases with backup age as well as backup / restored date
  • Replication and Log Shipping - Log Shipping statistics, Replication details for Publications and Subscriptions, Replication Agent Status and History
  • Users - Report of available SQL Server users, user management activities including create/delete/enable/disable user(s)
  • Configurations - Server configuration options management

Database Tab

The ManageEngine Applications Manager Database tab streamlines daily monitoring with insight into the database and transaction log usage and size for each database which is beneficial to understand growth trends.  This interface consolidates numerous key database and transaction log usage metrics to make decisions on changing growth parameters, shrinking files and if any of your databases are offline.  At a glance, you are able to determine the overall health of the database, such as if the database is offline and begin to drill into the details to begin troubleshooting.

ManageEngine Applications Manager Database tab

From the initial interface you can uncover the details for each database including:

  • Overview and Health
  • System Tables
  • Index Details
  • Table Relationships
  • Indexes that have not been used
  • User Tables
  • Fragmentation Details
  • View Details
  • Indexes that have been used
ManageEngine Applications Manager Database tab with database details

Below is an example of a detailed report with the User Tables including the total rows and associated size in MB.  This report and the similar set listed above can all be exported to a CSV file to share with your team.

ManageEngine Applications Manager Database tab with user tables

Sessions Tab

If there is an active production issue, understanding what’s happening at a session level for the database in question is critical.  The Sessions tab in ManageEngine Applications Manager includes Connection and SQL Statistics level information as well as User Connections, Logins, Recompilations, allocated locks, blocking and more to quickly review the active connections, drill into particular connections quickly to understand the code being run and take action such as killing a SPID.

ManageEngine Applications Manager Sessions Tab

On the bottom portion of the interface you get a listing of all of the spids with the associated application, performance metrics, etc.  When clicking on any spid you drill down into the current buffer code as well as an option to KILL the spid if you have permissions.

ManageEngine Applications Manager Sessions Tab

Jobs Tab

Jobs are the lifeblood of many key business and IT processes.  The Jobs tab of ManageEngine Applications Manager simplifies the management and monitoring of SQL Server Agent Jobs by summarizing key information in a single interface including the current status, last run time, overall health and history.

ManageEngine Applications Manager Jobs Tab

When clicking on the History icon for any job, you are able to see information like the following:

ManageEngine Applications Manager Job History

Backup\Restore Tab

Validating daily SQL Server backups is critical as a last means for business continuity.  ManageEngine Applications Manager consolidates SQL Server backup start and end times, type, size, location and backup age in hours. This is valuable to understand where the backup is stored and if the backup process is working as designed.  By configuring the backup age, we can easily determine if the backup process has failed, is running longer than expected, etc.

ManageEngine Applications Manager Backups Tab

Replication Tab

Similar to SQL Server backups, Log Shipping and Replication can be used for disaster recovery or reporting needs.  Gaining insight into these processes can be time consuming to review.  Once again, ManageEngine Applications Manager consolidates the key processes for both technologies on one interface.

ManageEngine Applications Manager Replication Tab
ManageEngine Applications Manager Replication Agents

Users Tab

Managing users in a SQL Server database is critical to ensure proper security at the database level.  ManageEngine Applications Manager provides a single interface to review, edit and create users (with the appropriate SQL Server privileges) at a database level.

ManageEngine Applications Manager Users Tab

Clicking on the pencil in the right column will enable you to map Logins to Users as well as assign permissions to Server and Database Roles as shown below.

ManageEngine Applications Manager Map Logins and Users to assign permissions

Keep in mind this level of security is dependent on your permissions in ManageEngine Applications Manager and SQL Server.  For example, the Operator and User roles in ManageEngine Applications Manager are read-only, which will prevent access to creating and modifying permissions.

ManageEngine Applications Manager Proactive Alerts

Proactive Alarms are beneficial for SQL Server DBAs to configure and address once an alarm is raised. ManageEngine Applications Manager includes Alarms that are universally configured for events such as a database offline, database creation, database drop, etc. and has the flexibility to configure alarms for specific events, instances, databases, new users, dropped users and more.  These Alarms enable DBAs to focus on items that are directly impacting the business and jump into ManageEngine Applications Manager for more details.

ManageEngine Applications Manager Configure Alarms
ManageEngine Applications Manager Configure Alarms

What applications does ManageEngine Applications Manager monitor?

As SQL Server Professionals we have a laser-focused purpose, but the databases we manage interact with numerous applications.  This is where ManageEngine Applications Manager can provide a great deal of value to the entire organization with end to end monitoring of more than 100 products.

  • Application Performance Monitoring – .NET, Java, Ruby on Rails, PHP & node.js
  • Operating Systems – Windows, Linux, Containers, Docker, VMWare, Hyper-V, XenApp
  • Cloud – Azure, AWS, RDS
  • Database – SQL Server, Oracle, DB2, MySQL, MongoDB, Sybase, SAP HANA, PostgreSQL, Couchbase, Cassandra, Memcached, Informix and more

Check out all of the supported technologies.

ManageEngine Applications Manager Monitored Applications

How do I get started with ManageEngine Applications Manager?

  1. Check out the online installation of ManageEngine Applications Manager to begin researching and learning.
  2. Download your free evaluation to see how the ManageEngine Applications Manager solution will help you.
  3. Think about all of the challenges you face with SQL Server Performance Monitoring and daily operations.
  4. Communicate with your team and management about how you think ManageEngine Applications Manager will help your organization:
    1. Balance SQL Server Performance Monitoring and daily operational tasks
    2. Time savings with Alarms and consolidated view of critical data to focus on the most pressing issues
    3. Depth and breadth of monitoring across the organization
    4. Internal application based and SQL Server security to balance sharing data and not inadvertently impacting production
    5. Easily configurable solution to begin monitoring immediately
    6. Seamlessly share data with your team with direct exports to CSV, PDF, etc.
    7. Affordable solution starting at $945 USD for 25 instance based monitors – Learn More
  5. Put ManageEngine Applications Manager through its paces in your environment, share the results with your team and determine your next steps.
Next Steps

MSSQLTips.com Product Spotlight sponsored by ManageEngine makers of Applications Manager.



Last Updated: 2019-05-28


get scripts

next tip button



About the author
MSSQLTips author Jeremy Kadlec Since 2002, Jeremy Kadlec has delivered value to the global SQL Server community as an MSSQLTips.com co-founder and Edgewood Solutions SQL Server Consultant.

View all my tips
Related Resources




Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



Learn more about SQL Server tools