Monitor, Manage and Improve Database Performance with Foglight for Databases
As our SQL Server environment grows, we need to focus on cost and time savings to be productive and an asset to the organization. How can I as a Database Administrator continue to do more for my team as we expand into cloud platforms (Azure, AWS, etc.) and other DBMS platforms (Oracle, MySQL, etc.) in the future? Application performance is critical to the organization, but managing scripts, continuously collecting data and manual reporting is overwhelming. We know this will grow even more complex as the business scales. We need a single pane of glass for monitoring, management and optimization of our environment to quickly resolve performance issues.
Around the globe, SQL Server professionals are being asked to support more applications, infrastructure and code, while continuously ensuring all applications are performing optimally. That is a tough mission to uphold without a means to monitor, diagnose and tune each layer of the application. One solution that has been helping SQL Server professionals meet this mission is Quest Foglight. In this article, we will to outline the top functionality that Foglight delivers to reduce costs, improve productivity and maximize performance.
The Foglight Global View, is the command center for your entire SQL Server environment. This single pane of glass provides complete insight into the environment to understand the performance, alarms, uptime and more. The value of this interface is the ability to hone in on the issues and begin troubleshooting.
The Foglight Global View includes the following:
- Server severity state
- Host name with short cuts to the Replication Monitor, Quick View performance metrics and dashboard shortcuts (Overview, SQL Performance, Databases, Blocking, Sessions, Jobs and Operating System)
- SQL Server version
- Date since last downtime
- Current workload
- Count of database alarms (fatal, warning and critical) with the ability to drill into these items to begin troubleshooting
SQL Server Instance Level Metrics
From the Global View, any server can be clicked on to get more insight into the environment. The pane of glass now focuses on an individual instance where much of the detail from the Global View comes to light. Beyond the counters and metrics, the Top 10 SQL Statements are also listed with the active time and number of executions. This enables a DBA or Developer to begin troubleshooting specific SQL queries or stored procedures to improve query performance.
There are also eight menus across the top of the page from SQL PI (Performance Investigator) related to performance, availability and configuration. The options from these menus enable you to gain deep insight into the instance.
Check out this video on Foglight for SQL Server Overview.
SQL PI (Performance Investigator) enables DBAs and Developers to slice and dice performance data more than a dozen different ways (instance, SQL Statements, TSQL Batches, Databases, Programs, Users, Client Machines, Context Information, Command Types, Sessions, Locked Objects, Object I/O, Files and Disks) for a relational database. The value of this interface is the flexibility to drill into the performance issue at hand based on the information available.
For example, if the performance problem presents itself at a database level, then you can drill into the data based on the database name then determine if a particular object, user, command, syntax error, indexing, fragmentation, temporary tables or blocking. The same is true when a user communicates that they are experiencing a SQL Server performance issue. Begin drilling into the data based on the user, database and command to see the associated SQL statements they were issuing in order to determine the root cause of the issue to begin query tuning.
SQL PI puts the power of database diagnostics into the hands of a DBA or Developer. The flexible, yet simple interface enables quick discovery to begin formulating a resolution.
Beyond just accessing data in real time, Quest Foglight also enables you to roll back the clock to understand the performance at a specific point in time with multiple levels of granularity often necessary for SQL performance tuning. This enables you to answer questions about what happened overnight, over the weekend or even a week ago regarding application response time. Answering these questions assures the organization that the environment is being properly managed, which is critical for the overall IT Team.
With SQL Server Professionals now supporting applications in the cloud and multiple RDBMS platforms, Quest Foglight enables DBAs to become proficient in management, monitoring and tuning of ten popular database platforms (Oracle, Microsoft SQL Server, MySQL, Azure, AWS, etc.). Returning to the Global View, it is possible to understand the overall database environment in the organization with the Table view to begin troubleshooting a database instance as we have discussed in the prior steps.
Alternatively, the Global View also includes a Heatmap view to visually guide you to the database instances that need the most attention. In the screen shot below, the MySQL instance needs a tremendous amount of attention with more than 40,000 fatal alarms.
Proactive Alerting with Alarms
Speaking of Alarms, Quest Foglight includes a flexible means to configure, deploy and resolve meaningful alarms. Each alarm is configured with three thresholds – Fatal, Critical or Warning. The alarms are accessible from the Global View, Instance View and also the Alarms interface. Foglight includes out of the box alarms that can be easily deployed, as well as customizable alarms including:
- Performance – Query, Memory, CPU, Storage, Counters, etc.
- Operating System – Configuration and Performance
- VMWare – Configuration and Performance
- Operational – Failed SQL Server Agent Jobs, missed backups, replication, etc.
- Azure – Subscription, quota and VM settings
- Database and table size
- Security – Login failures, accessibility issues, etc.
- Custom business logic
As previously mentioned, the Alarms can be accessed from the Global View where you can get the details for the alarm as well as acknowledge (Ack) and clear an alarm once completed.
Cloud Cost Savings
Up to this point the focus has been on time savings and productivity improvements for DBAs and Developers. Foglight’s Cloud Manager can directly improve your organization’s bottom line with direct insight into your cloud environment costs. Cloud Manager focuses on the infrastructure and application costs for Azure and AWS. The cost reduction comes from:
- VM Resizing
- Potential Zombie VMs
- Unused Resources
At a high level, Cloud Manager reports the current billing, last month billing and savings per month at the resource level such as storage, virtual machines, instance licensing, backups, network and more.
After drilling into a specific cloud resource, Cloud Manager will break down the cost per resource to provide a clear picture of your budget.
VMware Tuning and Management
With VMware supporting a large percentage of SQL Server instances, Foglight includes insight into this infrastructure (guests and hosts), which is generally not available to DBAs, yet very critical for performance tuning. Foglight’s Virtual Machine interface includes a heatmap to help focus troubleshooting for guests.
At a detailed level, the virtual machine guest data is organized by performance, monitoring, events, storage, network, process and database data. Each interface is unique and includes meaningful charts to understand resource allocation issues including bottlenecks, overallocation and insufficient allocation.
Foglight also includes VMware Insights which is intended to streamline the VMware host configuration and performance tuning with insight into datastore usage, cluster configuration, guest configuration and more.
In the screen shot below it is easy to discern overall capacity per storage device, disk space used, latency and growth to help plan for both short and long term infrastructure needs.
It is also possible to drill into the specific host to get additional performance metrics as well as see the associated virtual machines being supported.
Video - Learn about Building the Best Virtual SQL Server Environments.
Managing SQL Server Replication can be difficult with little insight into the overall implementation in a graphical manner with the native SQL Server tools. Foglight provides insight into Replication status with alarms and visually noting in the environment where issues or bottlenecks are occurring.
Why should I consider Foglight?
- Deep insight into SQL Server instances on-premises and in the cloud
- Multi-platform database monitoring in a single tool
- Flexibility to monitor the database, code, operating system, virtualization and cloud resources
- Comprehensive data collection and prioritization for database professionals yielding significant time savings
- Identify cost savings with cloud deployments in both Azure and AWS
- Better resource utilization and capacity planning for VMware deployments to reduce costs
- How do I get started with Foglight?
- Identify the database performance monitoring needs in your organization
- SQL Server, code, Windows, VMware and Cloud performance tuning
- Real time and historical data analysis
- Multi database engine support (Microsoft SQL Server, Oracle databases, MySQL, Azure, AWS, etc.)
- Cost savings with cloud deployments and VMware sizing
- Extensive alarms for proactive notification
- Check out the Foglight Virtual Lab to see the product in action
- Download Free Trial and put Foglight to the test in your environment
- Download Foglight for SQL Server
- Discuss the findings with your team
- Contact Quest to Request Pricing
- Identify the database performance monitoring needs in your organization
MSSQLTips.com Product Spotlight sponsored by Quest makers of Foglight.
Last Updated: 2020-11-02
About the author
View all my tips