SQL Server Performance

Upgrade dilemma

Discussion in 'Performance Tuning for Hardware Configurations' started by rweinstein, Jun 13, 2005.

  1. rweinstein New Member

    Hello fellow SQL Server gurus and professionals. Hope all is well with all of you.

    I would appreciate if any of you can add your quick 2 cents on the question below, all input is greatly appreciated. I browsed through the prior posts to get as much info as I could.

    I am being asked to make sure my system can handle a doubling in size and processing. I currently have SQL Server 2000 standard, Windows 2000 Server (not advanced), dual 2.8GHz processors hyperthreaded to 4 and 4GB of memory on my database server. The database is 17GB. The server is maxed out, no room for additional processors or memory (due to operating system).

    If I were to buy a new server, I would recommend that we purchase an Itanium server and have Windows 2003 Advanced server operating system (to utilize more than 2GB of memory) and to purchase SQL Server 2000 Enterprise 64-bit edition. At this time, I cannot go with SQL 2005 because it is not released yet and my company will not authorize a beta or SP1 implementation.

    In my opinion, I think my current system can handle the doubling through 2006 and this was validated by a consultant that came in and did a comprehensive audit. My suggestion is to replace the servers in early 2007 when the normal production server rotation cycle runs out (3 yrs), however they would like the system to last 2-3 years from now.

    I want to know if I am on the right track here and if I should go with SQL 2000 64 bit or push my mgmt to wait for SQL 2005 and get ther servers then.

    Question:

    Given the above specifications of my server and the notion to upgrade now instead of in 1 1/2 years, do you agree that I should:

    1. Ask for scalable 64-bit Itanium servers (dual processors expandable to 4, 4GB memory) with SQL Server 2000 Enterprise 64-bit edition and Windows Server 2003 advanced server. Then upgrade to SQL 2005 after it has been out for a year.

    2. Let mgmt. know we are really okay for another 1 1/2 years, but that we will need the Itanium servers and SQL 2005 when we get a new system. I.E. do nothing as far as upgrades now.

    3. Ask to upgrade to 64-bit Itanium architecture and SQL Server 2005 when the release comes out later this year.

    4. Other not mentioned here.

    Thanks!
  2. derrickleggett New Member

    What does your current server look like? Have you looked at the disk, memory, and processor counters to determine what your thresholds are right now? If you don't need to upgrade, then don't! Hardware gets better daily, and the prices drop as well. When you get ready to buy, make the decision based on historical stats you've gained, where you expect the business to go, and what you forecast as needed for 3 years out (depending on cap model). <br /><br />Hope you don't think this is just smoke. It's how you should buy systems. Company systems aren't just "cool toys". [<img src='/community/emoticons/emotion-1.gif' alt=':)' />]<br /><br />MeanOldDBA<br />derrickleggett@hotmail.com<br /><br />When life gives you a lemon, fire the DBA.
  3. rweinstein New Member

    Derrick,

    Thanks for the input, I agree and this is where my delemma is coming from. Yes, I have generated the hardware statistics and my system is running great right now. My processing power is enough, my memory is more than sufficient and I do not have any bottlenecks on the RAID 5 or RAID 1 drives.

    I am comfortable with my current system for another 1.5 years, not 2-3 as my mgmt. is now asking the system to last. I can't get into any details because of confidentiality issues, but it is likely that the funds may only be available now and not in 1.5 years when I would really like to have them for a new system.

    With this supposed "now or never" directive, what do you suggest I look to do? Take the risk on my current system or go for a new one, the options I outlined?

    Does the new system I scaled out make sense for me as an upgrade (SQL 2000 64 bit, Win 2003 server OS, Itanium processors, etc)?

    Thanks.

  4. derrickleggett New Member

    It makes sense. I would probably just stick with 32 bit though. Go with a 4-processor server with 16 GB of redundant, usable RAM, and the disks as you outlined above. Get Enterprise Edition of SQL Server. Based on your current load, that should be more than sufficient, unless you plan on diving into some really memory intensive projects over the next 2-3 years.

    MeanOldDBA
    derrickleggett@hotmail.com

    When life gives you a lemon, fire the DBA.
  5. bertcord New Member

    I sounds like you don#%92t need the power now but it would be nice to go to 64bit in the future. I would take a look at some of the new x64 or opteron based servers. Dell has the 2850 and the 6850. Both of theses servers have x64 chips. IF you use this hardware you could do the following

    1.Install 32bit OS and 32bit SQL Server

    2.Install 64bit windows 2003 and 32bit SQL Server – ServicePack 4. The advantage to this is that 64bit windows 2003 supports 32GB of RAM. Once SQL2005 is release you can do a side my side install and upgrade your databases to the 64bit version of SQL2005

    Service Pack 4 has a bug that only lets you see half of your memory but this will be fixed in a few weeks

    Bert
  6. joechang New Member

    if you are running on 2x2.8GHz Xeon, 4GB memory
    and need to handle a 2X increase in load,
    then the 32-bit vs 64-bit (Xeon or Opteron vs Itanium) should be decided on

    whether you need the 64-bit virtual address space, not performance or scaling.

    you can get a 4-way Xeon MP, or 4-way Opteron, the new systems may even be able to handle the dual core CPUs, so there is more than 2X scaling for the 32-bit SQL Server.
    the 32-bit SQL Server can also use more than 4GB (upto 32-64GB) for buffer cache, which is usually good enough.

    So the only thing 32-bit SQL Server cannot do (within the 2-4X scaling) is get lots of address space for non-data buffer uses.
    This will include applications that use extended stored procs extensively, sp_cursor calls, etc.
    Potentially an application might like lots of memory/address space for the procedure cache, but i think only an idiot designs such an app (given that there are plenty of these)
    In theory, a full 64-bit SQL Server might do things like large hash and sort ops, but this has not been well documented
  7. rweinstein New Member

    Thanks, guys. I'm not sure about 64-bit virtual address space. I took a look at the process: Virtual Bytes and Virtual Bytes Peak in Performance Monitor, but I am unsure of what are bad numbers that would cause concern.

    What about keeping my current system and just upgrading the operating system to 2000 advanced and SQL to enterprise? This will enable SQL to use 4GB of memory instead of 2GB and the new operating system will enable me to put in and use more than 4GB of memory, although it seems like 4GB is enough for me.

    Will going to SQL 2000 Enterprise edition (not 64-bit) and changing the DB setting to use 4GB instead of 2GB of memory help me here, or do you think it is critical for me to go to a 4X processor scenario? It sucks my server only has 2 processor slots and not 4, I would love to just pop a couple more CPU's in the machine.

    The major problem is the owness is on me 100% if I say not to upgrade and the system will not handle the double load, but if I upgrade to a beefy system that runs great all is well except the company may have overspent....

    Any other ideas?
  8. derrickleggett New Member

    The owners really are somewhat out of touch with reality. If they are going to place this burden on you, with the information you have given us, I would go for an upgrade. Look at something like an x365 from IBM. It's a 4 way server that can hold 2 or 4 processors. All the major vendors also have 8 or 16 processor servers that can start with only 4 processors and scale extremely well.

    The box you are currently on will only scale so far, since you can't add more processors. Depending on the type of server, you might be able to put slightly faster processors, and up to 32 GB of RAM in it; however, the processors constrain you because you only have two slots.



    MeanOldDBA
    derrickleggett@hotmail.com

    When life gives you a lemon, fire the DBA.
  9. rweinstein New Member

    Derrick,<br /><br />Based on the information from this post and some more research I did today, this is where I am at now.<br /><br />I think I should upgrade my SQL license from SQL Server 2000 Standard edition to SQL Server 2000 Enterprise edition with 2 years of software assurance. This new license will give me the following:<br /><br />--SQL Server will be able to use 4GB instead of 2GB of memory<br />--I will get a 'free' upgrade to SQL Server 2005 Enterprise edition when it comes out later this year<br /><br />Also, I should upgrade my operating system to Windows 2003 Enterprise edition. This new license will give me the following:<br /><br />--I can put more than 4GB of memory into the server<br /><br />Lastly, I may be able to utilize another server to split out and load-balance Cognos application processing, which is very processor intensive to make my SQL server a little more dedicated.<br /><br />I will still be clear that this system should only last through December 2006 and we should revisit the purchase of new servers for January 2007, but only if it is needed at the time. <br /><br />The good thing with this scenario is that I will have the 64 bit software technology already purchased (SQL Enterprise 2005 and Windows 2003 enterprise) so when we go to a new system, we will only need the hardware. And, as you said in a year or two, the 64-bit hardware will be much less expensive.<br /><br />Do you see any holes in this action plan?<br /><br />Thanks!<br /><br />P.S. - Purchasing a SQL Enterprise dual processor licence with 2 year software assurance isn't cheap! <img src='/community/emoticons/emotion-1.gif' alt=':)' />
  10. derrickleggett New Member

    quote:
    Lastly, I may be able to utilize another server to split out and load-balance Cognos application processing, which is very processor intensive to make my SQL server a little more dedicated.

    Since they are demanding you buy whatever you need now, I would do this immediately. Buy a four processor server with two processors for SQL Server. Migrate the SQL Server to that. Use the existing server for Cognos.

    Good luck. Let us know what you end up deciding. Also, check with the MS reps. Make sure the x64 upgrade will not cost anything if you buy software assurance. And, yes....it's expensive to buy the Enterprise licenses. It probably costs more than the server.

    MeanOldDBA
    derrickleggett@hotmail.com

    When life gives you a lemon, fire the DBA.
  11. rweinstein New Member

    Derrick,

    Thanks. I just found out that we have 2 EE licenses that I am not even using.

    If I go with a four processor server, would it be better if I installed SE version and used all 4 processors or installed EE version and dedicated only 2 of the processors to EE?

    What are the other 2 processors used for if I have a dedicated SQL server and only allocate 2 processors to SQL?

    Lastly, do you reccomend Intel Xeon 3.6/800 processors or Opteron 2.4 processors?

    Thanks.
  12. derrickleggett New Member

    I would still recommend the Xeon processors personally. I'm waiting for a little more maturity before we make the switch. I know that's not popular, but databases are all about stability and supportability.

    On the processor question, you have to pay for ALL physical processors on the server, even if you don't use them. So, on a four processor server, you can only reduce lisensing requirements by physically pulling (not disabling) some of the processors.

    MeanOldDBA
    derrickleggett@hotmail.com

    When life gives you a lemon, fire the DBA.
  13. rweinstein New Member

    Derrick,

    I upgraded to EE and will look to enable more memory using AWE. I also should be upgrading the hard drives and operating system on the current 2 processor servers, and most likely will rearrange the drive configurations for best practices of SQL. I do not think we are going for a new system. I will monitor and look to do a complete upgrade in another 1 1/2 years...
  14. derrickleggett New Member

    Sounds like a responsible decision. [<img src='/community/emoticons/emotion-1.gif' alt=':)' />] If you need any help along the way let us know. It's always interesting seeing how thing turn out long-term.<br /><br />MeanOldDBA<br />derrickleggett@hotmail.com<br /><br />When life gives you a lemon, fire the DBA.

Share This Page