Cpt_Picard (3/30/2015)
1) Each data file (mdf or ndf) of a database can be read by only one processor core at a time.
False.
Any thread can issue an IO to any file
Since the older data all resides in the one large mdf the OS is only leveraging one core to access the one file.
False.
I believe I am under utilizing my resources. If that is the case, the only way I can think of to justify more resources would be to distribute the older data from the large mdf across all 4 data files. I would of course then rebuild indexes. This would allow me to leverage all the resources and give better justification for more resources / making splitting the database / upgrading SP and then splitting the database more of a priority.
Probably not.
A DB should be split for one of two reasons:
- IO load. This requires careful design of what data goes into what file with the aim to balance load on the underlying drives and requires that all files are on different physical IO subsystems
- Fast restore. This requires careful design of what data goes into what file with the aim being to bring the critical portion of the DB online as fast as possible in the case of a disaster and the less-critical portions later. This requires full recovery model and a large degree of comfort with more advanced restore options. It can't be done for a Sharepoint DB.
1) Pros? Cons? Alternatives?
Since the entire plan was based on a false premise, I would suggest scrapping the entire thing and removing those extra files you created.
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