Over a million developers have joined DZone.
{{announcement.body}}
{{announcement.title}}

Runtime Metrics in Execution Plans

DZone's Guide to

Runtime Metrics in Execution Plans

In new versions of MS SQL Server, the ability to view a query's runtime metrics is available. So how does this affect how you tune your queries? Read on for the details.

· Database Zone
Free Resource

Whether you work in SQL Server Management Studio or Visual Studio, Redgate tools integrate with your existing infrastructure, enabling you to align DevOps for your applications with DevOps for your SQL Server databases. Discover true Database DevOps, brought to you in partnership with Redgate.

Capturing query execution metrics is much easier now that you can see the runtime metrics in execution plans when you’re using SQL Server 2016 SP1 or better in combination with SQL Server Management Studio 2017. When you capture an actual plan using any method, you get the query execution time on the server as well as wait statistics and I/O for the query. This fundamentally changes how we can go about query tuning.

Runtime Metrics

To see these runtime metrics in action, let’s start with a query:

SELECT p.LastName,
pp.PhoneNumber,
pnt.Name
FROM Person.Person AS p
JOIN Person.PersonPhone AS pp
ON pp.BusinessEntityID = p.BusinessEntityID
JOIN Person.PhoneNumberType AS pnt
ON pnt.PhoneNumberTypeID = pp.PhoneNumberTypeID
WHERE pnt.PhoneNumberTypeID = 3;

We’ll run this query and capture the actual execution plan using SSMS 2017. The changes are not immediately apparent. Here is a screen capture of the plan and one of the tooltips (you won’t see anything new here):

Image title

Instead, you need to go into the properties of the operators (which you should get into the habit of doing anyway). Here are some of the properties of the SELECT operator, showing off some exciting new information:

Image title

At the very top you can see the QueryTimeStats showing CpuTime and ElapsedTime, 7ms and 49ms respectively. At the bottom, you can see that there are WaitStats showing 3 waits of 42ms and a wait type of ASYNC_NETWORK_IO.

Excited?

Let’s see some of the properties of the Index Scan operator from this plan:

You can see that you have a full set of the reads for the query — in this case, 122 logical reads and no physical reads (I’d run the query a couple of times getting things together for this blog post so everything was in-memory). At the bottom, you can also see the time measurements. In this case, the value is zero when measured in milliseconds.

Now, we all know that a single run of a query sometimes doesn’t accurately reflect its general behavior. Capturing runtime metrics for a single run won’t always satisfy our tuning needs. For that, we can still use extended events to aggregate a series of runs.

Conclusion

In short, we have most of everything we need to see when we’re trying to capture runtime metrics for a query. No longer will we need to SET STATISTICS IO (which can cause problems anyway) and SET STATISTICS TIME or swap back and forth to extended events. These runtime metrics included with the plan even mean that we get to pass the data along to someone else when we share the plan. This is very useful stuff.

It’s easier than you think to extend DevOps practices to SQL Server with Redgate tools. Discover how to introduce true Database DevOps, brought to you in partnership with Redgate

Topics:
database ,runtime ,metrics ,tutorial ,ms sql server

Published at DZone with permission of Grant Fritchey, DZone MVB. See the original article here.

Opinions expressed by DZone contributors are their own.

{{ parent.title || parent.header.title}}

{{ parent.tldr }}

{{ parent.urlSource.name }}