Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2012 Forums
 Analysis Server and Reporting Services (2012)
 Report very slow or times out

Author  Topic 

sqlbug
Posting Yak Master

201 Posts

Posted - 2013-05-21 : 16:11:30
I've this report that uses a stored procedure.

For the exact same parameters - the Profiler shows the procedure takes less than 2 second to run. The query analyzer shows the same result.

Therefore - I ruled out the procedure.

What are the other areas I should look at?
In the report - there are 5 levels of grouping for 5 parameters. But this report used to run fine before and now doing this.

Any ideas?
Thanks.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-05-22 : 01:10:22
can you profile and check if only this procedure is getting called when report is rendered? It may be that there are some other datasets also involved in it which may call some other code as well in parallel.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

sqlbug
Posting Yak Master

201 Posts

Posted - 2013-05-22 : 16:43:49
Hi Vishakh, Yes I did check and that's not the case.
The report is hosted on a .NET page.

We also found - it runs on another backup server fine, but times out on the server where it's deployed now. That indicates - it's not any coding or procedure issue. It's something to do with other factors associated with the server.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-05-23 : 00:45:49
are you sure the statistics etc of associated objects are updated in this server? Is there a major difference between this server configuration against backup server?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

sqlbug
Posting Yak Master

201 Posts

Posted - 2013-05-23 : 14:27:27
I am going to double check on all of these. The operating systems are different - the deployment server is old with Windows Server 2003 and IIS 6.
The backup server is newer 2008 R2 and IIS 7.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-05-24 : 00:31:39
hmm...is there a way to get it upgraded and then test?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

sqlbug
Posting Yak Master

201 Posts

Posted - 2013-05-24 : 17:47:29
We are looking at different options, including moving it to a newer virtual machine.
I'll let you know if anything comes up.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-05-25 : 09:34:40
ok...will wait for your response

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2013-05-25 : 15:24:14
You have to look at the execution plan of the stored procedure (on the slow server). Also may need to rebuild indexes, update statistics, modify indexes etc.

the fact that it runs fine on one server and not the other is more likely indicative of missing/bad indexes than any hardware issue.

When you have a slow query, the first thing to do is always to look at the execution plan.
Go to Top of Page
   

- Advertisement -