Post reply

High CPU usage by sql server

  • August 25, 2015 at 11:40 pm

    #311107

    Experts,

    One of our server is consistently showing high cpu by sql server. It is dedicated db box too.

    When check for high cpu usage queries found one of the query, which runs every sec from a service using most cpu.

    However this query doesn't return any record nor it takes any time when tried from ssms.

    Attached the actual execution plan snapshot of that query. Can someone pls guide me on how to troubleshoot this ? Thanks in advance

  • August 26, 2015 at 1:38 am

    #1822393

    You probably get a different plan in SSMS compared to the application.

    See this article for more info:

    http://www.sommarskog.se/query-plan-mysteries.html

    -- Gianluca Sartori

  • August 26, 2015 at 2:59 am

    #1822405

    Thanks.

    Basically CPU and memory usage is consistently high in sql server.

    Was trying to figure out whats wrong.

    Any scripts to check step by step for CPU/memory utilization ?

    Thanks again.

  • August 26, 2015 at 3:01 am

    #1822406

    High memory usage is by design. SQL uses memory, as much as you allow it to use, this is not a problem or something that needs to be fixed.

    For high CPU, identify the queries using lots of CPU and tune them.

    https://www.simple-talk.com/sql/performance/finding-the-causes-of-poor-performance-in-sql-server,-part-1/

    https://www.simple-talk.com/sql/performance/finding-the-causes-of-poor-performance-in-sql-server,-part-2/

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • August 26, 2015 at 4:11 am

    #1822414

    Thanks Gila.

    Had checked those links earlier, need to brush up again.

    I read, parallel query processing may badly impact cpu usage and performance of the system in OLTP system where small queries are run frequently and from multiple sessions.

    From the attached plan, do you think that particualr query going for parallel processing ?.

    That query runs every sec from a service set up by app team. If ask them to add the hint maxdop(1) with query will it improve cpu usage by any chance ?

    I understand that it cannot be concluded just like that, however any suggestions for further investigation from that point of view ?

    Thanks again.

  • August 26, 2015 at 4:17 am

    #1822415

    Joy Smith San (8/26/2015)

    however any suggestions for further investigation from that point of view ?

    Yes. Don't try things at random.

    Do your analysis. Identify the heaviest overall CPU-using queries. Tune them.

    If Cost Threshold for parallelism is still at its default, you probably want to change it (higher). Hints are last resorts and shouldn't in general be used unless nothing else works.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • August 26, 2015 at 9:16 am

    #1822524

    Can't troubleshoot that query without more details on it.

    One specific thing to check, though, is that you have limited SQL's use of RAM via the 'max memory' setting, and not left it at the default of unlimited. Performance can become horrible if SQL uses so much memory that you get into memory thrashing between SQL and Windows.

    SQL DBA,SQL Server MVP(07, 08, 09) "Money can't buy you happiness." Maybe so, but it can make your unhappiness a LOT more comfortable!

Viewing 7 posts - 1 through 6 (of 6 total)

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