Blog

What Is Commodity Hardware?

You may have heard the term “commodity hardware” thrown around when describing solutions like Redis, Elasticsearch, or kCura’s new Data Grid.

Commodity hardware refers to cheap, standardized servers that are easy to buy off the shelf from any vendor. Here’s a typical example of a 2u, 2CPU commodity hardware server:

SYS-1028R-TDWSay we buy a few parts to get ‘er started:

  • Two Intel Xeon E5-2623 v3’s (quad core) – $900 total
  • 128GB RAM (using 8GB DIMMs) – $1,920
  • Two 512GB SSDs for fast storage – $450
  • Six 4TB hard drives for slow storage – $900
  • Grand total: $5,070

Not bad. Want a little more power? Here’s a faster config:

  • Two Intel Xeon E5-2623 v3’s (quad core) – $900 total
  • 256GB RAM (using 16GB DIMMs) – $3,500
  • 8 1TB SSDs – $2,600
  • Grand total: $7,900.

The term “commodity hardware” used to mean really crappy gear, but when you look at these numbers, that’s not necessarily the case anymore. You can build yourself quite the army of pizza boxes.

When vendors say, “You can deploy our solution on commodity hardware,” they’re not saying you’re going to get amazing performance with 16GB of RAM and a couple of spinning rusty frisbees. It’s time to reset your expectations about what commodity means.

Who’s Allowed to Add Indexes to ISV Apps?

Performance tuning of independent software vendor (ISV) databases is a little tricky.

To understand who does what, let’s think through all of the work required with building and hosting a database application:

Who adds indexes and tunes queries?

Who adds indexes and tunes queries?

Typically the two parties involved – the vendor and the customer – start at opposite ends of the table. The software vendor starts by designing their tables, and gradually works down. The customer starts by buying hardware, and works their way up.

In the middle, things kinda fall apart.

When I was a DBA, I thought it was the vendor’s responsibility to put the right indexes on their tables. It’s their app, right? Aren’t they supposed to know how people query their own app? Why am I responsible for reinventing that wheel? Will I lose support if I add indexes?

Now that I’ve worked with a lot of ISVs, I see things differently. Here are their challenges:

Every customer uses the app differently. Some customers really hammer a particular feature hard, whereas others never use it at all. If you index for every possible feature/query, then the database will be dead slow for delete/update/insert operations.

Customers don’t give feature usage or index usage data back to the ISV. Software vendors have no way of knowing what indexes are working well out in the field, or sometimes even know what queries are running on the server. This comes back to our responsibilities grid at the beginning of the post – often even the customers aren’t running any performance monitoring software on their app at all.

Customers won’t upgrade to current versions quickly. Sometimes the ISV fixes performance by improving queries or adding the right indexes, but they only add it in the current version of the software. It takes development time to backport the fixes to older versions, and software vendors only have so much time. If you ask end users if they’d rather pay for new features or backporting stuff to older versions, they vote with their wallet.

Some customers have really bad DBAs (or none at all). Time and again, I’ve seen “DBAs” add dozens – or in one case hundreds – of indexes to an ISV’s tables in an effort to improve performance. The first few indexes make things better, so they start solving every problem by adding more indexes. You can guess how that ends up. As a result, the ISV’s support team starts making rules like “no customer is allowed to add indexes.”

Building a Successful Relationship With Your ISV

When you install a software vendor’s database, show them the list of responsibilities at the top of this post. Say, “I agree to do the stuff in my column – if you’ve got any guidance on things you’d like me to do for your application, send ‘em over, and I’ll be glad to follow them.” For example, the vendor may have a list of trace flags they’d like to enable for their app.

Then say, “For the stuff in the middle, who’s responsible?”

If the vendor agrees to tune indexes, then you’ll probably need to give them index usage data and plan cache data on a regular basis to help them tune. I like to be my software vendor’s best customer – I’ll say, “I’ll send you the output of sp_BlitzIndex® and sp_BlitzCache® every week or every month, whatever works for you. I’ll be glad to take my time working with you on the output, and together, we can make the software experience awesome for everybody.”

You’ll be surprised by how often they accept because they’re desperate to find customers willing to give them some diagnostic data about how their software works in the wild.

If the vendor says you’re responsible for tuning indexes, then:

  • Instead of dropping their indexes, disable them, and let their support team know which ones you’re disabling
  • Create your own indexes with your company’s initials, like BGO_CustomerNumber_CustomerName, so you can quickly identify which ones are yours versus which shipped with the product
  • Run sp_BlitzIndex® every month to make sure your indexes are actually helping, not hurting
  • When it’s time to deploy a new version of their app, enable the vendor’s indexes again first, drop your own indexes, run their deployment scripts, and then set things back the way you had ‘em (this way you minimize the chance of their deployment scripts breaking)

There’s no right or wrong choice – for example, Sharepoint customers aren’t allowed to touch the database, whereas SAP customers are encouraged to do their own index tuning through the SAP UI. The key is knowing which method your vendor supports right from the start. Otherwise, both sides just assume the other side is taking care of the problem.

Brent’s Bad Idea Jeans [Video]

Over the years, I’ve come up with some rather “interesting” answers to SQL Server problems including:

In this 20-minute video, I’ll explain what drove me to these crazy solutions, and it’ll be up to you to decide whether they’re awesome or awful.

For questions & answers about these, tune in to our Tuesday webcast.

Black Friday Sale: Free $299 for the Last Holdouts

All day long, you’ve been watching and waiting, thinking you could resist a killer deal.

Black-Friday-Sale

Coupon LastHoldout299 gets you $299 off any of our $299 training videos, including:

Move fast – it’s only good for the first 5 buyers.

And with that, we’re done for the day! See you next year.

Black Friday Sale: 40% Off Training Videos

So you didn’t get in on the midnight doorbuster sale for 50-90% off our training videos, and you’re jealous of all the cool kids who got up early.

Black-Friday-Sale

Coupon FunTrainingAtHome will take 40% off any of our online training videos including:

This discount is in place for the rest of the day, with no limit on buyers. Go check ‘em out!

Black Friday Sale: 30% Off In-Person Classes

So you didn’t get in on the midnight doorbuster sale for 50-90% off our in-person classes, and now you’re kicking yourself because your boss gave you permission to attend.

Black-Friday-Sale

Coupon EasyPainRelief will take 30% off any of our 4-5 day in-person training classes. Now, instead of the first number in their price being a 3, it’s a more palatable 2. Probably even more palatable than that cranberry salad you’re warming up.

This discount is in place for the rest of the day, with no limit on buyers.

Good luck, and hope to see you in Chicago, Denver, or Portland!

Black Friday Sale: Learn to Read Execution Plans for $99

You’re comfortable writing queries, but some of them are slow – and you need more ways to tune than just adding indexes. You’ve heard that you should read execution plans, but you don’t know where to start.

Black-Friday-Sale

In this 5+ hour video class taught by Microsoft MVP Jeremiah Peschka, you’ll:

  • Learn how to read SQL Server execution plans
  • Understand how different query syntax changes plans
  • Explore the plan cache to find problem queries
  • Learn solutions to common query problems

You even get sample code to download, plus queries to check your own system and find the worst execution plans that need help.

You can watch it for 18 months on your desktop, laptop, iPad, or iPhone.

The first five people who use coupon code LearnToRead99 get the course for just $99. Go!

(Oh, and we may have activated a few more of those Doorbuster50 codes for 50% off, too.)

Black Friday Sale: Free $29 Coupon – And a Test

SQL Server’s Maximum Degree of Parallelism (MAXDOP) controls the number of processors that are used for the execution of a query in a parallel plan.

Black-Friday-SalePop quiz: what’s our favorite knowledge base article for recommendations and guidelines for MAXDOP?

That KB# is your coupon code good for $29 off our training classes or videos – even the $29 videos! Good luck – it’s only good for the first 10 buyers.

How Would You Change AlwaysOn Availability Groups?

SQL Server 2012 introduced AlwaysOn Availability Groups, a way to achieve high availability, disaster recovery, and scale-out reads. SQL 2014 brought some improvements around higher uptime and more scale-out, and all signs point to continued improvements in the next version of SQL Server, too. (I love it when Microsoft brings out features like this and continues to invest in them over time.)

A lot of the emails I get start with, “I’d like you to help me implement AlwaysOn AGs,” but it’s funny – most of the projects don’t end up actually deploying AGs. There’s a few barriers to adoption, and even when you’ve built an Availability Group, management can be a little tricky. Don’t get me wrong – I love the feature – but it comes with some surprises.

Rather than me prejudicing you, I’ll just put it out there as a question:

How would you change AlwaysOn Availability Groups?

Leave your answer in the comments. (And yes, Microsoft is watching.) Bonus points if you link to your Connect request.

SQL Server’s Cost Threshold for Parallelism

“Should a query get to use more than one CPU core?” That’s an important question for your SQL Server. If you’re not sure what parallelism is, get started by exploring the mysteries of CXPACKET with Brent. He’ll introduce you to the the setting, ‘Cost Threshold for Parallelism’.

Let’s test Cost Threshold for Parallelism

I generate an estimated execution plan for the following query. I’m running against a copy of the StackOverflow database that doesn’t have many indexes.

SELECT COUNT(*)
FROM dbo.Posts
WHERE PostTypeId=2

I get a parallel plan with an estimated cost of 272.29. (I can tell it’s parallel because of the yellow circle with double arrows on three of the operators.)

execution plan parallel

I decide I’m going to test out ‘Cost Threshold for Parallelism’ and make this plan go serial. This is a server wide configuration, but I’m on my test instance, so no worries.

exec sp_configure 'cost threshold for parallelism', 275;
GO
RECONFIGURE
GO

I run my query again and look at my actual plan this time…

Actual plan- paralleism

Hey wait, that’s still parallel! It has the same estimated cost, and that cost is below where I set the cost threshold for parallelism. This seems broken.

At this point, I might get confused and think SQL Server was using a cached plan. But changing configuration options like cost threshold for parallelism will trigger recompilations– that shouldn’t be it.

What ‘Cost’ is SQL Server Using?

The secret is in the serial plan. I need to look at the estimated cost for this query — it’s the same as our original, but I’ve added a MAXDOP 1 hint to force a serial plan:

SELECT COUNT(*)
FROM dbo.Posts
WHERE PostTypeId=2 OPTION (MAXDOP 1)
GO

Estimated cost - serial plan

The estimated cost for the serial version of the plan is 287.963, which is over the threshold I set at 275! This is the cost that is being considered and which is above the bar I set for who gets to go parallel. I can prove it by raising my cost threshold to just above this level:

exec sp_configure 'cost threshold for parallelism', 288;
GO
RECONFIGURE
GO

And now when I run my query (with no maxdop hint to force it), I get a serial plan.

serial plan because of cost threshold

Cost Threshold Doesn’t Just Apply to the Plan You See

Behind the scenes, the optimizer is considering many possible plans. The Cost Threshold for Parallelism setting comes into play during that process and uses costs that you don’t see in the final plan.

This can be tricky to remember. Special thanks to Jeremiah, who’s explained this to me in the past (more than once!) This is covered in SQL Server Books Online, but oddly enough most of us don’t seem to find it there.

css.php