| High Performance SQL Server |
Page 1 of 2 Author: Benjamin Nevarez This book aims to improve the performance of your SQL Server queries by optimizing your configuration settings, how does it fare? Benjamin Nevarez concentrates on improving the performance of your SQL server from a configuration/system perspective, rather than looking at how to improve specific queries. The premise being that if you improve your system settings, your queries should run faster. The book is aimed primarily at database administrators, architects, and system administrators. It assumes you already have a good understanding of SQL Server. In many ways it complements the author’s query performance book “SQL Server 2014 Query Tuning & Optimization”, which I previously reviewed. 
The new title is relatively short, with around 200 working pages spread over nine chapters. Don’t be deceived by its size, it contains impressive detail. The book primarily focuses on SQL Server 2016, but much of it is applicable to earlier versions of SQL Server. Below is a chapter-by-chapter exploration of the topics covered.
Chapter 1 How SQL Server Works Understanding how SQL Server works internally, is the key to making configuration changes to improve performance. While the default configuration settings may be acceptable for many systems, to get the most out of SQL Server, certain settings may need changing. The book opens with a look at how a connection is made (using Tabular Data Steam [TDS], and a network protocol). Next, we look at how work is undertaken, using SQLOS (manages operating system resources), schedulers (manage thread scheduling), and workers (running the SQL). Next, the higher-level session, connection, and request Dynamic Management Views [DMVs] are briefly discussed. Aspect of query optimization are then examined, with a look at parsing and binding (syntax and object checking), query optimization (criteria for selecting the ‘best’ query plan), and plan caching (stored to facilitate plan reuse). Some common operators (these perform the actual work, e.g. get a row) are then briefly discussed, including: data access, aggregations, and joins. The chapter then takes a brief look at memory grants, here queries need be allocated memory to run, and sometimes the memory estimation process goes awry, resulting in poor performance. The chapter ends with a brief look at locks and latches, these control object access (and consequently impact throughput/concurrency). This chapter looks at the underlying work SQL Server does from the moment a connection is made, until the query results are returned. This chapter forms the basis for the rest of the book. The chapter is generally easy to read, but it is not a book for beginners (e.g. it assumes you’re familiar with DMVs), the more you already know about SQL Server the easier the book is to understand. It contains useful links for further information, helpful diagrams, inter-chapter references, and useful code snippets. Plenty of detail and tips are given, often in passing. A useful chapter summary is given. These traits apply to the whole of the book. Chapter 2 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 new 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. In some ways, the information supplied is too limited (although there are links for more information), indeed a whole book has been written on waits, you can see my review of it here: Pro SQL Server Wait Statistics The chapter ends with a brief look at blocking (a common cause of waiting), together with an equally brief look at In-Memory OLTP – which uses a new concurrency model that does not use locks and laches, so is potentially significantly faster. This chapter provides a useful introduction to a common technique for examining why queries are not running (i.e. waiting), typically for the server instance as a whole. Although the need to correlate wait information with other techniques (e.g. Performance Counters) is mentioned, no detail is given. Chapter 3 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 execution 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. 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).
Chapter 4 SQL Server Configuration This chapter covers a miscellany of SQL Server settings that can improve the performance of your queries. The areas examined include:
In each case, the configuration setting is described, together with reasons why you might want to change it. This chapter discusses a wide range of configuration settings that can improve performance. The great thing is that optimising these settings can improve the performance of all query running on the server instance/database. Helpfully the author suggests the configuration settings should be based ultimately on the testing of your own query workload (i.e. a query or batch of queries). <ASIN:0071829423> <ASIN:B00N91C9PY> <ASIN:1484211405> <ASIN:B011BALPHO>
|
|||
| Last Updated ( Saturday, 28 November 2020 ) |