0

Please see below the statistics of our wait events.

Here is how it looks like when the server was slow

WaitType            Wait_S      Resource_S  Signal_S    WaitCount   Percentage  AvgWait_S   AvgRes_S    AvgSig_S
SOS_SCHEDULER_YIELD         25081233.07     5479.80     25075753.27       3861884336      71.85         0.0065      0.0000          0.0065
LCK_M_U                 4266673.43      4089972.80      176700.64       181841311       12.22       0.0235      0.0225      0.0010
QDS_SHUTDOWN_QUEUE              2990777.05      2990575.12      201.93         49843        8.57            60.0040     59.9999     0.0041
WRITELOG                510297.58       361099.16       149198.42       217670297       1.46            0.0023      0.0017      0.0007
LCK_M_IX                315018.33       314945.12       73.21       40190       0.90            7.8382      7.8364      0.0018

Here is how it looked like when the server was fast

SOS_SCHEDULER_YIELD                26677340.27       5587.81 26671752.46       3946140972         72.84     0.0068   0.0000                0.0068   https://www.sqlskills.com/help/waits/SOS_SCHEDULER_YIELD
LCK_M_U            4413555.12          4227846.32          185708.80            188067634           12.05     0.0235   0.0225   0.0010                https://www.sqlskills.com/help/waits/LCK_M_U
TRACEWRITE      3052641.54          3046721.06          5920.48 2456310                8.34        1.2428   1.2404   0.0024                https://www.sqlskills.com/help/waits/TRACEWRITE
WRITELOG          520701.92            368987.94            151713.99            219597994           1.42        0.0024   0.0017   0.0007                https://www.sqlskills.com/help/waits/WRITELOG
LCK_M_IX           317274.13            317197.46            76.68     41936    0.87        7.5657   7.5638   0.0018                https://www.sqlskills.com/help/waits/LCK_M_IX

The CPU is under constant pressure due to a continuously running query.

The CPU is around 50 to 60% and doesn't go higher. The Database didn't encounter any slowdowns when it was in SQL Server 2008 but started acting up on we migrated to SQL 2014.

Can anyone help shed light on this ?

scheduler_address   parent_node_id  scheduler_id    cpu_id  status  is_online   is_idle preemptive_switches_count   context_switches_count  idle_switches_count current_tasks_count runnable_tasks_count    current_workers_count   active_workers_count    work_queue_count    pending_disk_io_count   load_factor yield_count last_timer_activity failed_to_create_worker active_worker_address   memory_object_address   task_memory_object_address  quantum_length_us
0x0000000C7F190040  0   0   0   VISIBLE ONLINE  1   1   4201433 265078042   105192749   3   0   6   3   0   0   3   523674903   3017581829  0   0x0000000C7E090160  0x0000000C7F014040  0x0000000C7F016040  4000
0x0000000C7F1A0040  0   1   1   VISIBLE ONLINE  1   0   5334160 314838759   259747024   5   1   6   4   0   0   6   951409528   3017581954  0   0x00000015AD4F0160  0x0000000C7F01E040  0x0000000C7F020040  4000
0x0000000C7F1B0040  0   2   2   VISIBLE ONLINE  1   0   4232445 264243145   106502397   4   0   6   4   0   0   5   534826882   3017581955  0   0x0000002CEFFE2160  0x0000000C7F026040  0x0000000C7F028040  4000
0x0000000C7F1C0040  0   3   3   VISIBLE ONLINE  1   0   6172543 427766854   344998697   3   0   6   3   0   0   4   1199857921  3017581953  0   0x0000001993EE4160  0x0000000C7F02E040  0x0000000C7F030040  4000
0x0000000C7F1D0040  0   4   4   VISIBLE ONLINE  1   0   5195146 318930441   266000445   4   0   7   3   0   0   4   971326640   3017581952  0   0x0000000380B0E160  0x0000000C7F036040  0x0000000C7F038040  4000
0x0000000C7F1E0040  0   5   5   VISIBLE ONLINE  1   0   5276883 319954368   261387691   2   0   5   2   0   0   4   960323343   3017581953  0   0x0000000333D96160  0x0000000C7F03E040  0x0000000C7F040040  4000
0x0000000C7F1F0040  0   6   6   VISIBLE ONLINE  1   0   9467930 336593259   288395855   2   0   5   2   0   0   4   1032632158  3017581951  0   0x00000004CE9C8160  0x0000000C7F046040  0x0000000C7F048040  4000
0x0000000C7F580040  0   7   7   VISIBLE ONLINE  1   1   5783581 331850535   279067751   2   0   5   2   0   0   2   1032376996  3017581389  0   0x0000001BD5E16160  0x0000000C7F04E040  0x0000000C7F050040  4000
0x0000000C7F590040  0   8   8   VISIBLE ONLINE  1   1   4144833 265265702   101585831   2   0   5   2   0   0   3   523265379   3017580639  0   0x0000001BD5D3C160  0x0000000C7F056040  0x0000000C7F058040  4000
0x0000000C7F5A0040  0   9   9   VISIBLE ONLINE  1   1   6418985 323036670   263756664   3   0   5   3   0   0   4   960004710   3017581712  0   0x0000001754B9A160  0x0000000C7F05E040  0x0000000C7F060040  4000
0x0000000C7F5B0040  0   10  10  VISIBLE ONLINE  1   1   4153093 265733808   92616497    3   0   5   3   0   0   3   498959733   3017581933  0   0x0000002CBF4B2160  0x0000000C7F066040  0x0000000C7F068040  4000
0x0000000C7F5C0040  0   11  11  VISIBLE ONLINE  1   1   4386529 264383817   98983641    3   0   6   3   0   0   3   510839760   3017580637  0   0x0000001974E0A160  0x0000000C7F06E040  0x0000000C7F070040  4000
0x0000000C7F5D0040  0   12  12  VISIBLE ONLINE  1   1   3991082 265081140   99554703    2   0   5   2   0   0   3   509176368   3017581771  0   0x0000000C3F316160  0x0000000C7F076040  0x0000000C7F078040  4000
0x0000000C7F5E0040  0   13  13  VISIBLE ONLINE  1   1   4116189 262203107   101069089   2   0   5   2   0   0   3   510118760   3017581915  0   0x0000000A43526160  0x0000000C7F07E040  0x0000000C7F900040  4000
0x0000000C7F5F0040  0   14  14  VISIBLE ONLINE  1   0   5797666 378781420   176936312   2   0   5   2   0   0   3   817116602   3017581953  0   0x0000001DC7DEC160  0x0000000C7F906040  0x0000000C7F908040  4000
0x0000000C7FA80040  0   15  15  VISIBLE ONLINE  1   1   3625843 260929187   96066759    3   0   6   3   0   0   3   496703022   3017581849  0   0x0000000C7FB16160  0x0000000C7F90E040  0x0000000C7F910040  4000

Thanks

  • SOS_SCHEDULER_YIELD and high CPU can be caused by queries not going parallel because of low MAXDOP and Cost Threshold for Parallelism settings. What are these two set at? – tpet Sep 1 '16 at 15:33
  • 1
    Are you using new CE or Old CE ? What is the compatiblity mode of the database ? Also, what is max dop and max memory settings ? Did you check for blocking ? – Kin Shah Sep 1 '16 at 15:33
  • Take a look at the execution plan as Kin suggests. 2014 introduced a new cardinality estimator that doesn't always play nice with existing code. You can either back the compatability mode on the database back down to 110 or use trace flag 9481 on the query/batch until you get the code refactored. – Steve Mangiameli Sep 1 '16 at 15:36
  • we set the maxdop to 1 previously in sql server 2008 due to error "Query Processor does not have necessary "resources". we got high "cxpacket" wait in 2008 – grassbl8d Sep 1 '16 at 15:38
  • 2
    You can ignore QDS_SHUTDOWN_QUEUE. Also, maxdop =1 means you have turned of parallelism. Set maxdop to a sensible value along with cost threshold of parallelism. – Kin Shah Sep 1 '16 at 15:45

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.