0

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

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
user3339793
  • 3
  • 3

1 Answers1

0

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:

When we insert single rows in the table, SQL is able to figure out which data is going to require updating the indexed view index.

So in case inserted data do not affect the indexed view it does not update them.

michal.jakubeczy
  • 8,221
  • 1
  • 59
  • 63