Tips for Optimizing SQL Server OLAP/Analysis Services: Data Warehouse & Datamart Performance

If you know data will be numeric, store it using a numeric data type, and not in a character data type. Numeric data types are more efficient when using space and when being processed by SQL Server. [6.5, 7.0, 2000, 2005] Updated 1-28-2005

*****

If you monitor the SQL Server Buffer Cache Hit Ratio of a SQL Server dedicated as an OLAP or Analysis Services server, you may see that this ratio is much less than what you would expect to find on a SQL Server dedicated to OLTP (90% or higher). Don't let this alarm you, as this is to be expected.

The nature of the queries than run on an OLAP or Analysis Services SQL Server are generally different that the queries that run on an OLTP server. For example, you may run a monthly OLAP-style query that requires that 50GB of data be examined. Because of this, it is difficult for SQL Server to make good reuse of the data cache, and the Buffer Cache Hit Ratio may run much lower than 90%.

Now, this is not to say that a Buffer Cache Hit Ratio below 90% is adequate for your particular situation. An OLAP or Analysis Services server, just like an OLTP server, will perform faster it is has more RAM and a bigger data cache. If you find that your OLAP-style queries are not performing as fast as you like, and you notice that the SQL Server Buffer Cache Hit Ratio is running low, and if you have room to expand the amount of RAM in your server, then you should seriously consider adding extra RAM to boost performance. Just keep in mind that you will probably never be able to get close to a 100% Buffer Cache Hit Ratio on a dedicated OLAP or Analysis Services server. [7.0, 2000, 2005]  Updated 1-28-2005