SQL Server 2005 Performance Problems

We're in the process of migrating to a beefy new SQL Server (12GB RAM, 2 4-Core CPUs, 12 x 15k rpm drives, Gbit network). We have the drives divinded into 4 partitions for the OS, Data, Log and full text index files.

Here's the problem: I'm running a job that exectutes 36k searches (a combination of table and fulltext joins) from a single threaded console application. Only rather than taxing our server, the server registers about 5 to 7% CPU load, not the +-60% that we were getting on our old server.

From the dashboard reporting, the only waits we're receiving are the occasional network IO wait - but it comes and goes. So it seems like SQL Server is throttling our connection?

Can anyone shed some light on this?

Thanks
Jon

From serverfault Jon Leigh

• Well, you could try running Perfmon and SQL Profiler to get a lot more insight into this. But please tell us a little more about your drive config, firstly. You say you have 12 drives divided into 4 partitions. Does that mean that you made one big RAID array and cut it into 4 actual OS-level partitions, or did you make 4 RAID containers, each with one OS partition? The former is a good recipe for bad performance.

  Jon Leigh : Hi The we have 4 separate raid arrays, Raid 1 for the OS, log files and full text index and raid 5 for the data files

  mfinni : OK, that rules out a biggie for bad performance. RAID-5 isn't optimal for writes, but since your problem is reads, that's not a big red flag either. You're gonna have to dig into Perfmon and Profiler - and at this point I will stop being helpful, because I don't know the best counters to watch, off the top of my head.

  Jon Leigh : Thanks anyway :)
  From mfinni