Script to findout old prcesses in sql 2008 R2

  • 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.

  • 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
  • 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.

  • 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
  • Thanks for links provided. by using this links, could i get processes details for previous day also ?

  • 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
  • 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

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

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