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.
Create Database DMVTEST go USE DMVTEST go Create Table xtTax ( empid int identity(10000,1), paid int, balance int ) go Create Table xtdetails ( empid int identity(10000,1), country varchar(50) ) go SET NOCOUNT ON insert into xtTax values(RAND()*4517,RAND()*1221) go 500000 insert into xtdetails values('INDIA') go 100000 insert into xtdetails values('CHINA') go 100000 insert into xtdetails values('BRAZIL') go 100000 insert into xtdetails values('RUSSIA') go 100000 insert into xtdetails values('SOUTH AFRICA') go 100000
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.
Select SUM(t.paid) paid, SUM(t.balance) balance, d.country from xtTax t join xtdetails d on t.empid=d.empid group by d.country order by balance desc OPTION (MAXDOP 1)
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.
Select session_id,physical_operator_name,estimate_row_count,row_count from 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
Prince Kumar Rastogi