7

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?

András Váczi
  • 31,778
  • 13
  • 102
  • 151
Andy Felton
  • 71
  • 1
  • 1
  • 2

2 Answers2

5

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;
Erik Reasonable Rates Darling
  • 45,549
  • 14
  • 145
  • 532
Seth Delconte
  • 153
  • 1
  • 7
1

AWS Improving database optimization doc article provides information about Rebuild Indexes and Update Statistics. In Update Statistics they mention one of the solutions:

For database optimization, we recommend using an index and statistics maintenance script. For an example, see the SQL Server index and statistics maintenance script provided on the SQL Server Maintenance Solution website.

SQL Server Maintenance Solution is a plain SQL that can be run on your server. It also will create the necessary jobs: Index Maintain, Update Statistics, etc. You only need to schedule those you need.

elshev
  • 111
  • 3