Databases Reference
In-Depth Information
Configuring SQL Server to use more
processing power
In today's age, databases keep getting bigger and bigger, so in order to get information quickly
from a database, it is not enough to manage your database wisely; you will also need a CPU
with faster processing power.
No matter how efficiently you maintain indexes and statistics, you will not receive a prompt
response from your SQL Server if you are running with low processing power. Choosing a
proper CPU for your database need, is a part of "capacity planning", which is out of the scope
of this chapter, as it itself requires few chapters to describe it in-depth. However, here we will
learn how to utilize your current processing power efficiently.
Have you ever wondered how many CPUs are used by a SQL Server while processing a query?
Users used to buy increasingly powerful computers, with many processors and cores, but it is
interesting to know how many of them are used while executing a query in SQL server.
Getting ready
Before we move forward, it is important to know how many CPUs there are in the server. I will
be using the
sys.dm_os_sys_info
DMV to retrieve that information, as it tends to provide
miscellaneous useful information about the computer and about the resources available
to—and consumed by—the SQL Server:
SELECT
cpu_count AS 'Cores'
,hyperthread_ratio
FROM
sys.dm_os_sys_info
The
hyperthread_ratio
column given in this query does not distinguish between actual
hyper-threaded cores and true physical cores. This makes it even more difficult to guess which
processor is being used in the server. It would nice if Microsoft includes more information
about cores in this DMV, in its next release of SQL Server 2012.
How to do it...
Follow the steps given here to perform this recipe:
1.
To set the number of CPUs that can be used while executing a query on an instance
level, execute the following query:
--0 is the default value
sp_configure 'max degree of parallelism', 0
RECONFIGURE WITH OVERRIDE
GO
Search WWH ::
Custom Search