注册 登录  
 加关注
   显示下一条  |  关闭
温馨提示!由于新浪微博认证机制调整,您的新浪微博帐号绑定已过期,请重新绑定!立即重新绑定新浪微博》  |  关闭

奕克

爱家人爱工作爱生活

 
 
 

日志

 
 

OLAP优化技巧  

2012-08-03 23:12:42|  分类: 数据挖掘 |  标签: |举报 |字号 订阅

  下载LOFTER 我的照片书  |
    看到一老外的技巧总结,不错,转之。。。

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


BY BRAD MCGEHEE

While in production, set the “read only” database option to true for your data warehouses and datamarts (not cubes). This will turn off locking and greatly speed queries against the data. If you do make your database “read-only, be sure to update the database’s statistics first. This is especially important for SQL 7.0 and 2000 as “Auto Update Statistics” is turned off when a database is set to “read only”. [6.5, 7.0, 2000] Updated 3-15-2005

*****

With your data warehouses and datamarts, which are essentially read-only, having as many indexes as necessary for covering virtually any query is not normally a problem. The only major disadvantage of having lots of indexes in your data warehouse or datamart is that it increases the load time of data into your databases. [6.5, 7.0, 2000, 2005] Updated 3-15-2005

*****

Consider adding a clustered index to any table in a data warehouse that needs to produce sorted results. This way, the data is already pre-sorted (by the clustered index key), saving a lot of time when the query is actually run. This becomes more important as huge numbers of rows are returned from your query. [7.0, 2000, 2005] Updated 3-15-2005

*****

When using the star schema design, at a minimum, you will create a non-clustered index on the primary key of each dimension table and a non-clustered index on each of the related foreign-keys. From there, you can create non-clustered indexes on additional columns that will be queried on frequently. You don’t need to create composite indexes to create covering indexes because SQL Server will use index intersection to do this for you automatically. [7.0, 2000, 2005] Updated 3-15-2005

*****

The SQL Server Profiler and the Index Tuning Wizard (or Database Engine Tuning Advisor for 2005) can be used for tuning indexes for data warehousing and datamarts as well as for OLTP databases. [7.0, 2000, 2005] Updated 3-15-2005

*****

When you create indexes on your data warehouses and datamarts, use a FILLFACTOR of 100 to ensure that the index pages are as full as possible. This reduces unnecessary I/O, speeding up performance.  [6.5, 7.0, 2000, 2005] Updated 3-15-2005

*****

Schedule large data imports or exports on your production servers during less busy periods of the day to reduce the impact on your users. [6.5, 7.0, 2000, 2005] Updated 3-15-2005

*****

If you need to periodically perform data loads into your datamart or data warehouse, consider dropping the indexes on the applicable tables before starting the data load. In most cases, it is faster to drop indexes, insert the data, and re-add the indexes, than it is to import bulk data into tables that have pre-existing indexes. You will want to test this suggestions before implementing it to see how much it can, or cannot help. [7.0, 2000, 2005] Updated 1-28-2005

*****

Always select the smallest data type you can when building data warehouses and OLAP cubes. This reduces the amount of storage required, reducing server I/O and boosting performance.

When it comes to choosing between CHAR and VARCHAR data types, use CHAR when the data length is predictable, and use VARCHAR when data length will have a standard deviation of over 2 characters. This is because VARCHAR data types require 2 bytes of overhead, and if the standard deviation of the average length of expected data is less than 2, then the CHAR datatype can be more efficient of storing data.

Also avoid the text and NCHAR and NVARCHAR datatypes. These generally waste valuable space. 

While it may seem that selecting the smallest possible datatype for storing data is no big deal, it can be in data warehouses where millions of records are stored, and having one unnecessary byte of data, a million times, can be a big deal. [6.5, 7.0, 2000, 2005] Updated 1-28-2005


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

  评论这张
 
阅读(168)| 评论(0)
推荐 转载

历史上的今天

在LOFTER的更多文章

评论

<#--最新日志,群博日志--> <#--推荐日志--> <#--引用记录--> <#--博主推荐--> <#--随机阅读--> <#--首页推荐--> <#--历史上的今天--> <#--被推荐日志--> <#--上一篇,下一篇--> <#-- 热度 --> <#-- 网易新闻广告 --> <#--右边模块结构--> <#--评论模块结构--> <#--引用模块结构--> <#--博主发起的投票-->
 
 
 
 
 
 
 
 
 
 
 
 
 
 

页脚

网易公司版权所有 ©1997-2017