Author: Christian Bolton, Rob Farley et al.
Publisher: Wrox
Pages: 576
ISBN: 978-1118177655
Audience: Troubleshooting DBAs and Developers
Rating: 4.3
Reviewer: Ian Stirk

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:

• Microsoft has stated more clearly extended events will replace trace/profiler

• There is now a GUI for extended events (can also get GUI addin for 2008)

• Low impact (unlike most other profiling tools)

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:

• SQL Server and OS version

• Windows info

• Hardware info (CPUs, memory, start time etc)

• Server manufacturer and model (xp_readerrorlog)

• Processor description (xp_instance_regread)

• SQL Server Services info (name, startup, description, service account etc)

• Error log info (location, config)

• OS cluster info

• Config values (sys.configurations)

• TCP listener info

• SQL Server registry info (enabled network protocols, exe location, Agent location)

• Any memory dump info (if yes, scan error log)

• Databases running on instance (file locations, different? Log? Tempdb?, c drive?!)

• Database properties (recovery model, log reuse wait, log/usage size, auto stats etc)

• I/O stalls by Database file (data/log, same drive? Perfmon avg disk sec/read/write, RAID?)

• Database sizes in buffer pool

• Database taking most CPU

• Top cumulative wait stats (Bob Ward blog entry to decode meaning)

• Signal waits (CPU pressure) – correlate to SOS_SCHEDULER_YIELD

• Login count info (which logins have most open db connections)

• Simple .udl connectively test (good for connectivity of web server)

• Average task counts

• CPU usage history, over last 256 minutes, at 1 min intervals (for SQL Server, Idle, others)

• OS and SQL server memory info (lock pages in memory, OS memory pressure)

• Page Life Expectancy – watch trend, should be in 1000s…

• Memory Grants outstanding (>0 may be memory pressure) and pending (more important!)

• Overall memory usage (memory clerk info)

• Biggest queries that have only been used once, so wasting cache memory

There are also the followingscipts at database-level: 

• Database data/log locations, size, space remaining

• I/O stats per file

• Virtual Log File count (VLF) (less than 250 is good?!)

• Most executed queries (now has total, last, min/min rows)

• Stored procedures by most called or most time (elapsed or worker) or logical/physical reads/writes,

• Top SQL statements by IO

• Possible bad NC indexes (many writes, few reads)

• Missing indexes

• Missing indexes from cached plans

• Buffer usage by table/index (compression?!)

• Tables, rows counts, is compressed

• Last time stats updated

• Fragmentation info

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)

Conclusion

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.