Monitor, Manage and Improve Database Performance with Foglight for Databases

By:   |   Updated: 2020-11-02   |   Comments   |   Related: More > Monitoring


Problem

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 we continue to do more as a team as we expand into cloud platforms and other database platforms 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 to monitor and manage our environment to quickly resolve performance issues.

Solution

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 features that Foglight delivers to reduce costs, improve productivity and maximize performance.

Global View

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.

foglight global view

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 a specific piece of code to improve the overall system 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.

fogligh instance level metrics

Check out this video on Foglight for SQL Server Overview.

SQL PI

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).  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 or blocking is the next logical step.  The same is true when a user communicates that they are experiencing an 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 troubleshoot the issue.

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.

performance investigator

Historical Data

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.  This enables you to answer questions about what happened overnight, over the weekend or even a week ago.  Answering these questions assures the organization that the environment is being properly managed is critical for the overall IT Team.

foglight historical data selection

Cross Platform

With SQL Server Professionals now supporting applications in the cloud and multiple database platforms, Quest Foglight enables DBAs to become proficient in management, monitoring and tuning of ten popular database platforms.  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.

foglight multi database platform support

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.

foglight heatmap of problem areas

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
foglight alarms and notifications

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.

foglight alarms

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.

foglight cloud cost savings calculator

After drilling into a specific cloud resource, Cloud Manager will break down the cost per resource to provide a clear picture of your budget.

foglight cloud cost savings metrics

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.

foglight vmware tuning and management

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 vmware tuning and management

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.

foglight vmware tuning and management

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.

foglight vmware tuning and management

Video - Learn about Building the Best Virtual SQL Server Environments.

Replication

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.

foglight replication monitoring

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
Next Steps
  • 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 platform support
      • 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

MSSQLTips.com Product Spotlight sponsored by Quest makers of Foglight.



Last Updated: 2020-11-02


get scripts

next tip button



About the author
MSSQLTips author Jeremy Kadlec Jeremy Kadlec is the Co-Founder, Editor and Author at MSSQLTips.com, CTO @ Edgewood Solutions and a six time SQL Server MVP.

View all my tips
Related Resources





Comments For This Article