Gathering performance informtion

  • I have been tasked to monitor the performance of queries running on our dev/test machine.

    Can I store the output from using Set Statistics Time On ?

    I want to monitor the execution times of stored procedures when used by SSRS reports.

  • Chapter 1 of http://www.red-gate.com/community/books/accidental-dba, there's almost certainly something in http://www.amazon.com/Server-Performance-Tuning-Distilled-Experts/dp/1430219025/ref=tmm_pap_title_0?ie=UTF8&qid=1387523151&sr=8-1 and my manual methods are detailed in https://www.simple-talk.com/sql/performance/finding-the-causes-of-poor-performance-in-sql-server,-part-1/

    Or buy a good 3rd party monitoring tool

    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
  • If you are interested to see which ones of the most often executed SQL Statements /SPs take the longest time,

    I believe it is a good idea to use Profiler Trace data, and then select data from trace ordering it by Duration and/or Count columns

    to see the ones taking the longest time on top of the results.

    Or, from time to time you can run something like this: (and order results by other desired columns as well, depending what you are looking for)

    SELECT CASE WHEN dbid = 32767 then 'Resource' ELSE DB_NAME(dbid)END AS DBName

    ,OBJECT_SCHEMA_NAME(objectid,dbid) AS [SCHEMA_NAME]

    ,OBJECT_NAME(objectid,dbid)AS [OBJECT_NAME]

    ,MAX(qs.creation_time) AS 'cache_time'

    ,MAX(last_execution_time) AS 'last_execution_time'

    ,MAX(usecounts) AS [execution_count]

    ,SUM(total_worker_time) / SUM(usecounts) AS AVG_CPU

    ,SUM(total_elapsed_time) / SUM(usecounts) AS AVG_ELAPSED

    ,SUM(total_logical_reads) / SUM(usecounts) AS AVG_LOGICAL_READS

    ,SUM(total_logical_writes) / SUM(usecounts) AS AVG_LOGICAL_WRITES

    ,SUM(total_physical_reads) / SUM(usecounts)AS AVG_PHYSICAL_READS

    FROM sys.dm_exec_query_stats qs

    join sys.dm_exec_cached_plans cp on qs.plan_handle = cp.plan_handle

    CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle)

    WHERE objtype = 'Proc'

    AND text

    NOT LIKE '%CREATE FUNC%'

    GROUP BY cp.plan_handle,DBID,objectid ORDER BY (MAX(usecounts) * (SUM(total_worker_time) / SUM(usecounts))) desc

    Likes to play Chess

  • itwhiz (12/19/2013)


    I have been tasked to monitor the performance of queries running on our dev/test machine.

    Can I store the output from using Set Statistics Time On ?

    I want to monitor the execution times of stored procedures when used by SSRS reports.

    If you are only interested in the run times of the procedures from SSRS reports, you can also extrapolate that from the ReportServer database.

    Run statistics are kept in the ReportServer database, so you can query the ExecutionLog.

    You should be able to tell which procs are associated to which reports and tune from there.

    Jason...AKA CirqueDeSQLeil
    _______________________________________________
    I have given a name to my pain...MCM SQL Server, MVP
    SQL RNNR
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

Viewing 4 posts - 1 through 3 (of 3 total)

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