Maximum # of MSSQL 2000 datafiles | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

Maximum # of MSSQL 2000 datafiles

During an ‘ask the expert’ sessions at SAPAdmin2005, I was told that we should keep the number of sql datafiles to less than 24 in our production SAP environment.
Can anyone confirm this number or offer documentation that discusses an optimum/maximum number and the pro/cons of each? Thanks!
BD

I’m not aware of any "general recommendation" regarding the number of files. 24 already seems to be a fairly large number. Anyway, I think that largely depends on each individual environment. Who made that statement? SAP or Microsoft? —
Frank Kalis
Microsoft SQL Server MVP
http://www.insidesql.de
Heute schon gebloggt?http://www.insidesql.de/blogs

Thanks Frank,
The statement was made by a Microsoft sql expert. I reached out to him yesterday and he basically said the same thing. Ideally, he likes to see a 1:1 or 1:2 ratio of datafiles to core cpus on servers up to 8 cpus. Above that, it becomes an issue of managability. I just wanted to see if anyone else had additional thoughts. BD
Hm, that’s interesting. I think I’ll ask this in the private MVP newsgroup. I’ve bookmarked this thread and will respond when I knwo more. [<img src=’/community/emoticons/emotion-1.gif’ alt=’:)‘ />]<br /><br />–<br />Frank Kalis<br />Microsoft SQL Server MVP<br /<a target="_blank" href=http://www.insidesql.de>http://www.insidesql.de</a><br />Heute schon gebloggt?<a target="_blank" href=http://www.insidesql.de/blogs>http://www.insidesql.de/blogs</a><br />
the number of data files is more dictated by your disk layout,
if you have a very large number of physical disks, and you don’t like something about the raid array capabilities, you might build array groups of 4-7 disks,
suppose you had 210 disks in 30 array groups for data, then this would dictate 30 data files it does get somewhat difficult, i saw someone with 300+ data files.
the real problem was the restore on to another system, where the file path for each file must be changes,
hence 300+ WITH MOVE logical name file name appendages,
when used with LiteSpeed, there was a 32K total string limit, so we had to trim the file path lengths to get the total down
]]>