SQL Server Performance Forum – Threads Archive

which requires best performance

Sorry to post twice about the same topic, but i wasn’t completly satisfied with all the answers from my previous post. One was left unanswered. Maybe there’s no right way to answer it, i’d just like some input on the matter. Which parts of my database would require the best performant storage hardware, if multiple (but different) arrays were used in a single server. In other words, which part do i put on my best array, second best array and worst array. To summurize a bit,…my system is mainly 1 very large table, which performs about 50% reads/writes. This table is constantly growing for each client we add to our system. Our growth is about 1 Gig of data per month in the main table. The reads are moslty random during normal business hours and writes are constant 24/7. I have 4 raids in my server, 2 onboards, 1 internal and 1 external. I want to seperate these parts to improve performance of my server: -Transaction Logs (raid 1)
-Indexes (raid 10)
-My main table data file (raid 10) (which constitute 90% in size of the database)
-tempdb data file (raid 10)
-rest of the data files (raid 10) what order of importance would you set these parts for hardware priority? I hope i was clear enough with my explanation, if you require more info, please ask. thanks,
XiNull

Personally, I’d start with placing the log and main table data on their own raid sets. For the rest, I can’t say that I can completely answer your question, but I do have a few questions that I’d look into in your situation:
– is there a lot of tempdb activity?
– how fast are your various RAID sets?
– you mention mostly reads in the day, and writes in the night. how does this relate to the performance? would you prefer faster performance in the day or in the night?

Thanks for the reply, Yeah, that’s for sure, i plan on putting the logs on a raid 1 array and the data files on raid 10 arrays. I’m trying to seperate things as much as possible, but we dont have the budget for SAN’s or for more raid controllers and drives at this moment. I’m trying to do with what i’ve got, and that’s 4 raids, 2 onboard, 1 internal and 1 external SAS As for you questions, 1. It’s tough to say since we’re currently using SQL Server 2000 and when we’ll be switching servers, we’re also switching to SQL Server 2005. As we all know the workings of tempdb is very different from 2000 to 2005. But if i had to guess, i’d say it will be used a lot. 2. I’m only a developer trying to manage a database <img src=’/community/emoticons/emotion-7.gif’ alt=’:s’ /> , as for speed i’d have to check with our tech to get the exact specs of the raids we have for the new server. He’ll be available tomorrow morning. I can get you that info then. 3. No, writes are happening all day long, all the time at constant intervals. Reads are only executed at daytime, when clients access their data for reports. I was trying to explain how i have writes on my DB 24 hours a day, 7 days a week,…but my data’s only being read during business hours. On that note, does anyone know if large indexes from a large table need better/same/less IO performance then the table itself? XiNull

]]>