question

First thing I would like to ask is how is CPU and memory usage on your current environment when things are "slow" You didn't specify if the Windows XP environment is x64 or not. I would hope so otherwise you are not utilizing but 3 GB of your available RAM. What is the speed of your 500 GB drives and what is the speed of the proposed 1 TB drives. Chances are both are SATA drives at 5400 or 7200 RPM. If CPU is not your bottleneck or memory then increasing either will not do you any good. Most likely it is the speed of your disk and your memory that is your issue. The first thing that jumped into my mind when you said slowness is missing indexes. Had the database been properly tuned? Are you doing any routine maintenance such as rebuilding indexes, updating stats, etc on a regular basis? As for option A, It probably would not make a significant difference in performance. Option B would for sure with the faster disk and 24 GB of ram. The 8 cores is also a big plus.

It's likely that option A actually will make a difference, not because of the hardware, but because of SQL Server Workgroup edition... the Express edition you are using can only utilize 1GB of RAM. Workgroup bumps this up to 4GB RAM, so you will be able to take advantage of caching your entire database in RAM and the OS can divide up the processor work more efficiently. It might even make a difference with your current hardware. Read up on SQL Server edition differences here - [ http://www.microsoft.com/sqlserver/2008/en/us/editions-compare.aspx][1] I must ask, why do both of your options involve out-of-date operating systems? If you're getting something new, get Windows Server 2008 or R2. If you want to stay with the shoestring budget, at least use Windows 7 over Windows XP. Either way, go 64-bit so you can use the additional RAM without additional configuration to make it work. Don't forget to check your disk partition offsets for a "free" 30% database IO boost - [ http://msdn.microsoft.com/en-us/library/dd758814.aspx][2] I can't speak to which option works best for your business, but it looks like your options run to the extremes... get a barebones workstation for under $1K, or get a decently spec'd server for $20K. This is known as the either-or fallacy, and backs you into the corner of saving lots of money for what is perceived to be a poor server, or feeling like you're blowing a wad of cash for the high-end machine when you didn't need to. Either option sets management up for buyer's remorse, when you actually want them to think you're a budget-saavy super-genius. The workstation route doesn't have to be quite so bare-bones. Spice it up just a little bit for only a few hundred dollars. With a database that's only 2GB and growing very slowly, you could easily lose those 1.5TB rotating drives and throw in some 64GB SSDs for $100 apiece. A mirror for the OS, a mirror for the data files, and a mirror for the log files. $600 (minus the price of the 1.5TB drives you no longer need) for triple SSD mirrors. Not bad. Your new shoestring computer now has major curb-appeal and is weighing in at less than $1.5K. The server route doesn't have to run $20K, either. Look at the outlet side of your hardware manufacturer. You can often pick up a scratch & dent server (or mega-workstation) with great specifications for well under $5K - I see one with 16GB RAM with multiple 15K drives, similar to the one you might be looking at for $4K on a major manufacturer's site right now. The manufacturers stand behind these with as-new warranties, and they often weren't even scratched or dented! You're still getting the nicely spec'd server, just at a fraction of the price. If you take another look at your hardware options, suddenly you've got a couple very appealing alternatives. [1]: http://www.microsoft.com/sqlserver/2008/en/us/editions-compare.aspx [2]: http://msdn.microsoft.com/en-us/library/dd758814.aspx

Just one thought, given the workloads you mention... Are your databases set to "AutoClose" and "AutoShrink"? If so, turn that option off, and try again - you might find things run better and you don't need to upgrade at all. edit - OK, as you've been running with AutoShrink, one of the next thing to do is defrag your data internally. Suggest you get hold of either [Ola Hallengren's script][1] or [Michelle Ufford's script][2] and run it (out of hours). [1]: http://ola.hallengren.com/ [2]: http://sqlfool.com/2011/06/index-defrag-script-v4-1/

Take a look at this book http://www.simple-talk.com/books/sql-books/sql-server-hardware/ when its published and see if any of its recommendations can be applied to your scenario.

There are several good answers to your direct questions, but let me just add a couple of small throughts. First, neither of your options seem to consider involving SSDs. While they may not matter much with such a small database, I would as a general rule seriously consider using SSDs when working with a database. I happened to have just finished reading this post: [ http://blog.yafla.com/If_You_Arent_Using_SSDs_For_Your_Solutions_Youre_Wasting_Time_and_Money/][1] right before coming over and seeing this question. The same author discusses SSDs more at [ http://www.yafla.com/dforbes/The_Impact_of_SSDs_on_Database_Performance_and_the_Performance_Paradox_of_Data_Explodification/][2] though that one is primarily a piece about comparing NoSQL and SQL. Next, again with such a small database (assuming you are using the server only for the database and this server will serve no other functions) it may not matter much, but as a general rule RAM is the prime hardware chokepoint for a database. When looking at hardware, I generally ensure there is enough RAM first and foremost. This of course does not mean the other considerations are moot, they can be very significant, but as a general rule RAM is the most important. [1]: http://blog.yafla.com/If_You_Arent_Using_SSDs_For_Your_Solutions_Youre_Wasting_Time_and_Money/ [2]: http://www.yafla.com/dforbes/The_Impact_of_SSDs_on_Database_Performance_and_the_Performance_Paradox_of_Data_Explodification/

Forgive me for jumping on the bandwagon late, but I must reiterate a point or two that @KenJ made. We do not yet have a full picture of his configuration. One thing I am pointing to is the location of the db files. Does he have .mdf and .ldf on different LUNs? If @KenJ 's suggestions are followed here, I agree that he could have a screamer at a fraction of the cost by assembling it this way. I have run databases with both log and data on the same LUN and it was pathetic. After separating them, performance was stellar, on the exact same hardware. Here is my offering: modest hardware ~ $1000 to $1500 USD -Win7 or Srv08, x64 on dual core or more, 4-6G of RAM. -*At least* 3 LUNs in RAID 1 or 10 for R-W performance(small SSDs are great for this) one for OS, one for data, one for log, and if you have the $ you can also make one for tempdb. -SQL-WG scales to 4gb of RAM, so make your OS page file 6GB, or move up to SQL-STD and use all you can. Now that we have it all laid out nicely on the HW, let's start looking at indexes (frag, missing, unused, etc) and other performance items. My 2¢ worth.

Sorry but what IS a LUN?

A LUN is a logical unit number of a RAID disk array. When building the array you usually have the option of 'slicing' it in various ways so as to have logical disks arranged in such a fashion as to Span physical disks, thereby maximizing how your logical disk will take advantage of the physical array. It is rather a nebular concept to be typing on a mobile keyboard, so I will refer you here: http://en.wikipedia.org/wiki/Logical_Unit_Number HTH