SQL server 2012 query performance tuning

SQL Server 2012 Query

Performance Tuning

Grant Fritchey

SQL Server 2012 Query Performance Tuning

Copyright © 2012 by Grant Fritchey

This work is subject to copyright. All rights are reserved by the Publisher, whether the whole or part of the material is

concerned, speci fi cally the rights of translation, reprinting, reuse of illustrations, recitation, broadcasting, reproduction

on micro fi lms or in any other physical way, and transmission or information storage and retrieval, electronic

adaptation, computer software, or by similar or dissimilar methodology now known or hereafter developed. Exempted

from this legal reservation are brief excerpts in connection with reviews or scholarly analysis or material supplied

speci fi cally for the purpose of being entered and executed on a computer system, for exclusive use by the purchaser

of the work. Duplication of this publication or parts thereof is permitted only under the provisions of the Copyright

Law of the Publisher’s location, in its current version, and permission for use must always be obtained from Springer.

Permissions for use may be obtained through RightsLink at the Copyright Clearance Center. Violations are liable to

prosecution under the respective Copyright Law.

Trademarked names, logos, and images may appear in this book. Rather than use a trademark symbol with every

occurrence of a trademarked name, logo, or image we use the names, logos, and images only in an editorial fashion and

to the bene fi t of the trademark owner, with no intention of infringement of the trademark.

The use in this publication of trade names, trademarks, service marks, and similar terms, even if they are not identi fi ed

as such, is not to be taken as an expression of opinion as to whether or not they are subject to proprietary rights.

While the advice and information in this book are believed to be true and accurate at the date of publication, neither

the authors nor the editors nor the publisher can accept any legal responsibility for any errors or omissions that may be

made. The publisher makes no warranty, express or implied, with respect to the material contained herein.

President and Publisher: Paul Manning

Lead Editor: Jonathan Gennick

Technical Reviewer: Joseph Sack

Editorial Board: Steve Anglin, Ewan Buckingham, Gary Cornell, Louise Corrigan, Morgan Ertel, Jonathan Gennick,

Jonathan Hassell, Robert Hutchinson, Michelle Lowman, James Markham, Matthew Moodie, Jeff Olson,

Jeffrey Pepper, Douglas Pundick, Ben Renow-Clarke, Dominic Shakeshaft, Gwenan Spearing, Matt Wade,

Tom Welsh

Coordinating Editor: Anita Castro, Christine Ricketts

Copy Editor: Patrick Meador

Compositor: SPi Global

Indexer: SPi Global

Artist: SPi Global

Cover Designer: Anna Ishchenko

Distributed to the book trade worldwide by Springer Science+Business Media, LLC., 233 Spring Street, 6th Floor,

New York, NY 10013. Phone 1-800-SPRINGER, fax (201) 348-4505, e-mail [email protected],

or visit www.springeronline.com.

For information on translations, please e-mail [email protected], or visit www.apress.com.

Apress and friends of ED books may be purchased in bulk for academic, corporate, or promotional use. eBook versions

and licenses are also available for most titles. For more information, reference our Special Bulk Sales–eBook Licensing

web page at www.apress.com/bulk-sales.

Any source code or other supplementary materials referenced by the author in this text is available to readers at www.

apress.com. For detailed information about how to locate your book’s source code, go to www.apress.com/source-code.

ISBN 978-1-4302-4203-1 ISBN 978-1-4302-4204-8 (eBook)

DOI 10.1007/978-1-4302-4204-8

iii

Contents at a Glance

About the Author ......................................................................................................xxiii

About the Technical Reviewer .................................................................................... xxv

Acknowledgments ....................................................................................................xxvII

Introduction ............................................................................................................... xxix

■Chapter 1: SQL Query Performance Tuning ..................................................................1

■Chapter 2: System Performance Analysis .................................................................15

■Chapter 3: SQL Query Performance Analysis .............................................................59

■Chapter 4: Index Analysis ..........................................................................................99

■Chapter 5: Database Engine Tuning Advisor ............................................................149

■Chapter 6: Lookup Analysis .....................................................................................163

■Chapter 7: Statistics Analysis .................................................................................175

■Chapter 8: Fragmentation Analysis .........................................................................211

■Chapter 9: Execution Plan Cache Analysis ..............................................................241

■Chapter 10: Query Recompilation ............................................................................281

■Chapter 11: Query Design Analysis .........................................................................313

■Chapter 12: Blocking Analysis ................................................................................349

■Chapter 13: Deadlock Analysis ................................................................................393

■ CONTENTS AT A GLANCE

iv

■Chapter 14: Cursor Cost Analysis ............................................................................407

■Chapter 15: Database Performance Testing ............................................................429

■Chapter 16: Database Workload Optimization .........................................................437

■Chapter 17: SQL Server Optimization Checklist ......................................................469

Index ...........................................................................................................................489

v

Contents

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 Execution Plans ................................................................................................................................... 12

Excessive Blocking and Deadlocks.............................................................................................................. 12

Non-Set-Based Operations .......................................................................................................................... 12

Poor Database Design ................................................................................................................................. 12

Excessive Fragmentation............................................................................................................................. 13

■ CONTENTS

vi

Nonreusable Execution Plans ...................................................................................................................... 13

Frequent Recompilation of Queries ............................................................................................................. 13

Improper Use of Cursors .............................................................................................................................. 14

Improper Configuration of the Database Log ............................................................................................... 14

Excessive Use or 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

■ CONTENTS

vii

Changing from a 32-bit to a 64-bit Processor ............................................................................................. 29

Data Compression ....................................................................................................................................... 29

Enabling 3GB of Process Address Space ..................................................................................................... 29

Disk Bottleneck Analysis ...................................................................................................... 30

Disk Counters .............................................................................................................................................. 30

% Disk Time ................................................................................................................................................. 31

Current Disk 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_virtual_file_stats ....................................................................................................................... 32

Sys.dm_os_wait_stats ................................................................................................................................ 33

Disk Bottleneck Resolutions ................................................................................................. 33

Optimizing Application Workload ................................................................................................................. 33

Using a Faster I/O Path ................................................................................................................................ 34

Using a RAID Array ....................................................................................................................................... 34

Raid 0 .......................................................................................................................................................... 34

Raid 1 .......................................................................................................................................................... 35

Raid 5 .......................................................................................................................................................... 35

Raid 6 .......................................................................................................................................................... 35

RAID 1+0 (RAID 10) ...................................................................................................................................... 36

Using a SAN 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 Files to a Separate Physical Disk ....................................................................................... 40

Partitioning Tables ....................................................................................................................................... 40

■ CONTENTS

viii

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 More or Faster 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

■ CONTENTS

ix

Considerations for Monitoring Virtual Machines .................................................................. 50

Creating a Baseline .............................................................................................................. 50

Creating a Reusable List of Performance Counters ..................................................................................... 51

Creating a Counter 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

Data Storage ................................................................................................................................................ 66

Finishing the Wizard and Starting the Session ............................................................................................ 68

Extended Events Automation ................................................................................................ 70

Creating a Session Script Using the GUI ...................................................................................................... 70

Defining a Session 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 Queries with a Single Execution ....................................................................................................... 75

Costly Queries with Multiple Executions ..................................................................................................... 76

Identifying Slow-Running Queries ............................................................................................................... 78

■ CONTENTS

x

Execution Plans .................................................................................................................... 79

Analyzing a Query Execution Plan ............................................................................................................... 81

Identifying the Costly Steps in an Execution Plan ....................................................................................... 83

Analyzing Index Effectiveness ..................................................................................................................... 85

Analyzing Join Effectiveness ....................................................................................................................... 86

Hash Join ..................................................................................................................................................... 87

Actual vs. Estimated Execution Plans .......................................................................................................... 90

Plan Cache ................................................................................................................................................... 92

Query Cost ................................................................................................................................................... 93

Client Statistics ............................................................................................................................................ 93

Execution Time ............................................................................................................................................ 94

Statistics Io .................................................................................................................................................. 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

Examine the WHERE Clause and JOIN 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 with Nonclustered Indexes ................................................................................................... 116

Clustered Index Recommendations ........................................................................................................... 118

When to Use a Clustered Index .................................................................................................................. 120

Poor Design Practices for a Clustered Index ............................................................................................. 121

■ CONTENTS

xi

Nonclustered Indexes ......................................................................................................... 122

Nonclustered Index Maintenance .............................................................................................................. 123

Defining the Bookmark Lookup ................................................................................................................. 123

Nonclustered Index Recommendations ..................................................................................................... 123

Clustered vs. Nonclustered Indexes ................................................................................... 124

Benefits of a Clustered Index over a Nonclustered Index .......................................................................... 124

Benefits of a Nonclustered Index over a Clustered Index .......................................................................... 126

Advanced Indexing Techniques .......................................................................................... 128

Covering Indexes ....................................................................................................................................... 128

A Pseudoclustered Index ........................................................................................................................... 130

Recommendations ..................................................................................................................................... 130

Index Intersections .................................................................................................................................... 130

Index Joins ................................................................................................................................................ 132

Filtered Indexes ......................................................................................................................................... 134

Indexed Views ............................................................................................................................................ 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

Index on Computed Columns ..................................................................................................................... 146

Index on BIT Data Type Columns................................................................................................................ 146

CREATE INDEX Statement Processed As a Query ...................................................................................... 146

Parallel Index Creation ............................................................................................................................... 146

Online Index Creation ................................................................................................................................ 147

Considering the Database Engine Tuning Advisor ..................................................................................... 147

Summary ............................................................................................................................ 147

■ CONTENTS

xii

■Chapter 5: Database Engine Tuning Advisor ............................................................149

Database Engine Tuning Advisor Mechanisms ................................................................... 149

Database Engine Tuning Advisor Examples ........................................................................ 154

Tuning a Query ........................................................................................................................................... 154

Tuning a Trace Workload ............................................................................................................................ 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 a Clustered Index ............................................................................................................................. 168

Using a Covering Index .............................................................................................................................. 168

Using an Index Join ................................................................................................................................... 172

Summary ............................................................................................................................ 174

■Chapter 7: Statistics Analysis .................................................................................175

The Role of Statistics in Query Optimization ...................................................................... 175

Statistics on an Indexed Column ............................................................................................................... 176

Benefits of Updated Statistics ................................................................................................................... 176

Drawbacks of Outdated Statistics ............................................................................................................. 179

Statistics on a Nonindexed Column .................................................................................... 181

Benefits of Statistics on a Nonindexed Column ......................................................................................... 181

Drawback of Missing Statistics on a Nonindexed Column ........................................................................ 186

Analyzing Statistics ............................................................................................................ 188

Density ....................................................................................................................................................... 190

Statistics on a Multicolumn Index ............................................................................................................. 191

Statistics on a Filtered Index ..................................................................................................................... 192

■ CONTENTS

xiii

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 an Outdated Statistics Issue ..................................................................................................... 202

Recommendations ............................................................................................................. 205

Backward Compatibility of Statistics ......................................................................................................... 205

Auto Create Statistics ................................................................................................................................ 206

Auto Update Statistics ............................................................................................................................... 206

Automatic Update Statistics Asynchronously ............................................................................................ 206

Amount of Sampling to Collect Statistics .................................................................................................. 207

Summary ............................................................................................................................ 208

■Chapter 8: Fragmentation Analysis .........................................................................211

Causes of Fragmentation ................................................................................................... 211

Page Split by an UPDATE Statement .......................................................................................................... 213

Page Split by an INSERT Statement ........................................................................................................... 217

Fragmentation Overhead .................................................................................................... 218

Analyzing the Amount of Fragmentation ............................................................................ 221

Analyzing the Fragmentation of a Small Table ................................................................... 223

■ CONTENTS

xiv

Fragmentation Resolutions ................................................................................................ 225

Dropping and Re-creating the Index .......................................................................................................... 225

Re-creating the Index with the DROP_EXISTING Clause ............................................................................ 226

Executing the ALTER INDEX REBUILD Statement ....................................................................................... 227

Executing the ALTER INDEX REORGANIZE Statement ................................................................................ 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

Execution Plan 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 of an Ad Hoc Workload .................................................................................................... 255

Plan Reusability of a Prepared Workload ................................................................................................... 262

Parameter Sniffing ..................................................................................................................................... 270

Query Plan Hash and Query Hash ...................................................................................... 273

Execution Plan Cache Recommendations .......................................................................... 276

Explicitly Parameterize Variable Parts of a Query ...................................................................................... 277

Create Stored Procedures to Implement Business Functionality .............................................................. 277