About the Technical Reviewer
by Grant Fritchey, Sajal Dam
SQL Server 2008 Query Performance Tuning Distilled
- Copyright
- About the Author
- About the Technical Reviewer
- Acknowledgments
- Introduction
- 1. SQL Query Performance tuning
- 1.1. The Performance-Tuning Process
- 1.2. Performance vs. Price
- 1.3. Performance Baseline
- 1.4. Where to Focus Efforts
- 1.5. SQL Server Performance Killers
- 1.5.1. Poor Indexing
- 1.5.2. Inaccurate Statistics
- 1.5.3. Excessive Blocking and Deadlocks
- 1.5.4. Non-Set-Based Operations
- 1.5.5. Poor Query Design
- 1.5.6. Poor Database Design
- 1.5.7. Excessive Fragmentation
- 1.5.8. Nonreusable Execution Plans
- 1.5.9. Poor Execution Plans
- 1.5.10. Frequent Recompilation of Execution Plans
- 1.5.11. Improper Use of Cursors
- 1.5.12. Improper Configuration of the Database Log
- 1.5.13. Excessive Use or Improper Configuration of tempdb
- 1.6. Summary
- 2. System Performance Analysis
- 2.1. Performance Monitor Tool
- 2.2. Dynamic Management Views
- 2.3. Hardware Resource Bottlenecks
- 2.4. Memory Bottleneck Analysis
- 2.4.1. SQL Server Memory Management
- 2.4.2. Available Bytes
- 2.4.3. Pages/sec and Page Faults/sec Counters
- 2.4.4. Buffer Cache Hit Ratio
- 2.4.5. Page Life Expectancy
- 2.4.6. Checkpoint Pages/sec
- 2.4.7. Lazy writes/sec
- 2.4.8. Memory Grants Pending
- 2.4.9. Target Server Memory (KB) and Total Server Memory (KB)
- 2.5. Memory Bottleneck Resolutions
- 2.6. Disk Bottleneck Analysis
- 2.7. Disk Bottleneck Resolutions
- 2.7.1. Optimizing Application Workload
- 2.7.2. Using a Faster Disk Drive
- 2.7.3. Using a RAID Array
- 2.7.4. Using a SAN System
- 2.7.5. Aligning Disks Properly
- 2.7.6. Using a Battery-Backed Controller Cache
- 2.7.7. Adding System Memory
- 2.7.8. Creating Multiple Files and Filegroups
- 2.7.9. Placing the Table and Index on Separate Disks
- 2.7.10. Saving Log Files to a Separate Physical Disk
- 2.7.11. Partitioning Tables
- 2.8. Processor Bottleneck Analysis
- 2.9. Processor Bottleneck Resolutions
- 2.10. Network Bottleneck Analysis
- 2.11. Network Bottleneck Resolutions
- 2.12. SQL Server Overall Performance
- 2.13. Creating a Baseline
- 2.14. System Behavior Analysis Against Baseline
- 2.15. Summary
- 3. SQL Query Performance Analysis
- 4. Index Analysis
- 5. Database Engine Tuning Advisor
- 6. Bookmark Lookup Analysis
- 7. Statistics Analysis
- 8. Fragmentation Analysis
- 9. Execution Plan Cache Analysis
- 9.1. Execution Plan Generation
- 9.2. Execution Plan Caching
- 9.3. Components of the Execution Plan
- 9.4. Aging of the Execution Plan
- 9.5. Analyzing the Execution Plan Cache
- 9.6. Execution Plan Reuse
- 9.7. Query Plan Hash and Query Hash
- 9.8. Execution Plan Cache Recommendations
- 9.8.1. Explicitly Parameterize Variable Parts of a Query
- 9.8.2. Create Stored Procedures to Implement Business Functionality
- 9.8.3. Code with sp_executesql to Avoid Stored Procedure Maintenance
- 9.8.4. Implement the Prepare/Execute Model to Avoid Resending a Query String
- 9.8.5. Avoid Ad Hoc Queries
- 9.8.6. Prefer sp_executesql over EXECUTE for Dynamic Queries
- 9.8.7. Parameterize Variable Parts of Queries with Care
- 9.8.8. Do Not Allow Implicit Resolution of Objects in Queries
- 9.9. Summary
- 10. Stored Procedure Recompilation
- 11. Query Design Analysis
- 11.1. Query Design Recommendations
- 11.2. Operating on Small Result Sets
- 11.3. Using Indexes Effectively
- 11.4. Avoiding Optimizer Hints
- 11.5. Using Domain and Referential Integrity
- 11.6. Avoiding Resource-Intensive Queries
- 11.7. Reducing the Number of Network Round-Trips
- 11.8. Reducing the Transaction Cost
- 11.9. Summary
- 12. Blocking Analysis
- 12.1. Blocking Fundamentals
- 12.2. Understanding Blocking
- 12.3. Database Locks
- 12.4. Isolation Levels
- 12.5. Effect of Indexes on Locking
- 12.6. Capturing Blocking Information
- 12.7. Blocking Resolutions
- 12.8. Recommendations to Reduce Blocking
- 12.9. Automation to Detect and Collect Blocking Information
- 12.10. Summary
- 13. Deadlock Analysis
- 14. Cursor Cost Analysis
- 15. Database Workload Optimization
- 15.1. Workload Optimization Fundamentals
- 15.2. Workload Optimization Steps
- 15.3. Capturing the Workload
- 15.4. Analyzing the Workload
- 15.5. Identifying the Costliest Query
- 15.6. Determining the Baseline Resource Use of the Costliest Query
- 15.7. Analyzing and Optimizing External Factors
- 15.8. Analyzing the Internal Behavior of the Costliest Query
- 15.9. Optimizing the Costliest Query
- 15.10. Analyzing the Effect on Database Workload
- 15.11. Iterating Through Optimization Phases
- 15.12. Summary
- 16. SQL Server Optimization Checklist
- 16.1. Database Design
- 16.1.1. Balancing Under- and Overnormalization
- 16.1.2. Benefiting from Entity-Integrity Constraints
- 16.1.3. Benefiting from Domain and Referential Integrity Constraints
- 16.1.4. Adopting Index-Design Best Practices
- 16.1.5. Avoiding the Use of the sp_ Prefix for Stored Procedure Names
- 16.1.6. Minimizing the Use of Triggers
- 16.2. Query Design
- 16.2.1. Use the Command SET NOCOUNT ON
- 16.2.2. Explicitly Define the Owner of an Object
- 16.2.3. Avoid Nonsargable Search Conditions
- 16.2.4. Avoid Arithmetic Operators on the WHERE Clause Column
- 16.2.5. Avoid Optimizer Hints
- 16.2.6. Stay Away from Nesting Views
- 16.2.7. Ensure No Implicit Data Type Conversions
- 16.2.8. Minimize Logging Overhead
- 16.2.9. Adopt Best Practices for Reusing Execution Plans
- 16.2.10. Adopt Best Practices for Database Transactions
- 16.2.11. Eliminate or Reduce the Overhead of Database Cursors
- 16.3. Configuration Settings
- 16.3.1. Affinity Mask
- 16.3.2. Memory Configuration Options
- 16.3.3. Cost Threshold for Parallelism
- 16.3.4. Max Degree of Parallelism
- 16.3.5. Optimize for Ad Hoc Workloads
- 16.3.6. Query Governor Cost Limit
- 16.3.7. Fill Factor (%)
- 16.3.8. Blocked Process Threshold
- 16.3.9. Database File Layout
- 16.3.10. Database Compression
- 16.4. Database Administration
- 16.5. Database Backup
- 16.6. Summary
- 16.1. Database Design
JOSEPH SACK works for Microsoft as a dedicated support engineer on the Premier Field Engineering team. Since 1997, he has been developing and supporting SQL Server environments for clients in the financial services, IT consulting, manufacturing, retail, and real estate industries. He is the author of SQL Server 2008 Transact-SQL Recipes (Apress, 2008), SQL Server 2005 T-SQL Recipes (Apress, 2005), and SQL Server 2000 Fast Answers for DBAs and Developers (Glasshaus, 2003).
-
No Comment
..................Content has been hidden....................
You can't read the all page of ebook, please click here login for view all page.