Seen this? You’ve got a nice new sparkling multi processor box, packed with ‘go-faster’ chips but the performance isn’t much better than your old one or two processor box. You take a look at the processor usage, either through task manager or Performance (System) Monitor, and notice only one processor seems to be hard at work, the others are just idling.
Steam comes out of your ears; you’ve just spent all this money and what for? Well before you drop a wobbly have a look at how many files you have for your database out on disk. Is it one .mdf and one .ldf – the default? Yes? Well that’s most likely your problem – you can only write to one file with one thread. Your database has become IO bound as only one processor can write to the database at any one time.
Solution – create a new database with as many data files (all the same size) as you have processors and transfer the data from the old database into this new one – this is to ensure you get an even proportional fill across the files.
PS If this isn’t a new box and you used to get good performance see SQL Server Performance Top Tip: Multi Processor (SMP) sudden death syndrome.