March 8, 2006 at 9:38 am
Hi,
I am getting this error. "Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding." It is happening at random times and from random webpages/sprocs (but quite a few times from the login routine alone).
I send an email to myself whenever an error occurs in the application. It started with couple of error emails a week (since a month or so) and now I am getting 5-10 of these error emails a day.
I checked the application event viewer for MSSQLSERVER, there are no warnings or error messages posted on there. Can any please tell me what could be wrong. And how to monitor whats going on when the error occurs and what causes it.
Thanks.
March 8, 2006 at 12:42 pm
Please post the whole error message here including who was posting the error: SQL Server, ODBC, your application etc.
It is not possible to find out why the timeout expired without knowing at what step it happened: in the web application itself trying to get to the window or when application is trying to connect to the SQL Server or the query timeout expired.
You may need to enable both application and SQL Server tracing to pinpoint the source of the timeout.
Regards,Yelena Varsha
March 8, 2006 at 1:28 pm
This is the error detail I have.
--------------------
Error Message:
Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.
Stack Trace:
at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream)
at System.Data.SqlClient.SqlCommand.ExecuteReader()
at MemberLogin.LoginButton_Click(Object sender, EventArgs e)
Error Source:
.Net SqlClient Data Provider
--------------------
Its not always at MemberLogin.LoginButton_Click.
Thanks.
March 8, 2006 at 2:54 pm
Now we can see that the error was posted by SQLClient Data Provider when trying to return results by DataReader which is basically read-only forward-only recordset as talking about it in the familiar terms.
See
SqlCommand.CommandTimeout Property
to see how you can increase your command timeout. We don't know why did the timeout happened. You can check in the application what login is application using to connect to SQL Server,set up SQL Server Profiler trace for the the Audit Login Event class and see if at the timeof the error any login at all was able to connect, assuming that only one login at a time is connecting, not multiple uses of the same login from different users (generic login). After you make sure that the login event takes place add SQL-BatchCompleted event and see how much time does it take to perform your SQL batch containing your submitted statement. You may inlude aslo SQL-BatchStarted to see if the batch was started but not completed if there is no corresponding BatchCompleted event for this batch.
Regards,Yelena Varsha
April 12, 2006 at 2:35 pm
Hi,
I posted the original message a month back. I ignored the error as it went away after some time. Now I am getting this same timeout error again.
And 90% of the time it is happening from the login routine. I don't think anything is wrong with the sproc, its only a few lines. And there is only about 50000 rows in the users table. This is what I have.
------------------------------
CREATE PROCEDURE [dbo].[proc_userverifylogin]
@email varchar(100), @pass varchar(50)
AS
IF EXISTS (SELECT * FROM users WHERE email = @email AND pass = @pass)
BEGIN
UPDATE users SET lastlogin = GETDATE() WHERE email = @email
SELECT userid, fname, state FROM users WHERE email = @email
END
------------------------------
I did not change the command timeout. Can anyone tell me whats a good command timeout value? Is 30 seconds Ok?
And I don't understand much of what the above post says. Can any one explain
1. How to "set up SQL Server Profiler trace for the the Audit Login Event class and see if at the timeof the error any login at all was able to connect"
2. How to "add SQL-BatchCompleted event and see how much time does it take to perform your SQL batch containing your submitted statement"
Thanks.
April 12, 2006 at 3:35 pm
It sounds like you are not very familiar with SQL Profiler, so if you are, please do not take offense as I am writing this from the standpoint that you do not know how to use Profiler based on your question.
SQL Profiler is a tool that part of the default SQL Server installation. You should be able to get to it from the start menu, or from the Enterprise Manager>>Tools menu. Open Profiler, go to File..New..NewTrace and connect to the SQL box you want to monitor. Keep in mind that running Profiler does add some overhead so use your discretion when running it against a production environment. You should now see the Trace Properties window, go the the Events tab. The defualt trace already contains the Audit Login Event and SQL-BatchCompleted classes. Run the profiler and attempt to duplicate your problem. Yelena is attempting to show you how to monitor SQL Server for any new logins. Is SQL Server accepting any new logins while you are getting the timeout expired message?
Anytime I have seen the timeout expired message, it has been due to the SQL box being overloaded for one reason or another. You may consider running Windows Performance Monitor on your SQL box to monitor CPU, Memory, Disk I/O, and some generic SQL Server counters.
April 12, 2006 at 4:23 pm
Hi,
I know how to use a profiler. The error is not occurring everytime. I get 3-5 timeout errors in a span of 2 mins. Then I don't see any error for hours. Last time I started the profiler and allow it to ran for an hour, but nothing happened in that time frame.
I see lot of performancde counters. Can you tell what counters to add?
Thanks.
April 13, 2006 at 1:21 pm
I usually start out at a high level with perfmon. Monitoring disk, memory, and cpu at a high level should tell you if you need to drill down more into any one of them. I usually start out with Processor>% Processor Time, Physical Disk>Avg. Disk Queue Length, and Memory>% Committed Bytes in Use. You could also add in the buffer cache hit rate and total/target memory from the SQL Server counters. I would expect that you will see high activity in either disk, memory, or cpu at the time of your timeouts. You should then be able to focus more on that area.
April 17, 2006 at 2:51 pm
Hi,
I created a counter log and allowed it to run over the weekend. But no error occured in that time period.
May be its due to some memory intensive operation that runs once or twice a week that is causing the timeout errors. I can think of a couple, the database backups that runs every tuesday and the email newsletters that we send out once or twice a week. Could that be the cause of the problem? Any suggestions.
Thanks.
April 18, 2006 at 10:14 am
Any process that creates a resource bottleneck could be the problem. You should be running your backups either during a maintenance period, or in non-peak hours to minimize the performance impact of the backup. I would suggest that you just keep a close eye on the timing of these errors so that you can determine if there truely is a pattern to when they are happening. Either way, it would be beneficial for you to get some metrics gathered at the time of the error.
April 19, 2006 at 10:56 am
Luckily I got the timeout error when I tried to login to our wbesite yesterday. This is what happened. I entered the email and password and tried to login, it took forever and then redirected me to the error page saying timeout error has occured. I tried it again, I got the same error. But when I tried to login with an incorrect password, it showed the error message saying the email or password was wrong.
I went into our server, started the sql profiler and tried to run the login sproc manually from the query analyzer. It took 46 secs (my timeout was 30 secs) to finish running the query. I checked the profiler, there was nothing much going on, I don't think there is any resource bottleneck.
I went back to the website and I tried to login, it worked fine. I checked the query analyzer again, this time it took 0 secs (or fraction of a sec?) to execute the sproc.
It looks like it takes a long time to run the sproc the "first" time, from then on it only takes less than a sec to execute the sproc. Like I mentioned before, I get timeout error from other pages, but 90% of the time its from the login routine? The same thing happens from the shopping cart page. I don't get any timeout error, but whenever I add something to the cart the first time, it takes a long time to add the product to the cart and display the shopping cart. Next time onwards the response is really quick. Any help?
Thanks.
April 19, 2006 at 10:59 am
Login sproc
------------------------------
CREATE PROCEDURE [dbo].[proc_userverifylogin]
@email varchar(100), @pass varchar(50)
AS
IF EXISTS (SELECT * FROM users WHERE email = @email AND pass = @pass)
BEGIN
UPDATE users SET lastlogin = GETDATE() WHERE email = @email
SELECT userid, fname, state FROM users WHERE email = @email
END
------------------------------
Shoppingcart sproc
------------------------------
CREATE PROCEDURE [dbo].[proc_cartaddproduct]
@cartid VARCHAR(10),
@productid INT,
@quantity INT
AS
IF NOT EXISTS (SELECT * FROM shoppingcart WHERE cartid = @cartid AND productid = @productid)
INSERT INTO shoppingcart (cartid, productid, quantity) VALUES (@cartid, @productid, @quantity)
ELSE
UPDATE shoppingcart SET quantity = quantity + @quantity WHERE cartid = @cartid AND productid = @productid
GO
------------------------------
April 19, 2006 at 11:17 am
The first time a stored procedure runs, it is compiled and the execution plan is stored in cache. Execution plans are aged out of cache based on the frequency of their usage and the cost of the plan. With that said, it still should not take 45 seconds to compile a stored procedure as small as the examples you've given. Have you looked at the execution plans of the SQL statements in your stored procedures? How large are your Users and ShoppingCart tables? Are the SQL statements in your stored procedures searching these tables using indexed columns?
April 19, 2006 at 11:28 am
With as simple as your stored procedures are, your problem may be that the data pages are dropping out of cache. You may also look at pinned tables in BOL. Keeping your user and shopping cart tables in memory could aleviate your timeouts.
April 19, 2006 at 11:35 am
I looked at the execution plan. For login sproc, the query cost for IF statement is 24.41%, for UPDATE it is 51.21%, for SELECT it is 24.38%. But all this is when the proc is taking less than a sec to run. I dont know what takes up lot of time when the proc runs for 46 secs.
The tables are not large, the users table has 50,000 users, the shopping cart table will have less than 50 any given time as abandoned carts are cleaned up daily. The user table is indexed on userid and email. Shopping cart table is indexed on cartid.
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply