Lowering priority: maintaing

Maintaing a connection across pages (Persistent)

I've only been workign with Database programming for 6 months or so, specfically SQL Server. I've setup a site through a hosting company that is pretty reputable and allows me plent of space/bandwidth etc. However, they've recently been having problems with one of their servers (the one I'm co hosted on) as an "abornamlly" large amount of connections are being opened up on their server.

I realize that SQL Server is designed to handle large amounts of traffic and my humble little site probably isn't killing it with shear traffic, but I'm wondering if my programming practicies are sub par and causing problems.

I'm coding in ASP (work hasn't given us the chance to migrate to .net). Anyway my site is getting some 30 - 40k hits a day on the actual web portion and probably another 10-20k on backed stuff - all which open up database connections to do their work.

I've had locks and blocks routinely because of this and I'm afraid I'm just going about this all wrong.

So I'm wondering if there is a way to maintain an established connection in ASP across multiple pages and/or sessions so I don't have to constantly drop and reconnect to the db?

Any help would be great!So I'm wondering if there is a way to maintain an established connection in ASP across multiple pages and/or sessions so I don't have to constantly drop and reconnect to the db?

I don't think that this is what you would want to do. Database connection pools would normally be the responsibility of an applications server (something like BEA's WebLogic). You could write your own custom (COM+) connection pool manager, but it's a fair amount of work and would only make a difference if your site were generating an order of magnitude more traffic.

Check out the following link for some "best practices" when coding in ASP.

ASP Best Practices (http://www.valtara.com/csc123/WhitePaper/ASPBestPractices.htm#15).

Probably your best starting point is to tune your T-SQL, avoid using dynamic SQL (use stored procs), ensure you have adequate indexing and avoid mistakes like pulling back an entire recordset when you only need one or two records (ie, use a WHERE clause).

Regards,

hmscott

Maintaing 10GB Database?

Hi Experts,

I'm trying to query data from a database of size 10GB. But it is taking too much time to get the data.

I'm uing SQL Server 2005, Xeon 4 processor Server.

The database sits on a single machine.

I don't have clear Idea about scaling.

Is it need to scale-out My database.>>>?

Please could you provide more information to help track down the problem? It will be difficult to track down the cause of the poor performance you are seeing without more information.

It would be useful if you could provide the definitions of your tables and indexes, examples of poor-performing queries and sample data. Also, an indication of the number of rows in your tables would be helpful.

Chris

|||

You need to give us some more details so we can give you some useful answers. More details about the hardware, about what symptoms you are seeing, what type of workload that you have, etc.

If you read my blog post (below), it shows some easy steps for gathering some basic information about your hardware and your environment, that will serve as a starting point.

http://glennberrysqlperformance.spaces.live.com/blog/cns!45041418ECCAA960!424.entry

|||

Thanks for the reply

More details means,

I need to maintain data of around 500 tables. some tables will have 500000 records,

Maintaining data of an enterprise , like Customers,orders,warehouse etc.

How to maintain such type of database? Any Idea plz?

Maintaing 10GB Database