Querying msdb for job history


I recently had the need to create an SSRS report showing the status of some jobs from the 10 last days, so I wrote this script that I thought could come in handy to others as well. The contents should be pretty self explanatory except maybe for the RowNum column which is there to separate each iteration of the job:

WITH cte_jobhistory AS (
		--> Creating a cte only to make the columns run_date and run_time into a "datetime-friendly" format  
		--  so it can be used for filtering
		SELECT 
			RunDateString = SUBSTRING(CAST(run_date as VARCHAR(20)), 1, 4) + '-' +  SUBSTRING(CAST(run_date as VARCHAR(20)), 5, 2) + '-' + SUBSTRING(CAST(run_date as VARCHAR(20)), 7, 2),
			RunTimeString = SUBSTRING(RIGHT('000000' + CAST(run_time as VARCHAR(20)), 6), 1, 2) + ':' + SUBSTRING(RIGHT('000000' + CAST(run_time as VARCHAR(20)), 6), 3, 2) + ':' + SUBSTRING(RIGHT('000000' + CAST(run_time as VARCHAR(20)), 6), 5, 2),
			*
		FROM msdb.dbo.sysjobhistory
		)
SELECT 
	a.name,
	RowNum = ROW_NUMBER() OVER (PARTITION BY a.name, b.step_id ORDER BY instance_id DESC),
	b.step_id,
	b.step_name,
	b.message,
	RunStatus = CASE b.run_status 
		WHEN 0 THEN 'Failed'
		WHEN 1 THEN 'Succeded' 
		WHEN 2 THEN 'Retry' 
		WHEN 3 THEN 'Cancelled' 
		WHEN 4 THEN 'In Progress' 
		END,
	b.run_duration,
	b.RunDateString,
	b.RunTimeString
FROM msdb.dbo.sysjobs a
	INNER JOIN cte_jobhistory b
		ON a.job_id = b.job_id
WHERE CONVERT(datetime, b.RunDateString) > GETDATE()-10
ORDER BY a.name ASC, b.instance_id DESC

SQL Server Reporting Services performance monitoring


I’ve been doing a little bit of SSRS performance tuning lately and it has been somewhat of a challenge since I’m not all too familiar with Reporting Services. I was however thrilled when I found out about the ExecutionLog2-view in the ReportServer database and the fact that it has timings for the various parts of rendering each and every report that is run. The format of the view is somewhat self-explanatory but there is an xml column with some extra info that needs to be panned out as well, so I created this simple query to get it all out in a nice tabular format. (Detailed descriptions of the columns can be found on Robert Bruckners blog: http://blogs.msdn.com/b/robertbruckner/archive/2009/01/05/executionlog2-view.aspx)

USE ReportServer
GO
SELECT TOP 100
  Report = REVERSE(LEFT(REVERSE(ReportPath),CHARINDEX('/', REVERSE(ReportPath),1)-1)),
  Username,
  Parameters,
  TimeStart,
  TotalDurationMS = DATEDIFF(ms, TimeStart, TimeEnd),
  DurationDataRetrievalMS = TimedataRetrieval,
  DurationProcessingMS = TimeProcessing,
  DurationRendering = TimeRendering,
  [RowCount],
  ReportSizeKB = CAST(ByteCount/1024.0 AS decimal(18, 2)),
  ProcessingEngine = CAST(AdditionalInfo.query('data(/AdditionalInfo/ProcessingEngine)') AS varchar(200)),
  ScalabilityPagination = CAST(AdditionalInfo.query('data(/AdditionalInfo/ScalabilityTime/Pagination)') AS varchar(200)),
  ScalabilityProcessing = CAST(AdditionalInfo.query('data(/AdditionalInfo/ScalabilityTime/Processing)') AS varchar(200)),
  EstMemPaginationKB = CAST(AdditionalInfo.query('data(/AdditionalInfo/EstimatedMemoryUsageKB/Pagination)') AS varchar(200)),
  EstMemProcessingKB = CAST(AdditionalInfo.query('data(/AdditionalInfo/EstimatedMemoryUsageKB/Processing)') AS varchar(200)),
  DBQueryTimes = CAST(AdditionalInfo.query('data(/AdditionalInfo/DataExtension/SQL)') AS varchar(200))  
FROM ExecutionLog2 WITH (NOLOCK)
WHERE ReportPath <> 'Unknown'
ORDER BY TimeStart DESC