Muutke küpsiste eelistusi

Sybase SQL Server Performance and Tuning Guide [Multiple-component retail product]

  • Formaat: Multiple-component retail product, 500 pages, kõrgus: 240 mm, Illustrations, Contains 1 Paperback / softback and 1 CD-ROM
  • Ilmumisaeg: 31-Dec-1996
  • Kirjastus: International Thomson Computer Press
  • ISBN-10: 1850328838
  • ISBN-13: 9781850328834
Teised raamatud teemal:
  • Multiple-component retail product
  • Hind: 64,45 €*
  • * saadame teile pakkumise kasutatud raamatule, mille hind võib erineda kodulehel olevast hinnast
  • See raamat on trükist otsas, kuid me saadame teile pakkumise kasutatud raamatule.
  • Kogus:
  • Lisa ostukorvi
  • Tasuta tarne
  • Lisa soovinimekirja
Sybase SQL Server Performance and Tuning GuideSuurem pilt
  • Formaat: Multiple-component retail product, 500 pages, kõrgus: 240 mm, Illustrations, Contains 1 Paperback / softback and 1 CD-ROM
  • Ilmumisaeg: 31-Dec-1996
  • Kirjastus: International Thomson Computer Press
  • ISBN-10: 1850328838
  • ISBN-13: 9781850328834
Teised raamatud teemal:
Püsilink: https://www.kriso.ee/db/9781850328834.html
Märksõnad:
Foreword xxvii(2)
Introduction xxix
About This Book xxix(1)
Why the New Edition? xxix(1)
How to Use This Book xxix(3)
Essential
Chapters		 xxx(1)
Road Map xxx(1)
Section A (Chapters 1-5): Basics of Performance Tuning: Essential Tools xxx(1)
Section B (Chapters 6-12): Tuning the Performance of Queries xxxi(1)
Section C (Chapters 13-9): Server and System Tuning xxxi(1)
SQL Server Release Levels xxxii(1)
Focus on SQL Server 11.0 xxxii(1)
Notes for Users of SQL Server 10.0.X and Earlier xxxii(1)
Related Documents xxxiii(1)
Example Database xxxiv(2)
Diagram of the Pubs2 Database xxxv(1)
If You Need Help xxxvi(1)
Acknowledgments xxxvi(1)
About the Author xxxvii
Section A: Basics of Performance Tuning: Essential Tools 3(112)
Chapter
1. Performance Analysis
3(10)
What Is "Good Performance"?
3(1)
Designing for Performance
3(1)
What Is Tuning?
4(5)
Tuning Layers
4(5)
Application Layer
5(1)
Database Layer
6(1)
SQL Server Layer
6(1)
Devices Layer
7(1)
Network Layer
7(1)
Hardware Layer
8(1)
Operating System Layer
8(1)
When to Stop Tuning
9(1)
Steps in Performance Analysis
9(4)
Using sp-sysmon to Monitor Performance
11(2)
Chapter
2. Database Design and Denormalizing for Performance
13(20)
How Design Is Related to Performance
13(2)
Database Design
13(1)
Physical Database Design for SQL Server
14(1)
Normalization
15(5)
Levels of Normalization
16(1)
Benefits of Normalization
16(1)
First Normal Form
17(1)
Second Normal Form
18(1)
Third Normal Form
19(1)
Denormalizing for Performance
20(11)
Risks of Denormalization
21(2)
Disadvantages of Denormalization
22(1)
Performance Advantages of Denormalization
22(1)
Denormalization Decision Tree
23(1)
Denormalization Techniques
23(4)
Adding Redundant Columns
23(1)
Adding Derived Columns
24(1)
Collapsing Tables
25(1)
Duplicating Tables
26(1)
Splitting Tables
27(2)
Horizontal Splitting
27(1)
Vertical Splitting
28(1)
Managing Denormalized Data
29(1)
Using Triggers to Manage Denormalized Data
30(1)
Using Application Logic to Manage Denormalized Data
30(1)
Batch Reconciliation
30(1)
Next Steps: Understanding Data Storage and Indexing
31(2)
Chapter
3. Data Storage
33(24)
Performance and Object Storage
33(1)
Performance Tuning Goal: Reducing Disk Reads
33(1)
Query Processing and Page Reads
34(1)
SQL Server Data Pages
35(4)
Row Density on Data Pages
36(1)
Extents
36(1)
Linked Data Pages
37(1)
Text and Image Pages
38(1)
Additional Page Types
39(3)
Global Allocation Map (GAM) Pages
39(1)
Allocation Pages
40(1)
Object Allocation Map (OAM) Pages
40(1)
Why the Range?
40(1)
Relationships Between Objects, OAM Pages, and Allocation Pages
41(1)
The sysindexes Table and Data Access
42(1)
Heaps of Data: Tables Without Clustered Indexes
43(4)
Select Operations on Heaps
43(1)
Inserting Data into a Heap
44(2)
Deleting Data from a Heap
46(1)
Update Operations on Heaps
46(1)
How SQL Server Performs I/O for Heap Operations
47(1)
Sequential Prefetch, or Large I/O
47(1)
Caches and Object Bindings
48(4)
Heaps, I/O, and Cache Strategies
48(2)
Overview of Cache Strategies
48(2)
Select Operations and Caching
50(1)
Data Modification and Caching
50(2)
Caching and Inserts on Heaps
51(1)
Caching and Update and Delete Operations on Heaps
51(1)
Heaps: Pros and Cons
52(1)
Guidelines for Using Heaps
52(1)
Maintaining Heaps
52(1)
Methods for Maintaining Heaps
53(1)
Reclaiming Space by Creating a Clustered Index
53(1)
Reclaiming Space Using bcp
53(1)
The Transaction Log: A Special Heap Table
53(2)
From Heaps to Indexes
55(2)
Chapter
4. How Indexes Work
57(26)
Performance and Indexes
57(1)
What Are Indexes?
57(1)
Types of Indexes
58(2)
Index Pages
58(2)
Root Level
59(1)
Leaf Level
59(1)
Intermediate Level
59(1)
Clustered Indexes
60(9)
Clustered Indexes and Select Operations
61(2)
Clustered Indexes and Insert Operations
63(1)
Page Splitting on Full Data Pages
63(2)
Exceptions to Page Splitting
65(1)
Page Splitting on Index Pages
65(1)
Performance Impacts of Page Splitting
65(1)
Overflow Pages
66(1)
Clustered Indexes and Delete Operations
66(3)
Deleting the Last Row on a Page
67(2)
Index Page Merges
69(1)
Nonclustered Indexes
69(8)
Leaf Pages Revisited
70(1)
Row IDs and the Offset Table
70(2)
Nonclustered Index Structure
72(1)
Nonclustered Indexes and Select Operations
73(1)
Nonclustered Index Performance
74(1)
Nonclustered Indexes and Insert Operations
75(1)
Nonclustered Indexes and Delete Operations
76(1)
Index Covering
77(2)
Matching Index Scans
77(1)
Nonmatching Index Scans
78(1)
Indexes and Caching
79(3)
Using Separate Caches for Data and Index Pages
81(1)
Index Trips Through the Cache
82(1)
Indexing and the Size of Database Objects
82(1)
Chapter
5. Estimating the Size of Tables and Indexes
83(32)
Tools for Sizing Database Objects
83(3)
Why Should You Care about the Size of Objects?
83(1)
Effects of Data Modifications on Object Sizes
84(1)
OAM Pages and Size Statistics
84(2)
Using sp_spaceused to Display Object Size
86(2)
Advantages of sp_spaceused
87(1)
Disadvantages of sp_spaceused
88(1)
Using dbcc to Display Object Size
88(3)
Advantages of dbcc
90(1)
Disadvantages of dbcc
91(1)
Using sp_estspace to Estimate Object Size
91(2)
Advantages of sp_estspace
92(1)
Disadvantages of sp_estspace
93(1)
Using Formulas to Estimate Object Size
93(18)
Factors That Can Change Storage Size
93(1)
Storage Sizes for Datatypes
94(1)
Calculating the Size of Tables and Clustered Indexes
95(4)
Step 1: Calculate the Data Row Size
95(1)
Step 2: Compute the Number of Data Pages
96(1)
Step 3: Compute the Size of Clustered Index Rows
96(1)
Step 4: Compute the Number of Clustered Index Pages
97(1)
Step 5: Compute the Total Number of Index Pages
98(1)
Step 6: Calculate Allocation Overhead and Total Pages
98(1)
Example: Calculating the Size of a 9,000,000-Row Table
99(2)
Calculating the Data Row Size (Step 1)
99(1)
Calculating the Number of Data Pages (Step 2)
100(1)
Calculating the Clustered Index Row Size (Step 3)
100(1)
Calculating the Number of Clustered Index Pages (Step 4)
100(1)
Calculating the Total Number of Index Pages (Step 5)
100(1)
Calculating the Number of OAM Pages and Total Pages (Step 6)
100(1)
Calculating the Size of Nonclustered Indexes
101(2)
Step 7: Calculate the Size of the Leaf Index Row
101(1)
Step 8: Calculate the Number of Leaf Pages in the Index
102(1)
Step 9: Calculate the Size of the Non-Leaf Rows
102(1)
Step 10: Calculate the Number of Non-Leaf Pages
102(1)
Step 11: Calculate the Total Number of Non-Leaf Index Pages
103(1)
Step 12: Calculate Allocation Overhead and Total Pages
103(1)
Example: Calculating the Size of a Nonclustered Index
103(3)
Calculate the Size of the Leaf Index Row (Step 7)
104(1)
Calculate the Number of Leaf Pages (Step 8)
105(1)
Calculate the Size of the Non-Leaf Rows (Step 9)
105(1)
Calculate the Number of Non-Leaf Pages (Step 10)
105(1)
Totals (Step 11)
106(1)
OAM Pages Needed (Step 12)
106(1)
Total Pages Needed
106(1)
Other Factors Affecting Object Size
106(3)
Effects of Setting fillfactor to 100 Percent
107(1)
Other fillfactor Values
107(1)
Distribution Pages
107(1)
Using Average Sizes for Variable Fields
107(2)
Very Small Rows
109(1)
max_rows_per_page Value
109(1)
text and image Data Pages
109(1)
Advantages of Using Formulas to Estimate Object Size
110(1)
Disadvantages of Using Formulas to Estimate Object Size
110(1)
Moving Beyond the Basics
111(4)
Section B: Turning the Performance of Queries 115(240)
Chapter
6. Indexing for Performance
115(48)
How Indexes Can Affect Performance
115(1)
Symptoms of Poor Indexing
115(4)
Detecting Indexing Problems
116(3)
Lack of Indexes Is Causing Table Scans
116(1)
Index Is Not Selective Enough
117(1)
Index Does Not Support Range Queries
117(1)
Too Many Indexes Slow Data Modification
117(1)
Index Entries Are Too Large
117(2)
Index Limits and Requirements
119(1)
Tools for Query Analysis and Tuning
119(3)
Monitoring the Effects of Index Tuning
122(1)
Indexes and I/O Statistics
122(5)
Scan Count
123(2)
Queries Reporting Scan Count of 1
123(1)
Queries Reporting Scan Count Greater Than 1
123(1)
Queries Reporting Scan Count of 0
124(1)
Reads and Writes
125(2)
Logical Reads, Physical Reads, and 2K I/O
126(1)
Physical Reads and Large I/O
126(1)
Reads and Writes on Worktables
126(1)
Effects of Caching on Writes
127(1)
Effects of Caching on Reads
127(1)
Estimating I/O
127(8)
Table Scans
128(2)
Evaluating the Cost of a Table Scan
129(1)
Evaluating the Cost of Index Access
130(2)
Evaluating the Cost of a Point Query
130(1)
Evaluating the Cost of a Range Query
130(2)
Range Queries with Covering Nonclustered Indexes
132(2)
Range Queries with Noncovering Nonclustered Indexes
134(1)
Indexes and Sorts
135(3)
Sorts and Clustered Indexes
136(1)
Sorts and Nonclustered Indexes
137(1)
Sorts When the Index Covers the Query
137(1)
Choosing Indexes
138(8)
Index Keys and Logical Keys
139(1)
Guidelines for Clustered Indexes
139(1)
Choosing Clustered Indexes
139(1)
Candidates for Nonclustered Indexes
140(1)
Other Indexing Guidelines
140(2)
Choosing Nonclustered Indexes
142(1)
Performance Price for Data Modification
142(1)
Choosing Composite Indexes
142(2)
User Perceptions and Covered Queries
143(1)
The Importance of Order in Composite Indexes
144(1)
Advantages of Composite Indexes
145(1)
Disadvantages of Composite Indexes
145(1)
Key Size and Index Size
145(1)
Techniques for Choosing Indexes
146(3)
Examining a Single Query
146(1)
Examining Two Queries with Different Indexing Requirements
147(2)
Index Statistics
149(3)
The Distribution Table
149(2)
The Density Table
151(1)
How the Optimizer Uses the Statistics
152(1)
How the Optimizer Uses the Distribution Table
153(1)
How the Optimizer Uses the Density Table
153(1)
Index Maintenance
153(3)
Monitoring Index Usage Over Time
154(1)
Dropping Indexes That Hurt Performance
154(1)
Index Statistics Maintenance
154(1)
Rebuilding Indexes
155(1)
Speeding Index Creation with sorted data
156(1)
Displaying Information About Indexes
156(1)
Tips and Tricks for Indexes
157(1)
Creating Artificial Columns
158(1)
Keeping Index Entries Short and Avoiding Overhead
158(1)
Dropping and Rebuilding Indexes
158(1)
Choosing Fillfactors for Indexes
158(3)
Disadvantages of Using fillfactor
159(1)
Advantages of Using fillfactor
160(1)
Using sp_sysmon to Observe the Effects of Changing fillfactor
160(1)
Armed to Face the Optimizer
161(2)
Chapter
7. The SQL Server Query Optimizer
163(44)
What Is Query Optimization?
163(1)
SQL Server's Cost-Based Optimizer
163(2)
Steps in Query Processing
164(1)
Optimization Problems and their Roots
165(2)
Working with the Optimizer
165(1)
How Is "Fast" Determined?
166(1)
Query Optimization and Plans
166(1)
Diagnostic Tools for Query Optimization
167(3)
Using showplan and noexec Together
169(1)
noexec and statistics io
169(1)
Using set statistics time
169(1)
Optimizer Strategies
170(1)
Search Arguments and Using Indexes
171(4)
SARGs in where Clauses
172(3)
Indexable Search Argument Syntax
172(1)
Search Argument Equivalents
173(1)
Guidelines for Creating Search Arguments
173(1)
Adding SARGs to Help the Optimizer
174(1)
Optimizing Joins
175(10)
Join Syntax
176(3)
How Joins Are Processed
176(1)
Basic Join Processing
177(1)
Choice of Inner and Outer Tables
177(2)
Saving I/O Using the Reformatting Strategy
179(1)
Index Density and Joins
180(1)
Datatype Mismatches and Joins
181(1)
Join Permutations
182(1)
Joins in Queries with More Than Four Tables
182(3)
Optimization of or clauses and in (values_list)
185(3)
or syntax
185(1)
in (values_list) Converts to or Processing
185(1)
How or Clauses Are Processed
185(2)
or Clauses and Table Scans
186(1)
Multiple Matching Index Scans
186(1)
The OR Strategy
186(1)
Locking and the OR Strategy
187(1)
Optimizing Aggregates
188(1)
Combining max and min Aggregates
188(1)
Optimizing Subqueries
189(6)
Flattening in, any, and exists Subqueries
189(1)
Flattening Expression Subqueries
190(1)
Materializing Subquery Results
191(1)
Noncorrelated Expression Subqueries
191(1)
Quantified Predicate Subqueries Containing Aggregates
191(1)
Short Circuiting
192(2)
Subquery Introduced with an and Clause
193(1)
Subquery Introduced with an or Clause
193(1)
Subquery Results Caching
194(1)
Displaying Subquery Cache Information
194(1)
Optimizing Subqueries
195(1)
Update Operations
195(11)
Direct Updates
195(5)
In-Place Updates
196(1)
Cheap Direct Updates
197(1)
Expensive Direct Updates
198(2)
Deferred Updates
200(1)
Deferred Index Insert
201(2)
Optimizing Updates
203(3)
Indexing and Update Types
204(1)
Choosing Fixed-Length Datatypes for Direct Updates
205(1)
Using max_rows_per_page to Increase Direct Updates
205(1)
Using sp_sysmon While Tuning Updates
206(1)
From Observation to Diagnosis and Treatment
206(1)
Chapter
8. Understanding Query Plans
207(52)
Diagnostic Tools for Query Optimization
207(1)
Using showplan
207(2)
Combining showplan and noexec
208(1)
Echoing Input into Output Files
208(1)
Basic showplan Messages
209(10)
Query Plan Delimiter Message
209(1)
Step Message
210(1)
Query Type Message
210(1)
"FROM TABLE" Message
211(3)
"FROM TABLE" and Referential Integrity
213(1)
"TO TABLE" Message
214(1)
Nested Iteration Message
215(1)
Update Mode Messages
215(4)
Direct Update Mode
215(2)
Deferred Mode
217(1)
"Deferred Index" and "Deferred Varcol" Messages
218(1)
Using sp_sysmon While Tuning Updates
219(1)
showplan Messages for Query Clauses
219(11)
"GROUP BY" Message
220(1)
Selecting into a Worktable
220(1)
Grouped Aggregate Message
221(2)
Grouped Aggregate and group by
222(1)
compute by Message
223(1)
Ungrouped Aggregate Message
224(2)
Ungrouped Aggregates
224(1)
compute Messages
225(1)
Messages for order by and distinct
226(3)
Worktable Message for distinct
226(2)
Worktable Message for order by
228(1)
Sorting Message
229(1)
"GETSORTED" Message
229(1)
showplan Messages Describing Access Methods and Caching
230(12)
Table Scan Message
231(1)
Clustered Index Message
232(1)
Index Name Message
233(1)
Scan Direction Message
233(1)
Positioning Messages
233(1)
Scanning Messages
234(1)
Index Covering Message
234(2)
Keys Message
236(1)
Matching Index Scans Message
236(2)
Dynamic Index Message
238(1)
Conditions for Using a Dynamic Index
238(1)
Reformatting Message
239(3)
Trigger "Log Scan" Message
242(1)
I/O Size Message
242(1)
Cache Strategy Message
242(1)
showplan Messages for Subqueries
242(15)
Output for Flattened or Materialized Subqueries
244(2)
Flattened Queries
244(1)
Materialized Queries
245(1)
Structure of Subquery showplan Output
246(3)
Subquery Execution Message
249(1)
Nesting Level Delimiter Message
249(1)
Subquery Plan Start Delimiter
249(1)
Subquery Plan End Delimiter
249(1)
Type of Subquery
249(1)
Subquery Predicates
250(1)
Internal Subquery Aggregates
250(4)
Grouped or Ungrouped Messages
251(1)
Quantified Predicate Subqueries and the ANY Aggregate
251(1)
Expression Subqueries and the ONCE Aggregate
252(1)
Subqueries with distinct and the ONCE-UNIQUE Aggregate
253(1)
Existence Join Message
254(3)
Subqueries That Perform Existence Tests
255(2)
Going Beyond Standard Remedies
257(2)
Chapter
9. Advanced Optimizing Techniques
259(26)
What Are Advanced Optimizing Techniques?
259(1)
Specifying Optimizer Choices
259(1)
Specifying Table Order in Joins
260(5)
forceplan example
260(4)
Join Sequence and Scans Required for Each Plan
263(1)
Risks of Using forceplan
264(1)
Things to Try Before Using forceplan
264(1)
Increasing the Number of Tables Considered by the Optimizer
265(1)
Specifying an Index for a Query
265(2)
Risks of Specifying Indexes in Queries
267(1)
Things to Try Before Specifying Indexes
267(1)
Specifying I/O Size in a Query
267(3)
Index Type and Prefetching
269(1)
When prefetch Specification Is Not Followed
269(1)
set prefetch on
269(1)
Specifying the Cache Strategy
270(1)
Specifying Cache Strategy in select, delete, and update Statements
271(1)
Controlling Prefetching and Cache Strategies for Database Objects
271(1)
Getting Information on Cache Strategies
272(1)
Tuning with dbcc traceon 302
272(12)
Invoking the dbcc Trace Facility
272(1)
General Tips for Tuning with This Trace Facility
273(1)
Checking for Join Columns and Search Arguments
273(1)
Determine How the Optimizer Estimates I/O Costs
274(1)
Trace Facility Output
274(3)
Identifying the Table
274(1)
Estimating Table Size
275(1)
Identifying the where Clause
275(1)
Output for Range Queries
276(1)
Specified Indexes
276(1)
Calculating Base Cost
277(2)
Costing Indexes
277(1)
Index Statistics Used in dbcc 302
278(1)
Evaluating Statistics for Search Clauses
279(1)
Distribution Page Value Matches
279(1)
Values Between Steps or Out of Range
280(2)
Range Query Messages
280(1)
Search Clauses with Unknown Values
281(1)
Cost Estimates and Selectivity
282(1)
Estimating Selectivity for Search Clauses
283(1)
Estimating Selectivity for Join Clauses
283(1)
From Analysis to Art
284(1)
Chapter
10. Transact-SQL Performance Tips
285(10)
Introduction
285(1)
"Greater Than" Queries
285(1)
not exists Tests
285(1)
Variables vs. Parameters in where Clauses
286(2)
Count vs. Exists
288(1)
or Clauses vs. Unions in Joins
288(1)
Aggregates
289(1)
Joins and Datatypes
290(2)
Null vs. Not Null Character and Binary Columns
290(1)
Forcing the Conversion to the Other Side of the Join
291(1)
Parameters and Datatypes
292(1)
From Query Processing to Reliability
292(3)
Chapter
11. Locking on SQL Server
295(40)
Introduction
295(1)
Overview of Locking
296(1)
Granularity of Locks
297(1)
Types of Locks in SQL Server
297(6)
Page Locks
298(2)
Table Locks
300(1)
Demand Locks
301(1)
Summary of Lock Types
301(1)
Lock Compatibility
302(1)
How Isolation Levels Affect Locking
303(6)
Isolation Level 0
303(2)
Isolation Level 1
305(1)
Isolation Level 2
306(1)
Isolation Level 3
306(2)
SQL Server Default Isolation Level
308(1)
Controlling Isolation Levels
309(4)
Setting Isolation Levels for a Session
309(1)
Using holdlock, noholdlock, or shared
310(1)
Using the at isolation Clause
310(1)
Making Locks More Restrictive
311(1)
Using read committed
312(1)
Making Locks Less Restrictive
312(1)
Using read uncommitted
312(1)
Using shared
312(1)
Examples of Locking and Isolation Levels
313(2)
Cursors and Locking
315(2)
Using the shared Keyword
316(1)
Deadlocks and Concurrency in SQL Server
317(3)
Avoiding Deadlocks
318(2)
Acquire Locks on Objects In the Same Order
319(1)
Delaying Deadlock Checking
319(1)
Locking and Performance of SQL Server
320(7)
Using sp_sysmon While Reducing Lock Contention
321(1)
Reducing Lock Contention
321(5)
Keeping Transactions Short
322(1)
Avoiding "Hot Spots"
323(1)
Decreasing the Number of Rows per Page
324(2)
Additional Locking Guidelines
326(1)
Reporting on Locks and Locking Behavior
327(2)
Getting Information About Blocked Processes with sp_who
327(1)
Viewing Locks with sp_lock
327(1)
Observing Locks with sp_sysmon
328(1)
Configuring Locks and Lock Promotion Thresholds
329(4)
Configuring SQL Server's Lock Limit
329(1)
Setting the Lock Promotion Thresholds
329(4)
Setting Lock Promotion Thresholds Server-Wide
331(1)
Setting the Lock Promotion Threshold for a Table or Database
332(1)
Precedence of Settings
332(1)
Dropping Database and Table Settings
333(1)
Using sp_sysmon While Tuning Lock Promotion Thresholds
333(1)
Using Cursors for Row-by-Row Processing
333(2)
Chapter
12. Cursors and Performance
335(20)
How Cursors Can Affect Performance
335(2)
What Is a Cursor?
335(1)
Set-Oriented vs. Row-Oriented Programming
335(1)
Cursors: A Simple Example
336(1)
Resources Required at Each Stage
337(3)
Memory Use and Execute Cursors
339(1)
Cursor Modes: Read-Only and Update
340(1)
Read-Only vs. Update
340(1)
Index Use and Requirements for Cursors
340(1)
Comparing Performance With and Without Cursors
341(4)
Sample Stored Procedure: Without a Cursor
341(1)
Sample Stored Procedure With a Cursor
342(1)
Cursor vs. Non-Cursor Performance Comparison
343(1)
Cursor vs. Non-Cursor Performance Explanation
344(1)
Locking with Read-Only Cursors
345(1)
Locking with Update Cursors
345(2)
Update Cursors: Experiment Results
346(1)
Isolation Levels and Cursors
347(1)
Optimizing Tips for Cursors
347(8)
Optimize Using Cursors
348(1)
Use union Instead of or Clauses or in Lists
348(1)
Declare the Cursor's Intent
348(1)
Specify Column Names in the for update Clause
348(2)
Declare Shared Locks
350(1)
Use set cursor rows
350(1)
Keep Cursors Open Across Commits and Rollbacks
350(1)
Open Multiple Cursors on a Single Connection
350(5)
Section C: Hardware Tuning Issues and Application Maintenance 355(188)
Chapter
13. Controlling Physical Data Placement
355(22)
How Object Placement Can Improve Performance
355(2)
Multiuser and Multi-CPU Considerations
355(1)
Symptoms of Poor Object Placement
356(1)
Underlying Problems
356(1)
Using sp_sysmon While Changing Data Placement
356(1)
Terminology and Concepts
357(1)
Guidelines for Improving I/O Performance
358(5)
Spreading Data Across Disks to Avoid I/O Contention
358(1)
Isolating Server-Wide I/O from Database I/O
359(1)
Where to Place tempdb
359(1)
Where to Place sybsecurity
360(1)
Keeping Transaction Logs on a Separate Disk
360(1)
Mirroring a Device on a Separate Disk
361(2)
Device Mirroring Performance Issues
362(1)
Why Use Serial Mode?
363(1)
Creating Objects on Segments
363(3)
Why Use Segments?
364(1)
Separating Tables and Indexes
364(1)
Splitting a Large Table Across Devices
365(1)
Moving Text Storage to a Separate Device
365(1)
Improving Insert Performance with Partitions
366(3)
Page Contention for Inserts
366(1)
How Partitions Address Page Contention
367(1)
How Partitions Address I/O Contention
368(1)
Read, Update, and Delete Performance
369(1)
Partitioning and Unpartitioning Tables
369(6)
Selecting Tables to Partition
370(1)
Restrictions
370(1)
Cursors and Partitioned Tables
371(1)
Partitioning Tables
371(2)
alter table Syntax
371(2)
Effects on System Tables
373(1)
Getting Information About Partitions
373(1)
dbcc checktable and dbcc checkdb
374(1)
Unpartitioning Tables
374(1)
Changing the Number of Partitions
375(1)
Partition Configuration Parameters
375(1)
Working with tempdb
375(2)
Chapter
14. tempdb Performance Issues
377(14)
What is tempdb?
377(1)
How Can tempdb Affect Performance?
377(1)
Main Solution Areas for tempdb Performance
377(1)
Types and Use of Temporary Tables
378(1)
Truly Temporary Tables
378(1)
Regular User Tables
378(1)
Worktables
379(1)
Initial Allocation of tempdb
379(1)
Sizing tempdb
380(3)
Information for Sizing tempdb
381(1)
Sizing Formula
381(2)
Example of tempdb Sizing
383(1)
Placing tempdb
383(1)
Dropping the master Device from tempdb Segments
384(1)
Spanning Disks Leads to Poor Performance
385(1)
Binding tempdb to Its Own Cache
385(1)
Commands for Cache Binding
385(1)
Temporary Tables and Locking
386(1)
Minimizing Logging in tempdb
386(1)
Minimizing Logging with select into
386(1)
Minimizing Logging via Shorter Rows
386(1)
Optimizing Temporary Tables
387(2)
Creating Indexes on Temporary Tables
388(1)
Breaking tempdb Uses into Multiple Procedures
388(1)
Creating Nested Procedures with Temporary Tables
389(1)
Configuring Memory Use
389(2)
Chapter
15. Memory Use and Performance
391(38)
How Memory Affects Performance
391(1)
Memory Fundamentals
391(1)
How Much Memory to Configure
392(1)
Caches on SQL Server
393(1)
Procedure Cache
393(4)
Getting Information About the Procedure Cache Size
395(1)
proc buffers
395(1)
proc headers
396(1)
Procedure Cache Sizing
396(1)
Estimating Stored Procedure Size
396(1)
Monitoring Procedure Cache Performance
397(1)
Procedure Cache Errors
397(1)
The Data Cache
397(5)
Default Cache at Installation Time
397(1)
Page Aging in Data Cache
398(1)
Effect of Data Cache on Retrievals
398(1)
Effect of Data Modifications on the Cache
399(1)
Data Cache Performance
400(1)
Testing Data Cache Performance
400(2)
Cache Hit Ratio for a Single Query
401(1)
Cache Hit Ratio Information from sp_sysmon
401(1)
Named Data Caches
402(7)
Named Data Caches and Performance
402(1)
Large I/Os and Performance
403(1)
Types of Queries That Can Benefit From Large I/O
404(2)
Choosing the Right Mix of I/O Sizes for a Cache
405(1)
Cache Replacement Strategies
406(1)
The Optimizer and Cache Choices
407(1)
Commands to Configure Named Data Caches
407(1)
Commands for Tuning Query I/O Strategies and Sizes
408(1)
Named Data Cache Recommendations
408(1)
Sizing Named Caches
409(10)
Cache Configuration Goals
410(1)
Development Versus Production Systems
411(1)
Gather Data, Plan, Then Implement
411(1)
Evaluating Caching Needs
412(1)
Cache Sizing for Special Objects, tempdb and Transaction Logs
413(4)
Determining Cache Sizes for Special Tables or Indexes
413(1)
Examining tempdb's Cache Needs
413(1)
Examining Cache Needs for Transaction Logs
414(1)
Choosing the I/O Size for the Transaction Log
415(1)
Configuring for Large Log I/O Size
416(1)
Further Tuning Tips for Log Caches
416(1)
Basing Data Pool Sizes on Query Plans and I/O
417(1)
Checking I/O Size for Queries
417(1)
Configuring Buffer Wash Size
418(1)
Overhead of Pool Configuration and Binding Objects
419(1)
Pool Configuration Overhead
419(1)
Cache Binding Overhead
419(1)
Maintaining Data Cache Performance for Large I/O
420(3)
Causes for High Large I/O Counts
420(2)
Using sp_sysmon to Check Large I/O Performance
422(1)
Re-Creating Indexes to Eliminate Fragmentation
423(1)
Using Fillfactor for Data Cache Performance
423(1)
Speed of Recovery
423(2)
Tuning the Recovery Interval
424(1)
Housekeeper Task's Effects on Recovery Time
425(1)
Auditing and Performance
425(2)
Sizing the Audit Queue
425(1)
Auditing Performance Guidelines
426(1)
Interconnections among Systems: Analyzing Networks
427(2)
Chapter
16. Networks and Performance
429(14)
How SQL Server Uses the Network
429(1)
Why Study the Network?
429(1)
Potential Network-Based Performance Problems
430(1)
Basic Questions About Networks and Performance
430(1)
Techniques Summary
430(1)
Using sp_sysmon While Changing Network Configuration
431(1)
Changing Network Packet Sizes
431(3)
Large Packet Sizes vs. Default-Size User Connections
432(1)
Number of Packets Is Important
432(1)
Point of Diminishing Returns
432(1)
Client Commands for Larger Packet Sizes
433(1)
Evaluation Tools with SQL Server
434(1)
Evaluation Tools Outside of SQL Server
434(1)
Techniques for Reducing Network Traffic
434(2)
Server-Based Techniques for Reducing Traffic
434(2)
Using Stored Procedures to Reduce Network Traffic
435(1)
Ask for Only the Information You Need
435(1)
Fill Up Packets When Using Cursors
436(1)
Large Transfers
436(1)
Network Overload
436(1)
Impact of Other Server Activities
436(2)
Login Protocol
437(1)
Single User vs. Multiple Users
437(1)
Guidelines for Improving Network Performance
438(4)
Choose the Right Packet Size for the Task
438(2)
Isolate Heavy Network Users
440(1)
Set tcp no delay on TCP Networks
440(1)
Configure Multiple Network Listeners
440(2)
From Networks to CPUs: Analyzing Processing Efficiency
442(1)
Chapter
17. Using CPU Resources Effectively
443(14)
CPU Resources and Performance
443(1)
Task Management on SQL Server
443(3)
Measuring CPU Usage
446(3)
Single CPU Machines
446(1)
Using sp_monitor to See CPU Usage
446(1)
Using sp_sysmon
447(1)
Operating System Commands and CPU Usage
447(1)
Multiple CPU Machines
447(1)
Determining When to Configure Additional Engines
448(1)
Measuring CPU Usage from the Operating System
448(1)
Distributing Network I/O Across All Engines
449(1)
Enabling Engine-to-CPU Affinity
449(2)
How the Housekeeper Task Improves CPU Utilization
451(2)
Side Effects of the Housekeeper Task
451(1)
Configuring the Housekeeper Task
452(1)
Changing the Percentage by Which Writes Can Increase
452(1)
Disabling the Housekeeper Task
452(1)
Allowing the Housekeeper Task to Work Continuously
452(1)
Checking Housekeeper Effectiveness
453(1)
Multiprocessor Application Design Guidelines
453(2)
Multiple Indexes
453(1)
Managing Disks
453(1)
Adjusting the fillfactor for create index Commands
453(1)
Setting max_rows_per_page
454(1)
Transaction Length
454(1)
Temporary Tables
454(1)
Real-World Context: Maintenance Activities and Performance
455(2)
Chapter
18. Maintenance Activities and Performance
457(10)
Maintenance Activities That Affect Performance
457(1)
Creating or Altering a Database
457(1)
Creating Indexes
458(3)
Configuring SQL Server to Speed Sorting
459(1)
Extent I/O Buffers
459(1)
Increasing the Number of Sort Buffers and Sort Pages
460(1)
Dumping the Database After Creating an Index
460(1)
Creating a Clustered Index on Sorted Data
460(1)
Backup and Recovery
461(1)
Local Backups
461(1)
Remote Backups
461(1)
Online Backups
461(1)
Using Thresholds to Prevent Running Out of Log Space
461(1)
Minimizing Recovery Time
462(1)
Recovery Order
462(1)
Bulk Copy
462(2)
Batches and Bulk Copy
463(1)
Slow Bulk Copy
463(1)
Improving Bulk Copy Performance
463(1)
Replacing the Data in a Large Table
463(1)
Adding Large Amounts of Data to a Table
464(1)
Use Partitions and Multiple Copy Processes
464(1)
Impacts on Other Users
464(1)
Database Consistency Checker
464(1)
Regular Monitoring as a Maintenance Activity
465(2)
Chapter
19. Monitoring SQL Server Performance with sp_sysmon
467(76)
Introduction
467(1)
Invoking sp_sysmon
468(1)
Using sp_sysmon to View Performance Information
469(5)
When to Use sp_sysmon
469(1)
When to Use sp_sysmon
469(1)
How to Use the Data
470(1)
Reading sp_sysmon Output
471(2)
Rows
472(1)
Columns
472(1)
Interpreting sp_sysmon Data
473(1)
Per Second and Per Transaction Data
473(1)
Percent of Total and Count Data
473(1)
Per Engine Data
473(1)
Total or Summary Data
474(1)
Sample Interval and Time Reporting
474(1)
Kernel Utilization
474(6)
Sample Output for Kernel Utilization
474(1)
Engine Busy Utilization
475(2)
CPU Yields by Engine
477(1)
Network Checks
477(2)
Non-Blocking
478(1)
Blocking
478(1)
Total Network I/O Checks
478(1)
Average Network I/Os per Check
478(1)
Disk I/O Checks
479(1)
Total Disk I/O Checks
479(1)
Checks Returning I/O
479(1)
Average Disk I/Os Returned
480(1)
Task Management
480(7)
Sample Output for Task Management
480(1)
Connections Opened
481(1)
Task Context Switches by Engine
481(1)
Task Context Switches Due To
482(5)
Voluntary Yields
482(1)
Cache Search Misses
482(1)
System Disk Writes
482(1)
I/O Pacing
483(1)
Logical Lock Contention
483(1)
Address Lock Contention
484(1)
Log Semaphore Contention
484(1)
Group Commit Sleeps
484(1)
Last Log Page Writes
485(1)
Modify Conflicts
485(1)
I/O Device Contention
486(1)
Network Packet Received
486(1)
Network Packet Sent
486(1)
SYSINDEXES Lookup
487(1)
Other Causes
487(1)
Transaction Profile
487(6)
Sample Output for Transaction Profile
487(1)
Transaction Summary
488(2)
Committed Transactions
488(2)
Transaction Detail
490(3)
Inserts
490(2)
Updates
492(1)
Deletes
492(1)
Transaction Management
493(5)
Sample Output for Transaction Management
493(1)
ULC Flushes to Transaction Log
494(1)
By Full ULC
494(1)
By End Transaction
495(1)
By Change of Database
495(1)
By System Log Record and By Other
495(1)
ULC Log Records
495(1)
Maximum ULC Size
496(1)
ULC Semaphore Requests
496(1)
Log Semaphore Requests
497(1)
Transaction Log Writes
498(1)
Transaction Log Allocations
498(1)
Avg # Writes per Log Page
498(1)
Index Management
498(8)
Sample Output for Index Management
498(1)
Nonclustered Maintenance
499(2)
Inserts and Updates Requiring Maintenance to Indexes
500(1)
Deletes Requiring Maintenance
500(1)
RID Updates from Clustered Split
501(1)
Page Splits
501(5)
Reducing Page Splits for Ascending-Key Inserts
502(1)
Default Data Page Splitting
502(1)
Effects of Ascending Inserts
503(1)
Setting Ascending Inserts Mode for a Table
504(1)
Retries and Deadlocks
504(1)
Empty Page Flushes
505(1)
Add Index Level
505(1)
Page Shrinks
506(1)
Lock Management
506(6)
Sample Output for Lock Management
506(2)
Lock Summary
508(1)
Total Lock Requests
508(1)
Average Lock Contention
508(1)
Deadlock Percentage
508(1)
Lock Detail
509(1)
Address Locks
509(1)
Last Page Locks on Heaps
510(1)
Deadlocks by Lock Type
510(1)
Deadlock Detection
511(1)
Deadlock Searches
511(1)
Searches Skipped
511(1)
Average Deadlocks per Search
512(1)
Lock Promotions
512(1)
Data Cache Management
512(15)
Sample Output for Data Cache Management
515(1)
Cache Statistics Summary (All Caches)
516(4)
Cache Search Summary
516(1)
Cache Turnover
517(1)
Cache Strategy Summary
517(1)
Large I/O Usage
518(1)
Large I/O Effectiveness
518(1)
Dirty Read Behavior
519(1)
Cache Management By Cache
520(7)
Spinlock Contention
520(1)
Utilization
520(1)
Cache Search, Hit, and Miss Information
521(1)
Pool Turnover
522(2)
Buffer Wash Behavior
524(1)
Cache Strategy
525(1)
Large I/O Usage
526(1)
Large I/O Detail
527(1)
Dirty Read Behavior
527(1)
Procedure Cache Management
527(1)
Sample Output for Procedure Cache Management
527(1)
Procedure Requests
528(1)
Procedure Reads from Disk
528(1)
Procedure Writes to Disk
528(1)
Procedure Removals
528(1)
Memory Management
528(1)
Sample Output for Memory Management
528(1)
Pages Allocated
529(1)
Pages Released
529(1)
Recovery Management
529(3)
Sample Output for Recovery Management
529(1)
Checkpoints
529(2)
Number of Normal Checkpoints
530(1)
Number of Free Checkpoints
530(1)
Total Checkpoints
531(1)
Average Time per Normal Checkpoint
531(1)
Average Time per Free Checkpoint
531(1)
Increasing the Housekeeper Batch Limit
531(1)
Disk I/O Management
532(5)
Sample Output for Disk I/O Management
532(1)
Maximum Outstanding I/Os
533(1)
I/Os Delayed By
534(1)
Disk I/O Structures
534(1)
Server Configuration Limit
534(1)
Engine Configuration Limit
534(1)
Operating System Limit
535(1)
Requested and Completed Disk I/Os
535(1)
Total Requested Disk I/Os
535(1)
Completed Disk I/Os
535(1)
Device Activity Detail
536(1)
Reads and Writes
536(1)
Total I/Os
536(1)
Device Semaphore Granted and Waited
537(1)
Network I/O Management
537(6)
Sample Output for Network I/O Management
538(1)
Total Requested Network I/Os
539(1)
Network I/Os Delayed
540(1)
Total TDS Packets Received
540(1)
Total Bytes Received
540(1)
Average Bytes Rec'd per Packet
540(1)
Total TDS Packets Sent
540(1)
Total Bytes Sent
540(1)
Average Bytes Sent per Packet
540(1)
Reducing Packet Overhead
540(3)
Glossary 543(30)
Bibliography 573(2)
Performance Tuning 573(1)
Client/Server 573(1)
Sybase Transact-SQL 573(1)
SQL Server 11.0 573(1)
SQL Server 11.0 573(2)
Index 575