Professional SQL Server 2012 Internals and Troubleshooting
Home > Computing and Information Technology > Databases > Professional SQL Server 2012 Internals and Troubleshooting
Professional SQL Server 2012 Internals and Troubleshooting

Professional SQL Server 2012 Internals and Troubleshooting


     0     
5
4
3
2
1



Out of Stock


Notify me when this book is in stock
X
About the Book

Hands-on troubleshooting methods on the most recent release of SQL Server The 2012 release of SQL Server is the most significant one since 2005 and introduces an abundance of new features. This critical book provides in-depth coverage of best practices for troubleshooting performance problems based on a solid understanding of both SQL Server and Windows internals and shows experienced DBAs how to ensure reliable performance. The team of authors shows you how to master the use of specific troubleshooting tools and how to interpret their output so you can quickly identify and resolve any performance issue on any server running SQL Server. Covers the core technical topics required to understand how SQL Server and Windows should be working Shares best practices so that you know how to proactively monitor and avoid problems Shows how to use tools to quickly gather, analyze, and effectively respond to the source of a system-wide performance issue Professional SQL Server 2012 Internals and Troubleshooting helps you to quickly become familiar with the changes of this new release so that you can best handle database performance and troubleshooting.

Table of Contents:
Introduction xxix Part I: Internals Chapter 1: SQL Server Architecture 3 Introduction 3 Database Transactions 4 ACID Properties 4 SQL Server Transactions 5 The Life Cycle of a Query 5 The Relational and Storage Engines 6 The Buffer Pool 6 A Basic SELECT Query 7 A Simple Update Query 15 Recovery 18 SQL Server’s Execution Model and the SQLOS 22 Execution Model 22 The SQLOS 25 Summary 26 Chapter 2: Demystifying Hardware 29 The Importance of Hardware 29 How Workload Affects Hardware and Storage Considerations 30 Workload Types 30 Server Model Selection 32 Server Model Evolution 33 Processor Vendor Selection 35 Intel Processors 35 AMD Processors and Numbering 43 Choosing and Configuring Hardware for Redundancy 46 Hardware Comparison Tools 48 TPC-E Benchmark 48 Geekbench Benchmark 50 Summary 51 Chapter 3: Understanding Memory 53 Introduction 53 Physical and Virtual Memory 54 Physical Memory 54 Maximum Supported Physical Memory 55 Virtual Memory 56 NUMA 59 SQL Server Memory 63 Memory Nodes 64 Clerks, Caches, and the Buffer Pool 64 Optimizing SQL Server Memory Configuration 70 Min and Max Server Memory 70 Lock Pages in Memory 72 Optimize for Ad-Hoc Workloads 74 Summary 76 Chapter 4: Storage Systems 77 Introduction 77 SQL Server I/O 78 Storage Technology 78 SQL Server and the Windows I/O Subsystem 82 Choosing the Right Storage Networks 84 Shared Storage Arrays 86 Capacity Optimization 86 Storage Tiering 88 Data Replication 89 Remote Data Replication 92 Windows Failover Clustering 93 SQL Server AlwaysOn Availability Groups 94 Risk Mitigation Planning 94 Measuring Performance 95 Storage Performance Counters 96 Disk Drive Performance 97 Sequential Disk Access 100 Server Queues 101 File Layout 101 Partition Alignment 103 NTFS Allocation Unit Size 104 Flash Storage 104 Storage Performance Testing 106 Summary 110 Chapter 5: Query Processing and Execution 111 Introduction 111 Query Processing 112 Parsing 112 Algebrizing 112 Query Optimization 113 Parallel Plans 114 Algebrizer Trees 115 sql_handle or plan_handle 115 Understanding Statistics 116 Plan Caching and Recompilation 117 Influencing Optimization 123 Query Plans 129 Query Plan Operators 132 Reading Query Plans 135 Executing Your Queries 140 SQLOS 140 Summary 147 Chapter 6: Locking and Concurrency 149 Overview 149 Transactions 150 A is for Atomic 150 C is for Consistent 151 I is for Isolated 151 D is for Durable 151 Database Transactions 151 Atomicity 151 Consistency 152 Isolation 152 Durability 152 The Dangers of Concurrency 153 Lost Updates 153 Dirty Reads 155 Non-Repeatable Reads 156 Phantom Reads 158 Double Reads 161 Halloween Effect 162 Locks 163 Monitoring Locks 163 Lock Resources 165 Lock Modes 167 Compatibility Matrix 173 Lock Escalation 174 Deadlocks 175 Isolation Levels 175 Serializable 176 Repeatable Read 177 Read Committed 177 Read Uncommitted/NOLOCK 178 Snapshot 178 Read Committed Snapshot 178 Summary 179 Chapter 7: Latches and Spinlocks 181 Overview 181 Symptoms 182 Recognizing Symptoms 182 Measuring Latch Contention 183 Measuring Spinlock Contention 184 Contention Indicators 185 Susceptible Systems 185 Understanding Latches and Spinlocks 186 Definitions 186 Latching Example 187 Latch Types 194 Latch Modes 194 NL 195 KP 195 SH 195 UP 195 EX 195 DT 195 Latch Compatibility 196 Grant Order 196 Latch Waits 197 SuperLatches/Sublatches 198 Monitoring Latches and Spinlocks 199 DMVs 199 Performance Monitor 201 Extended Events 202 Latch Contention Examples 203 Inserts When the Clustered Index Key is an Identity Field 203 Queuing 205 UP Latches in tempdb 208 Spinlock Contention in Name Resolution 209 Summary 209 Chapter 8: Knowing Tempdb 211 Introduction 211 Overview and Usage 212 User Temporary Objects 213 Internal Temporary Objects 217 The Version Store 217 Troubleshooting Common Issues 220 Latch Contention 220 Monitoring Tempdb I/O Performance 229 Troubleshooting Space Issues 231 Configuration Best Practices 232 Tempdb File Placement 232 Tempdb Initial Sizing and Autogrowth 234 Confi guring Multiple Tempdb Data Files 237 Summary 237 Part II: Troubleshooting Tools and Lessons From the Field Chapter 9: Troubleshooting Methodology and Practices 241 Introduction 241 Approaching Problems 242 Ten Steps to Successful Troubleshooting 242 Behavior and Attitude 244 Success Criteria 245 Working with Stakeholders 245 Service-Level Agreements 246 Engaging External Help 247 Defining the Problem 248 Guidelines for Identifying the Problem 248 Isolating the Problem 249 Performance Bottlenecks 250 Data Collection 252 Focused Data Collection 253 Understanding Data Gathering 253 Tools and Utilities 254 Data Analysis 255 Validating and Implementing Resolution 256 Validating Changes 256 Testing Changes in Isolation 256 Implementing Resolution 257 Summary 257 Chapter 10: Viewing Server Performance With PerfMon and the Pal Tool 259 Introduction 259 Performance Monitor Overview 260 Reliability and Performance Monitor 260 New PerfMon Counters for SQL Server 2012 263 Getting Started with PerfMon 268 Getting More from Performance Monitor 278 Bottlenecks and SQL Server 278 Prescriptive Guidance 279 Wait Stats Analysis 284 Getting a Performance Baseline 285 Performance Analysis of Logs 285 Getting Started with PAL 285 Other PerfMon Log Analysis Tools 289 Using SQL Server to Analyze PerfMon Logs 289 Combining PerfMon Logs and SQL Profiler Traces 289 Using Relog 290 Using LogMan 291 Using LogParser 293 Summary 293 Chapter 11: Consolidating Data Capture With SQLdiag 295 The Data Collection Dilemma 295 An Approach to Data Collection 296 Getting Friendly with SQLdiag 297 Using SQLdiag in Snapshot Mode 298 Using SQLdiag as a Command-line Application 299 Using SQLdiag as a Service 303 Using SQLdiag Configuration Manager 305 Configuring SQLdiag Data Collection Using Diag Manager 307 Adding Trace Filters to a SQLdiag Configuration 310 Employing Best Practices 318 Gearing Up for Long-Term Data Collection 319 Filtering Out the Noise 320 Alert-Driven Data Collection with SQLdiag 322 Summary 323 Chapter 12: Bringing It All Together With SQL Nexus 325 Introducing SQL Nexus 325 Getting Familiar with SQL Nexus 326 Prerequisites 326 Loading Data into a Nexus Database 328 Analyzing the Aggregated Data 331 Customizing SQL Nexus 340 Using ReadTrace.exe 341 Building Custom Reports for SQL Nexus 342 Running SQL Nexus Using the Command Prompt 342 Creating Your Own Tables in the SQL Nexus Database 342 Writing Your Own Queries 344 The OSTRESS Executable 344 Resolving Common Issues 346 Issue #1 346 Issue #2 346 Issue #3 346 Issue #4 347 Summary 348 Chapter 13: Diagnosing SQL Server 2012 Using Extended Events 349 Introduction to Extended Events 349 Getting Familiar with Extended Events 350 Why You Should Be Using Extended Events 351 SQL Server Roadmap 351 Graphical Tools 351 Low Impact 351 When You Might Use Extended Events 352 What Are Extended Events? 352 Where the Name Extended Events Comes From 353 Extended Events Terminology 354 Creating Extended Events Sessions in SQL Server 2012 363 Introduction to the New Session Form 363 Monitoring Server Logins 366 Monitoring for Page Splits with Extended Events 367 Counting the Number of Locks Acquired per Object 369 Creating Sessions Using T-SQL 370 Viewing Data Captured by Extended Events 371 Viewing Event File Data 371 Summary 376 Chapter 14: Enhancing Your Troubleshooting Toolset With PowerShell 379 Introducing PowerShell 379 Getting Started with PowerShell 380 The PowerShell Environment 381 The Basics — Cmdlets, Variables, Advanced Functions, and Modules 383 Working Remotely 390 What’s New in SQL Server 2012 391 Using PowerShell to Investigate Server Issues 393 Interrogating Disk Space Utilization 393 Interrogating Current Server Activity 394 Interrogating for Warnings and Errors 396 Interrogating Server Performance 396 Proactively Tuning SQL Server Performance with PowerShell 397 Index Maintenance 397 Managing Disk Space Utilization of Backups 398 Extracting DDL Using SMO 398 Scheduling Script Execution 403 Summary 404 Chapter 15: Delivering A SQL Server Health Check 405 The Importance of a SQL Server Health Check 405 Running DMV and DMF Queries 406 SQL Server Builds 408 Database-Level Queries 426 Summary 442 Chapter 16: Delivering Manageability and Performance 445 Improve Efficiency with SQL Server Manageability Features 445 Manageability Enhancements in SQL Server 2012 446 Policy-Based Management 447 Overview 447 Other Microsoft Tools for Managing SQL Server 460 System Center Advisor 461 System Center Operations Manager 464 Summary 466 Chapter 17: Running SQL Server In a Virtual Environment 469 The Shift to Server Virtualization 469 An Overview of Virtualization 470 History of Virtualization 471 The Breadth of Virtualization 472 Platform Virtualization 472 Cloud Computing 473 Why Virtualize a Server? 473 Business Benefits 474 Technical Benefits 474 Encapsulation 475 SQL Server 2012 and Virtualization 476 Limitations of Virtualization 477 Common Virtualization Products 477 VMware 477 Microsoft Hyper-V 478 Xen 479 Hardware Support for Virtualization 479 Virtualization Concepts 480 Host Server 480 Hypervisor 480 Virtual Server (or Guest Server or Virtual Machine) 482 Extended Features of Virtualization 483 Snapshotting 483 High-Availability Features 483 Online Migration 484 Highly Available Virtual Servers 486 Host and Guest Clustering 487 Deploying SQL Server with Virtualization’s High-Availability Features 487 Managing Contention 488 Good Contention 488 Bad Contention 488 Demand-Based Memory Allocation 489 Weighting 490 Identifying Candidates for Virtualization 491 Guiding Principles 491 Server Workload 491 Gathering Sizing Data 492 Sizing Tools 493 Non-Performance Related Requirements 493 Architecting Successful Virtual Database Servers 494 Architecting Virtual Database Servers vs. Physical Database Servers 494 Virtual Database Server Design 495 Monitoring Virtualized Database Servers 502 Information and Misinformation from Performance Monitor 503 Summary 507 Index 509

About the Author :
Christian Bolton is the Technical Director for Coeo Ltd. and a Microsoft Certified Architect, Master, and MVP for SQL Server. Justin Langford is a Director at Coeo Ltd., a SQL Server MVP, and an author. Glenn Berry is a Principal Consultant at SQLSkills.com, a SQL Server MVP, and an author. Gavin Payne is a Senior Consultant for Coeo Ltd. and a Microsoft Certified Master for SQL Server. Amit Banerjee is a Premier Field Engineer at Microsoft and the owner of TroubleshootingSQL.com. Rob Farley is Owner/Principal of LobsterPot Solutions, a PASS Director, and a SQL Server MVP. Wrox Professional guides are written by working developers to address everyday needs. They provide examples, practical solutions, and expert education in new technologies, all designed to help programmers do a better job.


Best Sellers


Product Details
  • ISBN-13: 9781118240274
  • Publisher: John Wiley & Sons Inc
  • Publisher Imprint: Wrox Press
  • Language: English
  • ISBN-10: 1118240278
  • Publisher Date: 18 Oct 2012
  • Binding: Digital (delivered electronically)
  • No of Pages: 576


Similar Products

Add Photo
Add Photo

Customer Reviews

REVIEWS      0     
Click Here To Be The First to Review this Product
Professional SQL Server 2012 Internals and Troubleshooting
John Wiley & Sons Inc -
Professional SQL Server 2012 Internals and Troubleshooting
Writing guidlines
We want to publish your review, so please:
  • keep your review on the product. Review's that defame author's character will be rejected.
  • Keep your review focused on the product.
  • Avoid writing about customer service. contact us instead if you have issue requiring immediate attention.
  • Refrain from mentioning competitors or the specific price you paid for the product.
  • Do not include any personally identifiable information, such as full names.

Professional SQL Server 2012 Internals and Troubleshooting

Required fields are marked with *

Review Title*
Review
    Add Photo Add up to 6 photos
    Would you recommend this product to a friend?
    Tag this Book Read more
    Does your review contain spoilers?
    What type of reader best describes you?
    I agree to the terms & conditions
    You may receive emails regarding this submission. Any emails will include the ability to opt-out of future communications.

    CUSTOMER RATINGS AND REVIEWS AND QUESTIONS AND ANSWERS TERMS OF USE

    These Terms of Use govern your conduct associated with the Customer Ratings and Reviews and/or Questions and Answers service offered by Bookswagon (the "CRR Service").


    By submitting any content to Bookswagon, you guarantee that:
    • You are the sole author and owner of the intellectual property rights in the content;
    • All "moral rights" that you may have in such content have been voluntarily waived by you;
    • All content that you post is accurate;
    • You are at least 13 years old;
    • Use of the content you supply does not violate these Terms of Use and will not cause injury to any person or entity.
    You further agree that you may not submit any content:
    • That is known by you to be false, inaccurate or misleading;
    • That infringes any third party's copyright, patent, trademark, trade secret or other proprietary rights or rights of publicity or privacy;
    • That violates any law, statute, ordinance or regulation (including, but not limited to, those governing, consumer protection, unfair competition, anti-discrimination or false advertising);
    • That is, or may reasonably be considered to be, defamatory, libelous, hateful, racially or religiously biased or offensive, unlawfully threatening or unlawfully harassing to any individual, partnership or corporation;
    • For which you were compensated or granted any consideration by any unapproved third party;
    • That includes any information that references other websites, addresses, email addresses, contact information or phone numbers;
    • That contains any computer viruses, worms or other potentially damaging computer programs or files.
    You agree to indemnify and hold Bookswagon (and its officers, directors, agents, subsidiaries, joint ventures, employees and third-party service providers, including but not limited to Bazaarvoice, Inc.), harmless from all claims, demands, and damages (actual and consequential) of every kind and nature, known and unknown including reasonable attorneys' fees, arising out of a breach of your representations and warranties set forth above, or your violation of any law or the rights of a third party.


    For any content that you submit, you grant Bookswagon a perpetual, irrevocable, royalty-free, transferable right and license to use, copy, modify, delete in its entirety, adapt, publish, translate, create derivative works from and/or sell, transfer, and/or distribute such content and/or incorporate such content into any form, medium or technology throughout the world without compensation to you. Additionally,  Bookswagon may transfer or share any personal information that you submit with its third-party service providers, including but not limited to Bazaarvoice, Inc. in accordance with  Privacy Policy


    All content that you submit may be used at Bookswagon's sole discretion. Bookswagon reserves the right to change, condense, withhold publication, remove or delete any content on Bookswagon's website that Bookswagon deems, in its sole discretion, to violate the content guidelines or any other provision of these Terms of Use.  Bookswagon does not guarantee that you will have any recourse through Bookswagon to edit or delete any content you have submitted. Ratings and written comments are generally posted within two to four business days. However, Bookswagon reserves the right to remove or to refuse to post any submission to the extent authorized by law. You acknowledge that you, not Bookswagon, are responsible for the contents of your submission. None of the content that you submit shall be subject to any obligation of confidence on the part of Bookswagon, its agents, subsidiaries, affiliates, partners or third party service providers (including but not limited to Bazaarvoice, Inc.)and their respective directors, officers and employees.

    Accept

    New Arrivals


    Inspired by your browsing history


    Your review has been submitted!

    You've already reviewed this product!