第七章——DMVs和DMFs（2）——...-第七章——DMVs和DMFs（1）-OCP考题解析_043: flashback transaction query

当前位置: 数据库>其它

本页文章导读:

▪第七章——DMVs和DMFs（2）——用DMV和DMF监控索引性能本文继续介绍使用DMO来监控，这次讲述的是监控索引性能。索引是提高查询性能的关键性手段。即使你的表上有合适的索引，你也要时时刻刻进行索引维护任务。 SQLServer有专门的DMO.........

▪第七章——DMVs和DMFs（1）简介：从SQLServer2005开始，微软引入了一个名叫DMO（动态管理对象）的新特性，DMO可以分为DMFs（Dynamic Manage Functions，动态管理函数）和DMVs（Dynamic Manage Views，.........

▪OCP考题解析_043: flashback transaction query flashback transaction query可以从FLASHBACK_TRANSACTION_QUERY中获得指定事务的历史信息以及Undo_SQL 通过这个UNDO_SQL，查询具体事务所做的操作、我们就可以恢复特.........

[1]第七章——DMVs和DMFs（2）——用DMV和DMF监控索引性能

来源: 互联网发布时间: 2013-11-07

本文继续介绍使用DMO来监控，这次讲述的是监控索引性能。索引是提高查询性能的关键性手段。即使你的表上有合适的索引，你也要时时刻刻进行索引维护任务。

SQLServer有专门的DMO来显示索引相关统计信息。能帮助你分析现有索引的性能情况。通过这些DMO，可以做到：

Ø 检查索引使用模式

Ø 查找丢失索引

Ø 查找无用索引

Ø 查找索引碎片

Ø 分析索引页分配明细

本文将使用这些DMO来检查数据库的丢失索引，索引上的查找和扫描操作，并分析索引碎片是否有必要重组或重建。

准备工作：

下面的例子将演示如何通过DMO来找到丢失索引，丢失索引是不存在但创建后可以提高查询性能的索引。

同时可以看到如何查找索引使用明细和碎片明细，对索引维护工作很有帮助。

本文将使用SQLServer 2008R2和示例数据库AdventureWorks来演示。

步骤：

1、打开SSMS并连到SQLServer，选择AdventureWorks数据库。

2、在新窗口中输入以下代码：

USE AdventureWorks
GO
SELECT  SalesOrderID ,
        SalesOrderDetailID ,
        OrderQty ,
        ProductID
FROM    Sales.SalesOrderDetail
WHERE   ModifiedDate >= '2004-01-01 00:00:00.000'
GO

SELECT  SalesOrderDetailID ,
        UnitPrice ,
        UnitPriceDiscount
FROM    Sales.SalesOrderDetail
WHERE   ProductID = 921
GO

3、在另外一个窗口输入以下代码来查找缺失索引的情况：

SELECT  MID.statement AS ObjectName ,
        MID.equality_columns ,
        MID.inequality_columns ,
        MID.included_columns ,
        MIGS.avg_user_impact AS ExpectedPerformanceImprovement ,
        ( MIGS.user_seeks + MIGS.user_scans ) * MIGS.avg_total_user_cost
        * MIGS.avg_user_impact AS PossibleImprovement
FROM    sys.dm_db_missing_index_details AS MID
        INNER JOIN sys.dm_db_missing_index_groups AS MIG ON MID.index_handle = MIG.index_handle
        INNER JOIN sys.dm_db_missing_index_group_stats AS MIGS ON MIG.index_group_handle = MIGS.group_handle
GO

4、为了找到索引使用明细，输入以下代码：

USE AdventureWorks
GO
SELECT  O.name AS ObjectName ,
        I.name AS IndexName ,
        IUS.user_seeks ,
        IUS.user_scans ,
        IUS.last_user_seek ,
        IUS.last_user_scan
FROM    sys.dm_db_index_usage_stats AS IUS
        INNER JOIN sys.indexes AS I ON IUS.object_id = I.object_id
                                       AND IUS.index_id = I.index_id
        INNER JOIN sys.objects AS O ON IUS.object_id = O.object_id
GO

5、输入以下代码来查找索引碎片：

USE AdventureWorks
GO
SELECT  O.name AS ObjectName ,
        I.name AS IndexName ,
        IPS.avg_page_space_used_in_percent AS AverageSpaceUsedInPages ,
        IPS.avg_fragmentation_in_percent AS AverageFragmentation ,
        IPS.fragment_count AS FragmentCount ,
        suggestedIndexOperation = CASE WHEN IPS.avg_fragmentation_in_percent <= 30
                                       THEN 'ReOrganize Index'
                                       ELSE 'ReBuild Index'
                                  END
FROM    sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, 'DETAILED')
        AS IPS
        INNER JOIN sys.indexes AS I ON IPS.index_id = I.index_id
                                       AND IPS.object_id = I.object_id
        INNER JOIN sys.objects AS O ON IPS.object_id = O.object_id
WHERE   IPS.avg_fragmentation_in_percent > 5
ORDER BY AverageFragmentation DESC 
GO

结果如下：

分析：

在本文中，首先在AdventureWorks库上的Sales.SalesOrderDetail表中做一些简单的查询，第一个查询是基于这个表的ModifiedDate，而第二个查询是基于ProductID。

SQLServer提供缺失索引的详细情况。缺失索引是指在数据库中不存在的索引，但是如果创建，查询将会得益并运行得更快。可以关联两个DMV，sys.dm_db_missing_index_groups和sys.dm_db_missing_index_group_stats。其中DMV，sys.dm_db_missing_index_groups_stats返回关于如果创建了缺失索引，将会对查询有多少可能性的提高的详细信息。注意从这个视图中查找的avg_user_impact列，这里间接地通过这列来和Sys.dm_db_missing_index_details与sys.dm_db_missing_index_groups关联。

和DTA（数据库优化引擎顾问）类似，DMV可能会建议广泛使用很多INCLUDE列在索引中。所以你不应该不顾一切把所有索引全部创建。因为这对你的DML操作可能会带来比较大的影响，如增删改等操作。

在第二个查询DMO中，返回了特定索引的查找和扫描数量。为了返回索引名称和对象名称，关联了sys.indexes和sys.objects这两个目录视图。

最后就是查找碎片，这里使用了DMF而不是DMV，sys.dm_db_index_physical_stats()函数。留意一下，这里使用了DB_ID()函数，因为希望得到当前数据库的所有索引信息，所以用这个函数即可。为了得到索引名和对象名，关联了sys.indexex和sys.objects目录视图。

扩充知识：

除了上面提到的DMO

[2]第七章——DMVs和DMFs（1）

来源: 互联网发布时间: 2013-11-07

简介：

从SQLServer2005开始，微软引入了一个名叫DMO（动态管理对象）的新特性，DMO可以分为DMFs（Dynamic Manage Functions，动态管理函数）和DMVs（Dynamic Manage Views，动态管理视图）两部分。这些函数和视图用于查找SQLServer实例内部统计信息以供性能监控所用。它们提供实时的，关于SQLServer内部工作的，能用于性能分析和性能故障排除的各种统计信息。

所有的DMO都属于sys架构，并且以dm_开头。执行DMO需要有VIEW SERVER STATE和VIEW DATABASE STATE权限。

下面简述一下本系列将要介绍的DMO：

Ø 执行相关的DMO（sys.dm_exec_*）：提供与执行相关的统计信息。可以用于监控与缓存查询、执行计划、活动连接/会话和带有执行计划的当前运行的查询的相关统计信息。

Ø 索引相关的DMO（sys.dm_db_index_*和sys.dm_db_missing_*）：提供关于索引的统计信息。这些DMO可以用于监控和分析因为丢失索引、无效索引而导致的性能问题，也可以用来检查索引的使用情况。

Ø 数据库相关DMO（sys.dm_db_*）：提供数据库相关统计信息。可以用于监控和分析数据库的性能问题，分析数据库相关文件的统计信息、会话统计信息和任务统计信息。

Ø I/O相关DMO（sys.dm_io_*）：提供I/O操作的统计信息，用于监控和分析SQLServer的I/O性能问题。

Ø OS相关DMO（sys.dm_os_*）：提供关于sqlos内部统计信息，用于监控和分析服务器配置问题。

Ø 事务相关的DMO（sys.dm_trn_*）：提供事务相关的统计信息，用于监控和分析长时间运行的事务的锁定、死锁问题。

这些DMO的数据可以通过DBCC SQLPERF(‘SYS.DM_OS_WAIT_STATS’,CLEAR)或者重启服务器来重置。

监控当前查询执行的统计信息：

为了使得服务器上的查询足够的好，需要识别那些消耗资源的查询和找到这些资源的来源。为了实现这些功能，需要监控查询的请求和检查它们的运行时间、IO操作等等。

SQLServer有专用的DMO来监控查询的执行信息，这些DMO包含广泛的信息，以sys.dm_exec_开头。通过这些DMO可以快速发现问题查询，从而进行优化。

本文将演示使用DMO来获取当前正在请求SQLServer查询的信息，并找到长时间运行的查询，同时可以监控当前正在运行的游标，这个通常也会引起性能问题。

准备工作：

本文中将使用DMO来监控当前查询请求的一些有用信息，如数据库名、登录名、程序名、查询开始时间、读写数。

众所周知，游标是非常消耗资源且影响查询性能的，如非必要，不建议使用。

本例中使用SQLServer 2008R2，并在微软示例数据库AdventureWorks上操作。

步骤：

1、打开SSMS，连到SQLServer实例。

2、打开新查询窗口，并输入以下代码，用于监控当前查询：

SELECT  DB_NAME(R.database_id) AS DatabaseName ,
        S.original_login_name AS LoginName ,
        S.host_name AS ClientMachine ,
        S.program_name AS ApplicationName ,
        R.start_time AS RequestStartTime ,
        ST.text AS SQLQuery ,
        QP.query_plan AS ExecutionPlan ,
        R.cpu_time AS CPUTime ,
        R.total_elapsed_time AS TotalTimeElapsed ,
        R.open_transaction_count AS TotalTransactionOpened ,
        R.reads ,
        R.logical_reads ,
        R.writes AS TotalWrites
FROM    sys.dm_exec_requests AS R
        INNER JOIN sys.dm_exec_sessions AS S ON R.session_id = S.session_id
        CROSS APPLY sys.dm_exec_sql_text(R.sql_handle) AS ST
        CROSS APPLY sys.dm_exec_query_plan(R.plan_handle) AS QP
ORDER BY TotalTimeElapsed DESC 
GO

3、打开新窗口输入一下脚本，用于监控当前打开的游标：

SELECT  S.host_name AS ClientMachine ,
        S.program_name AS ApplicationName ,
        S.original_login_name AS LoginName ,
        C.name AS CursorName ,
        C.properties AS CursorOptions ,
        C.creation_time AS CursorCreatinTime ,
        ST.text AS SQLQuery ,
        C.is_open AS IsCursorOpen ,
        C.worker_time / 1000 AS DurationInMiliSeconds ,
        C.reads AS NumberOfReads ,
        C.writes AS NumberOfWrites
FROM    sys.dm_exec_cursors (0) AS C
        INNER JOIN sys.dm_exec_sessions AS S ON C.session_id = S.session_id
        CROSS APPLY sys.dm_exec_sql_text(C.sql_handle) AS ST
ORDER BY DurationInMiliSeconds DESC 
GO

分析：

在上面步骤中，使用了以下的DMOs：

Ø Sys.dm_exec_requests

Ø Sys.dm_exec_sessions

Ø Sys.dm_exec_sql_text

Ø Sys.dm_exec_query_plan

[3]OCP考题解析_043: flashback transaction query

来源: 互联网发布时间: 2013-11-07

flashback transaction query可以从FLASHBACK_TRANSACTION_QUERY中获得指定事务的历史信息以及Undo_SQL

通过这个UNDO_SQL，查询具体事务所做的操作、我们就可以恢复特定的事务、flashback transaction query测试

OCP考题：

13. You executed the following query: 
    SELECT operation, undo_sql, table_name FROM flashback_transaction_query; 
    Which statement is correct regarding the query output? 

A. It would return information regarding only the last committed transaction. 

B. It would return only the active transactions in all the undosegments in the database.
 
C. It would return only the committed transactions in all the undo segments in the database. 

D. It would return both active and committed transactions in all the undo segments in the database. 

E. It would return information regarding the transactions that began and were committed in the last 30 minutes. 


Answer: D

作者：linwaterbin 发表于2013-3-4 20:09:17 原文链接

阅读：46 评论：0 查看评论