Querying ExecutionLog in SQL Server Reporting Services: discretion is the better part of valor


I have to admit that I fairly
regularly query the ReportServer..ExecutionLog table to look at the
performance characteristics of my reports rather than using the
RS_ExecutionLog_Update.dts package like I’m supposed to. I’m frankly
lazy, and don’t want to manually execute the package over and over
again when I’m trying various things to try and speed up a report.



Whenever I’ve seen discussions around ExecutionLog internally, there
was a very good chance that Dave Wickert (a PM on the SQL BI Team)
would chime in and warn people not to query the table directly. Being hard-headed, I continued to do my thing.



Today, I
finally got curious enough to try and discover *why* he cautioned
against this technique, and found out that we can actually block
reports from rendering if the queries we issue against ExectionLog lock
the table. For example, this statement (yes, it’s overkill, but it
illustrates my point) will prevent ALL reports from rendering until you
rollback or commit the transaction which is opened:



BEGIN TRAN

UPDATE ExecutionLog WITH (TABLOCK) SET InstanceName = ‘other’ WHERE InstanceName = ‘myReportServerMineAllMine’

–ROLLBACK TRAN



Yeesh. So, lesson learned…don’t query ExecutionLog manually, or at least use the NOLOCK hint.





Comments (2)

  1. Lance says:

    If you setup the DTS package to execute within a SQL Job you can at least minimize the extra step of manually kicking it off.

    In my dev environment I use a 15 to 30-minute schedule and 99% of the time the job runs and simply finds no data to add so it quickly exits. I rarely run into any problems with delays this way – unless I’m doing performance testing/tuning where I need more rapid results.

    Also, if you use the pre-built RSExecutionLog reports, they (mostly) help you speed up the effort. I have even played with setting up data-driven subscriptions in Dev where it searches for my User Name and emails me a report containing any needed results in MSHTML so I don’t even have to run the report.

    Also, for production environments, I typically setup a nightly Sql Job so the process doesnt impact server peformance.

    Hope this helps…

  2. Joey Joe Joe says:

    why not just do a dirty read of the ExecutionLog?