Where did I put my Sales table?
Filegroups? What can I say! Sometimes I love them and sometimes I just hate them. In theory this should be quite simple and give you benefits – but in my years as a consultant, I have seen so many bad file group designs where the simplicity is traded in for an over complex layout that looks good on paper, but is a nightmare to maintain.
Another scenario that I often come across is misplaced tables and indexes. This happens a lot if the person that created the database layout is not responsible for creating objects afterwards, or if the end-users does not get the proper instructions on how to do things the right way. When this is the case, things can end up messy. I will show you how to query the Meta data, so that you can check if your data is placed in the right filegroup.