L2 vs. L3 processor cache in SQL Server Performance

Question

L2 vs. L3 processor cache in SQL Server Performance

sqlgiant

SSC Eights!

Points: 985
More actions
March 23, 2005 at 7:30 pm

#109643

I know that axiom that "more L2 processor cache is good for SQL Server 2000", but is it also true that "more L3 cache is good for SQL Server 2000"?
I'm going nuts trying to figure out what the benefits of L3 over L2 cache might be a Compaq ML570 quad 2.2 processor with Xeon MPs. We'll be running SQL 2k/sp3 on Win2003 OS. I've never been this close to hardware so I'm afraid that Joe Chang's wonderful system architecture articles on this site are just lost on me.
My Performance metrics show this server is not processor or Memory bound, but would be more likely to become memory bound over time. The current ML570 has 4 x 700 mHz Pentium IIIs with 1 MB L2 Cache, and those things are barely breaking a sweat. I don't want to drop the L2 cache down but it looks like the new Xeon MPs tend to be lighter on L2 but heavy on the L3 cache (512KB L2/1, 2, or 4 MB L3).
Thanks in advance to any who can address this. Much appreciated!
Stuart
Stuart
"Chance is always powerful. Let your hook be always cast. In the pool where you least expect it, will be a fish" - Ovid

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

colin.Leversuch-Roberts SSC Guru Points: 52643 More actions · Answer 1

The zeon 4's will leave your zeon 3's standing and give you hyperthreading too. I wouldn't worry too much about the procs and get as much ram as you can.

I run an 8 way 3ghz P4 cluster on w2003 and I average about 9% cpu - the old 4 way P4 did about 25% and the previous 4 way P3 was up around 60-70%

[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/

Antares686 SSC Guru Points: 125444 More actions · Answer 2

It is all about the distance it takes to reach the data. Basically the L1-3 are various distances from the processor at which data can be stored during processing as oppossed to being in the RAM.

The closer you can keep the data to the processor during processing the faster the results will be. This has to do with bus speeds and speeds of the equipment it is stored in itself.

Basically you want as much L2 (on modern processors) as then as much L3 (but newer processors are starting to move L3 on them with even higher bus speeds see http://www.newegg.com/app/ViewProductDesc.asp?description=19-116-189&depa=0) then as much RAM (as well as fastest) as you can get (and use) along with a high bus speed motherboard. But then RAM is least expensive and unless you are doing an extreme number of calculations on the data your biggest concern is the RAM anyway.

http://www.computerhope.com/jargon/c/cache.htm

sqlgiant SSC Eights! Points: 985 More actions · Answer 3

Thanks for the replies. Very helpful.

Sounds like cache is cache, and that the new Xeon MPs at 2.2 GHz with 512K L2/1MB L3 along with a faster bus will be a huge improvement over the curent 1 MB L2 on the Pentium III/700s.

Since I'm not stressing the older processors now, I'll resist the desire to "buy up" on cache and settle for something sensible for our needs.

Thanks again.

Stuart

"I not only use all the brains I have, but all I can borrow" - Woodrow Wilson

Stuart

"Chance is always powerful. Let your hook be always cast. In the pool where you least expect it, will be a fish" - Ovid