Like This Blog 0

Added by Don Jones August 2, 2013

No matter how well-designed and –build a business intelligence (BI) system is, it’s always possible that your workload will exceed its capabilities, and you’ll end up being dissatisfied with its performance. In those cases, what can you do? Your options depend a lot on the underlying database and analysis engine; for this article, we’ll assume that you’re using Microsoft SQL Server.

Hardware is obviously the first and most obvious thing you can do. Bigger, better hardware will almost always help achieve better performance, even in a somewhat poorly-designed system. Hardware is also one of the few things that you can change and upgrade without affecting any of the BI system’s software. After all, you’re not redesigning anything, you’re just migrating it to a bigger machine. More memory is often the first thing to try, since that helps improve SQL Server performance on a variety of levels. More and faster processors are a good option too, although that upgrade will generally entail moving to an entirely new server. More and faster disk storage can make a significant impact as well.

There’s also the option of examining how well the software is performing. Whether you’re using an off-the-shelf BI system or one that’s been custom-designed for your organization, you can generally examine the performance of that software without impacting it and without needing a great deal of special knowledge. For example, Microsoft has long offered a SQL Server Analysis Services Performance Guide for the various versions of Analysis Services. This guide focuses on examining the performance of queries within Analysis Services, and can help you identify poorly-performing queries. Even if all you’re able to do is take that information to your BI system vendor, it can lead directly to improvements. In some cases, you may be able to make some changes to SQL Server’s settings to help tune server resources and achieve better performance, all without changing the basic BI software.

Query performance analysis does require some specialized skills, although they’re often within the reach of any experienced IT professional who works with Microsoft products, especially if they have some basic SQL Server experience. There are also third-party vendors who make prepackaged Analysis Services performance “advisor” tools, which are much more automated and can be used by someone with less SQL Server experience. These tools can help identify problem queries, offer recommendations for changing SQL Server settings, and even create reports that you could deliver to your BI system vendor to get them to help you make performance improvements.

Dealing with performance problems in a BI system can be frustrating, especially in a prepackaged system that you’ve neither designed nor learned to deal with. Depending upon the responsiveness of your BI system vendor, performance problems may indicate that it’s time to bring some SQL Server skills into your organization, either by hiring someone or by developing a relationship with an independent subcontractor. And don’t forget that there’s always the hardware option: Moving to a bigger, better, faster server can often relieve performance problems simply by giving SQL Server more power to work with.

Don Jones
PowerShell and SQL Instructor – Interface Technical Training
Phoenix, AZ