SQL Server Performance Forum – Threads Archive

Stored Procedures responding to multi user request

Hi all, I wriote an enquiry screen which selects data from 8 different tables. Basically all tables are having millions of records. The enquiry screen was responding too slow before and now after changing to stored procedures, the speed is increased considerably. But here I have a doubt. How the stored procedure responding to multi user requests ? We have around 30-40 users accessing the enquiry screen at the same time. And each and every minute atleast 5-10 users are using this enquiry screen. So how the server will consider this requests ? When one user enter the product number the enquiry screen runs 6 different stored procedures to collect all the details from different tables to populate the results and after that only it dispalys the results. So while this execution is going on if another user request comes to the server will SQL manage the request by keeping it on a queue ? or is there any way we can use the stored procedures also same as ( no lock using for tables) method ? I tested the code in the test server and it is working fine. But I wish to know the exact things happening in the server before I implement it in the live server. Hopes experts can give ur valuable reply
Thanking in advance. Raj

The exact things happening you can watch using Profiler and building up a test scenario in which you put load on the server. There are several third party tools available to can be used in such stress tests. —
Frank Kalis
Microsoft SQL Server MVP
http://www.insidesql.de
Heute schon gebloggt?http://www.insidesql.de/blogs

HI ya, SQL Server will run all the user requests on a round-robin basis, so if you have put in assumptions with in the logic of the 6 procedures which assume that a single user has to run all 6 before the next user can start to run them, then that is something you’d need to build into the procs (or better design the procs to not be affected by this, using temp tables rather than permanent tables for building results for example) Cheers
Twan

You can check the query execution process from QA by referring to Query Execution plan, with the help of compiled plan in cache and as referred above SQL caters the requests from users. Satya SKJ
Contributing Editor & Forums Moderator
http://www.SQL-Server-Performance.Com
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.

]]>