SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Gathering performance informtion


Gathering performance informtion

Author
Message
itwhiz
itwhiz
SSC Rookie
SSC Rookie (39 reputation)SSC Rookie (39 reputation)SSC Rookie (39 reputation)SSC Rookie (39 reputation)SSC Rookie (39 reputation)SSC Rookie (39 reputation)SSC Rookie (39 reputation)SSC Rookie (39 reputation)

Group: General Forum Members
Points: 39 Visits: 95
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.
GilaMonster
GilaMonster
SSC Guru
SSC Guru (118K reputation)SSC Guru (118K reputation)SSC Guru (118K reputation)SSC Guru (118K reputation)SSC Guru (118K reputation)SSC Guru (118K reputation)SSC Guru (118K reputation)SSC Guru (118K reputation)

Group: General Forum Members
Points: 118486 Visits: 45545
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
VoldemarG
SSC Veteran
SSC Veteran (247 reputation)SSC Veteran (247 reputation)SSC Veteran (247 reputation)SSC Veteran (247 reputation)SSC Veteran (247 reputation)SSC Veteran (247 reputation)SSC Veteran (247 reputation)SSC Veteran (247 reputation)

Group: General Forum Members
Points: 247 Visits: 252
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


Voldemar
likes to play chess
SQLRNNR
SQLRNNR
SSC-Forever
SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)

Group: General Forum Members
Points: 41271 Visits: 18565
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

Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search