May 5, 2015 at 8:06 am
RonKyle (5/5/2015)
As a minimum it can allow for the separation of tables and indexes to separate disks and doesn't prevent using standard backup and restore procedures.
Yes, you can do that. Has it achieved anything?
I'm not fond of making decisions because 'that's the way it's done'. I make design decisions based on expected usage or measured stats or requirements.
If there's a requirement that the core of the DB be restorable within 10 minutes and the historical data can be restored later, then I'll split filegroups based on that requirement.
If there are performance problems and I've measured and confirmed that they are IO-related and can be alleviated by moving some tables into a filegroup on a separate array (of separate drives, possibly with a separate IO path), then I will do so, carefully selecting the tables to move and ensuring that moving the tables around has had the desired effect.
Thing is, if I've gone and put tables in filegroup 1 and indexes in filegroup 2 and someone comes with the requirement that the core of the system has to be restored first and historical data later, then I still have to go and move tables and indexes around to satisfy that requirement.
With respect to Steve, the example in the article is a poor one, as the script carefully sets a bunch of properties to their default values. All it's done is add a lot of extra code with no gain.
p.s. I'm not a DBA.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
May 5, 2015 at 8:16 am
Yes, you can do that. Has it achieved anything?
I'm not fond of making decisions because 'that's the way it's done'. I make design decisions based on expected usage or measured stats or requirements.
I agree that making decisions because 'that's the way it's done' is not a good start. But a lot of times we don't know exactly what the expectations are going to be. I prefer to ensure the designs will accomodate changes as easily as possible. I've had to work with VLDBs that consist of a single primary file. Running a CHECKDB is only possible when the backup is restored to the test environment. Less than optimum. But changing it now would involve time and testing that isn't going to be made available. Had the original designers had to knowledge and foresight to separate the items into multiple file groups, the CHECKDB could be done on a rotating file group basis. I can't move items to multiple disks nor can I consolidate tables that rarely or never change to a file group that could be exempted from the CHECKDB. But if that file ever gets corrupt, I'm on the hook to fix it.
May 5, 2015 at 8:34 am
Iwas Bornready (5/5/2015)
We've been caught by the default problems too. We now spell out some of the settings at the top of each of our stored procedures.
I wrote an article that contains a script that checks about 100 server and database settings. When you lock into specific database settings, your developers can have clarity and stability and know how to write their t-sql code. Your develops can also write less code, only including ANSI overrides in the scripts that require them. I recommend creating a custom version of this script for each of your databases/applications.
http://www.sqlservercentral.com/articles/Configuration/109089/
May 5, 2015 at 8:37 am
RonKyle (5/5/2015)
Running a CHECKDB is only possible when the backup is restored to the test environment. Less than optimum.
Personally that's my preferred way of doing it on large, busy databases as it means I can offload the entire operation to another server and test the backup in the same process, but each to their own.
Had the original designers had to knowledge and foresight to separate the items into multiple file groups, the CHECKDB could be done on a rotating file group basis.
Even in a single filegroup you can split CheckDB up. CheckDB is CheckAlloc, CheckCatalog and then CheckTable on each table in the database (not sure about indexed views). You can categorise the tables into sets and night 1 CheckAlloc, night 2 do the CheckCatalog then nights 3-7 CheckTable on that set of tables. That lets you spread CheckDB out over a week doing a little bit each night.
nor can I consolidate tables that rarely or never change to a file group that could be exempted from the CHECKDB.
Gah! No, don't do that.
Corruption is 99% of the time an IO subsystem problem. A misbehaving IO subsystem isn't going to check how often a table is updated before it mangles the underlying file. A read-only filegroup can't be excluded for the same reason.
You don't want to find out 6 months after the fact that the IO subsystem zeroed out a section of a table that's only used in the year end process, especially if your backup retention is less than 6 months
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
May 5, 2015 at 8:39 am
RonKyle (5/5/2015)
RonKyle (5/4/2015)
--------------------------------------------------------------------------------
But if the filegroup would become corrupt, I've made it harder to recover.
Not necessarily. Depends on the type of corruption. If the system tables get hit, that's a full database restore. If it's a couple of pages, that's just a page restore. If it's in nonclustered indexes, that's a drop and recreate of the index.
OK. It's possibly made it harder to restore. Better to reduce the risk and maintain future flexibility in my view. The hesitation from the top tier DBAs is surprising to me. I assumed this was a fairly normal undertaking and in line with the intent of the article advising more planning and thought when creating a new database. As a minimum it can allow for the separation of tables and indexes to separate disks and doesn't prevent using standard backup and restore procedures.
I don't think there is a huge amount of disagreement here. Basically your systems benefit from this, while others may not. I understand that your methods work in your environment, but may not be a best practice across the board. For example a lot of my databases are less than 5GB. All of mine are small enough that I can run integrity checks daily, and reindex weekly. Some of mine have multiple file groups, most do not.
I think the point of this discussion is that one needs to know when this can help, and when it can't.
Dave
May 5, 2015 at 8:40 am
Gah! No, don't do that.
Corruption is 99% of the time an IO subsystem problem. A misbehaving IO subsystem isn't going to check how often a table is updated before it mangles the underlying file. A read-only filegroup can't be excluded for the same reason.
My understanding is that if I have a file group with tables that rarely or never change, and I check only if there is a data change, then if the check is okay there is no need to check again unless there's another data change. Are you saying that's not so?
May 5, 2015 at 8:42 am
I'm with Gail, somewhat on this. It's not that I think it's harder for me to restore or recover, but it is slightly less readable and confusing. That's the concern I have, not because of my skills, but because I may use a sysadmin or because the next person is an accidental DBA, and they have trouble restoring this.
It is a little "aiming for the lowest common denominator", but only where it's not a problem. Personally I haven't seen this help me in any but the largest databases, and in those, we are trying everything possible not to completely restore because it's a significant amount of downtime to do so. I see this as a level of complexity that doesn't buy me anything in most cases, and can potentially hurt the business. I just don't see the corruption item as being likely or helpful here. In small database, it's just a full restore, likely not on EE, so this doesn't help.
Is this lowering the bar and not teaching people? Perhaps. There certainly is an argument there, but if I want to get DBAs to do something better, separating filegroups isn't where I'd start. I don't see that as enough of a win.
With respect to this being a bad example. How do you know the defaults are the defaults? That's the point of this. You reset things to the default, precisely because you can't count on those being defaults and because someone might have been "experimenting" with model and reset things.
I do think the example would have been better with checks before making changes, and "print" statements to show what had changed, but in trying to keep this down to a short read, I left those out. My apologies for that.
May 5, 2015 at 8:49 am
I'm with Gail, somewhat on this. It's not that I think it's harder for me to restore or recover, but it is slightly less readable and confusing. That's the concern I have, not because of my skills, but because I may use a sysadmin or because the next person is an accidental DBA, and they have trouble restoring this.
Perhaps. But there was a great article recently on this site that talked about striping backups. I tried it on our VLDBs, and it reduced the backup and restore times from more than three hours to about 90 minutes. This may make it more difficult for a future DBA to understand because the technique isn't even found the the SQL Server Backup and Restore book (at least not the edition I have). The indexing improvements I've made on another OLTP database will also be more difficult to understand, but I don't receive complaints from users anymore. A lot of this can be solved with careful documentation, and I'm careful to document everything, eventually at least, because if nothing else I have found it helps me in the future.
May 5, 2015 at 8:55 am
RonKyle (5/5/2015)
My understanding is that if I have a file group with tables that rarely or never change, and I check only if there is a data change, then if the check is okay there is no need to check again unless there's another data change. Are you saying that's not so?
It is definitely not so.
SQL does not corrupt its own databases. Corruption is an external process, drive errors, RAID driver problems, SAN controller bugs, misbehaving filter drivers, etc. None of those are going to wait until a change is made to a table before they mangle that table. None are going to honour a read only flag within SQL, etc.
You need to check your entire database within your backup retention period, so that if something gets corrupted 10 minutes after CheckDB completes, you have the option of restoring something (page, filegroup, database) to fix it.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
May 5, 2015 at 8:58 am
It is definitely not so.
Good to know. Thanks. I would have had a false sense of security.
May 5, 2015 at 9:05 am
RonKyle (5/5/2015)
I'm with Gail, somewhat on this. It's not that I think it's harder for me to restore or recover, but it is slightly less readable and confusing. That's the concern I have, not because of my skills, but because I may use a sysadmin or because the next person is an accidental DBA, and they have trouble restoring this.
Perhaps. But there was a great article recently on this site that talked about striping backups. I tried it on our VLDBs, and it reduced the backup and restore times from more than three hours to about 90 minutes. This may make it more difficult for a future DBA to understand because the technique isn't even found the the SQL Server Backup and Restore book (at least not the edition I have). The indexing improvements I've made on another OLTP database will also be more difficult to understand, but I don't receive complaints from users anymore. A lot of this can be solved with careful documentation, and I'm careful to document everything, eventually at least, because if nothing else I have found it helps me in the future.
I think djackson said it well. One needs to know when this matters.
VLDBs are different than many others. I've avoided them precisely because they are a pain and things need to be different. If you manage those, you have a much different set of issues and responsibilities than the 5GB databases. Heck, many of the dbs I've managed were < 1GB, so complexity doesn't matter. In those cases, striping doesn't help. It hampers restores when I need remote hands.
I've had 3 clients that lost data because they couldn't get their hands around the fact they needed all stripes, despite the fact I had explained it and emphasized the issue. Until someone goes through the issue, they don't always get it.
Look at your impression of checkdb and static data. I'd say that's the type of thing I need to emphasize and teach much more than separating out filegroups. We all have holes and misunderstandings in our knowledge. That's why we should try to educate others, and debate.
Not saying you're at all wrong with this, Ron. Just that I'm not sure that's the area I'd worry about when I want to build better DBA practices.
May 5, 2015 at 9:13 am
Heck, many of the dbs I've managed were < 1GB, so complexity doesn't matter. In those cases, striping doesn't help. It hampers restores when I need remote hands.
I agree. I don't stripe the smaller databases. They are small enough to do both the CHECKDB and backup in one file sequentially and still be done within an acceptable time.
May 5, 2015 at 9:27 am
Steve Jones - SSC Editor (5/5/2015)
I'm with Gail, somewhat on this. It's not that I think it's harder for me to restore or recover, but it is slightly less readable and confusing.
For me it's less about the readability and more about the value.
Yes, I could do this, I could split a database into multiple filegroups, but then if the client came to me and asked what benefit they would see, I'd be hard pressed to give an answer.
Because just putting tables on filegroup 1 and indexes on filegroup 2 is not going to improve performance. Splitting a DB for performance reasons requires that IO is the bottleneck, that the split does spread the load out, that the files have separate IO paths or at least separate drives not just slices from the same SAN lun and that just putting the entire DB onto SSD isn't a better solution. Tuning queries and adding memory also have to be considered
And because splitting for easier restores is hard and requires a good understanding of what tables are used together to ensure that a partially restored DB is usable. It may also require longer log backup retention. If it's required, fine, but there has to be some analysis and work done first.
Splitting for easier checkDB isn't something I'd suggest, because I can restore elsewhere and run checkDB or do CheckTable in sets instead without moving the DB around and corruption should be so incredibly rare that I'm not going to base a design on the need to restore part of my DB because of corruption. And besides, there's always the page restore option if it's just data/index pages affected.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
May 6, 2015 at 6:27 am
I despise defaults. It is not necessarily the "experts" that get caught out but it is a problem when ANYONE in the team gets caught out. More so with SQL Server than 3GL programming languages, upgrades can cause an issue that no one is aware of when there is no documented technical requirement for any particular setting because a default is assumed.
Gaz
-- Stop your grinnin' and drop your linen...they're everywhere!!!
Viewing 14 posts - 16 through 28 (of 28 total)
You must be logged in to reply to this topic. Login to reply