Contents

About the Authors

About the Technical Reviewers

Acknowledgments

Introduction

Chapter 1: Be Your Developer’s Best Friend

My Experience Working with SQL Server and Developers

Reconciling Different Viewpoints Within an Organization

Preparing to Work with Developers to Implement Changes to a System

Step 1: Map Your Environment

Step 2: Describe the New Environment

Step 3: Create a Clear Document

Step 4: Create System-Management Procedures

Step 5: Create Good Reporting

Ensuring Version Compatibility

Setting Limits

Logon Triggers

Policy-Based Management

Logging and Resource Control

Next Steps

Chapter 2: Getting It Right: Designing the Database for Performance

Requirements

Table Structure

A Really Quick Taste of History

Why a Normal Database Is Better Than an Extraordinary One

Physical Model Choices

Design Testing

Conclusion

Chapter 3: Hidden Performance Gotchas

Predicates

Residuals

Spills

Conclusion

Chapter 4: Dynamic Management Views

Understanding the Basics

Naming Convention

Groups of Related Views

Varbinary Hash Values

Common Performance-Tuning Queries

Retrieving Connection Information

Showing Currently Executing Requests

Locking Escalation

Finding Poor Performing SQL

Using the Power of DMV Performance Scripts

Divergence in Terminology

Optimizing Performance

Inspecting Performance Stats

Top Quantity Execution Counts

Physical Reads

Physical Performance Queries

Locating Missing Indexes

Partition Statistics

System Performance Tuning Queries

What You Need to Know About System Performance DMVs

Sessions and Percentage Complete

Conclusion

Chapter 5: From SQL Trace to Extended Events

SQL Trace

Trace rowset provider

Trace file provider

Event Notifications

Extended Events

Events

Predicates

Actions

Types and Maps

Targets

Sessions

Built in Health Session

Extended Events .NET provider

Extended Events UI

Conclusion

Chapter 6: The Utility Database

Start with Checklists

Daily Checklist Items

Longer-Term Checklist Items

Utility Database Layout

Data Storage

Using Schemas

Using Data Referential Integrity

Creating the Utility Database

Table Structure

Gathering Data

System Tables

Extended Stored Procedures

CLR

DMVs

Storage

Processors

Error Logs

Indexes

Stored Procedure Performance

Failed Jobs

Reporting Services

Mirroring

AlwaysOn

Managing Key Business Indicators

Using the Data

Automating the Data Collection

Scheduling the Data Collection

Conclusion

Chapter 7: Indexing Outside the Bubble

The Environment Bubble

Identifying Missing Indexes

Index Tuning a Workload

The Business Bubble

Index Business Usage

Data Integrity

Conclusion

Chapter 8: Release Management

My Release Management Process

A Change Is Requested

Release Process Overview

Considerations

Documents

Release Notes

Release Plan Template and Release Plans

Document Repository

Conclusion

Chapter 9: Compliance and Auditing

Compliance

Sarbanes-Oxley

Health Insurance Portability and Accountability Act

New Auditing Features in SQL Server 2012

Server-Level Auditing for the Standard Edition

Audit Log Failure Options

Maximum Rollover Files

User-Defined Auditing

Audit Filtering

Auditing

Server Audit

Server Audit Specification

Database Audit Specification

Query the Audit File

Pro Tip: Alert on Audit Events

Conclusion

Chapter 10: Automating Administration

Tools for Automation

Performance Monitor

Dynamic Management Views

SQL Server Agent

Maintenance Plans

SQL Server Integration Services

PowerShell

What to Automate

Monitoring

Backups and Restores

Database Integrity

Index Maintenance

Statistics Maintenance

Conclusion

Chapter 11: The Fluid Dynamics of SQL Server Data Movement

Why the Need for Replicating Data?

SQL Server Solutions

Replication

Log Shipping

Database Mirroring

AlwaysOn

Failover Clustering

Custom ETL Using SQL Server Integration Services

Bulk Copy Process

Choosing the Right Deployment

Keeping the Data Consistent

Conclusion

Chapter 12: Windows Azure SQL Database for DBAs

SQL Database Architecture

Infrastructure

Availability and Failover

Hardware

Differences with SQL Server

Database Components

Management Platform

Security

Other Important Information

Federations

Key Terms

T-SQL Changes for Federations

Federation Example

Limitations

Troubleshooting Performance Issues

DMVs Available

Execution Plans

Performance Dashboard

Related Services

Windows Azure SQL Reporting

Windows Azure SQL Data Sync

Import/Export Feature

Cost of SQL Database

Conclusion

Chapter 13: I/O: The Untold Story

The Basics

Monitoring

Considerations

Tactical

Code or Disk?

Times Have Changed

Getting to the Data

Addressing a Query

Environmental Considerations

Conclusion

Chapter 14: Page and Row Compression

Before You Get Started

Editions and Support

What to Compress and How to Compress It

Row Compression

Page Compression

What Do You Compress?

Fragmentation and Logged Operations

Conclusion

Chapter 15: Selecting and Sizing the Server

Understanding Your Workload

SQL Server 2012 Enterprise Edition Consideration Factors

Server Vendor Selection

Server Form Factor Selection

Server Processor Count Selection

Dell 12th Generation Server Comparison

Dell PowerEdge R320

Dell PowerEdge R420

Dell PowerEdge R520

Dell PowerEdge R620

Dell PowerEdge R720

Dell PowerEdge R720xd

Dell PowerEdge R820

Dell Comparison Recap

Processor Vendor Selection

Processor Model Selection

Memory Selection

Conclusion

Chapter 16: Backups and Restores Using Availability Groups

Setting Up an Availability Group

Configuring the Windows Server

SQL Server Availability Group

Enabling Backups on Availability Groups

Backup Location

Backup Priority

Automating Backups on Availability Groups

Maintenance Plans

T-SQL Scripts

Recovery on Availability Groups

Conclusion

Chapter 17: Big Data for the SQL Server DBA

Big Data Arrives with Hadoop

MapReduce: The Nucleus of Hadoop

Hardware

DBA As Data Architect

Big Data for Analytics

Using SQL Server with Hadoop

The DBA’s Role

Big Data in Practice

Exporting from HDFS

Hive

Hive and Excel

JavaScript

Pig

Big Data for the Rest of Us

Business Intelligence

Big Data Sources

Big Data Business Cases

Big Data in the Microsoft Future

Conclusion

Chapter 18: Tuning for Peak Load

Define the Peak Load

Determine Where You Are Today

Perform the Assessment

Define the Data to Capture

Analyze the Data

Analyzing Application-Usage Data

Analyzing Perfmon Data

Analyzing Configuration Data

Analyzing SQL Performance Data

Devise a Plan

Conclusion

Index