Pro SQL server internals /
Explains how different SQL Server components work internally and how they communicate with each other. Presents a large number of examples that will show how various design and implementation decisions affect the behavior and performance of systems. Covers multiple SQL Server versions from SQL Serve...
Clasificación: | Libro Electrónico |
---|---|
Autor principal: | |
Formato: | Electrónico eBook |
Idioma: | Inglés |
Publicado: |
Berkeley, CA :
Apress,
2014.
|
Colección: | Expert's voice in SQL server.
|
Temas: | |
Acceso en línea: | Texto completo (Requiere registro previo con correo institucional) |
Tabla de Contenidos:
- Machine generated contents note: pt. 1 Tables and Indexes
- ch. 1 Data Storage Internals
- Database Files and Filegroups
- Data Pages and Data Rows
- Large Objects Storage
- Row-Overflow Storage
- LOB Storage
- SELECT* and I/O
- Extents and Allocation Map Pages
- Data Modifications
- Much Ado About Data Row Size
- Table Alteration
- Summary
- ch. 2 Tables and Indexes: Internal Structure and Access Methods
- Heap Tables
- Clustered Indexes
- Composite Indexes
- Nonclustered Indexes
- Summary
- ch. 3 Statistics
- Introduction to SQL Server Statistics
- Column-Level Statistics
- Statistics and Execution Plans
- Statistics and Query Memory Grants
- Statistics Maintenance
- SQL Server 2014 Cardinality Estimator
- Comparing Cardinality Estimators: Up-to-Date Statistics
- Comparing Cardinality Estimators: Outdated Statistics
- Comparing Cardinality Estimators: Indexes with Ever-Increasing Key Values
- Comparing Cardinality Estimators: Joins
- Comparing Cardinality Estimators: Multiple Predicates
- Summary
- ch. 4 Special Indexing and Storage Features
- Indexes with Included Columns
- Filtered Indexes
- Filtered Statistics
- Calculated Columns
- Data Compression
- Row Compression
- Page Compression
- Performance Considerations
- Sparse Columns
- Summary
- ch. 5 Index Fragmentation
- Types of Fragmentation
- FILLFACTOR and PAD_INDEX
- Index Maintenance
- Designing an Index Maintenance Strategy
- Patterns that Increase Fragmentation
- Summary
- ch. 6 Designing and tuning the Indexes
- Clustered Index Design Considerations
- Design Guidelines
- Identities, Sequences, and Uniqueidentifiers
- Nonclustered Indexes Design Considerations
- Optimizing and Tuning Indexes
- Detecting Unused and Inefficient Indexes
- Index Consolidation
- Detecting Suboptimal Queries
- Summary
- pt. 2 Other things that matter
- ch. 7 Constraints
- Primary Key Constraints
- Unique Constraints
- Foreign Key Constraints
- Check Constraints
- Wrapping Up
- ch. 8 Triggers
- DML Triggers
- DDL Triggers
- Logon Triggers
- UPDATE() and C0LUMNS_UPDATED() functions
- Nested and Recursive Triggers
- First and Last Triggers
- CONTEXT_INFO
- Summary
- ch. 9 Views
- Regular Views
- Indexed Views
- Partitioned Views
- Updatable Views
- Summary
- ch. 10 User-Defined Functions
- Multi-Statement Functions
- Inline Table-Valued Functions
- Summary
- ch. 11 XML
- To Use or Not to Use XML? That Is the Question!
- XML Data Type
- Working with XML Data
- Value() Method
- Exists() Method
- Query() Method
- Nodes() Method
- Modify() Method
- OPENXML
- FOR XML
- Summary
- ch. 12 Temporary Tables
- Temporary Tables
- Table Variables
- User-Defined Table Types and Table-Valued Parameters
- Regular Tables in tempdb
- Optimizing tempdb Performance
- Summary
- ch. 13 CLR
- CLR Integration Overview
- Security Considerations
- Performance Considerations
- Summary
- ch. 14 CLR Types
- User-Defined CLR Types
- Spatial Data Types
- Hierarchyld
- Summary
- ch. 15 Data Partitioning
- Reasons to Partition Data
- When to Partition?
- Data Partitioning Techniques
- Partitioned Tables
- Partitioned Views
- Comparing Partitioned Tables and Partitioned Views
- Using Partitioned Tables and Views Together
- Tiered Storage
- Moving Non-Partitioned Tables Between Filegroups
- Moving Partitions Between Filegroups
- Moving Data Files Between Disk Arrays
- Tiered Storage in Action
- Tiered Storage and High Availability Technologies
- Implementing Sliding Window Scenario and Data Purge
- Potential Issues
- Summary
- ch. 16 System Design Considerations
- General System Architecture
- Data Access Layer Design
- Connection Pooling
- Working with Database Tables Directly
- Database Views
- Stored Procedures
- Code Generators and ORM Frameworks
- Analyzing Microsoft Entity Framework 6
- Connections and Transactions
- Executing Stored Procedures and Queries
- Data Loading
- Parameterization
- IN Lists
- Deletions
- Optimistic Concurrency
- Conclusions
- Summary
- pt. 3 Locking, Blocking and Concurrency
- ch. 17 Lock Types
- ch. 18 Troubleshooting Blocking Issues
- General Troubleshooting Approach
- Troubleshooting Blocking Issues in Real Time
- Collecting Blocking Information for Further Analysis
- Summary
- ch. 19 Deadlocks
- Classic Deadlock
- Deadlock Due to Non-Optimized Queries
- Deadlock Due to Simultaneous Read/Update Statements
- Deadlock Due to Multiple Updates of the Same Row
- Deadlock Troubleshooting
- Reducing the Chance of Deadlocks
- Summary
- ch. 20 Lock Escalation
- Lock Escalation Overview
- Lock Escalation Troubleshooting
- Summary
- ch. 21 Optimistic Isolation Levels
- Row Versioning Overview
- Optimistic Transaction Isolation Levels
- READ COMMITTED SNAPSHOT Isolation Level
- SNAPSHOT Isolation Level
- Version Store Behavior
- Summary
- ch. 22 Application Locks
- ch. 23 Schema Locks
- Schema Modification Locks
- Multiple Sessions and Lock Compatibility
- Lock Partitioning
- Low-Priority Locks (SQL Server 2014)
- Summary
- ch. 24 Designing Transaction Strategies
- pt. 4 Query Life Cycle
- ch. 25 Query Optimization and Execution
- Query Life Cycle
- Query Optimization
- Query Execution
- Operators
- Joins
- Aggregates
- Spools
- Parallelism
- Query and Table Hints
- INDEX Query Hint
- FORCE ORDER Hint
- LOOP, MERGE, and HASH JOIN Hints
- FORCESEEK/FORCESCAN Hints
- NOEXPAND/EXPAND VIEWS Hints
- FAST N Hints
- Summary
- ch. 26 Plan Caching
- Plan Caching Overview
- Parameter Sniffing
- Plan Reuse
- Plan Caching for Ad-Hoc Queries
- Auto-Parameterization
- Plan Guides
- Plan Cache Internals
- Examining Plan Cache
- Summary
- pt. 5 Practical Troubleshooting
- ch. 27 System Troubleshooting
- Looking at the Big Picture
- Hardware and Network
- Operating System Configuration
- SQL Server Configuration
- Database Options
- SQL Server Execution Model
- Wait Statistics Analysis and Troubleshooting
- I/O Subsystem and Non-Optimized Queries
- Memory-Related Wait Types
- High CPU Load
- Parallelism
- Locking and Blocking
- Worker Thread Starvation
- ASYNC_NETWORK_IO Waits
- Allocation Map Contention and Tempdb load
- Wrapping Up
- What to Do When the Server Is Not Responding
- Working with Baseline
- Summary
- ch. 28 Extended Events
- Extended Events Overview
- Extended Events Objects
- Packages
- Events
- Predicates
- Actions
- Types and Maps
- Targets
- Creating Events Sessions
- Working with Event Data
- Working with the ring_buffer Target
- Working with event_file and asynchronous_file_target Targets
- Working with event_counter and synchronous_event_counter Targets
- Working with histogram, synchronous_bucketizer, and asynchronous_bucketizer Targets
- Working with the pair_matching Target
- System_health and AlwaysOn_Health Sessions
- Detecting Expensive Queries
- Summary
- pt. 6 Inside the transaction log
- ch. 29 Transaction Log Internals
- Data Modifications, Logging, and Recovery
- Delayed Durability (SQL Server 2014)
- Virtual Log Files
- Database Recovery Models
- TempDB Logging
- Excessive Transaction Log Growth
- Transaction Log Management
- Summary
- ch. 30 Designing a Backup Strategy
- Database Backup Types
- Backing Up the Database
- Restoring the Database
- Restore to a Point in Time
- Restore with StandBy
- Designing a Backup Strategy
- Partial Database Availability and Piecemeal Restore
- Partial Database Backup
- Backup to Windows Azure
- Managed Backup to Windows Azure
- Summary
- ch.
- 31 Designing a High Availability Strategy
- SQL Server Failover Cluster
- Database Mirroring
- AlwaysOn Availability Groups
- Log Shipping
- Replication
- Designing a High Availability Strategy
- Summary
- pt. 7 In-Memory OLTP Engine
- ch. 32 In-Memory OLTP Internals
- Why Hekaton?
- In-Memory OLTP Engine Architecture and Data Structures
- Memory-Optimized Tables
- High-Availability Technology Support
- Data Row Structure
- Hash Indexes
- Range Indexes
- Statistics on Memory-Optimized Tables
- Garbage Collection
- Transactions and Concurrency
- Transaction Isolation Levels and Data Consistency
- In-Memory OLTP Transaction Isolation Levels
- Cross-Container Transactions
- Data Access, Modifications, and Transaction Lifetime
- Transaction Logging
- Data Durability and Recovery
- Memory Usage Considerations
- Summary
- pt. 8 Columnstore Indexes
- ch. 33 In-Memory OLTP Programmability
- Native Compilation
- Natively-Compiled Stored Procedures
- Optimization of Natively-Compiled Stored Procedures
- Creating Natively-Compiled Stored Procedures
- Supported T-SQL Features
- Execution Statistics
- Interpreted T-SQL and Memory-Optimized Tables
- Memory-Optimized Table Types and Variables
- In-Memory OLTP: Implementation Considerations
- Summary
- ch. 34 Introduction to Columnstore Indexes
- Data Warehouse Systems Overview
- Columnstore Indexes and Batch-Mode Processing Overview
- Column-Based Storage and Batch-Mode Execution
- Columnstore Indexes and Batch-Mode Execution in Action
- Columnstore Index Internals
- Nonclustered Columnstore Indexes
- Data Storage.
- Note continued: Metadata
- Design Considerations and Best Practices for Columnstore Indexes
- Reducing Data Row Size
- Giving SQL Server as Much Information as Possible
- Maintaining Statistics
- Avoiding String Columns in Fact Tables
- Summary
- ch. 35 Clustered Columnstore Indexes
- Internal Structure of Clustered Columnstore Indexes
- Internal Structure
- Data Load
- Delta Store and Delete Bitmap
- Archival Compression
- Clustered Columnstore Index Maintenance
- Excessive Number of Partially Populated Row Groups
- Large Delta Stores
- Large Delete Bitmap
- Index Maintenance Options
- SQL Server 2014 Batch-Mode Execution Enhancements
- Design Considerations
- Summary.