Determine slow running reports

I've been tasked with optimizing our reporting environment and want to
target the worst performers first. The problem is that our reports are
published directly to the reporting server by reporting analysts
throughout the organization so I have very little knowled as to what's
out there, who uses them, etc. Is there a way to query reporting
services to determine historical exectuion times? IOW, give me the top
10 reports ordered by avg exectuion time descending or something of
that nature.In your data source defenition - instead of single query use a stored
procedure or just type in something like that:
declare @.start smalldatetime
declare @.end smalldatetime
set @.start = getdate()
-- your query
set @.end=getdate()
-- and at the end log the execution time like that
insert logtable values (reportname, username, getdate(), @.end-@.start)
"Randy Knight" <knight.randy@.gmail.com> wrote in message
news:1128523651.640818.14420@.o13g2000cwo.googlegroups.com...
> I've been tasked with optimizing our reporting environment and want to
> target the worst performers first. The problem is that our reports are
> published directly to the reporting server by reporting analysts
> throughout the organization so I have very little knowled as to what's
> out there, who uses them, etc. Is there a way to query reporting
> services to determine historical exectuion times? IOW, give me the top
> 10 reports ordered by avg exectuion time descending or something of
> that nature.
>|||That would be great if I had control of the reports but as I said, the
reports are published by analysts from all over the company. I just
wondered if RS put some statistics or anything like that in the
ReportServer database. It would seem to be useful ... how many times
report is run, avg execution times, etc.
Guess I'll resort to good ol' SQL Profiler :)|||SQL 2005 has just what you need... You must install the samples from the SQL
install CD.. This will give you a directory in program files/microsoft sql
server 2005/Report samples/Server management Samples. --
THe directions are included... You can get all kinds of performance related
reports, who runs what, how long it takes, how many times each report has
been run...
Hope this helps
Wayne Snyder MCDBA, SQL Server MVP
Mariner, Charlotte, NC
I support the Professional Association for SQL Server ( PASS) and it''s
community of SQL Professionals.
"Randy Knight" wrote:
> I've been tasked with optimizing our reporting environment and want to
> target the worst performers first. The problem is that our reports are
> published directly to the reporting server by reporting analysts
> throughout the organization so I have very little knowled as to what's
> out there, who uses them, etc. Is there a way to query reporting
> services to determine historical exectuion times? IOW, give me the top
> 10 reports ordered by avg exectuion time descending or something of
> that nature.
>