SQL Server slow but CPU,Memory and Network fine

  • Team please looking for assistance on SQL Server 2016,CPU,Memory and Network are fine but application is timing out.Can long quereeries cause the timeouts and lead to overall poor database perfomance.Is so is there any metric that I can check?

    “When I hear somebody sigh, ‘Life is hard,’ I am always tempted to ask, ‘Compared to what?’” - Sydney Harris

  • kapfundestanley - Thursday, March 21, 2019 5:35 AM

    Team please looking for assistance on SQL Server 2016,CPU,Memory and Network are fine but application is timing out.Can long quereeries cause the timeouts and lead to overall poor database perfomance.Is so is there any metric that I can check?

    With the understanding that the reports "reset to 0" because the underlying tables reset to zero if you intentionally free procedure cache, something in the system causes procedure cache to empty, of the SQL Server Service is restarted, a super easy "quick look" for troublesome queries can be done as follows...

    1.  Open SSMS.
    2.  If the "Object Explorer" window isn't set to automatically open, press the {f8} key to open it.
    3.  Right click on the instance (the top-most item) in the "Object Explorer" and a menu will appear.
    4.  Click on {Reports} and another menu will appear.
    5.  Click on {Standard Reports} and another menu will appear.
    6.  Click on one of the following two menu items:
          {Performance - Top Queries By Total CPU Time}
          {Performance - Top Queries by Total IO}

    These two reports show the top 10 queries that are in cache that use the most CPU Time or Total IO (I refer to them as the "Top 10 WORST Queries").  Notice that I don't call them the top 10 "Longest Running" queries.  They're the queries that are in cache and the show cumulative values for the number of times they've executed.  If you explore the reports and expand the "+" signs on the reports, it'll tell you thing like when the query entered cache, how many times it has executed, etc, etc.  It'll also show you the snippet of code within a stored procedure (it'll usually show a whole view or function if that's the object) that SQL Server thinks uses the most resources (great aid in troubleshooting poorly performing code).  Remember that your worst query is most often NOT your longest running query.  A very short running query can be your worst consumer of resources because of how often it may be running.

    You should also have a gander at the following article because your worst query may have some additional overhead those reports don't tell you because the query may be recompiling all of the time.  Such was the case with several rather nasty pieces of code we had.  The code would "only" need 100ms to execute but would also require 2 to 22 seconds just to compile EVERY time it ran and it would run thousands of times per hour.  I've customized my copy of Jonathan's wonderful code to suit my particular needs but I'll leave any customizations up to you.  Here's the link:
    https://www.sqlskills.com/blogs/jonathan/identifying-high-compile-time-statements-from-the-plan-cache/

    There is code out there that you can use to do the same as what the graphical reports, that I told you about, do but they'll help you get out of the woods until you find something that you like.  I can tell you that if you improve the top 5 of each report and the top 5 worst recompiles, you're system will work a whole lot better.  And, yeah... it'll pick up on any queries that an application may be generating as well.  For us, that's where the biggest problems were.

    --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)

  • Fundamental questions:
    SQL 2016 - what edition?

    How long has the application / system been up?  Not talking about a server reboot, the system.  What's the history? Days, Weeks, Months, Years?  And stability during that time?

    How is it configured?  Database and application on same box or separate?

    Last question - when was the last time you made a change?  How recent?  And what was the change?

  • kapfundestanley - Thursday, March 21, 2019 5:35 AM

    Team please looking for assistance on SQL Server 2016,CPU,Memory and Network are fine but application is timing out.Can long quereeries cause the timeouts and lead to overall poor database perfomance.Is so is there any metric that I can check?

    How much memory is on this server and how is max memory configured?
    SELECT sm.total_physical_memory_kb / 1024 AS physical_mem,
      pm.physical_memory_in_use_kb / 1024 AS sql_physical_used,
      pm.locked_page_allocations_kb / 1024 as locked_in_mem,
      c.value_in_use AS config_max,
      sm.available_physical_memory_kb / 1024 AS avail_physical,
      sm.system_memory_state_desc
    FROM sys.dm_os_sys_memory sm
      CROSS JOIN sys.dm_os_process_memory pm
      CROSS JOIN sys.configurations c
    WHERE c.name = 'max server memory (MB)'

    Long queries can cause blocking of other sessions running, is it the long running query that is timing out or blocked sessions?
    SELECT s.session_id, s.host_name, s.program_name, s.client_interface_name, s.login_name, s.status AS session_status,
      db_name(er.database_id) AS database_name, er.status AS request_status, er.command, er.percent_complete,
      er.wait_type, er.wait_time / 1000.0 AS wait_sec, er.last_wait_type, er.wait_resource,
      s.memory_usage / 128.0 AS memory_meg, mg.used_memory_kb, mg.granted_memory_kb, mg.requested_memory_kb, mg.ideal_memory_kb,
      er.blocking_session_id, bs.host_name AS blocking_host, bs.program_name AS blocking_program, bs.login_name AS blocking_login,
      er.transaction_id, er.open_transaction_count,
      er.cpu_time / 1000.0 AS cpu_sec, er.total_elapsed_time / 1000.0 AS total_elapsed_sec, er.start_time, er.reads, er.writes, er.logical_reads,
      CASE er.transaction_isolation_level WHEN 1 THEN 'ReadUncomitted' WHEN 2 THEN 'ReadCommitted' WHEN 3 THEN 'Repeatable' WHEN 4 THEN 'Serializable' WHEN 5 THEN 'Snapshot' ELSE 'Unspecified' END AS isol_level,
      OBJECT_NAME(st.objectid, st.dbid) AS object_name, qp.query_plan, er.sql_handle, er.plan_handle, st.[text]
    FROM sys.dm_exec_requests er
      INNER JOIN sys.dm_exec_sessions s ON er.session_id = s.session_id
      LEFT OUTER JOIN sys.dm_exec_query_memory_grants mg ON er.session_id = mg.session_id AND er.request_id = mg.request_id
      LEFT OUTER JOIN sys.dm_exec_sessions bs ON er.blocking_session_id = bs.session_id
      OUTER APPLY sys.dm_exec_sql_text(er.sql_handle) st
      OUTER APPLY sys.dm_exec_query_plan(er.plan_handle) qp
    WHERE s.is_user_process = 1
      AND s.session_id <> @@SPID

    If the query the application is running is long, and the application is a .Net program, you may want to check what the command timeout is in the application.  A timeout is requested by the application, not from the SQL Server side.

    https://docs.microsoft.com/en-us/dotnet/api/system.data.sqlclient.sqlcommand.commandtimeout?view=netframework-4.7.2

  • I'd also like to know what methods were used to be able to make the claim that "CPU,Memory and Network are fine" and why no mention of I/O was included.

    As a bit of a side bar, I have seen queries in the past time out long before the timeout period has expired.  It's usually because of some bad (but not broken) code that needs to be fixed.  Imagine you're on the coast of California and someone tells you your job is to swim to Hawaii in the next hour.  It's kind of like that.

    --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)

Viewing 5 posts - 1 through 4 (of 4 total)

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