There is something about high performance tuning that I find very fascinating. Performance tuning your database server is one of those things that you cannot just pinpoint to a single cause. You must have an overall understanding of how SQL Server internals work to really understand all the areas that you can “tune”, how they all interplay with each other, etc. Without having a grasp on this crucial subject, you will find yourself scratching your head more times than not when learning performance tuning.
Just as it’s important to understand all areas of how SQL Server internals work, it’s equally important to learn it from a reliable, reputable source. It’s easy to fall prey to the endless, unverified posts out on the internet that will do nothing but further add to confusion.
Mr Benjamin Nevarez (bio below) was kind enough to send me a copy of his latest book, High Performance SQL Server. His book is a continuation from his earlier book, “Microsoft SQL Server 2014 Query Tuning & Optimization.”
The goal of this blog is to do a brief review of Mr Benjamin’s latest book and hopefully convince you to add it to your arsenal of “go to” material for SQL Server performance tuning.
High Performance SQL Server by Benjamin Nevarez
The 196 page book is spread over 9 chapters. I will list the chapters below with a brief description of what’s in each:
- How SQL Server Works: This chapter is great as it starts from the beginning. The networking protocols used by SQL Server (TCP/IP, Named Pipes, VIA, etc.), ports, SQLOS, Schedulers/Workers, Query Optimization, Joins (Nested Loops, Hash, Merge), and Parallelism are among the wealth of information in this first chapter.
- Analyzing Wait Statistics: Introduction to the Waits Performance Methodology. Retrieving wait statistics information via the DMVs and Extended Events.
- The Query Store: How the query store can help, using it, performance troubleshooting and live query statistics.
- SQL Server Configuration: This chapter talks about statistics updates, tempdb configuration, MAXDOP settings, IFI (instant file initialization), memory configurations, backup compression default, and a whole list of trace flags.
- tempdb Troublshooting and Configuration: Structure of a page, different types of pages, tempdb latch contention, using multiple data files, what’s new in SQL Server 2016, and monitoring disk space.
- SQL Server In-Memory Technologies: In-Memory OLTP, what’s new in SQL Server 2016 and memory-optimized tables.
- Performance Troubleshooting: Performance counters, DMVs and DMFs, and SQL Trace / Extended Events
- Indexing: How SQL Server uses indexes, where to use them, clustered/non-clustered/filtered indexes, and index maintenance.
- SQL Server Storage: Different storage types, flash based storage, database configuration, database files, fragmentation, VLFs and using tools like Resource Monitor, Diskspd, SQLIOsim and different RAID configurations.
As you can read from the above chapter descriptions, Mr. Nevarez put together a great book that should be part of any database professional’s performance tuning library.
It will definitely be part of mine! :)
About the Author
Benjamin Nevarez (b | t) is a database professional based in Los Angeles, California who specializes in SQL Server query tuning and optimization. He is the author of three books, “High Performance SQL Server”, “SQL Server 2014 Query Tuning & Optimization” and “Inside the SQL Server Query Optimizer” and has also coauthored other books including “SQL Server 2012 Internals.” Benjamin has also been a speaker at many SQL Server conferences and events around the world including the PASS Summit, SQL Server Connections and SQLBits.