Database Reference
In-Depth Information
However, in this configuration, not all CPUs are created equally. What do
you do after that? Wait for HP to build a server with more CPU sockets on
the motherboard? Wait for Intel to release a new CPU architecture? Those
often aren't realistic answers and aren't going to be tolerated by a business
thirsty for information.
Software
Let's assume that you have the maximum number of cores available to you.
In today's market, that means you have a very nice eight-CPU socket server
from Hewlett Packard in your data center. Now what? Well, first of all,
you need to configure the software to make sure that you are addressing
those cores efficiently using a non-uniform memory address (NUMA)
configuration. Configuring a server for NUMA is beyond the scope of this
book. However, you will have to trust me that this is an advanced topic and
generally involves bringing in expert consulting resources to ensure that it
is done properly. However, this brings me to my first point: the bigger the
server, the greater the need to have an “expert”-level operational team to
support it.
It could be argued that this is a one-time cost, so, assuming you know you
have to pay it, let's leave it there. Besides, it brings me to the next challenge:
generating parallel queries. Even though you might have built a beautiful
server and have it expertly configured, you still need to make sure that the
database makes that asset sweat—that means running parallel queries.
SQL Server's optimizer
can
generate query plans that use many cores (that
is, a parallel plan). This is governed by two factors: the user's query and the
cost threshold for parallelism setting.
Depending on the type of query issued by the user, SQL Server may or may
not generate a parallel plan. This could be due to a number of reasons.
For example, not all operations are parallelizable by SQL Server in a single
query.
The key takeaway here is that SQL Server does not offer a mode that
forces
parallelism (a minimum degree of parallelism or MINDOP, if you will). SQL
Server only offers a way to cap parallelism using the maximum degree of
parallelism (MAXDOP) and the cost threshold mentioned previously.
Remember, SQL Server's query optimizer was not built from the ground
up with data warehousing in mind. Over the years, it has had a number of
