March 14, 2009 at 5:26 am
We currently run SQL 2000 on a VMware VM, works fine for our size of environment.
We're going to be migrating to a new VM that will be running Windows 2003 and SQL Server 2005.
Whilst I'm not our "SQL guy" the upgrade is mainly to get current vs. because our performance requirements/environment has changed.
The VM will be allocated 1-2gb RAM and I can't see that changing for a very long time, if ever.
My question is, is there any benefit in going with Windows x64 / SQL 2005 64-bit until we're hitting the numbers where 32bit becomes the bottleneck?
March 14, 2009 at 7:26 am
if its a VM and you have the OS licence, may as well install 64 bit. As a VM you can scale memory, cpu and disk when required
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
March 14, 2009 at 1:10 pm
You might turn the question around and ask "Why not use 64-bit?"
One thing that may be a cost consideration is that 32-bit Windows Server 2003 requires Enterprise Edition to support more than 4 Gb of memory, while 64-bit Windows 2003 Standard Edition will support more than 4 GB of memory.
The way things are going, 2 GB is not that much memory, and memory is not that expensive. More memory is often the most cost-effective upgrade to increase server performance compared to adding CPUs or upgrading the disks. More memory means less time waiting for disk read IO and less CPU time devoted to disk read IO.
Since you are in a VM environment where adjusting the memory is fairly easy, keep you options open for adding memory to servers as needed.
March 14, 2009 at 1:36 pm
Paul Hutchings (3/14/2009)
the upgrade is mainly to get current vs. because our performance requirements/environment has changed.
What I've found in the past is that such upgrades are expensive and are usually short lived in the face of scalability. I've seen it too many times where folks update the hardware/software hoping to realize new performance only to be grossly disappointed when the code runs faster for a month a two and then slows to it's old, unsatisfactory speed or worse.
If you're not going to be allowed to have just oodles of Ram, then 32 bit should be just fine. The real problem is... the code... and it will eventually have a dramtic impact even on 64 bit machines with lots of memory. Instead of spending a pot wad on the new box (VM or not), doing the migration, and regression testing the whole kit and kaboodle to make sure nothing was left out and nothing broke during the migration, the money would be very well better spent reviewing the performance troublespots in the code and rewritting it to use best practices, proper set based code, and "Divide'n'Conquer' principles.
Lemme give you an example... because we get some really horribly formatted data from a 3rd party vendor who cannot be compelled to change, some folks where I work at wrote some split code to handle the import of the monster, atypical CSV files we get evey day. A lousy 44 megabyte file was taking 36 minutes just to split even after the shift from 32 bit dedicated to 64 bit VM and 4 times the memory. After my rewrite, it now takes only 3.25 minutes and that's without me spending a lot of time tuning it.
Here's another example... at a previous job, they had a run that would take 10 to 24 hours to sometimes fail. I was told that it was written by 3rd party experts and those experts said we just had too much data to run any faster. A change from the Standard Edition to Enterprise Edition, a whole new monster server, and more memory than you can shake a stick at, didn't help.
After the rewrite, it ran so fast they thought it didn't work (11 minutes). The daily runs in the same vein that used to take 44 minutes to run, dropped to 7 seconds and the code hasn't failed in the 2.5 years it's been in use, so far.
Everyone wants performance and is willing to pay through the nose for hardware, operating systems, and 64 bit SQL. The only thing they're not willing to do and it is the only thing to do is to spend some time rewritting performance and scalability challenged code. Performance is in the code... or not...
--Jeff Moden
Change is inevitable... Change for the better is not.
March 15, 2009 at 4:24 am
Jeff Moden (3/14/2009)Performance is in the code... or not...
A very good point, but unfortunately one that I have no control over.
I'm the network/server admin so I get to provide a SQL box that conforms to best practice wherever possible, and ensure that it gets backed up and the likes, but regards code, at that point I'm out of it and let our developers do what they're paid to do.
We're not upgrading in the belief that we're going to get a huge performance jump - we've had 9 years out of a SQL 2000 processor license and for various reasons such as some software that requires SQL 2005 (even if it could be bodged to work with 2000 they wouldn't support it) it's been necessary to look at upgrading.
March 15, 2009 at 5:46 am
Paul
what Jeff is saying (and you have to agree is quite correct),You shouldnt do it expecting a massive increase in performance otherwise you could well be dis appointed. There are other factors around this.
On the other hand taking the point you have mentioned it would make sense to go 64 bit, this is certainly the direction things are going anyway. As already pointed out, as a VM you have the luxury of scaling the machine when you need to. If you're upgrading the machine to move from windows\sql 2000 then it makes sense to go 64 bit IMHO
BTW what virtualisation platform\version are you using?
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
March 15, 2009 at 8:59 am
I'll also kick in that I'm amazed at the attitude of so many DBA's, especially System DBA's. The code is part of the server and bad code should be prevented from ever entering. I do realize that is a paradigm shift for many companies but, if performance is a key desire, then code must be adequately tested in the presence of lots of data and code reviews for best practices must be done. There are groups like AppDev who's only goal is to get the job done at any cost to the server and THAT is what they are paid for... not writing performance code. When their code ends up with a performance problem, don't be surprised when they say, "The server is running slow... fix it." and they will actually expect you to fix it instead of saying "the code is running slow".
Heh... if you were in charge of the health of a fancy race car, you wouldn't allow people to use it to haul manure. Why would anyone treat a server differently?
--Jeff Moden
Change is inevitable... Change for the better is not.
March 15, 2009 at 9:13 am
Jeff Moden (3/15/2009)
Heh... if you were in charge of the health of a fancy race car, you wouldn't allow people to use it to haul manure. Why would anyone treat a server differently?
Because using that analogy, it's pretty obvious to anyone looking that it's full of crap whilst with code you have to have the necessary knowledge to know and understand what you're looking at.
In an ideal world I agree 100%, but I don't have the luxury of an ideal world.
Sounds like 64-bit is the way to go though so thanks for the advice everyone.
March 15, 2009 at 9:22 am
Yep... I agree... knowledge and time are part of the real world items that are frequently missing in that area. Like Perry stated, I just don't want anyone to be disappointed if the new hardware and the shift from 32 to 64 bit doesn't actually help.
In all seriousness, good luck with the change... it's always fun and I always learn something new on those.
--Jeff Moden
Change is inevitable... Change for the better is not.
March 15, 2009 at 10:29 am
The problem is (and i have come across this before) if you do point out a dev's bad piece of code mostly they get uptight, jumpy and defensive instead of working with you. :angry:
pisses me off
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
March 16, 2009 at 4:40 am
The best advice is to go for 64-bit and SQL Server 2008 on Windows 2008. This will give you an operating platform that will stay current technology for the longest.
If you decide to use any of SQL Server 2005, Windows 2003 or 32-bit, you will probably have to upgrade away from that faster than you would have to upgrade away from SQL 2008, just to stay within supported / supportable versions of Windows and SQL.
Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.
When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara
March 17, 2009 at 1:45 pm
EdVassie (3/16/2009)
The best advice is to go for 64-bit and SQL Server 2008 on Windows 2008. This will give you an operating platform that will stay current technology for the longest.If you decide to use any of SQL Server 2005, Windows 2003 or 32-bit, you will probably have to upgrade away from that faster than you would have to upgrade away from SQL 2008, just to stay within supported / supportable versions of Windows and SQL.
It'd be nice, but a mix of Windows CALs and SQL support from one of the vendors of our software makes that a non-starter, for now at least.
The actual licenses we have are for 2008 so when the time comes we can upgrade/migrate at little (out the door) cost.
March 17, 2009 at 3:09 pm
Hi everybody
I'm one of the Devs but I definitely have to agree to your statements. Sometimes I see code (or here about performance) of other people, also within my company, and I just can't understand it!
There are processes which take about 30 minutes to import 5mb XML files and web applications which reach the 800mb boundary for ASP.Net apps with only 30 users...!
But some of the developers, I think, cannot be indicted because of this. The problem sometimes is not the mindset of the developer... As I started there have been senior developers who took me by hand and showed me how to do things right or wrong. Today we have a "huge base library which should be self explaining" (I cannot here any more about self explaining software and libraries) and no time will be invested for education.
So it's no wonder that some of the applications are as slow as they are. The people think the way as it was done previously has to be bad (or maybe uncool) because the new is the better. Sure frameworks like .Net are a good thing to become faster with development (yes, these applications do need more memory than C++ applications) but the problem stays that the people will not be briefed how to write for production.
Greets from the dev side 😉
Flo
March 25, 2009 at 1:42 am
may be you should consider the problems that may occur
for SQL Server on VMware, as well :
http://sqlsolutions.com/articles/articles/SQL_Server_and_VMware-A_Potentially_Fatal_Combination.htm
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply