SQL Server Performance and Operational Monitoring for DBAs with ManageEngine Applications Manager
By: Jeremy Kadlec | Updated: 2019-05-28 | Comments | Related: More > Monitoring
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.
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.
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:
Let’s review each of these tabs to understand the value from a SQL Server DBA perspective.
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
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.
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.
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.
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.
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
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.
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
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.
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.
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.
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.
When clicking on the History icon for any job, you are able to see information like the following:
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.
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.
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.
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.
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.
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.
How do I get started with ManageEngine Applications Manager?
- Check out the online installation of ManageEngine Applications Manager to begin researching and learning.
- Download your free evaluation to see how the ManageEngine Applications Manager solution will help you.
- Think about all of the challenges you face with SQL Server Performance Monitoring and daily operations.
- Communicate with your team and management about how you think ManageEngine
Applications Manager will help your organization:
- Balance SQL Server Performance Monitoring and daily operational tasks
- Time savings with Alarms and consolidated view of critical data to focus on the most pressing issues
- Depth and breadth of monitoring across the organization
- Internal application based and SQL Server security to balance sharing data and not inadvertently impacting production
- Easily configurable solution to begin monitoring immediately
- Seamlessly share data with your team with direct exports to CSV, PDF, etc.
- Affordable solution starting at $945 USD for 25 instance based monitors – Learn More
- Put ManageEngine Applications Manager through its paces in your environment, share the results with your team and determine your next steps.
- Learn more about ManageEngine Applications Manager
- Schedule a personal demo
- Download Trial
- Customer testimonials and case studies
- Additional resources
- Gartner Magic Quadrant
- Gartner Peer Insights Customer Choice Jan 2019
MSSQLTips.com Product Spotlight sponsored by ManageEngine makers of Applications Manager.
Last Updated: 2019-05-28
About the author
View all my tips