MSSQL 读书笔记 查询性能分析(三) DMO
SqlServer
2015-07-14
975
0
虽然Extended Events, SQL Trace, SQL Server Profiler, SET session options, 和 execution plans为你优化语句提供了很多帮助,但是这还不够。SQL Server一直在监控它自己,并且生成有用的数据。 SQL Server 提供了 DMO(dynamic management objects) 对象来查看这些信息。 其中包括动态管理视图和动态管理函数。 所有DMO,使用system schema,DMO的名字以dm_开始。部分DMO显示的是当前状态,有些则是显示累计数据。
在SQL Server 2012 有130个DMO ,具体可以看官方在线文档
Dynamic Management Views and Functions (Transact-SQL)
最主要DMO有以下三种:
SQL Server Operating System (SQLOS)–related DMOs The SQLOS manages operating system resources that are specific to SQL Server. ** Execution-related DMOs** These DMOs provide you with insight into queries that have been executed, including their query text, execution plan, number of executions, and more. ** Index-related DMOs** These DMOs provide useful information about index usage and missing indexes.
一些DMO使用举例:
-- Base info - sys.dm_os_sys_info SELECT cpu_count AS logical_cpu_count, cpu_count / hyperthread_ratio AS physical_cpu_count, CAST(physical_memory_kb / 1024\. AS int) AS physical_memory__mb, sqlserver_start_time FROM sys.dm_os_sys_info; -- Waiting sessions - sys.dm_os_waiting_tasks, sys.dm_exec_sessions SELECT S.login_name, S.host_name, S.program_name, WT.session_id, WT.wait_duration_ms, WT.wait_type, WT.blocking_session_id, WT.resource_description FROM sys.dm_os_waiting_tasks AS WT INNER JOIN sys.dm_exec_sessions AS S ON WT.session_id = S.session_id WHERE s.is_user_process = 1; -- Currently executing batches, with text and wait info SELECT S.login_name, S.host_name, S.program_name, R.command, T.text, R.wait_type, R.wait_time, R.blocking_session_id FROM sys.dm_exec_requests AS R INNER JOIN sys.dm_exec_sessions AS S ON R.session_id = S.session_id OUTER APPLY sys.dm_exec_sql_text(R.sql_handle) AS T WHERE S.is_user_process = 1; -- Top five queries by total logical IO SELECT TOP (5) (total_logical_reads + total_logical_writes) AS total_logical_IO, execution_count, (total_logical_reads/execution_count) AS avg_logical_reads, (total_logical_writes/execution_count) AS avg_logical_writes, (SELECT SUBSTRING(text, statement_start_offset/2 + 1, (CASE WHEN statement_end_offset = -1 THEN LEN(CONVERT(nvarchar(MAX),text)) * 2 ELSE statement_end_offset END - statement_start_offset)/2) FROM sys.dm_exec_sql_text(sql_handle)) AS query_text FROM sys.dm_exec_query_stats ORDER BY (total_logical_reads + total_logical_writes) DESC; GO -- 最后一次实例启动至今未使用过的非聚集索引 SELECT OBJECT_NAME(I.object_id) AS objectname, I.name AS indexname, I.index_id AS indexid FROM sys.indexes AS I INNER JOIN sys.objects AS O ON O.object_id = I.object_id WHERE I.object_id > 100 AND I.type_desc = 'NONCLUSTERED' AND I.index_id NOT IN (SELECT S.index_id FROM sys.dm_db_index_usage_stats AS S WHERE S.object_id=I.object_id AND I.index_id=S.index_id AND database_id = DB_ID('TSQL2012')) ORDER BY objectname, indexname;
查找Missing Indexes -- Recreation of the table from previous practice -- and reproducing the missing index problem SELECT N1.n * 100000 + O.orderid AS norderid, O.* INTO dbo.NewOrders FROM Sales.Orders AS O CROSS JOIN (VALUES(1),(2),(3)) AS N1(n); GO CREATE NONCLUSTERED INDEX idx_nc_orderid ON dbo.NewOrders(orderid); GO SELECT norderid FROM dbo.NewOrders WHERE norderid = 110248 ORDER BY norderid; GO -- . Missing indexes SELECT MID.statement AS [Database.Schema.Table], MIC.column_id AS ColumnId, MIC.column_name AS ColumnName, MIC.column_usage AS ColumnUsage, MIGS.user_seeks AS UserSeeks, MIGS.user_scans AS UserScans, MIGS.last_user_seek AS LastUserSeek, MIGS.avg_total_user_cost AS AvgQueryCostReduction, MIGS.avg_user_impact AS AvgPctBenefit FROM sys.dm_db_missing_index_details AS MID CROSS APPLY sys.dm_db_missing_index_columns (MID.index_handle) AS MIC INNER JOIN sys.dm_db_missing_index_groups AS MIG ON MIG.index_handle = MIG.index_handle INNER JOIN sys.dm_db_missing_index_group_stats AS MIGS ON MIG.index_group_handle=MIGS.group_handle ORDER BY MIGS.avg_user_impact DESC; GO --. Clean up DROP TABLE dbo.NewOrders; GO
--Top 10 Total CPU Consuming Queries SELECT TOP 10 QT.TEXT AS STATEMENT_TEXT, QP.QUERY_PLAN, QS.TOTAL_WORKER_TIME AS CPU_TIME FROM SYS.DM_EXEC_QUERY_STATS QS CROSS APPLY SYS.DM_EXEC_SQL_TEXT (QS.SQL_HANDLE) AS QT CROSS APPLY SYS.DM_EXEC_QUERY_PLAN (QS.PLAN_HANDLE) AS QP ORDER BY TOTAL_WORKER_TIME DESC
--Top 10 Average CPU Consuming Queries SELECT TOP 10 TOTAL_WORKER_TIME , EXECUTION_COUNT , TOTAL_WORKER_TIME / EXECUTION_COUNT AS [AVG CPU TIME] , QT.TEXT AS QUERYTEXT FROM SYS.DM_EXEC_QUERY_STATS QS CROSS APPLY SYS.DM_EXEC_SQL_TEXT(QS.PLAN_HANDLE) AS QT ORDER BY QS.TOTAL_WORKER_TIME DESC ;
--Top 10 I/O Intensive Queries SELECT TOP 10 TOTAL_LOGICAL_READS, TOTAL_LOGICAL_WRITES, EXECUTION_COUNT, TOTAL_LOGICAL_READS+TOTAL_LOGICAL_WRITES AS [IO_TOTAL], QT.TEXT AS QUERY_TEXT, DB_NAME(QT.DBID) AS DATABASE_NAME, QT.OBJECTID AS OBJECT_ID FROM SYS.DM_EXEC_QUERY_STATS QS CROSS APPLY SYS.DM_EXEC_SQL_TEXT(SQL_HANDLE) QT WHERE TOTAL_LOGICAL_READS+TOTAL_LOGICAL_WRITES > 0 ORDER BY [IO_TOTAL] DESC
--Execution Count of Each Query SELECT QS.EXECUTION_COUNT, QT.TEXT AS QUERY_TEXT, QT.DBID, DBNAME= DB_NAME (QT.DBID), QT.OBJECTID, QS.TOTAL_ROWS, QS.LAST_ROWS, QS.MIN_ROWS, QS.MAX_ROWS FROM SYS.DM_EXEC_QUERY_STATS AS QS CROSS APPLY SYS.DM_EXEC_SQL_TEXT(QS.SQL_HANDLE) AS QT ORDER BY QS.EXECUTION_COUNT DESC
--查询当前运行的SQL语句 SELECT er.session_id sid , blocking_session_id bsid -- ,er.percent_complete , er.status , er.wait_type , er.last_wait_type -- ,er.wait_resource , er.total_elapsed_time , er.cpu_time , er.reads , er.writes , er.logical_reads , er.start_time , er.command , DatabaseName = DB_NAME(er.database_id) -- ,user_name(er.user_id) Username , OBJECT_NAME(st.objectid, st.dbid) obj_name , StatementDefinition = SUBSTRING(st.text, ( er.statement_start_offset / 2 ) + 1, ( ( CASE er.statement_end_offset WHEN -1 THEN DATALENGTH(st.text) ELSE er.statement_end_offset END - er.statement_start_offset ) / 2 ) + 1) -- ,er.plan_handle FROM sys.dm_exec_requests er CROSS APPLY sys.dm_exec_sql_text(er.sql_handle) AS st; --31161940 --KILL 271 --SELECT * FROM DooiooDW..DIM_AreaType --SELECT * FROM AnalysisDooioo..DIM_AreaType --update agent_online_cs_fangyuan set empNo=@P0 where agentId=@P1 and convert(varchar(10),createDate,111)=convert(varchar(10),GETDATE(),111)