SQL Server Query Performance Tuning Distilled,9781590594216

SQL Server Query Performance Tuning Distilled

by
Edition: 2nd
ISBN13: 9781590594216
ISBN10: 1590594215
Format: Paperback
Pub. Date: 2004-10-30
Publisher(s): Apress
List Price: $57.74

Buy New

Usually Ships in 2-3 Business Days.
$54.99
Add to Cart

Rent Book

Select for Price
Add to Cart
There was a problem. Please try again later.

Used Book

We're Sorry
Sold Out

eBook

We're Sorry
Not Available

Buy from our Marketplace starting at $20.12

How Marketplace Works:

  • This item is offered by an independent seller and not shipped from our warehouse
  • Item details like edition and cover design may differ from our description; see seller's comments before ordering.
  • Sellers much confirm and ship within two business days; otherwise, the order will be cancelled and refunded.
  • Marketplace purchases cannot be returned to eCampus.com. Contact the seller directly for inquiries; if no response within two days, contact customer service.
  • Additional shipping costs apply to Marketplace purchases. Review shipping costs at checkout.

Summary

...very user-friendly, easy to understand, very well designed, and fun to read. - Palak Patel, SQL SquareI don't feel like this book bogs you down with all of the internal details that a lot of the other books do. - Aaron Weiker WeblogMany SQL Server-based applications perform far below expectationcosting money and time. And until now, there has been very little practical information available that explains how to identify and cure performance problems in SQL Server. But this book offers essential content to fill in the gap.SQL Server Query Performance Tuning Distilled, Second Editionincludes updated material plus one new chapter, and explains how to design and tune SQL Server applications to cope with ever-increasing workloads. With proven strategy, this book teaches you how to use SQL Server tools to identify a performance bottleneck, isolate the root cause, apply possible resolutions, and then quantify performance improvements. Also covered are crucial issues like index analysis and tuning, preventing deadlocks, optimizing workload, and minimizing costs.

Table of Contents

About the Author xvii
About the Technical Reviewer xix
Acknowledgments xxi
Introduction xxiii
SQL Query Performance Tuning
1(16)
The Performance Tuning Process
2(5)
An Iterative Process
4(3)
Performance vs. Price
7(1)
``Good Enough'' Tuning
7(1)
Performance Baseline
8(1)
Where to Focus Efforts
9(2)
SQL Server Performance Killers
11(4)
Poor Indexing
11(1)
Inaccurate Statistics
12(1)
Excessive Blocking and Deadlocks
12(1)
Poor Query Design
12(1)
Poor Database Design
13(1)
Excessive Fragmentation
13(1)
Nonreusable Execution Plans
13(1)
Frequent Recompilation of Execution Plans
14(1)
Improper Use of Cursors
14(1)
Improper Configuration of the Database Log
14(1)
Ineffective Connection Pooling
14(1)
Summary
15(2)
System Performance Analysis
17(42)
System Monitor Tool
17(2)
System Resource Utilization
19(1)
Identifying Bottlenecks
19(1)
Bottleneck Resolution
20(1)
Memory Bottleneck Analysis
20(5)
SQL Server Memory Management
20(5)
Memory Bottleneck Resolutions
25(4)
Optimizing Application Workload
27(1)
Allocating More Memory to SQL Server
27(1)
Increasing System Memory
27(1)
Enabling 3GB of Process Space
27(1)
Using Extended Memory Within SQL Server
28(1)
Disk Bottleneck Analysis
29(3)
Disk Counters
29(3)
Disk Bottleneck Resolutions
32(8)
Optimizing Application Workload
32(1)
Using a Faster Disk Drive
32(1)
Using a RAID Array
33(2)
Using a Battery-Backed Controller Cache
35(1)
Adding System Memory
36(1)
Creating Multiple Files and Filegroups
36(3)
Placing the Table and Index on Separate Disks
39(1)
Saving Log Files to a Separate Physical Disk
39(1)
Creating tempdb on RAID 0
39(1)
Processor Bottleneck Analysis
40(2)
% Processor Time
40(1)
% Privileged Time
41(1)
Processor Queue Length
41(1)
Context Switches/sec
41(1)
Processor Bottleneck Resolutions
42(2)
Optimizing Application Workload
42(1)
Using More or Faster Processors
42(1)
Using a Large L2 Cache
42(1)
Running More Efficient Controllers/Drivers
43(1)
Not Running a Screen Saver
43(1)
Using Lightweight Pooling or NT Fibers
43(1)
Network Bottleneck Analysis
44(1)
Bytes Total/sec
44(1)
% Net Utilization
45(1)
Network Bottleneck Resolutions
45(1)
Optimizing Application Workload
45(1)
Adding Network Adapters
46(1)
Moderating and Avoiding Interruptions
46(1)
SQL Server Overall Performance
46(3)
Missing Indexes
47(1)
Database Blocking
47(1)
Nonreusable Execution Plans
48(1)
General Behavior
49(1)
Creating a Baseline
49(6)
Create a Reusable List of Performance Counters
50(2)
Create a Counter Log Using the List of Performance Counters
52(2)
Minimize Performance Monitor Overhead
54(1)
System Behavior Analysis Against Baseline
55(2)
Summary
57(2)
SQL Query Performance Analysis
59(36)
The SQL Profiler Tool
59(9)
Events
60(3)
Data Columns
63(2)
Filters
65(1)
Trace Template
66(1)
A New Trace
66(2)
SQL Profiler Recommendations
68(4)
Limiting the Number of Events and Data Columns
68(1)
Discarding Start Events for Performance Analysis
69(1)
Limiting the Trace Output Size
69(1)
Avoiding Online Data Column Sorting
69(1)
Running Profiler Remotely
70(1)
Capturing a Trace Using Stored Procedures
70(2)
Costly Queries
72(5)
Identifying Costly Queries
73(4)
Identifying Slow-Running Queries
77(1)
Execution Plan
77(12)
Analyzing the Query Execution Plan
79(1)
Identifying the Costly Steps in an Execution Plan
80(1)
Analyzing Index Effectiveness
81(1)
Analyzing Join Effectiveness
82(5)
SHOWPLAN_ALL vs. STATISTICS PROFILE
87(2)
Query Cost
89(5)
Server Trace Information
89(1)
Execution Time
90(1)
STATISTICS IO
91(3)
Summary
94(1)
Index Analysis
95(48)
What Is an Index?
96(4)
The Benefit of Indexes
97(2)
Index Overhead
99(1)
Index Design Recommendations
100(12)
Examine the WHERE Clause and Join Criteria Columns
100(2)
Use Narrow Indexes
102(2)
Examine Column Uniqueness
104(3)
Examine the Column Data Type
107(1)
Consider Column Order
108(4)
Consider the Type of Index
112(1)
Clustered Index
112(9)
Heap Table
112(1)
Relationship with Nonclustered Indexes
112(3)
Clustered Index Recommendations
115(6)
Nonclustered Index
121(2)
Nonclustered Index Maintenance
121(1)
Introducing the Bookmark Lookup
121(1)
Nonclustered Index Recommendations
121(2)
Clustered vs. Nonclustered Index
123(5)
Benefits of a Clustered Index over a Nonclustered Index
123(2)
Benefits of a Nonclustered Index over a Clustered Index
125(3)
Advanced Indexing Techniques
128(11)
Covering Index
128(2)
Index Intersection
130(2)
Index Join
132(3)
Indexed View
135(4)
Additional Characteristics of Indexes
139(3)
Different Column Sort Order
139(1)
Index on Computed Columns
140(1)
Index on BIT Data Type Columns
140(1)
CREATE INDEX Statement Processed As a Query
140(1)
Parallel Index Creation
141(1)
Considering the Index Tuning Wizard
142(1)
Summary
142(1)
Index Tuning Wizard
143(18)
Index Tuning Architecture
144(5)
Index Tuning Wizard Recommendations
149(7)
Choosing a SQL Trace for the Index Tuning Wizard
156(1)
Index Tuning Wizard Limitations
156(3)
Summary
159(2)
Bookmark Lookup Analysis
161(14)
Purpose of a Bookmark Lookup
161(2)
Drawbacks of a Bookmark Lookup
163(2)
Analyzing the Cause of a Bookmark Lookup
165(2)
Resolving a Bookmark Lookup
167(7)
Using a Clustered Index
167(1)
Using a Covering Index
168(4)
Using an Index Join
172(2)
Summary
174(1)
Statistics Analysis
175(36)
The Importance of Statistics
175(1)
Statistics on an Indexed Column
176(4)
Benefits of Updated Statistics
176(2)
Drawbacks of Outdated Statistics
178(2)
Statistics on a Nonindexed Column
180(8)
Benefits of Statistics on a Nonindexed Column
180(5)
Drawback of Missing Statistics on a Nonindexed Column
185(3)
Analyzing Statistics
188(6)
Density
191(1)
Statistics on a Multicolumn Index
192(2)
Statistics Maintenance
194(7)
Automatic Maintenance
194(4)
Manual Maintenance
198(2)
Statistics Maintenance Status
200(1)
Analyzing the Effectiveness of Statistics for a Query
201(6)
Resolving a Missing Statistics Issue
201(3)
Resolving an Outdated Statistics Issue
204(3)
Recommendations
207(2)
Backward Compatibility of Statistics
207(1)
Auto Create Statistics
207(1)
Auto Update Statistics
207(2)
Amount of Sampling to Collect Statistics
209(1)
Summary
209(2)
Fragmentation Analysis
211(32)
Causes of Fragmentation
211(8)
Page Split by an UPDATE Statement
214(3)
Page Split by an INSERT Statement
217(2)
Fragmentation Overhead
219(2)
Analyzing the Amount of Fragmentation
221(5)
Analyzing the Fragmentation of a Small Table
224(2)
Fragmentation Resolutions
226(7)
Dropping and Re-creating the Index
227(2)
Re-creating the Index with the DROP_EXISTING Clause
229(1)
Executing the DBCC DBREINDEX Statement
230(2)
Executing the DBCC INDEXDEFRAG Statement
232(1)
Significance of the Fill Factor
233(3)
Automatic Maintenance
236(6)
Summary
242(1)
Execution Plan Cache Analysis
243(40)
Execution Plan Generation
243(10)
Command Parsing
246(1)
Normalization
247(1)
Optimization
248(5)
Execution Plan Caching
253(1)
Components of the Execution Plan
253(1)
Query Plan
253(1)
Execution Context
254(1)
Aging of the Execution Plan
254(1)
Analyzing the Execution Plan Cache
255(1)
Execution Plan Reuse
256(14)
Ad Hoc Workload
256(1)
Well-Defined Workload
257(1)
Plan Reusability of an Ad Hoc Workload
257(5)
Plan Reusability of a Well-Defined Workload
262(8)
Execution Plan Cache Recommendations
270(11)
Explicitly Parameterize Variable Parts of a Query
270(1)
Use Stored Procedures to Implement Business Functionality
270(1)
Use sp_executesql to Avoid Stored Procedure Maintenance
271(1)
Use the Prepare/Execute Model to Avoid Resending a Query String
271(1)
Avoid Ad Hoc Queries
271(1)
Use sp_executesql over EXECUTE for Dynamic Queries
271(1)
Parameterize Variable Parts of Queries with Care
272(3)
Avoid Modifying Environment Settings Between Connections
275(1)
Avoid Implicit Resolution of Objects in Queries
276(3)
Avoid Implicit Resolution of Stored Procedure Names
279(2)
Summary
281(2)
Stored Procedure Recompilation
283(32)
Benefits and Drawbacks of Recompilation
283(3)
Identifying the Statement Causing Recompilation
286(3)
Analyzing Causes of Recompilation
289(11)
Schema or Bindings Changes
290(1)
Statistics Changes
290(3)
Deferred Object Resolution
293(2)
SET Options Changes
295(2)
Execution Plan Aging
297(1)
Explicit Call to sp_recompile
297(1)
Explicit Use of the RECOMPILE Clause
298(2)
Avoiding Recompilations
300(13)
Do Not Interleave DDL and DML Statements
300(3)
Avoid Recompilations Caused by Statistics Change
303(2)
Do Not Refer to a Temporary Table Created Outside the Stored Procedure
305(2)
Use Table Variables
307(2)
Avoid Changing SET Options Within a Stored Procedure
309(1)
Isolate the Plan for the Statement Causing Recompilation
310(3)
Summary
313(2)
Query Design Analysis
315(50)
Query Design Recommendations
315(1)
Operate on Small Result Sets
316(2)
Limit the Number of Columns in select_list
316(1)
Use Highly Selective WHERE Clauses
317(1)
Use Indexes Effectively
318(8)
Avoid Nonindexable Search Conditions
318(5)
Avoid Arithmetic Operators on the WHERE Clause Column
323(1)
Avoid Functions on the WHERE Clause Column
324(2)
Avoid Optimizer Hints
326(6)
JOIN Hint
327(3)
INDEX Hints
330(1)
FORCEPLAN Hints
331(1)
Use Domain and Referential Integrity
332(5)
NOT NULL Constraint
332(2)
Declarative Referential Integrity
334(3)
Avoid Resource-Intensive Queries
337(23)
Avoid Implicit Data Type Conversion
337(4)
Use EXISTS over COUNT(*) to Verify Data Existence
341(1)
Optimize the Row Count Technique
341(2)
Use UNION ALL over UNION
343(1)
Use an Additional JOIN Clause over UNION
344(1)
Use Indexes for Aggregate and Sort Conditions
345(1)
Avoid Redundant WHERE Clause Conditions
346(1)
Use of JOIN Operations vs. Subqueries
347(4)
Avoid Local Variables in a Batch Query
351(5)
Be Careful Naming Stored Procedures
356(4)
Reduce the Number of Network Round-trips
360(1)
Execute Multiple Queries Together
360(1)
Use SET NOCOUNT
360(1)
Reduce the Transaction Cost
361(3)
Reduce Logging Overhead
361(1)
Reduce Lock Overhead
362(2)
Summary
364(1)
Blocking Analysis
365(58)
Blocking Fundamentals
365(1)
Understanding Blocking
366(5)
Atomicity
366(3)
Consistency
369(1)
Isolation
369(1)
Durability
370(1)
Database Locks
371(11)
Lock Granularity
371(4)
Lock Escalation
375(1)
Lock Modes
375(6)
Lock Compatibility
381(1)
Isolation Levels
382(9)
Read Uncommitted
382(1)
Read Committed
383(1)
Repeatable Read
383(3)
Serializable
386(5)
Effect of Indexes on Locking
391(4)
Effect of a Nonclustered Index
392(2)
Effect of a Clustered Index
394(1)
Effect of Indexes on the Serializable Isolation Level
395(1)
Blocking Analysis
395(8)
Blocker Script
396(6)
Profiler Trace
402(1)
Blocking Resolutions
403(3)
Optimize the Queries
403(1)
Decrease the Isolation Level
404(1)
Partition the Contended Data
405(1)
Covering Index on Contended Data
405(1)
Recommendations to Reduce Blocking
406(1)
Automation to Detect and Collect Blocking Information
407(14)
Summary
421(2)
Deadlock Analysis
423(20)
Deadlock Fundamentals
423(2)
Choosing the Deadlock Victim
424(1)
Handling Deadlocks
425(1)
Deadlock Analysis
425(7)
Collecting Deadlock Information
425(2)
Analyzing the Deadlock
427(5)
Deadlock Resolutions
432(10)
Access Resources in the Same Chronological Order
432(1)
Serialize Access to Resources
433(4)
Decrease the Number of Resources Accessed
437(3)
Minimize Lock Contention
440(2)
Summary
442(1)
Cursor Cost Analysis
443(30)
Cursor Fundamentals
443(7)
Cursor Location
445(1)
Cursor Concurrency
446(1)
Cursor Type
447(3)
Cursor Cost Comparison
450(6)
Cost Comparison on Cursor Location
450(2)
Cost Comparison on Cursor Concurrency
452(1)
Cost Comparison on Cursor Type
453(3)
Default Result Set
456(4)
Benefits
456(1)
Drawbacks
457(3)
Analyzing SQL Server Overhead with Cursors
460(10)
Analyzing SQL Server Overhead with T-SQL Cursors
461(3)
Analyzing SQL Server Overhead with API Cursors
464(6)
Cursor Recommendations
470(2)
Summary
472(1)
Database Connection Performance Issues
473(18)
Database Connection Cost
473(1)
Connection Pooling
474(5)
ODBC Connection Pooling
475(2)
OLE DB Session Pooling
477(2)
Effect of Session Pooling on Performance
479(5)
ADO/ADO.NET Threading Model
484(1)
Selecting the Data Access Mechanism
485(1)
Selecting Network Transfer Protocols
486(2)
Database Connection Recommendations
488(1)
Summary
489(2)
Database Workload Optimization
491(32)
Workload Optimization Fundamentals
491(1)
Workload Optimization Steps
492(3)
Sample Workload
493(2)
Capturing the Workload
495(2)
Analyzing the Workload
497(1)
Identifying the Costliest Query
498(2)
Determining the Baseline Resource Use of the Costliest Query
500(2)
Overall Resource Use
500(1)
Detailed Resource Use
501(1)
Analyzing and Optimizing External Factors
502(5)
Analyzing the Use of the Network Protocol
502(1)
Analyzing the Effectiveness of Statistics
502(2)
Analyzing the Need for Defragmentation
504(3)
Analyzing the Internal Behavior of the Costliest Query
507(2)
Analyzing the Query Execution Plan
507(1)
Identifying the Costly Steps in the Execution Plan
508(1)
Analyzing the Effectiveness of the Processing Strategy
508(1)
Optimizing the Costliest Query
509(7)
Creating a New Index
510(1)
Analyzing Index Behavior with an Index Hint
511(1)
Avoiding the Bookmark Lookup Operation
512(2)
Using Database Constraints
514(1)
Redesigning the Costliest Query
515(1)
Analyzing the Effect on Database Workload
516(2)
Iterating Through Optimization Phases
518(4)
Long-Running Queries
518(1)
Error Conditions
519(3)
Summary
522(1)
Scalability Scenarios
523(24)
Hit Count Measurement
523(7)
Large Dataset Migration
530(10)
Log Flushes/sec
532(1)
Transactions/sec
533(1)
Action Queries Treated As Separate Transactions
533(2)
Sizing the Transaction Correctly
535(2)
Calculating the Average Row Size of a Table
537(3)
Queue Processing
540(6)
Summary
546(1)
SQL Server Optimization Checklist
547(20)
Database Design
547(6)
Balance Between Under- and Overnormalization
548(1)
Benefit from Entity-Integrity Constraints
549(1)
Benefit from Domain and Referential Integrity Constraints
550(2)
Adopt Index-Design Best Practices
552(1)
Do Not Use the sp_ Prefix for Stored Procedure Names
553(1)
Query Design
553(5)
Use the Command SET NOCOUNT ON
554(1)
Explicitly Define the Owner of an Object
554(1)
Avoid Nonindexable Search Conditions
554(1)
Avoid Arithmetic Operators/Functions on WHERE Clause Columns
555(1)
Avoid Optimizer Hints
555(1)
Minimize Logging Overhead
555(1)
Reuse Query Execution Plans
556(1)
Optimize Database Transactions
557(1)
Reduce the Overhead of Database Cursors
558(1)
Configuration Settings
558(2)
Affinity Mask
558(1)
Memory Configuration Options
558(1)
Database File Layout
559(1)
Database File Compression
560(1)
Database Administration
560(4)
Keep the Statistics Up to Date
560(1)
Maintain a Minimum Amount of Index Defragmentation
561(1)
Update the Stored Procedure Plans
562(1)
Cycle the SQL Error Log File
563(1)
Minimize the Overhead of SQL Tracing
563(1)
Database Backup
564(2)
Transaction Log Backup Frequency
564(1)
Backup Distribution
565(1)
Backup Compression
566(1)
Summary
566(1)
Index 567

An electronic version of this book is available through VitalSource.

This book is viewable on PC, Mac, iPhone, iPad, iPod Touch, and most smartphones.

By purchasing, you will be able to view this book online, as well as download it, for the chosen number of days.

Digital License

You are licensing a digital product for a set duration. Durations are set forth in the product description, with "Lifetime" typically meaning five (5) years of online access and permanent download to a supported device. All licenses are non-transferable.

More details can be found here.

A downloadable version of this book is available through the eCampus Reader or compatible Adobe readers.

Applications are available on iOS, Android, PC, Mac, and Windows Mobile platforms.

Please view the compatibility matrix prior to purchase.