Gathering performance informtion

Question

Gathering performance informtion

itwhiz

Mr or Mrs. 500

Points: 531
More actions
December 19, 2013 at 4:51 pm

#283736

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.

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

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

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

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

VoldemarG Hall of Fame Points: 3883 More actions · Answer 2

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

SQLRNNR SSC Guru Points: 281334 More actions · Answer 3

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