0

Our databases files (data and log) of (a lot of) SQL Servers are hosted on flash storage (SAN), what would you use as auto-grow property for databases having size round 500GB (data) and 150Gb (log)? Knowing in the background that you cannot size 5 years ahead because of storage price. Subsidiary question: Is it still accurate to take care of file fragmentation on SSD SAN volumes?

  • It's unclear what advice you want to get for the auto-grow property. It doesn't depend on the current size but more depends on many other points. – Denis Rubashkin Mar 28 at 15:09
  • @Denis, size matters sometimes...what would you take in account also? – Filoo Mar 28 at 15:57
  • Fragmentation can still be an issue, even with fast drives, depending on size of data and amount of memory. It becomes less about the speed of loading data from disk into memory, and more about constantly swapping half-or-more-empty pages. Memory is cheap but adding more to keep pace with data growth is not always possible, and performance can suffer as a result. Swapping is fast but it is still more intrusive than not. Keeping fragmentation in check can, in some cases, be the difference between all data in memory in not. In other cases (huge DB and/or minimal RAM) it can't possibly help. – Aaron Bertrand Mar 28 at 16:10
2

In general, the type of storage and current size of database isn't particularly relevant to your auto-grow settings. Ensuring that instant file initialization is enabled is far more important.

If instant file initialization is not enabled, then everything (more or less) comes to a halt while it is initializing the new part of the file. With SSDs that time will be shorter, but it will still be a significant hiccup. So If you don't have it enabled, you would want to have growth size set fairly small, or increase the size during maintenance windows.

If instant file initialization is enabled, then the file growth will only take a few milliseconds regardless of whether the storage is SSD or platters. How much you grow it at once just depends on whether anything else will be using the disk space. If it is one database, one file, etc., and nothing else will be using it, you could just grow it once to use almost all of the disk and be done with it. But if there are multiple databases/files, then you'll just have to take all of those factors into consideration.

As to file fragmentation, the performance impact is all about disk read times, and since random disk seeks are far faster on SSDs, it's not near as much of an issue as it is with platters. As Aaron mentioned in comments above, reads should mostly be out of the buffer, so if there is adequate memory for SQL Server it won't be reading much from disk anyway.

In general, you want to set it up so that it doesn't have to grow very often so that when there is a performance issue, you won't even be wondering if it's due to file fragmentation, etc. It doesn't matter what your storage is, optimize the config for whatever resources you have. Without knowing all of the details of the system, we can only answer questions about concepts, not specifics of what would be best for your particular situation. Additionally, StackExchange is not really the place for getting custom specs--that's a consulting engagement.

  • Thank you, I was more concerned by file fragmentation at physical level than index fragmentation, that we constantly chase, despite of SAN vendor saying that it is not useful with flash storage and by the way fools de-duplication process. With an increase rate of let's say 1GB per day, should I use 16,32..512MB chunks? My metaphysical question of the moment. – Filoo Mar 28 at 16:37
  • OK--same concept applies. Will update the answer. – Tony Hinkle Mar 28 at 17:16

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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