Professional Web Applications Themes

Scaling problems with SQL Server - Microsoft SQL / MS SQL Server

We have a process which one run by a only a single client takes 93 seconds. I run this test from query yzer. The SQL Server is installed locally and is running on Quad processor each of 1.5ghz with hyperthreading. I next create 7 sessions in query yzer and run this test concurrently from 7 sessions to simulate 7 users. I am seeing some interesting results in this case. 5 clients finish within 180 seconds with fastest at 165 and slowest amongst them at 180. The last 2 however finish at 272 seconds. I have debugging messages put in that ...

  1. #1

    Default Scaling problems with SQL Server

    We have a process which one run by a only a single client takes 93 seconds.
    I run this test from query yzer. The SQL Server is installed locally and
    is running on Quad processor each of 1.5ghz with hyperthreading.

    I next create 7 sessions in query yzer and run this test concurrently
    from 7 sessions to simulate 7 users. I am seeing some interesting results in
    this case. 5 clients finish within 180 seconds with fastest at 165 and
    slowest amongst them at 180. The last 2 however finish at 272 seconds. I
    have debugging messages put in that tell me that all the 7 process start but
    then the last 2 processes kind of go into sleep state and start back again
    only after the first 5 are finished. There is no apparent blocking occuring,
    I check continuously thru EM.

    The process is pretty complex to put the complete DDL and code snippets
    here, but heres the skinny. The process calls two stored procedures, which
    work heavily with temporary tables. They do a lot of inserts/updates in the
    temp tables. The temporary tables are created outside the stored procedures.
    The second procedure was giving recompilation locks so we made it a temp
    stored procedure.

    I checked the thread count, and the thread count remains constant at 94. The
    server is configured to use the default 250 max worker threads.

    Any ideas or comments about this would be highly welcome.

    TIA, Amol.


    Amol Kasbekar Guest

  2. #2

    Default Re: Scaling problems with SQL Server

    We are not using any cursors but we have multiple insert/update statement
    specified individually for each column in the table. Would combining
    multiple update statements into one help ?? We are also using a lot of
    temporary table. This tables are populated with upto 20k rows and then
    modified; all this done within the stored procedure. Wrt to bottlenecks ,
    our buffer hit ratio is greater then 95% and avg disk queue length is less
    than 1.

    - Amol.


    "Andrew J. Kelly" <sqlmvpnooospamshadhawk.com> wrote in message
    news:O9dvKyjRDHA.1972TK2MSFTNGP11.phx.gbl...
    > I would work on seeing if you can't optimize the initial process first.
    93
    > seconds on a quad processor box is a lot of time. You must be using
    > cursors and a lot of individual processing. You need to narrow down to
    see
    > where the bottle necks are first. I would find it hard to believe there
    > isn't a lot of blocking when your running that with 7 users. But in any
    > case are you cpu bound, high disk queues, short of memory etc.
    >
    > --
    >
    > Andrew J. Kelly
    > SQL Server MVP
    >
    >
    > "Amol Kasbekar" <apkcbord.com> wrote in message
    > news:%23ThK0NjRDHA.1220TK2MSFTNGP12.phx.gbl...
    > > We have a process which one run by a only a single client takes 93
    > seconds.
    > > I run this test from query yzer. The SQL Server is installed locally
    > and
    > > is running on Quad processor each of 1.5ghz with hyperthreading.
    > >
    > > I next create 7 sessions in query yzer and run this test
    concurrently
    > > from 7 sessions to simulate 7 users. I am seeing some interesting
    results
    > in
    > > this case. 5 clients finish within 180 seconds with fastest at 165 and
    > > slowest amongst them at 180. The last 2 however finish at 272 seconds. I
    > > have debugging messages put in that tell me that all the 7 process start
    > but
    > > then the last 2 processes kind of go into sleep state and start back
    again
    > > only after the first 5 are finished. There is no apparent blocking
    > occuring,
    > > I check continuously thru EM.
    > >
    > > The process is pretty complex to put the complete DDL and code snippets
    > > here, but heres the skinny. The process calls two stored procedures,
    which
    > > work heavily with temporary tables. They do a lot of inserts/updates in
    > the
    > > temp tables. The temporary tables are created outside the stored
    > procedures.
    > > The second procedure was giving recompilation locks so we made it a temp
    > > stored procedure.
    > >
    > > I checked the thread count, and the thread count remains constant at 94.
    > The
    > > server is configured to use the default 250 max worker threads.
    > >
    > > Any ideas or comments about this would be highly welcome.
    > >
    > > TIA, Amol.
    > >
    > >
    >
    >

    Amol Kasbekar Guest

  3. #3

    Default Re: Scaling problems with SQL Server

    If you can update more than one column at a time with a single update
    statement it would be much faster. 20K rows is pretty small. I can't
    imagine doing anything with proper sql statements that would take 93 seconds
    on 20K rows. Maybe if you post the code we can make some suggestions.


    --

    Andrew J. Kelly
    SQL Server MVP


    "Amol Kasbekar" <apkcbord.com> wrote in message
    news:uex9VglRDHA.940TK2MSFTNGP11.phx.gbl...
    > We are not using any cursors but we have multiple insert/update statement
    > specified individually for each column in the table. Would combining
    > multiple update statements into one help ?? We are also using a lot of
    > temporary table. This tables are populated with upto 20k rows and then
    > modified; all this done within the stored procedure. Wrt to bottlenecks ,
    > our buffer hit ratio is greater then 95% and avg disk queue length is less
    > than 1.
    >
    > - Amol.
    >
    >
    > "Andrew J. Kelly" <sqlmvpnooospamshadhawk.com> wrote in message
    > news:O9dvKyjRDHA.1972TK2MSFTNGP11.phx.gbl...
    > > I would work on seeing if you can't optimize the initial process first.
    > 93
    > > seconds on a quad processor box is a lot of time. You must be using
    > > cursors and a lot of individual processing. You need to narrow down to
    > see
    > > where the bottle necks are first. I would find it hard to believe there
    > > isn't a lot of blocking when your running that with 7 users. But in any
    > > case are you cpu bound, high disk queues, short of memory etc.
    > >
    > > --
    > >
    > > Andrew J. Kelly
    > > SQL Server MVP
    > >
    > >
    > > "Amol Kasbekar" <apkcbord.com> wrote in message
    > > news:%23ThK0NjRDHA.1220TK2MSFTNGP12.phx.gbl...
    > > > We have a process which one run by a only a single client takes 93
    > > seconds.
    > > > I run this test from query yzer. The SQL Server is installed
    locally
    > > and
    > > > is running on Quad processor each of 1.5ghz with hyperthreading.
    > > >
    > > > I next create 7 sessions in query yzer and run this test
    > concurrently
    > > > from 7 sessions to simulate 7 users. I am seeing some interesting
    > results
    > > in
    > > > this case. 5 clients finish within 180 seconds with fastest at 165 and
    > > > slowest amongst them at 180. The last 2 however finish at 272 seconds.
    I
    > > > have debugging messages put in that tell me that all the 7 process
    start
    > > but
    > > > then the last 2 processes kind of go into sleep state and start back
    > again
    > > > only after the first 5 are finished. There is no apparent blocking
    > > occuring,
    > > > I check continuously thru EM.
    > > >
    > > > The process is pretty complex to put the complete DDL and code
    snippets
    > > > here, but heres the skinny. The process calls two stored procedures,
    > which
    > > > work heavily with temporary tables. They do a lot of inserts/updates
    in
    > > the
    > > > temp tables. The temporary tables are created outside the stored
    > > procedures.
    > > > The second procedure was giving recompilation locks so we made it a
    temp
    > > > stored procedure.
    > > >
    > > > I checked the thread count, and the thread count remains constant at
    94.
    > > The
    > > > server is configured to use the default 250 max worker threads.
    > > >
    > > > Any ideas or comments about this would be highly welcome.
    > > >
    > > > TIA, Amol.
    > > >
    > > >
    > >
    > >
    >
    >

    Andrew J. Kelly Guest

  4. #4

    Default Re: Scaling problems with SQL Server

    Amol,

    Are you using the MSDE?

    Peter Blackburn
    [url]www.boost.net/peter[/url]

    "Amol Kasbekar" <apkcbord.com> wrote in message
    news:%23ThK0NjRDHA.1220TK2MSFTNGP12.phx.gbl...
    > We have a process which one run by a only a single client takes 93
    seconds.
    > I run this test from query yzer. The SQL Server is installed locally
    and
    > is running on Quad processor each of 1.5ghz with hyperthreading.
    >
    > I next create 7 sessions in query yzer and run this test concurrently
    > from 7 sessions to simulate 7 users. I am seeing some interesting results
    in
    > this case. 5 clients finish within 180 seconds with fastest at 165 and
    > slowest amongst them at 180. The last 2 however finish at 272 seconds. I
    > have debugging messages put in that tell me that all the 7 process start
    but
    > then the last 2 processes kind of go into sleep state and start back again
    > only after the first 5 are finished. There is no apparent blocking
    occuring,
    > I check continuously thru EM.
    >
    > The process is pretty complex to put the complete DDL and code snippets
    > here, but heres the skinny. The process calls two stored procedures, which
    > work heavily with temporary tables. They do a lot of inserts/updates in
    the
    > temp tables. The temporary tables are created outside the stored
    procedures.
    > The second procedure was giving recompilation locks so we made it a temp
    > stored procedure.
    >
    > I checked the thread count, and the thread count remains constant at 94.
    The
    > server is configured to use the default 250 max worker threads.
    >
    > Any ideas or comments about this would be highly welcome.
    >
    > TIA, Amol.
    >
    >

    Peter Blackburn Guest

  5. #5

    Default Re: Scaling problems with SQL Server

    Yes, NOLOCK made a big difference as some of the problems we were facing
    were record locks. We were creating a data warehouse. We were not
    concerned about incomplete transactions (the system uses very few
    transactions to begin with), so the issue of NOLOCK returning incomplete
    transactions was not an issue and generally speaking the order of
    selecting the table(s) was always the same. (I've heard of this before,
    but never seen doentation that this really improves performance. If
    you know of some, I'd like to hear about it. Thanks.)

    I was the second dba to look at this, as the first dba wrote the 12 hour
    stored procedure/query and it was locking up our database too much.
    But, I primarily optimized by using 2 update statements instead of about
    6, by specifying that the tables being selected from did not need to be
    locked, and keeping as much massaging as possible to #temp tables.

    For what it's worth.

    Robert Taylor

    <<Do all your selects outside the transaction when ever possible>>

    and always
    address the tables in the same order.


    *** Sent via Developersdex http://www.developersdex.com ***
    Don't just participate in USENET...get rewarded for it!
    Robert Guest

  6. #6

    Default Re: Scaling problems with SQL Server

    On Tue, 15 Jul 2003 09:39:03 -0700, Robert Taylor
    <com> wrote: 

    Yes, NOLOCK has made a big difference in almost every place I've tried
    it. Frankly, I don't like what that says about SQLServer's innards,
    something along the lines of too many small critical sections each
    wrapped in a NOLOCK option, but there it is.

    Joshua Stern


    JXStern Guest

  7. #7

    Default Re: Scaling problems with SQL Server

    It really has nothing to do with the innards of sql server but more on how
    you code your application. Anyone can bring the biggest, baddest server to
    it's knees with poor code and poor schemas. I have a system where we
    routinely update, delete or insert over 100 million rows a day on a 4
    processor box. This is due to good use of transactions and optimization of
    the schema and code. Using NOLOCK to "fix" a problem is usually a sign of a
    bad design and should be investigated further. In the case where you are
    reading data that is static or it doesn't matter what the state is then that
    could help but it should not be used lightly.

    --

    Andrew J. Kelly
    SQL Server MVP


    "JXStern" <net> wrote in message
    news:com... 
    >
    > Yes, NOLOCK has made a big difference in almost every place I've tried
    > it. Frankly, I don't like what that says about SQLServer's innards,
    > something along the lines of too many small critical sections each
    > wrapped in a NOLOCK option, but there it is.
    >
    > Joshua Stern
    >
    >[/ref]


    Andrew Guest

  8. #8

    Default Re: Scaling problems with SQL Server

    Yes that is one of the biggest reasons people use NOLOCK that I have found.
    They are trying to duplicate the behavior of Oracle with the reader doesn't
    block writer architecture which doesn't currently exist in SQL Server. It's
    a different mind set and if the app is not written with that in mind it can
    cause issues.

    --

    Andrew J. Kelly
    SQL Server MVP


    "JXStern" <net> wrote in message
    news:com... [/ref]
    how [/ref]
    to [/ref]
    of [/ref]
    of a [/ref]
    that 
    >
    > A couple of points.
    >
    > First, I agree with you overall that NOLOCK can often be used as a
    > quick fix, when there is probably something better that could be done.
    >
    > Second, that I was talking about it in isolation, not suggesting it be
    > used as such a quick fix. There might be some legitimate uses for it,
    > depending on just what it really does do.
    >
    > Third, the problem that we use NOLOCK to fix might just be a problem
    > in SQLServer and not in my application at all. Oracle does things
    > differently with its write-aside architecture, and to duplicate on
    > SQLServer some things that Oracle does naturally, NOLOCK can be used,
    > though it seems to be generally awkward.
    >
    > Joshua Stern
    >[/ref]


    Andrew Guest

Similar Threads

  1. ftp server problems
    By Per Kårehed in forum Mac Applications & Software
    Replies: 3
    Last Post: February 23rd, 09:44 PM
  2. Replies: 7
    Last Post: June 10th, 02:11 PM
  3. Problems! WinNT Client -> WinXP Web Server -> Win2000 File Server
    By Fabricio Sperandio in forum ASP.NET Security
    Replies: 1
    Last Post: April 6th, 04:55 AM
  4. problems on server
    By muzikathzi webforumsuser@macromedia.com in forum Macromedia Director 3D
    Replies: 1
    Last Post: November 11th, 11:00 AM
  5. Replies: 0
    Last Post: June 30th, 11:21 AM

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