Thousands and thousands of simultaneous users hitting a sproc — what do you do?

5 pts.
Tags:
SQL Server
SQL Server 2008
SQL Server stored procedures
Stored Procedures
folks, i am not a db guy altogether, but I am a developer, and someone had asked me that question, and i got interested in the resolution. Lets say you have 50K users that hit the same stored procedure at the same time. The stored procedure is computation intensive and goes out to lunch for a bunch of seconds for each caller. Am i correct in thinking that the sql server has a certain number of threads that these calls to the sproc are made on -- so if the thread pool size is 100 threads, it means the first 100 callers are entering the procedure, and the rest are blocking? for the sake of argument, lets say there are no record locks inside that sproc -- its all complicated joins and calculations -- what can be done in this case? as a developer, i started thinking that perhaps i should have multiple copies of that database so that i can partition the incoming callers in some round robin fashion? but even if i have 100 databases that are all copies of each other, the number of users/callers is still by far larger than the number of threads available to do the work? From all the reading i have done, the suggestions seem to be of "tuning" nature -- meaning i can improve this or that --- but what i need is something "architectural" that will show me how to truly scale databases. For example, calling .NET dlls from the sproc will dramatically improve the performance -- but what if the number of users goes up to 100K? 500K? Any white papers, books you can suggest? i am truly looking for the way the "big boys" do it -- i need to know that as the number of users goes up and up, i can spend the money, buy new servers, do some DB magic and have a truly scalable solution. thank you

Oh, one more thing.   As a possible solution, i was told that these guys have multiple versions of the same sproc, but under different names and somehow this makes the situation more scalable -- does this make any sense?



Software/Hardware used:
sqlserver2008

Answer Wiki

Thanks. We'll let you know when a new response is added.

OK, you are over thinking it. Having multiple copies of the same stored proc, or multiple copies of the same database on the same server won’t do anything but make the think harder to manage.

SQL Server can handle thousands of users all calling the same procedure at the same time.

Everything in SQL Server is handled via what’s called a scheduler. The scheduler is basically a thread within the database engine. Each CPU core gets its own scheduler. The scheduler can technically only run a single process at a time, however if a process can’t access something (like it is waiting for a page to be returned from disk) then the process is kicked off the scheduler and another process starts running and the process that was kicked off the scheduler has to go and wait at the back of the queue for resources again.

Even with complex joins if your tables are properly indexed and your query is written correct you should be able to get subsecond response times.

CLR typically doesn’t scale very well. I know that when I worked at MySpace we pushed out a very simple CLR stored procedure to the farm, and the farm crashed in like 45 minutes or something.

SQL Server can support a huge number of users at a time. Where you’ll run into problems is if you have so many connections to the database server that you run out of TCP ports. This takes a while as you can have about 60k TCP ports in use at a time. This translates to a lot more users than that as you’ll be using connection pooling and users aren’t constantly connected to the database.

Discuss This Question: 2  Replies

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • Hlx
    Something isn't right in your description. If it as you say "you have 50K users that hit the same stored procedure at the same time. The stored procedure is computation intensive and goes out to lunch for a bunch of seconds for each caller" and you make the assumption that a "bunch of seconds for each caller" translates to 3 seconds of CPU, you would need about 200 of the largest systems I know of to service all of these requests simultaneously. This would probably cost somewhere in the range of about $500 million to buy. I'm going to go out on a limb and say your application needs to be streamlined substantially before you have any hope of scaling to the level required.
    690 pointsBadges:
    report
  • Oscarius
    [...] 10. Mrdenny and Hlx ponder along with Oscarius: Thousands and thousands of simultaneous users hitting a sproc - what do you do? [...]
    0 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following