DPM Q3FY20 - Accelerate Database Efficiency with SQL Performance Investigator (PI)

Figure 2: Proactive alerts on deadlocks and blocks with contextual alarms advisories and detailed wait-event analysis enable DBAs to prevent lock waits from causing noticeable delays. • Expert suggestions (such as for avoiding manual locking or reducing the locking time, range or number) To assist with collaboration and troubleshooting workflows, Foglight enables you to add notes to alarms and view the history of similar alarms all from the same dialog box. Workload investigations allow you to drill deeper into database locking for troubleshooting and assist you in quickly finding the user, session, SQL or program that caused the lock. You can also immediately identify the users impacted by the lock. CHANGE ANALYSIS Workload comparison capabilities allow you to track changes in database environments and spot configuration drift issues. This data can be visualized in the investigative drill downs or used in the comparison reports. Foglight tracks changes in: • Database objects • System configuration • Database configuration • Execution plans Figure 3: At any point in the investigation, you can generate a comparison report to contrast your current configuration to another point in time for that selection of data or even compare across instances. 3 Workload comparison capabilities allow you to track changes in database environments and spot configuration drift issues. Figure 4: Integrated virtualization and storage monitoring enable DBAs to collaborate with infrastructure administrators to manage the impact of virtualized resources on database performance. Execution plan analysis Foglight SQL PI has a variety of architectural features that reduce implementation costs, ensure rapid ROI and enable fast deployment and time to value. After identifying a potentially problematic SQL statement, you can automatically compare its execution plan to prior plans stored in the historical repository to see if there are any significant changes in the plan that may have impacted performance. Comparison reporting Built-in smarts ensure that only events worthy of deeper investigation are reported to the historical data repository, which then acts as a data warehouse for future trend analysis and reporting purposes. Comparison reports can be run against the historical data stored here to track deviations from any known good state, informing troubleshooters regarding appropriate corrective actions to take. VIRTUALIZED RESOURCE MANAGEMENT Given that a large proportion of SQL Server instances in production are virtualized, DBAs can get visibility into performance issues related to CPU and memory allocation in virtualized 4 environments by integrating with Foglight for Virtualization. If a storage area network (SAN) is part of the environment, Foglight for Storage Management can deliver the necessary visibility to troubleshoot, plan and optimize resources. Using a single platform across multiple teams enables collaboration to appropriately allocate and plan capacity for databases and applications. If provided with the relevant details, virtualization and storage administrators can work cohesively with DBAs on troubleshooting database performance issues. ENTERPRISE-READY ARCHITECTURE Foglight SQL PI has a variety of architectural features that reduce implementation costs, ensure rapid return on investment (ROI) and enable fast deployment and time to value: • Scalable: Hundreds of database instances can be monitored with a single monitoring instance to support enterprise-scale deployments. • Low overhead: Agentless data collection executed through remote agents ensures that no more than 2 percent CPU overhead is added to monitored database instances.