March 4, 2014 at 3:22 am
Ok sorry for the confusing title.
So I'm managing several servers, one is having serious performance issues.
One potential cause are several queries developed by an app team that run update statements on tables that currently contain at least 27 million records.
Besides that yesterday I saw a SPID with a lot of parallel runs and low IO compared to high cpu,now before I could look further into it the process that ran had ended.
So the question I'm having is can I find out what this SPID ran more then a day after the fact.
March 4, 2014 at 3:50 am
did you able to get the query ?
try this , it will show you the top most running queries for that server. hope you will able to find something.
SELECT DISTINCT TOP 10
t.TEXT QueryName,
s.execution_count AS ExecutionCount,
s.max_elapsed_time AS MaxElapsedTime,
ISNULL(s.total_elapsed_time / s.execution_count, 0) AS AvgElapsedTime,
s.creation_time AS LogCreatedOn,
ISNULL(s.execution_count / DATEDIFF(s, s.creation_time, GETDATE()), 0) AS FrequencyPerSec
FROM sys.dm_exec_query_stats s
CROSS APPLY sys.dm_exec_sql_text( s.sql_handle ) t
ORDER BY
s.max_elapsed_time DESC
GO
March 4, 2014 at 3:51 am
If you didn't have any trace during the time that it ran, I don't think that there is a way to get the session's ID nor any other detail about this session.
Adi
--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
March 4, 2014 at 3:53 am
Adi Cohn-120898 (3/4/2014)
If you didn't have any trace during the time that it ran, I don't think that there is a way to get the session's ID nor any other detail about this session.Adi
Correct, this information isn't logged unless you have some sort of monitoring setup.
Potentially, you could use a third party application to scan the transaction log backups to find some information depending on the query.
March 4, 2014 at 4:54 am
Ok tanks for the information I have passed this on,unfortunately it seems we can't do much about the big monster update queries until that app team responds to our request the redo them.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply