My technical skills
My favorites
My picture album
Shortcut keys
My code library
VB resources
SQLServer resources
SQLServer books
Replication FAQ
Scripting resources
ASP resources
Search my site
Sign my guestbook
Contact information
SQL Server Articles New
|
Click here to find out the top 15 SQL Server books purchased by this site's visitors! NEW |
| Want to keep in touch with the latest in SQL Server world? Email vyaskn@hotmail.com with 'subscribe' in the subject line |
| Title: | Microsoft SQL Server 2000 Performance Tuning Technical Reference |
| Authors: | Edward Whalen, Marcilina Garcia, Steve Adrien Deluca, Michael Dean Thompson, Jamie Reding |
| Publisher: | Microsoft Press |
| ISBN: | 0735612706 |
| Pages: | 450 |
I bought the Indian edition of this book while I was in India (works out to about $12 or £8.5 :-)) and just finished reading all the important chapters.
As the title indicates, this book is dedicated to SQL Server performance tuning. Some of the authors come from SQL Server performance related teams at Microsoft and the rest are industry leading experts. This book starts with the basics of performance tuning, SQL Server architecture, hard disk subsystem (including a good discussion on RAID) and goes on to cover the advanced topics like performance tuning, performance monitoring, capacity planning, system sizing of OLTP systems and datawarehouses. It also covers performance tuning of indexes, queries, replication, backup/restore etc. After reading this book, 'performance tuning' seems more like a science, as the authors introduce you to a whole range of formulae for sizing and tuning the database systems.
This book is of great value for SQL Server DBAs who want to pursue performance tuning, let them be junior or senior DBAs. There's so much of good stuff in this book, that a title like "SQL Server performance tuning Bible" would have been more appropriate :-) SQL Server developers may fancy a few chapters from this book too.
Now, let me walk you through all the chapters!
Chapter 1: Performance Tuning, Capacity Planning, and Sizing Overview
Introduces the basic concepts of performance tuning, sizing and capacity planning. Provides you with a logical approach towards troubleshooting performance problems. Explains the basic terminology like sizing, capacity planning.
Chapter 2: SQL Server 2000 Architecture Fundamentals
Briefly discusses the complete architecture of SQL Server (memory management, data storage, lock and thread management, transaction log architecture etc.). Good enough for a junior DBA to get an overall understanding of SQL Server architecture. This chapter looks like a very brief summary of the book "Inside Microsoft SQL Server 2000".
Chapter 3: Understanding the I/O Subsystem and RAID
This is a very useful chapter for DBAs, especially for those who are not very comfortable with the hardware aspects of a server. It explains, how hard disks work internally, different types of RAID configurations (I found a better explanation of RAID 5 parity stuff somewhere else though). Compares different RAID levels in terms of performance, costs involved and fault tolerance. Recommends proper RAID configurations for different scenarios and supports those recommendations with good statistical data.
Chapter 4: System Tuning
Explains how to tune different components of a system, like CPU, disk subsystem, RAM (memory). Tells you which performance monitor counters to look for in a given scenario. Shows you different formulae for calculating optimal number of disks in a RAID array (I did find an error in one of those calculations, but you'll figure it out if you read carefully). Explains why and how to use files and filegroups for improving database I/O performance. Discusses when to alter configuration parameters and when to leave those parameters for SQL Server, for dynamic management.
Chapter 5: New Features and Performance Enhancements
First of all, this chapter looks a little bit out of place. Information about new features of SQL Server 2000 is anyway available on SQL Server website and Books Online. So, I don't see any value addition in repeating that information again. Further, not all new features are covered, and some of the new features listed, are not directly related to performance.
Chapter 6: Monitoring Performance with System Monitor
This chapter appeals equally for both novice and experienced DBAs. First, it teaches you how to use System Monitor (Performance Monitor), how to generate, view and understand performance reports. Then it discusses the most useful performance counters, their meanings, optimal values for those counters and steps that are needed to arrive at those optimal values. There's a good discussion on avoiding deadlocks. This chapter is a must read for serious DBAs, about to enter the world of performance tuning.
Chapter 7: Using SQL Profiler
This chapter covers everything you need to know about the tool Profiler. It also explains how to use Profiler in specific troubleshooting scenarios. There's no information on how to trace using trace system stored procedures though. You might want to skip this chapter, if you are quite comfortable with using Profiler.
Chapter 8: Modeling for Sizing and Capacity Planning
This chapter marks the beginning of in-depth discussions on capacity planning and sizing. It briefly discusses the history of capacity planning, benchmarking standards and modeling principles.
Chapter 9: Sizing the Database and Application Servers
This chapter explains the factors that one must consider to size a database system accurately. These factors include types of transactions being processed (OLTP/DSS), amount of processing time, RAID levels, amount of I/O generated by transactions etc.
Chapter 10: Methods for Sizing a System
This chapter shows you how to size the processor, memory, disk subsystem and explains how to gather the necessary information and calculate the system throughput and response times. It offers all the essential calculations to size a system accurately.
Chapter 11: Capacity Planning
This chapter helps you understand the current resource utilization of systems and estimate/predict the future resource utilization and shows you different ways for gathering the relevant performance data.
Chapter 12: Online Transaction Processing Systems
This chapter explains what a typical OLTP system is and it's characteristics, along with specific performance tuning steps targeted towards OLTP systems, including two-tier and three-tier architectures, concurrency, file placement, backups, indexes etc.
Chapter 13: Data Warehouses
It's very similar to chapter 12, but is geared towards data warehouses. Starts by comparing OLTP and data warehousing systems and goes on to discuss specifics, like OLAP, fact and dimension tables, star and snowflake schemas, measures, storage design issues (MOLAP, ROLAP, HOLAP), cube design issues etc.
Chapter 14: Tuning Replicated Systems
Starts with a quick discussion of different types of replication and explains the characteristics of each type. Shows you how to tune the different types of agents (snapshot, logreader, distribution) using the agent profiles and parameters to get the maximum performance out of the replication architecture.
Chapter 15: High-Performance Backup and Recovery
Discusses different types of SQL Server database recovery models, logged and non-logged operations and their impact on transaction log and database backups, factors affecting backup and recovery performance. Provides formulae, recommendations and tips for maximizing the backup/restore performance. Explains the pros and cons of local and network backups and Storage Area Networks (SAN).
Chapter 16: Using SQL Query Analyzer
All about using the new SQL Server 2000 Query Analyzer (ISQLW), Index Tuning Wizard (ITW) and interpreting the graphical execution plan output. Most of this information can be found in the SQL Server Books Online.
Chapter 17: Tuning SQL Statements and Stored Procedures
Has a good discussion on how SQL statements and stored procedures get executed behind the scenes. Shows you best practices for designing and programming database systems. Also provides information on execution plans and managing transactions. Shows you how to tune joins used in queries. This chapter basically extends the topics from SQL Server Books Online.
Chapter 18: Using and Tuning Indexes
Covers all the basics of SQL Server indexing architecture. Helps you choose the right index type for different queries and shows you how to tune indexes and adjust fillfactor. "Full-Text indexes" are mentioned as one of the index types, which I thought was not right :-) And the information on Full-Text indexing feature is not completely accurate (Looks like this information is referring to SQL Server 7.0, instead of 2000).
Chapter 19: Using Hints in SQL Server
Has a good, concise discussion of different isolation levels, different types of hints (join, table, query, BCP), pros and cons of customizing isolation levels and using hints etc. Most of this information can be found in Books Online though.
Conclusion??? DON'T miss this book, if you are interested in learning the ins and outs of SQL Server performance tuning.
Click here for more information or to buy this book from: Amazon.com or Amazon.co.uk or Amazon.ca