Brent Ozar’s Reading List for MCM

While it is huge list.  But I have fun so far trekking on the road!

The Microsoft Certified Master certification for Microsoft SQL Server has a reading list for attendees. Here, Brent Ozar has grouped the articles together and given his thoughts about each of the MCM articles.

BOL means Books OnLine.

Architecture Training Articles

Infrastructure Design

Infrastructure Planning and Design Whitepaper

Description: “The Infrastructure Planning and Design (IPD) guides are the next version of Windows Server System Reference Architecture. The guides in this series help clarify and streamline design processes for Microsoft infrastructure technologies, with each guide addressing a unique infrastructure technology or scenario.”


SQL Server Replication BOL

Description: “Replication is a set of technologies for copying and distributing data and database objects from one database to another and then synchronizing between databases to maintain consistency. Using replication, you can distribute data to different locations and to remote or mobile users over local and wide area networks, dial-up connections, wireless connections, and the Internet.”

Replication Publishing Model Overview BOL

Description: “Replication uses a publishing industry metaphor to represent the components in a replication topology, which include Publisher, Distributor, Subscribers, publications, articles, and subscriptions. It is helpful to think of Microsoft SQL Server replication in terms of a magazine.”

Types of Replication Overview BOL

Description: “The type of replication you choose for an application depends on many factors, including the physical replication environment, the type and quantity of data to be replicated, and whether the data is updated at the Subscriber. The physical environment includes the number and location of computers involved in replication and whether these computers are clients (workstations, laptops, or handheld devices) or servers.”

Replication Agents Overview BOL

Description: “Replication uses a number of standalone programs, called agents, to carry out the tasks associated with tracking changes and distributing data. By default, replication agents run as jobs scheduled under SQL Server Agent, and SQL Server Agent must be running for the jobs to run. Replication agents can also be run from the command line and by applications that use Replication Management Objects (RMO). Replication agents can be administered from SQL Server Replication Monitor and SQL Server Management Studio.”

Engine Articles

Data File Maintenance

Shrink Blog Category

Articles from Paul Randal’s blog.

Only skip this if: you understand why shrinking a database (either via DBCC SHRINKDB or through turning AUTO_SHRINK on) causes fragmentation inside the database.

Database Snapshots Blog Category

Articles from Paul Randal’s blog.

Only skip this if: you understand that database snapshots are used by DBCC to grab a point-in-time copy of the database, and you understand how database snapshots use NTFS alternate streams and sparse files.

Database Maintenance Blog Category

Articles from Paul Randal’s blog.

Working with TempDB Whitepaper

Description: “This white paper describes how SQL Server 2005 uses tempdb. Many improvements in SQL Server 2005 optimize tempdb usage and make it easier to manage and to troubleshoot. A case study that uses a workload similar to TPC Benchmark H (TPC-H) shows new ways to manage and troubleshoot tempdb resources. This paper also includes items to consider when upgrading to SQL Server 2005 and configuring tempdb.”

Physical Database Storage Design Whitepaper

Description: “This physical storage design guide is written to help database architects and administrators configure Microsoft SQL Server 2005 systems for optimal I/O performance and space utilization. This article also emphasizes the new SQL Server 2005 features that are significant to these discussions. This paper is separated into three sections. The first section focuses on the database file storage design with the main focus on the new SQL Server 2005 features. The second section describes the design considerations of the physical hardware of the server system. This includes topics about system components such as disks, interfaces, buses, and RAID levels, and how each of these hardware components measure up with respect to the design criteria. The third section reviews the different types of workloads and the I/O requirements for various sizes of applications.”

Index Internals and Fragmentation

Fragmentation Blog Category

Articles from Paul Randal’s blog.

Indexes SQLSkills Blog Category

Articles from Paul Randal’s blog.

Online Indexing Operations Whitepaper

Description: “Introduced in SQL Server 2005 Enterprise Edition, the online index feature provides a powerful way to perform maintenance operations such as rebuilding or creating indexes in a production system without sacrificing DML concurrency. This paper provides a detailed discussion of the index process and provides guidelines and best practices for implementing this feature in a production environment.”

Logging, Recovery, and Log File Maintenance

Transaction Log Blog Category

Articles from Paul Randal’s blog.

On-Disk Structures

On-Disk Structures Blog Category

Articles from Paul Randal’s blog.

Filestream Blog Category

Articles from Paul Randal’s blog.

Spatial Indexes Post

Post from Paul Randal’s blog. Description: “One of the cool features of SQL Server 2008 for developers is spatial data support. There have been some great posts recently about using spatial, which is all developer stuff – but what I’m interested in are the implications of spatial support for DBAs, and they are focused on spatial indexes.”

Sparse Columns Blog Category

Articles from Paul Randal’s blog.

Data Compression Blog Category

Articles from Sunil Agarwal’s MSDN blog.

Data Compression Best Practices Whitepaper

SQLCat whitepaper. Description: “The data compression feature in the Microsoft® SQL Server® 2008 database software can help reduce the size of the database as well as improve the performance of I/O intensive workloads. However, extra CPU resources are required on the database server to compress and decompress the data, while data is exchanged with the application. Therefore, it is important to understand the workload characteristics when deciding which tables to compress.”

Hardware Articles

Storage and SQLIO

Overview of FAT, HPFS, and NTFS Filesystems KB

Microsoft Knowledge Base article. Description: “This article explains the differences between FAT, HPFS, and NTFS under Windows NT, and their advantages and disadvantages.”

SQL Server Logging and Data Storage Algorithms KB

Microsoft Knowledge Base article. Description: “SQL Server 7.0, SQL Server 2000, and SQL Server 2005 restructure and redesign the logging and data algorithms from earlier Microsoft SQL Server releases to improve data reliability and integrity. This document addresses the SQL Server 7.0, SQL Server 2000, and SQL Server 2005 techniques to extend data reliability and integrity as related to failures.”

Only skip this if: you fully understand the concepts of torn page detection, sector ordering, and battery-backed write cache.

Using Disk Drive Caches with SQL Server KB

Microsoft Knowledge Base article. Description: “database system is first and foremost responsible for the accurate storage and retrieval of data, even in the event of unexpected system failures. The system must guarantee the atomicity and durability of transactions, while accounting for current execution, multiple transactions, and various failure points. This is often referred to as the ACID (Atomicity, Consistency, Isolation, and Durability) properties. This article addresses the implications of a disk drive caches.”

Only skip this if: you fully understand the differences between SATA, IDE, and SCSI hard drives, and why SCSI drives are preferred in database environments.

File and Filegroups Architecture BOL

Description: ” SQL Server maps a database over a set of operating-system files. Data and log information are never mixed in the same file, and individual files are used only by one database. Filegroups are named collections of files and are used to help with data placement and administrative tasks such as backup and restore operations.”

Only skip this if: you understand how files are numbered, when autogrowths will occur in databases with multiple files, and how database snapshots are built with sparse files.

RAID Levels and SQL Server BOL

Description: very basic description of each RAID level and how it performs.

Microsoft Support for iSCSI Word Doc Direct Download Link

Apologies – we don’t have a landing page link for this. When you click on the above link, the download will start immediately. Description: “Differing data requirements, system complexities, and cost constraints mean that storage needs vary widely from business to business. Microsoft is committed to offering a broad range of storage solutions to meet this diversity of need. Most recently, this includes support for Internet Small Computer System Interface (iSCSI). The Microsoft iSCSI Software Initiator makes it possible for businesses to take advantage of existing network infrastructure to enable block-based storage over wide distances, without having to invest in additional hardware. This white paper is intended for storage administrators and others who are interested in capitalizing on the emerging iSCSI technology. To provide context for the development of iSCSI technologies and standards, the paper first outlines basic storage interconnect and data transfer technologies. Next, the paper explores details of the iSCSI technology, and then it places those details in the context of development of the Microsoft iSCSI Initiator package, which includes the Microsoft iSCSI initiator driver.”

Disk Partition Alignment Whitepaper

SQLCat whitepaper. Description: “This paper documents performance for aligned and nonaligned storage and why nonaligned partitions can negatively impact I/O performance; it explains disk partition alignment for storage configured on Windows Server 2003, including analysis, diagnosis, and remediation; and it describes how Windows Server 2008 attempts to remedy challenges related to partition alignment for new partitions yet does not correct the configuration of preexisting partitions. The following topics are also included: background information, implementation, vendor considerations, two essential correlations, valid starting partition offsets, and the simple protocol to align partitions, define file allocation unit size, and assign drive letters. It includes results from tests that show how partition alignment can affect performance for SQL Server 2008.”


Best Practices for Running SQL 2008 in a Hyper-V Environment Whitepaper

SQLCat whitepaper. Description: “This white paper describes a series of test configurations we ran, which represented a variety of possible scenarios involving SQL Server running in Hyper-V. The paper discusses our results and observations, and it also presents our recommendations.”

Production Administration Articles

Backup and Restore

Restore Internals Blog Category

Articles from Paul Randal’s blog.

CHECKDB Blog Category

Articles from Paul Randal’s blog.

Corruption Blog Category

Articles from Paul Randal’s blog.

IO Subsystems Blog Category

Articles from Paul Randal’s blog.

Backing Up and Restoring Databases BOL

Description: “Microsoft SQL Server enables you to back up and restore your databases. The SQL Server backup and restore component provides an important safeguard for protecting critical data stored in SQL Server databases. A well-planned backup and restore strategy helps protect databases against data loss caused by a variety of failures. Test your strategy by restoring a set of backups and then recovering your database to prepare you to respond effectively to a disaster.”

High Availability

High Availability Solutions Overview BOL

Description: “This section introduces several SQL Server high-availability solutions that improve the availability of servers or databases. A high-availability solution masks the effects of a hardware or software failure and maintains the availability of applications so that the perceived downtime for users is minimized.”

Selecting a High Availability Solution BOL

Description: lists the advantages for each HA option.

Database Mirroring Overview BOL

Description: “Database mirroring is a primarily software solution for increasing database availability. Mirroring is implemented on a per-database basis and works only with databases that use the full recovery model. The simple and bulk-logged recovery models do not support database mirroring. Therefore, all bulk operations are always fully logged. Database mirroring works with any supported database compatibility level.”

Log Shipping Overview BOL

Description: “Log shipping allows you to automatically send transaction log backups from a primary database on a primary server instance to one or more secondary databases on separate secondary server instances. The transaction log backups are applied to each of the secondary databases individually. An optional third server instance, known as the monitor server, records the history and status of backup and restore operations and, optionally, raises alerts if these operations fail to occur as scheduled.”

Using Replication for HA and DR Whitepaper

SQLCat whitepaper. Description: “An essential part of IT mission is maintaining high availability and disaster recovery capability. This technical case study shows how a company can use SQL Server 2008 database replication to eliminate single points of failure in data centers, and to enable fast recovery from a possible disaster at its primary data center. These strategies and solutions will be of interest to database administrators, senior IT managers, project leads, and architects.Introduction: An international company has deployed a number of SQL Server instances spread out over different locations in Asia, the United States and Europe. The main problem they wanted to resolve was the high availability of data critical to their main application. In the event this data ever became unavailable, the financial repercussions would have been substantial. While the company had many ways and methods to alleviate this problem, they were unsure of which SQL Server high availability/disaster recovery technology would address their problem plus provide a strategic platform for the future. They engaged with Microsoft to help in the design of an architecture best fit for their needs, budget, expectations and skill set. In this document, we will be going through the steps and processes the project team went through to evaluate and decide on the best choices and options.”

HA and DR at ServiceU Case Study

SQLCat whitepaper. Description: “Achieving maximum availability and near immediate recovery from a disaster is essential for maintaining our revenue stream. We have worked hard to eliminate all single points of failure in our architecture, and we have developed procedures for patching servers, upgrading software, and implementing application changes that preserve high availability. Based on these efforts, we have achieved 99.99 percent uptime, including both planned and unplanned downtime. This case study examines the decisions that we made and the procedures we employed to maintain maximum availability with minimal downtime. This information will be of interest to senior IT managers, project leads, architects, and database administrators (DBAs).”

SQL 2008 Failover Clustering Whitepaper

SQLCat whitepaper. Description: “This white paper complements the existing documentation on planning, implementation, and administration of a SQL Server 2008 failover cluster, which can be found in Microsoft SQL Server 2008 Books Online. There are links to relevant existing content throughout the paper, which is intended primarily for a technical audience. This white paper covers failover cluster architecture and concepts for Windows Server (2003 and 2008) and SQL Server 2008; installation of a SQL Server 2008 failover cluster; upgrades and updates to SQL Server 2008 failover clustering; and maintenance and administration of SQL Server 2008.”

SQL Replication – Providing HA Using Database Mirroring Whitepaper

SQLCat whitepaper. Description: “This white paper describes how to use database mirroring to increase the availability of the replication stream in a transactional environment. It covers setting up replication in a mirrored environment, the effect of mirroring partnership state changes, and the effect of mirroring failovers on replication. In addition, it describes how to use LSN-based initialization to recover from the failover of a mirrored subscriber database. Although brief overviews are given of both replication and database mirroring, it is easier to understand this white paper if the reader has some experience with one or both of these technologies, and has at least a rudimentary knowledge of database concepts such as transactions.”

Database Mirroring Best Practices Whitepaper

SQLCat whitepaper. Description: “Maximizing the availability of databases is on the top priority list for many database administrators worldwide. Database mirroring is a new feature in SQL Server 2005 that can help minimize planned and unplanned downtime, thereby maximizing the availability of the database. Database mirroring transports the changes in the production database to a mirror database, either synchronously or asynchronously. The mirror database can reside either in the same data center to provide a high-availability solution, or in a remote data center to provide a disaster-recovery solution. Business requirements such as service-level agreements and performance, as well as technical factors such as log generation rate, network throughput, and I/O throughput, influence the deployment of database mirroring. This paper discusses best practices and performance considerations for implementing database mirroring.”

Resource Governor

Introducing Resource Governor BOL

Description: “Resource Governor is a new technology in SQL Server 2008 that enables you to manage SQL Server workload and resources by specifying limits on resource consumption by incoming requests. In the Resource Governor context, workload is a set of similarly sized queries or requests that can, and should be, treated as a single entity. This is not a requirement, but the more uniform the resource usage pattern of a workload is, the more benefit you are likely to derive from Resource Governor. Resource limits can be reconfigured in real time with minimal impact on workloads that are executing. In an environment where multiple distinct workloads are present on the same server, Resource Governor enables you to differentiate these workloads and allocate shared resources as they are requested, based on the limits that you specify. These resources are CPU and memory.”

Managing Workloads with Resource Governor BOL

Description: “SQL Server 2008 provides Resource Governor, a feature than you can use to manage SQL Server workload and system resource consumption. Resource Governor enables you to specify limits on the amount of CPU and memory that incoming application requests can use.”

Resource Governor Concepts BOL

Description: Explains resource pools, workload groups, and classification functions.


Securing SQL Server BOL

Description: “Securing SQL Server can be viewed as a series of steps, involving four areas: the platform, authentication, objects (including data), and applications that access the system. The following topics will guide you through creating and implementing an effective security plan.”

Setting Up Windows Service Accounts BOL

Description: “Each service in SQL Server represents a process or a set of processes to manage authentication of SQL Server operations with Windows. This topic describes the default configuration of services in this release of SQL Server, and configuration options for SQL Server services that you can set during SQL Server installation.”

Security Considerations for a SQL Server Installation BOL

Description: “Security is important for every product and every business. By following simple best practices, you can avoid many security vulnerabilities. This topic discusses some security best practices that you should consider both before you install SQL Server and after you install SQL Server. Security guidance for specific features is included in the reference topics for those features.”

Reading Compliance – SQL Server 2008 Compliance Guide Whitepaper

SQLCat whitepaper. Description: “Organizations across the globe are being inundated with regulatory requirements. They also have a strong need to better manage their IT systems to ensure they are operating efficiently and staying secure. Microsoft is often asked to provide guidance and technology to assist organizations struggling with compliance. The SQL Server 2008 Compliance Guidance white paper was written to help organizations and individuals understand how to use the features of the Microsoft® SQL Server® 2008 database software to address their compliance needs. This paper serves as an accompaniment to the SQL Server 2008 compliance software development kit (SDK), which provides sample code and guidance for understanding SQL Server 2008 compliance features and using them for developing solutions.”

SQL Server 2005 Security Best Practices

SQLCat whitepaper. Description: “This white paper covers some of the operational and administrative tasks associated with SQL Server 2005 security and enumerates best practices and operational and administrative tasks that will result in a more secure SQL Server system. Each topic describes a feature and best practices. For additional information on the specifics of utilities, features, and data definition language (DDL) statements referenced in this white paper, see SQL Server 2005 Books Online. Features and options that are new or defaults that are changed for SQL Server 2005 are identified. Coding examples for operational tasks use Transact-SQL, so understanding Transact-SQL is required for you to get the most out of this paper.”


Optimizing TempDB Performance BOL

Description: “The size and physical placement of the tempdb database can affect the performance of a system. For example, if the size that is defined for tempdb is too small, part of the system-processing load may be taken up with autogrowing tempdb to the size required to support the workload every time you restart the instance of SQL Server. You can avoid this overhead by increasing the sizes of the tempdb data and log file.”

Capacity Planning for TempDB BOL

Description: “This topic provides guidelines for determining the appropriate amount of disk space that tempdb requires. This topic also includes recommendations about how to configure tempdb for optimal performance in a production environment and information about how to monitor tempdb space usage.”

Programming Articles


C# Tutorial

23 lessons at C# Station. Description: “This is a set of lessons suited for beginning to intermediate programmers or anyone who would like to gain familiarity with the C# programming language. These lessons will help you get a quick head-start with C# programming.”

C# Tutorial

7 lessons at C# Station. Description: “This lesson is an introduction to ADO.NET. It introduces primary ADO.NET concepts and objects that you will learn about in later lessons.” Objectives are to understand the functions of the data provider, connection object, command object, DataReader object, DataSet object, and DataAdapter object.


Using CLR Integration in SQL 2005 Whitepaper

Description: “This paper describes the new CLR integration features of SQL Server 2005 and how database application developers and architects can take advantage of them to write user-defined procedures, functions, and triggers, as well as define new types and aggregates. (46 printed pages)”

DBA’s Guide to SQL Server CLR Whitepaper

Description: “Microsoft® SQL Server™ 2005 provides the database application programmer with a rich, new development platform by hosting the Microsoft .NET Framework Common Language Runtime (CLR) environment. With new capabilities come new roles and responsibilities for the database administrator (DBA). This white paper helps the DBA determine the appropriate use of this new feature and also provides guidance about when other alternatives may provide better performance, flexibility, or capabilities. This white paper also offers guidance about suitable uses for the Database Engine .NET Framework Programming API. It also suggests code, change, and release management processes that should be tailored to each DBA’s circumstances in order to ensure a professional and safe deployment.”

Service Broker

SQL Server Service Broker BOL

Description: “SQL Server Service Broker provides the SQL Server Database Engine native support for messaging and queuing applications. This makes it easier for developers to create sophisticated applications that use the Database Engine components to communicate between disparate databases. Developers can use Service Broker to easily build distributed and reliable applications.”

SQL Server Service Broker Overview BOL

Description: ” Service Broker helps database developers build reliable and scalable applications. Because Service Broker is part of the Database Engine, administration of these applications is part of the routine administration of the database. Service Broker provides queuing and reliable messaging for SQL Server. Service Broker is used both for applications that use a single SQL Server instance and applications that distribute work across multiple instances.”

What Does Service Broker Do – BOL

Description: “Service Broker helps developers build asynchronous, loosely coupled applications in which independent components work together to accomplish a task. These application components exchange messages that contain the information that is required to complete the task.”

Typical Uses of Service Broker BOL

Description: “Service Broker can be useful for any application that needs to perform processing asynchronously, or that needs to distribute processing across a number of computers. Typical uses of Service Broker include asynchronous triggers, reliable query processing, reliable data collection, distributed server-side processing for client applications, data consolidation for client applications, and large-scale batch processing.”

Service Broker Performance and Reliability Whitepaper

SQLCat whitepaper. Description: “Service Broker provides support for building asynchronous messaging and queuing applications with the SQL Server Database Engine. This paper describes a large scale customer scenario and the techniques employed in scaling Service Broker to process tens of thousands of messages per second on one server. Microsoft SQL Server Service Broker provides native support to the SQL Server Database Engine for asynchronous, transactional messaging. Finding Service Broker bottlenecks requires a similar approach to tuning any high-end OLTP database systems. This paper will describe the performance and scalability techniques applied to a real-world workload to increase overall system throughput. After you understand the workload and Service Broker system tables that are used, you’ll be able to find and remove the bottlenecks to scale Service Broker applications.”

SQL Server Integration Services (SSIS)


Description: “Microsoft Integration Services is a platform for building enterprise-level data integration and data transformations solutions. You use Integration Services to solve complex business problems by copying or downloading files, sending e-mail messages in response to events, updating data warehouses, cleaning and mining data, and managing SQL Server objects and data. The packages can work alone or in concert with other packages to address complex business needs. Integration Services can extract and transform data from a wide variety of sources such as XML data files, flat files, and relational data sources, and then load the data into one or more destinations.”

SSIS Security Overview BOL

Description: “Security in SQL Server Integration Services consists of several layers that provide a rich and flexible security environment. These security layers include the use of digital signatures, package properties, SQL Server database roles, and operating system permissions. Most of these security features fall into the categories of identity and access control.”

Integration Services Packages BOL

Description: “A package is an organized collection of connections, control flow elements, data flow elements, event handlers, variables, and configurations, that you assemble using either the graphical design tools that SQL Server Integration Services provides, or build programmatically. You then save the completed package to SQL Server, the SSIS Package Store, or the file system. The package is the unit of work that is retrieved, executed, and saved.”

Creating Package Configurations BOL

Description: “You create package configurations by using the Package Configuration Organizer dialog box and the Package Configuration Wizard. To access these tools, click Package Configurations on the SSIS menu in Business Intelligence Development Studio.”

Schema Articles

Change Data Capture

Change Tracking BOL

Description: “Change tracking in SQL Server 2008 enables applications to obtain only changes that have been made to the user tables, along with the information about those changes. With change tracking integrated into SQL Server, complicated custom change tracking solutions no longer have to be developed.”

Tuning the Performance of Change Data Capture Whitepaper

SQLCat whitepaper. Description: “Change data capture is a new feature in SQL Server 2008 that provides an easy way to capture changes to data in a set of database tables so these changes can be transferred to a second system like a data warehouse. This document provides guidance on how to configure change data capture parameters to maximize data capture performance while minimizing the performance impact on the production workload. The scope of this document is limited to the capture of change data and the cleanup process. Querying the changed data is out of scope for this white paper.”

Spatial Data

Introduction to New T-SQL Programmability Features in 2008 Whitepaper

Description: “This paper introduces key new Transact-SQL programmability features in Microsoft SQL Server 2008 as well as SQL/Common Language Runtime (CLR) enhancements. New Transact-SQL features provide improved performance, increased functionality, and enhanced globalization support. Transact-SQL programmability enhancements in SQL Server 2008 address the needs of both OLTP and data warehouse environments.”

Introduction to Spatial Coordinate Systems – Flat Maps for a Round Planet

Description: “This paper is an introduction to Earth-oriented coordinate systems, projections, models, and mapping. While not specific to any technology, this information provides valuable background for those who will use spatial data in SQL Server.”


XML Support in SQL 2005 Whitepaper

Description: “This paper provides an overview of the XML support that is built into Microsoft SQL Server 2005. It includes a discussion of how XML integrates with client-side programming support in both the .NET Framework 2.0 and native code such as OLEDB and SQLXML. (40 printed pages)”

XML Options in SQL 2005 Whitepaper

Description: “Three options for handling XML data in the Visual Studio 2005/SQL Server 2005 environment are discussed, including usage scenarios and guidelines to help you choose between them. (34 printed pages)”

XML Best Practices for SQL 2005 Whitepaper

By the SQLCat team. Description: “Learn about the guidelines for XML data modeling and usage in Microsoft SQL Server 2005, and see illustrative examples. To get the most from this article, you should have a basic understanding of XML features in SQL Server.”

Tuning Articles

Benchmarking and Baselining

SQL Server Profiler BOL

Description: “Microsoft SQL Server Profiler is a graphical user interface to SQL Trace for monitoring an instance of the Database Engine or Analysis Services. You can capture and save data about each event to a file or table to analyze later. For example, you can monitor a production environment to see which stored procedures are affecting performance by executing too slowly.”

Pre-Deployment IO Best Practices Whitepaper

Description: “The I/O system is important to the performance of SQL Server. When configuring a new server for SQL Server or when adding or modifying the disk configuration of an existing system, it is good practice to determine the capacity of the I/O subsystem prior to deploying SQL Server. This white paper discusses validating and determining the capacity of an I/O subsystem. A number of tools are available for performing this type of testing. This white paper focuses on the SQLIO.exe tool, but also compares all available tools. It also covers basic I/O configuration best practices for SQL Server 2005.”

Performance Tuning and Optimization

Indexing Blog Category

Articles from Kimberly Tripp’s blog.

Statistics Used by the Query Optimizer Whitepaper

Description: “Microsoft® SQL Server™ 2005 collects statistical information about indexes and column data stored in the database. These statistics are used by the SQL Server query optimizer to choose the most efficient plan for retrieving or updating data. This paper describes what data is collected, where it is stored, and which commands create, update, and delete statistics. By default, SQL Server 2005 also creates and updates statistics automatically, when such an operation is considered to be useful. This paper also outlines how these defaults can be changed on different levels (column, table, and database).”

Batch Compilation, Recompilation, and Plan Caching Issues Whitepaper

Description: “This paper explains how batches are cached and reused in SQL Server 2005, and suggests best practices on maximizing reuse of cached plans. It also explains scenarios in which batches are recompiled, and gives best practices for reducing or eliminating unnecessary recompilations.”

Database Engine Tuning Advisor Word Doc Download Link

Apologies, but we don’t have a landing page link for this. When you click on the above link, the Word doc download will begin immediately. Description: “Database administrators in enterprises today face the challenging task of determining an appropriate physical design that consists of partitioned tables, indexes, and indexed views and that both optimizes server performance and is easy to manage. Database Engine Tuning Advisor (DTA) in Microsoft SQL Server 2005 can assist in this task. Given a workload of SQL queries and updates, DTA recommends an appropriate physical design, and generates a script to implement the recommended physical design. For more advanced database administrators, DTA exposes a powerful mechanism to perform efficient exploratory what-if analysis of different physical design alternatives. DTA can be used against both SQL Server 2005 and SQL Server 2000.”

Row Versioning-Based Transaction Isolation Whitepaper

Description: “Microsoft SQL Server 2005 provides nonlocking, nonblocking read consistency to your users through snapshot isolation and read committed isolation using row versioning. Find out when you can use these features to improve performance and reduce latency in your applications. (59 printed pages)”

Partitioned Tables and Indexes Whitepaper

Description: “Table-based partitioning features in SQL Server 2005 provide flexibility and performance to simplify the creation and maintenance of partitioned tables. Trace the progression of capabilities from logically and manually partitioning tables to the latest partitioning features, and find out why, when, and how to design, implement, and maintain partitioned tables using SQL Server 2005. (41 printed pages)”

Troubleshooting Performance Problems in SQL Server 2008 Whitepaper

SQLCat whitepaper. Description: “Sometimes a poorly designed database or a system that is improperly configured for the workload can cause the slowdowns in SQL Server. Administrators need to proactively prevent or minimize problems and, when they occur, diagnose the cause and take corrective action. This paper provides step-by-step guidelines for diagnosing and troubleshooting common performance problems by using publicly available tools such as SQL Server Profiler, Performance Monitor, dynamic management views, and SQL Server Extended Events (Extended Events) and the data collector, which are new in SQL Server 2008.”

Waits and Queues

SQL Server Waits and Queues Whitepaper

Description: “SQL Server 2005 Performance Tuning using the Waits and Queues is a proven methodology that allows one to identify the best opportunities to improve performance, the so called “biggest bang for the buck”. These performance improvements are likely to have a significant return on the performance tuning time investment. The methodology helps identify the areas of slow performance by looking at the problem from two directions called Waits and Queues. An analysis of Waits indicates where SQL Server is spending lots of time waiting. In addition, the biggest waits point out the most important or relevant Queues (that is, Performance Monitor counters and other data) for this workload. The cross validation of the waits analysis enables us to eliminate all except the most significant performance counters, and provides a strong indication of pressure on specific resources. Application performance can be easily explained by looking at SQL Server waits and System or Resource queues. In SQL Server 2005, the dynamic management view (DMV) sys.dm_os_wait_stats provides a valuable source of wait information from an application perspective. The system or resource perspective uses Performance Monitor counters and other data sources to provide a breakdown of system resource usage according to resource queues. Taken together, the value of the application and resource perspectives used by the waits and queues methodology enables us to narrow in on the bottleneck and eliminate the irrelevant performance data.”

Troubleshooting Performance Problems Whitepaper

Description: “It is not uncommon to experience the occasional slow down of a SQL Server database. A poorly designed database or a system that is improperly configured for the workload are but several of many possible causes of this type of performance problem. Administrators need to proactively prevent or minimize problems and, when they occur, diagnose the cause and take corrective actions to fix the problem. This paper provides step-by-step guidelines for diagnosing and troubleshooting common performance problems by using publicly available tools such as SQL Server Profiler, System Monitor, and the new Dynamic Management Views in SQL Server 2005.”

This entry was posted in Uncategorized. Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s