SQL Server Performance Tuning and Operational Monitoring with ManageEngine Applications Manager
Monitoring Microsoft SQL Server performance problems and daily operational items has become time consuming for my team who supports servers from SQL Server 2012 through 2019. We have tinkered with Extended Events, Profiler, SQL Trace, Performance Monitor (PerfMon), T-SQL scripts, Dynamic Management Views (DMVs), wait statistics, query plans, TempDB usage and PowerShell, but that too has detracted us from solving unexpected issues as well as SQL Server versioning issues. My team needs to focus on performance, query optimization 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 we can scale as we grow beyond having to monitor just SQL Server.
SQL Server performance monitoring 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 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 in the long term 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 tuning and daily operational tasks needed for database administration. ManageEngine Applications Manager supports SQL Server 2012 to 2019 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 provides insight into performance metrics including notification via Alarms. Let’s dive in.
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 150 technologies.
- Application Performance Monitoring - .NET, Java, Ruby on Rails, PHP & node.js
- Operating Systems - Windows, Linux
- Virtualization - VMWare, Hyper-V, XenApp
- Containers - Docker, Kubernetes, Red Hat OpenShift
- Cloud - Azure, AWS, Google Cloud, Oracle Cloud
- Database - SQL Server, Oracle, DB2, MySQL, MongoDB, Sybase, SAP HANA, PostgreSQL, Couchbase, Cassandra, Memcached, Informix and more
Check out all of the supported technologies.
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, the 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.
Alternatively, it is possible to see a graphical layout of your monitors.
As an example, if you click on the server from the prior screen, you land on the main interface to manage SQL Servers using the following tabs:
- Cluster Details
- AlwaysOn Availability Groups
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 Tuning with ManageEngine Applications Manager
Database performance is always critical for SQL Server DBAs. They need to address Transact-SQL performance issues such as query performance, slow execution plans, bottlenecks, deadlocks, and more. ManageEngine Applications Manager recognizes these needs and provides these report types:
- Top SQL 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.
Proactive Alarms with Machine Learning and Anomaly Detection
Alarms are beneficial for SQL Server DBAs to configure and proactively address performance and operational issues. 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. These Alarms can be configured based on a counter as shown below or can be configured on many of the tabs in the interface via the Configure Alarms icons.
In 2020, ManageEngine Applications Manager introduced Machine Learning and Anomaly Detection for their Alarms in order to improve the effectiveness of this functionality.
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 and expose the business to potential risks. ManageEngine Applications Manager enables DBAs to streamline the following daily operations:
- Database - Database usage metrics, data file 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 with the ability to modify in the interface
- Cluster Details - Configuration (Nodes, Storage, Networks, etc.), status and troubleshooting
- AlwaysOn Availability Groups - Monitor the health and availability of the implementation
The ManageEngine Applications Manager Database tab streamlines daily monitoring with insight into the database and transaction log usage and size for each database. This 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, including if the database is offline and 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 database's session level 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, a listing includes 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. The summary includes the current status, last run time, overall health and history. In addition, with the correct permissions, you have the ability to start, stop, delete, enable or disable a Job directly in the application.
When clicking on the History icon for any Job, you can 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's 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 enables 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.
Cluster Details Tab
ManageEngine Applications Manager includes insight into your clustered SQL Servers including:
- Resource Groups
Availability Groups Tab
With the popularity of SQL Server Availability Groups, ManageEngine Applications Manager includes insight into the configuration and health of your implementation. Many organizations use these interfaces to report on RPO and RTO needed to support application uptime.
Performance and Operational Reporting
Reporting is key for status, historical records and general communication for both SQL Server performance and operational needs. ManageEngine Applications Manager can create reports directly from the interfaces above by saving the results to a PDF or CSV file via the Create Report icon on the top right of most interfaces.
Beyond the direct capabilities on the individual interfaces, ManageEngine Applications Manager can also create and schedule reports that can be delivered to team members in PDF format. Below is an example of three reports related to Failed Jobs, SQL Job History and SQL User Report. Some of these reports can be created with existing counters while other reports can accept a SQL Server query as the basis for the report.
Below is a sample Failed Job History report for the last 24 hours which can be very helpful to SQL Server DBAs for troubleshooting failed processes that impact the organization.
ManageEngine Applications Manager also includes database forecasting reports in a graphical manner (as shown below) to help visualize storage needs across numerous databases and time frames to streamline planning.
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, query tuning 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 October 2020
MSSQLTips.com Product Spotlight sponsored by ManageEngine makers of Application Manager.
About the author
View all my tips
Article Last Updated: 2021-01-14