SQL索引管理器 - 用于SQL Server和Azure上的索引维护的免费GUI工具

我作为SQL Server DBA工作了8年多，管理和优化服务器的性能。在我的空闲时间，我想为宇宙和我的同事做一些有用的事情。这就是我们最终为SQL Server和Azure 提供免费索引维护工具的方法。

理念

每隔一段时间，人们在处理他们的优先事项时，可能就像一个手指式电池 - 一个激励充电只持续一闪，然后一切都消失了。直到最近，我在这一生活观察中也不例外。我经常被想法创造属于我自己的想法所困扰，但优先级从一个变为另一个并且没有完成任何事情。

DevArt开发用于开发和管理SQL Server，MySQL和Oracle数据库的软件，对我的动机和专业成长产生了很大的影响。

在他们来之前，我对创建自己的产品的具体细节知之甚少，但在此过程中，我获得了很多关于SQL Server内部结构的知识。一年多以来，我一直致力于优化产品线中的查询，逐渐开始了解市场上哪些功能比另一种功能更受欢迎。

在某个阶段，制作一个新的利基产品的想法出现在我面前，但由于某些情况，这个想法没有成功。那时，基本上我没有为公司内部的新项目找到足够的资源而不影响核心业务。

在一个崭新的地方工作，并试图自己创建一个项目让我不断妥协。制造一个拥有所有花里胡哨的大产品的最初想法很快就会停止并逐渐转变为一个不同的方向 - 将计划的功能分解为单独的迷你工具并相互独立地实现它们。

因此，SQL Index Manager诞生了，它是SQL Server和Azure的免费索引维护工具。主要想法是将RedGate和Devart公司的商业源码替代品作为基础，并尝试在我自己的项目中改进其功能。

履行

口头上说，一切听起来都很简单......只需观看几个激励视频，打开“Rocky Balboa”模式，开始制作一款很酷的产品。但让我们面对音乐，一切都不那么乐观，因为在使用系统表函数时存在许多陷阱，sys.dm_db_index_physical_stats同时，它是唯一可以从中获取有关索引碎片的最新信息的地方。

从开发的最初几天起，就有很好的机会在标准方案中制造沉闷的方式，并复制已经调试过的竞争应用程序的逻辑，同时添加一些自组织。但在分析了元数据的查询后，我想做一些更优化的事情，由于大公司的官僚主义，它们永远不会出现在他们的产品中。

在分析RedGate SQL索引管理器（v1.1.9.1378 - 每个用户155美元）时，您可以看到网站源码使用一种非常简单的方法：使用第一个查询，我们获得用户表和视图的列表，然后第二个，我们返回所选数据库中所有索引的列表。

<span style="color:#000000"><span style="color:#0000ff">SELECT</span> objects.name <span style="color:#0000ff">AS</span> tableOrViewName
     , objects.object_id <span style="color:#0000ff">AS</span> tableOrViewId
     , schemas.name <span style="color:#0000ff">AS</span> schemaName
     , CAST(ISNULL(lobs.NumLobs, <span style="color:#000080">0</span>) <span style="color:#0000ff">AS</span> <span style="color:#0000ff">BIT</span>) <span style="color:#0000ff">AS</span> ContainsLobs
     , o.is_memory_optimized
<span style="color:#0000ff">FROM</span> sys.objects <span style="color:#0000ff">AS</span> objects
<span style="color:#0000ff">JOIN</span> sys.schemas <span style="color:#0000ff">AS</span> schemas <span style="color:#0000ff">ON</span> schemas.schema_id = objects.schema_id
<span style="color:#0000ff">LEFT</span> <span style="color:#0000ff">JOIN</span> (
    <span style="color:#0000ff">SELECT</span> object_id
         , COUNT(*) <span style="color:#0000ff">AS</span> NumLobs
    <span style="color:#0000ff">FROM</span> sys.columns <span style="color:#0000ff">WITH</span> (<span style="color:#0000ff">NOLOCK</span>)
    <span style="color:#0000ff">WHERE</span> system_type_id <span style="color:#0000ff">IN</span> (<span style="color:#000080">34</span>, <span style="color:#000080">35</span>, <span style="color:#000080">99</span>)
        <span style="color:#0000ff">OR</span> max_length = -1
    <span style="color:#0000ff">GROUP</span> <span style="color:#0000ff">BY</span> object_id
) <span style="color:#0000ff">AS</span> lobs <span style="color:#0000ff">ON</span> objects.object_id = lobs.object_id
<span style="color:#0000ff">LEFT</span> <span style="color:#0000ff">JOIN</span> sys.tables <span style="color:#0000ff">AS</span> o <span style="color:#0000ff">ON</span> o.object_id = objects.object_id
<span style="color:#0000ff">WHERE</span> objects.<span style="color:#0000ff">type</span> = <span style="color:#800080">'</span><span style="color:#800080">U'</span>
    <span style="color:#0000ff">OR</span> objects.<span style="color:#0000ff">type</span> = <span style="color:#800080">'</span><span style="color:#800080">V'</span>

<span style="color:#0000ff">SELECT</span> i.object_id <span style="color:#0000ff">AS</span> tableOrViewId
     , i.name <span style="color:#0000ff">AS</span> indexName
     , i.index_id <span style="color:#0000ff">AS</span> indexId
     , i.allow_page_locks <span style="color:#0000ff">AS</span> allowPageLocks
     , p.partition_number <span style="color:#0000ff">AS</span> partitionNumber
     , CAST((c.numPartitions - <span style="color:#000080">1</span>) <span style="color:#0000ff">AS</span> <span style="color:#0000ff">BIT</span>) <span style="color:#0000ff">AS</span> belongsToPartitionedIndex
<span style="color:#0000ff">FROM</span> sys.indexes <span style="color:#0000ff">AS</span> i
<span style="color:#0000ff">JOIN</span> sys.partitions <span style="color:#0000ff">AS</span> p <span style="color:#0000ff">ON</span> p.index_id = i.index_id
                        <span style="color:#0000ff">AND</span> p.object_id = i.object_id
<span style="color:#0000ff">JOIN</span> (
    <span style="color:#0000ff">SELECT</span> COUNT(*) <span style="color:#0000ff">AS</span> numPartitions
         , object_id
         , index_id
    <span style="color:#0000ff">FROM</span> sys.partitions
    <span style="color:#0000ff">GROUP</span> <span style="color:#0000ff">BY</span> object_id
           , index_id
) <span style="color:#0000ff">AS</span> c <span style="color:#0000ff">ON</span> c.index_id = i.index_id
      <span style="color:#0000ff">AND</span> c.object_id = i.object_id
<span style="color:#0000ff">WHERE</span> i.index_id > <span style="color:#000080">0</span> <em><span style="color:#008000">--</span></em><em><span style="color:#008000"> ignore heaps</span></em>
    <span style="color:#0000ff">AND</span> i.is_disabled = <span style="color:#000080">0</span>
    <span style="color:#0000ff">AND</span> i.is_hypothetical = <span style="color:#000080">0</span></span>

接下来，在while每个索引分区的循环中，发送请求以确定其大小和碎片级别。在扫描结束时，客户端上会显示重量小于进入阈值的索引。

<span style="color:#000000"><span style="color:#0000ff">EXEC</span> sp_executesql N<span style="color:#800080">'</span><span style="color:#800080">
SELECT index_id, avg_fragmentation_in_percent, page_count
FROM sys.dm_db_index_physical_stats(@databaseId, @objectId, @indexId, @partitionNr, NULL)'</span>
    , N<span style="color:#800080">'</span><span style="color:#800080">@databaseId int,@objectId int,@indexId int,@partitionNr int'</span>
    , <span style="color:#339999">@databaseId</span> = <span style="color:#000080">7</span>, <span style="color:#339999">@objectId</span> = <span style="color:#000080">2133582639</span>, <span style="color:#339999">@indexId</span> = <span style="color:#000080">1</span>, <span style="color:#339999">@partitionNr</span> = <span style="color:#000080">1</span>

<span style="color:#0000ff">EXEC</span> sp_executesql N<span style="color:#800080">'</span><span style="color:#800080">
SELECT index_id, avg_fragmentation_in_percent, page_count
FROM sys.dm_db_index_physical_stats(@databaseId, @objectId, @indexId, @partitionNr, NULL)'</span>
    , N<span style="color:#800080">'</span><span style="color:#800080">@databaseId int,@objectId int,@indexId int,@partitionNr int'</span>
    , <span style="color:#339999">@databaseId</span> = <span style="color:#000080">7</span>, <span style="color:#339999">@objectId</span> = <span style="color:#000080">2133582639</span>, <span style="color:#339999">@indexId</span> = <span style="color:#000080">2</span>, <span style="color:#339999">@partitionNr</span> = <span style="color:#000080">1</span>

<span style="color:#0000ff">EXEC</span> sp_executesql N<span style="color:#800080">'</span><span style="color:#800080">
SELECT index_id, avg_fragmentation_in_percent, page_count
FROM sys.dm_db_index_physical_stats(@databaseId, @objectId, @indexId, @partitionNr, NULL)'</span>
    , N<span style="color:#800080">'</span><span style="color:#800080">@databaseId int,@objectId int,@indexId int,@partitionNr int'</span>
    , <span style="color:#339999">@databaseId</span> = <span style="color:#000080">7</span>, <span style="color:#339999">@objectId</span> = <span style="color:#000080">2133582639</span>, <span style="color:#339999">@indexId</span> = <span style="color:#000080">3</span>, <span style="color:#339999">@partitionNr</span> = <span style="color:#000080">1</span></span>

在分析此应用程序的逻辑时，您可能会发现各种缺点。例如，在发送请求之前，不会检查当前分区是否包含任何行以从扫描中排除空分区。

但是问题在另一个方面表现得更加尖锐 - 对服务器的请求数量大约等于来自的总行数sys.partitions。鉴于真实数据库可以包含数万个分区，这种细微差别可能导致对服务器的大量类似请求。在数据库位于远程服务器上的情况下，由于每个请求的执行中的网络延迟增加，扫描时间将更长，即使是最简单的一个。

与RedGate不同，由DevArt开发的类似产品 - 用于SQL Server的dbForge索引管理器（v1.10.38 - 每用户99美元）在一个大型查询中接收信息，然后在客户端上显示所有内容：

<span style="color:#000000"><span style="color:#0000ff">SELECT</span> <span style="color:#339999">SCHEMA_NAME</span>(o.[schema_id]) <span style="color:#0000ff">AS</span> [schema_name]
     , o.name <span style="color:#0000ff">AS</span> parent_name
     , o.[<span style="color:#0000ff">type</span>] <span style="color:#0000ff">AS</span> parent_type
     , i.name
     , i.type_desc
     , s.avg_fragmentation_in_percent
     , s.page_count
     , p.partition_number
     , p.[rows]
     , ISNULL(lob.is_lob_legacy, <span style="color:#000080">0</span>) <span style="color:#0000ff">AS</span> is_lob_legacy
     , ISNULL(lob.is_lob, <span style="color:#000080">0</span>) <span style="color:#0000ff">AS</span> is_lob
     , <span style="color:#0000ff">CASE</span> <span style="color:#0000ff">WHEN</span> ds.[<span style="color:#0000ff">type</span>] = <span style="color:#800080">'</span><span style="color:#800080">PS'</span> <span style="color:#0000ff">THEN</span> <span style="color:#000080">1</span> <span style="color:#0000ff">ELSE</span> <span style="color:#000080">0</span> <span style="color:#0000ff">END</span> <span style="color:#0000ff">AS</span> is_partitioned
<span style="color:#0000ff">FROM</span> sys.dm_db_index_physical_stats(<span style="color:#339999">DB_ID</span>(), <span style="color:#0000ff">NULL</span>, <span style="color:#0000ff">NULL</span>, <span style="color:#0000ff">NULL</span>, <span style="color:#0000ff">NULL</span>) s
<span style="color:#0000ff">JOIN</span> sys.partitions p <span style="color:#0000ff">ON</span> s.[object_id] = p.[object_id]
                     <span style="color:#0000ff">AND</span> s.index_id = p.index_id
                     <span style="color:#0000ff">AND</span> s.partition_number = p.partition_number
<span style="color:#0000ff">JOIN</span> sys.indexes i <span style="color:#0000ff">ON</span> i.[object_id] = s.[object_id]
                  <span style="color:#0000ff">AND</span> i.index_id = s.index_id
<span style="color:#0000ff">LEFT</span> <span style="color:#0000ff">JOIN</span> (
    <span style="color:#0000ff">SELECT</span> c.[object_id]
         , index_id = ISNULL(i.index_id, <span style="color:#000080">1</span>)
         , is_lob_legacy = MAX(<span style="color:#0000ff">CASE</span> <span style="color:#0000ff">WHEN</span> c.system_type_id <span style="color:#0000ff">IN</span> (<span style="color:#000080">34</span>, <span style="color:#000080">35</span>, <span style="color:#000080">99</span>) <span style="color:#0000ff">THEN</span> <span style="color:#000080">1</span> <span style="color:#0000ff">END</span>)
         , is_lob = MAX(<span style="color:#0000ff">CASE</span> <span style="color:#0000ff">WHEN</span> c.max_length = -1 <span style="color:#0000ff">THEN</span> <span style="color:#000080">1</span> <span style="color:#0000ff">END</span>)
    <span style="color:#0000ff">FROM</span> sys.columns c
    <span style="color:#0000ff">LEFT</span> <span style="color:#0000ff">JOIN</span> sys.index_columns i <span style="color:#0000ff">ON</span> c.[object_id] = i.[object_id]
                                 <span style="color:#0000ff">AND</span> c.column_id = i.column_id
                                 <span style="color:#0000ff">AND</span> i.index_id > <span style="color:#000080">0</span>
    <span style="color:#0000ff">WHERE</span> c.system_type_id <span style="color:#0000ff">IN</span> (<span style="color:#000080">34</span>, <span style="color:#000080">35</span>, <span style="color:#000080">99</span>)
        <span style="color:#0000ff">OR</span> c.max_length = -1
    <span style="color:#0000ff">GROUP</span> <span style="color:#0000ff">BY</span> c.[object_id], i.index_id
) lob <span style="color:#0000ff">ON</span> lob.[object_id] = i.[object_id]
     <span style="color:#0000ff">AND</span> lob.index_id = i.index_id
<span style="color:#0000ff">JOIN</span> sys.objects o <span style="color:#0000ff">ON</span> o.[object_id] = i.[object_id]
<span style="color:#0000ff">JOIN</span> sys.data_spaces ds <span style="color:#0000ff">ON</span> i.data_space_id = ds.data_space_id
<span style="color:#0000ff">WHERE</span> i.[<span style="color:#0000ff">type</span>] <span style="color:#0000ff">IN</span> (<span style="color:#000080">1</span>, <span style="color:#000080">2</span>)
    <span style="color:#0000ff">AND</span> i.is_disabled = <span style="color:#000080">0</span>
    <span style="color:#0000ff">AND</span> i.is_hypothetical = <span style="color:#000080">0</span>
    <span style="color:#0000ff">AND</span> s.index_level = <span style="color:#000080">0</span>
    <span style="color:#0000ff">AND</span> s.alloc_unit_type_desc = <span style="color:#800080">'</span><span style="color:#800080">IN_ROW_DATA'</span>
    <span style="color:#0000ff">AND</span> o.[<span style="color:#0000ff">type</span>] <span style="color:#0000ff">IN</span> (<span style="color:#800080">'</span><span style="color:#800080">U'</span>, <span style="color:#800080">'</span><span style="color:#800080">V'</span>)</span>

消除了竞争产品中类似请求的面纱的主要问题，但是这种实现的缺点是没有额外的参数传递给sys.dm_db_index_physical_stats可以限制对明显不必要的索引的扫描的函数。实际上，这会导致获取系统中所有索引的信息以及扫描阶段不必要的磁盘负载。

值得一提的是，从中获取的数据sys.dm_db_index_physical_stats并未永久缓存在缓冲池中，因此在获取有关索引碎片的信息时最小化物理读取是我的应用程序开发过程中的优先任务之一。

经过多次实验，我设法将扫描分为两部分，将两种方法结合起来。最初，一个大型请求通过过滤那些未包含在过滤范围中的分区来预先确定分区的大小：

<span style="color:#000000"><span style="color:#0000ff">INSERT</span> <span style="color:#0000ff">INTO</span> #AllocationUnits (ContainerID, ReservedPages, UsedPages)
<span style="color:#0000ff">SELECT</span> [container_id]
     , SUM([total_pages])
     , SUM([used_pages])
<span style="color:#0000ff">FROM</span> sys.allocation_units <span style="color:#0000ff">WITH</span>(<span style="color:#0000ff">NOLOCK</span>)
<span style="color:#0000ff">GROUP</span> <span style="color:#0000ff">BY</span> [container_id]
<span style="color:#0000ff">HAVING</span> SUM([total_pages]) <span style="color:#0000ff">BETWEEN</span> <span style="color:#339999">@MinIndexSize</span> <span style="color:#0000ff">AND</span> <span style="color:#339999">@MaxIndexSize</span></span>

接下来，我们只获取包含数据的分区，以避免从空索引中进行不必要的读取。

<span style="color:#000000"><span style="color:#0000ff">SELECT</span> [object_id]
     , [index_id]
     , [partition_id]
     , [partition_number]
     , [rows]
     , [data_compression]
<span style="color:#0000ff">INTO</span> #Partitions
<span style="color:#0000ff">FROM</span> sys.partitions <span style="color:#0000ff">WITH</span>(<span style="color:#0000ff">NOLOCK</span>)
<span style="color:#0000ff">WHERE</span> [object_id] > <span style="color:#000080">255</span>
    <span style="color:#0000ff">AND</span> [rows] > <span style="color:#000080">0</span>
    <span style="color:#0000ff">AND</span> [object_id] <span style="color:#0000ff">NOT</span> <span style="color:#0000ff">IN</span> (<span style="color:#0000ff">SELECT</span> * <span style="color:#0000ff">FROM</span> #ExcludeList)</span>

根据设置，仅获取用户想要分析的索引类型（支持堆，群集/非群集索引和列存储）。

<span style="color:#000000"><span style="color:#0000ff">INSERT</span> <span style="color:#0000ff">INTO</span> #Indexes
<span style="color:#0000ff">SELECT</span> ObjectID         = i.[object_id]
     , IndexID          = i.index_id
     , IndexName        = i.[name]
     , PagesCount       = a.ReservedPages
     , UnusedPagesCount = a.ReservedPages - a.UsedPages
     , PartitionNumber  = p.[partition_number]
     , RowsCount        = ISNULL(p.[rows], <span style="color:#000080">0</span>)
     , IndexType        = i.[<span style="color:#0000ff">type</span>]
     , IsAllowPageLocks = i.[allow_page_locks]
     , DataSpaceID      = i.[data_space_id]
     , DataCompression  = p.[data_compression]
     , IsUnique         = i.[is_unique]
     , IsPK             = i.[is_primary_key]
     , FillFactorValue  = i.[fill_factor]
     , IsFiltered       = i.[has_filter]
<span style="color:#0000ff">FROM</span> #AllocationUnits a
<span style="color:#0000ff">JOIN</span> #Partitions p <span style="color:#0000ff">ON</span> a.ContainerID = p.[partition_id]
<span style="color:#0000ff">JOIN</span> sys.indexes i <span style="color:#0000ff">WITH</span>(<span style="color:#0000ff">NOLOCK</span>) <span style="color:#0000ff">ON</span> i.[object_id] = p.[object_id]

                               <span style="color:#0000ff">AND</span> p.[index_id] = i.[index_id]
<span style="color:#0000ff">WHERE</span> i.[<span style="color:#0000ff">type</span>] <span style="color:#0000ff">IN</span> (<span style="color:#000080">0</span>, <span style="color:#000080">1</span>, <span style="color:#000080">2</span>, <span style="color:#000080">5</span>, <span style="color:#000080">6</span>)
    <span style="color:#0000ff">AND</span> i.[object_id] > <span style="color:#000080">255</span></span>

之后，我们添加了一些魔法，并且......对于所有小的索引，我们通过重复调用sys.dm_db_index_physical_stats具有所有参数的完整指示的函数来确定碎片的级别。

<span style="color:#000000"><span style="color:#0000ff">INSERT</span> <span style="color:#0000ff">INTO</span> #Fragmentation (ObjectID, IndexID, PartitionNumber, Fragmentation)
<span style="color:#0000ff">SELECT</span> i.ObjectID
     , i.IndexID
     , i.PartitionNumber
     , r.[avg_fragmentation_in_percent]
<span style="color:#0000ff">FROM</span> #Indexes i
<span style="color:#0000ff">CROSS</span> <span style="color:#0000ff">APPLY</span> sys.dm_db_index_physical_stats_
    (<span style="color:#339999">@DBID</span>, i.ObjectID, i.IndexID, i.PartitionNumber, <span style="color:#800080">'</span><span style="color:#800080">LIMITED'</span>) r
<span style="color:#0000ff">WHERE</span> i.PagesCount <= <span style="color:#339999">@PreDescribeSize</span>
    <span style="color:#0000ff">AND</span> r.[index_level] = <span style="color:#000080">0</span>
    <span style="color:#0000ff">AND</span> r.[alloc_unit_type_desc] = <span style="color:#800080">'</span><span style="color:#800080">IN_ROW_DATA'</span>
    <span style="color:#0000ff">AND</span> i.IndexType <span style="color:#0000ff">IN</span> (<span style="color:#000080">0</span>, <span style="color:#000080">1</span>, <span style="color:#000080">2</span>)</span>

接下来，我们通过过滤掉额外的数据将所有可能的信息返回给客户端：

<span style="color:#000000"><span style="color:#0000ff">SELECT</span> i.ObjectID
     , i.IndexID
     , i.IndexName
     , ObjectName       = o.[name]
     , SchemaName       = s.[name]
     , i.PagesCount
     , i.UnusedPagesCount
     , i.PartitionNumber
     , i.RowsCount
     , i.IndexType
     , i.IsAllowPageLocks
     , u.TotalWrites
     , u.TotalReads
     , u.TotalSeeks
     , u.TotalScans
     , u.TotalLookups
     , u.LastUsage
     , i.DataCompression
     , f.Fragmentation
     , IndexStats       = <span style="color:#339999">STATS_DATE</span>(i.ObjectID, i.IndexID)
     , IsLobLegacy      = ISNULL(lob.IsLobLegacy, <span style="color:#000080">0</span>)
     , IsLob            = ISNULL(lob.IsLob, <span style="color:#000080">0</span>)
     , IsSparse         = CAST(<span style="color:#0000ff">CASE</span> <span style="color:#0000ff">WHEN</span> p.ObjectID <span style="color:#0000ff">IS</span> <span style="color:#0000ff">NULL</span> <span style="color:#0000ff">THEN</span> <span style="color:#000080">0</span> <span style="color:#0000ff">ELSE</span> <span style="color:#000080">1</span> <span style="color:#0000ff">END</span> <span style="color:#0000ff">AS</span> <span style="color:#0000ff">BIT</span>)
     , IsPartitioned    = CAST(<span style="color:#0000ff">CASE</span> <span style="color:#0000ff">WHEN</span> dds.[data_space_id] _
                          <span style="color:#0000ff">IS</span> <span style="color:#0000ff">NOT</span> <span style="color:#0000ff">NULL</span> <span style="color:#0000ff">THEN</span> <span style="color:#000080">1</span> <span style="color:#0000ff">ELSE</span> <span style="color:#000080">0</span> <span style="color:#0000ff">END</span> <span style="color:#0000ff">AS</span> <span style="color:#0000ff">BIT</span>)
     , FileGroupName    = fg.[name]
     , i.IsUnique
     , i.IsPK
     , i.FillFactorValue
     , i.IsFiltered
     , a.IndexColumns
     , a.IncludedColumns
<span style="color:#0000ff">FROM</span> #Indexes i
<span style="color:#0000ff">JOIN</span> sys.objects o <span style="color:#0000ff">WITH</span>(<span style="color:#0000ff">NOLOCK</span>) <span style="color:#0000ff">ON</span> o.[object_id] = i.ObjectID
<span style="color:#0000ff">JOIN</span> sys.schemas s <span style="color:#0000ff">WITH</span>(<span style="color:#0000ff">NOLOCK</span>) <span style="color:#0000ff">ON</span> s.[schema_id] = o.[schema_id]
<span style="color:#0000ff">LEFT</span> <span style="color:#0000ff">JOIN</span> #AggColumns a <span style="color:#0000ff">ON</span> a.ObjectID = i.ObjectID
                       <span style="color:#0000ff">AND</span> a.IndexID = i.IndexID
<span style="color:#0000ff">LEFT</span> <span style="color:#0000ff">JOIN</span> #Sparse p <span style="color:#0000ff">ON</span> p.ObjectID = i.ObjectID
<span style="color:#0000ff">LEFT</span> <span style="color:#0000ff">JOIN</span> #Fragmentation f <span style="color:#0000ff">ON</span> f.ObjectID = i.ObjectID
                          <span style="color:#0000ff">AND</span> f.IndexID = i.IndexID
                          <span style="color:#0000ff">AND</span> f.PartitionNumber = i.PartitionNumber
<span style="color:#0000ff">LEFT</span> <span style="color:#0000ff">JOIN</span> (
    <span style="color:#0000ff">SELECT</span> ObjectID      = [object_id]
         , IndexID       = [index_id]
         , TotalWrites   = <span style="color:#0000ff">NULLIF</span>([user_updates], <span style="color:#000080">0</span>)
         , TotalReads    = <span style="color:#0000ff">NULLIF</span>([user_seeks] + [user_scans] + [user_lookups], <span style="color:#000080">0</span>)
         , TotalSeeks    = <span style="color:#0000ff">NULLIF</span>([user_seeks], <span style="color:#000080">0</span>)
         , TotalScans    = <span style="color:#0000ff">NULLIF</span>([user_scans], <span style="color:#000080">0</span>)
         , TotalLookups  = <span style="color:#0000ff">NULLIF</span>([user_lookups], <span style="color:#000080">0</span>)
         , LastUsage     = (
                                <span style="color:#0000ff">SELECT</span> MAX(dt)
                                <span style="color:#0000ff">FROM</span> (
                                    <span style="color:#0000ff">VALUES</span> ([last_user_seek])
                                         , ([last_user_scan])
                                         , ([last_user_lookup])
                                         , ([last_user_update])
                                ) t(dt)
                           )
    <span style="color:#0000ff">FROM</span> sys.dm_db_index_usage_stats <span style="color:#0000ff">WITH</span>(<span style="color:#0000ff">NOLOCK</span>)
    <span style="color:#0000ff">WHERE</span> [database_id] = <span style="color:#339999">@DBID</span>
) u <span style="color:#0000ff">ON</span> i.ObjectID = u.ObjectID
   <span style="color:#0000ff">AND</span> i.IndexID = u.IndexID
<span style="color:#0000ff">LEFT</span> <span style="color:#0000ff">JOIN</span> #Lob lob <span style="color:#0000ff">ON</span> lob.ObjectID = i.ObjectID
                  <span style="color:#0000ff">AND</span> lob.IndexID = i.IndexID
<span style="color:#0000ff">LEFT</span> <span style="color:#0000ff">JOIN</span> sys.destination_data_spaces dds <span style="color:#0000ff">WITH</span>(<span style="color:#0000ff">NOLOCK</span>) _
            <span style="color:#0000ff">ON</span> i.DataSpaceID = dds.[partition_scheme_id]
            <span style="color:#0000ff">AND</span> i.PartitionNumber = dds.[destination_id]
<span style="color:#0000ff">JOIN</span> sys.filegroups fg <span style="color:#0000ff">WITH</span>(<span style="color:#0000ff">NOLOCK</span>) _
            <span style="color:#0000ff">ON</span> ISNULL(dds.[data_space_id], i.DataSpaceID) = fg.[data_space_id]
<span style="color:#0000ff">WHERE</span> o.[<span style="color:#0000ff">type</span>] <span style="color:#0000ff">IN</span> (<span style="color:#800080">'</span><span style="color:#800080">V'</span>, <span style="color:#800080">'</span><span style="color:#800080">U'</span>)
    <span style="color:#0000ff">AND</span> (
            f.Fragmentation >= <span style="color:#339999">@Fragmentation</span>
        <span style="color:#0000ff">OR</span>
            i.PagesCount > <span style="color:#339999">@PreDescribeSize</span>
        <span style="color:#0000ff">OR</span>
            i.IndexType <span style="color:#0000ff">IN</span> (<span style="color:#000080">5</span>, <span style="color:#000080">6</span>)
    )</span>

之后，点请求确定大型索引的碎片级别。

<span style="color:#000000"><span style="color:#0000ff">EXEC</span> sp_executesql N<span style="color:#800080">'</span><span style="color:#800080">
DECLARE @DBID INT = DB_ID()
SELECT [avg_fragmentation_in_percent]
FROM sys.dm_db_index_physical_stats(@DBID, @ObjectID, @IndexID, @PartitionNumber, ''LIMITED'')
WHERE [index_level] = 0
    AND [alloc_unit_type_desc] = ''IN_ROW_DATA'''</span>
    , N<span style="color:#800080">'</span><span style="color:#800080">@ObjectID int,@IndexID int,@PartitionNumber int'</span>
    , <span style="color:#339999">@ObjectId</span> = <span style="color:#000080">1044198770</span>, <span style="color:#339999">@IndexId</span> = <span style="color:#000080">1</span>, <span style="color:#339999">@PartitionNumber</span> = <span style="color:#000080">1</span>

<span style="color:#0000ff">EXEC</span> sp_executesql N<span style="color:#800080">'</span><span style="color:#800080">
DECLARE @DBID INT = DB_ID()
SELECT [avg_fragmentation_in_percent]
FROM sys.dm_db_index_physical_stats(@DBID, @ObjectID, @IndexID, @PartitionNumber, ''LIMITED'')
WHERE [index_level] = 0
    AND [alloc_unit_type_desc] = ''IN_ROW_DATA'''</span>
    , N<span style="color:#800080">'</span><span style="color:#800080">@ObjectID int,@IndexID int,@PartitionNumber int'</span>
    , <span style="color:#339999">@ObjectId</span> = <span style="color:#000080">1552724584</span>, <span style="color:#339999">@IndexId</span> = <span style="color:#000080">0</span>, <span style="color:#339999">@PartitionNumber</span> = <span style="color:#000080">1</span></span>

由于这种方法，在生成请求时，我设法解决了竞争对手应用程序中遇到的扫描性能问题。这可能是它的终结，但在开发过程中，逐渐出现了各种新的想法，这使得扩大我的产品的应用范围成为可能。

最初，实现了对使用的支持WAIT_AT_LOW_PRIORITY，然后可以使用DATA_COMPRESSION和FILL_FACTOR重建索引。

 

该应用程序已被“撒上”以前未计划的功能，如维护列存储：

<span style="color:#000000"><span style="color:#0000ff">SELECT</span> *
<span style="color:#0000ff">FROM</span> (
    <span style="color:#0000ff">SELECT</span> IndexID          = [index_id]
         , PartitionNumber  = [partition_number]
         , PagesCount       = SUM([size_in_bytes]) / <span style="color:#000080">8192</span>
         , UnusedPagesCount = ISNULL(SUM(<span style="color:#0000ff">CASE</span> <span style="color:#0000ff">WHEN</span> [state] = <span style="color:#000080">1</span> _
                              <span style="color:#0000ff">THEN</span> [size_in_bytes] <span style="color:#0000ff">END</span>), <span style="color:#000080">0</span>) / <span style="color:#000080">8192</span>
         , Fragmentation    = CAST(ISNULL(SUM(<span style="color:#0000ff">CASE</span> <span style="color:#0000ff">WHEN</span> [state] = <span style="color:#000080">1</span> _
                              <span style="color:#0000ff">THEN</span> [size_in_bytes] <span style="color:#0000ff">END</span>), <span style="color:#000080">0</span>)
                            * <span style="color:#000080">100</span>. / SUM([size_in_bytes]) <span style="color:#0000ff">AS</span> <span style="color:#0000ff">FLOAT</span>)
    <span style="color:#0000ff">FROM</span> sys.fn_column_store_row_groups(<span style="color:#339999">@ObjectID</span>)
    <span style="color:#0000ff">GROUP</span> <span style="color:#0000ff">BY</span> [index_id]
           , [partition_number]
) t
<span style="color:#0000ff">WHERE</span> Fragmentation >= <span style="color:#339999">@Fragmentation</span>
    <span style="color:#0000ff">AND</span> PagesCount <span style="color:#0000ff">BETWEEN</span> <span style="color:#339999">@MinIndexSize</span> <span style="color:#0000ff">AND</span> <span style="color:#339999">@MaxIndexSize</span></span>

或者根据以下信息创建非聚簇索引的能力dm_db_missing_index：

<span style="color:#000000"><span style="color:#0000ff">SELECT</span> ObjectID     = d.[object_id]
     , UserImpact   = gs.[avg_user_impact]
     , TotalReads   = gs.[user_seeks] + gs.[user_scans]
     , TotalSeeks   = gs.[user_seeks]
     , TotalScans   = gs.[user_scans]
     , LastUsage    = ISNULL(gs.[last_user_scan], gs.[last_user_seek])
     , IndexColumns =
                <span style="color:#0000ff">CASE</span>
                    <span style="color:#0000ff">WHEN</span> d.[equality_columns] <span style="color:#0000ff">IS</span> <span style="color:#0000ff">NOT</span> <span style="color:#0000ff">NULL</span> 
                                _AND d.[inequality_columns] <span style="color:#0000ff">IS</span> <span style="color:#0000ff">NOT</span> <span style="color:#0000ff">NULL</span>
                        <span style="color:#0000ff">THEN</span> d.[equality_columns] + <span style="color:#800080">'</span><span style="color:#800080">, '</span> + d.[inequality_columns]
                    <span style="color:#0000ff">WHEN</span> d.[equality_columns] <span style="color:#0000ff">IS</span> <span style="color:#0000ff">NOT</span> <span style="color:#0000ff">NULL</span> <span style="color:#0000ff">AND</span> d.[inequality_columns] <span style="color:#0000ff">IS</span> <span style="color:#0000ff">NULL</span>
                        <span style="color:#0000ff">THEN</span> d.[equality_columns]
                    <span style="color:#0000ff">ELSE</span> d.[inequality_columns]
                <span style="color:#0000ff">END</span>
     , IncludedColumns = d.[included_columns]
<span style="color:#0000ff">FROM</span> sys.dm_db_missing_index_groups g <span style="color:#0000ff">WITH</span>(<span style="color:#0000ff">NOLOCK</span>)
<span style="color:#0000ff">JOIN</span> sys.dm_db_missing_index_group_stats gs <span style="color:#0000ff">WITH</span>(<span style="color:#0000ff">NOLOCK</span>) _
                       <span style="color:#0000ff">ON</span> gs.[group_handle] = g.[index_group_handle]
<span style="color:#0000ff">JOIN</span> sys.dm_db_missing_index_details d <span style="color:#0000ff">WITH</span>(<span style="color:#0000ff">NOLOCK</span>) _
                       <span style="color:#0000ff">ON</span> g.[index_handle] = d.[index_handle]
<span style="color:#0000ff">WHERE</span> d.[database_id] = <span style="color:#339999">DB_ID</span>()</span>

结果和计划

关键的是，开发计划并没有就此结束，因为我渴望进一步开发这个应用程序。下一步是添加查找重复（已完成）或未使用索引的功能，以及实现对在SQL Server中维护统计信息的完全支持。

现在市场上有很多付费解决方案。我想相信，由于自由定位，更优化的查询以及各种有用的gismos的可用性，这个产品肯定会在日常任务中变得有用。