Hello We currently have a third party accounting software that runs on SQL 2005 Server Express Edition. The system runs a bit on the slow side and around once a day will freeze for 10 - 30 seconds. If a large batch or report is run the system slows right down. Our database is 2 Gig We have 15 Users Our Expected Database Growth is arround 400b / Year We are running on Gigabite Network. We have decided to go to SQL 2008 Workgroup Edition. Now Management is wondering if they should upgrade the hardware at the sametime. Our CURRENT configuration is. Windows XP 4 Gig Ram Dual ST3250310NS 500GB Drives Raid 1 Intel E8200 2.66GHz Processor Option A Windows XP 6 Gb DDR3 1600MHz ram 5 x 7200rpm 1TB Drives Raid 10 Intel I7 960 3.2GHz Processor Option B Windows Server 2003 24 Gig Ram 2x 15K Drives Raid 1 For OS 2 x 15K Drives Raid 1 for data 2 x Intel Quad Core Xeon E5404 2.0 GHz Processor's We have been told that upgrading from our current configuration to Option A will make NO difference? Is this True? Will going to a Windows Server be a Huge advantage? A couple of Opinions would be appreciated as Management will be Happy if they can save $20,000 but not if it's what we Really need. Many Thanks
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] 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] 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. :
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] or [Michelle Ufford's script] and run it (out of hours). :
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.
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: