索引碎片監控：我可以讓這個查詢更快嗎？

我有一個查詢，它使用 PowerShell 腳本對我的兩台伺服器上的每個數據庫執行以啟動腳本並將結果放入 CSV，因此需要有臨時表。

有沒有辦法可以限制我已經擁有的東西？我嘗試將我的WHERE子句添加到我正在迭代的臨時表中，因為WHERE子句sys.dm_db_index_physical_stats實際上並不用作謂詞，而只是根據 MS 文件在事後過濾。

DECLARE @object_id int
DECLARE @index_id int
DECLARE @partition_number int
DECLARE @DatabaseID int
SET @DatabaseID = DB_ID()

SELECT [object_id], [index_id], [partition_number], [rows] INTO #tempTest FROM (
SELECT indexes.[object_id], indexes.[index_id], partitions.[partition_number], partitions.[rows] FROM sys.tables AS tables
INNER JOIN sys.indexes AS indexes on tables.[object_id] = indexes.[object_id]
INNER JOIN sys.schemas AS schemas on tables.[schema_id] = schemas.[schema_id]
INNER JOIN sys.partitions AS partitions ON indexes.[object_id] = partitions.[object_id] AND indexes.[index_id] = partitions.[index_id]
WHERE schemas.[name] <> 'Audit'
AND indexes.[type] IN(1,2,3,4)
AND indexes.is_disabled = 0
AND indexes.is_hypothetical = 0 
AND partitions.[rows] > 1000000) AS x

CREATE TABLE #tempTest2 (NK varchar(35), ServerName varchar(35), TimeStamp datetime, DatabaseName varchar(50), SchemaName varchar(50), ObjectName varchar (250), IndexName varchar (250),
ObjectType varchar(30), IndexType varchar (30), PartitionNumber int, PageCount bigint, avg_fragmentation_in_percent float)


DECLARE db_cursor CURSOR FOR

SELECT [object_id], [index_id], [partition_number] FROM #tempTest

OPEN db_cursor

FETCH NEXT FROM db_cursor INTO @object_id, @index_id, @partition_number

WHILE @@FETCH_STATUS = 0
BEGIN

INSERT INTO #tempTest2 (NK, ServerName, TimeStamp, DatabaseName, SchemaName, ObjectName, IndexName, ObjectType, IndexType, PartitionNumber, [PageCount], avg_fragmentation_in_percent)

      SELECT CONCAT(objects.[object_id], indexes.[index_id]) AS NK,
      @@SERVERNAME AS ServerName,
      GETDATE() AS TimeStamp,
      DB_NAME(@DatabaseID) AS DatabaseName,
      schemas.[name] AS SchemaName,
      objects.[name] AS ObjectName,
      indexes.[name] AS IndexName,
      objects.type_desc AS ObjectType,
      indexes.type_desc AS IndexType,
      dm_db_index_physical_stats.partition_number AS PartitionNumber,
      dm_db_index_physical_stats.page_count AS [PageCount],
      dm_db_index_physical_stats.avg_fragmentation_in_percent AS AvgFragmentationInPercent
FROM sys.dm_db_index_physical_stats (DB_ID(), @object_id, @index_id, @partition_number, 'LIMITED') dm_db_index_physical_stats
INNER JOIN sys.indexes indexes ON dm_db_index_physical_stats.[object_id] = indexes.[object_id] AND dm_db_index_physical_stats.index_id = indexes.index_id
INNER JOIN sys.objects objects ON indexes.[object_id] = objects.[object_id]
INNER JOIN sys.schemas schemas ON objects.[schema_id] = schemas.[schema_id]
WHERE objects.[type] IN('U','V')
AND objects.is_ms_shipped = 0
--AND indexes.[type] IN(1,2,3,4)
--AND indexes.is_disabled = 0
--AND indexes.is_hypothetical = 0
AND dm_db_index_physical_stats.alloc_unit_type_desc = 'IN_ROW_DATA'
AND dm_db_index_physical_stats.index_level = 0
AND dm_db_index_physical_stats.page_count >= 1000
--AND schemas.name <> 'Audit'
   FETCH NEXT FROM db_cursor INTO @object_id, @index_id, @partition_number
END

CLOSE db_cursor
DEALLOCATE db_cursor

SELECT * FROM #tempTest2

我想我將嘗試將其WHERE objects.[type] IN('U','V') AND objects.is_ms_shipped = 0移至上面的臨時表並從下面的部分中將其註釋掉。

我已經讀過：

• SQL Server 索引維護——你做錯了作者 Sean Gallardy
• 別再擔心 SQL Server 碎片了，作者 Brent Ozar
• 重建該索引將花費或節省多少成本？通過傑夫摩登

我建議減少循環中的連接數。您已經在第一個查詢中使用 sys.indexes 和 sys.schemas 來建構臨時表。最好只獲取您需要的任何其他欄位並將它們也放入您的臨時表中。

您希望使您在游標中循環的查詢盡可能高效。您甚至可能還想嘗試從 sys.objects 那裡獲取所需的數據。

說到游標，您也應該考慮使用 fast_forward 游標。它的佔地面積更小，效率更高。

您是否有時間一舉從數據庫中獲取所有內容

FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, ‘LIMITED’)

並將所有內容儲存到表中？可能比在游標內一次又一次地執行查詢要好。

引用自：https://dba.stackexchange.com/questions/246951