Building a high-performance Data Warehouse with SQL Server 2005

A Introduced

Some "super users" with access to data have learned professional Transact-SQL. The convenience of the Report Builder in SQL Server 2005 Report Services (SSRS) extends to the creation of powerful Transact-SQL queries, making it easier for more users to use it. Their ability to consume system resources is beyond their capabilities, posing a challenge to the database administrator (DBA) in maintaining consistent performance. However, when SQL Server Analysis Services (SSAS) are mentioned, users who need to access the data in unpredictable ways may feel that their query efficiency is hampered. So how you make your business users query what they need is as important as giving them a good performance query and always meeting service-level agreements (SLAs).

Over the past few years, a lot of information has been done around data warehousing and business intelligence (BI). A large part of the information focuses on building a data warehouse or data market to meet or exceed the business objectives associated with making strategic decisions. With the release of SQL Server 2005, the implementation of the relevant data warehouse in the 1000GB scope provides support in its additional functionality. This white paper describes the best practices that are included when architecting an associated data warehouse or data market with a performance perspective on SQL Server 2005.

The white paper also discusses some of the more common issues surrounding hardware selection, SQL Server instance and database configuration, and the design of Table/index, which also describes the overall strategy for balancing query performance in order to deliver a data market or data warehouse.

The performance of related data warehouses is often felt when updating databases and queries. The update operation requires more extraction, conversion, and loading of the new data. DBAs are responsible for designing and maintaining databases as efficiently as possible. Query operations can also occur in a number of ways, such as end-user queries, report services, and so on. Since Analysis Services are the most predictable in a variety of queries, the white paper focuses on direct queries to the relevant database warehouses.

Before we go on, let's make some assumptions about what we've already mentioned. Some of the relevant data warehouses used to store information are usually very large. data warehouses and data markets are often used in replaceable use. However, the data warehouse is more related to information storage in all enterprises. The data is emptied and is in a normal way and very few direct queries. Instead, it often uses a consistent data source to service users directly to one or more subject-oriented data warehouses. Data in the data market can be designed in a variety of ways, but often in a spatial way. In industry, the "data market" is combined into a "Data Warehouse".

However, in terms of consistency, in this white paper, we use the concept of "data warehousing" as a common concept that encompasses the data market.

This white paper assumes that readers are already familiar with the concepts of data warehousing, spatial modeling, separation of SQL Server 2005 tables, and indexed views. Many of these query results are compared to the test in the Project REAL database. Project REAL is a reference implementation of a true data warehouse on SQL Server 2005.