SQL Azure. Create Index recommendation and performance

1
votes

I got several CREATE INDEX recommendations on Azure SQL S3 tier.

Before going through, I'd like to know some issues during indexing with 10-million records.

  1. Can we know indexing progress or completion time approximately?
  2. Does indexing work in asynchronous (or we can say lazy index) manner? Or it blocks query to the table/database?
  3. Is there anything we need to know about performance degradation during indexing? If so, can we expect amount of degradation?
  4. Does it perform differently from my CREAT INDEX command?
  5. If the database is readonly-georedundant configured, I assume that index configuration itself is replicated either. But does indexing job operate separately?
  6. If the indexing is performed on their own(replicated) database, tier master(S3 tier) to replica(S1) could have different indexing progress. is it correct?
1
sql-serverazureindexingazure-sql-database

1 Answers

1
votes

Can we know indexing progress or completion time approximately?

You can get to know amount of space that will be used ,but not index creation time.You can track the progress though using sys.dm_exec_requests

also with SQL2016(azure compatabilty level 130) there is a new DMV called Sys.dm_exec_query_profiles..which can track accurate status better then exec requests DMV..

Does indexing work in asynchronous (or we can say lazy index) manner? Or it blocks query to the table/database?

There are two ways to create Index
1.Online
2.Offline

When you create index online,your table will not be blocked*,since SQL maintains a separate copy of index and updates both indexes parallely

with offline approach, you will experience blocking and table also won't be available

Is there anything we need to know about performance degradation during indexing? If so, can we expect amount of degradation?

You will experience additional IO load,increase in memory..This can't be accurately estimated.

Does it perform differently from my CREATE INDEX command? Create Index is altogether a seperate statement ,i am not sure what you meant here

If the database is readonly-georedundant configured, I assume that index configuration itself is replicated either. But does indexing job operate separately?

If the indexing is performed on their own(replicated) database, tier master(S3 tier) to replica(S1) could have different indexing progress. is it correct?

Index creation is logged and all the TLOG is replayed on secondary as well.so there is no need to do index rebuilds on secondary..