April 4, 2010 at 8:07 pm
We have been experiencing significant hardware problems with an older sql server install, and our IT guy decided that we should move the server onto our new virtualization platform.
I have been using one virtual server on this platform for a while, and it has been excellent fast and i have an sql express 2008 recieving replicated data there and again it is superb.
The problem is that the main sql server required more space than the good datastore has, so it is on an older raid 5 datastore eww.
If I want to be able to get some kind of performance out of this system, is there anything that I can do, or is it doomed until I can persuade our people to get a better set of drives?
I was considering if splitting into many smaller data files would help?
Any thoughts would be appreciated.
Regards
Martin
April 4, 2010 at 8:23 pm
If the disk are not providing adequate performance, it does not matter how you slice up the database files. They are still working on the same disk.
We have been experiencing significant hardware problems with an older sql server install, and our IT guy decided that we should move the server onto our new virtualization platform.
Are you the DBA or is the "IT guy" the DBA? Was adequate testing done prior to moving the database to justify that it was going to perform to expectations in virtual environment?
If I want to be able to get some kind of performance out of this system, is there anything that I can do, or is it doomed until I can persuade our people to get a better set of drives?
What indications are you going by that it is having performance issues? Is the VM host and guest configured optimally?
Shawn Melton
Twitter: @wsmelton
Blog: wsmelton.github.com
Github: wsmelton
April 4, 2010 at 8:25 pm
The Rambling DBA: Jonathan Kehayias
Good article to read...
Shawn Melton
Twitter: @wsmelton
Blog: wsmelton.github.com
Github: wsmelton
April 5, 2010 at 7:56 am
Unless you have a BUNCH of spindles, many smaller files will actually be SLOWER due to head thrashing on the disk drives. Not a good solution there. How about allocating more RAM for the VM?? That is likely the single most important thing you can do here. I might also recommend checking for OS-level file fragmentation on the RAID drive. Oh, and did you do a file IO stall analysis to see WHAT file or files are slow and whether or not it is reads or writes (or both) that are slow?
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
April 5, 2010 at 10:38 am
Thanks for all your replies, I decided to call this first attempt, a failure, and reverted back to the standalone hardware, I can provide more details about how the system is setup later, although I think I will do some more reading before we start to embark upon this task again.
I do however have a couple of questions regarding the setting up the of the server I have.
Currently it is on Windows server 2000, is there any benefit in changing to 2003?
I have 4 33gb disks and 4 66gb disks, the current config is two 33s in raid 1 for the system and 3 66gb in raid 5 for the data partition, which I know is not optimal
What would be the better solution?
I had been considering:
2x33 raid 1 for system
2x33 raid 1 for logs
4x66 in raid 1 + 0 for the data
although would it be better to use 2x66 for data and 2x66 for indexes both in raid 1?
Thanks for all the help
Martin
p.s. I am the closest thing there is to a DBA for sql server in our company, although I have been supporting this for a while (5+ years), I have no formal education in administration.
April 5, 2010 at 10:52 am
The biggest advantage for going to Server 2003 over Server 2000, is that 2000 is on life support as MSFT, 2003 is in the ICU. If you are going to upgrade, you really need to be considering the current OS's, not 7 year old operating systems.
As for SQL, without out knowing what you system is used for, how frequently it's hit, how much memory you have, etc... anything at this point, would be speculation. But I will affirm what the other posters have said, if you are just going to partition of physical drive into logical drives, don't even waste your time
/* ----------------------------- */
Tochter aus Elysium, Wir betreten feuertrunken, Himmlische, dein Heiligtum!
April 5, 2010 at 11:27 am
Well the configurations I mentioned just above are all based on physical disks. I'm pretty certain the server is 32bit technology. FYI this company is excessively tight with money 🙁
For just now I have stopped the virtualization.
I was told that the disks we were on were iSCSI operating on a dedicated 1gb nic, I have no idea if that is good or not, however my IT man thinks is the best way to operate...
Marts
April 5, 2010 at 11:54 am
Marts (4/5/2010)
Currently it is on Windows server 2000, is there any benefit in changing to 2003?
Definitely, a big yes on that one, Martin.
Way too many reasons to list, but iSCSI sucked on 2000 - I do remember that.
I have 4 33gb disks and 4 66gb disks...what would be the better solution?
I had been considering:
2x33 raid 1 for system
2x33 raid 1 for logs
4x66 in raid 1 + 0 for the data
All things being equal, and if the server is dedicated to SQL Server, I might be tempted not to have a separate LUN for the system at all. A well-configured SQL Server does not page or do much system file I/O at all - except when starting!
If your database activity features many more reads than writes (most do), or if your storage facility provides a SQL Server-compliant write cache, I could be persuaded by:
2 x 33 RAID 1 - Logs (only!)
2 x 33 RAID 1 - tempdb (only!)
4 x 66 RAID 5 - Everything else
Otherwise, sure, replace the RAID 5 with RAID 10 (it will cost you 66GB).
That said, tempdb really should be on direct-attached storage if you can manage it.
First and foremost though, give SQL Server the most RAM you can.
Paul
April 5, 2010 at 12:03 pm
Paul White NZ (4/5/2010)
Marts (4/5/2010)
Currently it is on Windows server 2000, is there any benefit in changing to 2003?Definitely, a big yes on that one, Martin.
Way too many reasons to list, but iSCSI sucked on 2000 - I do remember that.
I have 4 33gb disks and 4 66gb disks...what would be the better solution?
I had been considering:
2x33 raid 1 for system
2x33 raid 1 for logs
4x66 in raid 1 + 0 for the data
All things being equal, and if the server is dedicated to SQL Server, I might be tempted not to have a separate LUN for the system at all. A well-configured SQL Server does not page or do much system file I/O at all - except when starting!
If your database activity features many more reads than writes (most do), or if your storage facility provides a SQL Server-compliant write cache, I could be persuaded by:
2 x 33 RAID 1 - Logs (only!)
2 x 33 RAID 1 - tempdb (only!)
4 x 66 RAID 5 - Everything else
Otherwise, sure, replace the RAID 5 with RAID 10 (it will cost you 66GB).
That said, tempdb really should be on direct-attached storage if you can manage it.
First and foremost though, give SQL Server the most RAM you can.
Paul
I agree with Paul here. A little more the disk config: I would definitely leave the data RAID as RAID 5. Forego the RAID 10 until you have more drives. I think you will see better performance by having more spindles available for the data (it's worth testing in your setup).
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
April 5, 2010 at 12:16 pm
CirquedeSQLeil (4/5/2010)
I agree with Paul here. A little more on the disk config: I would definitely leave the data RAID as RAID 5. Forego the RAID 10 until you have more drives. I think you will see better performance by having more spindles available for the data (it's worth testing in your setup).
Thanks Jason. Not sure though: many controllers optimize RAID 1 so that reads can come from either side of the mirror - giving close to RAID 0 performance. Depending on the hardware then, RAID 10 might be as fast as RAID 5 for reads and slightly faster for writes. Agree?
April 5, 2010 at 12:25 pm
Paul White NZ (4/5/2010)
CirquedeSQLeil (4/5/2010)
I agree with Paul here. A little more on the disk config: I would definitely leave the data RAID as RAID 5. Forego the RAID 10 until you have more drives. I think you will see better performance by having more spindles available for the data (it's worth testing in your setup).Thanks Jason. Not sure though: many controllers optimize RAID 1 so that reads can come from either side of the mirror - giving close to RAID 0 performance. Depending on the hardware then, RAID 10 might be as fast as RAID 5 for reads and slightly faster for writes. Agree?
That is true. Too much depends on the hardware. We are going through an exercise with EMC currently to optimize our SAN. For their equipment (our models specifically), we would see very little difference in performance between the two RAID Levels. The main difference they emphasize between the two RAIDs is the level of protection you buy for the data.
I see no harm in setting it up both ways (if possible) and testing to determine which will provide the best performance.
Now, if we could recommend a different setup or different hardware then one might suggest SSD 😉
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
April 5, 2010 at 12:31 pm
Again thank you for all your replies.
One point I must note here, we have only sql server standard, and unless I'm very much mistaken it is limited to 2gb ram usage 🙁
I have 4gb in the machine and over a gig is free all the time.
One consideration I had was to make a 1gb ramdrive for the pagefile ... odd I admit, but it seemed like a good idea at the time
So it may make sense to keep the data files with the OS, and just move out the logs and tempdb onto seperate disks.
April 5, 2010 at 12:39 pm
Marts (4/5/2010)
Again thank you for all your replies.One point I must note here, we have only sql server standard, and unless I'm very much mistaken it is limited to 2gb ram usage 🙁
I have 4gb in the machine and over a gig is free all the time.
One consideration I had was to make a 1gb ramdrive for the pagefile ... odd I admit, but it seemed like a good idea at the time
So it may make sense to keep the data files with the OS, and just move out the logs and tempdb onto seperate disks.
Correct on the memory.
I think you will get the biggest bang for your buck by moving the logs and tempdb out as you said.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
April 5, 2010 at 12:40 pm
CirquedeSQLeil (4/5/2010)
That is true. Too much depends on the hardware. We are going through an exercise with EMC currently to optimize our SAN.
Cool! Which series/model? I have worked on Symmetrix DMX-IV, Clariion CX and Celerra (poor old thing that it was).
Now, if we could recommend a different setup or different hardware then one might suggest SSD 😉
I have used FusionIO ioDrive-320 Duos, which are just awesome.
April 5, 2010 at 12:44 pm
Paul White NZ (4/5/2010)
CirquedeSQLeil (4/5/2010)
That is true. Too much depends on the hardware. We are going through an exercise with EMC currently to optimize our SAN.Cool! Which series/model? I have worked on Symmetrix DMX-IV, Clariion CX and Celerra (poor old thing that it was).
Now, if we could recommend a different setup or different hardware then one might suggest SSD 😉
I have used FusionIO ioDrive-320 Duos, which are just awesome.
We are moving from DMX to Clariion.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
Viewing 15 posts - 1 through 15 (of 51 total)
You must be logged in to reply to this topic. Login to reply