About Enterprise Singularity Team

Ad Hoc Query Performance Best Practices

By: Enterprise Singularity Team

Posted on: October 12, 2017

Optimizing performance in Ad Hoc Query can be challenging. The optimizer does well with simple queries, but struggles with larger datasets pulled from multiple tables. This is because the optimizer does not have time to examine every option and must use a simplified approach. Essentially, it looks for a few good first steps, then a few good second steps, and keeps working until it has a good plan or it runs out of time. With complex queries, it might run out of time before it can find the best approach. Given that we cannot always avoid complex queries, how do we ensure good performance? Queri... Read More

Evaluating SQL Server 2014 for Blackbaud CRM

By: Enterprise Singularity Team

Posted on: March 25, 2015

Update: A series of performance issues have been reported in BBCRM 4.0 using SQL Server 2014. After further investigation into the issues, it has been determined that the cause is Cardinality Estimator (CE) changes in 2014 compatibility level (CL) 120. According the Microsoft, “SQL Server 2014 marks the first, significant redesign of the SQL Server Query Optimizer cardinality estimation component since version SQL Server 7.0. This redesign may cause degraded performance with some workloads. “ For version 4.0, Customer and Blackbaud testing of SQL Server 2014 CL 120 demonstrated areas of improv... Read More

Recommendations for the Maximum Degree of Parallelism for Blackbaud CRM

By: Enterprise Singularity Team

Posted on: March 25, 2015

The Enterprise Performance Team investigated the Maximum Degree of Parallelism (MaxDOP) configuration setting in SQL Server 2014 to determine the optimal setting for Blackbaud CRM. When SQL Server runs on multi-core servers, the default MaxDOP allows the system to use as many processors as it deems appropriate to run a single statement for each parallel plan execution. You can adjust the MaxDOP setting to limit the number of processors to use in parallel plan execution. The performance team conducted a number of experiments to determine the best MaxDOP setting for Blackbaud CRM, and our recomm... Read More

SQL Server Extended Events

By: Enterprise Singularity Team

Posted on: December 3, 2014

SQL Server Extended Events (XE) is a great tool for analyzing database activity at a very granular level. It allows the user to capture very detailed information about a broad set of events. This is useful not only for troubleshooting performance issues but also for analyzing which events occur and the order they occur in.  XE was introduced in SQL Server 2008 but did not include a GUI that interfaces with the collected events. Fortunately, SQL Server 2012 provides a GUI component for XE within SQL Server Management Studio (SSMS).  This article will describe the benefits of using Extended Even... Read More

Data Compression in Blackbaud CRM Databases

By: Enterprise Singularity Team

Posted on: October 31, 2014

To improve the performance of your Blackbaud CRM databases, you can apply data compression in SQL Server. Data compression applies mathematical techniques to make database objects such as tables and indexes take up less space than they normally require. The benefits of compression vary based on factors such as the database and workload, but tests by the Blackbaud Enterprise Performance Team found significant space savings and performance improvements. One test even reduced the space that the database used by 44 percent. To help customers realize the benefits of compression technology, the Perf... Read More

Index Fragmentation’s Impact on the Blackbaud CRM Workload

By: Enterprise Singularity Team

Posted on: April 11, 2014

You’ve probably heard that indexes and database statistics are key to a healthy database. And that maintaining them is vital to getting the most out of your SQL Server system. Well, when someone tells me something like that, I want them to prove it! So the Enterprise Performance team conducted an investigation into the impact of indexes and database statistics. We used a realistic scenario and test data, and we observed substantial performance gains. Our test found that rebuilding indexes and updating statistics significantly improved response times for the most heavily used pages (less waitin... Read More

Custom Indexes in Blackbaud CRM

By: Enterprise Singularity Team

Posted on: April 11, 2014

Database indexes can significantly improve the performance of queries, search lists, and other features. Blackbaud CRM includes a variety of out-of the-box indexes to optimize core system performance, and it also allows you to add your own indexes to Blackbaud production tables when necessary. Before you add indexes to your Blackbaud CRM database, however, you should understand the performance tradeoffs that indexes can incur. Likewise, you should follow a handful conventions to make sure that your custom indexes are easy to find and will not be overwritten. To help database administrators and... Read More

Manage File Growth in Blackbaud CRM Databases

By: Enterprise Singularity Team

Posted on: February 10, 2014

As with any SQL Server database, managing the growth of your Blackbaud CRM database files can be critical to the performance of your database. For example, auto-growth is a useful tool in scenarios where files need to expand automatically because some abnormal event requires more space. However, while auto-growth is useful as a fallback mechanism, you do not want to rely on it for normal database growth because it can cause delays or even time-outs for end users. Similarly, monitoring the growth of your Blackbaud CRM database is crucial to plan ahead for storage space requirements. After all,... Read More

Writing Efficient Search Lists

By: Enterprise Singularity Team

Posted on: September 4, 2013

When you create a search list with the Infinity SDK, it is important to write an efficient SQL stored procedure so that the search list returns results as quickly as possible. To do so, you must adhere to a set of performance principles: Choose highly selective filters Use covering indexes to retrieve results Return only the columns that you absolutely need Limit the number of tables referenced by a query Additionally, consider the following advice when writing search lists: Avoid “or” statements – If you use an OR statement to link filters from different tables, the optimizer cannot use index... Read More

Privacy Policy | Sitemap | © 2011 Blackbaud, Inc. All Rights Reserved

Digital Ocean