SqlClient TimeOut Issues In Asp.Net Only

[k_man]

Hi all,

Have a strange problem with SqlClient timeouts.

Here is some background: About a month ago I upgrade from SQL 2000 to 2008.
Am using Stored Procedures for all the data access. Most of them work fine,
but I have some of them, about 20%, that give me time out errors.

From my dev machine if I use SQL Server Management Studio and I right click
on the SP, enter in the query parameters and execute it, the resultset comes
back in less than one second. So far so good.

No here is the really strange part. When I use Sever Explorer from Visual
Studio 2008, and I try to execute the same SP I get a timeout error ("Timeout
expired. The timeout period elapsed prior to completion of the operation or
the server is not responding.")

I also get the same timeout when trying to execute from my code (C#) in
Asp.Net.

I have been beating my head against the wall for three days trying to track
this down with now and so far not luck. Any help would be greatly appreciated.

Thanks in advance.

 

[sloan]

Google "Parameter Sniffing" "Sql Server"

You can "up" the Connection.Timeout, but do that AFTER you research
Parameter Sniffing.

 

[k_man]

Sloan,

Thank you, thank you, thank you!

I researched a little bit and added OPTION (RECOMPILE) in the SP. Worked
like a charm, queries coming back in less than a second.

I would have never gone down that path becuase the query was performing fine
in SSMS. That is really wierd that the execution plan would be sub-optimal
when using the .NET Framework Data Provider for SQL Server.

Thanks again.

 

[sloan]

OPTION RECOMPILE is one way. Make sure you understand the consequences of
putting this one.

The other workaround is a "parameter copy"

CREATE PROC dbo.uspEmployeeDoSomething ( @EmpID int )

declare @EmpIDCopy int
select @EmpIDCopy = @EmpID

--Now use @EmpIDCopy where you originally had @EmpID

I've used both work-arounds (the above "copy" and OPTION RECOMPILE.

It just depends.

 

[William \(Bill\) Vaughn]

This is 100% understandable. Consider that when you execute a SP for the
first time, the query processor constructs a query plan based on the state
of the statistics and the input parameters. It's like getting in a cab in
downtown Seattle at 4 PM and telling the driver to go to the Microsoft
campus but via a Burger King. The route the cabbie takes is a function of
his knowledge of the traffic at that time of day and the location of his
cousin's BK franchise. The SS query processor does the same thing, but it
keeps the plan in the cache in case some other invocation of the same SP
comes along before the cache is cleared. If the cabbie used this approach,
the next time someone got in and asked to go to Microsoft, he would use the
same route--with a stop at the BK even though the traffic at 3AM is far
different than at 4PM. This means that when you used WITH RECOMPILE the QP
rebuilt the plan on each invocation. It works, but there are better
approaches as you're forcing the system to recompile even though there's a
perfectly good plan in cache (most of the time). Consider that if you write
the SP so that regardless of the parameters passed it comes up with an
optimal plan you would not have this issue. Sometimes this means breaking up
a complex SP into pieces that can be invoked in a logic tree--each with its
own QP.

I'll discuss this in depth in my workshop at Developer Connections in Vegas
in November and DevTeach in Montreal in December. It's also in my book.

hth

--
__________________________________________________________________________
William R. Vaughn
President and Founder Beta V Corporation
Author, Mentor, Dad, Grandpa
Microsoft MVP
(425) 556-9205 (Pacific time)
Hitchhiker’s Guide to Visual Studio and SQL Server (7th Edition)
____________________________________________________________________________________________

 

[sloan]

ROFL
"getting in a cab"
"and the location of his cousin's BK franchise"

Great illustration for your teaching session(s).

 

[k_man]

Sloan,

I did try the local varibles first, but it did not seem to help.

For this database OPTION RECOMPILE is probably okay since each SP is only
run a few times a day. If the db was being hit hard I wouldn't have been so
quick to use that solution.

Again, thanks for you help.

 

[k_man]

Bill,

Thanks for your reply. After I did what Sloan suggest and googled "Parameter
Sniffing" I understood what was going on.

But, what I really surprises me is that SSMS did not exhibit the problem,
but ASP.Net and Server Explorer in Visual Studio both did. All apps were
running on the same mahine.

So it seems like the execution plan was being recalulted if the SP was
running from SQL Server Management Studio but not from apps that use the the
..NET Framework Data Provider for SQL Server. Wierd.

Thanks.