Can SentryOne be modified to optimize this problem query with Reporting Services?

This query is executed every 2 - 3 seconds and averages 55 seconds in duration. It is the highest Top SQL query in our production database by sorted by CPU usage.

avatar image By Tom Sweet 1 asked Apr 26 at 07:20 PM
more ▼
(comments are locked)
avatar image Suril Jasani ♦♦ Apr 26 at 07:52 PM

Tom,

Can you also tell us what's listed in the Application column for those events (in the Top SQL tab)?

Thanks,

Suril

avatar image Tom Sweet Apr 26 at 07:54 PM

"SentryOne 11.0-Server" is the application listed.

avatar image Suril Jasani ♦♦ Apr 26 at 08:04 PM

Thank you. As Aaron suggested, please submit this to support@sentryone.com so we can have a closer look.

10|10000 characters needed characters left

1 answer: sort voted first

I wonder if you need to keep that much ExecutionLog history or if you can purge some of the older data. I also can't think of a reason that should be running as frequently as you suggest - is it possible you created an Advisory Condition that looks at SSRS? What exactly is the application name in Top SQL? Could you provide a screen shot?

In any case, could you generate an actual plan after creating this index?

  CREATE INDEX IX_LastStartPerReport ON dbo.ExecutionLogStorage
    (ReportID, TimeStart DESC)
  WHERE ReportAction = 1;

There are some other index changes that could lead to further efficiencies, but this should take care of the problem exhibited when ExecutionLog gets rather large.

If adding indexes isn't supported at all (I'm not familiar with SSRS / ReportServer database support policies), or if that index doesn't help, please open a support ticket (e-mail support@SentryOne.com). Even if it does help, I'm still interested to find out what could be causing that query to run so often...

avatar image By Aaron Bertrand ♦ 1.7k answered Apr 26 at 07:33 PM
more ▼
(comments are locked)
10|10000 characters needed characters left
Your answer
toggle preview:

Up to 50 attachments (including images) can be used with a maximum of 209.7 MB each and 209.7 MB total.

Follow this question

Topics:

x641
x35
x2
x1
x1

asked: Apr 26 at 07:20 PM

Seen: 62 times

Last Updated: Apr 27 at 01:50 AM