虽然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)

最主要ＤＭＯ有以下三种：

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.

一些ＤＭＯ使用举例：

-- 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)