Hi Friends,

A new DMV that is provided by Microsoft to check the progress of any long running time taking query i.e. sys.dm_exec_query_profiles. We all know that when we submit a query to SQL Server then first it generates the estimated execution plan. This plan includes all the required operators which have the information about estimated number of rows to be processed. SQL Server got this estimated number of rows from SQL Server statistics. Now with the help of this new DMV we can get the information about how many rows have been processed for each operator. Let me show you through code:

Create tables and insert data. Keep in mind that this code will take some time to insert data.

Now let me run the below select query in new query window along with actual execution plan and also run the DMV query in another new window parallel. Keep in mind that DMV should run in the middle of select query execution.

sys.dm_exec_query_profiles

The execution plan of above query

Now run the below query in new window and check the output.

NOTE: I have run the below query before the completion of above select query. So that it will show me the progress of query. If I will run below query after the execution of above select query then it will show me nothing as result for that session id.

sys.dm_exec_query_profiles

Output of the sys.dm_exec_query_profiles

Here [physical_operator_name] column will show all physical operators. [Estimate_row_count] will show you the estimated number of rows to be processes. [row_count] column will show you the number of rows has been processed as of now. By using this last column [row_count] you can identify how many rows have been processed.

Reference: Click Here

HAPPY LEARNING!

Regards:
Prince Kumar Rastogi

Like us on FaceBook | Join the fastest growing SQL Server group on FaceBook

Follow Prince Rastogi on Twitter | Follow Prince Rastogi on FaceBook