Please start any new threads on our new site at http://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | 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
 Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

gbritton
Flowing Fount of Yak Knowledge

2780 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

2780 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
52326 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

2780 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
52326 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

2780 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
52326 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  
 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.24 seconds. Powered By: Snitz Forums 2000