| Author |
Topic |
|
niemeyer
Starting Member
15 Posts |
 Posted - 2003-06-12 : 16:22:04
|
| I need some extra eyes on a subject that really puzzles me, i.e. the use of MSDE as the db engine for webapplications. I'm currently renting a full MSSQL license for my dedicated server (a couple of commercial ones but mostly sites in development) because I was led to believe that MSDE would underperform grossly when Internet users started to access the data. (I use SP's when I can and mostly run asp.net driven apps using output caching when suitable).I discovered a 4 month old discussion thread in another forum where the author claimed to being able to (using some test software) output nearly 70 datadriven pages/second from an AMD 1.7 webserver with MSDE installed (using the SA account). He then ran the same tests using the SQL 2000 server and fetched around 110.Bottomline is that I'm not near neither of these numbers (not even at peaks) for my websites. I'm quite happy with the functionality of SQL2000 and I know that MSDE has it shortcomings but would it be corrent to assume that I could serve hundreds of simultaneous users requesting different pages on websites running on my server using the same SA/asp.net account?Maybe I've missed something in the equation, but I'm anyway looking forward to having some experts opinions on the subject.Thanks,Niels (Brussels) |
|
|
nr
SQLTeam MVY
12543 Posts |
|
|
niemeyer
Starting Member
15 Posts |
Posted - 2003-06-12 : 17:17:07
|
quote:
It will work the same as sql server as long as you don't go above 5 concurrent batches.Probably means that you have to limit your connect pool to 5 connections.It has limitations with respect to memory and processors similar to personal edition.
Thanks for the quick reply! The fact of the matter is, however, that I use the same user account (the SA) to access all db requests from my webapplications.If we for one moment assumes that MSDE is in fact able to fetch around 70 data-driven webpages/second I guess its driven to max performance on that single user account, i.e. opening more connections (up till 5) would in fact not increase performance, or what?Sorry if my questions are trivial - merely trying to understand how MSDE behaves when the only task is to serve webpages.// Niels |
 |
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2003-06-12 : 17:30:31
|
| It's not have many users you have but how many connections.If you try to run more than 5 queries simultaneously (even wit the same user) then it will slow down.Below that it should perform the same as the other versions given the amount of resources it is able to address.Look at your app - if you are running a lot of queries at the same time then you wouldn't want to try msde.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
niemeyer
Starting Member
15 Posts |
Posted - 2003-06-12 : 18:22:00
|
quote:
It's not have many users you have but how many connections.If you try to run more than 5 queries simultaneously (even wit the same user) then it will slow down.Below that it should perform the same as the other versions given the amount of resources it is able to address.Look at your app - if you are running a lot of queries at the same time then you wouldn't want to try msde.
Thanks, Nr, for taking the time to explain the issue! The fact is that all my queries are stored procedures and most of are only for backend use. Most used are the select queries that are for producing the a paged datagrid with the product list and the product details (and pages are being served from the cache in most instances). I'm still insecure as to whether or not MSDE could do the job, but I guess I'll have to give it a try and then put up some kind of realtime monitoring. Btw, the original discussion thread can be found here http://www.asp.net/Forums/ShowPost.aspx?tabindex=1&PostID=148734 if anyone could have an interest.// Niels |
|
|
|
MichaelP
Jedi Yak
2489 Posts |
Posted - 2003-06-12 : 18:45:36
|
Also, do a lookup in the books online about DBCC CONCURRENCYVIOLATION From BOL quote:
Displays statistics on how many times more than five batches were executed concurrently on SQL Server 2000 Desktop Engine or SQL Server 2000 Personal Edition. Also Controls whether these statistics are also recorded in the SQL Server error log.
Michael<Yoda>Use the Search page you must. Find the answer you will.</Yoda> |
|
|
|
niemeyer
Starting Member
15 Posts |
Posted - 2003-06-12 : 19:25:10
|
quote:
Also, do a lookup in the books online about DBCC CONCURRENCYVIOLATION
< >That was just what I needed, thanx</> |
|
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2003-06-12 : 19:39:53
|
| That will only give info on msde or the personal edition - it won't give you anything on the current server.You can look at master..sysprocesses to see what is running.This will let you know how many threads are actually runnning queries.http://www.nigelrivett.net/sp_nrSpidByStatus.htmlIf you do go to msde and you do get more than 5 queries running at the same time (likely for an active web site) then all the queries will slow down - hence they will take longer and you will be more likely to have concurrent queries. The more batches you have the more the governor will slow down the system and eventually you will grind to a near halt.If you ever get more than 5 batches you are likely to have escalating problems.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
niemeyer
Starting Member
15 Posts |
Posted - 2003-06-13 : 13:19:18
|
quote:
That will only give info on msde or the personal edition - it won't give you anything on the current server.You can look at master..sysprocesses to see what is running.This will let you know how many threads are actually runnning queries.http://www.nigelrivett.net/sp_nrSpidByStatus.htmlIf you do go to msde and you do get more than 5 queries running at the same time (likely for an active web site) then all the queries will slow down - hence they will take longer and you will be more likely to have concurrent queries. The more batches you have the more the governor will slow down the system and eventually you will grind to a near halt.If you ever get more than 5 batches you are likely to have escalating problems.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy.
Thanks for that nice link - very interesting! I think I have a better understanding now and have decided to keep my rented sql license (does 240 $/month sound reasonable to you guys?)and then accelerate the launch of the planned webservices. I recently started with XML and the SQL Server seems to be very powerfull there..Thanks Nr and Michael for contributing!// Niels |
|
|
|
MichaelP
Jedi Yak
2489 Posts |
Posted - 2003-06-13 : 13:45:12
|
| What does $240/month buy you as fasr as storage, hardware specs, etc?Michael<Yoda>Use the Search page you must. Find the answer you will.</Yoda> |
|
|
|
niemeyer
Starting Member
15 Posts |
Posted - 2003-06-13 : 14:20:43
|
quote:
What does $240/month buy you as fasr as storage, hardware specs, etc?Michael<Yoda>Use the Search page you must. Find the answer you will.</Yoda>
Thats just for the SQL license(1 cpu)... I rent a dedicated windows server (1.7 Cel/512mb/40gb) for $180 as well.I'm sure that I could strike a better deal in the US, but its a local Belgian datacentre and I'm quite satisfied with the service levels...// Niels |
|
|
|
|
MSDE - Performant enough?