For Day 9 of this series, I want to talk about processor cache size and its relationship to SQL Server 2012 performance. <\/p>\n

Cache Size and the Importance of the L2 and L3 Caches<\/strong> <\/p>\n

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. <\/p>\n

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: <\/p>\n

\n

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<\/p>\n<\/blockquote>\n

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.<\/p>\n\n\n\n\n\n

L1 Cache<\/td>\n	L2 Cache<\/td>\n	L3 Cache<\/td>\n	Main Memory<\/td>\n	Disk<\/td>\n<\/tr>\n
32K size<\/td>\n	256K size<\/td>\n	20MB size<\/td>\n	256GB size<\/td>\n	Terabyte size<\/td>\n<\/tr>\n
2ns latency<\/td>\n	4ns latency<\/td>\n	6ns latency<\/td>\n	50ns latency<\/td>\n	15ms latency<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n Table 1: Data Retrieval Hierarchy for a Modern System<\/strong> <\/p>\n For example, on a new server using a 22nm Intel Ivy Bridge 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 this retrieval chain as possible. <\/p>\n 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. Figure 1 shows information about the caches in an Intel Xeon E5-2670 processor<\/a> in the bottom right corner. <\/p>\n <\/a> <\/p>\n Figure 1: CPU-Z Showing Cache Size Information<\/strong> <\/p>\n If the hardware budget limit for your database server dictates some form of compromise, then I suggest you opt to initially economize on RAM in order to get the processor(s) you really want. My experience as a DBA suggests that it\u2019s 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 \u201cstuck\u201d with the original processor(s) for the life of the database server, so it makes sense to get the one you need when you first buy the server. <\/p>\n You do have to be keenly aware of your total physical core counts as you select a processor for SQL Server 2012 Enterprise Edition, since you will have to pay for each core license.<\/p>\n","protected":false},"excerpt":{"rendered":" For Day 9 of this series, I want to talk about processor cache size and its relationship to SQL Server 2012 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 […]<\/p>\n","protected":false},"author":7,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[17,24],"tags":[119,120],"class_list":["post-697","post","type-post","status-publish","format-standard","hentry","category-sql-server-hardware","category-processors","tag-l2-cache","tag-l3-cache"],"yoast_head":"\n