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.

Community · Accepted Answer · 2017-05-23 11:49:28Z

2

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'

edited May 23, 2017 at 11:49

CommunityBot

11 silver badge

answered Sep 22, 2010 at 13:21

Martin Smith

457k97 gold badges777 silver badges887 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Daniel A. White Over a year ago

I saw no (noticeable) difference in the execution plan. However, now my sproc works on the ASP.NET page... Any ideas?

Martin Smith Over a year ago

@Daniel - Maybe the stored procedure got recompiled (this can happen for a variety of reasons - e.g. if you run ALTER on its definition or if any of the statistics it is dependant on get updated). If not then maybe it was not a parameter sniffing issue at all. I guess you'll not find out for sure unless it happens again.

Daniel A. White Over a year ago

Thanks Martin for letting me know about this strangeness.

Damien_The_Unbeliever · Accepted Answer · 2010-09-22 13:18:33Z

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.

answered Sep 22, 2010 at 13:18

Damien_The_Unbeliever

241k28 gold badges358 silver badges470 bronze badges

Comments

Chris Van Opstal · Accepted Answer · 2010-09-22 13:17:11Z

-2

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

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

answered Sep 22, 2010 at 13:17

Chris Van Opstal

37.7k10 gold badges77 silver badges91 bronze badges

Collectives™ on Stack Overflow

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

3 Answers 3

3 Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

3 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related