Don’t get bogged down by sluggish BI system performance -- speed it up

Don’t get bogged down by sluggish BI system performance -- speed it up

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

    Requires Free Membership to View

    Login

    By submitting your registration information to SearchSQLServer.com you agree to receive email communications from TechTarget and TechTarget partners. We encourage you to read our Privacy Policy which contains important disclosures about how we collect and use your registration and other information. If you reside outside of the United States, by submitting this registration information you consent to having your personal data transferred to and processed in the United States. Your use of SearchSQLServer.com is governed by our Terms of Use. You may contact us at webmaster@TechTarget.com.

Premium Access

Register now for unlimited access to our premium content across our network of over 70 information Technology web sites.
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

This was first published in December 2011

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.

For more on BI system performance

Get tips on improving BI system performance

Learn how in-memory technology can enable faster BI system performance

First time maintaining BI system performance? Here are six guidelines

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.

Join the conversationComment

Share
Comments

    Results

    Contribute to the conversation

    All fields are required. Comments will appear at the bottom of the article.

    Disclaimer: Our Tips Exchange is a forum for you to share technical advice and expertise with your peers and to learn from other enterprise IT professionals. TechTarget provides the infrastructure to facilitate this sharing of information. However, we cannot guarantee the accuracy or validity of the material submitted. You agree that your use of the Ask The Expert services and your reliance on any questions, answers, information or other materials received through this Web site is at your own risk.

    Back to top