weird query performance issues with ASP.NET  
Author Message
jzapin





PostPosted: Wed Apr 02 02:49:46 CDT 2008 Top

SQL Server Developer >> weird query performance issues with ASP.NET

Hi.

I have an old ASP.NET/SQL Server 2005 application that runs locally
with one user (me). It uses some report queries that are badly written
and suck performance wise, but generally run in 0.1-0.3 seconds or so.
Rewriting them is a PITA and given the fact that I use it rarely I'd
rather not do that.

If I make small changes to queries, such as replacing one number with
another or replace number type (say replace SUM(blah)*100 with
SUM(blah)*100.0 in SELECT list), and rebuild the app, queries start
timing out with considerable CPU and huge HDD load on SQL Server side.
When you go to Profiler and copy paste queries as they time out, and
then run them in Management Studio, they complete in a split second.
Rerunning them from the app after that still leads to timeouts all the
time.

As soon as you restart SQL Server and connection from ASP.NET is
dropped and reinitialized, everything goes back to normal.

How do I fix this, is this query change/chaching issue or an issue
with connection pool or something else on IIS/ASP.NET side after
rebuild? What exactly is getting cached, should I just discard
execution plan saved for a particular query? If yes, how?

SQL Server211  
 
 
Uri





PostPosted: Wed Apr 02 02:49:46 CDT 2008 Top

SQL Server Developer >> weird query performance issues with ASP.NET Hi
On then client run the query with SET ARITHABORT ON. If it does not
help please search on internet for 'parameter sniffing'







> Hi.
>
> I have an old ASP.NET/SQL Server 2005 application that runs locally
> with one user (me). It uses some report queries that are badly written
> and suck performance wise, but generally run in 0.1-0.3 seconds or so.
> Rewriting them is a PITA and given the fact that I use it rarely I'd
> rather not do that.
>
> If I make small changes to queries, such as replacing one number with
> another or replace number type (say replace SUM(blah)*100 with
> SUM(blah)*100.0 in SELECT list), and rebuild the app, queries start
> timing out with considerable CPU and huge HDD load on SQL Server side.
> When you go to Profiler and copy paste queries as they time out, and
> then run them in Management Studio, they complete in a split second.
> Rerunning them from the app after that still leads to timeouts all the
> time.
>
> As soon as you restart SQL Server and connection from ASP.NET is
> dropped and reinitialized, everything goes back to normal.
>
> How do I fix this, is this query change/chaching issue or an issue
> with connection pool or something else on IIS/ASP.NET side after
> rebuild? What exactly is getting cached, should I just discard
> execution plan saved for a particular query? If yes, how?
>
>
>