Pro SQL Server 2012 Practices ■ ■ ■ Bradley Ball, TJay Belt, Glenn Berry, Jes Borland, Carlos Bossy, Louis Davidson, Ben DeBow, Grant Fritchey, Jonathan Gardner, Jesper Johansen, Jeremy Lowell, Wendy Pastrick, Kellyn Pot’vin, Mladen Prajdi(cid:2), Herve Roggero, Chris Shaw, Gail Shaw, Jason Strate Pro SQL Server 2012 Practices Copyright © 2012 by Bradley Ball, TJay Belt, Glenn Berry, Jes Borland, Carlos Bossy, Louis Davidson, Jeremy Lowell, Ben DeBow, (cid:2) Grant Fritchey, Wendy Pastrick, Kellyn Pot’vin, Jonathan Gardner, Jesper Johansen, Mladen Prajdi , Herve Roggero, Chris Shaw, Gail Shaw, Jason Strate This work is subject to copyright. All rights are reserved by the Publisher, whether the whole or part of the material is concerned, specifically the rights of translation, reprinting, reuse of illustrations, recitation, broadcasting, reproduction on microfilms or in any other physical way, and transmission or information storage and retrieval, electronic adaptation, computer software, or by similar or dissimilar methodology now known or hereafter developed. Exempted from this legal reservation are brief excerpts in connection with reviews or scholarly analysis or material supplied specifically for the purpose of being entered and executed on a computer system, for exclusive use by the purchaser of the work. Duplication of this publication or parts thereof is permitted only under the provisions of the Copyright Law of the Publisher’s location, in its current version, and permission for use must always be obtained from Springer. Permissions for use may be obtained through RightsLink at the Copyright Clearance Center. Violations are liable to prosecution under the respective Copyright Law. ISBN978-1-4302-4770-8 ISBN978-1-4302-4771-5(eBook) Trademarked names, logos, and images may appear in this book. Rather than use a trademark symbol with every occurrence of a trademarked name, logo, or image we use the names, logos, and images only in an editorial fashion and to the benefit of the trademark owner, with no intention of infringement of the trademark. The use in this publication of trade names, trademarks, service marks, and similar terms, even if they are not identified as such, is not to be taken as an expression of opinion as to whether or not they are subject to proprietary rights. While the advice and information in this book are believed to be true and accurate at the date of publication, neither the authors nor the editors nor the publisher can accept any legal responsibility for any errors or omissions that may be made. The publisher makes no warranty, express or implied, with respect to the material contained herein. President and Publisher: Paul Manning Lead Editor: Jonathan Gennick Developmental Editor: Brian MacDonald Technical Reviewer: Rudi Bruchez, Robin Dewson, David Dye, Edgar Lanting, Ken Simmons Editorial Board: Steve Anglin, Ewan Buckingham, Gary Cornell, Louise Corrigan, Morgan Ertel, Jonathan Gennick, Jonathan Hassell, Robert Hutchinson, Michelle Lowman, James Markham, Matthew Moodie, Jeff Olson, Jeffrey Pepper, Douglas Pundick, Ben Renow-Clarke, Dominic Shakeshaft, Gwenan Spearing, Matt Wade, Tom Welsh Coordinating Editor: Anamika Panchoo Copy Editors: Roger LeBlanc, Vanessa Moore Compositor: Bytheway Publishing Services Indexer: SPi Global Artist: SPi Global Cover Designer: Anna Ishchenko Distributed to the book trade worldwide by Springer Science+Business Media New York, 233 Spring Street, 6th Floor, New York, NY 10013. Phone 1-800-SPRINGER, fax (201) 348-4505, [email protected], or visit www.springeronline.com. For information on translations, please [email protected], or visit www.apress.com. Apress and friends of ED books may be purchased in bulk for academic, corporate, or promotional use. eBook versions and licenses are also available for most titles. For more information, reference our Special Bulk Sales–eBook Licensing web page at www.apress.com/bulk-sales. Any source code or other supplementary materials referenced by the author in this text is available to readers atwww.apress.com. For detailed information about how to locate your book’s source code, go towww.apress.com/source-code. ii To my wife, Silva, and my children, Zachary, William, Serenity, and Chesney: Thank you for putting up with my long hours on the computer. I love you all No Matter What! –Bradley Ball To Ali, Adam, and Gaby, for all they taught me about writing, computer science, and devotion. –Carlos Bossy I dedicate my chapter on database design to the poor programmers having to suffer with malformed databases. –Louis Davidson I type this on the date 9/11/2012. I’ve never dedicated one of my books before because I never saw the point. But this time, I’d like to dedicate my contribution to this book to all the first responders and those who serve in the military. Thanks for everything you do to protect our civilization. –Grant Fritchey To my lovely wife, Birgitte; my wonderful children, Alexander, Andreas, Jakob, Mathilde; my father, Ib; my best friend, Lars; and not least, Morten. –Jesper Johansen I’d like to dedicate my chapter to my children, Sam, Cait, and Josh. Everything I do, I do for them—as a Mom, a caregiver, and most of all, as an example for them to take into this world. –Kellyn Pot’Vin I’d like to dedicate this book to my mom, Desanka, and sister, Tajana, who are awesome supporters of my endeavors. (cid:2) –Mladen Prajdi How can I dedicate my work to all those who have influenced me over my lifetime in a few short words? Only one word is needed, family. To my wife, Gigi; my son, Mike; my mom, Ruth; our best friend, Rock; and brother, Anthony. To my Italian by association family Dick, Linda, Grandma (Barbara), and Grandpa (Don). For my Marine brothers and sisters, and those who stand or stood next to us. For my #SQLFamily: we can do it together. For my Dad, Herb: you may be gone but you will never be forgotten. –Chris Shaw To Nikolai, Aspen, and Dysin who missed out on a few rounds of Dungeon Defenders while I worked on this book. –Jason Strate iii Contents at a Glance (cid:2)About the Authors...............................................................................................................xiv (cid:2)About the Technical Reviewers..........................................................................................xix (cid:2)Acknowledgments..............................................................................................................xxi (cid:2)Introduction......................................................................................................................xxiii (cid:2)Chapter 1: Be Your Developer’s Best Friend.........................................................................1 (cid:2)Chapter 2: Getting It Right: Designing the Database for Performance..............................17 (cid:2)Chapter 3: Hidden Performance Gotchas...........................................................................43 (cid:2)Chapter 4: Dynamic Management Views............................................................................71 (cid:2)Chapter 5: From SQL Trace to Extended Events...............................................................101 (cid:2)Chapter 6: The Utility Database........................................................................................135 (cid:2)Chapter 7: Indexing Outside the Bubble...........................................................................161 (cid:2)Chapter 8: Release Management......................................................................................197 (cid:2)Chapter 9: Compliance and Auditing................................................................................221 (cid:2)Chapter 10: Automating Administration...........................................................................235 (cid:2)Chapter 11: The Fluid Dynamics of SQL Server Data Movement.....................................271 (cid:2)Chapter 12: Windows Azure SQL Database for DBAs......................................................293 (cid:2)Chapter 13: I/O: The Untold Story ....................................................................................313 (cid:2)Chapter 14: Page and Row Compression.........................................................................335 (cid:2)Chapter 15: Selecting and Sizing the Server....................................................................361 (cid:2)Chapter 16: Backups and Restores Using Availability Groups........................................375 (cid:2)Chapter 17: Big Data for the SQL Server DBA..................................................................395 (cid:2)Chapter 18: Tuning for Peak Load.....................................................................................429 (cid:2)Index..................................................................................................................................465 iv Contents (cid:2)About the Authors...............................................................................................................xiv (cid:2)About the Technical Reviewers..........................................................................................xix (cid:2)Acknowledgments..............................................................................................................xxi (cid:2)Introduction......................................................................................................................xxiii (cid:2)Chapter 1: Be Your Developer’s Best Friend.........................................................................1 My Experience Working with SQL Server and Developers.....................................................1 Reconciling Different Viewpoints Within an Organization.......................................................2 Preparing to Work with Developers to Implement Changes to a System...............................3 Step 1: Map Your Environment...............................................................................................3 Step 2: Describe the New Environment..................................................................................5 Step 3: Create a Clear Document...........................................................................................7 Step 4: Create System-Management Procedures..................................................................7 Step 5: Create Good Reporting.............................................................................................10 Ensuring Version Compatibility.............................................................................................11 Setting Limits.......................................................................................................................12 Logon Triggers..............................................................................................................................................12 Policy-Based Management ...........................................................................................................................15 Logging and Resource Control......................................................................................................................15 Next Steps............................................................................................................................15 (cid:2)Chapter 2: Getting It Right: Designing the Database for Performance..............................17 Requirements.......................................................................................................................18 Table Structure.....................................................................................................................20 A Really Quick Taste of History .....................................................................................................................20 v ■CONTENTS Why a Normal Database Is Better Than an Extraordinary One ...................................................................21 Physical Model Choices................................................................................................................................33 Design Testing......................................................................................................................40 Conclusion............................................................................................................................41 (cid:2)Chapter 3: Hidden Performance Gotchas...........................................................................43 Predicates............................................................................................................................43 Residuals..............................................................................................................................59 Spills.....................................................................................................................................65 Conclusion............................................................................................................................70 (cid:2)Chapter 4: Dynamic Management Views............................................................................71 Understanding the Basics....................................................................................................71 Naming Convention......................................................................................................................................72 Groups of Related Views...............................................................................................................................72 Varbinary Hash Values..................................................................................................................................73 Common Performance-Tuning Queries................................................................................74 Retrieving Connection Information ...............................................................................................................74 Showing Currently Executing Requests ........................................................................................................75 Locking Escalation........................................................................................................................................77 Finding Poor Performing SQL .......................................................................................................................78 Using the Power of DMV Performance Scripts .............................................................................................80 Divergence in Terminology ...........................................................................................................................82 Optimizing Performance...............................................................................................................................82 Inspecting Performance Stats ......................................................................................................................85 Top Quantity Execution Counts .....................................................................................................................86 Physical Reads.............................................................................................................................................87 Physical Performance Queries.............................................................................................87 Locating Missing Indexes .............................................................................................................................87 Partition Statistics........................................................................................................................................92 System Performance Tuning Queries...................................................................................93 What You Need to Know About System Performance DMVs .........................................................................93 Sessions and Percentage Complete .............................................................................................................93 Conclusion............................................................................................................................99 (cid:2)Chapter 5: From SQL Trace to Extended Events...............................................................101 SQL Trace............................................................................................................................101 Trace rowset provider.................................................................................................................................103 vi ■CONTENTS Trace file provider.......................................................................................................................................107 Event Notifications.............................................................................................................110 Extended Events.................................................................................................................114 Events.........................................................................................................................................................115 Predicates...................................................................................................................................................115 Actions........................................................................................................................................................116 Types and Maps..........................................................................................................................................116 Targets........................................................................................................................................................117 Sessions.....................................................................................................................................................118 Built in Health Session................................................................................................................................121 Extended Events .NET provider ...................................................................................................................123 Extended Events UI.....................................................................................................................................125 Conclusion..........................................................................................................................133 (cid:2)Chapter 6: The Utility Database........................................................................................135 Start with Checklists..........................................................................................................136 Daily Checklist Items..................................................................................................................................136 Longer-Term Checklist Items ......................................................................................................................137 Utility Database Layout.......................................................................................................138 Data Storage...............................................................................................................................................138 Using Schemas...........................................................................................................................................140 Using Data Referential Integrity .................................................................................................................140 Creating the Utility Database ......................................................................................................................140 Table Structure...........................................................................................................................................141 Gathering Data....................................................................................................................143 System Tables.............................................................................................................................................143 Extended Stored Procedures ......................................................................................................................143 CLR.............................................................................................................................................................144 DMVs..........................................................................................................................................................144 Storage.......................................................................................................................................................144 Processors..................................................................................................................................................146 Error Logs...................................................................................................................................................148 Indexes.......................................................................................................................................................149 Stored Procedure Performance ..................................................................................................................151 Failed Jobs.................................................................................................................................................152 Reporting Services.....................................................................................................................................153 Mirroring.....................................................................................................................................................154 vii ■CONTENTS AlwaysOn....................................................................................................................................................156 Managing Key Business Indicators.............................................................................................................156 Using the Data....................................................................................................................158 Automating the Data Collection ..................................................................................................................158 Scheduling the Data Collection ...................................................................................................................159 Conclusion..........................................................................................................................160 (cid:2)Chapter 7: Indexing Outside the Bubble...........................................................................161 The Environment Bubble....................................................................................................162 Identifying Missing Indexes ........................................................................................................................162 Index Tuning a Workload .............................................................................................................................170 The Business Bubble..........................................................................................................191 Index Business Usage.................................................................................................................................191 Data Integrity..............................................................................................................................................193 Conclusion..........................................................................................................................195 (cid:2)Chapter 8: Release Management......................................................................................197 My Release Management Process.....................................................................................197 A Change Is Requested.......................................................................................................198 Release Process Overview .........................................................................................................................199 Considerations............................................................................................................................................199 Documents.........................................................................................................................207 Release Notes.............................................................................................................................................208 Release Plan Template and Release Plans .................................................................................................212 Document Repository.................................................................................................................................219 Conclusion..........................................................................................................................219 (cid:2)Chapter 9: Compliance and Auditing................................................................................221 Compliance.........................................................................................................................221 Sarbanes-Oxley..........................................................................................................................................221 Health Insurance Portability and Accountability Act ...................................................................................223 New Auditing Features in SQL Server 2012.......................................................................224 Server-Level Auditing for the Standard Edition ..........................................................................................225 Audit Log Failure Options ...........................................................................................................................225 Maximum Rollover Files .............................................................................................................................225 User-Defined Auditing.................................................................................................................................225 Audit Filtering.............................................................................................................................................225 Auditing ............................................................................................................................226 viii ■CONTENTS Server Audit........................................................................................................................226 Server Audit Specification..................................................................................................228 Database Audit Specification..............................................................................................230 Query the Audit File............................................................................................................231 Pro Tip: Alert on Audit Events..............................................................................................232 Conclusion..........................................................................................................................234 (cid:2)Chapter 10: Automating Administration...........................................................................235 Tools for Automation ..........................................................................................................235 Performance Monitor..................................................................................................................................235 Dynamic Management Views ....................................................................................................................237 SQL Server Agent .......................................................................................................................................238 Maintenance Plans.....................................................................................................................................252 SQL Server Integration Services ................................................................................................................259 PowerShell .................................................................................................................................................262 What to Automate ..............................................................................................................263 Monitoring ................................................................................................................................................264 Backups and Restores................................................................................................................................267 Database Integrity .....................................................................................................................................269 Index Maintenance ....................................................................................................................................269 Statistics Maintenance ..............................................................................................................................270 Conclusion ........................................................................................................................270 (cid:2)Chapter 11: The Fluid Dynamics of SQL Server Data Movement.....................................271 Why the Need for Replicating Data?...................................................................................271 SQL Server Solutions..........................................................................................................273 Replication..................................................................................................................................................274 Log Shipping...............................................................................................................................................278 Database Mirroring.....................................................................................................................................280 AlwaysOn ...................................................................................................................................................282 Failover Clustering .....................................................................................................................................284 Custom ETL Using SQL Server Integration Services ...................................................................................286 Bulk Copy Process......................................................................................................................................287 Choosing the Right Deployment.........................................................................................288 Keeping the Data Consistent..............................................................................................290 Conclusion..........................................................................................................................292 ix ■CONTENTS (cid:2)Chapter 12: Windows Azure SQL Database for DBAs......................................................293 SQL Database Architecture ................................................................................................294 Infrastructure..............................................................................................................................................294 Availability and Failover ..............................................................................................................................295 Hardware....................................................................................................................................................295 Differences with SQL Server..............................................................................................296 Database Components................................................................................................................................296 Management Platform................................................................................................................................297 Security......................................................................................................................................................298 Other Important Information .......................................................................................................................299 Federations.........................................................................................................................300 Key Terms...................................................................................................................................................300 T-SQL Changes for Federations ..................................................................................................................301 Federation Example....................................................................................................................................302 Limitations..................................................................................................................................................303 Troubleshooting Performance Issues.................................................................................304 DMVs Available...........................................................................................................................................304 Execution Plans..........................................................................................................................................305 Performance Dashboard .............................................................................................................................306 Related Services.................................................................................................................308 Windows Azure SQL Reporting ...................................................................................................................308 Windows Azure SQL Data Sync ...................................................................................................................309 Import/Export Feature.................................................................................................................................310 Cost of SQL Database.........................................................................................................311 Conclusion..........................................................................................................................312 (cid:2)Chapter 13: I/O: The Untold Story ....................................................................................313 The Basics..........................................................................................................................314 Monitoring..........................................................................................................................314 Considerations....................................................................................................................315 Tactical...............................................................................................................................317 Code or Disk?.....................................................................................................................321 Times Have Changed..........................................................................................................323 Getting to the Data.............................................................................................................324 Addressing a Query............................................................................................................328 Environmental Considerations............................................................................................331 x