High level question:
Is there some kind of queue in SQL Server where requests sit and wait for resources before they are executed?
Real life example:
- Webserver application (ASP.NET) with about 1000 connected users.
- Each user using same connection string (connection pooling)
- SQL Server 2000, Standard edition, 6 GB Ram, but only can use 2GB?? Server 2003 as O.S.
- User requests some data through the web interface via stored procedure at the DB. The amount of time to get this data is around 45 seconds.
- We can identify the user's request with SQL Tracing and see that it takes about 2 seconds to get the data, BUT this two seconds of execution time is happening at the end of the users 45 second wait. Somewhere in the first 43 seconds this request is where???? Some kind of queue?