How to find which SQL Server process was running at a particular time?

Question

How to find which SQL Server process was running at a particular time?

coolchaitu

SSChampion

Points: 10332
More actions
April 12, 2018 at 11:08 am

#328293

Good Morning Experts,
We received an email from the operations team that they are seeing frequent high CPU alerts on a particular server. These alerts occur around 5:20 AM EST . And, they are saying that high CPU is due to sqlserver process that runs at these times. My question is , how to find which sql server process was running at 5:20 AM EST? Any script or something like that. Could you please help me.

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Sue_H SSC Guru Points: 90860 More actions · Answer 1

coolchaitu - Thursday, April 12, 2018 11:08 AM
Good Morning Experts,
We received an email from the operations team that they are seeing frequent high CPU alerts on a particular server. These alerts occur around 5:20 AM EST . And, they are saying that high CPU is due to sqlserver process that runs at these times. My question is , how to find which sql server process was running at 5:20 AM EST? Any script or something like that. Could you please help me.

No, you can't find something like that after the fact. You can check the trace file and see if you can find anything in the trace for around that time - it may have some clues.
I'd probably check what jobs were running during that time.

Sue

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 2

If you did not have something monitoring what was running at that time, then you will no be able to tell what was running at the time.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

ZZartin SSC-Dedicated Points: 30901 More actions · Answer 3

Also keep in mind SQL Server using CPU is not inherently a bad thing.

Grant Fritchey SSC Guru Points: 398716 More actions · Answer 4

You should set up a server-side trace that starts around 5AM and ends around 6AM so you can observe the queries running on the server. Here's the basics for that.

This is assuming you're on 2008 or R2. If not, Extended Events

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

coolchaitu SSChampion Points: 10332 More actions · Answer 5

GilaMonster - Thursday, April 12, 2018 11:37 AM
If you did not have something monitoring what was running at that time, then you will no be able to tell what was running at the time.

Thanks for replying Gail. Can we use the server side trace to find ? Some script in this direction.

coolchaitu SSChampion Points: 10332 More actions · Answer 6

Grant Fritchey - Thursday, April 12, 2018 12:30 PM
You should set up a server-side trace that starts around 5AM and ends around 6AM so you can observe the queries running on the server. Here's the basics for that.
This is assuming you're on 2008 or R2. If not, Extended Events

Thanks for your reply Grant Fritchey. Is there anyway we can extract information from default trace.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 7

coolchaitu - Thursday, April 12, 2018 12:43 PM
GilaMonster - Thursday, April 12, 2018 11:37 AM
If you did not have something monitoring what was running at that time, then you will no be able to tell what was running at the time.
Thanks for replying Gail. Can we use the server side trace to find ? Some script in this direction.

Yup, server side trace will work nicely, providing it's running over the time period in question.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Grant Fritchey SSC Guru Points: 398716 More actions · Answer 8

coolchaitu - Thursday, April 12, 2018 12:45 PM
Grant Fritchey - Thursday, April 12, 2018 12:30 PM
You should set up a server-side trace that starts around 5AM and ends around 6AM so you can observe the queries running on the server. Here's the basics for that.
This is assuming you're on 2008 or R2. If not, Extended Events
Thanks for your reply Grant Fritchey. Is there anyway we can extract information from default trace.

Nope. That doesn't track queries in that fashion.

Now,2012 or higher, if the query really used that many resources, you can use the system_health extended events session. It captures queries that use excessive amounts of resources. However, since we're in a 2008 forum, I'm assuming that's not available to you.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning