3 Quick Fixes for Slow Queries in Blackbaud CRM

Why Slow Queries Happen

Slow queries in Blackbaud CRM. Everyone gets them. But do you know why?

• Poorly Written Query: Kind of a no-brainer. If you write a bad query, it might not perform well.
• Resource Contention: This one can take many forms: insufficient server resources (RAM and CPU), maybe a shared server where other databases are competing for resources, you're running up against back-end processes like backups, other greedy, long-running (bad?) queries, or concurrency issues due to default values for CTFP and MAXDOP.
• Index Issues: Indexes and queries go hand-in-glove. If you don't have a good handle on your indexes, you're guaranteed slow queries in Blackbaud CRM. Somebody needs to be minding the shop and doing index maintenance. Plus, you need to understand what indexes you have and leverage those indexes in your queries.
• Parameter Sniffing: SQL server creates an execution plan when a stored procedure is run for the first time. That plan depends upon the values sent during that first run. Think of the plan as being "optimized" for those values it first got. The plan may not be optimized for other values - those of YOUR query. In other words, a similar query by another team member might have hijacked the plan.

Slow Queries Quick Fix #1: Tweak Your Query

Writing slow queries is a rite of passage. You're going to need to fail a bit before you get good at it.

If you're anything like me and have a stubborn streak, you're going to struggle with this one. After all, you spent a good half day writing your query. And now you're pretty attached to it. You will try literally every other option before rewriting it.

Learning how to write a good query in Blackbaud CRM is as much an art as it is a science. Slow queries in Blackbaud CRM commonly result from bad logic. SQL logic, that is. What makes logical sense to you might be hard/impossible for the query engine to decipher.

If you want to get faster queries, ask better questions. Here are some simple tips to keep in mind.

• Start Small With Sample Groups
  • Narrow your search result.  Do this by first testing the performance of new queries against preset sample groups.
  • For example, instead of looking for all constituents, filter them by a certain attribute that only some have. Ideally, this should not just be yourself and other staff, but actual constituents. Typically, a sample size of at least 100 is a good starting point. The idea is that you want it to be representative across different types of test queries.
  • If your query doesn't perform well with your sample group, you know you have a bad query. Once you get the particulars down, swap out the sample group for the full set you're hoping to query against.
• Swap "OR" Logic With Merge
  
  • Over reliance on OR makes for slow queries. SQL may have to read all the data from a table -- or multiple tables. If it's just one table, OR logic might be OK. More than one table, you start to run into trouble. The SQL engine must search far more records in search of possible matches.
  • Instead of relying on "OR" logic, do a Merge. Break your query into two (or more) simple queries. Think definitive questions. Aim for exact matches. Be as selective as possible. Then combine them together, Venn diagram style.

A Merge is really a SQL UNION statement behind the scenes. If you're curious why the SQL engine prefers UNION ALL over OR, check out this article by Bert Wagner.

• Know Your Indexes: Want to graduate from query kindergarten? Learn about the indexes. Not just in theory, but the actual indexes in place on the SQL tables you're querying against.
  • Run your query in an isolated environment like Dev. Then use SQL Profiler to capture the SQL statement that CRM creates.  Run the captured SQL in a SQL query window and examine the plan. Don't know what I'm talking about? Read this post by Hugo to get started.
  • The point here is this. Indexes are the real heroes. They do the heavy lifting behind the scenes to give the answers you seek. Learn about them.
• Have a Query Buddy
  • Work with another team member. Show each other your slow queries. And try to fix them. A fresh pair of eyes can do wonders. Plus, maybe you can work out other things together, too. Naming conventions anyone?

Don't query alone. For the good of all humanity!

Slow Queries Quick Fix #2: Change Defaults for CTFP and MAXDOP

Blackbaud CRM requires high levels of concurrency. Meaning, lots of stuff happening at once. SQL Server defaults for just the opposite - low concurrency. Not good if you have Blackbaud CRM!

Go into the Server Properties on your SQL Server instance.  Under "Advanced" you'll find the two parameters that need changing:

• Cost Threshold for Parallelism (CTFP)
  

Change to 50 (Default is 5).

Monitor. Adjust up or down accordingly.

CTFP controls the "cost" between having a query stay single-threaded or going parallel and using multiple threads. The default cost of 5 means virtually all queries go parallel. While that may sound great, you've only got so many threads you're working with. You want to reserve parallel plans for those queries that really need it. Simple, low cost queries should remain single threaded.

If you don't change this settings, nearly every query will get to go parallel. Then your server becomes thread starved (THREADPOOL WAIT), keeping a lid on your concurrency.

• Max Degree of Parallelism (MAXDOP)
  

The setting here depends... (see link below).

Follow this advice from Microsoft directly.

When a query goes parallel, MAXDOP controls how parallel. MAXDOP controls the number of cores and threads per concurrent parallel branch that a query can use.

For example, in the Concourse Private Cloud, the underlying host hardware configuration has 2 physical processors of 8 cores each for a total of 16 cores. A Blackbaud CRM Production SQL Server may have 12 cores assigned to it as 2 NUMA nodes, each with 6 cores. In this instance, we assign MAXDOP = 6.

Warning! Some individual queries may actually be slower after you change the setting CTFP and MAXDOP.

For example, a medium cost query - say 25 - that otherwise would have gone parallel, instead stays single-threaded. Therefore, this one query may be slower as a result. If you see more problems than benefits after changing CTFP to 50, bump it down to 25.

Every Blackbaud CRM installation experiences different workload behaviors. Ideally, you have the option to change these values. What you for sure do not want are the default values.

Keep the big picture in mind. More concurrency means more things can get done in SQL at the same time.

Slow Queries Quick Fix #3: Setup Index Maintenance

SQL indexes get fragmented over time. Data moves around. An index that pointed to data that moved is not going to be as efficient. And you know the one thing that happens in Blackbaud CRM?

Data moves.

A lot.

Therefore, you really need to keep your indexes fresh and happy. To help maintain indexes for our Blackbaud CRM hosted customers, we rely on Ola Hallengren's SQL Sever Maintenance Solution. It's single script that you can run against your SQL server. This one scripts adds a number of helpful jobs for running common maintenance tasks like backups, integrity checks, and index maintenance.

Index maintenance involves 3 different things:

• Reorganize Indexes
  

Once indexes reach a certain level of fragmentation, they become inefficient. Specify a set threshold and reorganize indexes once they exceed this threshold. While this can be done while the index is in use, index maintenance should always be done after hours during a maintenance window.

We reorganize Blackbaud CRM database indexes once they reach 20% fragmentation.

• Rebuild Indexes
  

If fragmentation is even higher and has got the better of your index, it's better to rebuild. A rebuild drops the index and makes a fresh one. Yes, this causes blocking. Never ever do this during business hours! Maintenance window, my friend.

We rebuild Blackbaud CRM database indexes once they reach 50% fragmentation.

• Update Statistics
  

Indexes don't know exactly where any bit of data lives - only where it's likely to be found.  Statistics keep track of this likelihood. Keeping statistics up to date is a trade off. If you update them, query plans have to recompile the next time they run. That's a performance hit. But it happens only once the first time the query is run and the plan is generated.

We tend to update modified statistics daily for Blackbaud CRM databases.

Avoid Server Reboots

Do you "fix" your slow queries in Blackbaud CRM SQL with a reboot? If so, you're likely fighting one of these issues:

• RESOURCE_SEMAPHORE

SQL server plays a tight rope act with memory. A certain percentage goes to the buffer pool (cache). A certain percentage goes queries (memory grants). If your SQL server doesn't have enough RAM, it can't keep all the query plans around. Your query plan may be getting flushed to make room. Which means you're subject to parameter sniffing again.

• Parameter Sniffing
  

I know I know.  I already mentioned parameter sniffing as a possible culprit for slow queries.  Is it sinking in yet?

Parameter sniffing is a tricky beast. You can't eliminate parameter sniffing totally. Sometimes you'll get a bad query plan due to parameter sniffing. When this happens, somebody experiences slow queries. I recommend you learn from somebody who knows way more than me on the topic. Brent Ozar has several great articles such as this one which address parameter sniffing.

The reason a reboot feels like it fixes slow queries is because all the query plans are flushed. The first time the query is run after the reboot, a new query plan is generated. So the race is on after a reboot. If your query runs first it will generate a favorable plan.

SQL server keeps stats on index usage. Every time you reboot, these stats are reset. So if you're trying to learn about index usage on your system, you can't. You're shooting yourself in the foot with every server reboot. The server should be running for at least a week in order to have decent stats on index usage on your system.

Brent Ozar also has some great free SQL tools for checking on performance issues like parameter sniffing. Trouble is, every time you reboot SQL - all the performance checking data is gone. We use a SQL monitoring tool called Sentry One that keeps statistics across reboots.

Instead of a Reboot Try This

Instead of a reboot, try updating statistics (from #3 above).

Updating statistics forces query plans to recompile. But you won't lose the valuable stats on index usage like you do with a reboot.

In most cases, we update statistics for our Blackbaud CRM customers nightly!

Hosting Matters

Inevitably, your hosting situation for Blackbaud CRM comes into play when addressing the issue of slow queries. If you don't have control over your hosting, you're only going to be able to work on the first issue - poorly written queries.

The issues of resource contention and index issues can only be fixed with some control over SQL server. If you're not sure, ask your team if working with your hosting provider or on-premise DBA is an option.

Without some level of direct control over SQL server, you cannot optimize for your Blackbaud CRM instance. And slow queries will haunt you.