I have a server with an Intel Core i7 processor (4 cores). If I view processor activity in Task Manager I see that there are 8 cores. I believe that this is because of hyperthreading (please correct me if I'm wrong).
SQL Server 2008 R2 Standard will use a maximum of 4 CPUs when compared to SQL Server Enterprise which uses a maximum of 8 CPUs.
My question: Will the standard version of SQL Server fully utilize all available CPUs on this server? i.e. Will it see 4 or 8 CPUs?
The limit in SQL Server 2008 Standard for processors is per socket, not per core. For more info, check out the SQL Server 2008 page at Microsoft.com. Specifically, the "SQL Server 2008 R2 Editions Overview" PDF document. From the document:
With increasing hardware innovations, Microsoft continues to be the only major database vendor who does not price per core for multi-core processors
Yes, SQL Server 2008 Standard will use all 8 cores. It could use a hundred cores on a single-socket license if such a CPU existed.
It doesn't matter. If load is placed on only four cores, the scheduler is smart enough to make sure that the load is on one logical core for each physical core. And hyper-threading typically gives you at best an additional 15% of performance. Loading a virtual core robs the other virtual core that shares that physical core of resources such as execution units, I/O bandwidth, L2 cache space, and so on. So they don't run at anywhere near full speed.
So even in the worst imaginable case, it would mean about 15% of your CPU couldn't be used by SQL server itself. But when SQL server is doing things, so will the OS be -- handling network and disk interrupts, managing memory, and so on. In any realistic case, that will be around 20% of the CPU load, and that can use the other virtual cores. So you're fighting over essentially nothing.
