| SQL Server 2017 Administrator's Guide |
Page 2 of 2
(click on book jacket for more details) Print: 1786462540
Chapter 7 Troubleshooting SQL Server Environment and Internals A significant part of database administration involves solving performance problems. This chapter discusses some underlying system internals, together with tools to monitor, detect, and correct these problems. The chapter opens with a useful overview of SQL Server protocols (i.e. TCP/IP, Shared Memory, and Named Pipes). Next, the chapter looks at the work of the Query Processor, with a useful discussion on the (new) cardinality estimator and how used to give create a more accurate execution plan costing. There’s a helpful section on database-scoped settings that were previously set at the server-level (MAXDOP, parameter sniffing, and Query Optimizer fixes). There’s a useful section on what to look out for when examining the execution plan (e.g. row counts, warning messages). There’s a section on using performance monitor, together with some important performance counters to monitor – however some of the counter names are incorrectly specified (e.g. physical disk section). The chapter next discusses monitoring of memory and storage, and provides some useful SQL code to help with this. “Buffer cache hit ratio” is incorrectly described (higher number hmmm) since it makes no reference to “Buffer cache hit ratio base“. Some counters are given without saying why they might be important (e.g. memory grants pending). The chapter ends with a look at Wait Statistics (wait stats), these record the reason why SQL code is not running on the CPU, and can be useful for examining why a server might be slow. There’s a useful reference to chapter 6 (noticeable because cross-chapter links are rare in this book). Some useful website links are provided, together with some SQL code that shows the most common wait stats. Various terms are used but not defined (e.g. query tree, LOB pages, HW, VM, OPTION (RECOMPILE)). Cardinality is defined in this chapter, but was used in earlier chapter without explanation. The chapter says “There's a considerable difference between the estimated and actual execution plans.”, I would say this is largely untrue, the two are often or mostly the same. Again, there are some minor problems with the use of English (e.g. “The query optimizer is constantly being improved by new updates on the current SQL Server 2017, called only cumulative updates.”, “The displayed plans can be much more complex, and the whole plan revision is important,…”, “…or if your workload is not running with proper indexes and statistics,…”. This chapter is of mixed usefulness, it contains some useful detail, but is marred by slack language etc. Chapter 8 Migration and Upgrade This chapter opens with various reasons why you might want to upgrade your SQL Servers, including using the latest functionality and the end of mainstream support. It helpfully notes that many SQL Server Enterprise features (e.g. in-memory OLTP) are now including in SQL Server 2016 Standard Edition – if you have service pack 1 or higher. Next, the various types of upgrade methods are outlined (i.e. in-place upgrade, side-by-side migration, rolling upgrade), together with their advantages and disadvantages. There’s a helpful section on the use of the Data Migration Assistant to help you highlight any potential problems with your migration. Similarly, the System Configuration Checker is used to check any Windows related blockers. Also included is a useful discussion of the SQL Server Migration Assistant, helping your migration from other databases (e.g. Oracle, MS Access) to various versions of SQL Server. There’s some useful step-by-step walkthroughs for the SQL Server migration process itself, and for upgrading to Reporting Services 2017. I do wonder if this chapter should be an early chapter in the book (perhaps after the installation chapter). After the last few chapters, this chapter provides some welcomed normality – easy to read and structurally ‘correct’. Chapter 9 Automation - Using Tools to Manage and Monitor SQL Server 2017 DBAs on large systems with many databases tend to take advantage of automation to ensure their tasks complete in a timely manner. The major scheduling tool is SQL Server Agent. This chapter opens with a look at SQL Server Agent, describing with the use of helpful step-by-step walkthroughs the setting up of the Agent service, its properties, and database mail. Next, it discusses Maintenance Plans, these are existing templates that cover many of the more common DBA tasks (e.g. update statistics). The importance of a backup strategy is emphasized. The chapter ends with a discussion of the various Agent objects. The use of Operators, Jobs, and Alerts are discussed with walkthroughs. There’s a useful section on monitoring and troubleshooting jobs. There’s a good point about monitoring for a SQL Server error via the severity level rather than the many individual error numbers. Generally, this is a well structured and informative chapter. It contains plenty of walkthroughs, and some useful cross-references to other chapters. Although the use of English in this chapter is adequate to good, there are still some poor areas (e.g. much of the maintenance plans section). Although ‘idle CPU condition’ is mentioned, it doesn’t say what it is used for (e.g. could run defrag when CPU is idle). WMI is mentioned but is not defined. Chapter 10 Always On High Availability Features With systems required to be increasingly available, the importance of High Availability (HA) is growing. This chapter opens with a step-by-step walkthrough of installing Windows Server Failover Cluster (WSFC) which underlies the preferred methods of HA in SQL Server. The walkthrough includes helpful screenshots and discussion of configuration settings. There’s a useful section showing how to manually failover with the Failover Cluster Management tool. Next, there’s another useful walkthrough on configuring AlwaysOn Availability Groups (AOAG), together with the various configuration settings. There’s a very useful point about using secondaries for read-only access (allowing you to take advantage of additional CPUs, memory, IO subsystem etc). There’s also a useful section on monitoring and failover. This is another good chapter, replete with helpful walkthroughs and screenshots. The chapter is mostly easy to read, but almost inevitably poor English usage creeps in, this is from opening page “In this chapter, we'll at these two in-depth…”, and “Each Windows Server version is bringing new features to…” I do wonder if this chapter should be adjacent to the Disaster Recovery chapter, although they are not the same, they do have a conceptual degree of overlap. Chapter 11 In-Memory OLTP - Why and How to Use it In-Memory OLTP, with its tempting performance improvements, is probably the main reason to upgrade from earlier versions of SQL Server. Although introduced in 2014 it had many limitations, most of these have been removed in SQL Server 2016. This chapter opens with a discussion of the underlying In-Memory OLTP architecture, which is transparent to the user/client. The differences between the older disk-based system the In-Memory OLTP system are discussed and then summarised in a useful table. Next the chapter discusses Natively Compiled Stored Procedures (NCSP), and other objects, which can run significantly faster than traditional SQL code. The remaining In-Memory OLTP limitations are discussed (e.g. can’t use XML data type). The new lock and latch free concurrency model (MVCC) is briefly discussed. There’s a useful walkthrough of setting up In-Memory tables, this is followed with a practical section on creating In-Memory OLTP objects (indexes, NCSP). Next, there’s a section on migrating objects to In-Memory OLTP, and the Transaction Performance Analysis Overview report is highlighted (it’s a very useful starting point!). The section ends with a brief look at the reports/DMVs that can be used to monitor In-Memory OLTP (e.g. memory usage). The chapter ends with some practical example scenarios, relating to a water company with 100,000 sensors relaying data every 5 seconds to an In-Memory OLTP application. The examples create various tables, stored procedures, and indexes. Overall, this is a good chapter. There is still an occasional problem with the use of English which can prevent an understanding of what is being discussed (e.g. “…fast data contention…” – what is this?). Also, there is some waffle at the start of the chapter. In the walkthrough, the filegroup used in the screenshot (i.e. FS) does not match that used in the discussion or related SQL code (i.e. INMEM). The different types of In-Memory indexes (hash and nonclustered) are not described (quite important). Chapter 12 Combining SQL Server 2017 with Azure If you look at the IT Jobs Watch website Microsoft Azure is currently the 19th most popular skill in demand, 6 months ago it was the 43rd most popular skill – highlighting the rapid rise of the importance of the cloud to SQL Server. This chapter opens with a brief look at both on-premise and cloud based SQL Server systems, with the advantages of the cloud described (e.g. elastic demand, cheaper). There’s a useful link to creating an Azure trial account, allowing you to following along with the examples in the chapter. Next, the various different technologies that make up Azure are examined, namely:
In each case, details of how to create and use the technology are provided, together with instructive step-by-step walkthroughs and screenshots. The chapter next changes direction, and examines how SQL Server 2017 can be used with various hybrid cloud scenarios. This is useful because sometimes it is not possible or easy to migrate to the deeper cloud Azure offerings. The options discussed are:
In each case, the advantages of each option are outlined, together with walkthroughs and screenshots. This chapter was an interesting read, and should prove useful for those wanting to know more about SQL Server and the cloud (which should be everyone). There is a minor amount of waffle/unqualified statements at start of chapter. With reference to the Azure URL it says “We will visit it very often throughout the next sections and chapters.” – forgetting this is the last chapter in the book. Conclusion I’m not sure how much you can learn from this book. In many ways, you need to be an expert in SQL Server administration, and have a good understanding of English, to understand it fully. The book’s subject areas are largely the correct ones, it does have some useful content, tips, and walkthroughs. However it is let down significantly, in several chapters, by the poor use of English, muddled thinking, and some incorrect assertions. The book clearly needs better editing. Maybe 65% of the book is good (rating 4 out of 5), the other 35% is problematic, hopefully you can use this review to guide you to the good parts… What can be done so the book’s next edition will be an improvement? Firstly, the book needs editing by someone with a better knowledge of English. It also needs a technical editor that can challenge the assertions made. It also needs a gentler introduction to the topics, rather than assume some detailed knowledge already. I’ve reviewed around 100 books over the last 7 years, and this one has been the most frustrating. I'm awarding it a rating of 1 out of 5 on the grounds that, while the most essential task of a DBA is backup and recovery, the treatment of it in this book is terrible. I recommend you do not buy this book in its current format. For recommendations of SQL Server books see Pick of the Shelf - SQL Server in our Programmers' Bookshelf section
|
||||||
| Last Updated ( Tuesday, 24 November 2020 ) |
