SQL Server with Mr. Denny

Oct 10 2011   2:00PM GMT

SSRS runs some crappy queries against the ReportServer Database

Denny Cherry Denny Cherry Profile: Denny Cherry

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

 Comment on this Post

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when other members comment.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to: