How can I optimize my Microsoft SQL Server (MSSQL) database?

You can optimize the performance of a new database by setting the initial database size to a large number, for example 2Gb.

You can also set the new or existing database's autogrowth size in relation to your daily growth rates. For example, if your database grows 1GB per day you should change the database autogrowth setting to be 1GB, or to change the autogrowth an amount that is 1/2, 1/3rd, or 1/4th of the daily growth size. If you do not change the default autogrowth setting (default is 1MB) and the database grows in size by 1GB per day, the database will have to stop itself, grow, and then resume itself 1000 times per day which will exhibit very poor performance.

The transaction log usually does not need to have its autogrowth settings changed, but it is VERY important to have the transaction log (LDF file) on a separate hard drive than the database (MDF file) whenever possible so that the transaction log and database do not block each other with their combined disk I/O causing saturation of a single hard drive.

It is also important to perform index maintenance (rebuild or reorganize) for your database's indexes on a regular basis, such as every month. If you ever shrink the database, this will fragment the indexes and the database will perform more poorly until the indexes receive maintenance. To perform index maintenance you can use a SQL maintenance plan (https://www.eventsentry.com/kb/220) but that is not recommended for large databases because it cannot determine the best maintenance action for each index and will unconditionally reorganize or rebuild every index in the database regardless of whether or not an index was fragmented and with no regard as to whether rebuilding an index would have been faster than reorganizing that index and vice versa.

For large databases it is recommended to use a custom script for index maintenance such as https://ola.hallengren.com/sql-server-index-and-statistics-maintenance.html
If you wish use the recommended script, you may install it by clicking the "Download MaintenanceSolution.sql" link towards the top, and then open the downloaded file in notepad. Copy and paste the text from notepad into a new query window on the SQL server and then execute it. Then, any time you want to manually perform index maintenance you can execute this query:
EXECUTE [dbo].[IndexOptimize] @Databases = 'EventSentry'
If your database is named something other than EventSentry (it is not case sensitive) you would want to replace that name in the example with the actual name of your database.

The script will also create a new SQL Agent job called IndexOptimize, which you can use to automatically run index maintenance, and you would probably want to customize so that it only performs index maintenance on the EventSentry database rather than all of the databases. The job has to be manually scheduled or executed with the "Start Job At Step" function, so the maintenance won't end up running when you haven't told it to do so. To customize the IndexOptimize job so that it only performs index maintenance on the EventSentry database, go into the job properties and edit the job steps (there's only one) so that this part of the job command:
EXECUTE [dbo].[IndexOptimize] @Databases = 'USER_DATABASES'
says this instead:
EXECUTE [dbo].[IndexOptimize] @Databases = 'EventSentry'
Now you can select the Schedules tab of the job properties and schedule the job to run automatically as often as you see fit. If any database performance problems or slow-downs occur despite monthly index maintenance you may wish to change the index maintenance to run every two weeks or every week.