July 9, 2011 at 11:59 am
Hi everyone,
We have SQL Server 2008 RT2 in one of our machines and a stored procedure that gets data from multiple tables from multiple databases. Now, when i run the stored procedure from SQL Server Management studio it takes 00:00:01 sec. but when i use the same stored procedure from an ASP.Net website it takes forever. Before it would throw a timeout error but after i set CommandTimeout property of the data source in website it doesn't throw the error but takes very long time. This doesn't happen all the time but if it happens then it goes for some days.
As how I see it, this got to be a network issue since in SQL Server Management Studio it takes no time to execute the stored procedure.
What are your thoughts and suggestions about this? Thanks.
July 9, 2011 at 12:31 pm
Very likely the different SET options between ASP.NET and SSMS result in different execution plans, one of which is efficient and one not.
Get the actual execution plan from SSMS and use Profiler (I think it's the Statistics XML event) to catch the exection plan when run from the website and post them both here.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply