Azure SQL Datawarehouse中的索引碎片 [英] Index Fragmentation in Azure SQL Datawarehouse

问题描述

我们如何监控azure sql datawarehouse中的索引碎片。您能否提供一些选项来检查群集和非群集索引的索引碎片？任何检查碎片的脚本都会有所帮助。谢谢！

How can we monitor Index fragmentation in azure sql datawarehouse. Could you please provide what are the options to check Index fragmentation of clustered and non-clustered indexes? Any script to check fragmentation will be helpful. Thanks!

推荐答案

Azure SQL数据仓库默认使用群集Columnstore表，其中列段存储在Azure高级SSD存储上并缓存在本地闪存存储上。   Clustered Columnstore表不受可能影响BTree索引的索引碎片
的影响。 甚至BTree索引在存储在闪存存储器时也不需要进行碎片整理。

Azure SQL Data Warehouse uses clustered Columnstore tables by default, with the column segments stored on Azure premium SSD storage and cached on local flash storage.   Clustered Columnstore tables aren't subject to the kinds of index fragmentation that can affect BTree indexes.  And even BTree indexes don't really need to be defragmented when stored on flash storage.

所以预计你不需要担心索引碎片。

So it's not expected that you would need to worry about index fragmentation.

由于您通常只在较小（可能是复制的）表上具有聚簇索引或非聚簇索引，因此可以根据需要频繁地重建表。  

Since you typically only have clustered or non-clustered indexes on smaller (perhaps replicated) tables, you can rebuild the tables frequently if you really want.  

您可以使用DMV监控表和索引的存储，例如
sys.dm_db_partition_stats 

You can monitor the storage of your tables and indexes with DMVs like sys.dm_db_partition_stats 

David

这篇关于Azure SQL Datawarehouse中的索引碎片的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！