HiveBrain v1.2.0
Get Started
← Back to all entries
snippetsqlMinor

How to maintain Index and Statistics on AWS RDS?

Submitted by: @import:stackexchange-dba··
0
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?

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.