At what point should we introduce multiple datafiles/file groups? Right now the DB is about 2 TB and a very active ERP. We are seeing massive deadlocks and poor performance. I am wondering, at point point do I know if adding a second Data file or File group is the answer?
closed as too broad by Colin 't Hart, Lennart, mustaccio, hot2use, Marco May 7 '18 at 9:59
Please edit the question to limit it to a specific problem with enough detail to identify an adequate answer. Avoid asking multiple distinct questions at once. See the How to Ask page for help clarifying this question. If this question can be reworded to fit the rules in the help center, please edit the question.
Improvement (or not) will depend on your storage system. Multiple files can help performance if it increases IOPS and bandwidth with additional HBAs and drives. Multiple filegroups allows more granular placement of database objects. That will generally help in specialized use cases, such as separating sequential and random IO patterns on spinning media.
That said, I don't expect storage tuning to address the symptoms of "massive deadlocks and poor performance". Query and index tuning is the first thing I suggest you look at.
You can't guess, you have to run a full DMV analysis. This will tell you things like : which QUERIES or stored procedures are using the most disk I/O, which are using the most CPU, how much memory is used by each database, suggested indexes, lagging queries, ... on and on.. all kinds of CRITICAL INFORMATION. Here is a GREAT video from Glenn Berry: https://www.youtube.com/watch?v=AWFg6KdVoh4
If you don't know what DMV's are it doesn't matter, he explains it in the video. Here is a link to his latest DMV's. https://www.sqlskills.com/blogs/glenn/sql-server-diagnostic-information-queries-for-may-2018/ These queries are easy to use, just watch the video and be careful, he has done all the work for you and teaches you how to search for issues.
Good luck and please let us know your progress or send me a message.