I receive a .bak file from a 3rd party on a monthly basis and create 1 db each receipt.
.bak file size is ~1Gb.
This data is for lookup only and I never write to it. Are there any steps I can take to improve performance for this scenario?
I am considering:
ALTER DATABASE [TESTDB] SET READ_ONLY WITH NO_WAIT
Should I consider:
I am using the current version of SQL Express.
After restoring the database examine the query plans for your key queries and make sure you have optimal indexes for your use cases.
Setting the database READ_ONLY will normally increase performance by a few percentage points for SELECT queries. If you have time and it is not already in place consider setting the index fill factor to 100% and use page compression for all indexes - you don't need to write to them so they may as well be as small as possible.
Shrinking the log will not affect the performance. Also no one can tell you if you should reorganize/rebuild the indexes because we don't know if they are fragmented and also how selective your queries are and if the server does seek or scan in order to get the data.
I have to admit that it sounds as if you are looking for a task list that will help an all cases, but there is no such list. In order to improve performance, we need to know what cause the performance problems and react to this problem. Instead of trying to find a "global fix", maybe it will be better to concentrate on the worst performing queries or the most used queries and check for each one of them what should be done in order to improve the performance.
