DATA FILES AND CPU's | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

DATA FILES AND CPU’s

Does a configuration of 1 data file for each Server CPU increase performance over 1 data file for all CPU’s? In other words, on a server with 8 CPU’s, would the database be better configured using 8 data files @100MB each rather than 1 datafile @ 800MB?
I think data files should be distributed based on the number of physical disks you have rather than number of CPUs. For example, if you have 4 individual disks (or arrays) mounted as 4 different volumes, then create 4 file groups in your database with each file group on each disk and then distribute the objects across these file groups which can give you better performance.
Oppinable question.
I think if you have 8 databases and, just and example, 3 are more used than rest, may be would be better to have 8 than one.
But if all are used in same way, I believe is better to have only one.
I thinking in how SQL use memory, because (I think) is a memory problem no CPU problem. Like I said, oppinable, so wait for other members. Good Point Chakry, I read your post after write.
Luis Martin
Moderator
SQL-Server-Performance.com All postings are provided “AS IS” with no warranties for accuracy.
I was thinking that perhaps a separate thread would be used for each of the 8 data files rather in the example I used, rather than just 1 thread for a single file.
From maintenance point of view, one datafile.
How about, disk (Chakry post) and memory?
Luis Martin
Moderator
SQL-Server-Performance.com All postings are provided “AS IS” with no warranties for accuracy.
A seperate thread won’t be used. What you need to worry about is what others have alluded to here. You need to have the data spread across as many disks and I/O bandwidth as possible. If you can achieve this better, and have the money to implement it, then create more files. Otherwise, I wouldn’t do it. The memory utilization and processor threads won’t really be affected enough to make any difference. MeanOldDBA
[email protected] When life gives you a lemon, fire the DBA.
Somewhere I have a document from EMC where it states that it’s better to spread one single filegroup across as much disks as possible than to use multiple filegroups spread on few disks. IBM has also some empirical studies confirming this in their Redbook section. While this is related to DB2 I think it also holds true for SQL Server. –Frank
http://www.insidesql.de

That’s true about almost all disk subsystems Frank. <img src=’/community/emoticons/emotion-1.gif’ alt=’:)‘ /> The more you spread your IO out, the better distribution you get across the disks. There is a balance point though, depending on what type of RAID you are using on which system. Almost all the systems have "breakpoints" where the RAID type becomes less efficient if you go above or below a certain amount of disks. Once you reach that point, it can become faster to use multiple filegroups. <br /><br />Of course, on many of the EMC and IBM products, you can get around this and even increase performance more by creating a LUN that’s spread across multiple RAID groups and thus even more disks. At this point, the usability of your single filegroup greatly increases. It’s hard to say on a forum though without knowing the hardware, setup of the databases, etc.<br /><br />MeanOldDBA<br />[email protected]<br /><br />When life gives you a lemon, fire the DBA.
Yes, the most important word in here is "can". <br />My point was that it seems that answers (in general, nothing particular here) advocating the use of multiple filegroups with SQL Server are too quickly given. <br />Performance tuning advises you can’t take off the shelf. There are some general recommendation and mabye guidelines, but the by far bigger part depends on the environment in case. To a certain degree it’s a kind of trial and error, till you find a configuration that suits you.<br /><br />Off-topic: Best thread on this was one guy from Asia asking if 256 MB RAM will be sufficent for SQL Server running together with IIS on a PDC computer. The answers were funny, but also polite [<img src=’/community/emoticons/emotion-1.gif’ alt=’:)‘ />]<br /><br /><br />–Frank<br /<a target="_blank" href=http://www.insidesql.de>http://www.insidesql.de</a><br />
<img src=’/community/emoticons/emotion-1.gif’ alt=’:)‘ /> I can imagine. I have 2005 Beta2 and IIS running on a virtual server with 256mb of RAM. lol It’s surprising how reliable it’s been so far.<br /><br />MeanOldDBA<br />[email protected]<br /><br />When life gives you a lemon, fire the DBA.
Hi, I’ve done some DBA work in oracle, they allways recommend to split data from indexes in different tablespaces (Filegroups) and even so to split them in datafiles.
Is this advisable ?
BTW, are the Avg Disk Queue Read and Write counters reliable ? Saludos, Pablo
Same recomendations for SQL Server.
The counters are reliables. Igualmente,
Luis Martin
Moderator
SQL-Server-Performance.com All postings are provided “AS IS” with no warranties for accuracy.
Is there any way to monitor the IO of the server separating the index from data ?
Basically I want to know if I would get more performance by splitting index from data or by separating the most used data and indexes from the least used. I have a fixed amount of hard disks I can use, Just want to use them the best way I can. Saludos, Pablo
If data and Index are in differents drives, you can see using Performance Monitor, each disk I/O. Luis Martin
Moderator
SQL-Server-Performance.com All postings are provided “AS IS” with no warranties for accuracy.
There are several ways to request I/O statistics from SQL Server such as the System Statistical functions, sp_monitor, and fn_virtualfilestats. Each method has its advantages and disadvantages. http://www.sql-server-performance.com/performance_monitor_counters_io.asp
http://www.sql-server-performance.com/performance_monitor_counters.asp I find that the most useful technique is to gather statistics during peak usage at one-minute intervals. The key indicators are the I/O operations per second and the IoStallMS/Op. And alsoinvestigate other ratios for your server. Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
I know how to monitor IO counters, but what I really like to know is when I have the data and indexes in the same file in the same disks, how can I tell what percentage of the I/O is using each .
Saludos, Pablo
No way in same file and disk.
Luis Martin
Moderator
SQL-Server-Performance.com All postings are provided “AS IS” with no warranties for accuracy.
You can try to differentiate them in order to assess the performance. Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
]]>