Users getting timeouts
-
Hi, we have two separate database servers and a web server. Users or processes are receiving timeout messages. The network guys are looking at their end to see if it's a network issue. What can I look for from the database side to see if it's the database? I do weekly maintenance ingegrity checks and update statistics weekly. Update statistics is taking over an hour to run with a 30% sampling.
I don't know what to look for!
-
A great place to start would be to have someone notify you as soon as the users start to get the timeout messages and then for you to run SQLDiag.exe. This will give you all of the locking/blocking and process info. Odds are, you've got some blocking going on that is leading to your timeouts. This could be the result of anything from poorly written queries to network or hardware bottlenecks. The blocking and wait type data in the SQLDiag output file will tell you alot.
-
Are the users machines & db servers set up to talk via the same network protocols? For example, are the user machines running "named pipes" while the db server is configured to talk via "TCP/IP"?
Have a good day,Norene Malaney
-
Sorry, to clarify the issue the users are starting to get timeout messages. The application was running fine up to about two weeks ago. How do I determine if SQL listens to TCP/IP or amed pipes?
thanks,
-
In order to determine whether SQL is listening on the correct ports using the TCP protocol, you can simply run the statement below from the command line.
telnet
For example:
telnet MySQLServer 1041
(remember that the default port is 1433). For named pipes you can use the name provided in the named pipe config.
What version of SQL Server are you using? Is it possible that the configuration of the server changed recently? Timeouts can be controlled as part of the server configuration (among other places). Please provide more info.
-
We are using SQL2000 SP4.
I have not changed anything on that server. I did notice that the update usage job that runs weekly is taking over an hour to run. I use general maintenance plans to run them with a 30% sampling. The database is about 20GB of data not including the log file.
The database has increased in size considerably since our last software upgrade in October 06. It October it was about 8GB today it's 20GB. They are loading and storing more data.
-
I don't think this is a protocol issue. Especially if you've had a significant increase in your storage requirements. Timeouts occur because a process is taking too long (big duh, right). What we need to do is figure out which processes are timing out and why. It could be a resource/hardware issue or a query/index issue. Just because you are maintaining your indexes does not mean that you have the correct indexes on your tables for the queries that are running against those tables. Again, especially if you've had a large increase in your table sizes.
How's that SQLDiag comming?
-
1. Are web users getting Connection Timeouts or Execution Timeouts? The two are different.
2. In the meantime- can you just update your web.config to allow greater timeouts?
3. What control do you have over the servers and what type of a solution are you looking for? Can you change the code that gets executed from the web server on the DB servers? Timeouts (assuming execution timeouts) can easily be the result of a bad DML or query. If the issue is with execution timeouts, can you post the DML and DDL?
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply