April 18, 2018 at 8:07 am
dajonx - Wednesday, April 18, 2018 7:55 AMHi,
I have a large database (around 10 TB) that uses an SSD SAN array with 24 drives. I'm wondering if I need to maintain free space percentage above 15%? If so, there will be a lot of free GBs which would bloat the database.
Thanks!
We will need some more information such as growth rate etc.
😎
BTW: what are the specs of the server, how much of the data is "hot"?
April 18, 2018 at 8:13 am
Ideally, you should keep enough free space in the database to accommodate data growth without having to auto-grow or manually grow the file(s) too frequently. So, using the numbers you provided, if you know your data will grow by 500GB a week, you've got 3 weeks of free space before you'll need to grow the files.
If, on the other hand, your data only grows at 10GB a week, you've got quite a bit of time, and could probably reduce the amount of free space.
Or, if you have regular data loads that initially go to staging tables in that free space, do you really want to have to constantly shrink and grow the database to accommodate that process?
It really comes down to:
How fast your data grows
How frequently do you want to have to expand the data files
There is no hard-and-fast answer to your question, it all depends on your particular situation.
As Eirikur said, more information would help, but the final decision is going to be between you and your management.
April 19, 2018 at 7:41 am
Thank you.
I wasn't sure if the NTFS performance degrading when there's less than 15% free space still applies. There's ample free space that there are rarely any auto-growth going on, but it's definitely less than the 15% "rule" (7% or higher).
I'm guessing you guys don't care about the 15% rule?
April 19, 2018 at 7:50 am
I'm wondering now if you're confusing free space on disk and free space in the database file.
The file system won't care how much free space there is in the database file, as it only knows "there's a XXTB file," not "there's an XXTB file with YYTB empty space inside it." So if you're talking about free disk space (IE, your database is 10TB and your disk space is 11.5TB, and we don't care / know how much of the DB is empty space,) then I've never heard of the 15% rule. As for whether I care about how much free space I've got on disk, I do care, but when I was getting my new servers provisioned, I looked at ensuring I had enough space that I was extremely unlikely to get a call first thing Monday morning because my server ran out of room for a database or log or audit file and had stopped working.
April 19, 2018 at 8:42 am
dajonx - Thursday, April 19, 2018 7:41 AMThank you.I wasn't sure if the NTFS performance degrading when there's less than 15% free space still applies. There's ample free space that there are rarely any auto-growth going on, but it's definitely less than the 15% "rule" (7% or higher).
I'm guessing you guys don't care about the 15% rule?
I can't answer for others but, correct... I don't care about the 15% "rule" within the database. If you have instant file initialization enabled, growth of the MDF file is trivial especially with SSDs (not so for the log file, though). If would be far better to make sure that you have enough free disk space to accommodate a year or two of growth.
Shifting gears a bit, if the data is, indeed, 10TB, what are the top 4 largest table/indexes and are they partitioned? Also, have you checked to see what the average percent of page fullness is for each index? I've also found that people store a lot of junk that is either a duplicate of what can be found elsewhere or no longer matters for any reason. An example of this is that we discovered that our 1.4 TB ETL database has data in it that goes back 8 years and the data is mostly data that was imported and has been finalized somewhere else. We were able to drop almost 1 TB from that database and set a 1 year bit of rollover code that deletes data more than a year old every night. Our main "public facing" 1TB database had a similar problem and we've been able to drop 600GB from that.
--Jeff Moden
Change is inevitable... Change for the better is not.
April 20, 2018 at 7:16 am
Ah, yes... You're right, Jason. I am confusing database file free space with free space on disk. I feel pretty dumb now!
Thanks, Jeff! It looks like I, at first, was trying to keep the 15% "rule" for the database files, but since the database is so large 15% is a huge amount of space. So I stopped maintaining the 15% rule (within the database) and so I was curious if I was mess something up. Now that Jason blew my mind with his database file free space vs free space on disk, I'm all straightened out.
I would absolutely LOVE it if I could move old data out, but everyone else and their mammas want to keep that old data all in a single database. I do have the largest tables/indexes partitioned.
I will look into the page fullness for each index. I didn't think it was recommended to perform index reorgs/rebuilds anymore. Only to keep up with stats.
April 20, 2018 at 7:54 am
dajonx - Friday, April 20, 2018 7:16 AMAh, yes... You're right, Jason. I am confusing database file free space with free space on disk. I feel pretty dumb now!Thanks, Jeff! It looks like I, at first, was trying to keep the 15% "rule" for the database files, but since the database is so large 15% is a huge amount of space. So I stopped maintaining the 15% rule (within the database) and so I was curious if I was mess something up. Now that Jason blew my mind with his database file free space vs free space on disk, I'm all straightened out.
I would absolutely LOVE it if I could move old data out, but everyone else and their mammas want to keep that old data all in a single database. I do have the largest tables/indexes partitioned.
I will look into the page fullness for each index. I didn't think it was recommended to perform index reorgs/rebuilds anymore. Only to keep up with stats.
As a bit of a sidebar, if the partitions on the tables are temporal in nature (for example, by month) AND older partitions aren't updated (WORM tables such as audit tables and invoice tables of closed invoices) AND if the partitioning is setup to be similarly temporal AND you have one File Group and File for each temporal partition, you can save a huge amount on backups by setting the older, static partitions to READ_ONLY, back them up one final time, and then change your backups to no longer backup READ_ONLY file groups.
--Jeff Moden
Change is inevitable... Change for the better is not.
April 20, 2018 at 8:12 am
Free space in database files:
SELECT
[Logical Name] = [name],
[Size (MB)],
[UsedSpace (MB)] = [Size (MB)] - [AvailableSpace (MB)],
[AvailableSpace (MB)],
[AvailableSpace (%)] = CAST(([AvailableSpace (MB)]*100)/[Size (MB)] AS DECIMAL(6,2))
FROM sys.database_files
CROSS APPLY (
SELECT
[Size (MB)] = CAST(size/128.0 AS INT),
[AvailableSpace (MB)] = CAST(size/128.0 - FILEPROPERTY([name], 'SpaceUsed')/128.0 AS INT)
) x
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
April 20, 2018 at 8:32 am
Jeff Moden - Friday, April 20, 2018 7:54 AMdajonx - Friday, April 20, 2018 7:16 AMAh, yes... You're right, Jason. I am confusing database file free space with free space on disk. I feel pretty dumb now!Thanks, Jeff! It looks like I, at first, was trying to keep the 15% "rule" for the database files, but since the database is so large 15% is a huge amount of space. So I stopped maintaining the 15% rule (within the database) and so I was curious if I was mess something up. Now that Jason blew my mind with his database file free space vs free space on disk, I'm all straightened out.
I would absolutely LOVE it if I could move old data out, but everyone else and their mammas want to keep that old data all in a single database. I do have the largest tables/indexes partitioned.
I will look into the page fullness for each index. I didn't think it was recommended to perform index reorgs/rebuilds anymore. Only to keep up with stats.
As a bit of a sidebar, if the partitions on the tables are temporal in nature (for example, by month) AND older partitions aren't updated (WORM tables such as audit tables and invoice tables of closed invoices) AND if the partitioning is setup to be similarly temporal AND you have one File Group and File for each temporal partition, you can save a huge amount on backups by setting the older, static partitions to READ_ONLY, back them up one final time, and then change your backups to no longer backup READ_ONLY file groups.
dajonx, don't worry about it. It's just a case of us asking questions to make sure what we're thinking you're talking about is what you think you're talking about.
Piggy-backing on what Jeff was saying about the partitions and saving on backups, *if* your partitioning is set up similar to how Jeff described it, you could also look at moving the data files for the static partitions off the SSD storage and on to regular (and slower) hard drives (presuming your SAN is set up with such drives.) This has the advantage of reducing how much of the (generally expensive) SSD you're using (which would likely make the SAN admins smile.)
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply