For Day 21 of this series, I will talk about processor cache size and its relationship to SQL Server performance.

Cache Size and the Importance of the L2 and L3 Caches

All Intel-compatible CPUs have multiple levels of cache. The Level 1 (L1) cache has the lowest latency (i.e. the shortest delays associated with accessing the data), but the least amount of storage space, while the Level 2 (L2) cache has higher latency, but is significantly larger than the L1 cache. Finally, the Level 3 (L3) cache has the highest latency, but is even larger than the L2 cache. In many cases, the L3 cache is shared among multiple processor cores. In older processors, the L3 cache was sometimes external to the processor itself, located on the motherboard.

Whenever a processor has to execute instructions or process data, it searches for the data that it needs to complete the request in the following order:

1. internal registers on the CPU
2. L1 cache (which could contain instructions or data)
3. L2 cache
4. L3 cache
5. main memory (RAM) on the server
6. any cache that may exist in the disk subsystem
7. actual disk subsystem

The further the processor has to follow this data retrieval hierarchy, the longer it takes to satisfy the request, which is one reason why cache sizes on processors have gotten much larger in recent years.  Table 1 shows the typical size and latency ranges for these main levels in the hierarchy.

Table 1: Data Retrieval Hierarchy for a Modern System

For example, on a newer server using a 45nm Intel Nehalem-EP processor, you might see an L1 cache latency of around 2 nanoseconds (ns), L2 cache latency of 4 ns, L3 cache latency of 6 ns, and main memory latency of 50 ns. When using traditional magnetic hard drives, going out to the disk subsystem will have an average latency measured in milliseconds. A flash based storage product (like a Fusion-io card) would have an average latency of around 25 microseconds. A nanosecond is a billionth of a second; a microsecond is a millionth of a second, while a millisecond is a thousandth of a second. Hopefully, this makes it obvious why it is so important for system performance that the data is located as short a distance down the chain as possible.

The performance of SQL Server, like most other relational database engines, has a huge dependency on the size of the L2 and L3 caches. Most processor families will offer processor models with a range of different L2 and L3 cache sizes, with the cheaper processors having smaller caches and, where possible, I advise you to favor processors with larger L2 and L3 caches. Given the business importance of many SQL Server workloads, economizing on the L2 and L3 cache size is not usually a good choice.

If the hardware budget limit for your database server dictates some form of compromise, then I suggest you opt to economize on RAM in order to get the processor(s) you want. My experience as a DBA suggests that it’s often easier to get approval for additional RAM, at a later date, than it is to get approval to upgrade a processor. Most of the time, you will be “stuck” with the original processor(s) for the life of the database server, so it makes sense to get the one you need.