Sample Header Ad - 728x90

MS SQL: SSD Trim after TRUNCATE / DROP?

0 votes
2 answers
196 views
I consider to use one of those fancy new SSD-only SAN systems for our new Microsoft SQL Server. "Fancy" means, that it supports / use stuff as block compression / deduplication / 0x00-recognitation / disk space overprovioning etc. When I drop or truncate a table the SQL server usually only marks the pages in the page allocation map as free, but does not touch the data in the pages itself (e.g. overwriting it with 0x00). This is very fine (because fast) with HDDs but SSDs prever to know if a block is free and could be overwritten by its internal routines or if the data are still important and needs to be preserved. So my question: does the Microsoft SQL Server supports the SSD-Trim-Command to deallocate space in some way? Of course without shrinking shrinking the file or doing other manual / slow stuff which fragments my indexes, blocks my queries etc. Or is there a way to force the SQL server to overwrite the unassigned data by 0x00 (which will be recognized by the SAN, so it frees up the space internal), even if this is the worser option compared to the SSD-TRIM? PS: I know that it usually makes sense to have some (!) unassigned space in the database files to use for new data, but just assume that I delete more than I will add in the next 6 months or that I work with partitions and the deleted stuff resists in other files than the current hot data (but the old filegroup is still not empty, so it can't be dropped / shrinked).
Asked by Thomas Franz (885 rep)
Oct 25, 2021, 11:59 AM
Last activity: Oct 29, 2021, 02:54 PM