sql server 2000 research series - performance tuning
TRANSCRIPT
Technical Integration DepartmentTechnical Integration DepartmentSystem Analysis & Training GroupSystem Analysis & Training Group
Jerry YangJerry YangNovember 11, 2005November 11, 2005
SQL Server 2000 Research Series SQL Server 2000 Research Series
Performance TuningPerformance Tuning
IntroductionIntroduction SQL Server 2000 Index AnalyzingSQL Server 2000 Index Analyzing The Way to Achieve GoalThe Way to Achieve Goal SummarySummary
AgendaAgenda
IntroductionIntroduction SQL Server 2000 Index AnalyzingSQL Server 2000 Index Analyzing The Way to Achieve GoalThe Way to Achieve Goal SummarySummary
Performance TuningPerformance Tuning
What Is DB Tuning ?What Is DB Tuning ? Hardware PartHardware Part Software PartSoftware Part
Why It Is So Important ?Why It Is So Important ? Case Study 1. General Nutrition CompaniesCase Study 1. General Nutrition Companies Case Study 2. SYSCOMCase Study 2. SYSCOM Case Study 3. GSSCase Study 3. GSS
IntroductionIntroduction
IntroductionIntroduction SQL Server 2000 Index AnalyzingSQL Server 2000 Index Analyzing The Way to Achieve GoalThe Way to Achieve Goal SummarySummary
Performance TuningPerformance Tuning
SQL Server ManagersSQL Server Managers
SQL Server 2000 Index AnalyzingSQL Server 2000 Index Analyzing
Query Executer
Eight Types of Disk PagesEight Types of Disk Pages Bulk Changed Map PagesBulk Changed Map Pages Data PagesData Pages Differential Changed Map PagesDifferential Changed Map Pages Global Allocation Map (GAM & SGAM) PagesGlobal Allocation Map (GAM & SGAM) Pages Index Allocation Map (IAM) PagesIndex Allocation Map (IAM) Pages Index PagesIndex Pages Page Free Space (PFS) PagesPage Free Space (PFS) Pages Text/Image PagesText/Image Pages
SQL Server 2000 Index AnalyzingSQL Server 2000 Index Analyzing
Eight Types of Disk PagesEight Types of Disk Pages Bulk Changed Map PagesBulk Changed Map Pages Data PagesData Pages Differential Changed Map PagesDifferential Changed Map Pages Global Allocation Map (GAM & SGAM) PagesGlobal Allocation Map (GAM & SGAM) Pages Index Allocation Map (IAM) PagesIndex Allocation Map (IAM) Pages Index PagesIndex Pages Page Free Space (PFS) PagesPage Free Space (PFS) Pages Text/Image PagesText/Image Pages
SQL Server 2000 Index AnalyzingSQL Server 2000 Index Analyzing
Concept of B-TreeConcept of B-Tree Three Kinds of NodesThree Kinds of Nodes
Root NodeRoot Node Intermediate NodesIntermediate Nodes Leaf NodesLeaf Nodes
PointerPointer DataData
BookmarkBookmark Real DataReal Data
SQL Server 2000 Index AnalyzingSQL Server 2000 Index Analyzing
SQL Server Index TypesSQL Server Index Types Clustered IndexClustered Index
── Please See Example Please See Example ──
SQL Server 2000 Index AnalyzingSQL Server 2000 Index Analyzing
ID Name Sex City1234
AlexJohnBillMary
MMMF
TaipeiTainanKeelungTaipei
Table Name: PeopleClustered Index: IDClustered Index: IDNon-Clustered Index: NameNon-Clustered Index: Name
3
4
1
2
1
2
3
4
3 Bill M Keelung1 Alex M Taipei 2 J ohn M Tainan 4 Mary F Taipei
SELECT * FROM People WHERE ID = 3
SQL Server Index TypesSQL Server Index Types Non-Clustered IndexNon-Clustered Index
── Please See ExamplePlease See Example ──
SQL Server 2000 Index AnalyzingSQL Server 2000 Index Analyzing
ALEX
BILL
J OHN
MARY
J OHN
MARY
ALEX
BILL
1ALEX 3BILL 2J OHN 4MARY
ID Name Sex City1234
AlexJohnBillMary
MMMF
TaipeiTainanKeelungTaipei
Table Name: PeopleClustered Index: IDClustered Index: IDNon-Clustered Index: NameNon-Clustered Index: Name
Bookmarks
SELECT * FROM People WHERE Name = ‘Bill’
ALEX
BILL
J OHN
MARY
J OHN
MARY
ALEX
BILL
1ALEX 3BILL 2J OHN 4MARY
ID Name Sex City1234
AlexJohnBillMary
MMMF
TaipeiTainanKeelungTaipei
Table Name: PeopleClustered Index: IDClustered Index: IDNon-Clustered Index: NameNon-Clustered Index: Name
3
4
1
2
1
2
3
4
3 Bill M Keelung1 Alex M Taipei 2 J ohn M Tainan 4 Mary F Taipei
IntroductionIntroduction SQL Server 2000 Index AnalyzingSQL Server 2000 Index Analyzing The Way to Achieve GoalThe Way to Achieve Goal SummarySummary
Performance TuningPerformance Tuning
Index Creation TipsIndex Creation Tips Frequency (Where, Order By, Group By)Frequency (Where, Order By, Group By) SelectivitySelectivity Indexed Column Size (Integer, Character)Indexed Column Size (Integer, Character) OLTP (Online Transaction Processing) &OLTP (Online Transaction Processing) &
OLAP (Online Analytical Processing)OLAP (Online Analytical Processing)
The Way to Achieve GoalThe Way to Achieve Goal
Query Design TipsQuery Design Tips Use IndexUse Index
Ex: Column Employee_ID is used to define an index.
The Statement “… WHERE Employee_ID >= 50” will use this index.
Use “>=” And “<=” To Replace…Use “>=” And “<=” To Replace… BETWEEN … AND … <> , !=
Avoid To Use Calculation In WHERE ClauseAvoid To Use Calculation In WHERE Clause Ex: WHERE Last_Name + First_Name = ‘George Bush’
The Way to Achieve GoalThe Way to Achieve Goal
Query Design Tips (Cont.)Query Design Tips (Cont.) Avoid To Use Function In WHERE ClauseAvoid To Use Function In WHERE Clause
Ex: WHERE ABS(Account_Money) >= 50000
Follow SQL Server’s DecisionFollow SQL Server’s Decision
The Way to Achieve GoalThe Way to Achieve Goal
IntroductionIntroduction SQL Server 2000 Index AnalyzingSQL Server 2000 Index Analyzing The Way to Achieve GoalThe Way to Achieve Goal SummarySummary
Performance TuningPerformance Tuning
What Do You Need To Know Today…What Do You Need To Know Today… Something About DB TuningSomething About DB Tuning Analysis of IndexAnalysis of Index
B-TreeB-Tree PagesPages Clustered IndexClustered Index Non-Clustered IndexNon-Clustered Index
Index & Query Design TipsIndex & Query Design Tips
SummarySummary
Fundamentals of Database SystemsFundamentals of Database Systems Author:Author: Elmasri / Navathe Elmasri / Navathe Publisher: Addison-Wesley Publishing CompanyPublisher: Addison-Wesley Publishing Company
Inside of Microsoft SQL Server 2000Inside of Microsoft SQL Server 2000 Author:Author: Kalen Delaney Kalen Delaney Publisher: Microsoft PressPublisher: Microsoft Press
ReferenceReference
Any Question?Any Question?