REPEATABLE READ

In a previous life (actually two previous lives), I was a production DBA. One of the environments that I inherited had some ... issues. There were a number of Clustered SQL Server installations -- oodles of hardware, all churning to keep product inventory, customer information, and catalog browsing going for a number of web farms for a complex web site. The site was constantly firing sprocs against the servers to keep real-time info in sync -- but there were also a number of complex, long-running, jobs, sprocs, and batches running constantly in the background which did things like update customer information from other channels, or keep inventory levels synchronized between servers, etc.

The DBA before me had encountered some problems during peak usage of the site: smaller queries would queue up behind the longer running queries when the boxes were getting pegged. If you've studied the User Mode Scheduler implemented by SQL Server.. this comes as no surprise. In an attempt to alleviate the problem, and in coordination with some moron (reportedly) from MS SQL Server Product Support, my predecessor was instructed to set the Processor affinity on each server down to 1 -- i.e. limit all queries on the servers to using one proc only. This was done to stop long running queries from gobbling up the CPU, and allowed the smaller queries access to the CPU to get off the box quicker. Of course, this made the longer/slower queries take MUCH longer to complete, but made a number of 'quicker' queries slower as they were no longer able to harness SQL Server's excellent ability to use multiple procs.

Too bad nobody knew about the ability to set 'processor affinity' on query by query basis -- i.e. nobody knew how to specify the Max Degree of Parallelism. Of course, when I switched the servers back to being able to use all 8 processors, instead of just 1, I was the hero -- so it worked out great in the end. <grin>

The key lies in the OPTION query hint. Just leave your 'quick' queries as they are. And for longer running statements just specify like so:

SELECT blah,bletch,blunk 
FROM someTable
    INNER JOIN someOtherTables ON aBunchOf.Fields = aBunchOfOther.Fields
WHERE someCriteria = hideous 
OPTION (MAXDOP 2) -- ONLY use UP TO 2 processors