MS SQL Timeout on ASP.NET Page but not in SSMS

Question

When a sproc is executed on one of our ASP.NET pages, it times out on the SQL Server with the exception Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.. When I execute the same sproc in SSMS, it returns relatively quickly. The SQL Server and IIS are on the same box. I logged in as the same user in both places. Other pages are just fine.

Answer 1

1) The default timeout for an ADO.Net command to SQL Server is 30 seconds. You can change this to a higher value (or 0 to disable timeouts) on the SqlCOmmand object. SSMS doesn't have a timeout when it runs queries.

2) But it may be that the query is timing out because something else is blocking it from running. Whilst waiting for a response from the asp.net page, are you able to run sp_who2 on the SQL Server, and check the Blk column - if there's an entry there with a value different to the SPID in the same row, some blocking is occurring.

Answer 2

Try increasing the CommandTimeout property and see if that resolves the issue:

SqlCommand myCommand = new SqlCommand();
myCommand.CommandTimeout = 150;
myCommand.CommandType = CommandType.Text;

Answer 3

Probably Parameter Sniffing.

My answer here gives you some queries that you can use to retrieve both execution plans (the SSMS one and the ASP.NET one) to compare and contrast.

Edit

This might be a more useful query actually.

Use YourDatabase;

SELECT *
FROM sys.dm_exec_cached_plans 
CROSS APPLY sys.dm_exec_sql_text(plan_handle) 
CROSS APPLY sys.dm_exec_query_plan(plan_handle) 
cross APPLY sys.dm_exec_plan_attributes(plan_handle) AS epa
where sys.dm_exec_sql_text.OBJECTID=object_id('YourProcName') 
         and attribute='set_options'