thanks for all your replies, some really good ideas here. I think the main issue with the dog slowness of the box is because of the Virtual server AND a heavily fragmented database AND this db has many tables with over 100,000,000 rows in them. Trying to rebuild indexes was taking forever!
Edit: forgot to add, the drives where the datbase files live was massively fragmented also, however because the files were large, a defrag of the drive didn't help much.
In the end we have decided to move a better hyper-v server with SQL 2005 Developer (this is only a test box) x64 and use partitioning.
However if I had to carry on using this box I would have started on the index fragmentation probaby one table at a time, as I can estimate that some clustered index rebuilds would take an entire weekend. :w00t:
[font="Tahoma"]Who looks outside, dreams; who looks inside, awakes. – Carl Jung.[/font]