Healthy SQL : A Comprehensive Guide to Healthy SQL Server Performance

Healthy SQL : A Comprehensive Guide to Healthy SQL Server Performance

by Robert Pearl
Released May 2015
Publisher(s): Apress
ISBN: 9781430267720

Read it now on the O’Reilly learning platform with a 10-day free trial.

O’Reilly members get unlimited access to books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.

Start your free trial

Book description

Healthy SQL is about ensuring the ongoing performance health of a SQL Server database. An unhealthy database is not just an inconvenience; it can bring a business to its knees. And if you are the database administrator, the health of your SQL Server implementation can be a direct reflection on you. It's in everyone's best interest to have a healthy SQL implementation. Healthy SQL is built around the concept of a medical checkup, giving you the tools you need to assess the current health of your database and take action to improve upon that health and maintain good performance to your business.

Healthy SQL aids in developing a rigorous routine so that you know how healthy your SQL Server machines are, and how you can keep those same servers healthy and fit for duty. The book is filled with practical advice and a time-tested strategy, helping you put together a regimen that will ensure your servers are healthy, your implementation is fully optimized, your services are redundant and highly available, and you have a plan for business continuity in the event of a disaster. If your current environment doesn't match up with these criteria, then pick up a copy of Healthy SQL today and start your journey on the road to a fit and tight SQL Server deployment.

Table of contents

  1. Cover
  2. Title
  3. Copyright
  4. Dedication
  5. Testimonials
  6. Contents at a Glance
  7. Contents
  8. About the Author
  9. About the Technical Reviewer
  10. Acknowledgments
  11. Foreword
  12. Chapter 1: Introduction to Healthy SQL
    1. Enter the DBA
      1. Who Cares?
      2. SQL Fitness
      3. What You Will Learn?
    2. What Is Healthy SQL?
      1. What Is a Health Check?
      2. Recent Infamy
    3. Why Perform a Health Check?
      1. Performance
      2. Security
      3. Stability
      4. Audits
      5. Migration
      6. Upgrade
      7. Backups
      8. Business Continuity
    4. When to Perform a Health Check
  13. Chapter 2: Creating a Road Map
    1. Statistics and Performance
      1. Understanding the Terms
      2. Applying Real-World Principles of Statistics to Performance
    2. Inventories
    3. The Checklist
      1. What to Collect
      2. CPU, Memory, I/O, Locking, and Blocking
      3. Collecting Information from the System Catalog
    4. Virtually Speaking…
      1. Memory Ballooning
      2. Over-allocation of Memory/CPU
    5. Best Practices: Says Who?
      1. Some Not-So Best Practices
      2. It Depends
      3. Run Book
    6. Road Map Essentials
    7. Rob’s Bonus Best Practice Considerations
  14. Chapter 3: Waits and Queues
    1. Introducing Waits and Queues
    2. S-l-o-w Performance
    3. Blame Game: Blame SQL Server
    4. Back to Waiting
      1. Wait Type Categories
      2. Is Waiting a Problem?
      3. Observing Wait Statistics
    5. The Execution Model
    6. CPU Pressure
      1. Runnable Task Count
      2. Signal Waits
      3. Anatomy of a CPU Metadata Query
      4. CPU Blame Game
    7. I/O May Be Why Your Server Is So Slow
      1. I/O Blame Game
      2. Fragmentation Affects I/O
      3. I/O Latch Buffer Issues
      4. Related Performance Monitor Counters
      5. Memory Pressure
      6. Parallelism and CXPACKET
      7. Blocking and Locking, Oh My!
    8. Summary
  15. Chapter 4: Much Ado About Indexes
    1. Indexes 101
      1. What Are Indexes?
      2. Index Types and Terminology
      3. Index Advantages vs. Disadvantages
      4. B-Tree Index Structure
      5. Index-Related Dynamic Management Views and Functions
      6. Where to Place Indexes
      7. Fill Factor and Page Splitting
    2. Common Index Issues
      1. Index Usage
      2. Index Fragmentation
      3. Index Reorganization
      4. Missing Indexes
      5. Duplicate Indexes
    3. Database Engine Tuning Advisor
    4. Summary
  16. Chapter 5: Tools of the Trade: Basic Training
    1. Build a Better Mousetrap
    2. Monday Morning Mania (Busiest DBA Day)
      1. Activity Monitor
      2. sp_whoisactive
    3. SSMS Standard Reports
      1. Server-Level Reports
      2. Database Reports
    4. SQL Server 2012 Performance Dashboard
    5. The Power of Dynamic Management Views and Function Categories
      1. sys.dm_os_performance_counters (How to Read and Calculate Them)
      2. Diagnostic DMOs
      3. Bonus: sys.dm_exec_query_profiles DMO (SQL Server 2014 Only)
    6. SQL Profiler/Trace
      1. Default Trace
      2. Ensure Default Trace Is On
    7. Performance Monitor
    8. Data Collector
    9. Management Data Warehouse: SQL Server 2008 and Higher
    10. Basic Training Completed
  17. Chapter 6: Expanding Your Tool Set
    1. New Tools
      1. Extended Events
      2. The New Session Wizard
      3. The system_health Session
      4. The sp_server_diagnostics Procedure
      5. XQuery
      6. SQL Server 2012 System Health Reporting Dashboard
    2. Other Free and Downloadable Tools
      1. PowerShell
      2. SQLPowerDoc
      3. Performance Analysis of Logs
      4. SQL Server Best Practice Analyzer (Through 2012)
    3. Closing the Tool Shed
  18. Chapter 7: Creating a SQL Health Repository
    1. Laying the Repository Groundwork
    2. Deploying the Management Data Warehouse
    3. Configuring the Data Collection
    4. Post-Configuration Tasks
    5. Accounts, Privileges, Rights, and Credentials
      1. Configuring Account Privileges for MDW Server
      2. Configuring Account Privileges for the MDW Client
      3. MDW Caveats
    6. Defining a Custom Data Collection
    7. Rolling Your Own
    8. Summary
  19. Chapter 8: Monitoring and Reporting
    1. SQL Server Agent Alerts, Operators, and Notifications
      1. Configure Database Mail
      2. Configuring SQL Server Agent Alerts
    2. Monitoring with Extended Events
      1. Deadlocks
      2. Blocking and Locking
      3. Monitoring Errors with Extended Events
    3. Monitoring Software
      1. Build vs. Buy
      2. Features, Polling, Agents, Push vs. Pull
      3. Licensing and Pricing
      4. Evaluating Monitoring Software
    4. Reporting on Server Health
      1. MDW Reports
      2. Building the Custom SSRS Report
      3. Create the Report Framework
    5. Summary
  20. Chapter 9: High Availability and Disaster Recovery
    1. Evolution of Disaster Recovery and SQL Server
      1. Business Continuity
      2. Concepts of High Availability and Disaster Recovery
      3. Database Backups and Recovery Models
      4. Challenges of High Availability
      5. SQL DR Drawbacks
    2. Backup and Recovery
      1. First Line of Defense
      2. Point-in-Time Restore
      3. Database Corruption: Detection, Recovery, and Repair
    3. Log Shipping
      1. About Log Shipping
      2. Deploy Log Shipping
    4. Mirroring
      1. About Database Mirroring
      2. Configuring Database Mirroring
    5. Clustering
      1. About Clustering
      2. Multisubnet Clusters
    6. Availability Groups (2012)
      1. Enabling and Setting Up the AG Feature
      2. Read-Only Replicas
    7. Virtualization
    8. Peer-to- Peer Replication
    9. DR Run Book
    10. Summary
  21. Chapter 10: Sur?viving the Audit
    1. Overview
    2. Database Forensics
      1. Benefits of Database Forensics
    3. Regulatory Compliance
      1. Industry Rules and Regulation
    4. Database Access Control and Compliance
      1. Control Access to the Database Environment
      2. Segregation of Duties
      3. Monitoring
      4. Backup/Recovery
      5. SQL Code
      6. Change Control
      7. Login and Password Policy Enforcement
      8. The Tale of the DBA and the Developer
    5. Transaction Log
      1. Reading the Transaction Log
    6. C2 Auditing and Common Criteria Compliance
    7. SQL Audit
      1. Creating an Audit in SQL Server Management Studio
      2. Server Audit Specification
      3. Database Audit Specification
      4. Generating Audit Activity
      5. Viewing the Audit Log
    8. DDL Triggers
    9. Default Trace
      1. Reviewing the Default Trace Output
      2. Using the Default Trace to Capture SQL Server Configuration Changes
      3. Creating the History Tracking Table
      4. Methodology
      5. Creating the Stored Procedure
      6. Testing the Process
    10. Change Data Capture
    11. Policy-Based Management
      1. Creating a Implementing a SQL Password Enforcement Policy
      2. Testing the Policies
      3. Applying Policies
    12. Summary
  22. Index

Product information

  • Title: Healthy SQL : A Comprehensive Guide to Healthy SQL Server Performance
  • Author(s): Robert Pearl
  • Release date: May 2015
  • Publisher(s): Apress
  • ISBN: 9781430267720