2

I am looking at an actual execution plan in Microsoft SQL Server Management Studio v 17.9.1 and I can't see a lot of actual values to compare against the estimates.

For example, we have a connector which holds the following data:

enter image description here

I can see the Estimate Row and Data Size, but where is their actual size? Is there a way to see this information?

Same question for actual CPU, I/O, number of executions and so on.

Update: MS SQL Server 2008 R2

0

1 Answer 1

5

I can see the Estimate Row and Data Size, but where is their actual size? Is there a way to see this information?

As commented by @Randi Vertongen I use SQL Sentry Plan explorer to see actual data size if I need. This is a free tool you can download (without even giving out your email address).

Plan Explorer Installation & Overview

Here is an example of that data you are looking for in plan explorer.

enter image description here

As far as actual number of rows and number of rows read is available in SSMS tool tip or properties. Here is an example.

enter image description here

Same question for actual CPU, I/O, number of executions and so on.

For CPU and I/O you can right click individual operator and see it in properties or look at xml. If you right click the leftmost node and see it's properties you will total use of total cpu and memory grant details. Number of execution is also available in SSMS. See picture above.

Following is an example of overall cpu, time and memory grant information:

enter image description here

Following is an example of looking at details per operator. This will vary depending on the type of operator. This output is from a sort operator.

enter image description here

Here is a partial list of when some of these features were added and back ported to older versions.

enter image description here

Overall plan explorer is a far better tool to look at execution plan and many data professionals use it everyday.

4
  • Thank you for your reply, but I'm afraid there is no information in the SSMS regarding actual CPU and I/O, nor in the operator's properties, nor in the execution plan XML. Maybe I am looking in the wrong place. Could you please provide a screenshot of the area where you can see this information?
    – spektro37
    Dec 18, 2018 at 17:40
  • @spektro37 - this information hasn't always been there. It is a relatively recent enhancement, presumably your version of SQL Server doesn't have it Dec 18, 2018 at 18:48
  • @MartinSmith - I believe you are right.
    – spektro37
    Dec 19, 2018 at 11:12
  • @SqlWorldWide - thank you for the detailed answer, it is much appreciated.
    – spektro37
    Dec 19, 2018 at 11:13

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.