Title Pro SQL server internals / Dmitri Korotkevitch

Published Berkeley, CA : Apress, 2014 New York, NY : Distributed to the Book trade worldwide by Springer ©2014

Click on the following:
Springer Professional and Applied Computing eBooks
Springer eBooks
O'Reilly

Copies

Description 1 online resource (xxv, 750 pages) : illustrations Series The expert's voice in SQL server Expert's voice in SQL server. Contents 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 Summary 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 Server 2005 through SQL Server 2014. The book covers new SQL Server 2014 features including the new Cardinality Estimator. Topics covered include: designing efficient database schema, indexing, and transaction strategies and solutions utilizing SQL Server as the database backend; how various database objects and technologies are implemented internally and when they should or should not be used; how SQL Server executes queries and works with data and transaction logs; troubleshooting and addressing design, concurrency, and performance issues in systems; improving system availability and manageability and reduce implementation costs with data partitioning; designing efficient disaster recovery and high-availability strategies for systems; improving the performance of OLTP and data warehouse systems by utilizing new SQL Server 2014 features, such as the In-Memory OLTP Engine (codename Hekaton) and Clustered Columnstore Indexes. This book is for developers and database administrators Analysis computerwetenschappen computer sciences databasebeheer database management Information and Communication Technology (General) Informatie- en communicatietechnologie (algemeen) Notes Online resource; title from PDF title page (SpringerLink, viewed July 8, 2014) In Springer eBooks SUBJECT SQL server. http://id.loc.gov/authorities/names/n90684343 SQL server. blmlsh SQL server fast Subject Database management. Client/server computing. COMPUTERS -- Programming Languages -- SQL. COMPUTERS -- Client-Server Computing. COMPUTERS -- Databases -- General. Client/server computing Database management Form Electronic book ISBN 9781430259633 1430259639 9781484219645 1484219643

  Permalink