Results 1 to 2 of 2
  1. #1
    Join Date
    Sep 2009
    Posts
    4

    Unanswered: Advanced question - query/script

    In oracle there is a built in report which identifies the most costly queries for that "tuning" can take place for those queries and therefore speed up the database.

    I'm looking for something similiar in SQL Server 2005/2008 but have only been able to find "instant" based answers (only tell which are the most costly at that moment in time as opposed to most oft used/costly).

    Any ideas?

  2. #2
    Join Date
    Oct 2009
    Posts
    19
    This will give you the average running time for the top 25 slowest processes

    Code:
    SELECT TOP 25 DB_NAME(st.dbid) AS 'Database name',
     execution_count AS 'Execution count',
      (total_worker_time) / qs.execution_count * 2 AS 'Average running time (Microseconds)'
      , (total_worker_time/1000000) / qs.execution_count AS 'Average running time (Seconds)'
      , [text] AS 'Command' FROM master.sys.dm_exec_query_stats as qs
       CROSS APPLY master.sys.dm_exec_sql_text(qs.sql_handle)
     st ORDER BY (total_worker_time) / qs.execution_count desc
    -----------------------------------
    Free SQL server monitoring for DBA's
    SQL DBA manager by BlueThames.com

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •