November 4, 2021 at 2:47 pm
I'll have to think about that method some more, don't have time right now.
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
November 4, 2021 at 2:53 pm
Of course that forces the entire table to be rewritten every time. In a typical rebuild, often large parts of the table do not need rewritten because they haven't been modified since the last rebuild. You may want to compare the total I/O, including log activity, of the separate-filegroup method before implementing it in a lot of places.
ALL rebuilds force the entire table to be rewritten every time.
--Jeff Moden
Change is inevitable... Change for the better is not.
November 4, 2021 at 2:58 pm
ScottPletcher wrote:Of course that forces the entire table to be rewritten every time. In a typical rebuild, often large parts of the table do not need rewritten because they haven't been modified since the last rebuild. You may want to compare the total I/O, including log activity, of the separate-filegroup method before implementing it in a lot of places.
ALL rebuilds force the entire table to be rewritten every time.
Yes, sorry, I should have referenced REORGs, which often do not.
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
November 4, 2021 at 2:59 pm
Of course that method does not work with any cross-table constraints on the table, since constraints must be in the same filegroup (IIRC).
Try it and find out that the method doesn't bother FKs at all because the Primary Key Constraint is nothing more than meta-data backed up by a UNIQUE INDEX on a NOT NULL column.
--Jeff Moden
Change is inevitable... Change for the better is not.
November 4, 2021 at 4:12 pm
I still have to think about this. We have hundreds of dbs. Say anywhere from 3 to 24 large tables in each db, a separate fg for each. Presumably you'd want at least 2 files per filegroup, perhaps 3 if the data were very large. Hundreds more files. Some management issues.
Still thinking through this, if you restore a db, any existing scripts with affected fg names in them would be invalid, as new fgs would no longer exist and the old fg names would. Now, our shop pretty much never restores over a prod db, but we do it quite frequently in non-prod environments, to reset data, rebuild some data, etc.. A fair amount of stuff to consider before attempting that.
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
November 4, 2021 at 4:26 pm
You're presuming an awful lot. Why would you want "at least 2 files per filegroup" for a single table?
Also, I can see some instances where you might have specific reason to hardcode file group and maybe even file names but, and maybe it's just me, I cannot imagine that's the norm. It might still be very worthwhile to make the hardcoded changes to prevent having huge amounts of unwanted freespace left behind after rebuilds occur. There's also the extreme advantage that the content of the indexes stand a really good chance of having huge segment sizes because they're the only ones on the file and don't have the problem of interleaved extents like you would on the Primary file group.
As with all else, "It Depends" and "Almost nothing is a Panacea".
--Jeff Moden
Change is inevitable... Change for the better is not.
November 4, 2021 at 6:27 pm
You're presuming an awful lot. Why would you want "at least 2 files per filegroup" for a single table?
To spread the I/O out, the typical reason for multiple files in a filegroup. Are you saying (nearly) all your filegroups have only 1 file? I'm not sure that's a good idea for large+ tables.
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
November 5, 2021 at 4:41 pm
Jeff Moden wrote:You're presuming an awful lot. Why would you want "at least 2 files per filegroup" for a single table?
To spread the I/O out, the typical reason for multiple files in a filegroup. Are you saying (nearly) all your filegroups have only 1 file? I'm not sure that's a good idea for large+ tables.
I've not actually tested that for this particular server. Inherently, I AM using more than one file group... all this stuff used to live only in the PRIMARY File Group. 😀
It's a good suggestion. I'll do one of the tables and see if it makes a difference. I suspect it won't simply because everything resides on high performance SSDs but it's worth a test because "The answer is always 'No' unless you test". 😀
--Jeff Moden
Change is inevitable... Change for the better is not.
November 5, 2021 at 4:42 pm
As a bit of a sidebar, wouldn't adding more files add to the problem you spoke of previously?
--Jeff Moden
Change is inevitable... Change for the better is not.
November 6, 2021 at 4:52 am
As a bit of a sidebar, wouldn't adding more files add to the problem you spoke of previously?
I'm not sure which specific "problem" you're referring to.
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
November 6, 2021 at 3:12 pm
Jeff Moden wrote:As a bit of a sidebar, wouldn't adding more files add to the problem you spoke of previously?
I'm not sure which specific "problem" you're referring to.
The one below, Scott. And, presuming the large tables in those databases are not partitioned, do any of THOSE currently have multiple files for the I/O advantage that you're talking about in yet another post in this thread?
Still thinking through this, if you restore a db, any existing scripts with affected fg names in them would be invalid, as new fgs would no longer exist and the old fg names would. Now, our shop pretty much never restores over a prod db, but we do it quite frequently in non-prod environments, to reset data, rebuild some data, etc.. A fair amount of stuff to consider before attempting that.
The bottom line here is that the "Swap'n'Drop" Index Maintenance Method I'm talking about that I use for some of my larger tables is to prevent having a shedload of possibly unwanted space left in a filegroup (usually, the PRIMARY filegroup) and to possibly seriously increase the segment size (contiguous extents) of the clustered index.
All the other stuff you're talking can be taken or left or is already a concern if you change something having to do with your file names and file groups if your code can't automatically handle file name MADs. If it's too much of a bother, then don't use the method I'm using with the understanding that you're still going to suffer from unwanted leftover free-space.
Like any other change, "It Depends" and "Must Look Eye" for your own scenarios.
--Jeff Moden
Change is inevitable... Change for the better is not.
November 8, 2021 at 9:29 am
@jeff.Moden
In previous organisations I took to having a Primary, Data and Index filegroup and put all my clustered indices in the Data filegroup and non-clustered in the Index filegroup. I had an idea that this improved performance by a marginal (but measurable) amount when retrieving the definitions of objects / data from system tables etc. I haven't been able to implement that at my current workplace, and, to be honest, haven't felt the need to. That may be because of our oversized hardware.
Moving each large index to a separate filegroup would be technically possible but would create a management overhead that would be hard to justify (10 separate data drives). At present we are looking to break the primary database down by the workloads supported and move those workloads out to a separate database (or three!).
This is a primarily OLTP database (our OLAP database is completely separate) so we don't use temporal partitioning extensively.
November 8, 2021 at 3:45 pm
ScottPletcher wrote:Jeff Moden wrote:As a bit of a sidebar, wouldn't adding more files add to the problem you spoke of previously?
I'm not sure which specific "problem" you're referring to.
The one below, Scott. And, presuming the large tables in those databases are not partitioned, do any of THOSE currently have multiple files for the I/O advantage that you're talking about in yet another post in this thread?
Yes, all my filegroups for non-trivial tables have more than one file in them (for others, esp. non-DBAs who are reading this, keep in mind that the log does not go into a filegroup; there is no point in having multiple log files, since log files only write sequentially).
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
November 9, 2021 at 6:08 pm
Jeff Moden wrote:ScottPletcher wrote:Jeff Moden wrote:As a bit of a sidebar, wouldn't adding more files add to the problem you spoke of previously?
I'm not sure which specific "problem" you're referring to.
The one below, Scott. And, presuming the large tables in those databases are not partitioned, do any of THOSE currently have multiple files for the I/O advantage that you're talking about in yet another post in this thread?
Yes, all my filegroups for non-trivial tables have more than one file in them (for others, esp. non-DBAs who are reading this, keep in mind that the log does not go into a filegroup; there is no point in having multiple log files, since log files only write sequentially).
Cool. Did you constrain to one table per filegroup or do you have a mixed bag for those? If you've constrained to one table (Clustered Indexes), you might already be golden with the Swap'n'Drop method for rebuilding the indexes except that you'd have to change some existing code to incorporate the actual Swap'n'Drop methodology to save on large amounts of unwanted free space after REBUILDs, and it wouldn't take much.
--Jeff Moden
Change is inevitable... Change for the better is not.
November 9, 2021 at 7:03 pm
ScottPletcher wrote:Jeff Moden wrote:ScottPletcher wrote:Jeff Moden wrote:As a bit of a sidebar, wouldn't adding more files add to the problem you spoke of previously?
I'm not sure which specific "problem" you're referring to.
The one below, Scott. And, presuming the large tables in those databases are not partitioned, do any of THOSE currently have multiple files for the I/O advantage that you're talking about in yet another post in this thread?
Yes, all my filegroups for non-trivial tables have more than one file in them (for others, esp. non-DBAs who are reading this, keep in mind that the log does not go into a filegroup; there is no point in having multiple log files, since log files only write sequentially).
Cool. Did you constrain to one table per filegroup or do you have a mixed bag for those? If you've constrained to one table (Clustered Indexes), you might already be golden with the Swap'n'Drop method for rebuilding the indexes except that you'd have to change some existing code to incorporate the actual Swap'n'Drop methodology to save on large amounts of unwanted free space after REBUILDs, and it wouldn't take much.
No, I don't have a separate filegroup for each large index. You're in a much simpler situation than most people. I have hundreds of large indexes. Trying to manage a separate filegroup for each one would be prohibitive. And it's not needed. It's very rare that an index shrinks considerably; it's even more rare that one needs to immediately reclaim that space. Other indexes will use the space (or even that same index over time).
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
Viewing 15 posts - 31 through 45 (of 45 total)
You must be logged in to reply to this topic. Login to reply