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.
- 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.
Enjoy!