prod and stage comparison

  • marchello

    Old Hand

    Points: 355

    Hi all,

    I can see that identical request is executing much longer on stage comparing to prod.

    How do I check if it is CPU, memory or hard disk bound?

    Please advise.

  • Grant Fritchey

    SSC Guru

    Points: 396617

    The single best method is to use Extended Events to capture the call. You can get a general set of data through rpc_completed or sql_batch_completed, or you can get individual statements with them using the appropriate events. Add in sqlos_waitinfo and capture the causality between events and you can easily identify everything going on. This will tell you precisely what's up with each.

    You can also capture a general set of behaviors using Query Store, including the wait statistics. You can also capture an actual execution plan on each. In SQL Server 2017, you get query runtime and wait stats stored in execution plans (actual ones, which are just estimated plans plus runtime metrics). If you want to go old school, capture the wait statistics from the system view before and after you run the query and compare the two. This really a stone knives & bear skins approach, but it'll work.

    The key, is simply identifying what's different. You could probably just look at the system configurations to understand that. Worth noting, if there are differences in how the systems are configured, such as cost threshold for parallelism, max degree of parallelism or even the default ANSI connections, this can lead to differences in execution plans which can lead to differences in performance. So, I'd compare the execution plans too.

    ----------------------------------------------------
    The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood...
    Theodore Roosevelt

    The Scary DBA
    Author of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd Edition
    Product Evangelist for Red Gate Software

  • marchello

    Old Hand

    Points: 355

    Hi Grant Fritchey,

    How do I start? I'm new to SQL Server. Please, be so kind, give me hints to begin, like path in SSMS menu. I'm using SQL Server 2017.

    Thanks!

  • Grant Fritchey

    SSC Guru

    Points: 396617

    Honestly, the best resource is the query performance tuning book in my signature. It's completely focused on what you need.

    However, to get started with extended events, start with Microsoft. You can go here. I've got a video on how to use the Live Data Window. I have a bunch of entries on my blog covering various aspects of Extended Events. The best one to get started with on your problem is probably this one.

    Seriously though, get a copy of the book. That has everything you can possibly need on this topic in a single resource.

    ----------------------------------------------------
    The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood...
    Theodore Roosevelt

    The Scary DBA
    Author of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd Edition
    Product Evangelist for Red Gate Software

  • Grant Fritchey

    SSC Guru

    Points: 396617

    Oh, and here's another video on waits and queries.

    ----------------------------------------------------
    The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood...
    Theodore Roosevelt

    The Scary DBA
    Author of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd Edition
    Product Evangelist for Red Gate Software

  • Chris Harshman

    SSC-Forever

    Points: 42146

    Grant's advice is great, and he really is a top expert on the subjects at hand, but for someone new to SQL Server going through all that is like drinking from a fire-hose.

    While the query is running, you can use a query against the dynamic management views to see info about it:

    /* running requests */
    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

    looking at the following columns will help determine the bottleneck:

    wait_type, wait_sec  As Grant mentioned, waits in SQL Server can show you where bottlenecks are

    cpu_sec, total_elapsed_sec  time the query has been running, note cpu_sec may be greater than total if the query is CPU intensive and running in a parallel plan

    reads, writes, logical_reads  shows you the amount of I/O this query is causing.  logical reads are against RAM, reads and writes are against disk

  • Chris Harshman

    SSC-Forever

    Points: 42146

    If you want to analyze something that has already run, the quickest way to look at it is through the internally stored query stats:

    SELECT TOP 25
    qs.total_elapsed_time / qs.execution_count / 1000000.0 AS average_seconds,
    qs.total_elapsed_time / 1000000.0 AS total_seconds,
    qs.total_worker_time / qs.execution_count / 1000000.0 AS avg_cpu_seconds,
    qs.total_worker_time / 1000000.0 AS total_cpu_seconds,
    qs.total_logical_reads, qs.total_logical_writes,
    qs.total_logical_reads / qs.execution_count AS average_logical_reads,
    qs.total_logical_writes / qs.execution_count AS average_logical_writes,
    qs.execution_count, qs.last_execution_time, qs.creation_time,
    OBJECT_SCHEMA_NAME(qt.objectid, qt.dbid) AS schema_name, OBJECT_NAME(qt.objectid, qt.dbid) AS object_name, o.modify_date,
    qp.query_plan, qs.sql_handle, qs.plan_handle,
    DB_NAME(qt.dbid) AS database_name,
    SUBSTRING(qt.text, (qs.statement_start_offset/2)+1, ((CASE qs.statement_end_offset WHEN -1 THEN DATALENGTH(qt.text) ELSE qs.statement_end_offset END - qs.statement_start_offset)/2) + 1) AS statement_text
    FROM sys.dm_exec_query_stats qs
    OUTER APPLY sys.dm_exec_sql_text(qs.sql_handle) as qt
    LEFT OUTER JOIN sys.objects o ON qt.objectid = o.object_id
    OUTER APPLY sys.dm_exec_query_plan(qs.plan_handle) as qp
    WHERE qt.dbid = DB_ID()
    --AND o.name = 'stp_insSalesProductionTransaction'
    --AND qs.last_execution_time >= '2019-03-15 09:50'
    --AND qs.execution_count >= 2
    --ORDER BY average_seconds DESC;
    ORDER BY total_seconds DESC;
  • Jeff Moden

    SSC Guru

    Points: 996841

    I could be wrong but it looks like all of that only covers SQL Server.  If there's something else on the box that's putting the hammer to CPU or file I/O, you might want to start with a PerfMon session as a simple confirmation and then actually see what else is running using Task Manager Details.

    --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".
    "If "pre-optimization" is the root of all evil, then what does the resulting no optimization lead to?"

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

  • marchello

    Old Hand

    Points: 355

    Thank you for all your replies.

    Well, stage SQL Server is the only software that works on that machine (I installed it, so I know what is going on). Also it started to work better after installing windows update. Now I even can't see any difference between prod and stage (though RAM on prod is 3.7GB and one on stage is 2.00 GB). I can't understand it deeper for now though.

  • David Burrows

    SSC Guru

    Points: 64736

    Jeff Moden wrote:

    I could be wrong but it looks like all of that only covers SQL Server.  If there's something else on the box that's putting the hammer to CPU or file I/O, you might want to start with a PerfMon session as a simple confirmation and then actually see what else is running using Task Manager Details.

    +1

    Also check the settings and size of the paging file on the server. Had experience once where this was set wrong and too small and caused IO and CPU to go through the roof.

     

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

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

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