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.
Comment Notification
If you would like to receive an email when updates are made to this post, please register here
Subscribe to this post's comments using
