TSQL - SQL Server 2008 維護計劃 - 還有什麼？

我有許多 SQL 2008 R2 數據庫，它們自出生以來就沒有執行過任何維護計劃，因為我的 ISP 的伺服器提供了一個沒有維護計劃選項的 SQL 版本。

我已經瀏覽了許多指針、選項等，但我只想在它們上執行一些乾淨簡單的東西。拖網後，我想出了以下簡單的 TSQL 腳本：

USE dbname

ALTER INDEX ALL ON tablename1 REBUILD WITH (FILLFACTOR = 80);
ALTER INDEX ALL ON tablename2 REBUILD WITH (FILLFACTOR = 80);
etc etc for each tablename in a db

EXEC sp_updatestats;

而已。目前沒有一個 SQL DB 大於 300MB（可能已經有很多過時的軟體），並且每個都由另一個程序備份，所以我覆蓋了基礎嗎？有什麼我錯過的嗎？

更新：關於這個主題，我已經執行了上面的內容，並且由於某種原因，每次我這樣做時表格上的 frag % 都會發生變化？？？每個表/索引不規則地上下波動 - 這是正常的嗎？

問候

湯姆

首先看看Ola Hallengren這個出色的解決方案，以製定您自己的維護計劃。它得到了許多 SQL Server 專業人士的認可和信任。

如果您剛剛在開發/展示解決方案上“一次性”完成或想要獲得一些見解，請嘗試以下操作。這次你會對“提示”欄目感興趣：

DECLARE
@Minimal_Page_Count int = 1000, 
@Max_allowed_Fragmentation decimal(5,2) = 5,
@Hours_Since_Last_Stats_Update int = 24
---------------------------------------------------------------------------------------------------

DECLARE @WorkingHours float = (SELECT DATEDIFF(mi, [sqlserver_start_time], SYSDATETIME()) FROM sys.dm_os_sys_info)/60.0

;WITH [index_usage_stats] AS
(
SELECT
[object_id],
[index_id],
[Reads] = [user_seeks]  + [user_scans] + [user_lookups] + [user_updates],
[Writes] = [user_updates],
[Last_Read] = [last_user_seek],
[Last_Write] = [last_user_update]
FROM sys.dm_db_index_usage_stats
WHERE [database_id] = DB_ID()
UNION ALL
SELECT [object_id], [index_id], NULL, NULL, [last_user_scan], NULL
FROM sys.dm_db_index_usage_stats
WHERE [database_id] = DB_ID()
UNION ALL
SELECT [object_id], [index_id], NULL, NULL, [last_user_lookup], NULL
FROM sys.dm_db_index_usage_stats
WHERE [database_id] = DB_ID()
), [ius] AS
(
SELECT
[object_id],
[index_id],
[Reads] = MAX([Reads]),
[Writes] = MAX([Writes]),
[Last_Read] =MAX([Last_Read]),
[Last_Write] = MAX([Last_Write])
FROM [index_usage_stats]
GROUP BY [object_id], [index_id]
), [Table_Stats] AS
(
SELECT 
i.[object_id],
[Table_Row_QTY] = SUM(CASE WHEN i.[index_id] = 1 THEN p.[rows] ELSE 0 END),
[Total_Table_Size_(MB)] = CASE WHEN SUM(a.total_pages) > 0 THEN CAST(SUM(a.total_pages) / 128.0 as decimal(18,1)) ELSE 0 END,
[Not_Used_Table_Size_(MB)] = CAST(CASE WHEN SUM(a.total_pages) > 0 THEN SUM(a.total_pages) / 128.0 ELSE 0 END - CASE WHEN SUM(a.used_pages) > 0 THEN SUM(a.used_pages) / 128.0 ELSE 0 END as decimal(18,1))
FROM sys.indexes            i
JOIN sys.partitions         p ON i.[object_id] = p.[object_id] AND i.[index_id] = p.[index_id]
JOIN sys.allocation_units   a ON p.[partition_id] = a.[container_id]
GROUP BY i.[object_id]
), [ALL] AS
(
SELECT
[DB_Name]   = db_name(),
[Schema] = OBJECT_SCHEMA_NAME(ips.[object_id]), 
[Object_Name] = object_name(ips.[object_id]),
[Index_Name] = ix.[name],
[Allocation_Unit_Type] = ips.[alloc_unit_type_desc],
[Index_Type] = ips.[index_type_desc],
[Index_Size_(MB)] = CASE WHEN ips.[page_count]>0 THEN CAST(ips.[page_count] / 128.0 as decimal(18,1)) ELSE 0 END,
ts.[Total_Table_Size_(MB)],
ts.[Not_Used_Table_Size_(MB)],
ts.[Table_Row_QTY],
[AVG_Fragmentation(%)] = CAST(ips.[avg_fragmentation_in_percent] as decimal(5,2)),
[Is_Unique] = CASE ix.[is_unique] WHEN 1 THEN 'Yes' WHEN 0 THEN 'No' END,
[Is_PK] = CASE ix.[is_primary_key] WHEN 1 THEN 'Yes' WHEN 0 THEN 'No' END,
[Is_UC] = CASE ix.[is_unique_constraint] WHEN 1 THEN 'Yes' WHEN 0 THEN 'No' END,
[Has_Filter] = CASE ix.[has_filter] WHEN 1 THEN 'Yes' WHEN 0 THEN 'No' END,
[Is_Disabled] = CASE ix.[is_disabled] WHEN 1 THEN 'Yes' WHEN 0 THEN 'No' END,
[Fill_Factor] = ix.[fill_factor],
[ius].[Reads],
[AVG_Reads_per_hour] = CAST([ius].[Reads] / @WorkingHours as decimal(16,1)),
[ius].[Last_Read],
[ius].[Writes],
[AVG_Writes_per_hour] = CAST([ius].[Writes] / @WorkingHours as decimal(16,1)),
[ius].[Last_Write],
[Reads_vs_Writes] = CASE WHEN [ius].[Reads] > 0 AND [ius].[Writes] > 0 THEN CAST(CAST([ius].[Reads] as decimal(18,5)) / ([ius].[Reads] + [ius].[Writes]) as decimal(6,3))END,
[Last_Stats_Update] = CAST(STATS_DATE(ips.[object_id], ips.[index_id]) as smalldatetime)
FROM sys.dm_db_index_physical_stats (db_id(), NULL, NULL, NULL, NULL) ips
JOIN sys.indexes ix ON  ips.[object_id] = ix.[object_id] 
                   AND ips.[index_id]  = ix.[index_id]
LEFT JOIN [ius] ON  [ius].[object_id]   = ips.[object_id]
                   AND [ius].[index_id]    = ips.[index_id]
LEFT JOIN   [Table_Stats] ts ON ts.[object_id] = ips.[object_id]
WHERE   ips.[index_id] > 0
AND    ips.[page_count] >= @Minimal_Page_Count
)
SELECT *,
[Hint] = CASE WHEN [AVG_Fragmentation(%)] >= @Max_allowed_Fragmentation THEN N'ALTER INDEX [' + [Index_Name] + N'] ON [' + [Object_Name] + N'] REBUILD WITH (FILLFACTOR = ' + CAST((CASE [Fill_Factor] WHEN 0 THEN 100 ELSE [Fill_Factor]END) as varchar(3)) + N');' ELSE N'' END + nchar(13) + CASE WHEN DATEDIFF(hh,[Last_Stats_Update], SYSDATETIME()) >= @Hours_Since_Last_Stats_Update THEN N'UPDATE STATISTICS [' + [Object_Name] + N'] [' + [Index_Name] + N'] WITH FULLSCAN;' ELSE N'' END
FROM [ALL]
ORDER BY [AVG_Reads_per_hour] DESC

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