In part one, I covered step one: Narrow Your Focus. Now, let's dive into step two.

Note: The following section discusses troubleshooting but skips many steps in our recommended methodology.

If you’ve never performed troubleshooting or performance tuning on SQL Server, take the time to read the ebook Troubleshooting SQL Server Performance.

Step 2: Correlate

Now you know your way around the basic layout of the SQL Sentry dashboard and how the Windows side of the dashboard correlates performance information with the SQL Server side of the dashboard. The next step is to drill down into the details – either to a single point in time or a more tightly focused period. This drill-down process is called sample selection.

Single-click Drill-down

Clicking on a single specific time on the dashboard, say at the peak of troubling rise in Memory wait statistics, presents a vertical red line synchronized on all graphs at that single time position. This allows you to easily correlate what's happening with one single point in time compared to what's happening with other related metrics at the same time.

Click and Hold Drill-down

If you click the mouse and hold a sample dragging across a time range on the graph, the cursor changes color, and when you release the mouse, you'll be presented with a subset of the Jump To context items used for range selection. This allows you to jump directly to a different tab and view operations that overlap the currently selected sample time, as shown below in Figure 6.

Figure7: Correlated Timeframes on the SQL Sentry Dashboard

Once you have selected a sample selection, you can right-click to see a variety of options, which take you to the same sample selection on the other dashboard tabs. The options, as useful as they are, are beyond the scope of this blog post. (You can read more about these options in the online documentation).

We will focus on troubleshooting scenarios instead of covering every option available here. When troubleshooting the SQL Server, the most common question is, “What SQL statements are running during the sample selection that might be causing issues?” To answer that question, simply select Jump To the Top SQL tab, where you’ll be taken to the Top SQL tab, as shown below in Figure 7.

Figure 8: The SQL Sentry Top SQL Tab

The Top SQL tab, as with most of SQL Sentry tabs, is divided into multiple grids. The top grid displays Running Queries, Completed Queries, Procedure Stats, or Query Stats In each corresponding tab. Below that is the Statement Grid, displaying all of the SQL statements collected within completed queries, procedures, or T-SQL batches. Click on a SQL statement in the top grid that contains multiple SQL statements within it, such as a procedure. The statement grid will display all of the SQL statements within the procedure or batch, enabling you to see the code that is most problematic within the procedure.

You will also see the Execution Plan Diagram, if one is available for the query you’re investigating. The execution plan diagram is an integrated version of SolarWinds Plan Explorer, available independently as a free product, which provide deep query tuning information. Finally, the Query History grid is at the bottom of the tab. (If you are familiar with the SQL Server Query Store features, the query history grid is a superset of this capability).

Query History displays a graphical representation of the selected query over a specified range of time. Query History provides information about the query execution plans, when and if they changed, and how they impacted different resources. Each dot represents when the query is executed. The height of the dot from baseline indicates the latency of the query. Different colors of the dots indicate the query used different execution plans, and the plans are numbered. Hovering over any of the dots on the Query History provides you with additional information. You can right-click any dot on the Query History grid, and select Open Plan from the context menu to open the Plan Explorer diagram.

While we have focused on jumping to poorly performing queries, we could jump to any other tab if the performance analysis dashboard had shown issues with other aspects of SQL Server’s behavior. For example:

· A spike in Windows Server CPU not related to SQL Server, we could right-click and Jump To the Processes tab

· An issue with disk IO performance, we could right-click and Jump To the Disk Activity tab

· Issues with AlwaysOn Availability Groups, we could right-click and Jump To the AlwaysOn tab

· In the same way, we could right-click and jump to the tabs for Disk Space Indexes, Blocking SQL, Deadlocks,

Color-coding on the Top SQL tab or any other tab makes it easy to see any anomalies or elements of SQL Server that need remediation.

Summary

You don’t have to be a SQL Server expert to use SQL Sentry. If you know how to use Windows Task Manager, then you can take advantage of the power of SQL Sentry to troubleshoot any imaginable problem on an instance of SQL Server, on premises or in the cloud. The workflow couldn’t be easier:

SolarWinds database solutions help keep your data available and scalable while pinpointing the root cause of performance issues. SolarWinds database observability solutions provide deep insight into your databases, connected apps, and infrastructure to connect performance monitoring and root-cause analysis to your business success.

To learn more about SQL Sentry and our other database observability products, visit https://www.solarwinds.com/solutions/database-solutions.

Download the full-featured 14-day trial. Dive into a real-life interactive demo environment at https://www.solarwinds.com/sql-sentry/demo-registration.

Watch our extensive video library and read the documentation at https://documentation.solarwinds.com/en/success_center/sqlsentry/content/getting-started/getting-started-overview.htm.

And don’t forget to download our popular and free query tuning tool, Plan Explorer, at https://www.solarwinds.com/free-tools/plan-explorer.

Take advantage and subscribe of the many videos on SolarWinds products at https://www.youtube.com/@solarwinds.