Professional Web Applications Themes

No blocking, but simple queries run slow - Microsoft SQL / MS SQL Server

I'd also check the avg disk queue length. Perhaps you are disk bound. -- Tom --------------------------------------------------------------- Thomas A. Moreau, BSc, PhD, MCSE, MCDBA SQL Server MVP Columnist, SQL Server Professional Toronto, ON Canada www.pinnaclepublishing.com/sql "Andrew J. Kelly" <com> wrote in message news:phx.gbl... Are you saying that there are no high disk or cpu queues when this is occurring? Is this a multi-processor system? If so check to see if you have excessive cxpacket waits occuring on the larger queries. -- Andrew J. Kelly SQL Server MVP "Chris Birk" <com> wrote in message news:3f33dd09$vic.com...  transactional  is ...

Sponsored Links
  1. #1

    Default Re: No blocking, but simple queries run slow

    I'd also check the avg disk queue length. Perhaps you are disk bound.

    --
    Tom

    ---------------------------------------------------------------
    Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
    SQL Server MVP
    Columnist, SQL Server Professional
    Toronto, ON Canada
    www.pinnaclepublishing.com/sql


    "Andrew J. Kelly" <com> wrote in message news:phx.gbl...
    Are you saying that there are no high disk or cpu queues when this is
    occurring? Is this a multi-processor system? If so check to see if you
    have excessive cxpacket waits occuring on the larger queries.

    --

    Andrew J. Kelly
    SQL Server MVP


    "Chris Birk" <com> wrote in message
    news:3f33dd09$vic.com... 
    transactional 
    is 



    Sponsored Links
    Tom Guest

  2. #2

    Default Re: No blocking, but simple queries run slow

    Gee Tom, that's kinda what I meant by "no high disk or cpu queues"<g>

    --

    Andrew J. Kelly
    SQL Server MVP


    "Tom Moreau" <spam.me.cips.ca> wrote in message news:phx.gbl...
    I'd also check the avg disk queue length. Perhaps you are disk bound.

    --
    Tom

    ---------------------------------------------------------------
    Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
    SQL Server MVP
    Columnist, SQL Server Professional
    Toronto, ON Canada
    www.pinnaclepublishing.com/sql


    "Andrew J. Kelly" <com> wrote in message news:phx.gbl...
    Are you saying that there are no high disk or cpu queues when this is
    occurring? Is this a multi-processor system? If so check to see if you
    have excessive cxpacket waits occuring on the larger queries.

    --

    Andrew J. Kelly
    SQL Server MVP


    "Chris Birk" <com> wrote in message
    news:3f33dd09$vic.com... 
    transactional 
    is 


    Andrew Guest

  3. #3

    Default Re: No blocking, but simple queries run slow

    I just wanted to make sure he went for the right PerfMon counter. The avg disk queue length on my production system is off the dial. Maybe when the drives start whining louder than I am, they'll replace them. ;-)

    --
    Tom

    ---------------------------------------------------------------
    Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
    SQL Server MVP
    Columnist, SQL Server Professional
    Toronto, ON Canada
    www.pinnaclepublishing.com/sql


    "Andrew J. Kelly" <com> wrote in message news:phx.gbl...
    Gee Tom, that's kinda what I meant by "no high disk or cpu queues"<g>

    --

    Andrew J. Kelly
    SQL Server MVP


    "Tom Moreau" <spam.me.cips.ca> wrote in message news:phx.gbl...
    I'd also check the avg disk queue length. Perhaps you are disk bound.

    --
    Tom

    ---------------------------------------------------------------
    Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
    SQL Server MVP
    Columnist, SQL Server Professional
    Toronto, ON Canada
    www.pinnaclepublishing.com/sql


    "Andrew J. Kelly" <com> wrote in message news:phx.gbl...
    Are you saying that there are no high disk or cpu queues when this is
    occurring? Is this a multi-processor system? If so check to see if you
    have excessive cxpacket waits occuring on the larger queries.

    --

    Andrew J. Kelly
    SQL Server MVP


    "Chris Birk" <com> wrote in message
    news:3f33dd09$vic.com... 
    transactional 
    is 



    Tom Guest

  4. #4

    Default Re: No blocking, but simple queries run slow

    As it turns out this is a multiprocessor system, and in reviewing our log data there do appear to be a significant number of CXPacket waits. Based on some of the reading I've done, I changed the Parallelism setting from "Use all available processors" to "Use 1 processor". Preliminary testing with this change seems to indicate an improvement, but I won't be sure until our server is fully loaded again on Monday. I've also set up monitoring on the CPU and disk queues. Are there any other settings/query adjustments I should be looking at? We'll see what Monday brings; hopefully all will be well.

    Thanks to both Tom and Andrew for their assistance.

    Chris
    "Tom Moreau" <spam.me.cips.ca> wrote in message news:phx.gbl...
    I just wanted to make sure he went for the right PerfMon counter. The avg disk queue length on my production system is off the dial. Maybe when the drives start whining louder than I am, they'll replace them. ;-)

    --
    Tom

    ---------------------------------------------------------------
    Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
    SQL Server MVP
    Columnist, SQL Server Professional
    Toronto, ON Canada
    www.pinnaclepublishing.com/sql


    "Andrew J. Kelly" <com> wrote in message news:phx.gbl...
    Gee Tom, that's kinda what I meant by "no high disk or cpu queues"<g>

    --

    Andrew J. Kelly
    SQL Server MVP


    "Tom Moreau" <spam.me.cips.ca> wrote in message news:phx.gbl...
    I'd also check the avg disk queue length. Perhaps you are disk bound.

    --
    Tom

    ---------------------------------------------------------------
    Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
    SQL Server MVP
    Columnist, SQL Server Professional
    Toronto, ON Canada
    www.pinnaclepublishing.com/sql


    "Andrew J. Kelly" <com> wrote in message news:phx.gbl...
    Are you saying that there are no high disk or cpu queues when this is
    occurring? Is this a multi-processor system? If so check to see if you
    have excessive cxpacket waits occuring on the larger queries.

    --

    Andrew J. Kelly
    SQL Server MVP


    "Chris Birk" <com> wrote in message
    news:3f33dd09$vic.com... 
    transactional 
    is 


    Chris Guest

  5. #5

    Default Re: No blocking, but simple queries run slow

    One other thing to note when changing the MAXDOP. You may want to try dropping the number of procs by just one at a time instead of going all the way to 1. If you have a 4 processor system then try setting it to 3 and see how that goes. You can set it to 1 just to prove that is an issue but in my experience it doesn't actually have to be at 1 to fix the problem and you can still get some parallelism that way.

    --

    Andrew J. Kelly
    SQL Server MVP


    "Chris Birk" <com> wrote in message news:3f345762$vic.com...
    As it turns out this is a multiprocessor system, and in reviewing our log data there do appear to be a significant number of CXPacket waits. Based on some of the reading I've done, I changed the Parallelism setting from "Use all available processors" to "Use 1 processor". Preliminary testing with this change seems to indicate an improvement, but I won't be sure until our server is fully loaded again on Monday. I've also set up monitoring on the CPU and disk queues. Are there any other settings/query adjustments I should be looking at? We'll see what Monday brings; hopefully all will be well.

    Thanks to both Tom and Andrew for their assistance.

    Chris
    "Tom Moreau" <spam.me.cips.ca> wrote in message news:phx.gbl...
    I just wanted to make sure he went for the right PerfMon counter. The avg disk queue length on my production system is off the dial. Maybe when the drives start whining louder than I am, they'll replace them. ;-)

    --
    Tom

    ---------------------------------------------------------------
    Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
    SQL Server MVP
    Columnist, SQL Server Professional
    Toronto, ON Canada
    www.pinnaclepublishing.com/sql


    "Andrew J. Kelly" <com> wrote in message news:phx.gbl...
    Gee Tom, that's kinda what I meant by "no high disk or cpu queues"<g>

    --

    Andrew J. Kelly
    SQL Server MVP


    "Tom Moreau" <spam.me.cips.ca> wrote in message news:phx.gbl...
    I'd also check the avg disk queue length. Perhaps you are disk bound.

    --
    Tom

    ---------------------------------------------------------------
    Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
    SQL Server MVP
    Columnist, SQL Server Professional
    Toronto, ON Canada
    www.pinnaclepublishing.com/sql


    "Andrew J. Kelly" <com> wrote in message news:phx.gbl...
    Are you saying that there are no high disk or cpu queues when this is
    occurring? Is this a multi-processor system? If so check to see if you
    have excessive cxpacket waits occuring on the larger queries.

    --

    Andrew J. Kelly
    SQL Server MVP


    "Chris Birk" <com> wrote in message
    news:3f33dd09$vic.com... 
    transactional 
    is 


    Andrew Guest

  6. #6

    Default Re: No blocking, but simple queries run slow

    I would also look at the exact queries that improved by turning off parallelism. You can use OPTION (MAXDOP 1) for just those queries and take advantage of parallelism for the remainder.

    --
    Tom

    ----------------------------------------------------
    Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
    SQL Server MVP
    Columnist, SQL Server Professional
    Toronto, ON Canada
    www.pinnaclepublishing.com/sql
    ..
    "Andrew J. Kelly" <com> wrote in message news:%23fsk%phx.gbl...
    One other thing to note when changing the MAXDOP. You may want to try dropping the number of procs by just one at a time instead of going all the way to 1. If you have a 4 processor system then try setting it to 3 and see how that goes. You can set it to 1 just to prove that is an issue but in my experience it doesn't actually have to be at 1 to fix the problem and you can still get some parallelism that way.

    --

    Andrew J. Kelly
    SQL Server MVP


    "Chris Birk" <com> wrote in message news:3f345762$vic.com...
    As it turns out this is a multiprocessor system, and in reviewing our log data there do appear to be a significant number of CXPacket waits. Based on some of the reading I've done, I changed the Parallelism setting from "Use all available processors" to "Use 1 processor". Preliminary testing with this change seems to indicate an improvement, but I won't be sure until our server is fully loaded again on Monday. I've also set up monitoring on the CPU and disk queues. Are there any other settings/query adjustments I should be looking at? We'll see what Monday brings; hopefully all will be well.

    Thanks to both Tom and Andrew for their assistance.

    Chris
    "Tom Moreau" <spam.me.cips.ca> wrote in message news:phx.gbl...
    I just wanted to make sure he went for the right PerfMon counter. The avg disk queue length on my production system is off the dial. Maybe when the drives start whining louder than I am, they'll replace them. ;-)

    --
    Tom

    ---------------------------------------------------------------
    Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
    SQL Server MVP
    Columnist, SQL Server Professional
    Toronto, ON Canada
    www.pinnaclepublishing.com/sql


    "Andrew J. Kelly" <com> wrote in message news:phx.gbl...
    Gee Tom, that's kinda what I meant by "no high disk or cpu queues"<g>

    --

    Andrew J. Kelly
    SQL Server MVP


    "Tom Moreau" <spam.me.cips.ca> wrote in message news:phx.gbl...
    I'd also check the avg disk queue length. Perhaps you are disk bound.

    --
    Tom

    ---------------------------------------------------------------
    Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
    SQL Server MVP
    Columnist, SQL Server Professional
    Toronto, ON Canada
    www.pinnaclepublishing.com/sql


    "Andrew J. Kelly" <com> wrote in message news:phx.gbl...
    Are you saying that there are no high disk or cpu queues when this is
    occurring? Is this a multi-processor system? If so check to see if you
    have excessive cxpacket waits occuring on the larger queries.

    --

    Andrew J. Kelly
    SQL Server MVP


    "Chris Birk" <com> wrote in message
    news:3f33dd09$vic.com... 
    transactional 
    is 


    Tom Guest

  7. #7

    Default Re: No blocking, but simple queries run slow

    Andrew Guest

Similar Threads

  1. Replies: 11
    Last Post: December 5th, 12:02 PM
  2. slow queries from CF 5 to CF 7
    By dr_bozak in forum Coldfusion Database Access
    Replies: 19
    Last Post: November 23rd, 05:02 AM
  3. Slow Queries with Linux/Oracle
    By cetacea in forum Coldfusion Database Access
    Replies: 0
    Last Post: March 31st, 02:29 PM
  4. CF 5 generating slow DB queries
    By AdamK in forum Coldfusion Database Access
    Replies: 0
    Last Post: March 15th, 04:48 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