|SQL Server Advanced Troubleshooting and Performance Tuning (O'Reilly)|
Page 5 of 5
Chapter 14. Database Schema and Index Analysis
The previous chapters have looked largely at changes outside tables that may improve performance. This chapter now looks deeper at changes to tables, heaps, and indexes that might improve performance.
The chapter opens with a look at database schema analysis. Various catalog views (e.g. sys.tables) can be used to explore the schema, a useful diagram of these is provided together with their relationships. Examining the schema can identify the cause of various database issues including:
In all cases, useful illustrative code is provided to support the discussions and assertions.
Next, there’s a useful section on index analysis. Indexes can help with performance, but they can increase transaction costs, and blocking. Two useful DMVs for analyzing index usage are discussed, namely:
The author discusses his own utility sp_Index_Analysis, this joins various views together, showing: size of index on disk and in buffer pool, index usage stats, index operational stats, stats info and more.
Perhaps this is the place where the author should make reference to the Database Engine Tuning Advisor, which aims to look at your existing data structures, and the existing query workload (e.g the cache plans or Query Store or a Trace), and determines the optimal data structures for that workload.
Reading this chapter in particular, I could see many of the utilities I’ve created myself, and reading the author was like examining the thoughts I’ve had over the years (e.g. Data Type Mismatch utility). This is another very useful chapter, perhaps it should be merged with the other chapter containing index information. I’m not sure if this is the best position for this chapter within the book.
Chapter 15. SQL Server in Virtualized Environments
The use of virtualization is now relatively common, it adds a layer of complexity but provides various advantages (e.g sharing resources). The author acknowledges this chapter is not comprehensive but should provide enough information to talk with the virtualization infrastructure team about concerns.
The chapter opens with a look at the pros and cons of using virtualization. The advantages include: reduced infrastructure costs, simplified maintenance, resize easily, and can give another layer of HA. The disadvantages include: performance overhead (can be 10%+ on larger systems), a more complex system.
Next, there’s a look at configuration, including:
The chapter ends with a look at how to troubleshoot in the VMs environment. Both the guest VM and the host load need to be examined – here it’s important to get the infrastructure team involved. Common problems briefly examined, including: insufficient CPU bandwidth, memory pressure, and IO performance.
This chapter provides a useful introduction to virtualization, it’s importance, advantages and disadvantages. A very good piece of advice is to get the infrastructure team involved.
Like the author, I thought overprovisioned meant something like you provision 110% of available resources, but it seems virtualization experts take overprovision to mean when the amount of resource allocated is 3 times greater than the actual resources!
Chapter 16. SQL Server in the Cloud
The cloud is very popular, businesses realize there are advantages in using the cloud over on-premise SQL Servers. Luckily, the same base SQL Server engine is used on both on-premise and the cloud, so many of the tools and techniques for troubleshooting are familiar.
The chapter opens with a high-level look at the cloud platforms. There’s a useful reminder that there isn’t a cloud, it’s just someone else’s data center. All major cloud vendors can provide routine database maintenance and HA.
The cloud providers offer a simple, cheap lift-and-shift migration approach via VMs, however, the author suggests this initial low cost might be misleading, he suggests later when the system needs significant changes there may be expensive re-architecture costs.
There’s a list of some typical differences between on-premise and cloud, including: reliability (outages will happen), and throttling (and be aware of latency).
There’s a useful section on connectivity errors. I must admit it is quite common to see these on new systems or with new users, so this section gives good advice on common problems and their solutions.
Next, we look at SQL Server in cloud VMs. In essence these are similar to virtualized on-premise SQL Servers but hosted by the cloud provided. The capabilities of SQL Server are the same as on-premise, where you have full control over the environment. These are often used in a lift-and-shift migration. Some differences do exist and are discussed (e.g. cloud storage can handle bursts of IO activity).
Following on, we look at Managed Microsoft Azure SQL Services. This is broken into 2 areas, the Azure Managed Instance and the Azure SQL Database. Azure SQL Database provides a database only, which is patched, updated, backed-up, and has HA, all provided automatically, note you are abstracted from the OS, you cannot see the file system etc. Azure Managed Instance provides a halfway house between Azure SQL VM and Azure SQL Database, you can run SQL Agent, and access the OS.
Approaches to troubleshooting are discussed, and typically involve using the Azure portal, XEs, and/or catalog views and DMVs. There is a facility to automatically add required indexes. There’s a brief look at Amazon SQL Server RDS, and Google Cloud SQL.
This topic can be a big area when just considering the Azure SQL Server offering, add in the offerings from other venders and it can quickly get complex and confusing. Perhaps it may have been better to concentrate on the Microsoft Azure cloud offerings.
Appendix A. Wait Types
The appendix provides a central area to review the waits. Details are provided on the typical cause of the wait, together with some possible solutions, finally a reference to the relevant book chapter is included.
This book aims to improve the performance of your SQL Servers, and certainly succeeds. The book is well written with a good flow between the topics, having useful discussions, diagrams, code, links to other chapters, and web-links for further information. Each chapter ends with a short summary, and a VERY useful and instructive troubleshooting checklist.
The author has written several books on SQL Server performance, all are detailed with excellent content. Here, the author aims to update the content to cover the later versions of SQL Server, while taking a practical problem/solution approach. Although the book covers internals, it’s more concise and practical than other offerings.
The author takes a holistic approach to the problem solving, providing cross-references to other methods where possible. The author is not dogmatic, but rather knows things depend on system circumstances. There are some general rules, but you’re advised not to follow these blindly because there are often exceptions too. As always, it’s best to test things out on your own systems.
The book is aimed at various database professionals, but primarily DBAs and database developers. You might need a few years of SQL Server experience to get the most out of this book.
Although the book is largely concerned with SQL Server 2022, 2019, and the cloud, much is applicable to older versions, from SQL Server 2005 onwards. The book’s companion website contains the book’s code and more (e.g. some code for earlier versions of SQL Server).
The book contains plenty of sage advice and practical code, won from years of investigating and fixing SQL Server performance problems.
This is now my favorite SQL Server performance book. If you have SQL Server performance problems, you need this book.
|Last Updated ( Wednesday, 24 August 2022 )|