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. 7 1888
Google "Parameter Sniffing" "Sql Server"
You can "up" the Connection.Time out, but do that AFTER you research
Parameter Sniffing.
"k_man" <k_***@discussi ons.microsoft.c omwrote in message
news:23******** *************** ***********@mic rosoft.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.
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" wrote:
Google "Parameter Sniffing" "Sql Server"
You can "up" the Connection.Time out, but do that AFTER you research
Parameter Sniffing.
"k_man" <k_***@discussi ons.microsoft.c omwrote in message
news:23******** *************** ***********@mic rosoft.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.
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.uspEmployee DoSomething ( @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.
"k_man" <k_***@discussi ons.microsoft.c omwrote in message
news:B1******** *************** ***********@mic rosoft.com...
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" wrote:
>Google "Parameter Sniffing" "Sql Server"
You can "up" the Connection.Time out, but do that AFTER you research Parameter Sniffing. "k_man" <k_***@discussi ons.microsoft.c omwrote in message news:23******* *************** ************@mi crosoft.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.
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_***@discussi ons.microsoft.c omwrote in message
news:23******** *************** ***********@mic rosoft.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.
ROFL
"getting in a cab"
"and the location of his cousin's BK franchise"
Great illustration for your teaching session(s).
"William (Bill) Vaughn" <bi**********@b etav.comwrote in message
news:uK******** ******@TK2MSFTN GP06.phx.gbl...
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_***@discussi ons.microsoft.c omwrote in message
news:23******** *************** ***********@mic rosoft.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.
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.
"sloan" wrote:
>
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.uspEmployee DoSomething ( @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.
"k_man" <k_***@discussi ons.microsoft.c omwrote in message
news:B1******** *************** ***********@mic rosoft.com...
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" wrote:
Google "Parameter Sniffing" "Sql Server"
You can "up" the Connection.Time out, but do that AFTER you research
Parameter Sniffing.
"k_man" <k_***@discussi ons.microsoft.c omwrote in message
news:23******** *************** ***********@mic rosoft.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.
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.
"William (Bill) Vaughn" wrote:
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_***@discussi ons.microsoft.c omwrote in message
news:23******** *************** ***********@mic rosoft.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.
This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics |
by: MMSdev |
last post by:
Hi,
We recently placed a new E-Commerce site into production using ASP.NET
& ADO.NET and SQL Server 7.0. We've been having issues with database
timeouts that weren't an issue with our older, ASP site and the same
database server. We currently have the timeout set to 45 seconds in
our connection string. These procs should never run longer than 45
seconds. Most of them either pull back small amounts of customer
information or...
|
by: levtoma |
last post by:
Hello,
We have an ADO.NET application using .NET version 1.1.4322 SP1. It is
calling stored procedures in a database that it never written to. The
only thing the stored procedures do is a select statement on a few
tables that are joined. In the last few weeks we have experienced
issues where, two or three of these stored procedures consistently
timeout. When we recompile the stored procedures, the problem goes
away for a period of...
|
by: Mike |
last post by:
I recently deployed a new ASP.NET application. Since initial deployment we are consistantly having timeout issues. The browser clocks for several minutes and then this message is displayed:
"Timeout expired. The timeout period elapsed prior to obtaining a connection
from the pool. This may have occurred because all pooled connections were in
use and max pool size was reached."
I have done a fair amount of searching for a solution and...
|
by: Dee |
last post by:
Hi
I have a \secure folder in my application that has its own web.config
When i change the timeout in the application's web.config it doesnt affect
timeout of that page. And when i add timeout to the inner web.config I get
and error.
How can this be done?
Thanks
Dee
|
by: John |
last post by:
Hello,
I have a program that is syncing data from an access database to a
sql database nightly. The problem is that all of the sudden, program has
been working for months now, that we are getting a timeout error during the
syncing of information for a specific table. The table, call it table A, is
sent to a webservice via a dataset and then the webservice updates sql. How
can I increase the timeout for this specific function? The table...
| |
by: Mad Scientist Jr |
last post by:
My asp.net pages sessions are timing out after 20 minutes, and when I
asked my Web host about it they said due to the nature of a shared
hosting environment they cannot allow for sessions to keep application
pools and worker processes hosed for too long as these fearures are
consuming CPU resources while they are active. Therefore the pool
application shutdown and re-start settings are at 20 minutes. Is there
a workaround for this? Do I...
|
by: supercooper |
last post by:
I am downloading images using the script below. Sometimes it will go
for 10 mins, sometimes 2 hours before timing out with the following
error:
Traceback (most recent call last):
File "ftp_20070326_Downloads_cooperc_FetchLibreMapProjectDRGs.py",
line 108, i
n ?
urllib.urlretrieve(fullurl, localfile)
File "C:\Python24\lib\urllib.py", line 89, in urlretrieve
|
by: John Nagle |
last post by:
I thought I had all the timeout problems with urllib worked around,
but no.
socket.setdefaulttimeout is useful, but not always effective.
I'm setting that to 15 seconds.
If the host end won't open the connection within 15 seconds,
urllib times out. But if the host end opens the connection,
then never sends anything, urllib waits for many minutes before
timing out. Any idea how to deal with this? And don't just
say "use urllib2"...
|
by: ayush patel |
last post by:
Hi,
I developed a website using ASP. my client does not want the application to timeout even after 8-9 hours(if they are working). i know that connection timeout is set for timing out when the system is idle. but for the time when user is working on the website how should i set timeout values.
i changed session timeout and asp script timeout from IIS on the server but it does not seem to work. i have also changed values in web.config...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it.
First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth.
The Art of Business Website Design
Your website is...
| |
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own....
Now, this would greatly impact the work of software developers. The idea...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules.
He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms.
Adolph will...
|
by: conductexam |
last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one.
At the time of converting from word file to html my equations which are in the word document file was convert into image.
Globals.ThisAddIn.Application.ActiveDocument.Select();...
|
by: TSSRALBI |
last post by:
Hello
I'm a network technician in training and I need your help.
I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs.
The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols.
I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
|
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
| |
by: bsmnconsultancy |
last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...
| |