Sql-server – How to maintain Index and Statistics on AWS RDS

amazon-rdsola-hallengrensql server

In the process of setting up the SQL Server Index and Statistics Maintenance code on our AWS RDS instance, I've managed to set up the scripts and can run it from SSMS.

Please advice on how to setup SQL Server Agent considering the restrictions of RDS?

Best Answer

I am running SQL Server RDS maintenance jobs (DBCC CHECKDB, index reorgs/rebuilds, statistics updates) without a problem. The only limitation I had to work around was that I could not run sp_updatestats, so instead had to generate an 'UPDATE STATISTICS' script for each table like this:

DECLARE @sql NVARCHAR(max) = '';
SELECT @sql = @sql+
'UPDATE STATISTICS ' + '[' + table_name + ']' + ' WITH FULLSCAN;
PRINT ''UPDATING STATISTICS ON ' + table_name +'...'';
' 
FROM information_schema.tables
where TABLE_TYPE = 'BASE TABLE';

EXEC sp_executesql @statement=@sql;
Related Question