1 von 1 Kunden fanden die folgende Rezension hilfreich
- Veröffentlicht auf Amazon.com
Format: Kindle Edition
I have written a detailed chapter-by-chapter review of this book on www DOT i-programmer DOT info, the first and last parts of this review are given here. For my review of all chapters, search i-programmer DOT info for STIRK together with the book's title.
This book aims to give you the tools and knowledge to get peak performance from your queries, how does it fare?
The book covers query tuning and optimization, aiming to give you the tools and knowledge to get peak performance from your queries and applications. The optimizer creates an execution plan, detailing how the query will be fulfilled, the quality of this plan depends on various factors (e.g. database design, indexes available etc). This book looks at the factors that affect the plan, with a view to improving query performance.
The book is aimed at SQL Server professionals, database developers, DBAs, and data architects. It assumes some knowledge of SQL Server, and a familiarity with the SQL language.
Below is a chapter-by-chapter exploration of the topics covered.
Chapter 1 An Introduction to Query Tuning and Optimization
The chapter opens with the idea that the more you know about how SQL Server works, the more you’re in a better position to understand and fix SQL problems.
It continues with a brief look at the major architectural components: the storage engine (controls getting data, concurrency, and integrity), and the relational engine (aka the query processor, this creates a plan, executes the query and returns the results). This chapter provides an overview on how the query processor works.
The query processing process is broken down and its steps discussed, namely:
*Parsing and binding (parsing ensures SQL syntax is valid, binding is mostly name resolution)
-Generating candidate execution plans (want good-enough plan. often potentially lots of plans, uses rules and heuristics, there is limited optimization time)
-Assessing the cost of each plan (cost of physical operator [CPU, IO etc] together with estimated rows [cardinality] reflects total cost)
*Query execution and plan caching (execution plan stored in plan cache. Cache checked first to see if plan exists, since optimization can be relatively expensive)
The chapter next focuses on execution plans, their components, and how to read them. They are the primary method of interacting with the query processor. The different formats of the plans are discussed, namely: graphical, XML and text – it is noted the latter will be deprecated. Plans often contain warning messages (e.g. NoJoinPredicate), which are useful indications of perhaps a problematic or suboptimal plan. The ability to obtain the plan from a trace or the plan cache is discussed together with the relevant tool usage. Finally, the use of SET STATISTICS TIME and IO are discussed with examples, providing another useful tuning tool.
This chapter provides a very good overview of how the query processor works (parsing, binding, optimization, execution), as well as providing a useful overview of concepts that are used in the remainder of the book.
There are useful diagrams showing the query processing, and compilation and recompilation processes. There’s some useful code for creating an Extended Events (XE) session for recording plan information.
There’s a useful tip about examining the schema associated with the XML plan to discover any new plan elements (e.g. StatementOptmEarlyAbortReason, NonParallelPlanReason). There is good example code to illustrate the concepts discussed, good links to related chapters, and links to website for further information – is it is throughout the book.
This book aims to give you the tools and knowledge to get peak performance from your queries and applications. It differs from other performance related books by concentrating on the factors that influence the production of a good execution plan, and thus improve performance.
The book is generally easy to read, this is quite an achievement since some of the concepts are deep and undocumented. There are good links between the chapters, helpful website links for further information, good diagrams to support the discussions, and helpful example code to support the assertions made. The appendices should prove very useful. I enjoyed the author’s pragmatic approach e.g. use hints as a last resort, and always test recommendations on your own system.
It might be argued that the book is not as diverse as it could be, since it doesn’t cover all aspects of performance e.g. RAID levels, SQLDIAG, wait stats and queues. I think this misses the point, the book concentrates on those factors that influence the quality of the execution plan. Besides, the additional references provided will cover these other areas.
The book is suitable for intermediate to advanced level DBAs/developers, and should take your level of understanding of performance tuning in general, and the optimizer in particular, from level 5 to 8 or 9. Although the book is primarily for SQL Server 2014, it typically indicates in which version of SQL Server a given feature was introduced, so should prove useful in SQL Server 2012 and 2008.
I enjoyed reading this book. If you spend time troubleshooting performance problems, or you want a deeper understanding of the optimizer and the factors that influence it, I can heartily recommend this excellent book.