Chapter 4​ tempdb Troubleshooting and Configuration 

In many ways tempdb is the most important database, since it can be used by all the application databases, by SQL Server itself, and is often heavily used. Problems with tempdb can impact the performance of your queries, so implementing tempdb best practices is important.

The chapter opens with a look at a common tempdb problem, DML contention, where modification operations on temporary objects can block each other. A detailed example of this is provided, together with various solutions to reduce the contention (e.g. having multiple data files).

Next, the use of multiple tempdb data files is discussed. Some previous trace flag functionality has now been incorporated since SQL Server 2016 (e.g. trace flag 1118).

Some tempdb related features that were introduced in SQL Server 2016 are examined, before looking at tempdb related features that are new in SQL Server 2019. Most notable is “in-memory optimized tempdb metadata” which should reduce contention and improve throughput considerably – useful example code is provided, using the ostress tool, to show off its impressive performance.

Some common tempdb warnings are discussed, with reference to their cause (typically the optimizer provides incorrect memory estimates), and possible solutions. The chapter ends with a look at monitoring disk space, with useful SQL code provided.

This chapter provides a helpful introduction to the importance of tempdb, its common problems, possible solutions and best practices.

Chapter 5​ Analyzing Wait Statistics 

When a query isn’t using the CPU, it’s typically waiting on some resource (e.g. IO), SQL Server keeps track of the types of waiting (wait types), and investigating them can reveal important insights into how to improve your queries.

The chapter opens with a brief discussion on waiting, including a reference to Tom Davidson’s still useful paper on decoding wait types (available here: https://technet.microsoft.com/en-us/library/cc966413.aspx).

Next, DMVs concerned with real-time waiting are discussed, including the SQL Server 2016 DMV that records wait types for a given session. Useful code is provided to list the most common non-benign waits. Wait information is also recorded in the system_health Extended Event session, and this is briefly discussed.

We now move to the practical part of the chapter, where a given wait type is examined (in this case CXPACKET), and appropriate solutions highlighted to reduce waiting. Next latches and spinlocks (synchronization mechanisms) are examined with reference to contention and waiting.

We then proceed to look briefly at some of the more common wait types, and identifying their underlying causes. Finally, the author acknowledges the importance of in-memory OLTP in significantly reducing blocking waits.

In some sections, the information supplied is too limited (although there are links for more information), indeed a whole book has been written on waits, and you can see my review of it here: Pro SQL Server Wait Statistics. Although the link between waits and performance counters is mentioned, further detail would be helpful for troubleshooting problems.

Chapter 6​ The Query Store 

Query plans inform SQL Server how to physically implement your SQL code. Sometimes, for a variety of reasons (e.g. statistics updated), a new query plan is created for the same query, and the query can run slower. The Query Store allows you to troubleshoot and solve problems relating to changing query plans.

The chapter starts with an overview of the Query Store, which automatically captures the history of queries, execution plans, and runtime statistics. It is possible to force the query processor to use one of the previously faster plans, and thus restore performance.

The usefulness of the Query Store is examined briefly with reference to: Plan Regressions, SQL Server Upgrades, and Application/​Hardware Changes. Next, its architecture is examined before looking at enabling, disabling, and using the Query Store functionality.

There’s a useful section on performance troubleshooting, probably the primary reason for using Query Store. There’s particular emphasis on the “Top Resource Consuming Queries” report. The Query Store can be useful for identifying queries that have not completed (e.g. C# default command timeout is 30 seconds).

Since SQL Server 2017, Query Store can record information about waits stats at the database level (as opposed to the more typical instance level). This can be useful for troubleshooting the underlying causes of waits per database (remember we have a DMV for session-level waits too for even finer granularity).

Finally, we look at Live Query Statistics, this allows you to view a query plan while the query is still executing, allowing real-time troubleshooting of problems. Helpfully, this 2016 functionality can also be used in SQL Server 2014.

This chapter provides a useful overview of the problems that the Query Store and Live Query Statistics can help investigate and solve. There’s a useful reminder that forcing the query plan is typically not a good long-term solution (cf. the use of hints) – since data and schema can change. There’s a useful reminder that SSMS includes an execution plan comparison tool, which should help with troubleshooting plan differences and the impact on performance.

Chapter 7​ SQL Server In-Memory Technologies 

The declining cost of memory, together with improvements in hardware, allowed a change in architecture from disk-based storage to faster in-memory storage. In-Memory tables were introduced, with various limitations (e.g. no foreign keys), in SQL Server 2014, many of these limitations have been progressively removed in the later releases.

The chapter looks at In-Memory’s origin, its structures (e.g. hash indexes), its concurrency model (no locks or latches), and its advantages (typically significantly improved performance). Various migration tools are outlined (e.g. Table Memory Optimizer Advisor), followed by a discussion on natively compiled modules (SQL code compiled to fast C code).

Next, the related feature of columnstore indexes is examined. Here the data is stored in a column-wise fashion (instead of row-wise), which can offer significant space and performance advantages. Again, the initial 2012 product release had various limitations (e.g. read-only), in the later releases the data is updateable. Various helpful code examples are discussed.  

The chapter ends with a look at the various options for operational analytics, discussing the traditional disk-based tables and the advantages of an architecture that uses the latest In-Memory tables (implementing updateable columnstore indexes on In-Memory tables). Interesting solutions using read-only secondary replicas and columnstore filtered indexes are discussed. The idea of replacing a basic data warehouse with in-memory columnstore indexes on OLTP tables looks interesting.

This chapter provides a useful overview of some of the later SQL Server technologies, with the later product releases having progressively less limitations.