Author: Grant Fritchey
Publisher: Apress
Pages: 612
ISBN: 9781430267430
Print: 1430267437
Kindle:B01JC6P8MC
Audience: DBAs and developers
Rating: 4.8
Reviewer: Ian Stirk

Chapter 23 Memory-Optimized OLTP Tables and Procedures

In-memory tables perform faster than disk-based table because locks and latches aren’t used, and reading from physical disks is much slower than reading from memory.

The chapter opens with a look at in-memory OLTP fundamentals, looking at the reason for its use, and how it is implemented (optimistic locking approach coupled with versioned data changes). Various limitations are discussed (e.g. need more memory). A useful step-by-step example on creating in-memory OLTP tables is provided. Compiling stored procedures to native code results in fewer CPU instructions, which together with them being compiled (as opposed to interpreted SQL) results in faster performance.

There’s a brief look at the various included tools: Memory optimization advisor that suggests which tables might be migrated, and the Native Compilation advisor, which suggests which stored procedures might be migrated.

This chapter provide a useful overview of what in-memory tables and natively compiled stored procedures are, how they work, and how they improve performance. There is some useful template code provided.

I was surprised the chapter didn’t mention the Analyze, Migrate, and Report (AMR) tool. This tool uses a recorded workload to determine which tables and stored procedures are best to migrate to in-memory tables and natively compiled stored procedures. The tools discussed in this chapter seem to be a subset of this, being limited to superficial validation.

Chapter 24 Database Performance Testing

This chapter details how to capture, record, and replay data needed for database performance testing. The chapter starts with how to capture a load for replay i.e. backup database, and run a server-side trace via SQL Profiler. The backup database is restored, any changes applied (e.g. new indexes), and the distributed replay tool used to replay the database load – during this time, another trace is recorded, this trace and the original trace can then be compared to determine if the change has had a positive impact.

Step-by-step instructions and screenshots are provided on how to set up the trace for the distributed replay tool. This trace definition can be saved for later reuse. The various distributed replay tool options are explained.

This chapter provides a good overview of how to capture, record, and replay data need for database performance testing. The step-by-step instructions for setting up the trace for the distributed replay tool are very helpful, including good use of screenshots.

Chapter 25 Database Workload Optimization

This chapter takes many of the topics learned in the previous chapters and applies them to a sample workload, captured via an XE session. The workload is extracted from XEs and stored in a table, and the longest running queries identified. The accumulative effect of queries is examined using the DMV sys.dm_exe_query_stats.

The most costly queries are analysed using techniques given previously in the book, including:  

• Statistics (dbcc show_statistics, stale?)

• Fragmentation (sys.dm_db_index_physical_stats)

• Internal behaviour of query ()

• Exec plan (indexes? Join strategy? Constraints? Functions?)

• Costly plan steps (focus effort)

• Optimizing costliest query (new index? Check impact on workload)

• Fix key lookups (use include) 

Any proposed changes should be tested with the whole workload, to ensure an improvement in one area doesn’t result in decreased workload performance. Lastly, there is always another ‘most costly’ query, this iterative process is then repeated until the performance is deemed ‘good enough’

This chapter shows the steps and processes that could be undertaken to identify and improve a given workload. The use of the AdventureWorks sample database is instructive since it will allow readers to follow along. As expected, there are good links to other chapters. It may have been useful to say that when tables/indexes are rebuilt, their statistics are automatically built with a fullscan. 

Chapter 26 SQL Server Optimization Checklist

This chapter summarises performance best practices in single place. It provides various checklists (together with explanations) including:

Database design 

• Balance over/under normalization

• Use Constraints (e.g. PK/unique. helps optimizer produce better plan)

• Index design best practices (e.g. include, filter, clustered, missing indexes etc)  

Configuration settings  

• Memory configuration options (max server memory)

• Cost threshold for parallelism (35s?)

• Database compression (more CPU but typically faster)  

Database admin  

• Keep the statistics up-to-date.

• Maintain a minimum amount of index defragmentation  

Query design 

• Avoid non-sargable search conditions

• Avoid optimizer hints

• Ensure there are no implicit data type conversions

• Minimize logging overhead

• Adopt best practices for database transactions 

In many ways this chapter summarises the whole book, and probably justifies the cost of the book itself. The recommendations offered are sensible, but as repeated often in this book – please ensure you test them on your own system to know they actually do improve performance. 

Conclusion

This book covers a wide range of performance topics and techniques, with the central aim of improving the performance of your SQL workload. It is generally easy to read, with practical step-by-step walkthroughs, helpful links between chapters, and good use of screenshots. Typically, code is provided to back up the assertions made. The book should take your level of expertise from level 3 to level 8 (based on a 1 to 10 scale).

While this book covers the majority of code-based performance topics, there were times when I wanted more detail. There was no mention of the more advanced performance tools like SQLDIAG or SQL Nexus. Sometimes the structure of the book felt awkward e.g. perfMon is explained in the memory chapter but its example usage is given in a later chapter. Having a separate detailed tools chapter, that is repeatedly referenced, may have been better.

This book is very similar to the 2012 edition of the book; while the screenshots have been updated for 2014, the text is perhaps 80% the same or similar. Although written for SQL Server 2014, much is applicable to 2012 and 2008. If you want a good, wide ranging, SQL code optimization book, I can certainly recommend this book.