Poor Performance from Web Server

Hi,

I am having a problem with one of my stored procedures in SQL Server 2005. Basically the proc brings back a data set for the ASP.NET front end, but it is running very slowly from .NET.

I have run SQL profiler on the procedure and its taking around 20 seconds to bring back the data for the .NET, where as if I copy and paste the executed SP from profiler into the management studio and run it in a query window, it runs in around 1 second, even if I run DBCC DROPCLEANBUFFERS before I run it. More worryingly, the CPU usage is 40 times higher and the number of reads is 50% higher from .NET.

We have the .NET front end spread over 3 clustered web servers with load balancers and the SQL db is on a dedicated rig. I am having the same problem on my locally published version of the site as well, so I don't think it's an issue with the web site.

If anyone has got any ideas on this then please let me know as I am completely stuck. I should mention that the issue has only recently started occuring and it used to be fine and the rest of the site is fine...

Thanks in advance

Tom

maybe put some trace statements to echo out the time it execute a line of code. This way you can maybe find the bottleneck in your DAL. Are you using the Data Access Application Blocks. I have found those to be very valuable to manage my connections. I never see issues like you are describing anymore. Back in the day, like 4 years ago maybe when I was doing things on my own. Are you trying to fill a custom list or collection with a large set of records? I found that takes way too long and just op for datasets, readers or change my procedure to return a fixed set of rows.

But try the tracing thing to see what line(s) take the longest to execute and I think you will find your issue.