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)
__________________________________________________ __________________________________________
"k_man" <k_***@discussions.microsoft.comwrote in message
news:23**********************************@microsof t.com...
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.