Post reply

CPU "sleeping" when executing queries

  • October 27, 2005 at 5:25 am

    #110295

    We encounter a performance problem under SQL 2000 (SP3a)/ W03K. In spite of the complexity of the queries, the CPU (Xeon Bi-processor/4GB RAM) never exceeds 1% of occupation (using query analyzer or any other tool). The execution time of the queries is thus very long. Any idea if it is about a configuration problem of SQL Server ?

  • October 27, 2005 at 6:32 am

    #600539

    Can we see what the query is doing and how much data is being processsed?

  • October 27, 2005 at 9:38 am

    #600621

    In fact, all queries we use becomes very slow to execute on that particular server (because the CPU just stays almostly innactive). The same queries works fine on another machine, and we can't find out any difference between the parameters of both SQL Server installations (the only notable thing is that the one server where it works well is a mono-processor and the CPU works at 100% there...).

    here an example of a suite of queries which are executed on the server. The amount of datas included in the tables never exceed about 1000 rows

    SELECT cursusId FROM cursus with (nolock) where sommet = 1 OR structure = 1

    go

    exec sp_reset_connection

    go

    SELECT titre, sommet, structure, DtIns, contenuVisible FROM cursus with (nolock) where cursusId=406

    go

    SELECT moduleId FROM compositionCursus with (nolock) where cursusId=406 AND sousCursusId=0 AND pos=0

    go

    exec sp_reset_connection

    go

    SELECT titre, sommet, structure, DtIns, contenuVisible FROM cursus with (nolock) where cursusId=420

    go

    SELECT moduleId FROM compositionCursus with (nolock) where cursusId=420 AND sousCursusId=0 AND pos=0

    go

    exec sp_reset_connection

    go

    SELECT titre, label, duree FROM module with (nolock) where moduleId=124

    go

    SELECT COUNT(*) FROM compositionCursus with (nolock) where cursusId=420 AND sousCursusId>0

    go

    SELECT langueId FROM visibiliteCursus with (nolock) where cursusId=420

    go

    exec sp_reset_connection

    go

    .......

  • October 27, 2005 at 9:43 am

    #600626

    Have any dead lock on tempdb or maybe a filegrowth of tempdb?

    Do you have autoshrink or auto-close set on any of the dbs on that server?

  • October 27, 2005 at 12:21 pm

    #600709

    Is there any parallellism in the query plans?

    If the cpu is sleeping it could be an I/O issue.

    just a sidequestion: why don't you name the object owner?

  • October 28, 2005 at 7:20 am

    #600896

    First of all, reindex the tables and update statistics. If the problem is still there,

    Set the MAXDOP to 1 for the query or go to the server property to change the parallelism to 1. If the problem is still there,

    As Jo said, it could be I/O issue. Since you are not quering large amount of data, the disk driver maybe have some problem. Check any fixes or downloads.

     

  • October 28, 2005 at 8:17 am

    #600923

    Hold on.

    These tables have 1000 rows? Really? How large is the database (run sp_spaceused when in the database and send the output.

    I very much doubt that sql will be generating a parallel plan for a 1000 row table. Looking at these queries, I very much doubt that tempdb will be used much, and lets face it, you hardly need an index on a 1000 row table. 4Gb Ram? Probably not disk then.

    Something very odd going on by the sound of it.

    Can you run sp_configure and send the output?

    Does anything run well on this server? Do you see anything strange in the errorlog?

     

     

  • October 28, 2005 at 10:34 am

    #600996

    Hi,

    we've had similar problems on a 4 cpu machine, where cpu load never got over 35 percent, even if the server responded very slowly. we were told that hyperthreading might be the problem here, and we configured the server with no hyperthreading. the problem got better (cpu load seems to be more accurate, but no 100 percent yet), but i'm not convinced that it is really solved...

    You might have a look at your server, if HT is on.

    regards

    karl

    Best regards
    karl

  • October 28, 2005 at 3:21 pm

    #601127

    Hi all and thanx for all your interesting inputs !

    We checked all the staff, no problems in the errorlogs, the disk drivers seems to be updated and there are no poblems with the tempdb locks... 🙁

    HT should not be a problem on this server.. it just looks like our server's CPU got hollidays !

    here some more informations as asked by mark:

    sp_spaceused:

    database 734.75 MB-11585.20 MB

    sp_configure:

    affinity mask-2147483648214748364700

    allow updates0100

    awe enabled0100

    c2 audit mode0100

    cost threshold for parallelism03276711

    Cross DB Ownership Chaining0100

    cursor threshold-12147483647-1-1

    default full-text language0214748364710331033

    default language0999900

    fill factor (%)010000

    index create memory (KB)704214748364700

    lightweight pooling0100

    locks5000214748364700

    max degree of parallelism03200

    max server memory (MB)4214748364711961196

    max text repl size (B)021474836476553665536

    max worker threads3232767255255

    media retention036500

    min memory per query (KB)512214748364710241024

    min server memory (MB)0214748364700

    nested triggers0111

    network packet size (B)5126553640964096

    open objects0214748364700

    priority boost0111

    query governor cost limit0214748364700

    query wait (s)-12147483647-1-1

    recovery interval (min)03276700

    remote access0111

    remote login timeout (s)021474836472020

    remote proc trans0100

    remote query timeout (s)0214748364700

    scan for startup procs0100

    set working set size0100

    show advanced options0111

    two digit year cutoff1753999920492049

    user connections03276700

    user options03276700

  • October 28, 2005 at 4:47 pm

    #601140

    I would start turning off priority boost (a strange thing to turn on), and then set max and min server memory back to the defaults (just to get things on a level paying field).

  • October 30, 2005 at 10:54 pm

    #601214

    Hi everyone, I am also encountering the same problem with Steve here.

    It took 2 mins+ to execute the query on the prod. server while in dev. (a notebook) and other PCs, it only took a few seconds. *I did a back up and restored it to the dev. db*

    When the system originally went live, I heard it took only a few seconds to execute, too. However, as time goes by, I notice that somehow, for some reason, the perfomance is getting worse and worse until its current state right now.

    The DBA in-charge mentioned that the perf. drop could be caused by the query, itself? I do know that the query is not optimized but I just have not yet understood how could it affect the perf. so much.. from few seconds to 2 mins+ and still getting slower..  When I have optimized it, it runs much much faster.

    Below is unoptimized the query:

    SELECT     SUM(H.C_Female) AS c_female, SUM(H.C_Male) AS c_male, SUM(H.C_Sub) AS c_sub, SUM(H.I_Female) AS i_female, SUM(H.I_Male) AS i_male, SUM(H.I_Sub) AS i_sub, SUM(H.M_Female) AS m_female, SUM(H.M_Male) AS m_male, SUM(H.M_Sub) AS m_sub, SUM(H.O_Female) AS o_female, SUM(H.O_Male) AS o_male, SUM(H.O_Sub) AS o_sub, SUM(H.T_Female) AS t_female, SUM(H.T_Male) AS t_male, SUM(H.T_Sub) AS t_sub

    FROM Population H INNER JOIN Postcode_boundary B ON H.Postcode = B.POSTCODE

    WHERE     (B.CD_CODE = 'TH')

    Table:

    Population : appr. 1280580

    Postcode_Boundary : appr. 108850

    Could anyone shed light to me? Thanks..

    Btw, the system is updated quarterly and yearly (different type of data) hence data/records should not change much.

    Jhon

  • October 31, 2005 at 2:16 am

    #601233

    A lot of sums

    Are there indexes on Population.Postcode, Postcode_boundary.Postcode?

    Depending on the uniqueness of B.CD_CODE , is there an index on Postcode_boundary.CD_CODE?

    Are the statistics updated?

     

  • October 31, 2005 at 2:42 am

    #601234

    Hold on a second.

    You have configured cost threshold for parallelism to be 1. You also have prioirty boost tuned on

    This means that any query that will take more than a second will be parralellised and boosted.

    I really have no idea what happens when you force the majority of queries to have parallel plans, whilst also allowing them to run at a boosted NT Thread priority, but I can't imagine it's likely to have a particulalry positive impact.

    I'd seriously suggest you change the threshold back to 5, drop priority boost back to 0, and then see if you have a problem.

    It's always best to tune your database via the code, indexes and schema (and hardware if needed), rather than fiddling with sp_configure. When you change from defaults, you put yourself on an alternative SQL code path, and chances are that code path will not be as widely understood as the default. These options sometimes pay off, but for the most part, create strange and wonderful problems, testing is always key.

    Get rid of those options for now and lets see if you still have a problem.

    Mark

     

     

  • November 2, 2005 at 2:31 am

    #601767

    All settings (boost, memory, parralesim) are default now, but we still have the same problem (also after a full reboot). Thanks all for your help, but it seems it will remain a mistery why this CPU doesn't want to work more...

  • November 2, 2005 at 5:42 am

    #601798

    Another reason maybe that you have a bottleneck elsewhere that is slowing things down.

    Have had a look at the following counters:

    Logical Disk: Avg. Disk sec/Transfer -

    Logical Disk: Disk Bytes/sec

    Logical Disk: Avg. Disk Queue Length

    If Logical Disk: Avg. Disk sec/Transfer is consistently greater than 0.015 on your data, log or tempdb drive, then you might have a disk issue.

     

     

Viewing 15 posts - 1 through 15 (of 20 total)

You must be logged in to reply to this topic. Login to reply