Database performance optimization checkList

1.       Maintenance of Health Checkup of Production Database on Daily basis.

2.       Implementation of Maintenance Jobs to be scheduled at regular intervals (Daily and Weekly)

a.       Database Integrity Check- Weekly

b.      Index Defragmentation – Rebuild and Reorganizing- Daily

c.       Check for Missing Indexes (to be performed manually)- Weekly

d.      Check for Unused and Duplicate Indexes (to be performed manually)- Weekly

e.      Update Statistics

f.        Check Wait Stats and network latencies for Data loads (to be performed manually)- Weekly

g.       Purging wherever applicable- Weekly

h.      Database Shrinking wherever applicable- Weekly

i.         Checking for costliest queries and their improvement recommendations- weekly

j.        Db growth and disk space checks

k.       Deadlocks and IO Bottlenecks

l.         Checking wait stats.

3.       Server Settings Recommendations as per Microsoft’s Best Practices.

a.       Setting up Minimum and Maximum memory allocation on Sql server.

b.      Analyze the best practice for changing Fill Factor

c.       Analyze the best practice to implement optimize adhoc workload value.

d.      Changing the MAXDOP setting as per available CPUs on server.

4.       Database Settings Recommendations as per Microsoft’s Best Practices.

a.       Setting up database initial size, Auto growth and Max growth.

b.      Checking for Recovery Model.

c.       Checking for Compatibility level

d.      Analysis and implementation of best practices of Automatic options like Auto Create Statistics.

e.      Checking for Page Verify as CheckSum.

f.        Checking for state options.

g.       Setting up multiple mdf files for user database wherever applicable.

h.      Implementation of best practices of TempDB.

i.         Checking of Disk space available for database in drives.

j.        Setting up c drive space as per available RAM.

PS: All Server and Database settings are one time activity and need to be performed in production downtime and also, sql services needs to be restarted after completion of these activities.