How to get the result of query execution at each step?

Question

Here is my query example.

select *
from employee as a
where 1=(select count(distinct(b.salary))
from employee as b
where a.salary<b.salary)

I want to know that how many times my outer query and inner query runs. Is there any method to handle a counter inside the query ? As in visual studio, we can watch the value of any variable at each iteration of loop, similarly can we see the result of outer and inner query at any step ? Is there any tool that is used for this purpose ? Any method ?

Thanks in Advance.

What rdbms are you using? For SQL-Server the [**Profiler**](http://msdn.microsoft.com/en-us/library/ms181091.aspx) is the right tool. — Tim Schmelter, Jun 29 '13 at 14:05

score 0 · Accepted Answer · edited May 23 '17 at 12:05

0

When you execute this query, get the actual execution plan. It will tell you what the execution counts are. Here's how to get it: How do I obtain a Query Execution Plan?

It is impossible to step through SQL code. The whole idea of SQL is to have a separation between what result you want, and how the result is fetched. Hence, how the SQL code is executed may look very little like what your query is. Each part of the SQL code has multiple operations that do the same thing, but perform differently based on the data.

For example, if you don't have any indices on employee, your queries would be a nested loop with table scans. If the tables are large, there is probably only one table scan with a sort and a table spool to reuse that sorted temporary table. If you had an index on salary, then there would be a nested loop with a bunch of index scans.

In conclusion, get the actual execution plan to see what SQL Server is actually doing.

edited May 23 '17 at 12:05

Community

1
1

answered Jun 29 '13 at 16:27

John Tseng

6,262
2
27
35

Thanks for reply but this post is not giving me answer. For example I have two tables. A & B. If for each row of table A, B is fully scanned. I just want to know that on the given condition which values are compared and which row is returned on each step. In short I want a complete picture just as the whole operation is being done in front of me in real life and I am watching this whole operation with my eyes. Thanks – Hamid Mahmood Jun 29 '13 at 16:58
@HamidMahmood The execution plan shows what SQL Server is doing. It will show you how many times B is fully scanned. (Which it shouldn't be doing if you care about performance.) I've updated my answer with how to get an execute plan. – John Tseng Jun 29 '13 at 17:04

How to get the result of query execution at each step?

1 Answers1