Monitor Running Processes in SQL Server 2005/2008

You often need to monitor the processes running on the server in order to improve the performance, by tuning them.  A common option is to make use of a profiler. But in SQL Server versions starting 2005, we can also use dynamic management views.

select
    source_code,stats.total_elapsed_time/1000000 as seconds,last_execution_time
from sys.dm_exec_query_stats as stats
    cross apply(SELECT text as source_code FROM sys.dm_exec_sql_text(sql_handle))AS query_text
order by total_elapsed_time desc

The above code will list out the queries based on the descending order of the time they take

The sys.dm_exec_query_stats is a Dynamic Management view that gives the statistical
information's about cached data. The sys.dm_exec_sql_text is the another view that
gives actual text of the sql_handle which is in binary format. It converts the binary
to actual characters

You can also make use of an article from the Microsoft available at
http://msdn.microsoft.com/en-us/library/ms191511.aspx

Further Reading:

Dynamic Management Views (DMV) for SQL Server Performance and Tuning
Important DMVs to monitor CPU – SQL Server