利用动态管理视图提高SQL Server索引效率

最新推荐文章于 2023-08-02 21:37:49 发布

SAP user

最新推荐文章于 2023-08-02 21:37:49 发布

阅读量774

点赞数

分类专栏： SQL学习笔记文章标签： sql server null 数据库 sql object database

本文链接：https://blog.csdn.net/mazhaoyi/article/details/5158350

版权

SQL学习笔记专栏收录该内容

10 篇文章 0 订阅

订阅专栏

　就如同数据库DBA了解的一样，合适的索引能够提高查询性能和应用程序可测量性。但是每个附加的索引，都给系统增加了额外开销，因为随着数据从表和视图中不断增加、修改或清除，SQL Server需要维护这些索引。

　　之前，我介绍了一下动态管理视图(DMV)。它是一种很有用的监控和解决SQL Server故障的工具。本文是它的续篇，我将继续和大家一起探讨其他的一些数据库管理员用来能够测定现存索引效率的动态管理视图(DMV)和分片级别。此外，我还提供了应该用来检索指定的SQL Server程序ID号(SPID)执行的最后语句。

　　DMV提高索引效率

　　就如同数据库DBA了解的一样，合适的索引能够提高查询性能和应用程序可测量性。但是每个附加的索引，都给系统增加了额外开销，因为随着数据从表和视图中不断增加、修改或清除，SQL Server需要维护这些索引。在安装新的索引之前，你需要检测数据库活动，保证你只有一些能提高平常执行的查询的索引。注意SQL Server并不能阻止你在相同的列上建立多个索引。它也不能提醒你你即将建立的查询并不能优化查询。

　　复制索引对系统并没有好处。同样地，SQL Server查询优化程序不能用解决查询问题的索引也不能对系统带来什么好处。因此，在这里我们至关重要的事情就是了解索引的利用效率和它们对查询性能的影响。幸运的是，SQL Server 2005和2008包括了sys.dm_db_index_usage_stats动态管理视图，我们可以用它来测量索引的效率。和所有其他的动态管理视图一样，contents of sys.dm_db_index_usage_stats的内容在你重启SQL Server实例时就被丢弃了。所以如果你想收集索引使用统计数据，你就应该对自定义表定期复制DMV。

　　每次用索引进行扫描时，DMV就增加了在SQL Server中搜索或查找列。例如一下的查询就在AdventureWorksDW示例数据库中检索用户表和相应视图使用统计：

　　 SELECT

　　 object_name (a. object_id ) AS table_name，

　　 COALESCE (name， ' object with no clustered index ' ) AS index_name，

　　type_desc AS index_type，

　　user_seeks，

　　user_scans，

　　user_lookups，

　　user_updates

　　 FROM sys.dm_db_index_usage_stats a INNER JOIN sys.indexes b

　　 ON a.index_id = b.index_id

　　 AND a. object_id = b. object_id

　　 WHERE database_id = DB_ID ( ' AdventureWorksDW ' )

　　 AND a. object_id > 1000

　　有用的索引在user_seeks列中的总数最大。要注意user_updates这个列，这个列表示指定索引需要的维护级别。如果你注意到了一些用户搜索、扫描或查询很少用到但是还是会经常更新的索引，维护它们的成本就要比持有它们的成本要高。

　　动态管理函数(DMF)和分片索引(fragmented indexes)

　　数据更改会造成索引分段，高级别的分片还会减少索引的效率。结果，SQL Server就不得不去扫描更多的索引页，甚至在用到索引时查询会变得越来越慢。为避免分片的负面影响，DBA可以重建或对索引消除碎化。在SQL Server之前的版本中，你不得不用到DBCC SHOWCONTIG 语句获取索引分片级别。这个语句还有WITH TABLERESULTS这个选项，它返回的结果表格形式、有序结果。

　　你可以想象，在一个有成千上万个表的数据库中检测每个索引肯定是一件很枯燥的工作。更不用说手动执行这项任务就等于是浪费数据库管理员的时间了。相反，许多DBA都实施了一个自动解决方案，这个解决方案上带有临时表、并且得到的结果为 DBCC SHOWCONTIG。然后，你就可以根据索引的分片级别对索引进行重建或消除碎化。

　　然是，这种方法已经过时了。虽然DBCC SHOWCONTIG仍然在SQL Server 2005或2008里还存在，你还是应该用sys.dm_db_index_physical_stats动态管理函数(DMF)。DBCC SHOWCONTIG不支持最新版本中的新索引特征，可能不久就会被清除掉。

　　有了sys.dm_db_index_physical_stats，你就不在需要创建临时表存储结果了。相反你可以在指定的时间内在定义列中用到最新的分片级别，DMF的句法如下：

　　Sys.dm_db_index_physical_stats (

　　{database_id | NULL | DEFAULT | 0 }，

　　{ object_id | NULL | DEFAULT | 0 }，

　　{index_id | NULL | 0 | - 1 | DEFAULT }，

　　{partition_number | NULL | 0 | DEFAULT }，

　　{mode | DEFAULT | NULL } )

　　你能够在SQL Server联机丛书上找到每个字段的详细说明。注意在指定DMF的字段时，你可以用db_id()和object_id()这两个系统函数。

　　以下查询返回所有数据库所有索引的分片信息：

　　 SELECT * FROM sys.dm_db_index_physical_stats( NULL ， NULL ， NULL ， NULL ， NULL )

　　第二个语句返回特定对象所有索引的索引分片级别：

　　 SELECT * FROM sys.dm_db_index_physical_stats( 6 ， 469576711 ， NULL ， NULL ， NULL )

　　你会得到如下结果：

　　database_id object_id index_id partition_number

　　 6 469576711 1 1

　　 6 469576711 1 1

　　index_type_desc alloc_unit_type_desc index_depth

　　 CLUSTERED INDEX IN_ROW_DATA 3

　　 CLUSTERED INDEX LOB_DATA 1

　　index_level avg_fragmentation_in_percent fragment_count

　　 0 0.592592593 87

　　 0 0 NULL

　　avg_fragment_size_in_pages page_count

　　 7.75862069 675

　　 NULL 8396

　　avg_page_space_used_in_percent record_count

　　 NULL NULL

　　 NULL NULL

　　ghost_record_count version_ghost_record_count

　　 NULL NULL

　　 NULL NULL

　　min_record_size_in_bytes max_record_size_in_bytes

　　 NULL NULL

　　 NULL NULL

　　avg_record_size_in_bytes forwarded_record_count

　　 NULL NULL

　　 NULL NULL

　　尽管得到的结果很庞大，该DMF还是只允许你检索你感兴趣的这些列。这是DBCC SHOWCONTIG的另一种更新，它不允许你检索这些列的子集。

　　检索目前执行的SQL语句

　　许多DBA已经用过SQL Server之前版本的DBCC INPUTBUFFER命令来获取已给出的链接执行的最后那个SQL语句。但是这个语句之返回了该语句最后的255个字符，可能不是整个句子。SQL Server 2005和2008提供了检索该信息的几个选项。

　　sys.dm_exec_sql_text和sys.dm_exec_requests DMV应该通力合作，并且fn_get_sql()也应该和同一个sys.dm_exec_requests DMV进行关联，这是另一个获取最后语句的选项。

　　以下是示例：

　　 To find a statement executed by a specific session ( 53 ):

　　 SELECT

　　 SUBSTRING (b. text ， (a.statement_start_offset / 2 ) + 1 ，

　　(( CASE statement_end_offset

　　 WHEN - 1 THEN DATALENGTH (b. text )

　　 ELSE a.statement_end_offset END

　　 - a.statement_start_offset) / 2 ) + 1 ) AS statement_text

　　 FROM sys.dm_exec_requests a

　　 CROSS APPLY fn_get_sql (a.sql_handle) b

　　 WHERE a.session_id = 53

　　 To get SQL statements submitted by all running or suspended sessions:

　　 SELECT

　　a.session_id，

　　a.status，

　　a.start_time，

　　a.command，

　　 SUBSTRING (b. text ， (a.statement_start_offset / 2 ) + 1 ，

　　(( CASE statement_end_offset

　　 WHEN - 1 THEN DATALENGTH (b. text )

　　 ELSE a.statement_end_offset END

　　 - a.statement_start_offset) / 2 ) + 1 ) AS statement_text

　　 FROM sys.dm_exec_requests a

　　 CROSS APPLY sys.dm_exec_sql_text(a.sql_handle) b

　　 WHERE a.status IN ( ' running ' ， ' suspended ' )

　　你可以在SQL Server联机丛书上找出fn_get_sql， sys.dm_exec_requests和sys.dm_exec_sql_text的详细信息。以上查询用statement_start_offset和statement_end_offset 列只检索目前执行的SQL语句，即使是存储程序或自定义函数中已经付带了。如果我们已经用DBCC INPUTBUFFER，那我们应该只能获取存储程序或自定义函数名称和执行参数。