• No results found

SQL Server 2012 Query. Performance Tuning. Grant Fritchey. Apress*

N/A
N/A
Protected

Academic year: 2021

Share "SQL Server 2012 Query. Performance Tuning. Grant Fritchey. Apress*"

Copied!
19
0
0

Loading.... (view fulltext now)

Full text

(1)

SQL Server

2012

Query

Performance

Tuning

Grant

Fritchey

(2)

Contents

J

About the Author

xxiii

About the Technical

Reviewer

xxv

Acknowledgments

xxvii

Introduction

xxix

Chapter

1:

SQL Query

Performance

Tuning

1

The Performance

Tuning

Process

2

The Core Process 2

Iterating

the Process 4

Performance

vs.

Price

5

Performance

Targets

5

"Good

Enough" Tuning

8

Performance

Baseline

8

Where

to Focus Efforts

9

SQL

Server Performance Killers

10

Poor

Indexing

11

Inaccurate Statistics 11

Poor

Query Design

11

Poor ExecutionPlans 12

Excessive

Blocking

and Deadlocks 12

Non-Set-Based

Operations

12

Poor Database

Design

12

(3)

CONTENTS

Nonreusable Execution Plans 13

Frequent Recompilation

of

Queries

13

Improper

UseofCursors 14

Improper Configuration

oftheDatabase

Log

14 Excessive Useor

Improper

Configuration

of

tempdb

14

Summary

14

Chapter

2:

System

Performance

Analysis

...15

Performance Monitor Tool

15

Dynamic Management

Objects

17

Hardware Resource Bottlenecks

18

Identifying

Bottlenecks 18

Bottleneck Resolution 19

Memory

Bottleneck

Analysis

19

SQL Server

Memory Management

19

Available

Bytes

23

Pages/sec

and

Page

Faults/sec 23

Paging

File

%Usage

and

Page

File

%Usage

23

Buffer Cache Hit Ratio 24

Page

Life

Expectancy

24

Checkpoint

Pages/sec

24

Lazy

writes/sec 24

Memory

Grants

Pending

25

Target

Server

Memory (KB)

and Total Server

Memory (KB)

25

Additional

Memory Monitoring

Tools

25

DBCC

Memorystatus

25

Dynamic

Management Objects

26

Memory

Bottleneck Resolutions

27

Optimizing Application

Workload 27

Allocating

More

Memory

to

SQL

Server 29

Increasing

System

Memory

29

(4)

Changing

froma32-bittoa64-bit Processor 29 Data

Compression

29

Enabling

3GB of Process Address

Space

29

Disk Bottleneck

Analysis

30

DiskCounters 30

% Disk Time 31

CurrentDisk

Queue

Length

31

Disk Transfers/sec 31

Disk

Bytes/sec

32

Avg.

Disk Sec/Read and

Avg.

Disk Sec/Write 32

Additional I/O

Monitoring

Tools

32

Sys.dm_io_virtuaLfile_stats

32

Sys.dm_os_wait_stats

33

Disk Bottleneck Resolutions

33

Optimizing

Application

Workload 33

Using

aFaster I/O Path 34

Using

aRAID

Array

34

RaidO 34

Raid 1 35

Raid5 35

Raid 6 35

RAID 1+0

(RAID 10)

36

Using

aSAN

System

36

Using

SSD Disks 36

Aligning

Disks

Properly

36

Adding System Memory

37

Creating Multiple

Files and

Filegroups

37

Placing

the Table and Index on

Separate

Disks 39

Moving

the

Log

Filesto a

Separate Physical

Disk 40

Partitioning

Tables 40

(5)

y.CONTENTS

Processor Bottleneck

Analysis

40

% Processor Time 41

%

Privileged

Time 41

Processor

Queue

Length

42

Context

Switches/sec 42

Batch

Requests/sec

42

SQL

Compilations/sec

42 SQL

Recompilations/sec

42

Other Tools

for

Measuring

CPU Performance

43

Sys.dm_os_wait_stats

43

Sys.dm_os_workers

and

Sys.dm_os_schedulers

43

Processor Bottleneck

Resolutions

43

Optimizing

Application

Workload 43

Eliminating

Excessive

Compiles/Recompiles

44

Using

MoreorFaster Processors 44

Using

a

Large

L2/L3 Cache 44

Running

More Efficient Controllers/Drivers 44 Not

Running

Unnecessary

Software 45

Network Bottleneck

Analysis

45

Bytes

Total/sec 45

% Net Utilization 45

Network

Bottleneck Resolutions

46

Optimizing Application

Workload 46

SQL Server Overall

Performance

46

Missing

Indexes

47

Database

Blocking

48

Nonreusable Execution Plans 49

General Behavior 49

User Connections 49

Batch

requests/sec

50

(6)

Considerations

for

Monitoring

Virtual

Machines

50

Creating

a

Baseline

50

Creating

aReusableList of Performance Counters 51

Creating

aCounter

Log

Using

the List of Performance Counters 52 Performance Monitor Considerations 54

System

Behavior

Analysis Against

Baseline 55

Summary

57

Chapter

3:

SQL

Query

Performance

Analysis

59

Extended Events

Wizard

59

Extended Events Sessions 60

Events 61

Global Fields 64

Event Filters 66

Datastorage

66

Finishing

the Wizard and

Starting

the Session 68

Extended

Events Automation

70

Creating

aSession

Script Using

the GUI 70

Defining

aSession

Using

Stored Procedures 71

Extended

Events Recommendations

72

Set Max File Size

Appropriately

72

Avoid

Debug

Events 72

Partition

Memory

in the Sessions 72

Avoid Use of

No_Event_Loss

73

Other Methods

for

Query

Performance Metrics

73

Costly

Queries

74

Identifying Costly

Queries 75

Costly

Querieswitha

Single

Execution 75

Costly

Queries with

Multiple

Executions 76

Identifying

Slow-Running Queries

78

(7)

CONTENTS

Execution Plans

79

Analyzing

a

Query

Execution Plan 81

Identifying

the

Costly

Steps

inan ExecutionPlan 83

Analyzing Index

Effectiveness 85

Analyzing

Join Effectiveness 86

Hash Join 87

Actualvs. Estimated Execution Plans 90

Plan Cache 92

Query

Cost 93 Client Statistics 93 Execution Time 94 Statistics lo 95

Summary

97

Chapter

4: Index

Analysis

99

What Is

an

Index?

99

The Benefit of Indexes 101

Index Overhead 103

Index

Design

Recommendations

105

Examinethe WHEREClauseandJOIN Criteria Columns 105

Use Narrow Indexes 107

Examine Column

Uniqueness

109

Examine the Column Data

Type

112

Consider Column Order 112

Consider the

Type

of Index 115

Clustered Indexes

115

Heap

Tables 115

Relationship

withNonclustered Indexes 116 Clustered Index Recommendations 118

When to Usea Clustered Index 120

Poor

Design

PracticesforaClustered Index 121

(8)

Nonclustered Indexes

122

Nonclustered

Index Maintenance 123

Defining

the Bookmark

Lookup

123

Nonclustered

Index Recommendations 123

Clustered

vs.

Nonclustered

Indexes

124

Benefits ofaClustered Indexover aNonclustered Index 124 Benefits ofa Nonclustered Indexover aClustered Index 126

Advanced

Indexing

Techniques

128

Covering

Indexes 128 APseudoclusteredIndex 130 Recommendations 130 Index Intersections 130 IndexJoins 132 Filtered Indexes 134 IndexedViews 136 Index

Compression

141 ColumnStore Indexes 142

Special

Index

Types

144

Full-Text 144

Spatial

145

XML 145

Additional

Characteristics of Indexes

145

Different Column Sort Order 145

Indexon

Computed

Columns 146

IndexonBIT Data

Type

Columns 146 CREATE INDEX StatementProcessedAsa

Query

146

Parallel IndexCreation 146

Online Index Creation 147

Considering

theDatabase

Engine Tuning

Advisor 147

(9)

HCONTENTS

Chapter

5: Database

Engine

Tuning

Advisor

149

Database

Engine Tuning

Advisor Mechanisms

149

Database

Engine Tuning

Advisor

Examples

154

Tuning

a

Query

154

Tuning

aTraceWorkload 158

Tuning

from the Procedure Cache 161

Database

Engine Tuning

Advisor

Limitations

161

Summary

162

Chapter

6:

Lookup

Analysis

163

Purpose

of

Lookups

163

Drawbacks of

Lookups

165

Analyzing

the Cause of

a

Lookup

166

Resolving

Lookups

168

Using

aClustered Index 168

Using

a

Covering

Index 168

Using

an IndexJoin 172

Summary

174

Chapter

7: Statistics

Analysis

175

The Role of Statistics in

Query

Optimization

175

Statistics

onanIndexed Column 176 Benefits of

Updated

Statistics 176 Drawbacks of Outdated Statistics 179

Statistics

on a

Nonindexed Column

181

Benefits of Statisticson aNonindexed Column 181

Drawback of

Missing

Statisticson aNonindexed Column 186

Analyzing

Statistics

188

Density

190

Statisticson aMulticolumn Index 191

StatisticsonaFilteredIndex 192

(10)

Statistics Maintenance

193

Automatic Maintenance 194

Auto Create Statistics 194

Auto

Update

Statistics

195 Auto

Update

Statistics

Asynchronously

195

Manual

Maintenance

196

Manage

Statistics

Settings

197

Generate Statistics 198

Statistics Maintenance Status

199

Status

of

Auto Create Statistics 199

Status of Auto

Update

Statistics 200

Analyzing

the Effectiveness

of Statistics for

a

Query

200

Resolving

a

Missing

Statistics Issue 200

Resolving

anOutdated Statistics Issue 202

Recommendations

205

Backward

Compatibility

of Statistics 205

Auto Create Statistics 206

Auto

Update

Statistics

206 Automatic

Update

Statistics

Asynchronously

206

Amountof

Sampling

to Collect Statistics 207

Summary

208

Chapter

8:

Fragmentation

Analysis

211

Causes

of

Fragmentation

211

Page Split by

anUPDATE Statement 213

Page

Split

by

anINSERT Statement 217

Fragmentation

Overhead

218

Analyzing

the Amount of

Fragmentation

221

Analyzing

the

Fragmentation

of

a

Small Table

223

(11)

CONTENTS

Fragmentation

Resolutions

225

Dropping

and

Re-creating

the Index 225

Re-creating

the Index with theDROP_EXISTING Clause 226

Executing

theALTER INDEX REBUILD Statement 227

Executing

the ALTER INDEX REORGANIZEStatement 229

Significance

of the Fill Factor

230

Automatic

Maintenance

233

Summary

239

Chapter

9: Execution Plan Cache

Analysis

241

Execution Plan

Generation

241

Parser 243

Binding

243

Optimization

244

ExecutionPlan

Caching

251

Components

of

the Execution Plan

251

Query

Plan 251

Execution Context 251

Aging

of the

Execution

Plan

251

Analyzing

the Execution Plan

Cache

252

Execution Plan

Reuse

253

Ad

Hoc

Workload

254

Prepared

Workload 254 Plan

Reusability

ofanAd HocWorkload 255 Plan

Reusability

ofa

Prepared

Workload

262

Parameter

Sniffing

270

Query

Plan

Hash and

Query

Hash

273

Execution

Plan Cache Recommendations

276

Explicitly

Parameterize Variable Parts ofa

Query

277 Create Stored

Procedures

to

Implement

Business

Functionality

277 xiv

(12)

Code with

sp_executesql

to Avoid Stored

Procedure

Maintenance 277

Implement

the

Prepare/Execute

Model to Avoid

Resending

a

Query

String

277

Avoid Ad Hoc

Queries

278

Prefer

sp_executesql

overEXECUTEfor

Dynamic

Queries 278 Parameterize Variable Parts of Queries with Care 279

Do Not Allow

Implicit

Resolutionof

Objects

in Queries 279

Summary

279

Chapter

10:

Query Recompilation

281

Benefits and

Drawbacks

of

Recompilation

281

Identifying

the Statement

Causing

Recompilation

284

Analyzing

Causes of

Recompilation

285

Schema or

Bindings Changes

287

Statistics

Changes

287

Deferred

Object

Resolution 289

SET

Options

Changes

293

Execution Plan

Aging

294

Explicit

Call to

sp_recompile

294

Explicit

Use ofRECOMPILE 295

Avoiding

Recompilations

297

Don't Interleave DDL and DML Statements 297

Avoiding Recompilations

Caused

by

Statistics

Change

299

Using

the KEEPFIXED PLAN

Option

299

Disable

Auto

Update

Statisticsonthe Table 301

Using

Table Variables 301

Avoiding Changing

SET

Options

WithinaStoredProcedure 304

Using

OPTIMIZE FOR

Query

Hint 305

Using

Plan Guides 307

Summary

311

(13)

CONTENTS

Chapter

11:

Query Design

Analysis

...313

Query Design

Recommendations

313

Operating

on

Small Result Sets

314

Limit the Number of Columns in

selectjist

314

Use

Highly

Selective WHERE Clauses 315

Using

Indexes

Effectively

315 Avoid

Nonsargable

Search Conditions 316

BETWEENvs.IN/OR 316

Avoid Arithmetic

Operators

onthe WHEREClauseColumn 319 Avoid Functionsonthe WHERE Clause Column 320

Avoiding Optimizer

Hints

323

JOIN Hint 323

INDEX Hints 327

Using

Domain

and

Referential

Integrity

327

NOT

NULL

Constraint 328

Declarative Referential

Integrity

331

Avoiding

Resource-Intensive

Queries

333

Avoid Data

Type

Conversion 333

Use EXISTSover

C0UNT(*)

to

Verify

Data Existence

335

Use UNIONALLInstead ofUNION 336 Use Indexes for

Aggregate

and Sort Conditions 337 Avoid Local Variablesin aBatch

Query

338 Be Careful When

Naming

Stored Procedures 341

Reducing

the Number of Network

Round-Trips

343

Execute

Multiple

Queries

Together

343 Use

SETN0C0UNT

343

Reducing

the Transaction Cost

344

Reduce

Logging

Overhead 344

Reduce LockOverhead 345

Summary

xvi

(14)

Chapter

12:

Blocking Analysis

349

Blocking

Fundamentals

349

Understanding

Blocking

350

Atomicity

350

Consistency

353 Isolation 353

Durability

354

Locks

354

Lock

Granularity

355 Row-Level Lock 355

Page-Level

Lock 357 Extent-Level Lock 358

Heap

orB-tree Lock 358

Table-Level Lock 358

Database-Level Lock 359

Lock

Operations

and Modes

359

Lock Escalation 359

Lock

Modes 359

Exclusive

(X)

Mode 364

Intent Shared

(IS),

Intent Exclusive

(IX),

and Shared withIntent Exclusive

(SIX)

Modes 364 Schema Modification

(Sch-M)

andSchema

Stability

(Sch-S)

Modes 365 Bulk

Update

(BU)

Mode 365

Key-range

Mode 365 Lock

Compatibility

366

Isolation Levels

366

Read Uncommitted 366 Read Committed 367

Repeatable

Read 368 Serializable 371

Snapshot

376 xvii

(15)

CONTENTS

Effect of Indexes

on

Locking

376

Effect ofa NonclusteredIndex 377

Effect ofaClustered Index 379

Effect of IndexesontheSerializable Isolation Level 380

Capturing Blocking

Information

380

Capturing

Blocking

Informationwith SQL 381 Extended Events and the

blocked_process_report

Event 383

Blocking

Resolutions

385

Optimize

the Queries 386

Decreasethe Isolation Level 386

Partition the Contended Data

387

Recommendations

to Reduce

Blocking

388

Automation to Detect and Collect

Blocking

Information

389

Summary

392

Chapter

13: Deadlock

Analysis

393

Deadlock Fundamentals

393

Choosing

the Deadlock Victim 394

Using

Error

Handling

to CatchaDeadlock 394

Deadlock

Analysis

395

Collecting

DeadlockInformation 395

Analyzing

the Deadlock 398

Avoiding

Deadlocks

403

Accessing

ResourcesintheSame

Physical

Order 403

Decreasing

the Number of Resources Accessed 404 ConvertaNonclustered Index toaClustered Index 404 Use a

Covering

Index foraSELECT Statement 404

Minimizing

LockContention 404

Summary

406

(16)

Chapter

14:

Cursor Cost

Analysis

407

Cursor Fundamentals

407

Cursor Location 409

Cursor

Concurrency

410

Cursor

Types

411

Cursor Cost

Comparison

414

Cost

Comparison

on Cursor Location 414 Cost

Comparison

on Cursor

Concurrency

416

Read-Only

416

Cost

Comparison

onCursor

Type

417

Default Result Set

419

Benefits 420

Multiple

Active

ResultSets 420

Drawbacks 421

Cursor

Overhead

423

Analyzing

OverheadwithT-SQL Cursors 423

Cursor Recommendations 427

Summary

428

Chapter

15: Database

Performance

Testing

429

Database Performance

Testing

429

A

Repeatable

Process 430

Distributed

Replay

430

Capturing

Data with

the Server Side Trace

431

Distributed

Replay

for Database

Testing

434

Configuring

the Client 435

Running

theDistributedTests 436

Conclusion

436

(17)

CONTENTS

Chapter

16:

Database

Workload

Optimization..

437

Workload

Optimization

Fundamentals

437

Workload Optimization Steps

438

Sample

Workload 439

Capturing

the Workload

441

Analyzing

the

Workload

442

Identifying

the

Costliest

Query

444

Determining

the

Baseline

Resource Use of the Costliest

Query

445

Overall

Resource Use 445

Detailed ResourceUse 446

Analyzing

and

Optimizing

External Factors

448

Analyzing

the

Connection Options

Used

by

the

Application

449

Analyzing

theEffectiveness of Statistics 449

Analyzing

the Need for

Defragmentation

450

Analyzing

the Internal Behavior of the Costliest

Query

453

Analyzing

the

Query

Execution Plan 454

Identifying

the

Costly Steps

in the

Execution

Plan 456

Analyzing

the

Processing

Strategy

456

Optimizing

the Costliest

Query

457

Modifying

an

Existing

Index 457

Analyzing

the

Application

ofaJoin Hint 459

Avoiding

the

Clustered

Index Scan

Operation

461

Modifying

the Procedure 462

Analyzing

the

Effect

on

Database

Workload

464

Iterating Through

Optimization

Phases

466

Summary

468

(18)

Chapter

17:

SQL

Server

Optimization

Checklist

469

Database

Design

469

Balancing

Under- and

Ovemormalization

470

Benefiting

from

Entity-Integrity

Constraints 471

Benefiting

from Domain and Referential

Integrity

Constraints 472

Adopting Index-Design

Best Practices 474

Avoiding

the Use of the sp_ Prefix for Stored Procedure Names 476

Minimizing

the Use of

Triggers

476

Query

Design

476

Use the Command SET NOCOUNT ON 476

Explicitly

Define the

Owner

of an

Object

477

Avoid

Nonsargable

Search Conditions 477

Avoid

Arithmetic Expressions

onthe WHERE Clause Column 477

Avoid

Optimizer

Hints 478

Stay Away

from

Nesting

Views 478

Ensure No

Implicit

Data

Type

Conversions 479

Minimize

Logging

Overhead 479

Adopt

Best Practices for

Reusing

Execution Plans 479

Adopt

Best Practices for Database Transactions 480 EliminateorReduce the

Overhead of

Database Cursors 481

Configuration

Settings

481

Affinity

Mask 481

Memory Configuration Options

482 Cost Threshold for Parallelism 482 Max

Degree

ofParallelism 482

Optimize

for Ad Hoc Workloads 482

Query

Governor Cost Limit 482

Fill Factor

(%)

482

Blocked Process Threshold 483

(19)

CONTENTS

Database File

Layout

483

Database

Compression

483

Database Administration

483

Keep

the Statistics

Up-to-Date

484

MaintainaMinimum Amount of Index Defragmentation 484

Cycle

the SQL Error

Log

File 485 Avoid Database Functions SuchAs

AUTO_CLOSE

or

AUTO_SHRINK

485

Database

Backup

485

Incremental

and

Transaction

Log Backup

Frequency

485

Backup

Distribution 486

Backup Compression

486

Summary

487

Index

.

489

References

Related documents

The Secure Remote Access Session window is displayed as shown in the following figure, and the plug-in begins to download.. When the download has completed, the Secure Remote

[r]

and services related to mobile phone market including: mobile phones, accessories, content services, prepaid and more.. • SAY chain provides photo finishing,

EDITOR Tarun J Tejpal MANAGING EDITOR Shoma Chaudhury DEPUTY EDITOR Ramesh Sharma EDITOR-AT-LARGE Ajit Sahi CONSULTING EDITOR Jay Mazoomdaar EDITOR, BUSINESS Shaili Chopra

The findings from the pilot study shifted my original research position of interest from exploring how Aistear (NCCA, 2009) could support early childhood educators and

To prevent slowdown of economic growth and to combat the problems of public economics caused by ageing, several reports, including Moody’s report (2014) and the report by Prime

As a result, some industry watchers began to challenge the validity of partnership and pure-play business models supporting the online (Internet) grocery home delivery

strategies to combating SHARP-related incidents against gay soldiers in the U.S. While this cannot be an all-inclusive study of all the issues within the military regarding