October 23, 2017 at 10:45 am
I recently migrated a database from SQL Server 2005 to 2016 and noticed the free space in the database dropped after our ETL ran.
Upon investigation, I notice that the unused size in the tables had increased.
I have some work arounds, listed below, but I am not sure I really understand what is going on.
Does anyone have a good explanation?
The database is a loaded by a daily ETL that truncates the tables and reloads the data - yes there are better solutions.
The package uses OLE DB Source and Destination.
Work arounds:
Change the ‘Rows per batch’ to 99 or below
Turn on the trace flag 692 - Disables fast inserts while bulk loading data into heap or clustered index. See: https://blogs.msdn.microsoft.com/sql_server_team/sql-server-2016-minimal-logging-and-impact-of-the-batchsize-in-bulk-load-operations/
Rebuild the clustered index after the data load – removes the used pages but does not prevent it.
Things tried that did not correct issue:
Changing the database to Full logging mode
Dropping the clustered index ( all 3 tables have clustered indexes)
Changing versions of visual studio – tried 2008, 2015, 2017
Rebuilding the clustered index of the source before running the ETL
I used sp_msforeachtable 'EXEC sp_spaceused [?]' to determine the free pages
| name | rows | reserved | data | index_size | unused | |
| rows per batch 99 | ||||||
| Table_C | 9427513 | 1229704 KB | 1223448 KB | 6088 KB | 168 KB | |
| Table_D | 6508317 | 386632 KB | 383976 KB | 2584 KB | 72 KB | |
| Table_S | 6409762 | 709840 KB | 502728 KB | 206928 KB | 184 KB | |
| rows per batch 100 | ||||||
| Table_C | 9427513 | 1304264 KB | 1223448 KB | 6088 KB | 74728 KB | |
| Table_D | 6508317 | 418888 KB | 383976 KB | 2568 KB | 32344 KB | |
| Table_S | 6409762 | 804176 KB | 502728 KB | 206928 KB | 94520 KB | |
| traceFlag 692 and row per batch 100 | ||||||
| Table_C | 9427513 | 1229704 KB | 1223448 KB | 6088 KB | 168 KB | |
| Table_D | 6508317 | 386568 KB | 383976 KB | 2568 KB | 24 KB | |
| Table_S | 6409762 | 709776 KB | 502728 KB | 206928 KB | 120 KB | |
Info:
I was able to repro the issue, using only 3 of the smaller tables. The larger tables had up to 800 MB of unused space.
Table_C, Table_D and Table_S.
Row Size:
Table_C – Max 211, Min 39, Avg 128
Table_D – Max 71, Min 41, Avg 56
Table_S – Max 85, Min 77, Avg 77
SSIS package:
Keep Identity checked
Keep nulls checked
Table Lock Checked
Check constraints unchecked
Rows per Batch: 99 or 100 depending on test
Maximum insert commit Size: 2147483647
DefaultBufferMaxRows: 10000
DefaultBufferSize: 10485760
EngineThreads: 10
Thanks.
October 23, 2017 at 11:33 am
I'm more curious as to whether it really matters or not. SQL 2005 was designed more than a decade ago, and the 2014 version of SQL Server made changes to the cardinality estimator, so I wouldn't be all that surprised that perhaps it simply over-allocated space because the execution plan that was generated by the newer version of SQL Server allocates more space under the right circumstances, which in your case appears to be a batch size in excess of 99. You might be running into some kind of cost threshold that the newer SSIS might be putting in play. Hard to know. Is a couple of hundred meg really a space issue in this day and age? It would eventually get used as more data comes into the database, so I'm not so sure that it will matter unless you are seriously short on disk space.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
October 23, 2017 at 11:47 am
I should have added that I tested this in SQL 2014 and it worked as expected. Little unused space.
It is not so much the space as my understanding.
It does not make sense to me that 3 tables all with different row sizes would all have 99 as the magic max batch size.
Thank you for looking and responding to this post.
October 24, 2017 at 12:13 pm
If you do come up with an answer, please post back. Ya never know what MS is up to at times, and strange things can and do occur, from time to time..
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply