2

I'm building an application where a certain group of programmatically-created Azure SQL Server tables will only ever be read from.

The app will create a new table, insert a bunch of data, and then serve queries from that data. After the initial inserting the data will never be changed in any way.

Given this usage pattern, are there efficiencies I could take advantage of when setting up such a table, or the indexes on it?

Improve this question
1
  • 1
    You can index the life out of them, to cover all data access patterns for all queries against them. – Randolph West Oct 12 '16 at 19:14
2
  1. Rebuild indexes with a FILLFACTOR 100 so pages are packed as tight as possible.
  2. Update statistics on the table WITH FULLSCAN so the query optimizer has the most complete information possible to generate a query plan.
Improve this answer
1

I think the biggest efficiency you could gain is excluding these tables from index maintenance. If the data never changes, then the index won't ever become fragmented. The down side to SSIS maintenance plans is they won't be able to do this sort of thing.

I use SQL Stored procedures to do my index maintenance, but I would have to adjust them to exclude tables. I use SP because when it does error, I get useful error information whereas SSIS puts so much garbage into the agent table it truncates the error.

Improve this answer
1
  • Ola Hallengren's Maintenance Solution would automatically bypass maintenance on these objects if they haven't changed recently. – Randolph West Oct 12 '16 at 19:59

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.