From the course: Microsoft Azure Database Administrator Associate (DP-300) Cert Prep by Microsoft Press

Interpret performance metrics

From the course: Microsoft Azure Database Administrator Associate (DP-300) Cert Prep by Microsoft Press

Start my 1-month free trial Buy for my team

Interpret performance metrics

“

- Welcome back. This is lesson 7.3, Interpret Performance Metrics. Okay, so you've gathered your metrics. Now what? First, what are you trying to solve? Since we've been talking about performance baselines, more often than not, the issue is performance-related. If you're going through this analysis, more than likely, you're trying to solve a performance problem, whether it is an overall performance issue or a specific performance issue, like a slow-running query. So where do you start to look at? What are you trying to solve? And it really depends on what you're trying to fix. If you're trying to troubleshoot query performance, your go-to is Query Store, as it provides insight into what the SQL Server query engine is doing when it executes a query, and it gives you the the execution plan to review. The query plan is a great way to see how the query is executed. SP_who2, is there any blocking and locking going on? SP_who2 is a built-in system store procedure that reports on current SQL Server processes and users and length of duration of those processes, and any blocking activity. Dashboards. A lot of the dashboards let you look at historical information to help you identify patterns or trends to the activity of your database, including CPU and Memory. So you can go back in history and see if there is a pattern or trend of performance issues. Start looking at the extended event and performance monitor logs and output. Now a lot of these like Performance Monitor is only available in infrastructure as a service, but extended events is both paths and IAS. So it can start looking at those logs to help determine what's going on in your database. The idea here is to just dive in and start reviewing. Experience helps of course, but the more you dive in, the more experience that you gain. So you're going through all the metrics and logs, reviewing the data, what do they mean? Let's start with Query Store. As we discussed in the previous video about Query Store, Query Store comes with a number of reports and dashboards that that provide insight into the queries executed in your database, and how the query engine executes them. So it's a great view into what the query engine was thinking when it executed your query. The Query Store and the associated reports makes it easy to identify where the problem areas are, and where to start focusing your attention. You can group these by duration, by high CPU, by high memory. It really depends on what you're trying to look at, but it's a great way to start looking at query performance issues, and then looking at the execution plan to determine what the query engine was doing when it executed the query. The execution plan. When reviewing the Query Store queries and output, the dashboard also shows you the execution plan the query engine generated. The execution plan does take some experience to know what you're looking at, but it also does a somewhat decent job helping interpret what you're looking at. So in many cases, it'll recommend a missing index, and we'll talk about more of that in another lesson. Did the query do a SQL scan on a table? What is the majority of the cost of the query, for example. The point is, don't overlook the execution plan as this is a great place to start looking. Waits. This is one of the most underutilized performance troubleshooting areas in SQL Server. Basically, SQL Servers is permanently tracking why execution threads have to wait. and reviewing this information is a great way to interpret performance metrics. Some waits are referred to as poison waits, and if you see these waits, that means SQL Server isn't happy. We won't go into all of them, but CMAM thread and any resource SEMA4 wait means SQL Server is having CPU and memory issues. Reviewing waits will tell you if there is a lot of locking or blocking going on, and there's a lot of information on the web about the different waits that cause or that are the result of locking and blocking. Okay, the logs, if you have configured extended events, review these output files. We discussed that in the last video. Extended events and other tools have logs, output logs. Review those to see what is going on. And again, not to beat a dead horse, but review the built-in dashboards both in SQL Server Management Studio and in Azure. There is a plethora of dashboards in the Azure portal that make it easy to interpret any performance issues. The goal here is to start using these tools and reviewing their information as they are full of information that makes it fairly easy to start troubleshooting.

Contents