March 12, 2020 at 8:54 pm
Hi All,
We recently upgraded one of our OLTP systems from SQL 2014 to SQL 2017, and after the upgrade I observed that our main data disk was quickly filling up. Ran a query to see if perhaps we were ingesting a large volume of new data for some reason, but based on the row counts in the partition files that is not the case, however, I see that the 'reserved_mb_all_indexes' is 5x what it was before the upgrade on the 15th of last month. Data being loaded into the partitions post upgrade is consuming tons of space. In the attached picture, the blue line represents the upgrade. Green = files before the upgrade, red is after. Does anyone know why this is occurring and how to resolve it? Possibly worth mentioning that when looking at percent of free space in those large files, there is less than 15%, and the ones pre-upgrade are >45% free space. When we upgraded we also doubled the RAM and CPU on the server.
Thanks in advance for your time.
March 12, 2020 at 9:27 pm
you may be suffering from the change to bulk insert since 2016.
have a look at https://techcommunity.microsoft.com/t5/sql-server/sql-server-2016-minimal-logging-and-impact-of-the-batchsize-in/ba-p/385537
if this is the reason you will need to either change your load processes to have bigger batch size, rebuild indexes more often or enable trace flag 692
March 12, 2020 at 9:48 pm
Thanks for the info frederico_fonseca, I will take a look at this!
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply