🚀 go-pugleaf

Best Practice for Building SQL Server 2008 BI System(1)
--Hardware/Software Selection/Configuration and Database Engine Related

SQL Server FAQ

Published: August 2008

Applies to: SQL Server 2008 

1. Introduction

SQL Server 2008 introduces many new functional and performance improvements 
for building a BI System. The application of best practices to a data 
warehouse project is one of the best investments you can make toward the 
establishment of a successful BI infrasturcture. 

The goals for a data warehouse solution are typically the following, 
although they may vary somewhat in different environments:
�� Support rapid response time for queries that summarize large volumes of 
data.
�� Support efficient drill-through queries that extract moderate volumes of 
detail data for further analysis.
�� Support rapid initial data loading.
�� Support efficient incremental loading (for example, load 24 hours of 
data in a one-hour window).
�� Support fast maintenance operations (backup, restore, loading, indexing, 
compression, bulk removal of old data).
�� Given that the above goals are met, limit total cost of ownership (TCO):
�� Minimize the database administrator (DBA) time required during setup and 
for normal operations.
�� Keep hardware costs reasonable.

This article includes best practice for logical database design and 
database engine related only. The following articles will complete solution 
to build SQL Server 2008 BI System.

2. Section of Best Practice
Best Practices cover the following:
a. Hardware/Software Selection/Configuration
b. Database Engine Related
c. Database Design
d. Query Design
e. SSAS Related 
f. SSIS Related
g. SSRS Related

3. Hardware/Software Selection/Configuration
3.1 Hardware Selection/Configuration
Some of these best practices are independent of the type of hardware being 
used. Others are more closely related to the use of a scale-up 
configuration. Best practices for hardware selection include considerations 
for assessing processor and memory resources.

3.1.1 Processors
With the multi-core processors today, the estimation gets slightly more 
complicated. Taking your workload to a lab for testing may be the best way 
to determine the number of processors needed. 

3.1.2 Memory
Empirical evidence has shown that 4 GB per core is sufficient for most data 
warehouse applications. This estimate can be influenced by the number of 
simultaneous queries or transactions as well as the number of large 
table/index scans needed.

3.2 Software Selection/Configuration
3.2.1 Software Selection
For the best results in scaling up your computer to its largest 
configuration it is best to use the following:
�� Windows Server? 2008 Enterprise, Datacenter, or Itanium editions
�� SQL Server 2008 Enterprise Edition (SQL Server 2005 and 2008 Enterprise 
editions are both available in versions for the Intel Itanium processor)
This will give you the following maximum limitations:
�� 64 cores
�� 2 terabytes RAM
SQL Server 2008 does not have a Datacenter edition. The highest edition is 
Enterprise. Many of the data warehouse scale enhancements in SQL Server 
2008 are only available in the Enterprise edition.

You can refer to "How to configure SQL Server to use more than 2 GB of 
physical memory"(http://support.microsoft.com/kb/274750/en-us) for detailed 
configuration.

3.2.2 SQL Server Settings
There are several issues to consider when configuring the SQL Server 2008 
Database Engine for data warehouse workloads.
�� Configuring ��max server memory��
�� Configuring ��max degree of parallelism��
�� Configuring affinity masks 
�� Using Resource Governor 
�� Using Windows System Resource Manager (WSRM) 
You can refer to SQL Server 2008 Book Online for more detailed settings.

4. Database Engine Related

4.1 Policy Management
Best Practice Analyzer is not going to exist as such in SQL Server 2008. 
Instead there will be a set of policies that you can import and use in 
Policy Management.
 
Policy-Based Management lets you monitor best practices for the SQL Server 
Database Engine. SQL Server 2008 provides a set of policy files that you 
can import as best practice policies, and then evaluate the policies 
against a target set that includes instances, instance objects, databases, 
or database objects. 
 
Policy-Based Management makes it possible to declare policies (such as "all 
log files must be stored on a disk other than the data disk") in one 
location and then apply them to multiple servers. So a (somewhat recursive) 
best practice is to set up best practices on one server and apply them to 
all servers. For example, you might build three data marts that draw data 
from a main data warehouse and use Policy-Based Management to apply the 
same rules to all the data marts.
 
These policies are now installed to the file system rather than in msdb. You
��ll find these policies under C:\Program Files\Microsoft SQL 
Server\100\Tools\Policies\ (this location may be different if you install 
the tools to a custom location).
 
You can refer to the table in "Monitoring and Enforcing Best Practices by 
Using Policy-Based 
Management"(http://technet.microsoft.com/en-us/library/cc645723(SQL.100).aspx
) to customize best practices rules according to your needs.
 
Additional Resources:
You can find additional tips mostly related to getting the best scalability 
and performance from SQL Server 2008 in the companion white paper. These 
tips cover a range of topics including storage configuration, query 
formulation, indexing, aggregates, and more.
 
Analysis:
There are several excellent white papers on Best Practices for analysis 
such as OLAP Design Best Practices for Analysis Services 
2005(http://www.microsoft.com/technet/prodtechnol/sql/bestpractice/olapdbpssa
s2005.mspx), Analysis Services Processing Best Practices 
(http://www.microsoft.com/technet/prodtechnol/sql/bestpractice/ssaspbpr.mspx)
, and Analysis Services Query Performance Top 10 Best 
Practices(http://www.microsoft.com/technet/prodtechnol/sql/bestpractice/ssasq
ptb.mspx). Rather than repeat their content, in this paper we focus more 
specifically on best practices for analysis in SQL Server 2008.
 
4.2 Full-Text Search
Full-text search allows fast querying over textual data. 
 
Full-text search allows fast and flexible indexing for keyword-based 
querying of text data stored in a SQL Server database. Unlike the LIKE 
predicate, which only works on character patterns, full-text queries 
perform a linguistic search against the data, operating on words and 
phrases based on rules of a particular language.
 
The performance benefit of using full-text search is best realized when 
querying against a large amount of unstructured text data. A LIKE query 
(such as '%fernando%') against millions of rows of text data can take 
minutes to return, whereas a full-text query (for 'fernando') can take only 
seconds or less against the same data, depending on the number of rows that 
are returned. 
 
Full-text indexes can be built not just on columns that contain text data, 
but also against formatted binary data, such as Microsoft Word documents 
stored in a BLOB-type column; in these cases, it is not possible to use the 
LIKE predicate for keyword queries.
 
You can refer to 
http://msdn.microsoft.com/en-us/library/cc721269%28SQL.100%29.aspx#_Toc202506
236 to get more information on full-text search.
 
4.3 Physical Database Design
The section covers best practices for designing schemas and for the 
physical design of a database such as disk layout, indexes, compression, 
and summary aggregates. 

4.3.1 Schema Design
Database schema design (logical design) can have a major impact on the 
performance of queries and updates. For best performance, and ease of 
understanding the data and writing queries, we recommend using a star 
schema if that is feasible in your data warehouse environment. If you 
prefer a normalized schema, you can still get excellent query performance, 
but certain types of star join optimizations may not be possible for your 
queries.
 
4.3.2 Partitioning
For a data warehouse fact table beyond 50 GB, it is recommended that you 
partition it by time, such as by week. This allows you to bulk purge a week 
of data at a time with a metadata-only operation. It also can improve 
performance of loading, and management of summary aggregates maintained 
using indexed views, as discussed later.

If the same table is partitioned by time, generally only the most recent 
partition must be touched, which means that the majority of the table (and 
indexes) remain untouched. You can drop all the indexes prior to the load 
and rebuild them afterwards to avoid index maintenance overhead. This can 
greatly improve your load time.

Partition large fact tables

Partitioning a table means splitting it horizontally into smaller 
components (called partitions). Partitioning brings several benefits. 
Essentially, partitioning enables the data to be segregated into sets. This 
alone has huge advantages in terms of manageability. Partitions can be 
placed in different filegroups so that they can be backed up independently. 
This means that we can position the data on different spindles for 
performance reasons. In data warehouses it also means that we can isolate 
the rows that are likely to change and perform updates, deletes, and 
inserts on  those rows alone. 

Query processing can be improved by partitioning because it sometimes 
enables query plans to eliminate entire partitions from consideration. For 
example, fact tables are frequently partitioned by date, such as by month. 
So when a report is run against the July figures, instead of accessing 1 
billion rows, it may only have to access 20 million. 

Partition-align your indexed views
Indexes as well as tables can be (and frequently are) partitioned, which 
increases the benefits.

SQL Server 2008 enables you to create indexed views that are aligned with 
your partitioned fact tables, and switch partitions of the fact tables in 
and out. This works if the indexed views and fact table are partitioned 
using the same partition function. Typically, both the fact tables and the 
indexed views are partitioned by the surrogate key referencing the Date 
dimension table. When you switch in a new partition, or switch out an old 
one, you do not have to drop the indexed views first and then re?create 
them afterwards. This can save a huge amount of time during your ETL 
process. In fact, it can make it feasible to use indexed views to 
accelerate your queries when it was not feasible before because of the 
impact on your daily load cycle. 

Design your partitioning scheme for ease of management first and foremost
In SQL Server 2008 it is not necessary to create partitions to get 
parallelism, as it is in some competing products. SQL Server 2008 supports 
multiple threads per partition for query processing, which significantly 
simplifies development and management. When you design your partitioning 
strategy, choose your partition width for the convenience of your ETL and 
data life cycle management. For example, it is not necessary to partition 
by day to get more partitions (which might be necessary in SQL Server 2005 
or other DBMS products) if partitioning by week is more convenient for 
system management.

For best parallel performance, include an explicit date range predicate on 
the fact table in queries, rather than a join with the Date dimension
 
4.3.3 Index Design
If you use partitioning to support a sliding-window scenario, partition the 
fact table and all its indexes on the date key. If your table partition 
width is one day, there is no need for the clustered index on the date key 
of the fact table in this situation. Instead, create a clustered index on 
the most frequently used dimension key column of the fact table besides the 
date key.

4.3.4 Design of Summary Aggregates
using summary aggregates in the database into sets of rows that are 
typically much smaller than the fact table. If most of your queries can be 
answered from these aggregates, which are much smaller than the raw event 
data, query performance can be improved by an order of magnitude. This in 
turn can make it possible to meet your users�� performance requirements on 
a smaller hardware platform, or provide better quality service to your 
users, or both.

If you wish to keep a relational-database-only configuration with SQL 
Server without using SSAS, or if you have a significant SQL 
decision-support query workload that could benefit from aggregates, 
consider using indexed views or user-defined summary tables to accelerate 
your common aggregate queries.

4.3.5 Compression
As businesses intelligence becomes increasingly common, businesses are 
pouring more and more data into their data warehouses for analysis. The 
result is an exponential growth in the size of the data being managed. 

Data compression, a new feature available in SQL Server 2008 Enterprise 
Edition, is extremely valuable in a scale-up environment. 

As a best practice, a rule of thumb is to: 
Compress your fact tables by using the PAGE compression feature.
Add enough memory to your system so that at least 10-20% of the compressed 
data will fit.

Compression is not free. Its main cost is the increased CPU time required 
to load data. Query processing time does not differ significantly between 
row and page compression. The actual benefits and costs of compression 
depend on your data, hardware, and workload. However, in large-scale 
internal tests on a 600?GB and 6?terabyte data warehouse and a workload of 
123 different queries, when using page compression, we observed a 30-40% 
improvement in query response time with a 10-15% CPU time penalty.

Compression can be applied to individual partitions. If you have a large 
table that is already partitioned, but the partitions are not compressed, 
and you do not have a large time window available in which you can compress 
the full table, consider compressing only the new data during ETL. Then 
over time, as you age out old partitions and add new ones, the entire data 
set will gradually become compressed, without the need to extensively 
re-organize the data all at once.

This description of the characteristics of compression should enable you to 
determine your optimal compression strategy for the tables and indexes in 
the warehouse. It may not be as simple as applying compression to every 
table and index. 

For example, suppose that you partition your fact table over time, such as 
by Quarter 1, Quarter 2, and so on. The current partition is Quarter 4. 
Quarter 4 is updated nightly, Quarter 3 far less frequently, and Quarters 1 
and 2 are never updated. However, all are queried extensively.

After testing you might find that the best practice is to apply both row 
and page compression to Quarters 1 and 2, row compression Quarter 3, and 
neither to Quarter 4. 

Your mileage will, of course, vary and testing is vital to establish best 
practices for your specific requirements. However, most data warehouses 
should gain significant benefit from implementing a compression strategy. 
Start by using page compression on all fact tables and fact table indexes. 
If this causes performance problems for loading or querying, consider 
falling back to row compression or no compression on some or all partitions.

4.3.6 Disk Layout
Read-and-write caching in the disk devices or the SAN can be a big benefit 
up to a certain point. However, very large table scans can quickly fill 
this hardware-level cache and cause it to not give much benefit.

While a proper configuration of direct attached storage systems can equal 
or exceed the throughput ratings of the SANs, SAN systems have features 
that are highly beneficial for very large databases. The SAN snapshots, 
copy on write, and other similar features can make administrative tasks 
much easier.

To help you determine how good your disk layout is, download SQLIO.exe or 
IOMETER.exe. Run one of these tools to get a rating for both the number of 
IO/sec and throughput in bytes/sec. SQL Server uses a variety of block 
sizes for both reads and writes so it is important to run a variety of 
tests that will match your usage patterns.

4.3.7 tempdb Configuration
A common problem is having tempdb in a small file on the C: drive. You need 
to add tempdb files on a SAN or set of direct-attached disks with 
sufficient I/O throughput, and remove tempdb from the C: drive, because 
extent allocations are written on the first database file. These 
allocations may not be fast enough if you leave that file on a relatively 
slow C: drive. Similarly, log files should also not be on C:.

tempdb should be set to autogrow in case of emergencies, but should never 
be allowed to grow beyond its original size. It is considered a best 
practice to create tempdb at an appropriate size. 

tempdb should also contain one file per CPU core. For example, if you have 
four dual core CPUs, you should create eight files in tempdb. These files 
should be of equal size to achieve round-robin loading. This is considered 
a general best practice but may not fit all projects. And if you do not 
have heavy tempdb usage, it really doesn��t matter how many files you 
create.

References
Scaling Up Your Data Warehouse with SQL Server 2008
http://msdn.microsoft.com/en-us/library/cc719182(SQL.100).aspx

Best Practices for Data Warehousing with SQL Server 2008
http://msdn.microsoft.com/en-us/library/cc719165(SQL.100).aspx

SQL Server 2008 Data Warehouse Query Performance
http://technet.microsoft.com/en-us/magazine/cc434693.aspx 
--
I thought what I'd do was, I'd pretend I was one of those deaf-mutes
Or should I?

You know what I��d like to be? I mean if I had my goddam choice, I��d just be
the catcher in the rye and all.

�� ��Դ:����ˮ˼Դ bbs.sjtu.edu.cn��[FROM: 58.35.146.84]