|Professional SQL Server 2012 Internals and Troubleshooting|
Page 3 of 3
Author: Christian Bolton, Rob Farley et al.
Chapter 11 Consolidating Data Capture with SQLDiag
SQLDiag is a free tool that collects and centralises data from PerfMon, SQL traces, and other outputs (e.g. VBScript, T-SQL, DOS, custom script outputs). It can be run as a console app, windows service or in snapshot mode. The data collected can be analysed by the SQL Nexus tool (the subject of the next chapter). As an example of the type of data collected, when run in snapshot mode the following are collected: default trace, msinfo32 output, all errorlogs, sp_configure, sp_who, sp_locks, sp_helpdb, sys.dm_exec_sessions, sys.dm_os_wait_stats, memory, PerfMon counters, requests and input buffers, and schedulers. There is a configuration tool for editing the XML that underlies the SQLDiag tool. Some useful examples and sample code is provided.
This is a very interesting chapter, it contains many of the scripts I currently use, but in a more consistent and centralised manner. That said, I don’t know anyone that uses this tool. (5/5)
Chapter 12 Bringing it all together with SQL Nexus
SQLDiag imports results into a database, which can be analysed using various reports in SQL Nexus. SQL Nexus is a free Microsoft tool available on CodePlex. The tool can analyse gigabytes of data in a relatively short time, highlighting problem areas quickly. Also included in this chapter is an overview of the OStress tool, a great utility for stress testing (it can run SQL or a stored procedure multiple times concurrently). Note the chapter is based on the 2008 version of SQL Nexus since the 2012 version was not available at the time of writing.
This is another interesting chapter, taking analysis of metrics to the next level. It would be more useful if the reports included some commentary to give interpretation of the results (like PAL does for PerfMon counters logs). (4/5)
Chapter 13 Diagnosing SQL Server 2012 Using Extended Events
There is a movement away from using SQL Profiler/Trace, to using extended events. The reason behind this includes:
While this chapter starts as introductory, it becomes quite detailed. There is a walkthrough of creating an extended events session, and examples include monitoring login, page splits, and the number of locks acquired per object. There a link to a very good blog series by Jonathan Kehayias.
This chapter is an excellent introduction to using extended events, with clear explanations and examples. (5/5)
Chapter 14 Enhancing your troubleshooting skills with PowerShell
PowerShell is the preferred tool for scripting on Microsoft servers. It’s an object oriented language that’s built on .NET, allowing full access to COM, WMI, registry, and the .NET framework. An example of its power can been seen in the following snippet, which gets the 10 top processes using the most CPU: Get-Process | Sort-Object cpu -Descending | Select-Object -First 10. It can be used with SQL Server to investigate such things as Index maintenance (rebuild or reorg), disk space usage of backups, and extracting DDL.
I’m not convinced this chapter belongs to this book. The chapter is generally useful and interesting, and PowerShell is undoubtedly powerful, but I’m not sure it is really a core topic in a troubleshooting book. Perhaps a page or two on the topic would have been sufficient. (3/5)
Chapter 15 Delivering a SQL Server Health Check
For me, this book is worth owning just for this one chapter. It provides 51 scripts that give you information about the state of your databases/servers. I regularly run a similar set of scripts on my databases, to highlight problems or potential problems. Included with the scripts is a short commentary about their meaning and what to look out for in the output. At server-level, the scripts include information about:
There are also the followingscipts at database-level:
A tailored version of the scripts for each of 2005, 2008, and 2012 individually would be useful. I suspect the chapter’s author is Glenn Berry whose website does include similar scripts by version. Anotherr minor gripe: whilst there is a script to examine individual queries that are using the most IO, similar scripts based on the most CPU or waiting/blocking, could also have been provided, but they are easy enough to create. (5/5)
Chapter 16 Delivering Manageability and Performance
I found this chapter to be a mixture of topics with varying degrees of detail. It starts by briefly introducing management enhancements found in 2012 i.e. visual timeline for database restores, DTA using plan cache as input, and contained databases, all quite interesting topics. It then proceeds to discuss Policy Based Management (PBM) in detail. Finally, it provides a brief review of some other management tools (System Center Advisor and System Center Operations Manager).
I didn’t find much cohesion within this chapter, also while the chapter is interesting, I’m not sure it fits with the spirit of the book. (3/5)
Chapter 17 Running SQL Server in a Virtual Environment
Virtualization allows better use of expensive hardware resources. In recent times, although servers have become cheaper, we tend to use more of them, hence to movement to use virtual servers to reduce cost. Again, the chapter is interesting in its own right, but I am not sure it belongs in a book that purports to be about troubleshooting. (3/5)
So did the book fulfil my expectations? Yes and no! I certainly learned more about the latest version of SQL Server.
I found all the chapters interesting and well written, however some seemed to have only a tenuous link to the central theme of troubleshooting, for example the chapters on PowerShell and Virtualization probably belong to another book.
Maybe there could have been fewer internals chapters, since often there was little linking the internals chapters to the troubleshooting chapters - just as you don’t need to be a mechanic to drive a car, you don’t need to know too much about internals to fix SQL Server problems.
If you want to increase your knowledge of SQL Server this is an excellent book, if you want to know more about troubleshooting this book is useful. For perhaps better books on troubleshooting I can recommend SQL Server 2012 Query Performance Tuning by Grant Fritchey and Troubleshooting SQL Server by Jonathan Kehayias and Ted Krueger.
|Last Updated ( Tuesday, 02 April 2013 )|