In Bulk logged recovery model (minimal logging), SQL Server flushes data pages as soon as it commits the batch (also known as eager writes). I/O effect of exceedingly large batch sizes You can enable the trace flag while SQL Server is online by using the following query: DBCC TRACEON(692,-1)Īlternatively, you can add -T692 as a SQL Server service startup parameter to enable TF 692 automatically upon SQL Server service restart. This minimizes the unused space issue that is described in the Symptoms section. Trace flag 692 disables fast inserts while loading bulk data into a heap or clustered index. Therefore, bulk load operations that have small batch sizes can cause an increase in unused space in objects. As a result, each bulk load batch allocates new extents and bypasses the lookup for available free space in existing pages by default. Starting with SQL Server 2016 fast inserts are enabled by default in the product. If, for some reason, you can't change the batch size or use regular INSERTS, you can disable the fast inserts (minimal logging) behavior by using trace flag 692 (TF 692). If batch size configuration isn't an option for bulk load operation For more information, see I/O effect of exceedingly large batch sizes. SELECTįROM sys.dm_db_index_physical_stats (DB_ID(N'AdventureWorks2016'), OBJECT_ID(N'Production.ProductDocument'), 1, NULL, 'DETAILED')īe careful not to set the batch size to a large value because doing this could cause bursts of large I/O requests. For tables that have clustered indexes, use 1, as shown in the following example. For heaps (tables that have no clustered index), use 0 for the index_ID parameter (the third parameter). To find the average row size in your table, use the following query. You can test this by using batch sizes of say a range between 2,500 and 2,700 and observe the space usage. ![]() Therefore, you can choose a batch size around this number, allowing space for the header of each data page. In this case, 64 KB = 65,536 bytes / 25 bytes per row = 2,620 rows. For example, if the average row size is 25 bytes, you would divide 64 KB by 25 bytes per row to determine how many rows you can pack into a batch size. Such a batch size value would allow the rows to efficiently fill the space within the extent. Setting the batch size value for bulk load operationįor bulk load operations, choose a batch size that's a multiple of the size of an extent (64 KB) and is based on the average row size. In cases of small batch sizes, we recommend that you use regular fully-logged INSERT statements, rather than bulk load operations with minimal logging optimizations. If you have a relatively small number of rows to insert, then these aren't "bulk" inserts. Small number of inserts means no bulk load operations To resolve this issue, consider the following guidelines. The following table from the MSSQL Tiger Team blog site shows some empirical evidence to illustrate this behavior. Therefore, the fast load optimization combined with a smaller batch size causes inefficient space usage. ![]() The remaining pages in the extent are unused but reserved for the object. This is wasteful for most row sizes (some rows may be so wide as to fit on a single page in which case 10 records may be appropriate). ![]() Therefore, if you use a small batch size (for example, 10 rows per batch), SQL Server reserves a new 64-KB extent for every batch of 10 records. It directly creates new extents instead of looking for free space in existing ones. SQL Server skips this cache lookup to optimize insert performance. In minimal logging mode, each bulk load batch bypasses a lookup for available free space when it allocates one or more new extents. But the batch size ( BATCHSIZE in BULK INSERT and -b option in bcp utility) that you use in these operations plays a critical role in achieving faster performance on one hand and efficient space usage on another. Running bulk load operations that use minimal logging can help improve the performance of data load operations in indexes if the data is pre-ordered or sequentially loaded. If you use bulk load operation that have a small batch size, tables might allocate page extents that are barely used. If you run the sp_spaceused command, you may observe the unused space in the table occupies a large percentage of the reserved space (overall space allocated for the table). You might observe consistently high growth of unused space for tables in your databases when you run bulk load operations.
0 Comments
Leave a Reply. |