Choosing and Configuring Hardware - Professional SQL Server 2005 Performance Tuning - page 166

x64orIA64
First, a bit of history; x 64 was created by AMD as an alternative to the strictly patented IA64
platform from HP and Intel and became a feature in all of their processors. Then Microsoft announced
the development of Windows XP Professional x 64 Edition to take advantage of this new computing
environment on the desktop and Intel finally responded to the competitive pressure by releasing
x 64 compatible extensions to their processors called EM64 T. AMD renamed their technology AMD64 and
Intel renamed theirs Intel64. We use the x 64 term today as a vendor neutral way to refer to
either platform.
The key factors when deciding between x 64 and IA64 are cost, availability, scalability, and performance.
It's always dangerous to generalize, but today most x 64 processors have faster clock speeds than IA64
systems. The current state of processor clock speed is that x 64 processors are running over 3 GHz,
whereas IA64 processors have stayed around 1.6 GHz for some time now.
However, processor speed alone can be misleading as the most recent IA64 processors have larger cache
sizes than x 64 processors. The larger cache can help minimize the disadvantage of slower processor
speeds and is very beneficial when you have many processors in a server. x 64 processors are typically
available in a wider range of servers than IA64 systems, which are increasingly only found in special-
ized machines, either high performance workstations, or very large highly scalable systems from a few
specialist vendors.
Cache
The reason modern processors need cache is because the processor runs at 2 to 3 GHz and main memory
simply cannot keep up with the processor's appetite for memory. To try and help this, the processor
designers added several layers of cache to keep recently used data in small fast-memory caches so that if
you access it again it might already be in cache. To make this process more efficient it doesn't just load
the byte requested, but the subsequent range of addresses as well.
The cache on modern processors is typically implemented as multiple layers commonly known as L1,
L2, and L3. Each layer is physically further from the processor core, and gets larger and slower until you
are back at main memory. Some caches are general purpose and hold copies of any memory (L2 and
L3). Other caches are very specific and hold only address lookups, data, or instructions. Typically L1 is
smaller and faster than L2, which is smaller and faster than L3. L3 cache is often physically located on
a separate chip, so is further away from the processor core than L1 or L2 but still closer and faster than
main memory. Figure 6-11 illustrates an example of CPU cache implementation.
Processor Cache is implemented as a transparent look-thru cache. This means that there are controlling
functions on the chip that manage the process of filling the cache and managing cache entries.
Now onto some SQL Server specifics; SQL Server 2005 is a considerably more complex product than
SQL Server 2000 and building in all this additional complexity comes at the cost of additional lines of
code. This can be seen as additional bytes in the size of the final executable (EXE) file, Sqlservr.exe .The
executable for SQL Server 2000 is 8.9 MB. The executable for SQL Server 2005 has grown considerably
and is now at 28 MB. Not just that, but simply starting the service requires more system resources. SQL
Server 2000 runs in about 29 MB of memory (you can easily see this by using Task Manager and looking
at the Mem Usage column for SQL Server. The Mem Usage column provides an approximation of the
working set for the process). Connecting with SQLCMD and issuing a simple query, such as select name
from master..sysdatabases, adds another 0.5 MB to that number. SQL Server 2005 uses around 50 MB of