Some background info: this is a test SQL Server 2012, with restored databases from production servers, all of them with over 20% space available in the data file, up to even 50%. I'd rather restore as many databases on this test server than waste the limited storage space. So decided to shrink the data files.
The way I see it with sys.dm_exec_requests, it seems to be a connection between the reported percentage_complete for the shrink operation and the memory taken up by SQL Server. It looks like when SQL Server grabs all the available memory (configured maximum memory), the shrink slows down to a crawl. I assume the server tries to load as much data in memory to save I/O ?
Killing the shrink process seems to clear the memory (doesn't release it to the OS, but as I restart the data file shrink - it's fast again). Adding more memory to the OS helps as well. But for databases in the hundreds of GBs, it's a painful process.
Is there any proper way to do this ? Maybe an option to release the free space when restoring the backups ? Drop the indexes before the shrink and rebuild once done ?
Any good resources to read and understand how the shrink works, or on how the data files are organized ?
