Sql-server – TSQL – SQL Server 2008 Maintenance Plan – anything else

maintenancesql-server-2008sql-server-2008-r2t-sql

I have a number of SQL 2008 R2 DBs that haven't had any maintenance plans run on them since birth, as my ISP's server provides a SQL version that doesn't have the Maintenance Plan option.

I've trawled through the numerous pointers, options, etc, but I just want to run something clean and simple on them. Having trawled, I've come up with the following simple TSQL script:

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;

That's it. None of the SQL DBs are bigger than 300MB each at the moment (probably a lot of bloatware in there already) and each one is backed up by another process so have I covered the bases? Is there anything I've missed?

UPDATE: on this topic I've run the above and for some reason the frag % changes on the tables each time I do it??? It goes up and down erratically per table/index – is this normal???

Regards

Tom

Best Answer

First have a look on this brilliant solution by Ola Hallengren to build your own maintenance plan. It has been well recognised and trusted by many SQL Server pros.

If you are just after "one off" on dev/demo solution or want to get some insight please try the below. You will be interested in "Hint" column on this occasion:

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
Related Question