SQL Server 2014 – New DMV sys.dm_exec_query_profiles

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.

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

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