SQL 培训 PPT

SQLDancyLeeSummaryTableIndexSARGStoreProcedureExecutionPlanSQL2000ArchitectureTablePrimaryKey.Alwayscreateaprimarykey.AvoidcreateprimarykeyonAttribute.NULLIndexIndexBasicClusteredIndexNon-ClusteredIndexIndexBasicAllSQLServerIndexesAreOrganizedAsB-Trees.MultipleIndexesCanBeUsedOnASingleTable.WithNoIndexes,ATableMustBeScanned.ClusteredIndexClusteredIndexDataorderbyClusteredIndexOnlyoneClusteredIndexinonetableTheleaflevelcontainsthedatapages.theclusteredkeyisduplicatedineverynon-clusteredindexrow.ClusteredIndexUniqueIfNon-Unique,RIDwillautoaddedNarrowStaticEver-increasingTheImpactofClusteredIndexChangeRecordRelocationEveryNon-ClusteredIndextobemodifiedCausepagesplitandfragmentationWasteTimeGoodClusteredIndexEver-IncreasingIdentitycolumn,IntorBigIntEver-IncreasingDatecolumnfollowedbyIdentitycolumnEver-IncreasingGUIDNon-ClusteredIndexNon-ClusteredIndexTheLeafLevelContainsaBookmark:ClusteredIndex.RID.EfficientWhenSearchingForASingleRow.95%performanceissuecanbefixedwithcorrectindex.Indexlikesalt.StoreProcedureASeriesofSQLstatements.Pre-compiled.Lownetworktraffic.SP_versusUSP_.SARG(SearchArgument)columnoperatorvalue.valueoperatorcolumn.Operatormustbeoneof=,,=,=.Somenon-SARGcanbeconverted.QueryOptimizationSELECT*versusSELECTcolumn1,column2,column3,….NeverreturnmoredatathatyoureallyneedUNIONversusUNIONALLLIKE‘%A’versusLIKE‘A%’DATEDIFF…ExecutionPlanTableScanIndexSeekClusteredIndexSeekClusteredIndexScan,aslikeTableScanPhysicaloperation:Thisistheoperatorusedbythequeryoptimizerforthisstep.Oneexamplewouldbeanestedloop.Logicaloperation:Isusuallythesameasthephysicaloperator.Whendifferent,thisrepresentsakeywordinthequery.OneexamplewouldbeINNERJOIN.Rowcount:Thisisthenumberofrowsfetchedbythisstepandpassedtothenextstep.Estimatedrowsize:Thisistheestimatedrowsizepassedontothenextstep.I/Ocost:ThisistheestimatedcostforallI/Oactivityforastep.Thelowerthisnumberisthebetter.CPUcost:ThisistheestimatedcostforallCPUactivity.Numberofexecutes:Isthenumberoftimesthestepwasexecuted.Cost:Isthecostforthestepbeingviewed.Thelowerthisnumberisthebetter.Subtreecost:Isthecostofthestepbeingviewedandallprevioussteps.SQLProfilerBooksOnline