I have a questions about filegroups in SQL Server and geodatabases. Is there any performance or other benefit to have multiple filegroups for things like vector data, indexes, A/D tables compare to have 1 filegroup with many files in it? I have check the ESRI help document and search the forums but it mute on what is better. It tells you how to set the dbtune keywords to use different filegroups but not if you should use them or how many. If you go into some of the extensions such as data reviewer and workflow manager, they say to use 10 different filegroups (see Creating data files for the Reviewer workspace in SQL Server—Help | ArcGIS Desktop ).
So anyone have experience with what is a best practice? Should I use 1 filegroup with say 10 files. Or should I have small set of filegroups with 2-3 files each for things like vector, indexes, and versioning tables. Or should I use all 10 different filegroups as describe in the help doc above?
Some notes about our system. We are using SQL Server 2016. We are different drives for the data files and log files but all data files will be on the same drive. We are using synchronize AlwaysOn with 2 replicas. We will be using ArcGIS 10.4.1. Based on some tests we estimate our database will be roughly 45-60GB when everything is load. We will have versioning, archiving, and topologies in the database.