SSRS Report Execution Time

dba2dba
dba2dba used Ask the Experts™
on
Hello Experts,

I have a situation where a report on Production Server takes 2 min 30 secs while on Dev takes 40 secs.

We are on SQL Server 2005 Ent. Edition.

Hardware, Data and configurations are identical. The test is done at 12.00 Midnte to isolate application load on prod from being the reason for difference.

Can you please point me some areas to look into as what is causing the difference.

Thanks,
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®

Commented:
are you using the same user?

are you sure indexes match?

can you run the report from bid ( to the prod server)

if you run the sql code on the prod server how long does it take from sql manager
Raja Jegan RSQL Server DBA & Architect, EE Solution Guide
Awarded 2009
Distinguished Expert 2018

Commented:
Out of statistics indexes can cause these kind of issues at some times.
Try rebuilding all indexes using the script given below:

USE ur_db_name
GO
sp_msforeachtable ‘ALTER INDEX ALL ON ? REBUILD WITH (FILLFACTOR = 80)’;

Author

Commented:
I just restored the latest production copy on to Dev and executed a stored procedure against both the environments.

On Prod it takes 1 min 18 Seconds and on Dev it takes 6 seconds.

Data, Indexes and everything is identical.

Any pointers ...

Thanks,

Author

Commented:
Hello Experts,

I have traced the stored procedure exectuion in prod and dev instances.

Below are the details:

Development:
CPU: 1919
READS: 240259
DURATION: 1870

Production:

CPU: 71016
READS: 87627059
DURATION: 71832

I have also executed the queries by setting up the option
SET STATISTICS IO ON

and verified that there are far too many logical reads on the tables in production that on the development.

Also, both on Prod and Dev, no index or db mgmt jobs are setup now (I'm working on setting these up)

Please suggest me if you have any infomration on what is wrong with prod Server.

Thanks
SQL Server DBA & Architect, EE Solution Guide
Awarded 2009
Distinguished Expert 2018
Commented:
Once you restore a database, it would be out of statistics and its recommended to Rebuild index using the script provided in my earlier comment. Just run this script in your production database now and check again..

USE ur_db_name
GO
sp_msforeachtable ‘ALTER INDEX ALL ON ? REBUILD WITH (FILLFACTOR = 80)’;

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial