SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2012 Forums
 Analysis Server and Reporting Services (2012)
 Report very slow or times out
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

sqlbug
Posting Yak Master

Canada
197 Posts

Posted - 05/21/2013 :  16:11:30  Show Profile  Reply with Quote
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

India
52309 Posts

Posted - 05/22/2013 :  01:10:22  Show Profile  Reply with Quote
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

Canada
197 Posts

Posted - 05/22/2013 :  16:43:49  Show Profile  Reply with Quote
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

India
52309 Posts

Posted - 05/23/2013 :  00:45:49  Show Profile  Reply with Quote
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

Canada
197 Posts

Posted - 05/23/2013 :  14:27:27  Show Profile  Reply with Quote
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

India
52309 Posts

Posted - 05/24/2013 :  00:31:39  Show Profile  Reply with Quote
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

Canada
197 Posts

Posted - 05/24/2013 :  17:47:29  Show Profile  Reply with Quote
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

India
52309 Posts

Posted - 05/25/2013 :  09:34:40  Show Profile  Reply with Quote
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

USA
5072 Posts

Posted - 05/25/2013 :  15:24:14  Show Profile  Visit russell's Homepage  Reply with Quote
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
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000