3 Reasons Blackbaud CRM Scares Your SQL Server

Table of Contents
  • Blackbaud CRM Can Be Scary
  • The 3 Ways It’s Already Creeping Up On You…
    • 1. Everyone gets Queries
    • 2. Procedurally Generated SQL
    • 3. There’s so much Ad Hoc
  • Keeping the Monster Contained

Blackbaud CRM Can Be Scary

SQL Monster supercrop-1

I have a recurring nightmare where I’m being chased by Google Chrome. I keep throwing RAM at it, but it won’t stop. Everything is in slow motion, it feels like I’m running through benchmarks. I close more tabs, but it keeps coming, and I keep getting slower and slower.

I open Task Manager. So many processes. Chrome has me surrounded. I can’t end tasks quick enough. It’s breathing down my neck. I turn around to face it. It’s wearing a Blackbaud logo.

I wake up and shoot out of bed. I pry open every case in the house. All of my DIMM slots are still populated. My RAM is safe, for now. I take a deep breath and remind myself: A RAM hungrier version of Google Chrome doesn’t exist (yet). Until then, it can’t hurt me.

But Blackbaud CRM is STILL a monster. And one that is hungry for resources. It might look cute in the pet store, and you might have room in the aquarium, for now. But it’s going to grow, as monsters do, and if you aren’t prepared for it, you’re going to be living with a cantankerous beast that controls you more than you control it.

Blackbaud CRM is still a Frankenstein’s Monster compared to GPT-3’s Godzilla, but it can sure seem terrifying when you start budgeting in host systems. It takes some horsepower to run a properly secured and segmented Blackbaud CRM deployment.

At the end of the day, and following a winning recipe, you end up with a complex network of machines, separated into split environments, pared into comfy security zones, and all of that IS complicated, and resource intensive.

The 3 Ways It’s Already Creeping Up On You…

pexels-joonas-kääriäinen-239107

Some organizations might see their Blackbaud CRM systems hit the red-line on occasion, especially around expected busy periods like giving seasons and monthly reports. Often, they don’t think much of it. No Big Deal. They may as well tap their monitor to make sure that it’s working right.

They shrug it off as Atlas adjusting a crick in his neck.

And sure, things hum along. The spikes go down and you return to your usual programming.

But that giving season brought in how many donors? Your database grew how much? SQL Server has to dig through HOW much new data this time? And how many people are querying SQL?

You need to account for growth. You WANT growth in the first place, right? It’s good! Grow, prosper, may your DB flourish! But it all must go somewhere.

The growth of your environment, in many dimensions, should be an expected consequence of the growth of your organization. As the needs of your organization grow more complex, so shall the environment.

But plenty of people keep the same ol’ Atlas around, throwing weight on his world (Their world, really), letting anyone and everyone tap his shoulder to ask him a quick question, then get surprised when Atlas was a myth all along.

A fully equipped Blackbaud CRM environment that’s experienced some growth is going to be more RAM hungry than most organizations ever imagined, and we know that it’s hard to keep up with it’s appetite.

We get a lot of questions why, so here’s 3 reasons you might be seeing performance issues and misbehavior from Blackbaud CRM.

1. Everyone gets Queries

pexels-canva-studio-3194519

Blackbaud CRM is powered by the fact that most users authenticated into the CRM front end has some kind of access to query, for basic look ups and pulling day-to-day operational data. This greatly simplifies a lot of things, but it also means that ALL of these users are performing some action that queries SQL server. Everyone is poking Atlas for questions.

To further complicate things, not everyone is going to be efficient in the way they query the system. “Show me EVERY X, I’ll look for the date I want.” versus “Show me X’s that occurred on Y date through Z method” are very different queries to swallow. One is pretty specific, the other a little broad. Atlas is a big guy. Ask him at the wrong time? A lot? It might be accounting for something.

You’re not going to make every single team member a query-fu master, and that’s fine. You have to have enough resources to cover your users. The more users that you add, and the more that those roles rely on your data, the more taxing it’s going to be on your system as it taps away at the database for answers.

2. Procedurally Generated SQL

pexels-castorly-stock-3945638

Without going into a whole lot of detail, Blackbaud CRM generates queries that are custom brewed in C# within Blackbaud CRM itself. Overall, this makes for a very exotic, complex SQL query plan, and that alone makes it resource intensive to handle.

That’s okay, let Blackbaud CRM do it’s thing, it’s a monster, let it grow! But the translation act has a cost, and it’s hard baked into Blackbaud CRM.

Want to fix it? Write your own CRM, or go work for Blackbaud. Your resident SQL expert can’t do much about SQL generated from C# within the very structure of Blackbaud CRM itself. At least, not without way more effort than it’s worth.

Blackbaud CRM writes queries on-the-fly, all on it’s own, as part of what it does. Nimble, quick, built in access to this data is powerful, but there’s a cost to that agility and ease.

3. There’s so much Ad Hoc

AddHawkHarHar

You’ve probably planned something before. Let’s stick to birthdays.

You know what date it’s going to be, and hopefully, you’re correct. First hurdle accomplished.

You alert others of the date, and prepare cake-like resources in preparation for this date.

On the date, you execute a myriad of debatably comfortable rituals, you sing some copy-written material, and it all results in a one sided gift exchange. Somehow, this all makes sense to everyone but me.

But at the end of that day, having some kind of a plan laid out for all of those complicated gestures made them a lot easier to complete, and sticking to the same formula for any given birthday in the year make them easier to throw. Just having the DATE was a great start, right?

Having these parameters let you form a plan, and keep it concise. You weren't pooling the resources in case you need to throw a birthday party every day, you prepared for key dates.

SQL does something similar, in that it has a SQL Query Plan it's going to generate in order to process queries. When it's a one off query, it's ad hoc, and there's a price you pay twice for ad hoc queries.

When you execute a query in SQL, it gets compiled and optimized, and this is a hit to the CPU. But once a query plan is cached, it can be re-used to save on resources. Queries with the same parameters can re-use and stick to the plan that’s already in cache. When it's ad hoc, the plan is not going to be re-used.

Because Blackbaud CRM is procedurally generating queries, these parameters won't ever be exactly the same, meaning the plan can't really be re-used.

So at the end of the day, Blackbaud CRM is way more reliant on ad hoc queries than your team or current host may have imagined.

Keeping the Monster Contained

pexels-goochie-poochie-grooming-3299901

So to recap, we've covered 3 things that make Blackbaud CRM so hungry: All of the querying users, the procedural generation of those queries, and the ad hoc workload this creates.

A lot of this might sound like I’m calling Blackbaud CRM bloated or inefficient. This is far from the case! The features that make it so resource intensive are the same things that make it so powerful.

With that in mind, Blackbaud CRM is going to be hard on your SQL hardware. If you’re already experiencing some strain from the resource intensive nature of Blackbaud CRM, trust me when I say it can ONLY get worse if you don’t feed the beast, or understand it’s nature.

If you are dealing with a system that experiences frequent slow-downs and lock ups, especially when you’re at your most busy, you aren’t “making it work”. You’re at the mercy of a system that is trying to tell you something, and it’s a warning: This cage will not contain it much longer. It’s trying to get your attention. If you ignore it, it's going for the expensive shoes!

It isn’t just a matter of capitulating with a tyrannical, resource hungry machine either: You’re slowing YOURSELF down. Your whole team is bogged down with each failed connection, and each lock up. Each spike in latency is time lost. Same thing goes for any query that would have been faster on optimized hardware.

If your team or current hosting provider isn’t monitoring SQL adequately, you might not be able to keep an eye out for the warning signs that Blackbaud CRM isn’t happy.

That's why Concourse is around: To wrangle the most wily of Blackbaud CRM deployments, help them realize their true strengths, and let them thrive in an ideal environment that was tailor made to contain them. We might even teach them new tricks!

Is your Blackbaud CRM deployment trying to escape the lab? Want to learn more ways to build a Better Blackbaud CRM trap?

Sign up for our Better Ideas Newsletter!

Until next time!

Share this post

Recommended

You ever get the feeling like something isn't quite right with your current Blackbaud CRM hosting arrangement? You might be onto something.
Learn about the 3 most common reasons for slow queries in Blackbaud CRM: A poorly written query, resource contention, and index issues.