Archive for June, 2011


CACTUSS June 2011

Thanks for the Memory

Last night, I had the good fortune of being selected to present at the local SQL Server User Group, CACTUSS.

This was my first presentation with the user group, and my first one on SQL Server, as well.  I have given technical presentations before, but it has been a couple of years.  So I was a little rusty when it came to pacing and how much content to try to include.

SQL Server Memory

My topic for the night was how SQL Server uses memory.  As a back story, I spoke about some of my experiences supporting and troubleshooting SQL Server over the years.  From there I rolled it up into some techniques and queries to spot problems.

Bit Off More Than I Could Chew

One comment I received, and I agree with, is that while I had plenty of good information, perhaps I could break it up into multiple presentations.  Even after removing several slides and demos I still went over time.

The other thing I realize is that instead of saving all the demos for the end, it may have been more effective to intersperse the demos within the presentation.  Present Topic 1, Demo Topic 1; Present Topic 2, Demo Topic 2, etc.

My audience was most gracious and stayed the entire time.  The best compliment for me was that several people were taking notes on what I was showing them.  Nice.  🙂

Disaster Strikes

As much as I practiced my presentation and related demos, I was not able to keep Mister Murphy at bay.  Once I first adjusted my Buffer Pool to introduce some Memory Pressure, I was no longer to connect and query the database.  I had to laugh and tell the audience that I just created some true Memory Pressure.

Slide Deck

If you are interested in the slides from my presentation, SQL Server Amnesia.  It doesn’t look like the free, hosted version of WordPress will let me upload a .ZIP or .SQL with all of my demo scripts.  My apologies, I’m still new at blogging.  I may publish the code later as a separate post.

Feedback

If you were there and would like to leave me some feedback, feel free to leave a comment.  Alternatively, you can use SpeakerRate to give me some feedback.

Club News

Before the presentation, Wes Brown (blog | twitter) gave us an update about the South Austin SQL Users Group and SQL Saturday Austin.  It looks like our space for the South Austin Group fell through for this month, so now we are hoping to try again for next month.

Likewise, the struggle for a SQL Saturday Austin got another twist.  It turns out the date we were shooting for has several other regions hosting a SQL Saturday at the same time.  So now, the official rumour date is December 3rd.  Cross your fingers…

After working hard all summer and saving your pennies you finally have enough money to buy a Lamborghini.  So you pedal downtown to the dealership and trade your jar of coins for a shiny new Fasterosa.  While driving home, you start to notice some bondo and shellac flaking off your pimp ride.  By the time you pull back into the driveway, you realize what you got was a Pinto.  Somebody done got swindled, and it was you!

CPU Throttling

CPU Throttling can occur when your Operating System and BIOS are set to Power Savings mode.  On certain types of computers, DEV boxes and desktop for example, this may be OK.  However, this is not something that you typically want on a Production Database Server.

CPUID

CPUID Utility

The best way to verify if you have CPU Throttling is to use the CPUID utility.  CPUID is a free utility that will give you correct information about you CPU speeds, cache, and memory.  It will even tell you which memory slots are in use and the specifics for that particular stick of RAM.  You can also use it to determine if you have Hyper-threading enabled.

Performant Green

For many organizations, being green is part of their mission statement.  Perhaps it is even part of your own personal philosophy.  However, as a DBA you must consider the cost / benefit analysis of Power Savings versus Performance.

Are these DEV servers, or do they run an application critical to your business?  Would slow performance cause safety problems for anyone who depends upon your data?

Licensing Costs

Another important factor is licensing and hardware costs.  SQL Server Enterprise Edition costs about $30,000 per processor.  How much do your servers and racks cost?

Add up all of your servers and CPUs and do a little math.  For example, if you have four servers with two CPUs each in your onsite data center.  Then, another four servers in your offsite data center.  That makes for a total of 16 CPUs with a licensing cost of approximately $480,000.  Let’s call this M, for money.

CPU Throttling

Next, figure out just how much throttling is going on.  Launch CPUID and look at the CPU tab.  First, look for the section named Specification; this is what you paid your hardware vendor for.  Let’s call this C, for CPU speed.  Next, look for the section named Core Speed; this is what your systems guys are giving you.  Let’s call this T for throttling.  Once you have those two numbers, it is time for some tricky division.

OK, let’s find out what percentage of your capicity you are actually using…

True Speed = T / C * 100

Looking at my example numbers, I would have:

True Speed = 1600 / 2800 * 100

True Speed = 0.5714 * 100

True Speed = 57%

Now, let’s use that number to find out how much money you are throwing out the window…

Wasted Money = (1 – T / C)  * M

Looking at my example numbers, I would have:

Wasted Money = (1 – 1600 / 2800) * 480000

Wasted Money = (1 – 0.5714) * 480000

Wasted Money = 0.4286 * 480000

Wasted Money = $205,714.29

Since we are true geeks, let’s use SQL Server to do the calculations for us.


-- CPU Throttling Analysis

declare @CPUSpeed as numeric;
declare @ThrottlingSpeed as numeric;

set @CPUSpeed = 2800;			-- CPUID Specification
set @ThrottlingSpeed = 1600;	-- CPUID Core Speed

-- calculate how much throttling is going on
select cast(cast(@ThrottlingSpeed / @CPUSpeed * 100 as numeric(5,2)) as varchar) + '%'
	as 'What your cruise control is set to...';

-- now, let's factor in your licensing costs...

declare @LicensingCost as numeric;

-- add up your total licensing costs
-- http://www.microsoft.com/sqlserver/2008/en/us/pricing.aspx
set @LicensingCost = 480000;

-- figure out how much money you are wasting
select '$' + cast(cast((1 - @ThrottlingSpeed / @CPUSpeed) * @LicensingCost as numeric(10,2))as varchar)
	as 'How much bread you''re wasting...'

Results

CPU Throttling Analysis

Business Cost

How much does it cost your business when critical processes take longer to complete?  Will you lose sales?  Will patients receive the care they need?

Are there costs associated when certain processes are not able to finish in a short amount of time?

If a customer has to wait on your application longer than the competition, then you may lose that customer.

Only you can answer these questions, they will be different for every business.

This one is a little harder to quantify.  After you have gotten management’s attention with the money figures from above, use this angle to tie everything back to the business.  Say it with me, you are not a DBA, you are a Business Partner.

If you can create a simple spreadsheet or PowerPoint presentation which illustrates this, then you will have a much easier time of convincing management of your proposal.

CPU Utilization versus CPU Speed

One misconception is that if you do not have high CPU utilization, then you do not need to worry about CPU throttling.  If look at my example, you will see that we are not maxing out the CPU, so why worry about it?

CPU Utilization

CPU Utilization and CPU Throttling are two different ideas and have different affects upon the system.  CPU Utilization is telling us how many of the CPU cycles we are using.

Consider a highway.  Let’s say we have a six-lane highway, but only three lanes have traffic in them.  This is 50% utilization.

Next, let’s consider the Speed Limit.  If the same six-lane highway has a speed limit of 35 MPH, then it will take us a while to get out of Dodge.  Remember the Lamborghini you bought?  If the engine has a governor that keeps you legal, then you may as well have bought a Pinto.  That is throttling.

So, even though your utilization is low, i.e. lots of lanes are open, you may have the cruise control set to half of what the car is capable of.  You have lots of cycles available for processing; they’re just running at half speed.

Ticking Out the Camaro

The fix for this is pretty easy, but it takes two steps.  First, you will need to check your BIOS settings and see what your Power Management settings are currently set to.  Each manufacturer is different, so my screenshot my not reflect the hardware you have in your shop.  However, it should be fairly similar.  The bad option is highlighted in red, while the good option is highlighted in green.

BIOS Power Savings The Bad with the Good

Next, under the Control Panels, select Power Options and make sure High Performance is selected.

Power Savings Turned Off

Trust, but Verify

Once you have taken care of these two steps, run CPUID again and verify that you are getting all the CPU that you have paid for.  Have fun, and enjoy that Lamborghini; you’ve earned it!