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!
LVL 1
AutomatedITAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Barry CunneyCommented:
-- list current sql server processes
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/2) + 1, --#1
((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) + 1) AS [Individual Query]
,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.sql_handle)as qt
WHERE session_Id > 50
AND session_Id NOT IN (@@SPID)
ORDER BY (sp.cpu + sp.memusage + sp.physical_io) DESC
0
AutomatedITAuthor Commented:
Where do I put in my ID Thread Number?

(Sorry, complete beginner in SQL)
0
Barry CunneyCommented:
Hi AutomatedIT,
In Microsoft SQL Server Management Studio, choose New Query and enter and execute the SQL that I posted, as is - This will list all current SQL processes and then look at the spid and ecid columns ,
The SQL I posted is sorted by CPU, so processes using most CPU will be at the top of the list

If you can relate the spid to the thread number you mention then I can help you modify the above SQL with a WHERE clause
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.