snippetsqlMinor
How to maintain Index and Statistics on AWS RDS?
Viewed 0 times
maintainstatisticsrdsawshowandindex
Problem
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?
Please advice on how to setup SQL Server Agent considering the restrictions of RDS?
Solution
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;Code Snippets
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;Context
StackExchange Database Administrators Q#197397, answer score: 4
Revisions (0)
No revisions yet.