Stored Procedure runs in SQL Server Management Studio, but Timeout in .NET call
I ran into a really strange issue that is worth mentioning. There was an existing system that had been running fine for over two years now and suddenly began to timeout in 30 seconds from an ASP.Net web page. I was asked to look into it. I took a look and began tracing through it. It gave the following error “Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.”. The .Net page was using LINQ to SQL. So I extended the timeout on the connection and command side and still received the same error. I then ran a similation of the stored procedure call in SQL Server Management Studio, passing in the same parameters. The results were returing quickly within one second. I thought his was odd and began to wonder if there was some issue with LINQ to SQL. So I created an ADO.Net version of running the remote procedure call to the stored procedure and surprisingly received the same error. I began to think there must have been a change to permission or network configuration. The server engineers explained there were no changes. I asked to receive a copy of the database to run locally. Running againts the local database I received the same error. I lauched the SQL Server Profiler to view the actual steps taking place when running from .Net compared to running it directly in SQL Server Management Studio. The difference was the remote procedure call classification. After doing some research, I found that with complex stored procedures doing aggregations, if there isn’t an adaquate maintenance plan for index rebuilding and updating statistics then you could run into and issue with remote procedure calls. So if you don’t have access to adjust maintenance plans on production servers, you can add the following two lines of code to your stored procedure and it will fix the timeout error from when running in .Net.
Step 1
Towards the beginning of the stored procedure you should add the line, “SET ARITHABORT ON;”.
When running stored procedure call in .NET, the arithabort option disabled by default, but it’s enabled by default in when running in SQL Server Management Studio. The result is that the server actually caches 2 separate execution plans for most/all procedures. This affects how the server performs numerical calculations and as such you can get wildly different results depending on the procedure.
Step 2
At the end of your select statement you should add the line, “OPTION(RECOMPILE)”.
This statement will force a seek to obtain potentially faster execution plan for the stored procedure. This explicit call helps if you are unsure about what maintenance plan you have in place and how often it is run. I would say this statement should be used on an as needed basis, such as a scenario like the one described above.
This was a very unique scenario that came up, and there is not much out there talking about this and pulling it all together. Hope this is helpful to someone.
Regards,
Brian McCumber (Find me on Twitter: @nterdev)
NTERDEV, LLC
Popularity: 1% [?]
