当前位置: 首页 » 产品 » 商务广告 » 正文

使用DMV和DMF分析数据库性能

放大字体  缩小字体 发布日期: 2024-10-04 19:23   来源:http://www.baidu.com/  作者:无忧资讯  浏览次数:537
核心提示:  服务器等待的原因  SELECt TOP 10  [Wait type]=wait_type,  [Wait time (s)]=wait_time_ms / 1000,  [% waiti

  服务器等待的原因

  SELECt TOP 10

  [Wait type]=wait_type,

  [Wait time (s)]=wait_time_ms / 1000,

  [% waiting]=ConVERT(DECIMAL(12,2), wait_time_ms * 100.0

  / SUM(wait_time_ms) OVER())

  FROM sys.dm_os_wait_stats

  WHERe wait_type NOT LIKE '%SLEEP%'

  ORDER BY wait_time_ms DESC;

  读和写

  SELECt TOP 10

  [Total Reads]=SUM(total_logical_reads)

  ,[Execution count]=SUM(qs.execution_count)

  ,DatabaseName=DB_NAME(qt.dbid)

  FROM sys.dm_exec_query_stats qs

  CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) as qt

  GROUP BY DB_NAME(qt.dbid)

  ORDER BY [Total Reads] DESC;

  SELECt TOP 10

  [Total Writes]=SUM(total_logical_writes)

  ,[Execution count]=SUM(qs.execution_count)

  ,DatabaseName=DB_NAME(qt.dbid)

  FROM sys.dm_exec_query_stats qs

  CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) as qt

  GROUP BY DB_NAME(qt.dbid)

  ORDER BY [Total Writes] DESC;

  数据库缺失索引

  SELECt

  DatabaseName=DB_NAME(database_id)

  ,[Number Indexes Missing]=count(*)

  FROM sys.dm_db_missing_index_details

  GROUP BY DB_NAME(database_id)

  ORDER BY 2 DESC;

  缺失索引列表信息

  SELECt DatabaseName=DB_NAME(database_id),* FROM sys.dm_db_missing_index_details Order BY DB_NAME(database_id)

  高开销的缺失索引

  SELECt TOP 10

  [Total Cost]=ROUND(avg_total_user_cost * avg_user_impact * (user_seeks + user_scans),0)

  , avg_user_impact

  , TableName=statement

  , [EqualityUsage]=equality_columns

  , [InequalityUsage]=inequality_columns

  , [Include Cloumns]=included_columns

  FROM sys.dm_db_missing_index_groups g

  INNER JOIN sys.dm_db_missing_index_group_stats s

  ON s.group_handle=g.index_group_handle

  INNER JOIN sys.dm_db_missing_index_details d

  ON d.index_handle=g.index_handle

  ORDER BY [Total Cost] DESC;

  确定开销最高的未使用索引

  SELECt TOP 10 [Total Cost]=ROUND(avg_total_user_cost * avg_user_impact * (user_seeks + user_scans),0),avg_user_impact,TableName=statement, [EqualityUsage]=equality_columns,[InequalityUsage]=inequality_columns,[Include Cloumns]=included_columns FROM sys.dm_db_missing_index_groups g INNER JOIN sys.dm_db_missing_index_group_stats s ON s.group_handle=g.index_group_handle INNER JOIN sys.dm_db_missing_index_details d ON d.index_handle=g.index_handle ORDER BY [Total Cost] DESC;

  确定最高开销索引所使用的脚本并显示结果。

  -- Create required table structure only.

  -- Note: this SQL must be the same as in the Database loop given in the following step.

  SELECt TOP 1

  [Maintenance cost]=(user_updates + system_updates)

  ,[Retrieval usage]=(user_seeks + user_scans + user_lookups)

  ,DatabaseName=DB_NAME()

  ,TableName=OBJECT_NAME(s.[object_id])

  ,IndexName=i.name

  INTO #TempMaintenanceCost

  FROM sys.dm_db_index_usage_stats s

  INNER JOIN sys.indexes i ON s.[object_id]=i.[object_id]

  AND s.index_id=i.index_id

  WHERe s.database_id=DB_ID()

  AND OBJECTPROPERTY(s.[object_id], 'IsMsShipped')=0

  AND (user_updates + system_updates) > 0 -- only report on active rows.

  AND s.[object_id]=-999 -- Dummy value to get table structure.

  ;

  -- Loop around all the databases on the server.

  EXEC sp_MSForEachDB 'USE [?];

  -- Table already exists.

  INSERT INTO #TempMaintenanceCost

  SELECT TOP 10

  [Maintenance cost]=(user_updates + system_updates)

  ,[Retrieval usage]=(user_seeks + user_scans + user_lookups)

  ,DatabaseName=DB_NAME()

  ,TableName=OBJECT_NAME(s.[object_id])

  ,IndexName=i.name

  FROM sys.dm_db_index_usage_stats s

  INNER JOIN sys.indexes i ON s.[object_id]=i.[object_id]

  AND s.index_id=i.index_id

  WHERe s.database_id=DB_ID()

  AND i.name IS NOT NULL -- Ignore HEAP indexes.

  AND OBJECTPROPERTY(s.[object_id], ''IsMsShipped'')=0

  AND (user_updates + system_updates) > 0 -- only report on active rows.

  ORDER BY [Maintenance cost] DESC

  ;

  '

  -- Select records.

  SELECT TOP 10 * FROM #TempMaintenanceCost

  ORDER BY [Maintenance cost] DESC

  -- Tidy up.

  DROp TABLE #TempMaintenanceCost

  显示索引已被使用的次数,并按“使用率”排序。

  -- Create required table structure only.

  -- Note: this SQL must be the same as in the Database loop given in the -- following step.

  SELECT TOP 1

  [Usage]=(user_seeks + user_scans + user_lookups)

  ,DatabaseName=DB_NAME()

  ,TableName=OBJECT_NAME(s.[object_id])

  ,IndexName=i.name

  INTO #TempUsage

  FROM sys.dm_db_index_usage_stats s

  INNER JOIN sys.indexes i ON s.[object_id]=i.[object_id]

  AND s.index_id=i.index_id

  WHERe s.database_id=DB_ID()

  AND OBJECTPROPERTY(s.[object_id], 'IsMsShipped')=0

  AND (user_seeks + user_scans + user_lookups) > 0

  -- only report on active rows.

  AND s.[object_id]=-999 -- Dummy value to get table structure.

  ;

  -- Loop around all the databases on the server.

  EXEC sp_MSForEachDB 'USE [?];

  -- Table already exists.

  INSERT INTO #TempUsage

  SELECt TOP 10

  [Usage]=(user_seeks + user_scans + user_lookups)

  ,DatabaseName=DB_NAME()

  ,TableName=OBJECT_NAME(s.[object_id])

  ,IndexName=i.name

  FROM sys.dm_db_index_usage_stats s

  INNER JOIN sys.indexes i ON s.[object_id]=i.[object_id]

  AND s.index_id=i.index_id

  WHERe s.database_id=DB_ID()

  AND i.name IS NOT NULL -- Ignore HEAP indexes.

  AND OBJECTPROPERTY(s.[object_id], ''IsMsShipped'')=0

  AND (user_seeks + user_scans + user_lookups) > 0 -- only report on active rows.

  ORDER BY [Usage] DESC

  ;

  '

  -- Select records.

  SELECT TOP 10 * FROM #TempUsage ORDER BY [Usage] DESC

  -- Tidy up.

  DROp TABLE #TempUsage

  逻辑上最零碎的索引所使用的脚本

  -- Create required table structure only.

  -- Note: this SQL must be the same as in the Database loop given in the -- following step.

  SELECT TOP 1

  DatbaseName=DB_NAME()

  ,TableName=OBJECT_NAME(s.[object_id])

  ,IndexName=i.name

  ,[Fragmentation %]=ROUND(avg_fragmentation_in_percent,2)

  INTO #TempFragmentation

  FROM sys.dm_db_index_physical_stats(db_id(),null, null, null, null) s

  INNER JOIN sys.indexes i ON s.[object_id]=i.[object_id]

  AND s.index_id=i.index_id

  WHERe s.[object_id]=-999 -- Dummy value just to get table structure.

  ;

  -- Loop around all the databases on the server.

  EXEC sp_MSForEachDB 'USE [?];

  -- Table already exists.

  INSERT INTO #TempFragmentation

  SELECt TOP 10

  DatbaseName=DB_NAME()

  ,TableName=OBJECT_NAME(s.[object_id])

  ,IndexName=i.name

  ,[Fragmentation %]=ROUND(avg_fragmentation_in_percent,2)

  FROM sys.dm_db_index_physical_stats(db_id(),null, null, null, null) s

  INNER JOIN sys.indexes i ON s.[object_id]=i.[object_id]

  AND s.index_id=i.index_id

  WHERe s.database_id=DB_ID()

  AND i.name IS NOT NULL -- Ignore HEAP indexes.

  AND OBJECTPROPERTY(s.[object_id], ''IsMsShipped'')=0

  ORDER BY [Fragmentation %] DESC

  ;

  '

  -- Select records.

  SELECT TOP 10 * FROM #TempFragmentation ORDER BY [Fragmentation %] DESC

  -- Tidy up.

  DROp TABLE #TempFragmentation

  获得IO高的查询

  SELECT TOP 10

  [Average IO]=(total_logical_reads + total_logical_writes) / qs.execution_count

  ,[Total IO]=(total_logical_reads + total_logical_writes)

  ,[Execution count]=qs.execution_count

  ,[Individual Query]=SUBSTRINg (qt.text,qs.statement_start_offset/2,

  (CASE WHEN qs.statement_end_offset=-1

  THEN LEN(ConVERT(NVARCHAr(MAX), qt.text)) * 2

  ELSE qs.statement_end_offset END - qs.statement_start_offset)/2)

  ,[Parent Query]=qt.text

  ,DatabaseName=DB_NAME(qt.dbid)

  FROM sys.dm_exec_query_stats qs

  CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) as qt

  ORDER BY [Average IO] DESC;

  获得I/O统计

  Select wait_type, waiting_tasks_count, wait_time_ms from sys.dm_os_wait_stats where wait_type like 'PAGEIOLATCH%' order by wait_type

  查询当前I/O锁

 
 
[ 产品搜索 ]  [ 加入收藏 ]  [ 告诉好友 ]  [ 打印本文 ]  [ 违规举报 ]  [ 关闭窗口 ]

 

 
推荐图文
推荐产品
点击排行
    行业协会  备案信息  可信网站