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.
[sql]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[/sql]
