Tech Off Thread

4 posts

SQL Server 2000 - is there a queue?

Back to Forum: Tech Off
  • User profile image
    Red5

    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:

    1. Webserver application (ASP.NET) with about 1000 connected users.
    2. Each user using same connection string (connection pooling)
    3. SQL Server 2000, Standard edition, 6 GB Ram, but only can use 2GB??  Server 2003 as O.S.
    4. 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.
    5. 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?
    If there is some kind of queue, is there a way to set properties on it or manage it via some interface? Thanks all.

  • User profile image
    Richard.Hein

    There is no queue.  MSDE has a queue when more than 5 users are connected, it slows down on purpose.  SQL Server Express no longer has that constraint.

    2GB of RAM is the OS limitation (Server 2003 Standard??), otherwise you can configure the RAM usage in SQL Server Enterprise Manager to use more.

    1000 simultaneous connections is too much one CPU, you need to figure out a load balancing scheme or get a multi-core machine.  100 is the recommended limit on a single CPU, (1GHz). 

    Check your CPU perfomance and see if requests are being queued there.

    You should also run the sp_updatestats 'resample' stored proc., if performance has been decreasing over time, and see if that helps, because the stored procs use the statistics as part of the creation of the execution plans.  But the problem sounds like you have too many users and not enough horsepower.

  • User profile image
    Richard.Hein

    Also check to see how long it takes for users to get a connection object from the pool.  You may have to adjust the pool size and see if that helps.  You should be able to use SQL Profiler for that, and look for connection events.

  • User profile image
    Red5

    Richard.Hein wrote:
    Also check to see how long it takes for users to get a connection object from the pool.  You may have to adjust the pool size and see if that helps.  You should be able to use SQL Profiler for that, and look for connection events.


    That's one thing I haven't messed with is the pool size.  I will do some testing with that.
    Coincidently we rebuild our stats ever day during down time.

    Is that a Paul Reed Smith guitar in your avatar? Looks like a nice one.  I have a '77 Gibson LP Deluxe collecting dust at home. Rock on and thanks for the tips.

Comments closed

Comments have been closed since this content was published more than 30 days ago, but if you'd like to continue the conversation, please create a new thread in our Forums, or Contact Us and let us know.