SQL Server - Request Time Outs all too frequent.

Forums › IT Pro and developers › SQL Server - Request Time Outs all too frequent.

pwaddles

113 posts

Master Geek

#28249 24-Nov-2008 11:36

Running SQL Server 2005 on a webserver, doesnt get huge amounts of traffic hence we can host it ourselves, but keep getting timeout errors on SQL queries. Website is in VB.Net 2.0.

The website takes order for spectacles lenses. Theres heaps of information that needs to get loaded from the DB to my order form before an order can even be placed, then theres a big long query to write to the DB to save the new order. This goes fine and dandy until about 5 or 6 orders have been placed. I suspect its dues to me not closing connections properly, or not at all, so that will be my first thing to change, making sure I end each method that reads from the DB with sqlCon.close() or .dispose() (which is better?)

I thought there would be some sort of automatic garabe gobler built into SQL 2005 but maybe thats not the case.

So my question is, if my problem is not cause by leaving sqlconnections open, then what else could it be? Im fairly new to administering SQL 2005. Im running SQL Server Management Studio Express. I start a new job next week so would like to get this resolved ASAP.

I'll post back here this afternoon how I get on after closing all my connections etc.

Cheers

Hadley

freitasm

BDFL - Memuneh
78995 posts

Uber Geek

Administrator

ID Verified

Trusted

Geekzone

Lifetime subscriber

#179849 24-Nov-2008 11:54

Have you checked the basic stuff, such as CPU utilisation during that time, the event log, allocated memory for the SQL server process, lacking index to search the data, too many indexes that could impact when adding rowns to the database, using stored procedures to reduce the number of compilations, etc?

My technology disclosure

Tradepub

Free professional, reference and technical white papers (affiliate link).

pwaddles

113 posts

Master Geek

#179881 24-Nov-2008 13:47

Each table has a primary key etc. aspnet_wp and sqlservr never get over about 70mb of memory usage each. The machine has 3gb of ram so I dont think its memory getting out of hand.

I've not used any stored procedures, as I dont think the project is big enough to require them. But I've been wrong before!!

My standard syntax for reading something from the DB is like this.

Dim sqlCon As New SqlConnection
sqlCon.ConnectionString = ConfigurationManager.ConnectionStrings("ILSConnectionString").ToString()
Dim sqlComm As New SqlCommand("SELECT * FROM Orders", sqlCon)
sqlCon.Open()

Dim r As SqlDataReader
r = sqlComm.ExecuteReader()

While (r.Read())
do some stuff
End While

'and now just added

sqlCon.dispose()

Would it be worth my while going through all my code and doing r.close() (my SQLDataReader) as well, I've used r.close() in a few subs where I need to use my datareader more than once.

Ragnor

8192 posts

Uber Geek

Trusted

#179885 24-Nov-2008 14:04

At the very minimum you need try, catch, finally around that code with r.Close() and sqlCon.Close() in the finally block. You can just use .Close() rather than Dispose and then the automatic garbage collection do the dispose at a convient time.

Yes you should always close the reader and connection after use.

While it's good to know how the ADO.NET stuff works at a low level you would save yourself a lot time andheadaches with sql injection, exception handling, performance... if you use a simple free DataLayer tool.

I think in your situation you would gain a lot by using the SubSonic 2.1 Data Access Layer, here is a short video that gives an introduction to using Subsonic with both a Web Site Project using a build provider and a Web Application Project using generated classes.

http://www.wekeroad.com/webcasts/subsonicintro/intro.html

It's about 9min long.

RedJungle

Phil Gale
1108 posts

Uber Geek

Trusted

Red Jungle

Subscriber

#179887 24-Nov-2008 14:13

Are you able to post the exact error message?

Also if you disable custom error messages in the web.config it should give you a stack trace when it throws.

PS. Generally if an object has a .dispose() method you should call it, then set the object = nothing.

pwaddles

113 posts

Master Geek

#179889 24-Nov-2008 14:15

I like doing my SQL Conns at that level as it lets me process and format the data how I want it, but also... I dont know what those other things you mentioned are :P

I've just made some changes about an hour ago adding in .dispose and then I see you said I can use .close(). Will it be okay to leave .dispose() in place as it is? Or is this another bad idea?

I wish I could give you a good reason why Im not using exception handlers, but I'd just be lying. I'll get some of those into their this afternoon as well. At least then if there is an error it wont look so nasty to the user I guess.

Thanks for the feedback guys, it is all really well appreciatted.

pwaddles

113 posts

Master Geek

#179893 24-Nov-2008 14:21

I've been trying to get the error message to come back and bite me in the backside again but it seems to be working better since I've added those .dispose() lines of code in. If it happens again tho, I'll be sure to post it!!

<red jungle>PS. Generally if an object has a .dispose() method you should call it, then set the object = nothing.</red jungle>

Why do I need to set it to nothing? I was under the impression (that was built from assumption) that the .dispose() method would make the object = nothing. But as I said earlier, I've been wrong before, and Im not ashamed to admin it.

RedJungle

Phil Gale
1108 posts

Uber Geek

Trusted

Red Jungle

Subscriber

#179901 24-Nov-2008 14:35

Why do I need to set it to nothing? I was under the impression (that was built from assumption) that the .dispose() method would make the object = nothing. But as I said earlier, I've been wrong before, and Im not ashamed to admin it.

Your generally correct on this point. It's more from convention and habit that I add this. :)

Ragnor

8192 posts

Uber Geek

Trusted

#179905 24-Nov-2008 14:47

In c# you can just cheat and wrap the reader.read in a using statement and same for the sql connection and they will be closed/disposed at the end of the statement. Not sure if vb.net has an equivalent concept.

pwaddles

113 posts

Master Geek

#179906 24-Nov-2008 14:51

You can certainly use the Using construct in vb.net, but I couldn't get it to go in this instance, I only spent 5 minutes on it but moved away from the idea pretty quick. It wasn't opening the connection, so I had to put in the sqlCon.open() before the using structure, so that didnt give me much confidence that it would close my connection after the 'end using' line.

nate

6472 posts

Uber Geek

Retired Mod

Trusted

Lifetime subscriber

#179918 24-Nov-2008 15:41

The problems you are having could be anything but some suggestions I can add to whats already been said:

Open connections as late as possible, and close them early. I always thought you keep a connection open and do what you need to. I've read on MSDN that opening and closing connections for each operation is fine as connection pooling looks after the resources for you. Do a Google search for more info.
Is your server locked down, ie no-one else has access? Someone else may be using your resources so it pays to check
With your queries, only bring down the data you need (use WHERE criteria to filter records down). The less data you push and pull from the database, the quicker your queries will operate.

I can't understand how processing an order would be CPU intensive, in pseudo-code can you describe the steps you are going through to achieve this?

pwaddles

113 posts

Master Geek

#180215 25-Nov-2008 14:48

Hi Nate,

it wasnt so much CPU intensive, as it was the service quality of the DB connection just deteriorating to the point where it was no longer functional. I run about 4 or 5 queries to get the information I need to populate the fields on the order form for a customer to be able to proceed. This is where the problems were coming from as I was doing

Dim sqlCon As New SqlConnection
sqlCon.ConnectionString = ConfigurationManager.ConnectionStrings("ILSConnectionString").ToString()
etc... etc...

for each query in a seperate method without going sqlCon.close() or sqlCon.dispose(). I changed this so that all the queries were in the one method and used the same sqlCon, kept it open the whole time, run my first query, close my data reader, run the next query, close the datareader etc... and then do a sqlCon.dispose() at the end of the method to close the connection.

I also then went through heaps of my other pages where I use the same techniques to read/write DB info and added sqlCon.dispose() to them all and things seem to be going much better since then (touch wood).

The insertion of an order into the DB requires me to add a bunch of parameters, like 90 or so, to the SQL command, and then run the thing. If I continue to have anymore problems with users saving orders I'll look at opening the connection just before I run the query, although I think I may need to have the sqlConnection open or at least declared before I can specify which connection for the sqlCommand to use.

Oh isnt development a load of fun? So many ways to skin a cat.