I have a fairly big table, let's call that 2B records split evenly between 200 partitions (SQL Server 2012).
Every day, I have a process that takes 6 hours to create a new partition and insert 10 million rows.
I think indexed views could improve the performance of my reporting, but we are a little concerned if there are any performance issues with the inserts.
So if I create an indexed view over my big table, at what point will those indexes be refreshed: after every insert, or when a user will read from the indexed view for the first time?
Thanks in advance
Indexed view will be refreshed at the point of a data modification operation (e.g. INSERT, UPDATE, DELETE). So it affects performance of those operations.
But as for each such case, the best is to make a benchmark how much it affects inserts as it depends case by case. If performance of reads is your goal you can accept the insert slowdown.
Also I can recommend a good article - https://www.brentozar.com/archive/2017/03/indexed-views-data-modifications/ ... the interesting thing it says is:
So in case inserted data do not affect the indexed view it does not update them.