Arquitectura del procesador

Writers: Richard Tkachuk and Thomas Kejser

Contributors and Technical Reviewers:

T.K. Anand

Marius Dumitru

Greg Galloway

Siva Harinath

Denny Lee

Edward Melomed

Akshai Mirchandani

Mosha Pasumansky

Carl Rabeler

Elizabeth Vitt

Sedat Yogurtcuoglu

Anne Zorner

Published: October 2008

Applies to: SQL Server 2008

Summary: This white paper describes how application developers can apply query and processing performance-tuning techniques to their SQL Server 2008 Analysis Services OLAP solutions.

Copyright

The information contained in this document represents the current view of Microsoft Corporation on the issues discussed as of the date of publication. Because Microsoft must respond to changing market conditions, it should not be interpreted to be a commitment on the part of Microsoft, and Microsoft cannot guarantee the accuracy of any information presented after the date of publication.

This white paper is for informational purposes only. MICROSOFT MAKES NO WARRANTIES, EXPRESS, IMPLIED, OR STATUTORY, AS TO THE INFORMATION IN THIS DOCUMENT.

Complying with all applicable copyright laws is the responsibility of the user. Without limiting the rights under copyright, no part of this document may be reproduced, stored in, or introduced into a retrieval system, or transmitted in any form or by any means (electronic, mechanical, photocopying, recording, or otherwise), or for any purpose, without the express written permission of Microsoft Corporation.

Microsoft may have patents, patent applications, trademarks, copyrights, or other intellectual property rights covering subject matter in this document. Except as expressly provided in any written license agreement from Microsoft, the furnishing of this document does not give you any license to these patents, trademarks, copyrights, or other intellectual property.

© 2008 Microsoft Corporation. All rights reserved.

Microsoft, Excel, SQL Server, Visual Basic, Windows, and Windows Server are trademarks of the Microsoft group of companies.

All other trademarks are property of their respective owners.

Contents

1 Introduction 6

2 Understanding the Query Processor Architecture 6

2.1 Session Management 7

2.2 Job Architecture 8

2.3 Query Processor 9

2.3.1 Query Processor Cache 9

2.3.2 Query Processor Internals 12

2.4 Data Retrieval 16

3 Enhancing Query Performance 17

3.1 Baselining Query Speeds 17

3.2 Diagnosing Query Performance Issues 19

3.3 Optimizing Dimensions 21

3.3.1 Identifying Attribute Relationships 21

3.3.2 Using Hierarchies Effectively 23

3.4 Maximizing the Value of Aggregations 24

3.4.1 Detecting Aggregation Hits 24

3.4.2 How to Interpret Aggregations 25

3.4.3 Building Aggregations 26

3.5 Using Partitions to Enhance Query Performance 29

3.5.1 Introduction 29

3.5.2 Partition Slicing 30

3.5.3 Aggregation Considerations for Multiple Partitions 31

3.5.4 Distinct Count Partition Design 31

3.5.5 Partition Sizing 31

3.6 Optimizing MDX 32

3.6.1 Diagnosing the Problem 32

3.6.2 Calculation Best Practices 32

3.7 Cache Warming 45

3.8 Improving Multiple-User Performance 47

3.8.1 Increasing Query Parallelism 47

3.8.2 Memory Heap Type 48

3.8.3 Blocking Long-Running Queries 49

3.8.4 Network Load Balancing and Read-Only Databases 49

4 Understanding and Measuring Processing 50

4.1 Processing Job Overview 50

4.2 Baselining Processing 51

4.2.1 Performance Monitor Trace 51

4.2.2 Profiler Trace 52

4.3 Determining Where You Spend Processing Time 52

5 Enhancing Dimension Processing Performance 53

5.1 Understanding Dimension Processing Architecture 53

5.2 Dimension-Processing Commands 55

5.3 Dimension Processing Tuning Flow Chart 57

5.4 Dimension Processing Performance Best Practices 57

5.4.1 Use SQL Views to Implement Query Binding for Dimensions 58

5.4.2 Optimize Attribute Processing Across Multiple Data Sources 58

5.4.3 Reduce Attribute Overhead 58

5.4.4 Use the KeyColumns, ValueColumn, and NameColumn Properties Effectively 58

5.4.5 Remove Bitmap Indexes 59

5.4.6 Turn Off the Attribute Hierarchy and Use Member Properties 59

5.5 Tuning the Relational Dimension Processing Query 60

6 Enhancing Partition Processing Performance 60

6.1 Understanding the Partition Processing Architecture 60

6.2 Partition-Processing Commands 61

6.3 Partition Processing Tuning Flow Chart 62

6.4 Partition Processing Performance Best Practices 64

6.4.1 Optimizing Data Inserts, Updates, and Deletes 64

6.4.2 Picking Efficient Data Types in Fact Tables 66

6.5 Tuning the Relational Partition Processing Query 66

6.5.1 Getting Rid of Joins 67

6.5.2 Getting Relational Partitioning Right 67

6.5.3 Getting Relational Indexing Right 68

6.5.4 Using Index FILLFACTOR = 100 and Data Compression 69

6.6 Eliminating Database Locking Overhead 70

6.7 Optimizing Network Throughput 71

6.8 Improving the I/O Subsystem 72

...