Query running in SQL Server

I use this sript to check what query is running in server

select r.blocking_session_id,r.session_id ,h.text,substring(h.text, (r.statement_start_offset/2)+1 , ((case r.statement_end_offset when -1 then datalength(h.text)
else r.statement_end_offset end - r.statement_start_offset)/2) + 1) as text , r.wait_type, r.wait_time , r.last_wait_type , r.wait_resource ,
r.command , r.database_id , r.granted_query_memory, r.reads , r.writes , r.row_count , s.[host_name] , s.program_name , s.login_name
from sys.dm_exec_sessions as s
inner join sys.dm_exec_requests as r on s.session_id =r.session_id and s.last_request_start_time=r.start_time
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) h where is_user_process = 1 order by 2 desc

SQL process, percentage of complete

I restore one database and want know how many percent the SQL process will be complete?

Step
1. Restore database test from disk='c:\test.mdf'
2. We can check which session belong to this process with running the below command
sp_who2 'active'
3. Check on command column, and look for "Restore database"
My SPID for this "Restore database" = 57
4. Run the below command and look for session_id=57

Select session_id,percent_complete,estimated_completion_time,total_elapsed_time
From sys.dm_exec_requests
Where session_id=57

session_id percent_complete estimated_completion_time total_elapsed_time
---------- ---------------- ------------------------- ------------------
57 26.47547 5800366 2090266

The above result show the SQL process already complete 26.47%, estimated_completion_time=5800366 and total_elapsed_time=2090266

This above example is use to show how to use sys.dm_exec_requests to show how many percent_complete of SQL process.
We can also run the below command to show percentage complete of restore process.

Restore database test from disk='c:\test.mdf' with stats
10 percent processed.
20 percent processed.
30 percent processed.
....