SQL Server Performance Forum – Threads Archive

SQL Best practices for Hardware config

When I read that best practices are to have the DB file on one drive (RAID5 for a lot of reads), the logs and the tempDB file and the backups on another drive (RAID1 for a lot of writes), this is confusing me and my network team. When you guys say separate out these files to different "disks" do you really mean "partitions"? Form what I am being told about RAID sets, that you can partition different drives to hold these files separately, but technically they are on the same disk. Is this still okay. For a dev environment, they are proposing the following solution, please le tme know if this follows best practices: Dev Server 1:
2 X 3.0 GHz 533 FSB
4GB RAM
10 Disk Drives in 3 RAID sets:
1st set: RAID1 Mirror 2 drives – 30GB – Holds OS and Page file
2nd set: RAID5 6 drives – 364GB usable – holds DB file, Backups, TempDB
3rd set: RAID1 Mirror 2 drives – 36.4GB – Holds the Logs Is it okay to have a HUGE RAID5 set and split out a few partitions, one partition having the DB, and the other partition having the TempDB and backup? Is this "Best practice"? Another thing that is very weird is that when I go into "Computer Mgmt." on my server it says I have 4 processors, however my network team says it really is only 2 physical processors. What is up with this? Apparently, to go to a 4 processor environment, the servers will not hold enough disks. Please advise. Thanks.

---

When we separate out DB files and log files, we mean on separate physical arrays, not partitions. Using different partitions on the same physical array would negate the performance benefit. But this is an ideal configuration, and not always necessary or practical. For a dev environment, putting everything on one large array would probably not be a performance issue. In fact, that’s what I do for my dev environments. —————————–
Brad M. McGehee, MVP
Webmaster
SQL-Server-Performance.Com

---

Agree with Brand. "Is it okay to have a HUGE RAID5 set and split out a few partitions, one partition having the DB, and the other partition having the TempDB and backup? Is this "Best practice"?" No sence at all. Luis Martin
Moderator
SQL-Server-Performance.com All postings are provided “AS IS” with no warranties for accuracy.

---

I agree with Brad. What the did for Dev is fine. I keep the tempdb database on a seperate RAID array on production servers though if I can afford it. Also, you have two processors hyper-threaded. It appears that you have four this way. Type in hyper-threading on google for more info. MeanOldDBA
[email protected] When life gives you a lemon, fire the DBA.

---

Its not a good practice to partition and separate, not matter what Raid you use. Imagine a 100GB Raid, with 25GB partitions, if i separate for example, data and index,
when a access i must "jump" 25GB, to access one or the other. the best practice its to partition (to reduce fragmentation) and then grow, partition by partition
by this way, the disk heads can concenter its activity in a disk area, disminishing the "latency" time. If you make a lot of partitions and separate the information, you waste, more time, seeking than reading/writing. In other aspects, if your database is write oriented (OLTP), you must erradicate the Raid-5,
its the cheap solution, or mitigate, the slow raid with a good write-cache at the controller level. Raid-5 is usefull, only for Read-Only data for its cheap perfil.

---

There are some exceptions to this with SAN’s cosaco, so be careful on this. On a high-end RAID, sometimes you can get a good performance increase by creating RAID LUN’s on top of several RAID arrays. This in affect spreads IO across all the disks. It depends on what your application is, size of data, etc. It’s always a game with this kind of stuff. MeanOldDBA
[email protected] When life gives you a lemon, fire the DBA.

---

From analysis, we have more reads than wries. I guess this is why RAID5 is okay. We have settled on a new Dev server, the specs in my original post. I am requesting that we do not Hyper-thread the two processors to 4 because I don’t see any glaring benefit. However, is it possible to hyper-thread processors once the system is built and being used? Conversely, is the opposite true as well? If I have a 2 hyperthreaded processors, can I remove the hyper-threading. I have found out from Cognos a few best practices that I am implementing. I can activate all the queries for multi-processing, but it only uses max of 2, but if I run two processes at the same time, I can use 4 physical processors. However, it only uses physical and not hyperthreaded processors. My prod system is only a 2X, but I hope to ask for 4X for future system. I am still a little confused about memory. I am asking for 4GB for my replacement dev and 16GB for all my future prod machines next year. Cognos says that it is very memeory intensive, and I know SQL server uses a good amount as well, but am I asking for too much? Is there such thing as too much memory? Is there a ratio between number of processors, size of DB, speed of processors, RAM, etc? I want to make sure I have enough memory, but don’t want to burn budget if that money would be best spent elsewhere. You guys are the best. Thanks for the help. Someday (and it will take time) I will maybe get close.

---

It depends on the BIOS that your machine has. Some have an option that lets you disable HTT, while other don’t. If you cannot find such an option, it is possible that a BIOS update will add it. You should consult this with your motherboard’s vendor. — Marek ‘chopeen’ Grzenkowicz, MCP
Poland

---

RAID 5 should be fine for Dev. It’s cheaper also. I have all my dev server built with RAID 5. We have six instances of dev, QA, etc though, which is another story, so space cost was a big issue.

You should be able to turn it off. It will require a reboot. Other than that, it’s not usually a big deal. We switched back and forth on our servers quite a bit when we first got them. If the server you’re looking at doesn’t support this, I would seriously consider a different server.

You might want to look at just buying a four-processor with 2 processors in it still. Whenever you upgrade, the sheer increase in processing power on 2 processors will be significant as Joe pointed out. If you have done your homework and know 2 will work, try that route. If you have significant growth, it makes it easy to expand. You can also explore options such as multiple instances to increase procedure cache usage, etc.

I dont know that you can have too much RAM. The big thing if you’re running both COGNOS and SQL Server on the same server is the memory contention for the base 2-3gb of RAM between Cognos and SQL Server. This might be a fun configuration to manage until you get it tuned. There’s a million opinions on your question about ratio. Some people say you should have enough RAM to fit your DB into RAM which is just insane. A lot of people like a minimum of 1GB per processor, which I think is reasonable. On speed of processors, get as fast as you can buy without killing yourself. I’m currently buying all my servers with 2.8 because of the price break. It gives me the best performance currently for the price. The 3.0 are considerably more expensive still and don’t offer a comparable performance increase. I would say your level of cache on the processor is just as important as speed btw.

Good luck on this. If you get this right, you can see your environment become soooo much better because they have the infrastructure they need to allow efficient development. MeanOldDBA
[email protected] When life gives you a lemon, fire the DBA.

---

]]>