SSRS runs some crappy queries against the ReportServer Database

Published On: 2011-10-10By:

While doing some research for my Half Day SQL PASS Session with Stacia Misner (blog | @staciamisner) I discovered that some of the queries which the SSRS engine runs against the ReportServer database are less than perfect total crap.  My specific problem is that there are key lookups on the bulk of the queries.

Granted most of the queries results are pretty small, but these queries run every 10 seconds, and crap running every 10 seconds adds up to a whole lot of crap.

Below are some indexes you can create to resolve these problems.  Keep in mind that adding this will probably make Microsoft not help you if you call, so be sure to delete the indexes if you call CSS for support.

These indexes will minimize the index scans and key lookups.  I can’t remove all of them because of the way that the queries and the schema are designed and I’m not about to go around changing the schema of the tables or hacking the inline code of the SSRS UI.  So while these indexes won’t fix every problem, some is better then none.

CREATE NONCLUSTERED INDEX [mrdenny_IX_Notifications3] ON [dbo].[Notifications]
(
[NotificationEntered] ASC,
ProcessStart,
ProcessAfter
)
include (NotificationID)
GO
CREATE INDEX mrdenny_PK_Catalog on dbo.Catalog
(ItemID)
include (Path, Type, PolicyId)
GO
CREATE INDEX mrdenny_IX_Event_TimeEntered on dbo.Event
(ProcessStart, TimeEntered)
include (EventID)
GO
CREATE INDEX mrdenny_IX_BatchID on dbo.Event
(BatchID, TimeEntered)
include (EventID, EventType, EventData)
with (drop_existing=on)
GO
CREATE INDEX mrdenny_IX_UpgradeID on dbo.ServerUpgradeHistory
(UpgradeID DESC)
include (ServerVersion)
GO

Contact the Author | Contact DCAC

Video

Globally Recognized Expertise

As Microsoft MVP’s and Partners as well as VMware experts, we are summoned by companies all over the world to fine-tune and problem-solve the most difficult architecture, infrastructure and network challenges.

And sometimes we’re asked to share what we did, at events like Microsoft’s PASS Summit 2015.

Awards & Certifications

Microsoft Partner       Insights Sccess Award    Technology Headlines Award    Golden Bridge Gold Award    CIO Review Top 20 Azure Solutions Providers    VMWare Partner
Microsoft Certified Master    Microsoft MVP
Share via