SQL Server BI performance for the uninitiated

Maintaining a SQL Server business intelligence (BI) system may be challenging for many database administrators, but there’s a lot you can do to ensure top SQL Server BI performance.

Let’s say your company has purchased a business intelligence (BI) system that’s based on Microsoft SQL Server. The system was designed and implemented by outside consultants (a common scenario for BI systems), and now it’s up and running. 

You, being the “Microsoft guy” (or gal) in your organization, have been saddled with the task of keeping the BI system’s back-end database properly maintained. You’re not necessarily a SQL Server expert, and you’re definitely not a BI expert. You’re a “reluctant DBA,” and you just need to know enough about SQL Server BI performance to keep the thing running smoothly.

Here’s what you need to know:

Don’t mess with the database design, and definitely don’t mess with the indexes that have been created on the database. BI systems are incredibly finicky about their database schema, and performance is heavily dependent upon the indexes.

Do maintain the indexes. If you can, ask the folks who created the system how often those indexes should be rebuilt. If the BI system is routinely loaded with data (a very common approach), then you’ll often want to at least reorganize the indexes after every major data load. That can take a long time on large data warehouses, so be sure to plan the reorganization as part of a scheduled maintenance plan. Some BI systems are set up to do this themselves; ask the people who implemented it if that’s the case with your system.

Don’t load other tasks onto the database server that drives the BI system. SQL Server BI performance is often calculated with the assumption that the BI system will be the only thing using the server, and adding workload will affect that performance.

Do keep Windows and SQL Server properly patched, just as you would for any machine.

Don’t upgrade SQL Server versions without first ensuring that you won’t be causing any incompatibility issues. A new version of SQL Server will rarely produce any kind of immediate gain unless the BI system can take advantage of specific new features, so there’s rarely a pressing need to upgrade SQL Server. It’s very common, in fact, for organizations to be running several versions of SQL Server, since applications are almost always paired to the specific version they were written for.

Do find out if you need to back up the BI system’s databases. The answer is usually yes, of course, but not always. Some BI systems get data directly from production systems, meaning the BI system may not actually contain copies of that data. Understanding if, and, more important, how often, backups are needed is very important. Also make sure you understand what kinds of backups are necessary. For example, some BI databases are built to run without transaction logs, making more-frequent full data backups advisable.

You can get the answers to most of your “what do I need to maintain” questions from whoever set up the BI system, and it’s a good idea to document those answers for posterity.

ABOUT THE AUTHOR

Don Jones is a senior partner and principal technologist at strategic consulting firm Concentrated Technology. Contact him via www.ConcentratedTech.com.

Next Steps

Random text added to the Next Steps field that incldues a link to cnn.com. Read further to get the full story.

Dig deeper on Microsoft SQL Server Performance Monitoring and Tuning

Pro+

Features

Enjoy the benefits of Pro+ membership, learn more and join.

0 comments

Oldest 

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to:

-ADS BY GOOGLE

SearchBusinessAnalytics

SearchDataCenter

SearchDataManagement

SearchAWS

SearchOracle

SearchContentManagement

SearchWindowsServer

Close