Blogcritics The critical lens on today's culture & entertainment
When you are first developing a SQL Server system one of the last things that most people think about is performance. That unfortunately becomes one of the biggest problems that the developer has when the system goes into production. You end up finding out when someone calls and says the system seems to be running slow. It is these kinds of problems that are hard to find!
That is, unless you know how to diagnose the problems systematically. This is the goal of SQL Server 2008 Query Performance Tuning Distilled. This book provides the tools that you will need to find the problems. It does this by showing you how to set up mechanisms for collecting performance data so you can get to the heart of the problem. The book is 600 pages divided into 16 chapters.
Chapter One, "SQL Query Performance Tuning," begins by looking at what performance tuning is. You will look at how to establish a baseline, find bottlenecks, resolving problems, and then how to measure the improvements. Chapter Two, "System Performance Analysis," starts off by monitoring the system upon which SQL Server runs – namely Windows. With Performance Monitor you will learn how to use the different performance counters that are required to create a baseline.
Chapter Three, "SQL Query Performance Analysis," now examines the best methods to look under the hood of SQL Server and see what queries are being run on the system. This chapter provides a detailed look at SQL Profiler and Management Studio tools. Chapter Four, "Index Analysis," next looks at indexes and how they are built. You will see the difference between clustered and non-clustered and which works best with each type of query. The right index on the right column may mean everything to proper performance.
Chapter Five, "Database Engine Tuning Advisor," is a tool that helps identify an optimal set of indexes and statistics for a given workload without requiring an expert understanding of the database schema. Here you will learn how the tool works, how best to use it, and what its limitations are. Chapter Six, "Bookmark Lookup Analysis," is all about key lookup and the major overhead that is associated with non-clustered indexes. It is here that you will learn how to avoid these costs.
Chapter Seven, "Statistics Analysis," now looks at statistics and how the optimizer uses them to make decisions regarding the execution of a query. Here you will see how stats are maintained, stored, and how they affect your query. Chapter Eight, "Fragmentation Analysis," will show you how your indexes fragment over time. In this chapter you will see how to identify when an index is fragmented, what happens to your queries when they fragment, and how to help eliminate index fragmentation.
Chapter Nine, "Execution Plan Cache Analysis," will show you the mechanisms that SQL Server uses to store execution plans. Here you will learn how to identify if plans are being reused, how to look at the cache, as well as introducing you to dynamic management views. Chapter 10, "Stored Procedure Recompilation," addresses a costly effort and to reduce that burden; SQL Server tries to reuse execution plans. Here you will learn to understand what causes recompilation and how to avoid them when possible.
Chapter 11, "Query Design Analysis," will help you avoid performance problems by showing you how to make sure that you are using proper design to make best use of indexes. You will learn how to make your query designs less intense on resources, reduce network overhead, and reduce the transaction cost of a query. Chapter 12, "Blocking Analysis," examines what causes SQL problems as the number of users increase and the volume of data grows. In this chapter you will see what causes blocking, the effect of indexes on locking, and ways to automate blocking detection.
Chapter 13, "Deadlock Analysis," now looks at what happens when deadlocks occur. In this chapter you will see what a deadlock is, how to catch one, and how to resolve the deadlock. Chapter 14, "Cursor Cost Analysis," will show you the fundamental of cursors, the different characteristics of cursors, and how to minimize the cost overhead of cursors.
Chapter 15, "Database Workload Optimization," now takes all the analysis that you have learned thus far and will put it to optimizing the database workload. Here you will find out how to identify costly queries, analyze factors that affect the performance of costly queries, and apply techniques to optimize costly queries. Chapter 16, "SQL Server Optimization Checklist" finishes the book off by providing a performance monitoring checklist to serve as a quick reference in the field. It provides a summary of some of the major tuning activities that can have quick impact on your SQL Server system.
Considering the topic, SQL Server 2008 Query Performance Tuning Distilled is short, sweet, and to the point. It tackles what is a complex topic and does it in a manner that even someone who is relatively new to SQL Server and performance tuning and pick up in a short time.
Most people let SQL Server handle the heavy lifting until problems arise and then don't really understand what is going on in the black box. SQL Server 2008 Query Performance Tuning Distilled helps you to understand just what is going on inside. It is that what makes this book such an invaluable investment. It brings you up to speed slowly at first and then adds on layer by layer all the way to advanced topics.
If you need to get up to speed with performance tuning on your SQL Server then you need to look no further than SQL Server 2008 Query Performance Tuning Distilled. I very highly recommend this book.
