Script to findout old prcesses in sql 2008 R2

Question

Script to findout old prcesses in sql 2008 R2

sri2015

SSCrazy

Points: 2780
More actions
January 9, 2015 at 4:55 am

#397854

I need script to find out the old processes (since one week back) in SQL 2008 R2 including columns username, host name, program name, cpu, physicalIO..etc
I tried with DMV's, views but all are current processes and not included above columns.
if any one post this script would be appreciate.

Viewing 7 posts - 1 through 6 (of 6 total)

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

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

You're looking for current sessions which have been connected a long time, or every single connection which has been made since a particular date?

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

sri2015 SSCrazy Points: 2780 More actions · Answer 2

Yes, my requirement is some users processes are utilizing more cpu and Physical IO daily. i need whoever using more resources for previous days also.

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

That's not tracked by default. You'll need to implement some form of monitoring or auditing.

But if you're trying to track performance, looking at connections isn't necessarily the way to go. Try these

https://www.simple-talk.com/sql/performance/finding-the-causes-of-poor-performance-in-sql-server,-part-1/

https://www.simple-talk.com/sql/performance/finding-the-causes-of-poor-performance-in-sql-server,-part-2/

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

sri2015 SSCrazy Points: 2780 More actions · Answer 4

Thanks for links provided. by using this links, could i get processes details for previous day also ?

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

No. But if you're trying to tune overall workload, looking at just connections is not going to give you sufficient information.

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: 398715 More actions · Answer 6

You can see an aggregate of query performance from the queries that are still in cache by looking at sys.dm_exec_query_stats.

But, understand, it doesn't show individual calls. It shows aggregate calls. It also doesn't record who made the calls, because again, it's an aggregate. It doesn't store parameters, aggregate. However, it will show you activity that has been occurring on your system. It might prove a little bit helpful until you get appropriate monitoring set up as Gail outlined.

"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