0

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).

2 Answers 2

3

does the Microsoft SQL Server supports the SSD-Trim-Command to deallocate space in some way?

That's really not in the purview of the database, which is an application on top of the operating system. Windows has support for TRIM in the most common form which is via NTFS (as the most common filesystem) of which SQL Server sits on top of (technically there is also RAW and ReFS) which is what is mostly responsible for this behavior, outside of some background Windows processes and services such as storage optimization.

The big issue here is this is not how SQL Server utilizes storage. When the table is truncated, for example, depending on the size it may be deallocated in the same command or it might be marked for deallocation as part of a background cleanup. Either way, the space will be marked as available inside of SQL Server, which means so far as NTFS/ReFS is concerned that cluster is still being used. If SQL Server zeroed out the page, it'd still span 1 or more clusters that aren't going to be marked as free in the underlying filesystem, again no real TRIM. SQL Server will re-use the space, so it's not lost and there isn't the hit of allocation at the filesystem and disk level sin ceit's already allocated.

Long and short, this would cause more wear and IO to the disks if the model were to deallocate at the filesystem level when a page is deallocated (also depends on cluster size), along with filesystem fragmentation (which can/will lead to the dreaded os error 665 with NTFS).

Edit: This is a very high level and does not get into the internal page sizes, wear leveling, and write amplifcation pieces of SSDs and NVMe.

0

TRIM is usually issued by the filesystem layer in the operating system. Functions that shrink the table space file and return that space to the OS will result in TRIMs getting issued for those blocks.

Spending time zeroing blocks will consume unnecessary I/O and reduce performance.

Unless your database is entirely static, the database will grow again and dirty those blocks again soon, so there’s not much benefit in micromanaging the space this way. Modern SSDs for enterprise use are pretty smart about pre-cleaning pages.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.