Pro SQL Server Internals

Front Matter....Pages i-xxxi Front Matter....Pages 1-1 Data Storage Internals....Pages 3-29 Tables and Indexes: Internal Structure and Access Methods....Pages 31-53 Statistics....Pages 55-80 Special Indexing and Storage Features....Pages 81-110 SQL Server 2016 Features....Pages 111-140 Index Fragmentation....Pages 141-154 Designing and Tuning the Indexes....Pages 155-178 Front Matter....Pages 179-179 Constraints....Pages 181-193 Triggers....Pages 195-212 Views....Pages 213-225 User-Defined Functions....Pages 227-240 XML and JSON....Pages 241-267 Temporary Objects and TempDB....Pages 269-291 CLR....Pages 293-309 CLR Types....Pages 311-334 Data Partitioning....Pages 335-377 Front Matter....Pages 379-379 Lock Types and Transaction Isolation Levels....Pages 381-393 Troubleshooting Blocking Issues....Pages 395-405 Deadlocks....Pages 407-422 Lock Escalation....Pages 423-432 Front Matter....Pages 379-379 Optimistic Isolation Levels....Pages 433-442 Application Locks....Pages 443-446 Schema Locks....Pages 447-456 Designing Transaction Strategies....Pages 457-460 Front Matter....Pages 461-461 Query Optimization and Execution....Pages 463-489 Plan Caching....Pages 491-515 Front Matter....Pages 517-517 Extended Events....Pages 519-544 System Troubleshooting....Pages 545-580 Query Store....Pages 581-596 Front Matter....Pages 597-597 Transaction Log Internals....Pages 599-614 Backup and Restore....Pages 615-636 High Availability Technologies....Pages 637-655 Front Matter....Pages 657-657 Column-Based Storage and Batch Mode Execution....Pages 659-685 Columnstore Indexes....Pages 687-713 Front Matter....Pages 715-715 In-Memory OLTP Internals....Pages 717-751 Transaction Processing in In-Memory OLTP....Pages 753-769 In-Memory OLTP Programmability....Pages 771-791 Back Matter....Pages 793-804 Improve your ability to develop, manage, and troubleshoot SQL Server solutions by learning how different components work “under the hood,” and how they communicate with each other. The detailed knowledge helps in implementing and maintaining high-throughput databases critical to your business and its customers. You’ll learn how to identify the root cause of each problem and understand how different design and implementation decisions affect performance of your systems. New in this second edition is coverage of SQL Server 2016 Internals, including In-Memory OLTP, columnstore enhancements, Operational Analytics support, Query Store, JSON, temporal tables, stretch databases, security features, and other improvements in the new SQL Server version. The knowledge also can be applied to Microsoft Azure SQL Databases that share the same code with SQL Server 2016. __Pro SQL Server Internals__ is a book for developers and database administrators, and it covers multiple SQL Server versions starting with SQL Server 2005 and going all the way up to the recently released SQL Server 2016. The book provides a solid road map for understanding the depth and power of the SQL Server database server and teaches how to get the most from the platform and keep your databases running at the level needed to support your business. The book: • Provides detailed knowledge of new SQL Server 2016 features and enhancements • Includes revamped coverage of columnstore indexes and In-Memory OLTP • Covers indexing and transaction strategies • Shows how various database objects and technologies are implemented internally, and when they should or should not be used • Demonstrates how SQL Server executes queries and works with data and transaction log **What You Will Learn*** Design and develop database solutions with SQL Server. * Troubleshoot design, concurrency, and performance issues. * Choose the right database objects and technologies for the job. * Reduce costs and improve availability and manageability. * Design disaster recovery and high-availability strategies. * Improve performance of OLTP and data warehouse systems through in-memory OLTP and Columnstore indexes. **Who This Book Is For** Developers and database administrators who want to design, develop, and maintain systems in a way that gets the most from SQL Server. This book is an excellent choice for people who prefer to understand and fix the root cause of a problem rather than applying a 'band aid' to it. 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