| Author |
Topic |
|
DryWater
Starting Member
10 Posts |
 Posted - 2002-05-14 : 10:38:30
|
| Hello, I actually submitted this question to AskSQLTeam, but didn't realize it could take several days for an answer, so I thought I'd post here as well to see if anybody else had some feedback while I wait. I'm an asp/.net developer and am building an application on SQL2k that could concievably have 10-20k simultaneous users, meaning at the exact same moment in time 20k people could be executing the same stored procedure. Now, this sproc will be quering a table that could be ~ 100k records, but immediately closes the connection to the db after execution. On to my question. Would it make sense to have the core of my application in a single database and then break out the one 100k record table into several different datases? That way my 20k simultaneous users would be hitting say, 5 or 10 different databases and each time the individual sproc ran it would only have to query a table with 10-20k records? I would then use distributed queries to grab any other data I needed from the "master" database. My biggest worry or need is to know how many simultaneous open db connections is acceptable on a single database (if only for a millisecond) as well as any performance issues with distributed queries and sprocs on large tables. I've seen posts of people querying tables with 18 million records, so I'm not sure if my setup is something to be worried about, but before fully developing this application I have to be sure my design is scalable. Any comments on this would be greatly appreciated,Leo |
|
|
M.E.
Aged Yak Warrior
539 Posts |
Posted - 2002-05-14 : 12:02:14
|
| Well, it really depends what this stored proc is doing... is it updating / inserting several records or is it simply running selects on them?I would recommend not splitting up your data into multiple databases in any case as that quickly becomes a nightmare (10k-20k records in each, but how do you know which database to look into for that record? That and you couldn't reference these tables to other tables unless those tables also exists in all databases. Just messy) |
 |
|
|
DryWater
Starting Member
10 Posts |
Posted - 2002-05-14 : 12:17:32
|
| The sproc is doing a simple data select with a single inner join. I figured out a way to reference the correct database (if i went that route), but you are certainly correct in saying it'd be a nightmare. I'm more concerned about the performance of the app if that many simultaneous users and hitting the same db at once. Thanks for the comments,Leo |
|
|
|
M.E.
Aged Yak Warrior
539 Posts |
Posted - 2002-05-14 : 12:28:39
|
| If it's not being updated at all and the stored proc is only doing selects with an inner join, make a view and give it a clustered index (look up index and views on this site, theres a good bit on info there). Then just reference your view from the stored proc... It will make the stored proc a little more effiecient (not needing to run a join every time). As far as performance, I don't see any problems with letting that many users hit it. Just try to get the SP as effiecient as possible. |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2002-05-14 : 12:31:09
|
| The worst that might happen is lock contention, which you can avoid by using SELECT...WITH (NOLOCK). Look in Books Online for "NOLOCK" and the other locking hints. Since you are doing nothing but SELECT operations you don't have to worry about locks.You might also want to try setting the database to read-only status, that will also avoid locks and speed up the process a bit. If you have a moderate amount of INSERT activity, or data updates, you can set the database to allow write operations, do the work, and then set it back to read-only.Also, check the query execution plan (SET SHOWPLAN ON in query analyzer) and look for any bottlenecks like table scans. Read up on "hints" in BOL. You might also want to add indexes to tables depending on which columns are queried most. This is basic performance tuning, there's som more info here on SQL Team:http://www.sqlteam.com/FilterTopics.asp?TopicID=103As a last resort (and I do mean LAST RESORT, do EVERYTHING ELSE before you even THINK of trying this) there's DBCC PINTABLE. This will prevent the table data from being flushed from the data cache, so once it's in server memory, the table basically remains in RAM. This can help performance tremendously, but it can also cause other operations to slow to a crawl, so TEST IT THOROUGHLY if you do use it. |
|
|
|
Kevin Snow
Posting Yak Master
149 Posts |
Posted - 2002-05-14 : 12:32:10
|
| It appears to me you shouldn't have a problem. The execution plan will still be cached, and SQL will reuse that plan for multiple users.If you are really worried about it, you may consider using a pinned table, which means Microsoft® SQL Server™ does not flush the pages for the table from memory. Use the commandDBCC PINTABLE(@DatabaseID,@TableID)However, I would wait and see what kind of performance you get with the load. It may respond better than you think. |
|
|
|
graz
Chief SQLTeam Crack Dealer
4149 Posts |
Posted - 2002-05-14 : 13:41:05
|
| If the ASP/.NET application has 20,000 users then SQL Server will have significantly less due to connection pooling. In the SQL Server 2000 resource kit there is a tool called the Database Hammer that you can use to simulate large loads. Unfortunately you are simulating it using their transaction set :(Are you planning to run 20,000 users on one web server or on a server farm? 20,000 concurrent users is a very large number.===============================================Creating tomorrow's legacy systems today.One crisis at a time. |
|
|
|
MichaelP
Jedi Yak
2489 Posts |
|
|
|
load balance and distributed query performance