High cpu of 90-95% on SQL Box

  • Hi All,

    Now a days we are receiving high CPU alerts from our monitoring tool. In general does seeing High CPU on SQL Server box , call for any action from SQL Server side?

    So far, no user complained about slowness of anything of such sort. Any action needs to be taken when we are seeing such alerts?

    Thanks,

    Bob

  • A single CPU going to 90-95% is expected.  Are you saying that the total of ALL CPU's is what's going to 90-95% or just one now and then?

    If so, check the connection for the software you've built or bought... we had a similar issue and it turned out that the connections that were supposed to default to having M.A.R.S. (Multiple Active Result Sets) in the .net connects were all defaulting to ON for M.A.R.S.  Explicitly stating the M.A.R.S. is disabled fixed our problem.  Disclaimer.  That was 5 or 6 years ago... I don't know if they made any changes to .net to correctly disable M.A.R.S. as a default since then.

    And, yeah... it could also be a shedload of performance challenged code and, yeah, I'd be concerned there.  Of course, if you have something like 100 connections and your box only has 4 core, well... you know the real problem then. 😀

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)
    Intro to Tally Tables and Functions

  • I would check for any correlation between excessive IO and high cpu. Insufficient paging file space will cause sql server excessive page swapping and therefore high IO. Second is there enough memory, memory pressure will force sql server to discard and/or swap pages causing high IO.

    Far away is close at hand in the images of elsewhere.
    Anon.

  • How to identify if it is M.A.R.S and disable it?

  • Hi David,

    You mean to say, I/O and CPU goes hand in hand?

  • As a hard and fast rule no. But high cpu can sometimes be caused by high io and that can definitely be a symptom of poor queries as Jeff indicated. I have known Jeff for many years and he is an expert on the effects of poor performing queries. I only posted something that sometimes can be overlooked. End user performance can be a very subjective term, how would they know without some benchmark. Your issues could be that increases in transactions, data or databases means you are reaching the point of insufficient resources for your needs. If cpu is spiking then it could be what is running at that time. However if cpu is high constantly then it could be an increase in workload or one or more frequent queries now performing badly. As Jeff mentioned fix any poorly performing queries. If cpu is still constantly high then you might need more cores.

    Far away is close at hand in the images of elsewhere.
    Anon.

  • bobrooney.81 wrote:

    How to identify if it is M.A.R.S and disable it?

    There may be another way but we had our developers check the actual connection strings they were using.  It was plain as day there and specifically said to enable "Multiple Active Result Sets".  I'm not the one that did the checking and, after more than 2 decades of not being a front-end developer, couldn't even tell you where to look specifically for connection strings but our Developers had them in files (IIRC) on the Web Servers.

    I also asked some folks in our old PASS chapter to check their stuff.  They confirmed that they had the same issue (it defaulted to ON rather than OFF) and were experiencing similar issues that went away when they explicitly disabled M.A.R.S. in their connection strings.

    Like I said before, it can also be caused by performance challenged code and simply having too few CPUs for the number of connections being serviced.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)
    Intro to Tally Tables and Functions

  • 1 way of fetching high cpu queries

    -- top 10 queries by worker time

    SELECT TOP 10

    [qs].[last_worker_time],

    [qs].[max_worker_time],

    [qs].[total_worker_time],

    [qs].[execution_count],

    stmt_start = [qs].[statement_start_offset],

    stmt_end = [qs].[statement_end_offset],

    [qt].[dbid],

    [qt].[objectid],

    SUBSTRING([qt].[text], [qs].[statement_start_offset] / 2,

    (CASE WHEN [qs].[statement_end_offset] = -1

    THEN LEN(CONVERT(NVARCHAR(MAX), [qt].[text])) * 2

    ELSE [qs].[statement_end_offset]

    END - [qs].[statement_start_offset]) / 2) AS statement

    FROM [sys].[dm_exec_query_stats] qs

    CROSS APPLY [sys].[dm_exec_sql_text]([qs].[sql_handle]) AS qt

    ORDER BY [qs].[total_worker_time] DESC; --- i.e. cpu time in microsecs

     

    Alternate way,

    --- I am grouping the queries by query hash and based on that i am getting total worker time

    SELECT [qs].[last_worker_time],

    [qs].[max_worker_time],

    [qs].[total_worker_time],

    [qs].[execution_count],

    stmt_start = [qs].[statement_start_offset],

    stmt_end = [qs].[statement_end_offset],

    [qt].[dbid],

    [qt].[objectid],

    SUBSTRING([qt].[text], [qs].[statement_start_offset] / 2,

    (CASE WHEN [qs].[statement_end_offset] = -1

    THEN LEN(CONVERT(NVARCHAR(MAX), [qt].[text])) * 2

    ELSE [qs].[statement_end_offset]

    END - [qs].[statement_start_offset]) / 2) AS statement

    FROM [sys].[dm_exec_query_stats] qs

    CROSS APPLY [sys].[dm_exec_sql_text]([qs].[sql_handle]) AS qt

    ORDER BY [qs].[total_worker_time] DESC;

    -- Plug in query hash

    SELECT SUBSTRING([qt].[text], [qs].[statement_start_offset] / 2,

    (CASE WHEN [qs].[statement_end_offset] = -1

    THEN LEN(CONVERT(NVARCHAR(MAX), [qt].[text])) * 2

    ELSE [qs].[statement_end_offset]

    END - [qs].[statement_start_offset]) / 2) AS statement,

    [qs].[total_worker_time],

    [qs].[execution_count],

    [qs].[query_hash],

    [qs].[query_plan_hash]

    FROM [sys].[dm_exec_query_stats] qs

    CROSS APPLY [sys].[dm_exec_sql_text]([qs].[sql_handle]) AS qt

    WHERE [qs].[query_hash] = 0x88B8B513764CB9F4C;

    Question here, how can I know in which stored procedure these sql stmts are being used and what parameter values being used at runtime ?

  • You can try to check the Query Store for "regressed queries". These are ones that have chosen less optimal plans. There could be a few procedures this is afflicting but that account for most of your calls to the database.

    ----------------------------------------------------

Viewing 9 posts - 1 through 9 (of 9 total)

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