x

SSRS Reports running slow in sql server 2005

Hello,

We have few of our SSRS reports running very slow. These SSRS reports fetch data from a reporting server database which is acting as subscriber for the replication process. i.e. we have an OLTP DB server where Database 'A' is replicating on reporting sever as Database 'B' from where the SSRS reports fetch their data.

The reports are running very slow when we try executing their SQL query manually. I am new to reading executions plans, so as highlighted its pointing clustered index seek as high operating cost, which I am not sure on how to look upon:

Also, the DB is 350 GB in size and sql server is 2005, SP4.

Moreover, I made sure my reindexing and update stats jobs are running properly and yes they are.(Using OLA's Index script).

Please refer to below execution plans for the SQL query executed from one of the SSRS reports.

alt text

alt text

Kindly help or provide any inputs that may help in resolving this slowness!

Thanks!

screen1.jpg (121.1 kB)
untitled.png (39.2 kB)
more ▼

asked Mar 23, 2015 at 09:42 AM in Default

avatar image

MAXKA
641 2 8 9

(comments are locked)
10|1200 characters needed characters left

1 answer: sort voted first

Hi MAXKA

Looking at the information provided, the estimates and actuals are very different e.g estimated executions 15298 actual 9311176. I'd suggest that you could start by updating the statistics on the tables and see if that helps.

There will generally be somewhere in a plan that appears as the bottle neck and there will generally be something that has a high number, that doesn't mean it's bad - the plan has to come to 100% after all.

How long did it used to take and how long does it take now?

Mart

more ▼

answered Mar 23, 2015 at 12:08 PM

avatar image

Mart
529 1 6 10

Thanks @mart, I've updated stats and auto update stats is also enabled. The job for same also runs. Earlier it used to be 3 minutes and now its 15-20 minutes.

Mar 23, 2015 at 12:30 PM MAXKA

Would you mind checking the stats on the index in question to confirm they are very recent - sometimes, depending on the stats update script used, updates are missed.

Have you got any monitoring to see what's what with the query? Has anything else changed between the 3 minute and 15-20 minute times? What are the parameters that are stored in the plan, and what are the parameters passed to the procedure?

Mar 23, 2015 at 12:36 PM Mart

@mart, how can I check if the stats missed the update they suppose to go through.

Mar 23, 2015 at 12:39 PM MAXKA

I don't think the estimated rows is actually as bad as it first looks. I think the plan is saying it expects 15298 rows for each unique value in the other side of the join (there are 3968 rows coming into that join - if each row has a different value then that gives an expect of around 60 million rows).

There's an article by Gail Shaw here that explains this: http://sqlinthewild.co.za/index.php/2009/09/22/estimated-rows-actual-rows-and-execution-count/comment-page-1/

My question would be can you limit the data at all - there's 90 million records getting processed - that's a lot of data to be dealing with.

Mar 23, 2015 at 01:01 PM mjharper

93K rows through a nested loops join and index seeks? That's a horror show. In fact, I wouldn't be at all surprised to find that you have query hints forcing those choices. It may be statistics related as @Mart and @mjharper have suggested. But it might be the query.

Mar 23, 2015 at 01:18 PM Grant Fritchey ♦♦
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x2036
x75
x23

asked: Mar 23, 2015 at 09:42 AM

Seen: 109 times

Last Updated: Mar 23, 2015 at 02:20 PM

Copyright 2018 Redgate Software. Privacy Policy