L1, L2 and L3 latch on intel CPU??

My project needed immediate support when system engineer started a thread that one server out of rest 18 in that group is not performing good. This is really confusing when I get anyone telling me that something is not performing well. Performing well could lead to a many scenrios. We can find out so many things on the top of our mind as per last section of my previous blog query-running-slow-significance-of-non-clustered-index. But this scenario was different.

INTERVIEW PREPARATION IN 30 MINs – Read Following wires for a quick interview preparation.

  1. SQL Server DBA “Interview Questions And Answers”
  2. SQL Azure Interview Questions and Answers Part – 1
  3. SQL Azure Interview Questions and Answers Part – 2
  4. Powershell Interview Questions and Answers

Everything looked good except cpu performance. The %CPU was as high as 70% throughout whole last month. Report was totally red. Why would CPU behave like that? The answer could be found at CPU architecture. This was my last resort when i started looking at CPU architecture today morning. I used following command to get basic properties of all the CPUs of 18 servers.

$servers = GET-CONTENT D:\SQL\CPUComparison\serverlist.txt
foreach($server in $servers)
$server | out-file d:\SQL\CpuComparison\CPUinfo.txt -append
gwmi -computer $server -class win32_processor | select Caption,Name,AddressWidth,NumberOfCores,NumberOfLogicalProcessors,L1CacheSize,L1CacheSpeed,L2CacheSize,L2CacheSpeed,L3CacheSize,L3CacheSpeed,MaxClockSpeed | out-file d:\SQL\CpuComparison\CPUinfo.txt -append

This was astonishing when I compared among L1, L2 and L3 cache. Following was the result:

NumberOfCores             : 4
NumberOfLogicalProcessors : 4
L1CacheSize               :
L1CacheSpeed              :
L2CacheSize               : 6144
L2CacheSpeed              :
L3CacheSize               : 0
L3CacheSpeed              : 0

NumberOfCores             : 4
NumberOfLogicalProcessors : 8
L1CacheSize               :
L1CacheSpeed              :
L2CacheSize               : 1024
L2CacheSpeed              :
L3CacheSize               : 8192
L3CacheSpeed              : 0

Which one should I think as a better CPU? Any idea?

Size wise -> L1 < L2 < L3.
Speed Wise -> L1 > L2 > L3

CPU L1 cache is fastest. As there is more work on CPU, L1 is filled up first and the requests which are satisfied by L1 lead to the fastest response. In my scenario L1 cache is same. That is tiny also so no need to worry to much on this.

CPU L2 cache is slower than L1 cache. As soon as L1 is filled up, L2 is used. More size on L2 can help L1 cache to bear her burden.

CPU L3 cache is slowest among all. L3 bears over burden of L2.
In my scenario L3 is missing on the problematic server. This means there is no one to share the burden of L2. Having L2 smaller and L3 existence can give us better performance than having L2 larger and no L3. This was the whole reason why the CPU was was performing bad than other servers.

Along with technical learning I would like to share some great articles for anyone interested in the betterment of his/her family life

Online SQL/Powershell Classes @ http://tuitionaffordable.webstarts.com


One comment

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s