Lesson Learned #120: What is the impact having an Indexed View importing data?

This post has been republished via RSS; it originally appeared at: New blog articles in Microsoft Tech Community.

Today, we have been working on an important issue that our customer reported that importing data, in this case, using a bacpac, took too much time. 


During the importing process we were checking the performance of the client machine and database, in fact, we scaled up to one of the most powerfull database tiers in Azure SQL Database. 


  • Running the command: SELECT * FROM sys.dm_db_resouce_stats we saw that CPU, IO or LOG consumption is not high. 
  • Reviewing Resource Monitor of the client machine where SQLPackage is running the CPU, IO and Network usage are not high
  • In this situation, as SQLPackage is using, by default, 8 threads to import the data, we tried to find out if there is any blocking issue that may cause this problem.
    • We executed the query that we have in this URL: We found that 7 sessions are waiting for 1 session and all of them are running Bulk Insert command. 
      • We reviewed several details of the tables and we found that the blocking issues are coming from tables that have multiple views. 
      • We found that these views are indexed views that are causing these blockings because we have 8 sessions that are adding data and depending on the definition of the view (some of them have count,sum operators) are impacting in the process because needs to be evaluated
    • Running this TSQL we found the indexed views (contains an index) and we removed them : SELECT o.name as view_name, i.name as index_name
      FROM sysobjects o
      INNER JOIN sysindexes i
      ON o.id = i.id
      WHERE o.xtype = 'V' 
    • After removing all of them, the importing process was reduce several hours and after it we were able to re-create the indexed views



REMEMBER: these articles are REPUBLISHED. Your best bet to get a reply is to follow the link at the top of the post to the ORIGINAL post! BUT you're more than welcome to start discussions here:

This site uses Akismet to reduce spam. Learn how your comment data is processed.