No matter how well-designed and well-built your business intelligence (BI) system is, it’s possible your workload...
will exceed the system’s capabilities and you’ll end up dissatisfied with its performance. So what can you do? Your options depend a lot on the underlying SQL Server database and analysis engine.
Buying hardware is the first and most obvious step. Bigger, better hardware for SQL Server will almost always help achieve better performance, even in a poorly designed system. Hardware is also one of the few things that you can change and upgrade without affecting the BI system’s software. After all, you’re not redesigning anything; you’re just migrating it to a bigger machine.
Installing more memory is often the first thing many people try, since that helps improve SQL Server performance on a variety of levels. More processors and faster ones are a good option, too, though 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 or needing a great deal of special knowledge. For example, Microsoft has long offered its SQL Server Analysis Services Performance Guide that 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 can 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, though they’re often within the reach of experienced IT professionals who work with Microsoft products, especially if they have some basic SQL Server experience. There are also third-party vendors that make prepackaged SQL Server Analysis Services performance “adviser” tools, which are much more automated and can be used by someone with less SQL Server experience. These tools can help identify problem queries and offer recommendations for changing SQL Server settings. They can even create reports for your BI system vendor to get it to help you make performance improvements.
Dealing with plodding BI system performance 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 through hiring someone or developing a relationship with an independent subcontractor. And don’t rule out the hardware option: It’s easy to spend so much time trying to tweak software that a hardware upgrade -- which may seem expensive at first glance -- might ultimately be the cheaper route.
ABOUT THE AUTHOR
Don Jones is a co-founder of Concentrated Technology LLC, the author of more than 30 books on IT and a speaker at technical conferences worldwide. He can be reached through his website at www.ConcentratedTech.com.