1、数据库性能监控与分析,微博:陈能技 广州亿能测试技术服务有限公司 ,作为测试人员,需要关注数据库的哪些方面的知识?,数据库整体架构、工作原理 数据库性能监控和分析方法 数据库性能优化常用方法,SQL Server数据库性能监控方法,LoadRunner+计数器 PerfMon SQL Server自带监控方法 第三方监控工具,SQL Server自带监控方法,动态管理视图DMV、系统表 SQL Server Profiler,基于等待时间的性能分析Performance troubleshooting based on wait timesSELECT wait_type ,SUM(wait_
2、time_ms / 1000) AS wait_time_s FROM sys.dm_os_wait_stats DOWS WHERE wait_type NOT IN ( SLEEP_TASK, BROKER_TASK_STOP,SQLTRACE_BUFFER_FLUSH, CLR_AUTO_EVENT,CLR_MANUAL_EVENT, LAZYWRITER_SLEEP ) GROUP BY wait_type ORDER BY SUM(wait_time_ms) DESC,有利于诊断方向的选取,CPU问题的症兆,CXPACKET SOS_SCHEDULER_YIELD,CXPACKET,
3、当为SQL查询创建一个并行操作时，会有多个线程去执行这个查询。每个查询处理不同的数据集或行集。 因为某些原因，一个或多个线程滞后，而产生了CXPACKET等待状态。 有一个组织/协调（organizer/coordinator）线程(Thread 0)，它需要等待所有线程完成并聚合数据来呈现给客户端。 组织线程必须等待所有线程完成处理才能进行下一步。由于组织线程等待缓慢的线程完成处理所产生的等待，就叫CXPACKET等待。,可调的参数,cost threshold for parallelism（ 创建和运行并行查询计划的阈值） http:/ max degree of parallelism
4、（执行单个查询时所用的最大处理器数） http:/ SOS_SCHEDULER_YIELD的等待值偏高，那么说明有cpu密集型查询，需要优化sql或者增加cpu,结合sys.dm_exec_query_stats定位CPU消耗高的SQL,SELECT TOP 3total_worker_time ,execution_count ,total_worker_time / execution_count AS Avg CPU Time ,CASE WHEN deqs.statement_start_offset = 0AND deqs.statement_end_offset = -1THEN
5、- see objectText column-ELSE - query - + CHAR(13) + CHAR(10)+ SUBSTRING(execText.text, deqs.statement_start_offset / 2,( ( CASE WHEN deqs.statement_end_offset = -1THEN DATALENGTH(execText.text)ELSE deqs.statement_end_offsetEND ) - deqs.statement_start_offset ) / 2)END AS queryText FROM sys.dm_exec_q
6、uery_stats deqsCROSS APPLY sys.dm_exec_sql_text(deqs.plan_handle) AS execText ORDER BY deqs.total_worker_time DESC ;,从Connections、Sessions和Requests从手调查,sys.sysprocesses sys.dm_exec_connections sys.dm_exec_sessions sys.dm_exec_requests sys.dm_tran_session_transactions,谁连接到数据库了？- Get a count of SQL co
7、nnections by IP address SELECT dec.client_net_address ,des.program_name ,des.host_name ,-des.login_name ,COUNT(dec.session_id) AS connection_count FROM sys.dm_exec_sessions AS desINNER JOIN sys.dm_exec_connections AS decON des.session_id = dec.session_id - WHERE LEFT(des.host_name, 2) = WK GROUP BY
8、dec.client_net_address ,des.program_name ,des.host_name - des.login_name - HAVING COUNT(dec.session_id) 1 ORDER BY des.program_name,dec.client_net_address ;,这些连接正在执行什么SQL语句？SELECT dec.client_net_address ,des.host_name ,dest.text FROM sys.dm_exec_sessions desINNER JOIN sys.dm_exec_connections decON d
9、es.session_id = dec.session_idCROSS APPLY sys.dm_exec_sql_text(dec.most_recent_sql_handle) dest -WHERE des.program_name LIKE Microsoft SQL Server Management Studio% ORDER BY des.program_name ,dec.client_net_address,- Sessions that are open but have been inactive for more than 5 days. DECLARE days_ol
10、d SMALLINT SELECT days_old = 5 SELECT des.session_id ,des.login_time ,des.last_request_start_time ,des.last_request_end_time ,des.status ,des.program_name ,des.cpu_time ,des.total_elapsed_time ,des.memory_usage ,des.total_scheduled_time ,des.total_elapsed_time ,des.reads ,des.writes ,des.logical_rea
11、ds ,des.row_count ,des.is_user_process FROM sys.dm_exec_sessions desINNER JOIN sys.dm_tran_session_transactions dtstON des.session_id = dtst.session_id WHERE des.is_user_process = 1AND DATEDIFF(dd, des.last_request_end_time, GETDATE() days_oldAND des.status != Running ORDER BY des.last_request_end_t
12、ime,连接泄漏问题,目前连接正在执行什么SQL语句?,- Retrieving the text for a currently executing ad hoc query. SELECT dest.text ,dest.dbid ,dest.objectid FROM sys.dm_exec_requests AS derCROSS APPLY sys.dm_exec_sql_text(der.sql_handle) AS dest WHERE session_id = spid ;,- Retrieving the text for a currently executing batc
13、h. SELECT dest.text FROM sys.dm_exec_requests AS derCROSS APPLY sys.dm_exec_sql_text(der.sql_handle) AS dest WHERE session_id spidAND text LIKE %waitfor% ;,- Returning the text of an executing stored procedure SELECT dest.dbid ,dest.objectid ,dest.encrypted ,dest.text FROM sys.dm_exec_requests AS de
14、rCROSS APPLY sys.dm_exec_sql_text(der.sql_handle) AS dest WHERE objectid = object_id(test, p);,- Who is running what at this instant SELECT dest.text AS Command text ,des.login_time ,des.host_name ,des.program_name ,der.session_id ,dec.client_net_address ,der.status ,mand ,DB_NAME(der.database_id) A
15、S DatabaseName FROM sys.dm_exec_requests derINNER JOIN sys.dm_exec_connections decON der.session_id = dec.session_idINNER JOIN sys.dm_exec_sessions desON des.session_id = der.session_idCROSS APPLY sys.dm_exec_sql_text(sql_handle) AS dest WHERE des.is_user_process = 1,Who is running what?,Sp_who2,- A
16、 better sp_who2. SELECT des.session_id ,des.status , des.login_name , des.HOST_NAME , der.blocking_session_id , DB_NAME(der.database_id) AS database_name , mand , des.cpu_time , des.reads , des.writes , dec.last_write , des.program_name , der.wait_type , der.wait_time , der.last_wait_type , der.wait
17、_resource ,CASE des.transaction_isolation_levelWHEN 0 THEN Unspecified WHEN 1 THEN ReadUncommittedWHEN 2 THEN ReadCommitted WHEN 3 THEN RepeatableWHEN 4 THEN Serializable WHEN 5 THEN SnapshotEND AS transaction_isolation_level ,OBJECT_NAME(dest.objectid, der.database_id) AS OBJECT_NAME ,SUBSTRING(des
18、t.text, der.statement_start_offset / 2,( CASE WHEN der.statement_end_offset = -1THEN DATALENGTH(dest.text)ELSE der.statement_end_offsetEND - der.statement_start_offset ) / 2)AS executing statement ,deqp.query_plan FROM sys.dm_exec_sessions desLEFT JOIN sys.dm_exec_requests der ON des.session_id = de
19、r.session_idLEFT JOIN sys.dm_exec_connections dec ON des.session_id = dec.session_idCROSS APPLY sys.dm_exec_sql_text(der.sql_handle) destCROSS APPLY sys.dm_exec_query_plan(der.plan_handle) deqp WHERE des.session_id SPID ORDER BY des.session_id,查找正在执行的最耗CPU的SQL并取出执行计划进行分析,- Requests by CPU consumptio
20、n. SELECT der.session_id ,DB_NAME(der.database_id) AS database_name ,deqp.query_plan ,SUBSTRING(dest.text, der.statement_start_offset / 2,( CASE WHEN der.statement_end_offset = -1THEN DATALENGTH(dest.text)ELSE der.statement_end_offsetEND - der.statement_start_offset ) / 2)AS statement executing ,der
21、.cpu_time-der.granted_query_memory-der.wait_time-der.total_elapsed_time-der.reads FROM sys.dm_exec_requests derINNER JOIN sys.dm_exec_sessions desON des.session_id = der.session_idCROSS APPLY sys.dm_exec_sql_text(der.sql_handle) destCROSS APPLY sys.dm_exec_query_plan(der.plan_handle) deqp WHERE des.
22、is_user_process = 1AND der.session_id spid ORDER BY der.cpu_time DESC ; - ORDER BY der.granted_query_memory DESC ; - ORDER BY der.wait_time DESC; - ORDER BY der.total_elapsed_time DESC; - ORDER BY der.reads DESC;,深入定位SQL性能问题 - 分析执行计划,执行计划的重用情况调查 调查重用度最高的查询执行计划及SQL 调查具体某个存储过程的执行计划重用情况 调查重用度低的SQL 调查资源
23、消耗高的SQL,- An overview of plan reuseSELECT MAX(CASE WHEN usecounts BETWEEN 10 AND 100 THEN 10-100WHEN usecounts BETWEEN 101 AND 1000 THEN 101-1000WHEN usecounts BETWEEN 1001 AND 5000 THEN 1001-5000WHEN usecounts BETWEEN 5001 AND 10000 THEN 5001-10000ELSE CAST(usecounts AS VARCHAR(100)END) AS usecount
24、s ,COUNT(*) AS countInstance FROM sys.dm_exec_cached_plans GROUP BY CASE WHEN usecounts BETWEEN 10 AND 100 THEN 50WHEN usecounts BETWEEN 101 AND 1000 THEN 500WHEN usecounts BETWEEN 1001 AND 5000 THEN 2500WHEN usecounts BETWEEN 5001 AND 10000 THEN 7500ELSE usecountsEND ORDER BY CASE WHEN usecounts BE
25、TWEEN 10 AND 100 THEN 50WHEN usecounts BETWEEN 101 AND 1000 THEN 500WHEN usecounts BETWEEN 1001 AND 5000 THEN 2500WHEN usecounts BETWEEN 5001 AND 10000 THEN 7500ELSE usecountsEND DESC ;,- Investigating the most-used plansSELECT TOP 2 WITH TIESdecp.usecounts ,decp.cacheobjtype ,decp.objtype ,deqp.que
26、ry_plan ,dest.text FROM sys.dm_exec_cached_plans decpCROSS APPLY sys.dm_exec_query_plan(decp.plan_handle) AS deqpCROSS APPLY sys.dm_exec_sql_text(decp.plan_handle) AS dest ORDER BY usecounts DESC ;,- Examining plan reuse for a single procedureSELECT usecounts ,cacheobjtype ,objtype ,OBJECT_NAME(dest
27、.objectid) FROM sys.dm_exec_cached_plans decpCROSS APPLY sys.dm_exec_sql_text(decp.plan_handle) AS dest WHERE dest.objectid = OBJECT_ID()AND dest.dbid = DB_ID() ORDER BY usecounts DESC ;,调查具体某个存储过程的执行计划重用情况,- Examining single-use plans in the cache - Find single-use, ad-hoc queries that are bloating
28、 the plan cacheSELECT TOP ( 100 )text ,cp.size_in_bytes FROM sys.dm_exec_cached_plans AS cpCROSS APPLY sys.dm_exec_sql_text(plan_handle) WHERE cp.cacheobjtype = Compiled PlanAND cp.objtype = AdhocAND cp.usecounts = 1 ORDER BY cp.size_in_bytes DESC ;,对于只重用1次的SQL,要注意是否未正确参数化导致,- Finding the CPU-intens
29、ive queriesSELECT TOP 3total_worker_time ,execution_count ,total_worker_time / execution_count AS Avg CPU Time ,CASE WHEN deqs.statement_start_offset = 0AND deqs.statement_end_offset = -1THEN - see objectText column-ELSE - query - + CHAR(13) + CHAR(10)+ SUBSTRING(execText.text, deqs.statement_start_
30、offset / 2,( ( CASE WHEN deqs.statement_end_offset = -1THEN DATALENGTH(execText.text)ELSE deqs.statement_end_offsetEND ) - deqs.statement_start_offset ) / 2)END AS queryText FROM sys.dm_exec_query_stats deqsCROSS APPLY sys.dm_exec_sql_text(deqs.plan_handle) AS execText ORDER BY deqs.total_worker_tim
31、e DESC ;,关注执行次数、平均CPU时间,- Investigating logical reads performed by cached stored procedures - Top Cached SPs By Total Logical Reads (SQL 2008 only). - Logical reads relate to memory pressureSELECT TOP ( 25 )p.name AS SP Name ,deps.total_logical_reads AS TotalLogicalReads ,deps.total_logical_reads /
32、deps.execution_count AS AvgLogicalReads ,deps.execution_count ,ISNULL(deps.execution_count / DATEDIFF(Second, deps.cached_time,GETDATE(), 0) AS Calls/Second ,deps.total_elapsed_time ,deps.total_elapsed_time / deps.execution_count AS avg_elapsed_time ,deps.cached_time FROM sys.procedures AS pINNER JO
33、IN sys.dm_exec_procedure_statsAS deps ON p.object_id = deps.object_id WHERE deps.database_id = DB_ID() ORDER BY deps.total_logical_reads DESC ;,存储过程的逻辑读多的话跟内存压力有关系,从Transaction角度调查性能问题,锁阻塞检测 sys.dm_tran_locks,- An uncommitted update of the Production table in AdventureWorks.BEGIN TRANSACTION UPDATE
34、Production.ProductCategory SET Name = Parts WHERE Name = Components; -ROLLBACK TRANSACTION,- A simple query against the ProductCategory table, which will be blocked. SELECT * FROM Production.ProductCategory ;,检测锁SELECT resource_type ,DB_NAME(resource_database_id) AS Database Name ,CASE WHEN DTL.reso
35、urce_type IN ( DATABASE, FILE, METADATA )THEN DTL.resource_typeWHEN DTL.resource_type = OBJECTTHEN OBJECT_NAME(DTL.resource_associated_entity_id,DTL.resource_database_id)WHEN DTL.resource_type IN ( KEY, PAGE, RID )THEN ( SELECT OBJECT_NAME(object_id)FROM sys.partitionsWHERE sys.partitions.hobt_id =
36、DTL.resource_associated_entity_id)ELSE UnidentifiedEND AS requested_object_name ,request_mode ,resource_description FROM sys.dm_tran_locks DTL WHERE DTL.resource_type DATABASE ;,- Which sessions are causing blocking and what statement are they running? SELECT DTL.request_session_id AS session_id , D
37、B_NAME(DTL.resource_database_id) AS Database , DTL.resource_type ,CASE WHEN DTL.resource_type IN ( DATABASE, FILE, METADATA ) THEN DTL.resource_type WHEN DTL.resource_type = OBJECT THEN OBJECT_NAME(DTL.resource_associated_entity_id, DTL.resource_database_id)WHEN DTL.resource_type IN ( KEY, PAGE, RID
38、 ) THEN ( SELECT OBJECT_NAME(object_id) FROM sys.partitions WHERE sys.partitions.hobt_id = DTL.resource_associated_entity_id)ELSE UnidentifiedEND AS Parent Object , DTL.request_mode AS Lock Type , DTL.request_status AS Request Status , DER.blocking_session_id , DES.login_name ,CASE DTL.request_lifet
39、imeWHEN 0 THEN DEST_R.TEXTELSE DEST_C.TEXTEND AS Statement FROM sys.dm_tran_locks DTLLEFT JOIN sys.dm_exec_requests DER ON DTL.request_session_id = DER.session_id INNER JOIN sys.dm_exec_sessions DES ON DTL.request_session_id = DES.session_id INNER JOIN sys.dm_exec_connections DEC ON DTL.request_sess
40、ion_id = DEC.most_recent_session_id OUTER APPLY sys.dm_exec_sql_text(DEC.most_recent_sql_handle) AS DEST_C OUTER APPLY sys.dm_exec_sql_text(DER.sql_handle) AS DEST_R WHERE DTL.resource_database_id = DB_ID() AND DTL.resource_type NOT IN ( DATABASE, METADATA ) ORDER BY DTL.request_session_id ;,基于等待任务调查锁和阻塞,关于性能测试与调优,性能测试不仅仅是LoadRunner对被测系统了解得越多,越有可能找到性能问题在哪里对被测试系统了解得越深,越有可能找到性能优化的最佳方案,Thanks!,
