Whitepaper : Predictable Business Intelligence Performance with Unpredictable Queries

SQL Server database administration is difficult; queries and indexes require tuning in response to application changes. For many database administrators, change is regular; change happens after a development cycle with testing phases and a careful roll out process. Business intelligence (BI) applications are different; they can be unpredictable. Schemas are designed to respond to changing user demands. Users may change query habits in response to changing market forces, and complex relationships may be redefined at the drop of a hat. Our best online transaction processing tuning efforts cannot keep up with the changing demands of business intelligence (BI) power users.

The purpose of this white paper is to teach database administrators on how to determine if SQL Server is configured correctly for BI workloads. This whitepaper introduces foundational ideas for sizing SQL Server storage and hardware. It then addresses more complex SQL Server features like SQL Server Resource Governor and Buffer Pool Extensions. Besides, we will examine tempdb configuration, parallelism, and licensing. That should provide a database administrator with the core knowledge needed to design a powerful business intelligence SQL Server.