I have several stored procedures on SQL Server 2005 that, as I noticed, suddenly take a lot of time to be called from my ASP.NET MVC application running on the IIS6 web farm on four servers. Normal, expected run time less than a second; unexpected abnormal runtime is 25-45 seconds. The problem never seems to correct itself.
However, if I ALTER the stored procedure (even if I do not change anything in the procedure, except possibly adding a space to the script created by the SSMS Modify command), the completion time returns to the expected completion time.
IIS and SQL Server work in separate units running Windows Server 2003 R2 Enterprise Edition. SQL Server is the standard version. All machines have two Xeon E5450 3GHz processors and 4 GB of RAM. SQL Server is accessed using TCP / IP over gigabit Ethernet (not sure which physical media is).
The problem is present from all web servers in the web farm. When I call a procedure from the request window in SSMS on my development machine, the procedure ends at normal time. This is strange because I got the impression that SSMS uses the same SqlClient driver as in .NET. When I point my web application development instance to the production database, I again get an abnormally long completion time. If my SqlCommand Timeout is too short, I get
System.Data.SqlClient.SqlException: . .
: ALTER , , , ?
: , , SSMS . , , - ( , , SSMS ). , procs , .
: , . , . , sp_recompile sprocs. - , sproc . , - , sproc , , - SQL sproc.
SQL Server 2005, OPTIMIZE FOR SELECT. , , "--", . , , , , .