Lessons Learned #540:Bulk Insert Throughput in Azure SQL Hyperscale with Partitioned Heap Tables
In this lesson learned, I would like to share an interesting scenario working on a service request where our customer was running a high-volume data load process in Azure SQL Database Hyperscale. The workload was based on a common pattern: Recreate a staging table. Load a large number of rows using bulk insert. The bulk insert showed unstable execution times and became the main area to investigate. The process was loading a very large number of rows into an Azure SQL Database Hyperscale database. The process used a staging table that was initially loaded as a heap. The main concern was the inconsistent execution time during the load process. Why Manually Adding Data Files Was Not the Right Direction In Azure SQL Database Hyperscale, the storage architecture is different from a traditional SQL Server deployment. The data layout and storage management are handled internally by the service. Because of this architecture, manually creating or pre-allocating multiple data files is not the same tuning option that we may consider in SQL Server on-premises or SQL Server running on Azure Virtual Machines. For this reason, the troubleshooting focus moved from manual file layout configuration to the actual workload pattern, waits, concurrency, batch size, and staging table design. What We Observed During the bulk insert phase, waits such as PAGELATCH_EX were observed. Since the staging table was loaded as a heap and the clustered primary key was created only after the bulk insert completed, OPTIMIZE_FOR_SEQUENTIAL_KEY was not directly applicable to the bulk insert phase. This changed the direction of the investigation. Instead of focusing on last-page insert contention on an existing clustered index, the analysis moved toward heap insert behavior, allocation contention, concurrency, batch size, and whether a different staging table design could help. First Recommendation: Start with Low-Impact Changes Before changing the table design, the first recommendation was to test the least intrusive changes: Reduce the number of concurrent bulk insert sessions. Increase the batch size, for example from 10,000 rows to 50,000 or 100,000 rows. Test TABLOCK on the dedicated heap staging table. The goal was to avoid assuming that more concurrency would always reduce the total execution time. In some high-volume load scenarios, excessive concurrency may increase contention and make the process less stable. The Interesting Design Option: Partitioned Heap Staging Table One of the most interesting design options was to evaluate a partitioned heap staging table. The idea is simple: instead of loading all rows into a non-partitioned heap staging table, the staging table can be created on the same partition scheme used by the target table, using the same partitioning column. This does not mean that a partitioned heap will always be faster. However, it can be a useful design option when: The bulk load phase is affected by allocation or latch contention. Concurrent load processes can naturally distribute rows across different partition ranges. The staging table is used only as an intermediate structure.Lessons Learned The main lessons from this scenario were: In Azure SQL Database Hyperscale, manually managing multiple data files is not the right tuning direction. PAGELATCH_EX during heap loading may point to concurrency or allocation-related contention. Reducing concurrency can sometimes improve total throughput. Larger batch sizes may provide better results than many small batches. TABLOCK on a dedicated heap staging table is a low-impact test worth evaluating. A partitioned heap staging table can be a valid second-phase design option when the load can be distributed across partition ranges. The best approach is to test small, measurable changes before introducing architectural redesigns. Final Thoughts A partitioned heap staging table can be a powerful option, but only when it is tested carefully and when the workload pattern can benefit from partition distribution.
0x000000e blue screen error on boot after mbr2gpt
Hello, I just tried converting my boot disk to gpt in order to upgrade to Windows 11 from 10. I don't have screenshots on the cmd while doing so, but I remember it said it was successful. I have disabled CSM support and enabled Secure boot and made sure it says secure boot is active on the BIOS as well, but I still get this error when trying to boot. I do have 2 identical boot options which I've read can happen after mbr2gpt, but neither seem to work. I am able to boot in safe mode and would love any help getting my PC to boot now, and while I'm fine with wiping and rebuilding windows as a last resort, I would love to not have to reinstall everything. I take being able to boot in safe mode as a sign I might just have to repair with my partitions or something so I'm hoping I don't have to reset everything. Thank you!Dynamically adding partitions to a topic/event hub under Event Hubs namespace
Event Hubs provides partitions to scale consumers for parallel processing. The concept of partitions belongs to Topics under Event Hubs namespace. Topics helps categorize the incoming messages and consumer in a group processes the events from one of the Topic partitions. When a Topic is created, the number of partitions are specified at the time of creation. For some special cases though, you may have to add partitions after the Topic has been created. This requires you to dynamically accommodate the addition of partitions. This blog describes the behavior of adding partitions to an existing Topic with Event Hubs. Dynamic additions of partitions is available only on Dedicated Event Hubs clusters and not on Standard Event Hubs namespace.
