none
prod and stage comparison RRS feed

  • Question

  • 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.

    Monday, May 20, 2019 4:03 PM

Answers

  • To check if there is some big process running:

    SELECT dec.client_net_address , login_name, status, PROGRAM_NAME,login_time, row_count, memory_usage, net_packet_size,client_interface_name,
             des.host_name ,
             dest.text
             FROM sys.dm_exec_sessions des
             INNER JOIN sys.dm_exec_connections dec
             ON des.session_id = dec.session_id
             CROSS APPLY sys.dm_exec_sql_text(dec.most_recent_sql_handle) dest
             where status<>'sleeping'
             ORDER BY des.program_name ,
             dec.client_net_address

    to check the buffering:

    SELECT DB_NAME(database_id) AS [Database Name] ,
     COUNT(*) * 8 / 1024.0 AS [Cached Size (MB)]
    FROM sys.dm_os_buffer_descriptors
    WHERE database_id > 4 -- exclude system databases
     AND database_id <> 32767 -- exclude ResourceDB
    GROUP BY DB_NAME(database_id)
    ORDER BY [Cached Size (MB)] DESC ;
    -- Breaks down buffers by object (table, index) in the buffer pool
    SELECT OBJECT_NAME(p.[object_id]) AS [ObjectName] ,
     p.index_id ,
     COUNT(*) / 128 AS [Buffer size(MB)] ,
     COUNT(*) AS [Buffer_count]
    FROM sys.allocation_units AS a
     INNER JOIN sys.dm_os_buffer_descriptors
     AS b ON a.allocation_unit_id = b.allocation_unit_id
     INNER JOIN sys.partitions AS p ON a.container_id = p.hobt_id
    WHERE b.database_id = DB_ID()
     AND p.[object_id] > 100 -- exclude system objects
    GROUP BY p.[object_id] ,
     p.index_id
    ORDER BY buffer_count DESC ;

    Who is doing what:

    SELECT  SUBSTRING(dest.text, ( der.statement_start_offset / 2 ) + 1,
                      ( CASE der.statement_end_offset
                          WHEN -1 THEN DATALENGTH(dest.text)
                          ELSE der.statement_end_offset
                               - der.statement_start_offset
                        END ) / 2 + 1) AS querystatement ,
            deqp.query_plan ,
            der.session_id ,
            der.start_time ,
            der.status ,
            DB_NAME(der.database_id) AS DBName ,
            USER_NAME(der.user_id) AS UserName ,
            der.blocking_session_id ,
            der.wait_type ,
            der.wait_time ,
            der.wait_resource ,
            der.last_wait_type ,
            der.cpu_time ,
            der.total_elapsed_time ,
            der.reads ,
            der.writes
    FROM    sys.dm_exec_requests AS der
            CROSS APPLY sys.dm_exec_sql_text(der.sql_handle) AS dest
            CROSS APPLY sys.dm_exec_query_plan(der.plan_handle) AS deqp;

    Please mark as answer if this post helped you

    Monday, May 20, 2019 4:16 PM