SELECT
    SPID                = er.session_id
    ,BlkBy              = er.blocking_session_id
    ,ElapsedMS          = er.total_elapsed_time
    ,CPU                = er.cpu_time
    ,IOReads            = er.logical_reads + er.reads
    ,IOWrites           = er.writes
    ,Executions         = ec.execution_count
    ,CommandType        = er.command
    ,ObjectName         = OBJECT_SCHEMA_NAME(qt.objectid,dbid) + '.' + OBJECT_NAME(qt.objectid, qt.dbid)
    ,SQLStatement       =
        SUBSTRING
        (
            qt.text,
            er.statement_start_offset/2,
            (CASE WHEN er.statement_end_offset = -1
                THEN LEN(CONVERT(nvarchar(MAX), qt.text)) * 2
                ELSE er.statement_end_offset
                END - er.statement_start_offset)/2
        )
    ,Status             = ses.status
    ,[Login]            = ses.login_name
    ,Host               = ses.host_name
    ,DBName             = DB_Name(er.database_id)
    ,LastWaitType       = er.last_wait_type
    ,StartTime          = er.start_time
    ,Protocol           = con.net_transport
    ,transaction_isolation =
        CASE ses.transaction_isolation_level
            WHEN 0 THEN 'Unspecified'
            WHEN 1 THEN 'Read Uncommitted'
            WHEN 2 THEN 'Read Committed'
            WHEN 3 THEN 'Repeatable'
            WHEN 4 THEN 'Serializable'
            WHEN 5 THEN 'Snapshot'
        END
    ,ConnectionWrites   = con.num_writes
    ,ConnectionReads    = con.num_reads
    ,ClientAddress      = con.client_net_address
    ,Authentication     = con.auth_scheme
FROM sys.dm_exec_requests er
LEFT JOIN sys.dm_exec_sessions ses
ON ses.session_id = er.session_id
LEFT JOIN sys.dm_exec_connections con
ON con.session_id = ses.session_id
CROSS APPLY sys.dm_exec_sql_text(er.sql_handle) as qt
OUTER APPLY
(
    SELECT execution_count = MAX(cp.usecounts)
    FROM sys.dm_exec_cached_plans cp
    WHERE cp.plan_handle = er.plan_handle
) ec
ORDER BY
    er.blocking_session_id DESC,
    er.logical_reads + er.reads DESC,
    er.session_id

Msg 102, Level 15, State 1, Line 46
Incorrect syntax near '.'.
Msg 102, Level 15, State 1, Line 52
Incorrect syntax near 'ec'.

EXEC sp_dbcmptlevel 'databasenamehere'

quote:

---

Originally posted by sunitabeck

I copied and pasted the code to my SQL 2008 and I didn't see any errors. Your server has to be SQL 2005 or later and database compatibility level 90 or greater for cross apply to work.

EXEC sp_dbcmptlevel 'databasenamehere'

A similar utility that I have found to be very useful and use quite often is Adam Machanic's sp_whoisactive. http://sqlblog.com/blogs/adam_machanic/archive/2012/03/22/released-who-is-active-v11-11.aspx

---

quote:

---

Originally posted by visakh16

I tried running it on my machine and can confirm its working fine for me! I tried on SQL 2008 R2 and SQL 2012 instances

B/w which editor are you using?

---

quote:

---

Originally posted by Musawar Mustafa

If you are using 2008 or 2008 R2 and EXEC sp_dbcmptlevel 'databasename' give below 90 then use the script below:

ALTER DATABASE databasename SET COMPATIBILITY_LEVEL = 100

---