SQL Server Performance

Why Sql Server does not use most of the cpu?

Discussion in 'Performance Tuning for DBAs' started by inancgumus, May 4, 2004.

  1. inancgumus New Member

    Hello,

    I am querying the sql server with a long-running query on a massive table (more than a hundred millions of rows) which in my opinion requires a high cpu power. But while the query is running I am looking onto the cpu usage of the sql server (even looking for the overal system) the cpu usage has not going further than %3-4.

    Does not sql server need to use most of the cpu power when executing such a query? What is the problem?

    Thanks
  2. Luis Martin Moderator

    May be there is no problem at all.
    If query was optimizer, then use Index to do the job, and instead a lot of CPU use more I/O.
    Check with Performance Monitor Avg. Disk Queue Length and with Profiler all values used by that query.


    Luis Martin
    Moderator
    SQL-Server-Performance.com

    The views expressed here are those of the author and no one else. There are no warranties as to the reliability or accuracy of anything presented here.
  3. inancgumus New Member

    Ok, i am gonna check it. But first let me tell you that if sql server uses more i/o instead of cpu power or little with cpu why does not it load huge block of data using i/o and then process it through more cpu power.. I think this is the efficient one, maybe there is an activitation mechanism which enables this?..

    I know query the optimizer half of the time selects the nearly fine (not the best i think) facilities to accomplish the job..
  4. Luis Martin Moderator

    No, as far I know there is no activation, except box was not SQL dedicated (Boost).
    SQL take all CPU it need.
    BTW: What OS, SQL and SP?


    Luis Martin
    Moderator
    SQL-Server-Performance.com

    The views expressed here are those of the author and no one else. There are no warranties as to the reliability or accuracy of anything presented here.
  5. gaurav_bindlish New Member

    I think you may be tunning into some blocking or bottleneck issues like disk i/o or nrtwork. Have you tried capturing performance counters during the operation?

    Gaurav
    Moderator
    Man thrives, oddly enough, only in the presence of a challenging environment- L. Ron Hubbard

    The views expressed here are those of the author and no one else. There are no warranties as to the reliability or accuracy of anything presented here.
  6. derrickleggett New Member

    Can you post the query? Also, run a trace and see how much it times to execute. Then, find out from the time execution completes how much time it takes to return recordset to client. Look at your network utilization during this time. What type of hardware/version of SQLServer are you using?

    MeanOldDBA
    derrickleggett@hotmail.com

    When life gives you a lemon, fire the DBA.
  7. inancgumus New Member

    It takes ~50secs to complete.

    The version of Sql Server I am using is 8.0 (2000). And the hardware is 2xIntel-Pentium 2.4mhz xeon-cpus with 4GB ram. The client is on the same machine with sql server cause I was used Query Analyzer then I wrote this question.

    Network utilization is not an issue in this problem.

    ----------------------------------------------------------------------
    The query is:
    ----------------------------------------------------------------------



    SELECT main.RegionId, (p.[Name] +'::'+ r.RegionName) as [Name], main.Impressions, main.Clickthroughs FROM
    (
    SELECT imps.RegionId, COUNT(imps.ImpressionLogId) as Impressions, ISNULL(o.Clickthroughs, 0) as Clickthroughs
    FROM dbo.Log_Impressions imps LEFT OUTER JOIN (
    SELECT clicks.RegionId, COUNT(clicks.ClickLogId) as Clickthroughs
    FROM dbo.Log_Clicks clicks
    WHERE (clicks.ClickDate >= @dateFrom AND clicks.ClickDate <= @dateTo)
    GROUP BY clicks.RegionId) as o
    ON imps.RegionId = o.RegionId
    LEFT OUTER JOIN MediaPlanPublisherRelations pubrel ON (imps.BannerId = pubrel.CreativeId
    AND imps.RegionId = pubrel.RegionId)
    INNER JOIN MediaPlans mp ON mp.MediaPlanId = pubrel.MediaPlanId
    WHERE (imps.ImpressionDate >= @dateFrom AND imps.ImpressionDate <= @dateTo)
    AND mp.CampaignId = @campaignId
    GROUP BY imps.RegionId, o.Clickthroughs
    ) as main
    INNER JOIN Regions r ON main.RegionId = r.RegionId
    INNER JOIN Publishers p ON r.PublisherId = p.PublisherId
    ORDER BY p.[Name], r.RegionName
    GO

    Thanks.
  8. gaurav_bindlish New Member

    Have you looked at disk counters?

    Gaurav
    Moderator
    Man thrives, oddly enough, only in the presence of a challenging environment- L. Ron Hubbard

    The views expressed here are those of the author and no one else. There are no warranties as to the reliability or accuracy of anything presented here.
  9. Luis Martin Moderator

    Did you see execution plan or ITW to get suggestions about indexs?


    Luis Martin
    Moderator
    SQL-Server-Performance.com

  10. satya Moderator

Share This Page