Professional Web Applications Themes

Max worker threads - Microsoft SQL / MS SQL Server

Looking at the server characteristics I suppose this worker process threads is in consequential as far as your database is concerned. They seem to work for maximum throughput ... And when you say by 1700 concurrent connections are you looking at the web server or the database server. For all you know even if the web server says 1700 concurrent connections there maybe just 100~200 connections made to the database. Lets see what others have to say on this ... -- HTH, Vinod Kumar MCSE, DBA, MCAD SCT Software Solutions [url]http://www32.brinkster.com/sqlvinod[/url] "Bob Castleman" <nomailhere> wrote in message news:uLOj5MKQDHA.2432TK2MSFTNGP10.phx.gbl... > I ...

  1. #1

    Default Re: Max worker threads

    Looking at the server characteristics I suppose this worker process threads is in consequential as far as your database is concerned. They seem to work for maximum throughput ... And when you say by 1700 concurrent connections are you looking at the web server or the database server. For all you know even if the web server says 1700 concurrent connections there maybe just 100~200 connections made to the database. Lets see what others have to say on this ...

    --
    HTH,
    Vinod Kumar
    MCSE, DBA, MCAD
    SCT Software Solutions
    [url]http://www32.brinkster.com/sqlvinod[/url]

    "Bob Castleman" <nomailhere> wrote in message news:uLOj5MKQDHA.2432TK2MSFTNGP10.phx.gbl...
    > I just read an article on the setting of Max work threads to more than the
    > default of 255. I was curious what experience anyone has had with this in
    > regard to performance. Currently our server is running 99% buffer/cache hit
    > ratio and %30 CPU utilization. We average about 1700+ concurrent connections
    > at peak times. This would imply significant use of thread pooling.
    >
    > Any thoughts would be appreciated.
    >
    > Bob Castleman
    > SuccessWare Software
    >
    >
    Vinodk Guest

  2. #2

    Default Re: Max worker threads

    Database server. I am using sp_who and looking at the number of rows
    returned. I assume that each spid is a connection.

    "Vinodk" <vinodk_scthotmail.com> wrote in message
    news:ehZ26lKQDHA.1148TK2MSFTNGP11.phx.gbl...
    Looking at the server characteristics I suppose this worker process threads
    is in consequential as far as your database is concerned. They seem to work
    for maximum throughput ... And when you say by 1700 concurrent connections
    are you looking at the web server or the database server. For all you know
    even if the web server says 1700 concurrent connections there maybe just
    100~200 connections made to the database. Lets see what others have to say
    on this ...

    --
    HTH,
    Vinod Kumar
    MCSE, DBA, MCAD
    SCT Software Solutions
    [url]http://www32.brinkster.com/sqlvinod[/url]

    "Bob Castleman" <nomailhere> wrote in message
    news:uLOj5MKQDHA.2432TK2MSFTNGP10.phx.gbl...
    > I just read an article on the setting of Max work threads to more than the
    > default of 255. I was curious what experience anyone has had with this in
    > regard to performance. Currently our server is running 99% buffer/cache
    hit
    > ratio and %30 CPU utilization. We average about 1700+ concurrent
    connections
    > at peak times. This would imply significant use of thread pooling.
    >
    > Any thoughts would be appreciated.
    >
    > Bob Castleman
    > SuccessWare Software
    >
    >

    Bob Castleman Guest

  3. #3

    Default Re: Max worker threads

    select count(distinct spid) from master.dbo.sysprocesses returns about the
    same number. At our peak time we show closer to 2000 connections.

    Part of this is a design issue on the application that won't be easy to
    change. :)

    Bob


    Bob Castleman Guest

  4. #4

    Default Re: Max worker threads

    is there a specific problem you are trying to solve?

    "Bob Castleman" <nomailhere> wrote in message
    news:#eb2q$KQDHA.2036TK2MSFTNGP10.phx.gbl...
    > select count(distinct spid) from master.dbo.sysprocesses returns about the
    > same number. At our peak time we show closer to 2000 connections.
    >
    > Part of this is a design issue on the application that won't be easy to
    > change. :)
    >
    > Bob
    >
    >

    Guest

  5. #5

    Default Re: Max worker threads

    unless you have quite a few CPUs I think it's best to leave this setting
    alone. Have you drilled down at all with your performance problem? Is it a
    particular set of queries? Any indication of disk IO queing?

    "Bob Castleman" <nomailhere> wrote in message
    news:#4zOImLQDHA.3020TK2MSFTNGP10.phx.gbl...
    > Just a general sense that things are 'too slow'. So we've been examining a
    > number of issues, including the server settings. The Max Worker Threads
    > setting is the only thing that seems to apply since everything else looks
    to
    > be within expected ranges. We don't want to try changing this setting on a
    > whim as it requires stopping the service and it is also a clustered
    server.
    > We are also having deadlock problems, but I don't think it is related to
    > threading contention issues since the deadlocks always happen in the same
    > part of the software. If it was general threading, I would think that the
    > deadlocks would be throughout different areas of the applicaiton.
    >
    >
    > <nntp> wrote in message news:OGtukULQDHA.1072TK2MSFTNGP10.phx.gbl...
    > > is there a specific problem you are trying to solve?
    > >
    > > "Bob Castleman" <nomailhere> wrote in message
    > > news:#eb2q$KQDHA.2036TK2MSFTNGP10.phx.gbl...
    > > > select count(distinct spid) from master.dbo.sysprocesses returns about
    > the
    > > > same number. At our peak time we show closer to 2000 connections.
    > > >
    > > > Part of this is a design issue on the application that won't be easy
    to
    > > > change. :)
    > > >
    > > > Bob
    > > >
    > > >
    > >
    > >
    >
    >

    Guest

  6. #6

    Default Re: Max worker threads

    We are looking at client side performance issues. We already know of
    specific areas of the application that need improvement. Disk I/O doesn't
    seem to be a problem.

    The applicaton is deployed on a load balanced Citrix farm hitting a SQL
    Cluster. The cluster runs dual processors/5 gig ram on each box with a fibre
    channel to the RAID. As we have added more users and servers to the farm,
    the load on the cluster doesn't seem to have gone up dramatically (30% ave
    CPU utilization, good cache hit ratios, etc), but there seems to be a
    general slowing down of the responsiveness of the app. The network engineer
    is looking at hardware issues (the current setup requires moving traffic
    across 3 segments to get from the farm to the SQL Cluster). We want to put
    the farm servers and database servers on the same gigabit segment, but the
    bean counters need justification, so we have to make sure that we are doing
    all that we can with the current setup. The engineer is going to put
    sniffers on the network, and we are working together to see if the SQL
    Server can be tweaked.

    The performance issue hasn't become critical, but we are looking at some
    significant growth over the next year so we are trying to be pro-active.



    <nntp> wrote in message news:uNPJFrNQDHA.2316TK2MSFTNGP12.phx.gbl...
    > unless you have quite a few CPUs I think it's best to leave this setting
    > alone. Have you drilled down at all with your performance problem? Is it
    a
    > particular set of queries? Any indication of disk IO queing?
    >
    > "Bob Castleman" <nomailhere> wrote in message
    > news:#4zOImLQDHA.3020TK2MSFTNGP10.phx.gbl...
    > > Just a general sense that things are 'too slow'. So we've been examining
    a
    > > number of issues, including the server settings. The Max Worker Threads
    > > setting is the only thing that seems to apply since everything else
    looks
    > to
    > > be within expected ranges. We don't want to try changing this setting on
    a
    > > whim as it requires stopping the service and it is also a clustered
    > server.
    > > We are also having deadlock problems, but I don't think it is related to
    > > threading contention issues since the deadlocks always happen in the
    same
    > > part of the software. If it was general threading, I would think that
    the
    > > deadlocks would be throughout different areas of the applicaiton.
    > >
    > >
    > > <nntp> wrote in message news:OGtukULQDHA.1072TK2MSFTNGP10.phx.gbl...
    > > > is there a specific problem you are trying to solve?
    > > >
    > > > "Bob Castleman" <nomailhere> wrote in message
    > > > news:#eb2q$KQDHA.2036TK2MSFTNGP10.phx.gbl...
    > > > > select count(distinct spid) from master.dbo.sysprocesses returns
    about
    > > the
    > > > > same number. At our peak time we show closer to 2000 connections.
    > > > >
    > > > > Part of this is a design issue on the application that won't be easy
    > to
    > > > > change. :)
    > > > >
    > > > > Bob
    > > > >
    > > > >
    > > >
    > > >
    > >
    > >
    >
    >

    Bob Castleman Guest

  7. #7

    Default Re: Max worker threads

    Bob,

    The max worker threads setting is adequate unless your getting warnings in
    your logs that you have reached the limit of the pool. SQL Server does a
    very good job at managing the pool. Adding more threads to the pool if your
    not currently at max will not affect anything. 30% avg cpu usage on a dual
    proc with 1700 concurrent users is certainly nothing to sneeze at.
    >Just a general sense that things are 'too slow'. So we've been examining a
    >number of issues, including the server settings.
    This should be one of the last places to look. SQL Server does a really
    good job at allocating resources and the sources of slowness are usually not
    associated with things like that. It's most often things you have direct
    control over such as the disk subsystems and application code. I would
    start by using profiler to see what queries / sp's are used most often or
    are the most hogs on time and resources and see what you can do to optimize
    them. Hit the biggest offenders first and work down. Often you will find
    just one or 2 frequently used sp's can be tuned and make a dramatic
    difference in overall performance.

    >We are also having deadlock problems, but I don't think it is related to
    >threading contention issues since the deadlocks always happen in the same
    >part of the software.
    99.9% of the time deadlocks are caused by updating the tables in the
    opposite order in different sps or just poor practices in keeping the
    transactions as short and sweet as possible. Changing settings on the
    server will have no bearing on this issue. If your experiencing slowness
    overall then I would guess blocking is a major part of your problem. You
    can use sp_who2 or sp_lock to see how much blocking is occurring.
    Bottom line is you should look at the code and schema long before you look
    at changing settings.




    --

    Andrew J. Kelly
    SQL Server MVP


    "Bob Castleman" <nomailhere> wrote in message
    news:%23pVEF4WQDHA.3020TK2MSFTNGP10.phx.gbl...
    > We are looking at client side performance issues. We already know of
    > specific areas of the application that need improvement. Disk I/O doesn't
    > seem to be a problem.
    >
    > The applicaton is deployed on a load balanced Citrix farm hitting a SQL
    > Cluster. The cluster runs dual processors/5 gig ram on each box with a
    fibre
    > channel to the RAID. As we have added more users and servers to the farm,
    > the load on the cluster doesn't seem to have gone up dramatically (30% ave
    > CPU utilization, good cache hit ratios, etc), but there seems to be a
    > general slowing down of the responsiveness of the app. The network
    engineer
    > is looking at hardware issues (the current setup requires moving traffic
    > across 3 segments to get from the farm to the SQL Cluster). We want to put
    > the farm servers and database servers on the same gigabit segment, but the
    > bean counters need justification, so we have to make sure that we are
    doing
    > all that we can with the current setup. The engineer is going to put
    > sniffers on the network, and we are working together to see if the SQL
    > Server can be tweaked.
    >
    > The performance issue hasn't become critical, but we are looking at some
    > significant growth over the next year so we are trying to be pro-active.
    >
    >
    >
    > <nntp> wrote in message news:uNPJFrNQDHA.2316TK2MSFTNGP12.phx.gbl...
    > > unless you have quite a few CPUs I think it's best to leave this setting
    > > alone. Have you drilled down at all with your performance problem? Is
    it
    > a
    > > particular set of queries? Any indication of disk IO queing?
    > >
    > > "Bob Castleman" <nomailhere> wrote in message
    > > news:#4zOImLQDHA.3020TK2MSFTNGP10.phx.gbl...
    > > > Just a general sense that things are 'too slow'. So we've been
    examining
    > a
    > > > number of issues, including the server settings. The Max Worker
    Threads
    > > > setting is the only thing that seems to apply since everything else
    > looks
    > > to
    > > > be within expected ranges. We don't want to try changing this setting
    on
    > a
    > > > whim as it requires stopping the service and it is also a clustered
    > > server.
    > > > We are also having deadlock problems, but I don't think it is related
    to
    > > > threading contention issues since the deadlocks always happen in the
    > same
    > > > part of the software. If it was general threading, I would think that
    > the
    > > > deadlocks would be throughout different areas of the applicaiton.
    > > >
    > > >
    > > > <nntp> wrote in message news:OGtukULQDHA.1072TK2MSFTNGP10.phx.gbl...
    > > > > is there a specific problem you are trying to solve?
    > > > >
    > > > > "Bob Castleman" <nomailhere> wrote in message
    > > > > news:#eb2q$KQDHA.2036TK2MSFTNGP10.phx.gbl...
    > > > > > select count(distinct spid) from master.dbo.sysprocesses returns
    > about
    > > > the
    > > > > > same number. At our peak time we show closer to 2000 connections.
    > > > > >
    > > > > > Part of this is a design issue on the application that won't be
    easy
    > > to
    > > > > > change. :)
    > > > > >
    > > > > > Bob
    > > > > >
    > > > > >
    > > > >
    > > > >
    > > >
    > > >
    > >
    > >
    >
    >

    Andrew J. Kelly Guest

  8. #8

    Default Re: Max worker threads

    This is a good sneeze. That's not bad at all for those figures. I guess I
    don't understand the point about it being backward compatible to an access
    db. Do you mean that some of the users point to a local access db? If
    that is the case then yes that will always be a problem You will never be
    able to optimize the app like that. Just the fact that your using adhoc sql
    vs sp's is probably slowing things down. I bet most of your adhoc queries
    aren't even reusing a cached plan. Sounds like the code is the place to
    look if you want to improve performance.

    --

    Andrew J. Kelly
    SQL Server MVP


    "Bob Castleman" <nomailhere> wrote in message
    news:%23ZAgtYXQDHA.2460TK2MSFTNGP10.phx.gbl...
    > > 30% avg cpu usage on a dual
    > > proc with 1700 concurrent users is certainly nothing to sneeze at.
    >
    > Is this a good sneeze or a bad sneeze?
    >
    > > 99.9% of the time deadlocks are caused by updating the tables in the
    > > opposite order in different sps or just poor practices in keeping the
    > > transactions as short and sweet as possible. Changing settings on the
    > > server will have no bearing on this issue. If your experiencing
    slowness
    > > overall then I would guess blocking is a major part of your problem.
    You
    > > can use sp_who2 or sp_lock to see how much blocking is occurring.
    > > Bottom line is you should look at the code and schema long before you
    look
    > > at changing settings.
    > >
    >
    > There is something of an institutional problem here. One is that the
    > application must remain backwards compatible to an Access database, so we
    > can't use stored procedures or any other SQL Server specific techniques.
    The
    > other is the ever present mindset "throw more hardware at it". I have been
    > pushing hard to migrating all users to MSDE/SQL Server and working on
    client
    > side issues. This exercise in tweaking the hardware is really just a way
    of
    > me gathering evidence to make my case for re-engineering the client side
    as
    > solid as possible. I am actually making some headway (who says you can't
    > move a mountain?).
    >
    >

    Andrew J. Kelly Guest

  9. #9

    Default Re: Max worker threads

    > I
    > don't understand the point about it being backward compatible to an access
    > db. Do you mean that some of the users point to a local access db?
    That's pretty much the problem. The application is deployed to customer's
    LANs with either Access, MSDE, or SQL Server, and to our hosted environment
    with SQL Server. All of these use the same client executables. I've been
    slowly building the case to abandoning Access. The other developers are on
    board, I just have to make the case to management, then come up with a
    viable plan to migrate all the users.


    Bob Castleman Guest

Similar Threads

  1. Worker Process Account for ASP.NET
    By Ajay Choudhary in forum ASP.NET Security
    Replies: 1
    Last Post: April 20th, 08:19 AM
  2. worker process aspnet_wp.exe identity
    By Calvin in forum ASP.NET Security
    Replies: 1
    Last Post: August 20th, 03:27 PM
  3. Two Worker Process running at once
    By Adam in forum ASP.NET General
    Replies: 5
    Last Post: July 29th, 07:00 PM
  4. ModRuby / Apache2.0 worker mpm
    By George Moschovitis in forum Ruby
    Replies: 0
    Last Post: July 7th, 04:40 PM
  5. ASP.NET worker process size keeps growing.
    By Hoon in forum ASP.NET General
    Replies: 1
    Last Post: July 3rd, 09:46 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139