script about finding lockings, CPU usage, Memeroy usage of a query running on the SQL 2012server

Hi,

Can some one send me a script to find out lockings, CPU usage, Memory consumption of a currently running query on the SQL server?

thanks in advance.

,
ken hanseAsked:
Who is Participating?
 
Anthony PerkinsCommented:
All you need is sp_whoisactive.
0
 
Kyle AbrahamsSenior .Net DeveloperCommented:
To Get the memory usage:
SELECT 
    TEXT
    ,query_plan
    ,requested_memory_kb
    ,granted_memory_kb
    ,used_memory_kb 
FROM sys.dm_exec_query_memory_grants emg
CROSS APPLY sys.dm_exec_sql_text(sql_handle)  
CROSS APPLY sys.dm_exec_query_plan(emg.plan_handle)
ORDER BY emg.requested_memory_kb DESC

Open in new window


or
For memory (this is aggregate not a user by user basis):
http://support.microsoft.com/kb/271624



For Locks: (credit: http://www.techrepublic.com/blog/the-enterprise-cloud/write-your-own-sp-lock-system-stored-procedure-in-sql-server-2005/)
USE MASTER
GO


CREATE  PROCEDURE [dbo].[sp_LockDetail]


AS


                                        

        

            

        

    

            
BEGIN


    SELECT 


        SessionID = s.Session_id,    


        

    

        resource_type,   


        DatabaseName = DB_NAME(resource_database_id),


        request_mode,


        request_type,


        login_time,


        host_name,


        program_name,


        client_interface_name,


        login_name,


        nt_domain,


        nt_user_name,


        s.status,


        last_request_start_time,


        last_request_end_time,


        s.logical_reads,


        s.reads,


        request_status,


        request_owner_type,


        objectid,


        dbid,


        a.number,


        a.encrypted ,


        a.blocking_session_id,


        a.text       


    FROM   


        sys.dm_tran_locks l


        JOIN sys.dm_exec_sessions s ON l.request_session_id = s.session_id


        LEFT JOIN   


        (


            SELECT  *


            FROM    sys.dm_exec_requests r


            CROSS APPLY sys.dm_exec_sql_text(sql_handle)


        ) a ON s.session_id = a.session_id


    WHERE  


        s.session_id > 50


END

Open in new window


For cpu usage:  (credit:  http://www.sqlservercentral.com/Forums/Topic611107-146-1.aspx)

DECLARE @ts_now BIGINT
	SELECT @ts_now = cpu_ticks / CONVERT(FLOAT, cpu_ticks_in_ms) FROM sys.dm_os_sys_info
	
	SELECT record_id,
		DATEADD(ms, -1 * (@ts_now - [timestamp]), GETDATE()) AS EventTime, 
		SQLProcessUtilization,
		SystemIdle,
		100 - SystemIdle - SQLProcessUtilization AS OtherProcessUtilization
	FROM (
		SELECT 
			record.value('(./Record/@id)[1]', 'int') AS record_id,
			record.value('(./Record/SchedulerMonitorEvent/SystemHealth/SystemIdle)[1]', 'int') AS SystemIdle,
			record.value('(./Record/SchedulerMonitorEvent/SystemHealth/ProcessUtilization)[1]', 'int') AS SQLProcessUtilization,
			TIMESTAMP
		FROM (
			SELECT TIMESTAMP, CONVERT(XML, record) AS record 
			FROM sys.dm_os_ring_buffers 
			WHERE ring_buffer_type = N'RING_BUFFER_SCHEDULER_MONITOR'
			AND record LIKE '% %') AS x
		) AS y 
	ORDER BY record_id DESC


SELECT TOP 10 (total_worker_time * 1.0) / 1000000 AS CPU_Utilized_in_Seconds, text,* 
FROM sys.dm_exec_query_stats
CROSS APPLY sys.dm_exec_sql_text(sql_handle)

Open in new window

0
 
ken hanseAuthor Commented:
contribution from all helpful.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.