次の方法で共有

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

  • Anonymous
    August 12, 2005
    The comment has been removed
  • Anonymous
    September 11, 2008
    why not just do a dirty read of the ExecutionLog?