SQL Server business intelligence performance baked in for most SMBs

Get expert advice on the challenges that small and medium-sized businesses with SQL Server business intelligence systems can face in trying to improve their BI performance.

More and more organizations are implementing business intelligence (BI) systems, including many small and medium-sized businesses (SMBs) that previously considered BI tools to be out of their reach. As these companies make their forays into the world of BI, often with SQL Server as the underlying database, one of the most common concerns they have is about system performance -- and with good reason.

Large enterprises often can devote millions of dollars in IT resources to their BI systems: powerful software, mega-sized servers, massive amounts of disk storage, dedicated teams of technical workers, and so on. Those things can significantly improve the performance of a BI system – but they’re definitely not in scope for most SMBs. So, what can smaller organizations do?

BI systems are designed to provide answers to business questions, typically by consolidating disparate information from systems across a company and letting end users run queries or reports against the data. For example, you might be able to take a discrete fact -- perhaps customer satisfaction metrics are falling -- and trace the reason for that back to its source. Let’s say your company has been heavily marketing a particular product and its sales are increasing, but the product is easily damaged in shipping and customers are obviously unhappy -- and your shipping insurance claims are going up. It’s the ability to consolidate all of that information into a single tool set that makes BI so powerful.

There are, broadly speaking, two ways of working with a BI system. The most traditional way is via a data warehouse. Structured specifically to respond quickly to queries that involve a number of different criteria, most data warehouses are updated at pre-set times, meaning they don’t act against live data but instead operate with snapshots of information from specific points in time. The performance of these systems is primarily derived from the underlying database design and from the processes that load data into the data warehouse from various organizational sources.

The in-memory approach to SQL Server business intelligence

A somewhat newer technique is in-memory analytics. It can also be used to pull data from multiple sources, but the BI connections between disparate data can be made in real time, and the information is stored in the memory of an analysis server. In addition, in-memory systems can support hybrid approaches that combine real-time data with historical information from a data warehouse.

Microsoft Corp.’s PowerPivot for Excel software, which operates in conjunction with SQL Server, is one example of a tool with in-memory analysis capabilities. In-memory analytics performance is mostly a function of how powerful the analysis server itself is: The more memory and the more (and faster) processors that a server has, the faster it should be able to process queries.

To improve the performance of an existing BI system, you really have only two options:

  • Design a more efficient system, or
  • Run it on faster and more powerful hardware

The first option isn’t something that most SMBs are likely to have under their control. Often, SMBs with limited internal IT resources purchase pre-built BI systems that combine a data warehouse and BI software in what is effectively a black-box server. Or else they deploy systems that were designed and built for them by a consultant.

The second element is easier to manage: Buy a bigger server. Or, for larger and more complex BI systems, split the workload across multiple servers. But scaling out in that way is complex and often requires a certain amount of redesign work to take advantage of database techniques such as data federation and data partitioning -- which may put it beyond the budgets and IT capabilities of many SMBs.

System design may dictate SQL Server business intelligence performance

Of course, scaling up a single server also costs money. The bottom line is that unless you’re able to invest in new or upgraded hardware, the upper limits of your BI performance are often determined in advance by the design of the packaged BI system you choose, including how well it makes use of SQL Server or whatever other database platform it’s built upon.

As a result, if you’re the buyer or architect of a BI system for an SMB, performance should be your top concern after the data analysis features your organization needs in order to meet its business goals. Make performance a real focus of your technology evaluation efforts: Ask to see demonstrations of packaged systems running workloads with a data set that’s representative of the amount of information you anticipate having in your BI system.

You also can grill the vendors about their system designs. The process, though, is like buying a car: It's interesting to ask about the kind of engine that a vehicle has -- but ultimately, you need to take it on a test drive because there's more than just the engine at play. With BI systems as well, there are a lot of complex, interconnected components, and the only way to check their combined performance is to try them.


Don Jones is a senior partner and principal technologist for strategic IT consulting firm Concentrated Technology LLC.

Dig Deeper on SQL Server Business Intelligence Strategies