MSBI Tutor

 --Transact-SQL to list retrieval, processing and rendering duration times of SSRS reports executions. /* The most often complain of users is "My report is running that slowly, why?" and when you run it as admin, it works normal. With this Transact-SQL script you can check the execution durations of report splitted in data retrieval, processing and rendering durations to analyse which part takes to most time and if it's really slow. Please remark: Querying the ReportServer database directly is not a supported way. Works with SQL Server 2005 / Reporting Services 2005 and higher version in all editions. Requires select permissions on the ReportServer database. */ -- List processing duration times of SSRS reports executions. DECLARE @begin datetime, @end datetime, @minDuration int; -- Include executions of the last month. SET @begin = DATEADD(m, -1, GETDATE()); SET @end   = GETDATE(); SET @minDuration = 3000;  -- min Duration in Millisecondes = 3 sec. ;WITH

-- Transact-SQL to query datasets with command text for all SSRS reports. /* This Transact-SQL script list for all SSRS reports the used datasources and datasets with the command text. Requires select permissions on the ReportServer database. */ -- List datasets with command text for all SSRS reports ;WITH  XMLNAMESPACES      (DEFAULT 'http://schemas.microsoft.com/sqlserver/reporting/2008/01/reportdefinition'              ,'http://schemas.microsoft.com/sqlserver/reporting/reportdesigner'       AS rd) ,DEF AS     (SELECT RPT.ReportPath            ,R.RptNode.value('(./Query/DataSourceName)[1]', 'nvarchar(425)') AS DataSourceName            ,R.RptNode.value('@Name[1]', 'nvarchar(425)') AS DataSetName            ,REPLACE(REPLACE(LTRIM((R.RptNode.value('(./Query/CommandText)[1]', 'nvarchar(4000)')))                     ,'&gt;', '>')                     ,'&lt;', '<'