* Find out Long Running query using sp_who2 with DMV's

EXEC sp_who2
and see the result shown below. Find out rows with runnable status with highest CPUTime.
Sp_who2 will not display the text of the query, so we need to find out the text using dm_exec_requests and dm_exec_sql_text management views. The view dm_exec_requests will show the text of the query. All we need to do is to find out spid from the result of sp_who2 and filter it in dm_exec_requests using session_id column.

 The following code displays the text of the query.
 SELECT
        sql.text AS statement_text
FROM
        sys.dm_exec_requests  AS req
CROSS APPLY sys.dm_exec_sql_text(req.sql_handle) as sql
WHERE
    req.session_id=SPID

* find Last Run Query in SQL Server

SELECT conn.session_id, sson.host_name, sson.login_name, 
 sqltxt.text, sson.login_time,  sson.status
FROM sys.dm_exec_connections conn
INNER JOIN sys.dm_exec_sessions sson 
ON conn.session_id = sson.session_id
CROSS APPLY sys.dm_exec_sql_text(most_recent_sql_handle) AS sqltxt
ORDER BY conn.session_id

* Progress Percentage of Backup/Restore operation in SQL Server

SELECT session_id as SPID, command, a.text AS Query, start_time, percent_complete, dateadd(second,estimated_completion_time/1000, getdate()) as estimated_completion_time
FROM sys.dm_exec_requests r CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) a
WHERE r.command in ('BACKUP DATABASE','RESTORE DATABASE')

Cluster Information in SQL Server

Script to get Cluster information on any node in the cluster group....

SELECT * FROM   sys.dm_os_cluster_nodes
--OR
SELECT * FROM   Fn_virtualservernodes()


--If accessing across Network

SELECT * FROM   sys.dm_io_cluster_shared_drives