Skip to main content
main-content
Top

About this book

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.

Table of Contents

Tables and Indexes

Frontmatter

Chapter 1. Data Storage Internals

Abstract
A SQL Server database is a collection of objects that allow you to store and manipulate data. In theory, SQL Server supports 32,767 databases per instance, although the typical installation usually has only several databases. Obviously, the number of databases SQL Server can handle depends on the load and hardware. It is not unusual to see servers hosting dozens or even hundreds of small databases.
Dmitri Korotkevitch

Chapter 2. Tables and Indexes: Internal Structure and Access Methods

Abstract
SQL Server stores data in tables and indexes. They represent a collection of data pages with rows that belong to a single entity or object.
Dmitri Korotkevitch

Chapter 3. Statistics

Abstract
SQL Server Query Optimizer uses a cost-based model when choosing an execution plan for queries. It estimates the costs of the different execution plans and chooses the one with the lowest cost. Remember, however, that SQL Server does not search for the best execution plan available for the query, as evaluating all possible alternatives is time consuming and expensive in terms of the CPU. The goal of Query Optimizer is finding a good enough execution plan, fast enough.
Dmitri Korotkevitch

Chapter 4. Special Indexing and Storage Features

Abstract
This chapter discusses several storage- and indexing-related features available in SQL Server. It covers indexes with included columns, filtered indexes and statistics, data compression, and sparse columns.
Dmitri Korotkevitch

Chapter 5. SQL Server 2016 Features

Abstract
This chapter provides an overview of several new SQL Server 2016 features, such as temporal tables, stretch databases, row-level security, dynamic data masking, and Always Encrypted.
Dmitri Korotkevitch

Chapter 6. Index Fragmentation

Abstract
Index fragmentation is, perhaps, one of those rare topics that does not entirely belong to the “It Depends” category. Most database professionals agree that fragmentation negatively affects the system. While that is correct, it is still important to understand the downside of index fragmentation and analyze how your system is affected by it.
Dmitri Korotkevitch

Chapter 7. Designing and Tuning the Indexes

Abstract
It is impossible to define an indexing strategy that will work everywhere. Every system is unique and requires its own indexing approach based on workload, business requirements, and quite a few other factors. However, there are several design considerations and guidelines that can be applied in every system.
Dmitri Korotkevitch

Other Things That Matter

Frontmatter

Chapter 8. Constraints

Abstract
It is important to design databases in a way that makes efficient processing and querying of the data possible. That by itself, however, is not enough. We must make sure that the data we get from the database can be trusted. Think about an Order Entry system, for example. We can query the OrderLineItems table to get the information about products we sold, but we cannot trust the results unless we know that the table has no orphaned rows that do not belong to any orders in our system.
Dmitri Korotkevitch

Chapter 9. Triggers

Abstract
Triggers define the code that runs in response to specific events. There are three types of triggers available in SQL Server, as follows.
Dmitri Korotkevitch

Chapter 10. Views

Abstract
Views represent virtual tables defined by underlying queries, and they add another layer of abstraction to the system. Views hide implementation details and can present queries with complex joins and aggregation as a single table. Moreover, views can be used to restrict access to the data and provide just a subset of the rows and columns to users.
Dmitri Korotkevitch

Chapter 11. User-Defined Functions

Abstract
This chapter discusses multi-statement and inline user-defined functions. It analyzes how SQL Server executes multi-statement functions and the performance impact they introduce. After that, this chapter demonstrates a technique that can help address those performance issues by converting multi-statement functions into inline ones.
Dmitri Korotkevitch

Chapter 12. XML and JSON

Abstract
We are living in a world full of information. Businesses are constantly collecting large amounts of data from multiple sources, processing it, and exchanging it with other systems. XML and its popular alternative JSON have become the de-facto standards for information exchange. They work across different platforms and are supported in every development platform that exists today.
Dmitri Korotkevitch

Chapter 13. Temporary Objects and TempDB

Abstract
Temporary objects are an essential part of SQL Server. SQL Server will sometimes create them during query execution to store working tables and intermediate result sets. At other times, they are created by developers.
Dmitri Korotkevitch

Chapter 14. CLR

Abstract
There are several different programming paradigms that exist nowadays. Some languages, such as SQL or XQuery, are declarative. They define what needs to be done without specifying how it needs to be achieved. Other languages, such as C# or Java, are imperative. This model requires specifying an exact, step-by-step control flow of the execution, defining how to achieve the results.
Dmitri Korotkevitch

Chapter 15. CLR Types

Abstract
CLR types make up another area of Common Language Runtime (CLR) integration with SQL Server. User-defined CLR types allow us to expand the standard type library by developing .Net classes and registering them in the database. Standard CLR types, such as Geometry, Geography, and HierarchyId, provide built-in support for spatial and hierarchical data. You will learn about both user-defined and system CLR types in this chapter.
Dmitri Korotkevitch

Chapter 16. Data Partitioning

Abstract
The amount of data stored in relational databases is growing exponentially every year. Customers are collecting more data, and they are processing and retaining it for a longer amount of time. We, as database professionals, are working with databases that have become larger over time.
Dmitri Korotkevitch

Locking, Blocking, and Concurrency

Frontmatter

Chapter 17. Lock Types and Transaction Isolation Levels

Abstract
The concurrency model is, perhaps, the least understood part of SQL Server internals. It can often be confusing; you can encounter hard-to-explain blocking issues in almost every SQL Server installation. Internally, however, the SQL Server concurrency model is based on several well-defined principles, which we are going to discuss in this part of the book.
Dmitri Korotkevitch

Chapter 18. Troubleshooting Blocking Issues

Abstract
Blocking is one of the most common problems encountered in systems. When blocking occurs, multiple queries block each other, which increases the execution time of queries and introduces timeouts. All of this negatively affects the user’s experience with the system.
Dmitri Korotkevitch

Chapter 19. Deadlocks

Abstract
A deadlock is a special blocking case when multiple sessions, or sometimes multiple execution threads within a single session, block each other. When it happens, SQL Server terminates one of the sessions, thus allowing others to continue.
Dmitri Korotkevitch

Chapter 20. Lock Escalation

Abstract
Although row-level locking is great from a concurrency standpoint, it is expensive. In memory, a lock structure uses 64 bytes in 32-bit and 128 bytes in 64-bit operating systems. Keeping information about millions of row- and page-level locks would require SQL Server to allocate gigabytes of RAM to storing them.
Dmitri Korotkevitch

Chapter 21. Optimistic Isolation Levels

Abstract
Optimistic transaction isolation levels were introduced in SQL Server 2005 as a new way to deal with blocking problems and to address data-consistency issues. With optimistic transaction isolation levels, queries read “old” committed versions of rows while accessing the data modified by the other sessions, rather than being blocked by the incompatibility of shared (S) and exclusive (X) locks.
Dmitri Korotkevitch

Chapter 22. Application Locks

Abstract
This chapter discusses another SQL Server locking feature called application locks, which allow you to place locks on an application’s resources as identified by name. One of the most common scenarios where application locks are beneficial is serializing access to T-SQL code in the client application, similar to critical sections and mutexes.
Dmitri Korotkevitch

Chapter 23. Schema Locks

Abstract
SQL Server uses two additional lock types, called schema locks, to prevent table and metadata alteration during query execution. This chapter discusses schema locks in depth, along with other topics such as lock partitioning, which occurs in systems with more than 16 CPUs, and low-priority locks, which were introduced in SQL Server 2014 to reduce blocking during online index rebuilds and partition-switch operations.
Dmitri Korotkevitch

Chapter 24. Designing Transaction Strategies

Abstract
This rather short chapter provides a set of generic guidelines for how you can design transaction strategies and improve concurrency in the systems.
Dmitri Korotkevitch

Query Life Cycle

Frontmatter

Chapter 25. Query Optimization and Execution

Abstract
SQL Server Query Processor is perhaps the least visible and least well-known part of SQL Server. It does not expose a large set of public features, and it allows very limited control in a documented and supported way. It accepts a query as input, compiles and optimizes it to generate the execution plan, and finally executes it.
Dmitri Korotkevitch

Chapter 26. Plan Caching

Abstract
Query optimization is a resource-intensive process that could introduce a significant CPU load on busy servers. SQL Server tries to minimize such load by caching plans in a special part of the process memory called the plan cache.
Dmitri Korotkevitch

Practical Troubleshooting

Frontmatter

Chapter 27. Extended Events

Abstract
Extended Events is a highly scalable performance-monitoring and troubleshooting solution introduced in SQL Server 2008. It is targeted as a replacement for SQL Traces, which was deprecated in SQL Server 2012. Extended Events is lightweight, and it has the flexibility to allow for troubleshooting of some scenarios that were not possible with SQL Traces.
Dmitri Korotkevitch

Chapter 28. System Troubleshooting

Abstract
Things do not always work as expected. System performance can degrade over time when the amount of data and load increases, or sometimes a server can become unresponsive and stop accepting any connections at all. In either case, you need to find and fix such problems quickly while working under pressure and stress.
Dmitri Korotkevitch

Chapter 29. Query Store

Abstract
Query Store is a new SQL Server 2016 component that collects execution plans and runtime statistics for the queries in the system. It helps you to detect suboptimal queries, shows how execution plans evolve over time, and allows you to force specific plans in order to address parameter sniffing–related issues.
Dmitri Korotkevitch

Inside the Transaction Log

Frontmatter

Chapter 30. Transaction Log Internals

Abstract
As you already know, every database in SQL Server has one or more transaction log files in addition to data files. The transaction log stores information about all of the changes made in the database, and it allows SQL Server to recover databases to transactionally consistent states in case of an unexpected shutdown or crash.
Dmitri Korotkevitch

Chapter 31. Backup and Restore

Abstract
In the technology world, it is just a matter of time before disaster happens. A database could become corrupted due to a user error, hardware malfunction, or software bug. A disk array could fail, making databases unavailable to users. An engineer could accidentally change the LUN configuration in a SAN array and affect the database it stores. A natural disaster could affect the availability of a datacenter. In any of these instances, it is essential to recover the database and bring the system online with minimal data loss and downtime.
Dmitri Korotkevitch

Chapter 32. High Availability Technologies

Abstract
A high-availability (HA) strategy helps improve the availability of the system due to hardware, software, or network malfunctions. Even though it sounds similar to a backup and disaster-recovery (DR) strategy, it is not the same. A high-availability strategy serves as the first level of defense, making a hardware failure or software crash transparent to users. Disaster recovery, on the other hand, deals with situations where a system needs to be recovered after a disaster that was not prevented by the high-availability strategy in use.
Dmitri Korotkevitch

Columnstore Indexes

Frontmatter

Chapter 33. Column-Based Storage and Batch Mode Execution

Abstract
Columnstore indexes are an Enterprise Edition feature introduced in SQL Server 2012. They are part of the new family of technologies called x Velocity (formerly known as VertiPaq), which optimizes the performance of analytic queries that scan and aggregate large amounts of data.
Dmitri Korotkevitch

Chapter 34. Columnstore Indexes

Abstract
This chapter provides an overview of the different columnstore index types available in various versions of SQL Server. It discusses their internal structure as well as best practices for data loading and maintenance.
Dmitri Korotkevitch

In-Memory OLTP Engine

Frontmatter

Chapter 35. In-Memory OLTP Internals

Abstract
Hekaton was the code name of an In-Memory OLTP Engine introduced in SQL Server 2014. It is an Enterprise Edition feature, and it is available only in the 64-bit version of SQL Server. Hekaton is Greek for one hundred, which was the target performance-improvement goal of the project. Even though this goal has yet to be achieved, it is not uncommon to see a 10X–40X system-throughput increase when In-Memory OLTP is used.
Dmitri Korotkevitch

Chapter 36. Transaction Processing in In-Memory OLTP

Abstract
This chapter discusses transaction processing in In-Memory OLTP. It elucidates which isolation levels are supported by technology, talks about the lifetime of In-Memory OLTP transactions, and explains how In-Memory OLTP addresses concurrency phenomena encountered in the database systems. Finally, this chapter provides an overview of transaction logging in In-Memory OLTP.
Dmitri Korotkevitch

Chapter 37. In-Memory OLTP Programmability

Abstract
This chapter focuses on the programmability aspects of the In-Memory OLTP Engine in SQL Server. It describes the process of native compilation and provides an overview of the natively-compiled modules and T-SQL features that are supported in In-Memory OLTP. Finally, it discusses several questions related to the design of new systems and migration of existing systems to the In-Memory OLTP architecture.
Dmitri Korotkevitch
Additional information