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?


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.

| improve this answer | |
  • 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 – Jon Leigh Mar 4 '10 at 10:54
  • 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. – mfinni Mar 4 '10 at 14:18

IIRC, the only edition of SQL Server 2005 that throttles connections for any reason is Express and it only throttles when it seems "many" simultaneous connections. It's unlikely that someone installed Express on a beefy server or that a single-threaded console app has many simultaneous connections.

If your queries are not parallelized, each query will only run on one of that machines eight (2x4) cores, effectively wasting the other seven cores.

With no other bottleneck (IOW, all of the data that your queries need is cached in RAM and there are no blocking queries from something else), your queries will use up 100% of one core. One "core's worth" of load on your system is 12.5%. That is close-ish to what you see, if you add in a little bit of disk activity.

With the old server hitting 60%, it might be that the old server paralellized (at least some) queries and the new one doesn't parallelize any queries, for some reason.

I would use Perfmon to look at the per-core processor load. My guess is that you will find that one of them is at 100%, or very close to it. You may find that the "busy" core changes from one to another. If all of the cores are equally busy, something stranger is happening. In that case, I'd definitely want to look for waits on the database file I/O. There is a DMV for that.

I would also use SSMS to look at the query plans for at least a sampling of the queries that the app executes. Sometimes, things just jump out at you ("Hey, where did that index go? I thought we put it back after the upgrade", or something like that.).

To sum up:

  • Make sure that your index statistics are up to date. (The simple thing to do is reindex everything, if you can.) This is particularly important to do if you have gone from SQL2000 SQL2005 as part of your upgrade.
  • Look for blocking. If you find some, the questions to ask are "Why was this different on the old system?" and "How do I minimize blocking now?"
  • If you need your queries to paralellize, make sure that MAXDOP configuration for the server hasn't been set to 1 (default is 0) by a friendly DBA. This is configurable at the server level. See Maximum degree of parallelism. Regardless of the setting, you can force queries to parallelize (or not) by using the MAXDOP keyword in your queries.
  • Have a look at the query plans for those 36 queries. Tune your queries and tables/indexes.
  • Rework your single threaded console app to run different queries on different connections on (probably) other threads, so you can run more than one at a time. (This is clearly the most complicated and least desireable thing to do, assuming that you even have the source code.)
| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.