Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Gathering performance informtion Expand / Collapse
Author
Message
Posted Thursday, December 19, 2013 4:51 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, April 22, 2014 2:49 AM
Points: 18, Visits: 72
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.
Post #1524834
Posted Friday, December 20, 2013 12:13 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 11:54 AM
Points: 42,466, Visits: 35,530
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 2008, MVP
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

Post #1524884
Posted Thursday, December 26, 2013 2:10 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, April 28, 2014 10:18 AM
Points: 39, Visits: 91
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
Post #1526051
Posted Thursday, December 26, 2013 2:30 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 1:09 PM
Points: 21,251, Visits: 14,955
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


SQL RNNR

Posting Performance Based Questions - Gail Shaw
Posting Data Etiquette - Jeff Moden
Hidden RBAR - Jeff Moden
VLFs and the Tran Log - Kimberly Tripp
Post #1526056
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse