Try Spotlight Cloud ProSign in

      Understanding Workload Analyzer to Map Performance Bottlenecks

      By: Spotlight Cloud

      When a user or application makes requests to a database, it consumes resources on that system. As the number of requests increases, you may experience resource waits. These waits lead to performance bottlenecks and, in the case of cloud-deployed databases, extra monthly costs! When diagnosing performance bottlenecks, the first step is to understand which resources are affected.

      Being able to map a performance bottleneck back to a specific resource wait, then to specific code, and finally to a specific user’s workload will enable you to get to the root cause and resolve the bottleneck permanently. 

      For example, you might discover that an application is running slow because the CPU is over-consumed on the database server because Matt in the purchasing department is running a stock take report in the plant database.

      Spotlight Cloud’s Workload Analyzer is the tool that makes this possible with its user-friendly navigation.

      How to Use Spotlight Cloud’s Workload Analyzer

      To begin, you can select the time frame of interest. Spotlight Cloud stores one year of data, so you can go back to any point in time or time range in the past year.

      Then you have the option of filtering by resource. For example, if you know the issue is CPU-related, you can select the CPU resource. Doing so filters out the information related to all other resources, such as I/O, locks, and memory, effectively eliminating the white noise and making it easier to get to the root cause.

      Workload Analyzer Default Page 

      Drill into the databases dimension and it will order the top databases consuming the most resources from high to low and shade them correspondingly. This sorting mechanism is preserved through each iteration of a drilldown.  

      Drilling into the Database Dimension 

      Furthermore, you should drill into the sales database because it is important to know what the behavior of the waits are within the top-consuming database specifically. In this example, it appears that most of the workload was accounted for by CPU (45.7 percent) and I/O resources (30.2 percent), and their rates are close at .48 sec/s and .43 sec/s.

       

      Drilling into the Sales Database Dimension

      In parallel, selecting CPU will filter out the other resources and yield a customized, CPU-only readout. The ability to isolate a specific workload is useful because it visually screens out the distracting metrics, allowing you to focus only on what takes precedence. Also, performance indicators may be graphed on top of each other so you can visually see correlations. 

      Key Performance Indicators Filtered for CPU Stats Only

      Next, drill down into T-SQL batches. This allows us to find out which batches within the sales database are the most taxing. 

      Drilling into the T-SQL Batches

      Because this batch is highly CPU-intensive, it is important to know which queries within that batch serve as the culprit for the extra cost. Using the T-SQL text in conjunction with the execution plan shows that the Sort operator is to blame. SQL Optimizer forecasts that the estimated charge is 97 percent. Adding an index may help optimize performance. 

      T-SQL Statements 

      Execution Plan and Cost Analysis of the Operations Performed 

      Note that the resource selector can be configured to highlight a resource once its utilization breaches a pre-defined threshold. For example, you can set the selector to highlight I/O resources if it the wait is more than 30 percent. 

      Adjusting Resource Selector Configurations for I/O Resources

      Updated Configurations for I/O Resource Selector Applied 

      SQL server performance

      July 14, 2020 8:15:00 AM PDT
      Spotlight Cloud

      Written by Spotlight Cloud

      Understand your SQL Server infrastructure health at a glance by monitoring SQL Server's relational engine, Analysis Services, Windows, VMware ESX and SQL Azure with Spotlight.

      Topics