AutomatedIT
asked on
Find SQL Database or Process Related to SQLserver ID Thread
Our SQL server is continually running at full CPU and we have identified the process as SQLservr. Running Process Monitor we have identified a couple of ID Threads underneath the SQLservr process that are showing very high % Processor Time.
Now that we have some ID Threads to work with, how do I tie these threads back to SQL databases or queries to fully identify the cause of our performance loss?
(SQL Server 2008 R2)
Thank you!
Now that we have some ID Threads to work with, how do I tie these threads back to SQL databases or queries to fully identify the cause of our performance loss?
(SQL Server 2008 R2)
Thank you!
ASKER
Where do I put in my ID Thread Number?
(Sorry, complete beginner in SQL)
(Sorry, complete beginner in SQL)
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
SELECT
Hostname
,nt_username
,program_name
,sp.login_time
,qt.text AS [Parent Query]
,sp.cpu
,sp.memusage
,sp.physical_io
,er.total_elapsed_time
,session_Id AS [Spid]
,er.blocking_session_id
,er.granted_query_memory
,er.start_time
,ecid
,DB_NAME(sp.dbid) AS [Database]
,er.status
,er.wait_resource
,wait_type
,SUBSTRING (qt.text, (er.statement_start_offset
((CASE WHEN er.statement_end_offset = -1
THEN LEN(CONVERT(NVARCHAR(MAX),
ELSE er.statement_end_offset
END - er.statement_start_offset)
,nt_domain
FROM sys.dm_exec_requests er --#2
INNER JOIN sys.sysprocesses sp ON er.session_id = sp.spid
CROSS APPLY sys.dm_exec_sql_text(er.sq
WHERE session_Id > 50
AND session_Id NOT IN (@@SPID)
ORDER BY (sp.cpu + sp.memusage + sp.physical_io) DESC