| 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 MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
 |
|
|
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. |
|
|
|
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 MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
|
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. |
|
|
|
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 MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
|
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. |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-05-25 : 09:34:40
|
| ok...will wait for your response------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
|
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. |
|
|
|
|
Report very slow or times out