June 1, 2004 at 5:07 am
Hi,
I want to know what criteria i have to keep in when i am developing a web site using sql server where more than 10000 users are going to visit the site at a time.
if this situations what are the steps i have to keep in mind regarding software wise pls hardware wise.
awating for quick reply.
vijay verma
June 2, 2004 at 5:01 am
if you want to select data .. then write { with (nolock) } in table hint ... so no lock you make when dealing with the tables .. this will improve your sitr performance
Alamir Mohamed
Alamir_mohamed@yahoo.com
June 2, 2004 at 8:24 am
Even thou SQl can handle a lot of connections you have to consider if 10000 connections hit SQL at the same time it will take roughly .3 GB of Memory to handle all connections. This is because it cost about 50k of memory to support a single connection up to the max worker thread number, beyond that it is roughtly 30k (this should still be correct but I couldn't find on MS site), this does not include resources for query processing.
A few things you may read are that you should increase the maximum number of worker threads to the number of expected connections due to working to avoid thread pooling. Basically this means that by default SQL is set to 255 max worker threads, if 256 connections are made then 1 thread will be shared for the wrk of 2 of the connections that are made. You can of course adjust to 256 then ewach connection will work in it's own thread. However consider that a thread gets a division of processor time as dictated by the OS. When a thread is swapped it is called a context switch. The problem is the more threads you add the more context switches occurr and thus the more slices are made. Make too many slices of the CPU time and everthing will stall because the context switches are occurring so often and so quick nothing can get done. Unless you see and issue I suggest (as does MS) leave this alone.
Avoid SQL Server connections unless you need. If you need static data consider exports to files or XML to use with the web.
Close connection at the end of each page or as soon as you no longer need the connection object. Also open only the moment you will need.
Anything you can do to cut down on netowrk chatter between the web and SQL is impact site performance. such as using SPs with SET NOCOUNT ON, multiple recordsets if a page needs more than one piece of data, using OUTPUT parameters and using Execute with not return when doing updates, inserts or deletes and need nothing more than a success.
There are lots of other things you could do for performance, but I do think you may need to find a tool for sizing memory as Standard Edition of SQL only goes so high then you have to swap to Enterprise Edition to support greater needs.
June 3, 2004 at 1:11 am
hi Antares686, your reply was more specific and it is realy helpful for a lot of us.
so in Server properties .. you adjust maxmum concurrent user connection to 0 (unlimited) .. and did you changed the maximum worker thread to 255 or anything else ?
there is also another solution to make global connection at the start of the server and send All user queries through this connection ... is this good ?
Alamir Mohamed
Alamir_mohamed@yahoo.com
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply