刺激和阶段比较 [英] prod and stage comparison

问题描述

大家好， 

我可以看到，与prod相比，相同的请求在舞台上的执行时间要长得多。

如何检查是CPU，内存还是硬盘？

请指教。

解决方案

检查是否有大型进程在运行：

 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 
其中status<>'sleeping'
 ORDER BY des.program_name，
 dec.client_net_address 

检查缓冲：

 SELECT DB_NAME （database_id）AS [数据库名称]，
 COUNT（*）* 8 / 1024.0 AS [缓存大小（MB）] 
 FROM sys.dm_os_buffer_d escriptors 
 WHERE database_id> 4  - 排除系统数据库
 AND database_id<> 32767  - 排除ResourceDB 
 GROUP BY DB_NAME（database_id）
 ORDER BY [缓存大小（MB）] DESC; 
  - 按缓冲池中的对象（表，索引）细分缓冲区
 SELECT OBJECT_NAME（p。[object_id]）AS [ObjectName]，
 p.index_id，
 COUNT（*）/ 128 AS [缓冲区大小（MB）]，
 COUNT（*）AS [Buffer_count] 
 FROM sys.allocation_units AS 
 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  - 排除系统对象
 GROUP BY p。[object_id]，
 p.index_id 
 ORDER BY buffer_count DESC; 

谁在做什么：

 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; 

如果这篇文章帮助你，请标记为答案

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.

解决方案

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

这篇关于刺激和阶段比较的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！