Reviewer: Ian Stirk 

Author: Benjamin Nevarez
Publisher: Apress

ISBN: 978-1484222706 

Kindle: B01N2KHVAZ

Chapter 5 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, tempdb related features that are new in SQL Server 2016 setup are examined, including instant file initialization, and multiple tempdb data files. Some previous trace flag functionality has now been incorporated into SQL Server 2016 (e.g. trace flag 118).

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 6 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 removed in the current release.

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 latest release 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.

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

Chapter 7 Performance Troubleshooting

Performance problems can be investigated using various tools, this chapter looks at some of the more traditional tools.  

The chapter opens with a look at Performance Counters, these record information about Windows OS, services, applications etc (e.g. CPU usage). The importance of having baseline values is highlighted, allowing you to decide if current values deviate significantly and deserve further investigation. The Performance Counters discussed include: 

• Page Reads/​Sec – indicates time spent reading data

• Page Writes/​Sec -indicates time spent writing data

• Page Life Expectancy - how long data lives in cache

• Buffer Cache Hit Ratio – is data coming from cache or disk

• % Processor Time – CPU usage

• Processor Queue Length – waiting on CPU

• Locks – indicates reduced concurrency 

In most cases the meaning of the counter is discussed together with reasons for high values, and some potential solutions. There’s a helpful mention of the Performance Analysis of Logs (PAL) tool for analysing your results – it can quickly highlight potential areas of concern.

Next, the chapter looks at some useful DMVs, including: 

• sys.​dm_​io_​virtual_​file_​stats – IO and wait information

• sys.​dm_​db_​index_​usage_​stats – how a table/index is used

• sys.​dm_​exec_​query_​stats – records time, IO, CPU etc used by queries

• sys.​dm_​os_​sys_​info – OS information e.g. CPU count, server memory 

In each case the meaning of the salient columns of the DMV are discussed, and its usage in troubleshooting highlighted.

The chapter ends with a miscellany of features: Extended Events (lightweight monitoring/tracing), SQL Trace (deprecated, use Extended Events instead), Data Collector (mechanism for centrally storing monitoring information), and Trace flags (these influence how SQL Server work).

This chapter provides a useful overview of some traditional monitoring and troubleshooting tools. Little or no detail on how to use the tools is given, this is left to the reader to discover. I would have liked to have seen more discussion on combining the various tools (e.g. DMVs and Performance Counters) to corroborate any underlying problems/solutions.

Chapter 8 Indexing

Indexes are the primary means of improving the performance of SQL queries. The chapter opens with a look at how and where indexes can be used, including helpful code examples. There’s a useful section on validating index usage (to ensure your indexes are being used).

Next, we look at index maintenance, an important topic if you want your indexes to perform optimally (else indexes become fragmented and can perform significantly slower).

The chapter then discusses index structures, including heaps (rows have no specific order), clustered indexes (ordered), nonclustered indexes (duplicate data, often improve queries), and filtered indexes (index based on a WHERE condition). Various code examples of index usage are discussed.

There’s a short discussion on identifying missing indexes via the DMVs, and helpful code is supplied to identify these. The chapter ends with a look at the Database Engine Tuning Advisor (DTA), a very useful tool that takes your workload and identifies the optimal indexes for that SQL workload. A useful walkthrough of the DTA is provided.

This chapter provides a useful overview of the importance of indexes, the different types, how to identify missing indexes, and how to use the DTA to optimise your tables/indexes for your workload. There’s a helpful recommendation to not apply indexes blindly, but only after appropriate analysis.

Chapter 9 SQL Server Storage

Traditionally, disk storage has been the slowest component of database systems, this should change in the future as more systems move to using In-Memory technologies. The chapter opens with a look at the various storage types, including: Storage area networks (SANs), Direct attached storage (DAS), and file shares/servers. The growing usage of faster flash-based storage is briefly discussed.

We next look at aspects of database configuration, including: file placement, fragmentation, and compression – and how these can impact performance.  This is followed with a look at some of the common tools for measuring and monitoring disk performance, including: Resource Monitor, SQLIOSim (simulates a workload), and various DMVs.

The chapter ends with a look at the various RAID (redundant array of independent disks) configurations, and how they impact performance (typically SAN admins use RAID 5 as a best practice, but DBAs often prefer RAID 10 – especially for log files).

This chapter provides a useful reminder of the impact the SQL Server storage system can have on the performance of your queries.

Conclusion

The aim of this book is to improve the performance of your SQL Server queries by optimizing your configuration settings, and it mostly succeeds.

The book is generally easy to read, contains useful links for further information, helpful diagrams, inter-chapter references, and useful code snippets. It has useful detail of what’s new in SQL Server 2016, together with some of the more traditional features, from a performance perspective. Although the book focuses primarily on SQL Server 2016, much of it is applicable to earlier versions.

This is not a book for beginners (e.g. it assumes you’re familiar with DMVs), I suspect the more you already know about SQL Server performance, the more you will get out of this book.

Sometimes, when a feature is introduced, the author overburdens the discussion by including many additional facts about the feature, thus interrupting the normal flow – the additional information should have been included in separate boxes away from the main discussion.

Several chapters of the book (e.g. indexes, In-Memory OLTP) do not fit in with the spirit of the book, as it relates to improving performance via configuration settings. Indeed, there are similar chapters in the author’s related Query Tuning & Optimization book. This probably reflections the difficulty in separating the two topics, since there is a natural overlap. Perhaps a single book (albeit quite large) would have been better. You will need both books to gain a comprehensive understanding of how to improve the performance of your SQL Server queries.

Overall, a useful account of the various factors, largely configuration based, that can affect the performance of your SQL Server.