Log In
Or create an account ->
Imperial Library
Home
About
News
Upload
Forum
Help
Login/SignUp
Index
Cover
Title
Copyright
Dedication
Contents at a Glance
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
← Prev
Back
Next →
← Prev
Back
Next →