| Author |
Topic |
|
Hariarul
Posting Yak Master
160 Posts |
 Posted - 2007-08-28 : 01:31:10
|
| Hi experts,We have a huge table with around 250 million records and have implemented SQL server 2005's new table partitioning feature. Now the data seems to be evenly spread across 20 different filegroups ( each 5 GB approx ) for the same table that was occupying 100 GB itself in the PRIMARY filegroup earlier.Still the query response times have not come down drastically but we could see a good improvement in the execution plans now.WE ARE USING RAID 5 IN OUR PRODUCTION ENVIRONMENT. ANY IDEA / THOUGHT ON HOW TO PLACE THE PARTITIONED FILEGROUPS AND THE LOG FILES IN THE RAID 5 (BTW , I'm very new to RAID concepts , any detailed instruction would be helpful ).Any help would be greatly appreciated.Thanks,Hariarul |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2007-08-28 : 01:42:47
|
The most important thing here to remember is that without any proper indexes, the speed will not increase.
E 12°55'05.25"N 56°04'39.16" |
 |
|
|
Hariarul
Posting Yak Master
160 Posts |
Posted - 2007-08-28 : 01:48:20
|
| Thanks Peso. We have indexed the tables properly. The response times are within few seconds as expected. My comment was I didnt find any difference much in performance when compared to the partitioned table and the non partitioned table.Any inputs for my query regarding the RAID 5 ? |
|
|
|
evjo
Starting Member
20 Posts |
Posted - 2007-08-28 : 05:06:04
|
| RAID 5 provides some data redundancy, but it is slow are writing. Reads are not too bad. Since a lot of your time IO is going to be your bottle neck, I try to go for RAID 10. This improves performance greatly for writes and I believe is also faster for reads.An alternative is to split your table into multiple files and put them across multiple disks.Since RAM or CPU could also be a problem I have to say that you should be running perfmon and sql profiler to see if your bottle neck is IO. also the sys.dm_os_wait_statst view will be of a benefit as well.------------------------------------------------------------------------Whenever I see an old lady slip and fall on a wet sidewalk, my first instinct is to laugh. But then I think, what if I was an ant, and she fell on me. Then it wouldn't seem quite so funny. - Jack Handey |
|
|
|
Hariarul
Posting Yak Master
160 Posts |
Posted - 2007-08-28 : 07:59:27
|
| Thanks evjo. |
|
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2007-08-28 : 21:13:36
|
| How many disk arrays does the server have? Better to put log files on separated raid1 or raid10 array. |
|
|
|
Kristen
Test
22859 Posts |
|
|
MichaelP
Jedi Yak
2489 Posts |
Posted - 2007-08-29 : 22:58:39
|
| Kristen - Re Your RAID 5 issueI'd take that up with the vendor your got the array from. Sounds like a bad config there someplace (drivers, bios, etc).<Yoda>Use the Search page you must. Find the answer you will. Cursors, path to the Dark Side they are. Avoid them, you must. Use Order By NewID() to get a random record you will.</Yoda>Opinions expressed in this post are not necessarily those of Microsoft Corp. All information is provided "AS IS" with no warranties and confers no rights. |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2007-08-30 : 04:24:35
|
| "I'd take that up with the vendor your got the array from"It was bog standard Dell server kit. Latest drivers etc. were in place. Its happened with several different machines, and my reading-around-the-issue suggests it is commonplace (contrary to my expectation!)It appears to go something like this:Write, say, 10 blocks to the RAID5 (which in turn implies writing Parity across the Array etc.)Write fails on, say, block 4.Bad drive is locked out. Not sure if the writing of block 4 is completed to the other drives, or rolled back, or what.However, reminder of the Write is aborted.SQL Server detects this using Torn Page detection (make sure you have that turned ON folks - I think it was OFF by default in SQL 7, and thus probably OFF on any system progressively upgraded from SQL 7)What I don't understand is why there is no feedback to Windows that the write has failed because, given that the bad drive is locked out, Windows could just re-issue the Write and it would then succeed.Kristen |
|
|
|
|
Table Partitions & RAID 5.