Server数据仓库的优化

12SQLServerMicrosofSQLServer7.0OLAP(ROLAP)OLAP(HOLAP)OLAP(MOLAP)()SQLServer7.0SQLServer7.012.1SQLServerQueryAnalyzerProfilerIndexTuningWizardMicrosoftSQLServer7.0OLAPServicesROLAPHOLAPMOLAPQueryAnalyzer12.1.1(QueryAnalyzer)QueryAnalyzerflyoverFoodMartSQLServerNorthwindSQLSQLServerEnterpriseManagerNorthwindNorthwindTools|SQLServerQueryAnalyzerNorthwindSelect*FromOrdersjoin[OrderDetails]ON[OrderDetails].OrderID=Orders.OrderIDORDERBYOrders.OrderDate12-1Query|DisplayEstimatedExecutionPlanSQLServer00:00:01.1msOrderDetailsflyover(12-2)12-1QueryAnalyzer12-2OrderDetailsflyoverCPUI/OCPUI/O29%29%238MicrosoftSQLServer7.0OrderIDOrderDateOrderID00:00:0012-312-311%Query|PerformIndexAnalysis12-412-412SQLServer239OrderIDSQLServerEnterpriseManagerNorthwindPropertiesOptions(12-5)12-5NorthwindAutoCreateStatisticsAutoUpdateStatisticsSQLServerQueryAnalyzerCancelPropertiesQueryAnalyzerProfiler12.1.2ProfilerProfilerSQLTraceSQLServerSQLServerSQLServer(SQLServerProfiler)OLEDBOpenDataServices(ODS)ODSSQLAPIProfilerProfiler68240MicrosoftSQLServer7.0ProfilerProfilerQueryAnalyzerProfilerProfilerIndexTuningWizardSQLServerBookSQLServer1.ProfileSQLServer7.0EnterpriseManagerTools|SQL7.0ProfilerProfilerToolsCreateTraceWizardWelcomeNextIdentifyTheProblemFindTheWorstPerformingQueries(12-6)12-6Next0msNorthwind(12-7)SpecifyTraceFiltersNextFinishQueryAnalyzerProfiler(12-8)12SQLServer24112-7012-8ProfilerTools|PerformanceMonitorMicrosoftWindowsNTPerformanceMonitorPerformanceMonitorProfilerFile|SaveNorthwindProfilerTools|IndexTunningWizardIntroductionNextKeepExistingIndexesNorthwind(12-9)242MicrosoftSQLServer7.012-9NorthwindNextIdentifyAWorkloadIHaveASavedWorkloadFileSpecifyWorkloadProfilerNorthwind.trc(12-10)12-10trace12SQLServer243(AdvancedOptions)NextSelectQueriesToTuneNextFinalAnalysisAnalysisNextScheduleIndexJobCancel2.ProfileProfilerEdit|ClearTraceWindowTools|OptionsGeneralAllEventClassesAllDataColumns(12-11)6812-11OKFile|New|TraceTraceProperties4GeneralErrorProfileCapturetoFile(12-12)EventsRemoveSelectedErrorandWarningsAddEvents12-13244MicrosoftSQLServer7.012-12profile12-13ErrorandWarningsEvents12SQLServer245DataColumns12-112-1ApplicationNameSQLServerBinaryDataConnectionIDSQLServerCPUCPU(ms)DatabaseIDUSEUSEServerNameSQLServerProfilerDuration(ms)EndTimeSQLBatchStartingSP:StartingEventClassEventSubClassHostNameHOST_NAMEHostProcessIDHOST_IDIndexIDsysindexesindIDIntegerDataNTDomainNameWindowsNTNTUserNameWindowsNTObjectIDReadsServerNameSQLServerSeveritySPIDSQLServerSQLUserNameSQLServerStartTimeTextTransactionIDWritesSQLServer7.0SQLServerBooksSQLServer7.0Start|Programs|MicrosoftServerSQL7|BooksOnLineEventsMonitoredbySQLServerProfileEventCategoryUnselectedDataSelectedData:listboxFiltersOKQueryAnalyzerSelect*FromOrdersjoin[OrderDetails]ON[OrderDetails].OrderID=Orders.OrderIDORDERBYOrder.OrderDate246MicrosoftSQLServer7.012-1412-14SchemaOptimizerSQLServerOLAPManager12.1.3•()•••OLAPServicesOptimizeSchemaWizardOLAPManagerFoodMartSales1998EditCubeEditorTools|OptimizeSchemaOK(12-15)MemberKeyColumnProductProductIDsales_fact_1998ProductIDProductProductNameProperties(12-16)12SQLServer24712-15199812-16ProductMemberKeyColumn248MicrosoftSQLServer7.0OptimizerROLAPHOLAPMOLAPOLAP12.2OLAPOLAPServicesUsage-BasedOptimizationWizardUsageAnalysisToolOLAPServicesUsage-BasedOptimizationWizardUsageAnalysisToolOLAPServicesOLAPManagerProperties(12-17)12-17PropertiesQueryLogSampleFrequency(OncePer)10(12-18)UsageAnalysisWizard12SQLServer24912-18SampleFrequency12.2.1UsageAnalysisWizardFoodMartSalesUsageAnalysisWizardWelcomeSalesUsage-BasedOptimizationWizardQueryRun-TimeTableReportType(12-19)12-19250MicrosoftSQLServer7.0NextSelectCriteriaQueriesBefore(12-20)Sales12-20Next12-211s12-21FinishUsage-BasedOptimizationWizard12SQLServer25112.2.2(Usage-BasedOptimizationWizard)AggregationWizardSalesUsage-BasedOptimizationWizardWelcomeSkipThisScreenInTheFutureNextSelectPartition(Enterprise)SalesNextSelectQueriesUsageAnalysisWizardBeforeNext(12-22)12-22NextAggregationsAlreadyExist(12-23)12-23252MicrosoftSQLServer7.0ReplaceMyExistingAggregationsNextMOLAPNextPerformancegainreaches30(12-24)12-2430%NextFinishProcessFinish12.3()12.3.131.•()••/yearsCountryRegionStateProvinceCityRegion12SQLServer2532.OLAPServer(32)255SQLServer3.CountryStateCityNESENWSWRegionCountryStateCity4.UniqueMembersUniqueMembersHOLAPROLAPUniqueMembersFlatYesMOLAPROLAP5.12.3.2484OLAPServer254MicrosoftSQLServer7.012.3.31.FoodMart1997199819972.30%5%10%Usage-BasedOptimizationWizardUsage-BasedOptimizationWizard15000000200000003.ROLAPHOLAPMOLAPMOLAPROLAPHOLAP12.3.4ProperiesProcessing12-2512-25Processing12SQLServer255ProcessBufferSizeProcessBufferSize5MB5MBProcessBufferSizeProcessBufferSizeProcessBufferSizeProfilerRead-aheadBufferRead-aheadBufferI/O12.3.5I/OPerformanceMonitorI/O(RAID)5RAID1&0RAID012.425204542(12-26)12-262012-26x()2052514375%222100%sparedatasparedata100%55100%100%2312-262100%256MicrosoftSQLServer7.021100%34100%100%52100%100MB2100MB200MB2200MB2400MB34002800MB53200MB3.2GB53292500100%UsageOptimizationWizardnuMBer12.5SQL