Great question. Arriving at an accurate answer requires some legwork…

First of all, if you’ve enabled lightweight pooling you might be surprised that this feature might be wreaking havoc on your system; telling SQL Server to run in fiber mode sounds cool, but it’s rarely adviseable and it makes certain applications function erraticaly (SQLXML) or stop working altogether (CLR). Ken Henderson’s the pro in this area, and published a great article on the topic, aptly named The Perils of Fiber Mode.

Now, as for CPU-bound SQL Servers you can determine how your instance is handling requests by looking at DBCC SQLPERF(umsstats) in SQL Server 2000 (again, Ken’s published a highly in-depth article on schedulers called Inside the SQL Server 2000 User Mode Scheduler) or at sys.dm_os_schedulers in SQL Server 2005. For a good blog on how to use sys.dm_os_schedulers, go to Slava Oks’s blog on the topic.

With either version of SQL Server you need to create a baseline, or at least gather information over time to accurately determine whether your system is CPU bound; just determining that there is CPU pressure at a single point in time isn’t a good enough indication to go about upgrading your server. First, you should determine that your application code is tuned and there are no cascading resource bottlenecks freezing up your system. Tools like Quest’s Performance Analysis, together with SQL Tuning can identify inefficient statements over time and allow you to quickly get optimized plan recommendations; you can even link to Benchmark Factory to test the impact of the newly upgraded statements! Once you’ve analyzed and tuned your workload you can use osql, sqlcmd, or even ostress to execute scripts over time and dump their results to a table. Once you have a set of data you should set about determining if, for example in SQL Server 2005, the number of runnable tasks per each scheduler always greater than 1. If you’ve tuned your environment, and the aforementioned statement is still true, you can present a good argument for adding CPU resources to your server.