SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 SQL Server Administration (2008)
 A better sp_who2
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

gbritton
Flowing Fount of Yak Knowledge

1764 Posts

Posted - 10/02/2012 :  09:01:22  Show Profile  Reply with Quote
I ran across a script here:

http://sqlserverplanet.com/dmv-queries/a-better-sp_who2-using-dmvs-sp_who3/


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



I tried to run it but it bombs with these errors:


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


at the "cross apply". I can't see the error! Can someone point it out to me?

Edited by - gbritton on 10/02/2012 09:02:57

sunitabeck
Flowing Fount of Yak Knowledge

5155 Posts

Posted - 10/02/2012 :  09:39:36  Show Profile  Reply with Quote
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
Go to Top of Page

gbritton
Flowing Fount of Yak Knowledge

1764 Posts

Posted - 10/02/2012 :  10:24:09  Show Profile  Reply with Quote
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



I ran the sp_dbcmptlevel proc. It says:
The current compatibility level is 100.

I am running SQL Server 2008 R2.

The error is consistent.

btw, if I replace the er.sql_handle in the function parameter with an actual (hex) handle, it works just fine.

Edited by - gbritton on 10/02/2012 10:35:23
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 10/02/2012 :  10:51:26  Show Profile  Reply with Quote
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?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

gbritton
Flowing Fount of Yak Knowledge

1764 Posts

Posted - 10/02/2012 :  10:59:03  Show Profile  Reply with Quote
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?



The one built-in to SSMS
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 10/02/2012 :  11:36:24  Show Profile  Reply with Quote
Hmm...thats a bit strange.. It worked for me

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

gbritton
Flowing Fount of Yak Knowledge

1764 Posts

Posted - 10/02/2012 :  13:25:10  Show Profile  Reply with Quote
It fails every time for me...on multiple servers with different SQL Server versions
Go to Top of Page

Musawar Mustafa
Starting Member

Pakistan
2 Posts

Posted - 10/10/2012 :  12:15:31  Show Profile  Reply with Quote
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
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 10/11/2012 :  00:39:13  Show Profile  Reply with Quote
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


OP has already specified compatibility level is 100

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000