Professional SQL Server® 2008 Internals and Troubleshooting

Professional SQL Server® 2008 Internals and Troubleshooting

Released January 2010
Publisher(s): Wrox
ISBN: 9780470484289

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

A hands-on resource for SQL Server 2008 troubleshooting methods and tools

SQL Server administrators need to ensure that SQL Server remains running 24/7. Authored by leading SQL Server experts and MVPs, this book provides in-depth coverage of best practices based on a deep understanding of the internals of both SQL Server and the Windows operating system.

You'll get a thorough look at the SQL Server database architecture and internals as well as Windows OS internals so that you can approach troubleshooting with a solid grasp of the total processing environment. Armed with this comprehensive understanding, readers will then learn how to use a suite of tools for troubleshooting performance problems whether they originate on the database server or operating system side.

Topics Covered:

  • SQL Server Architecture

  • Understanding Memory

  • SQL Server Waits and Extended Events

  • Working with Storage

  • CPU and Query Processing

  • Locking and Latches

  • Knowing Tempdb

  • Defining Your Approach To Troubleshooting

  • Viewing Server Performance with PerfMon and the PAL Tool

  • Tracing SQL Server with SQL Trace and Profiler

  • Consolidating Data Collection with SQLDiag and the PerfStats Script

  • Introducing RML Utilities for Stress Testing and Trace File Analysis

  • Bringing It All Together with SQL Nexus

  • Using Management Studio Reports and the Performance Dashboard

  • Using SQL Server Management Data Warehouse

  • Shortcuts to Efficient Data Collection and Quick Analysis

Note: CD-ROM/DVD and other supplementary materials are not included as part of eBook file.

Table of contents

  1. Copyright
  2. ABOUT THE AUTHORS
  3. CREDITS
  4. ACKNOWLEDGMENTS
  5. INTRODUCTION
    1. WHO THIS BOOK IS FOR
    2. WHAT THIS BOOK COVERS
      1. Understanding Internals
      2. Additional Troubleshooting Tools
    3. HOW THIS BOOK IS STRUCTURED
        1. Chapter 1: SQL Server Architecture
        2. Chapter 2: Understanding Memory
        3. Chapter 3: SQL Server Waits and Extended Events
        4. Chapter 4: Working with Storage
        5. Chapter 5: CPU and Query Processing
        6. Chapter 6: Locking and Latches
        7. Chapter 7: Knowing Tempdb
      2. The Troubleshooting Tools Chapters
        1. Chapter 8: Defining Your Approach to Troubleshooting
        2. Chapter 9: Viewing Server Performance with PerfMon and the PAL Tool
        3. Chapter 10: Tracing SQL Server with SQL Trace and Profiler
        4. Chapter 11: Consolidating Data Collection with SQLDiag and the PerfStats Script
        5. Chapter 12: Introducing RML Utilities for Stress Testing and Trace File Analysis
        6. Chapter 13: Bringing It All Together with SQL Nexus
        7. Chapter 14: Using Management Studio Reports and the Performance Dashboard
        8. Chapter 15: Using SQL Server Management Data Warehouse
        9. Chapter 16: Shortcuts to Efficient Data Collection and Quick Analysis
    4. CONVENTIONS
    5. SOURCE CODE
    6. ERRATA
    7. P2P.WROX.COM
  6. 1. SQL Server Architecture
    1. 1.1. DATABASE TRANSACTIONS
      1. 1.1.1. ACID Properties
        1. 1.1.1.1. Atomicity
        2. 1.1.1.2. Consistency
        3. 1.1.1.3. Isolation
        4. 1.1.1.4. Durability
      2. 1.1.2. SQL Server Transactions
    2. 1.2. THE LIFE CYCLE OF A QUERY
      1. 1.2.1. The Relational and Storage Engines
      2. 1.2.2. The Buffer Pool
      3. 1.2.3. A Basic Select Query
        1. 1.2.3.1. SQL Server Network Interface
        2. 1.2.3.2. TDS (Tabular Data Stream) Endpoints
        3. 1.2.3.3. Protocol Layer
        4. 1.2.3.4. Command Parser
          1. 1.2.3.4.1. Plan Cache
        5. 1.2.3.5. Optimizer
        6. 1.2.3.6. Query Executor
        7. 1.2.3.7. Access Methods
        8. 1.2.3.8. Buffer Manager
        9. 1.2.3.9. Data Cache
        10. 1.2.3.10. A Basic select Statement Life Cycle Summary
      4. 1.2.4. A Simple Update Query
        1. 1.2.4.1. Transaction Manager
        2. 1.2.4.2. Buffer Manager
      5. 1.2.5. Recovery
        1. 1.2.5.1. Dirty Pages
        2. 1.2.5.2. Lazywriter
        3. 1.2.5.3. Checkpoint Process
          1. 1.2.5.3.1. Recovery Interval
        4. 1.2.5.4. Recovery Models
          1. 1.2.5.4.1. Full
          2. 1.2.5.4.2. Bulk-Logged
          3. 1.2.5.4.3. Simple
    3. 1.3. THE SQLOS (SQL OPERATING SYSTEM)
    4. 1.4. SUMMARY
  7. 2. Understanding Memory
    1. 2.1. WINDOWS AND MEMORY
      1. 2.1.1. Physical Memory
        1. 2.1.1.1. SIMMs and DIMMs
        2. 2.1.1.2. DRAM
        3. 2.1.1.3. SDRAM
        4. 2.1.1.4. DDR SDRAM
        5. 2.1.1.5. Dual-Channel Memory
        6. 2.1.1.6. Registered and Unbuffered Modules
        7. 2.1.1.7. Error-Correcting Code (ECC) Memory
        8. 2.1.1.8. FB-DIMMs
        9. 2.1.1.9. Maximum Supported Physical Memory
      2. 2.1.2. Virtual Address Space
      3. 2.1.3. Virtual Memory Manager
      4. 2.1.4. Tuning 32-Bit Systems
        1. 2.1.4.1. /3GB and increaseUserVA
          1. 2.1.4.1.1. Paged Pool and Nonpaged Pool
          2. 2.1.4.1.2. System PTEs
        2. 2.1.4.2. /PAE Switch
        3. 2.1.4.3. Address Windowing Extensions (AWE)
        4. 2.1.4.4. /PAE and /3GB Together?
          1. 2.1.4.4.1. Windows Server 2008
        5. 2.1.4.5. MemToLeave, or ReservedMemory
          1. 2.1.4.5.1. Note on 64-Bit Systems
      5. 2.1.5. Tuning 64-Bit Systems
        1. 2.1.5.1. AWE on 64-Bit Systems
    2. 2.2. SQL SERVER MEMORY
      1. 2.2.1. Memory Nodes
      2. 2.2.2. Memory Clerks, Caches, and the Buffer Pool
        1. 2.2.2.1. Memory Clerks
        2. 2.2.2.2. Caches
        3. 2.2.2.3. Buffer Pool
        4. 2.2.2.4. Min and Max Server Memory
          1. 2.2.2.4.1. Looking at the Buffer Pool's Maximum Usage
          2. 2.2.2.4.2. Determining the Maximum Potential for Non-Buffer Pool Usage
        5. 2.2.2.5. Plan Cache
        6. 2.2.2.6. Query Memory/Workspace Memory
          1. 2.2.2.6.1. The Query Wait Option
          2. 2.2.2.6.2. Query Memory Diagnostics
    3. 2.3. SUMMARY
  8. 3. SQL Server Waits and Extended Events
    1. 3.1. WAITS
      1. 3.1.1. SQL Server Execution Model
      2. 3.1.2. Understanding Wait Statistics
      3. 3.1.3. Wait Types
        1. 3.1.3.1. Resource Wait Types
          1. 3.1.3.1.1. Memory Waits
          2. 3.1.3.1.2. Disk I/O Waits
          3. 3.1.3.1.3. Blocking Waits
          4. 3.1.3.1.4. CPU
          5. 3.1.3.1.5. Network Waits
        2. 3.1.3.2. System Waits
        3. 3.1.3.3. Preemptive Wait Types
    2. 3.2. EXTENDED EVENTS
      1. 3.2.1. Architecture
        1. 3.2.1.1. Extended Events Engine
        2. 3.2.1.2. Event Firing
        3. 3.2.1.3. Dynamic Management Views and Functions
          1. 3.2.1.3.1. Metadata Views
          2. 3.2.1.3.2. Session Definition Views
          3. 3.2.1.3.3. Active Session Views
        4. 3.2.1.4. Extended Event Metadata
          1. 3.2.1.4.1. Packages
          2. 3.2.1.4.2. Events
          3. 3.2.1.4.3. Actions
          4. 3.2.1.4.4. Targets
          5. 3.2.1.4.5. Predicates
          6. 3.2.1.4.6. Maps
      2. 3.2.2. Event Sessions
        1. 3.2.2.1. system_health Session
        2. 3.2.2.2. Managing Extended Events Sessions
      3. 3.2.3. Using the Extended Events Manager
    3. 3.3. EXAMINING WAITS WITH EXTENDED EVENTS
    4. 3.4. SUMMARY
  9. 4. Working with Storage
    1. 4.1. TYPES OF STORAGE
      1. 4.1.1. Understanding Individual Drives
        1. 4.1.1.1. Parallel ATA (PATA, IDE, EIDE) Magnetic Drives
        2. 4.1.1.2. Serial ATA (SATA) Magnetic Drives
        3. 4.1.1.3. Serial ATA (SATA) Solid-State Drives
        4. 4.1.1.4. Serial Attached SCSI (SAS) Magnetic Drives
        5. 4.1.1.5. Fibre Channel (FC) Magnetic Drives
      2. 4.1.2. Protecting Data with RAID
        1. 4.1.2.1. RAID 1: Mirroring
        2. 4.1.2.2. RAID 0: Striping
        3. 4.1.2.3. RAID 5 and 6: Striping with Parity
        4. 4.1.2.4. RAID 0+1 and RAID 10: Mirroring and Striping
        5. 4.1.2.5. Choosing between Performance and Price
      3. 4.1.3. Direct Attached Storage
      4. 4.1.4. Storage Area Networks
        1. 4.1.4.1. Fibre Channel Storage Area Networks
        2. 4.1.4.2. iSCSI Storage Area Networks
        3. 4.1.4.3. Multipathing: Multiple Routes to the Storage
        4. 4.1.4.4. Choosing Shared or Dedicated Drives
    2. 4.2. STORAGE PERFORMANCE TESTING
      1. 4.2.1. Choosing a Storage Testing Tool
        1. 4.2.1.1. Using SQLIO
        2. 4.2.1.2. Using SQLIOSim
      2. 4.2.2. Interpreting Storage Test Results
        1. 4.2.2.1. Hitting the 100/200/400MB/sec Wall
        2. 4.2.2.2. Cache Is Not Always King
        3. 4.2.2.3. Knowing What's Happening in the Black Box
        4. 4.2.2.4. Testing the Worst-Case Scenario
        5. 4.2.2.5. Wash, Rinse, and Repeat
    3. 4.3. CONFIGURING SOFTWARE FOR STORAGE
      1. 4.3.1. Configuring Windows Server
        1. 4.3.1.1. Partition Alignment
        2. 4.3.1.2. NTFS Allocation Unit Size
        3. 4.3.1.3. Configuring HBA Queue Depth
        4. 4.3.1.4. Adapting to Virtual Servers
      2. 4.3.2. Configuring SQL Server 2008
        1. 4.3.2.1. Choosing Which Files to Place on Which Disks
        2. 4.3.2.2. Using Compression to Gain Performance
      3. 4.3.3. Corruption
        1. 4.3.3.1. Detecting Corruption
        2. 4.3.3.2. Recovering from Corruption
          1. 4.3.3.2.1. Fix the Underlying Cause First
          2. 4.3.3.2.2. Focus on the Worst Problem First
          3. 4.3.3.2.3. Dealing with Damaged System Tables
          4. 4.3.3.2.4. Dealing with Corrupt Clustered Indexes
          5. 4.3.3.2.5. Dealing with Data Purity Errors
          6. 4.3.3.2.6. Dealing With Corrupt Non-Clustered Indexes
          7. 4.3.3.2.7. The Final Steps After Any Repairs
    4. 4.4. SUMMARY
  10. 5. CPU and Query Processing
    1. 5.1. THE CPU
    2. 5.2. THE CPU AND SQL SERVER
      1. 5.2.1. Processor Speed
      2. 5.2.2. Hyper-Threading
      3. 5.2.3. Licensing with Multicore and Hyper-Threading
      4. 5.2.4. Cache
      5. 5.2.5. Multicore Processors
        1. 5.2.5.1. Multicore Terminology
    3. 5.3. SYSTEM ARCHITECTURE
      1. 5.3.1. Symmetric Multiprocessing
      2. 5.3.2. NonUniform Memory Access
        1. 5.3.2.1. AMD or Intel?
        2. 5.3.2.2. Server, Workstation, or Desktop
    4. 5.4. QUERY PROCESSING
      1. 5.4.1. Parsing
      2. 5.4.2. Binding
    5. 5.5. QUERY OPTIMIZATION
      1. 5.5.1. Parallel Plans
      2. 5.5.2. Algebrizer Trees
      3. 5.5.3. sql_handle or plan_handle
      4. 5.5.4. Statistics
        1. 5.5.4.1. Auto_create_statistics
        2. 5.5.4.2. Auto_update_statistics
        3. 5.5.4.3. Auto_update_statistics_asynchronously
      5. 5.5.5. Plan Caching and Recompilation
        1. 5.5.5.1. Parameterization
        2. 5.5.5.2. Looking into the Plan Cache
        3. 5.5.5.3. Compilation/Recompilation
      6. 5.5.6. Influencing Optimization
        1. 5.5.6.1. Query Hints
          1. 5.5.6.1.1. FAST number_rows
          2. 5.5.6.1.2. {Loop | Merge | Hash } JOIN
          3. 5.5.6.1.3. MAXDOP n
          4. 5.5.6.1.4. OPTIMIZE FOR
          5. 5.5.6.1.5. RECOMPILE
          6. 5.5.6.1.6. USE PLAN N 'xml plan'
        2. 5.5.6.2. Plan Guides
    6. 5.6. QUERY PLANS
      1. 5.6.1. Query Plan Operators
        1. 5.6.1.1. Join Operators
          1. 5.6.1.1.1. Nested Loop
          2. 5.6.1.1.2. Merge
          3. 5.6.1.1.3. Hash
        2. 5.6.1.2. Spool Operators
        3. 5.6.1.3. Scan and Seek Operators
        4. 5.6.1.4. Lookup Operators
      2. 5.6.2. Reading Query Plans
    7. 5.7. EXECUTING YOUR QUERIES
      1. 5.7.1. SQLOS
        1. 5.7.1.1. Memory Nodes
          1. 5.7.1.1.1. Soft NUMA
        2. 5.7.1.2. CPU Nodes
          1. 5.7.1.2.1. Processor Affinity
        3. 5.7.1.3. Schedulers
        4. 5.7.1.4. Tasks
        5. 5.7.1.5. Workers
        6. 5.7.1.6. Threads
        7. 5.7.1.7. Scheduling
    8. 5.8. SUMMARY
  11. 6. Locking and Latches
    1. 6.1. TRANSACTIONS
      1. 6.1.1. Atomic
      2. 6.1.2. Consistent
      3. 6.1.3. Isolated
      4. 6.1.4. Durable
    2. 6.2. CONSEQUENCE OF CONCURRENT ACCESS
      1. 6.2.1. Lost Updates
      2. 6.2.2. Dirty Reads
      3. 6.2.3. Non-Repeatable Reads
      4. 6.2.4. Phantom Reads
      5. 6.2.5. Double Reads
      6. 6.2.6. Halloween Effect
    3. 6.3. LOCKING
      1. 6.3.1. Viewing Locks — sys.dm_tran_locks
      2. 6.3.2. Lock Granularity
      3. 6.3.3. Lock Modes
        1. 6.3.3.1. Shared Lock Mode (S)
        2. 6.3.3.2. Update Lock Mode (U)
        3. 6.3.3.3. Exclusive Lock Mode (X)
        4. 6.3.3.4. Schema Lock Modes (Sch-S), (Sch-M)
        5. 6.3.3.5. Intent Lock Modes (IS), (IU), (IX)
        6. 6.3.3.6. Conversion Lock Modes (SIX), (SIU), (UIX)
        7. 6.3.3.7. Bulk Update Lock Mode (BU)
      4. 6.3.4. Lock Hierarchy
      5. 6.3.5. Lock Compatibility
      6. 6.3.6. Lock Escalation
        1. 6.3.6.1. Controlling Lock Escalation
        2. 6.3.6.2. Detecting Lock Escalation
      7. 6.3.7. Deadlocking
    4. 6.4. PESSIMISTIC CONCURRENCY
      1. 6.4.1. Pessimistic Isolation Levels
        1. 6.4.1.1. Read Uncommitted
        2. 6.4.1.2. Read Committed
        3. 6.4.1.3. Repeatable Read
        4. 6.4.1.4. Serializable
      2. 6.4.2. Concurrency vs. Isolation
    5. 6.5. OPTIMISTIC CONCURRENCY
      1. 6.5.1. Optimistic Isolation Levels
        1. 6.5.1.1. Read Committed Snapshot Isolation
        2. 6.5.1.2. Snapshot Isolation
        3. 6.5.1.3. The Snapshot Achilles Heel — Update Conflicts
        4. 6.5.1.4. Other Limitations of Snapshot Isolation
      2. 6.5.2. How Row Versioning Works
      3. 6.5.3. Row Versioning Deep Dive
        1. 6.5.3.1. Reading the XSN and Pointer
        2. 6.5.3.2. Rogue Transaction
        3. 6.5.3.3. Garbage Collection
      4. 6.5.4. Monitoring Row Versioning
        1. 6.5.4.1. sys.dm_tran_top_version_generators
    6. 6.6. LATCHES
      1. 6.6.1. Latch Types
        1. 6.6.1.1. Non-Buffer (Non-BUF) Latches
        2. 6.6.1.2. Buffer (BUF) Latches
        3. 6.6.1.3. IO (BUF) Latches
      2. 6.6.2. BUF Latch Architecture
        1. 6.6.2.1. Buffer Manager
        2. 6.6.2.2. Hash Table
        3. 6.6.2.3. Buffer
        4. 6.6.2.4. BUF Arrays and BUF Structures
        5. 6.6.2.5. Data Pages
      3. 6.6.3. Latch Modes
        1. 6.6.3.1. Keep (KP) Latch
        2. 6.6.3.2. Shared (SH) Latch
        3. 6.6.3.3. Update (UP) Latch
        4. 6.6.3.4. Exclusive (EX) Latch
        5. 6.6.3.5. Destroy (DT) Latch
      4. 6.6.4. Grant Order
      5. 6.6.5. Latch Waits and Blocking
    7. 6.7. SUB-LATCHES AND SUPER-LATCHES
    8. 6.8. LATCHING IN ACTION
      1. 6.8.1. Without Latching
      2. 6.8.2. With Latching
    9. 6.9. SUMMARY
  12. 7. Knowing Tempdb
    1. 7.1. OVERVIEW AND USAGE
      1. 7.1.1. User Temporary Objects
        1. 7.1.1.1. Temp Tables vs. Table Variables
          1. 7.1.1.1.1. Statistics
          2. 7.1.1.1.2. Indexes
          3. 7.1.1.1.3. Schema Modifications
      2. 7.1.2. Internal Temporary Objects
      3. 7.1.3. The Version Store
        1. 7.1.3.1. Version Store Overhead
        2. 7.1.3.2. Append-Only Stores
    2. 7.2. TROUBLESHOOTING COMMON ISSUES
      1. 7.2.1. Latch Contention
        1. 7.2.1.1. What Is a Latch?
        2. 7.2.1.2. Allocation Pages
          1. 7.2.1.2.1. PFS (Page Free Space)
          2. 7.2.1.2.2. GAM (Global Allocation Map)
          3. 7.2.1.2.3. SGAM (Shared Global Allocation Map)
        3. 7.2.1.3. Allocation Page Contention
        4. 7.2.1.4. Allocation Page Contention Example
        5. 7.2.1.5. Resolving and/or Avoiding Allocation Page Contention Problems
          1. 7.2.1.5.1. Multiple Tempdb Data Files
          2. 7.2.1.5.2. Temporary Object Reuse
          3. 7.2.1.5.3. Trace Flag 1118
      2. 7.2.2. Monitoring Tempdb Performance
        1. 7.2.2.1. I/O Performance
          1. 7.2.2.1.1. Performance Monitor
          2. 7.2.2.1.2. SQL Server DMVs
          3. 7.2.2.1.3. Thresholds
      3. 7.2.3. Troubleshooting Space Issues
        1. 7.2.3.1. sys.dm_db_file_space_usage
        2. 7.2.3.2. sys.dm_db_task_space_usage
        3. 7.2.3.3. sys.dm_db_session_space_usage
      4. 7.2.4. Transaction Log Growing Too Big?
    3. 7.3. CONFIGURATION BEST PRACTICES
      1. 7.3.1. Tempdb File Placement
        1. 7.3.1.1. Scenario 1: Single Instance Failover Cluster with 12 Disks in a Shared Disk Array
          1. 7.3.1.1.1. The Quorum Disk
          2. 7.3.1.1.2. More Disks in an Array vs. Workload Separation
        2. 7.3.1.2. Scenario 2: Single Instance Connected to an Enterprise SAN
      2. 7.3.2. Tempdb Initial Sizing and Autogrowth
        1. 7.3.2.1. How Big Should I Set My Tempdb Database?
        2. 7.3.2.2. What Should I Set Autogrow To?
      3. 7.3.3. Configuring Multiple Tempdb Data Files
    4. 7.4. SUMMARY
  13. 8. Defining Your Approach To Troubleshooting
    1. 8.1. APPROACHING THE PROBLEM CORRECTLY
      1. 8.1.1. Having the Right Attitude
        1. 8.1.1.1. Remain Calm
        2. 8.1.1.2. Avoid Prejudice
        3. 8.1.1.3. Avoid Rushing or Plodding
        4. 8.1.1.4. Think Ahead
      2. 8.1.2. Dealing with Management
      3. 8.1.3. When to Call for Outside Help
    2. 8.2. DEFINING THE PROBLEM
      1. 8.2.1. Tips for Identifying a Problem
      2. 8.2.2. Bite-Size Chunks
        1. 8.2.2.1. Connectivity Issues
        2. 8.2.2.2. Performance Issues
        3. 8.2.2.3. Memory or Compilation Issues
      3. 8.2.3. Service-Level Agreements
      4. 8.2.4. Defining Exit Criteria
      5. 8.2.5. Understanding Your Baselines
      6. 8.2.6. Events and Alerts
    3. 8.3. GATHERING DATA
      1. 8.3.1. Understanding the Data Gathering Process
      2. 8.3.2. Tools and Utilities
        1. 8.3.2.1. SQLDiag
        2. 8.3.2.2. PSSDiag
        3. 8.3.2.3. Windows Event logs (Application, System, and Security)
        4. 8.3.2.4. Custom Application Logs
        5. 8.3.2.5. User Dumps
        6. 8.3.2.6. PerfMon
        7. 8.3.2.7. NetMon
        8. 8.3.2.8. Management Data Warehouse
        9. 8.3.2.9. Policy-Based Management
    4. 8.4. ANALYZING DATA
      1. 8.4.1. SQL Nexus
      2. 8.4.2. Profiler
      3. 8.4.3. Database Tuning Advisor
      4. 8.4.4. Visual Studio Database Edition
    5. 8.5. TESTING SOLUTIONS
    6. 8.6. TROUBLESHOOTING OTHER COMPONENTS
      1. 8.6.1. Failover Clustering
      2. 8.6.2. Replication
        1. 8.6.2.1. Snapshot Agent
        2. 8.6.2.2. Log Reader Agent
        3. 8.6.2.3. Distribution Agent
        4. 8.6.2.4. Merge Agent
        5. 8.6.2.5. Cleanup Agents
        6. 8.6.2.6. Replication Monitor
      3. 8.6.3. Analysis Services
        1. 8.6.3.1. XMLA Queries
        2. 8.6.3.2. Processing Performance
        3. 8.6.3.3. MDX Queries
        4. 8.6.3.4. Unexpected MDX Query Results
        5. 8.6.3.5. Security
        6. 8.6.3.6. Connectivity
        7. 8.6.3.7. HTTP
        8. 8.6.3.8. Kerberos
    7. 8.7. SUMMARY
  14. 9. Viewing Server Performance with PerfMon and the PAL Tool
    1. 9.1. PERFORMANCE MONITOR OVERVIEW
      1. 9.1.1. Reliability and Performance Monitor in Windows Server 2008
        1. 9.1.1.1. Resource Overview
        2. 9.1.1.2. Data Collector Sets
        3. 9.1.1.3. Reliability Monitor
        4. 9.1.1.4. Usability Enhancements in Windows 2008 PerfMon
          1. 9.1.1.4.1. Auto-Scaling Counters
          2. 9.1.1.4.2. Show/Hide Counters
      2. 9.1.2. New Counters for SQL Server 2008 in PerfMon
      3. 9.1.3. Troubleshooting SQL Server Problems
        1. 9.1.3.1. Data-Driven Troubleshooting
        2. 9.1.3.2. Choosing the Right Tool for the Job
    2. 9.2. GETTING STARTED WITH PERFMON
      1. 9.2.1. Monitoring Real-Time Server Activity
      2. 9.2.2. Starting Out with Data Collector Sets
      3. 9.2.3. Working with User-Defined Data Collector Sets
        1. 9.2.3.1. Configuring Collector Properties
        2. 9.2.3.2. Configuring Properties for Performance Counters
        3. 9.2.3.3. PerfMon Log Formats
        4. 9.2.3.4. Running PerfMon Remotely
      4. 9.2.4. What to Be Aware of When Running PerfMon
      5. 9.2.5. The Impact of Running PerfMon
        1. 9.2.5.1. Sample Interval
        2. 9.2.5.2. Number of Counters
        3. 9.2.5.3. Disk Performance
      6. 9.2.6. Servers with Very Poor Performance
      7. 9.2.7. Common PerfMon Problems
        1. 9.2.7.1. Using PerfMon on 64-bit Systems Using WOW
        2. 9.2.7.2. Remote Monitoring Failures
        3. 9.2.7.3. Missing SQL Server Counters
        4. 9.2.7.4. Missing Counters or Numbers Instead of Names
    3. 9.3. GETTING MORE FROM PERFORMANCE MONITOR
      1. 9.3.1. Identifying SQL Server Bottlenecks
        1. 9.3.1.1. Types of Bottleneck
          1. 9.3.1.1.1. Configuration-Based Bottlenecks
          2. 9.3.1.1.2. Schema-Based Bottlenecks
        2. 9.3.1.2. Prescriptive Guidance
        3. 9.3.1.3. Investigating CPU Problems
          1. 9.3.1.3.1. CPU Performance Counters
          2. 9.3.1.3.2. Common Causes of CPU Problems
        4. 9.3.1.4. Investigating Memory-Related Problems
          1. 9.3.1.4.1. Types of Memory Pressure
          2. 9.3.1.4.2. Virtual Address Space
          3. 9.3.1.4.3. Memory Performance Counters
        5. 9.3.1.5. Disk or Storage Problems
        6. 9.3.1.6. SQL Server Performance Problems
      2. 9.3.2. Wait Stats Analysis
      3. 9.3.3. Getting a Performance Baseline
    4. 9.4. GETTING STARTED WITH PERFORMANCE ANALYSIS FOR LOGS (PAL)
      1. 9.4.1. Templates and PAL
      2. 9.4.2. Capturing PerfMon Logs
      3. 9.4.3. Using PAL for Log Analysis
    5. 9.5. OTHER PERFMON LOG ANALYSIS TOOLS
      1. 9.5.1. Using SQL Server to Analyze PerfMon Logs
      2. 9.5.2. Combining PerfMon Logs and SQL Profiler Traces
      3. 9.5.3. Using Relog
        1. 9.5.3.1. Extracting Performance Data for a Specific Timeframe
        2. 9.5.3.2. Extracting Specific Performance Counters
        3. 9.5.3.3. Converting Log Files to New Formats
      4. 9.5.4. Using LogMan
      5. 9.5.5. Using LogParser
    6. 9.6. SUMMARY
  15. 10. Tracing SQL Server with SQL Trace and Profiler
    1. 10.1. TRACING 101
      1. 10.1.1. Why Trace
      2. 10.1.2. When to Trace
      3. 10.1.3. Where to Trace
      4. 10.1.4. What to Trace
      5. 10.1.5. How to Trace
    2. 10.2. THE ARCHITECTURE OF SQL TRACE
    3. 10.3. EVENT CLASSIFICATION AND HIERARCHIES
    4. 10.4. SQL TRACE CATALOG VIEWS
      1. 10.4.1. sys.traces
        1. 10.4.1.1. Is_rowset
        2. 10.4.1.2. Is_rollover
        3. 10.4.1.3. Is_shutdown
        4. 10.4.1.4. Is_default
        5. 10.4.1.5. Black Box vs. Default Trace
      2. 10.4.2. sys.trace_categories
      3. 10.4.3. sys.trace_events
      4. 10.4.4. sys.trace_columns
      5. 10.4.5. sys.trace_subclass_values
      6. 10.4.6. sys.trace_event_bindings
    5. 10.5. SQL TRACE PROCEDURES AND FUNCTIONS
      1. 10.5.1. sp_trace_create
      2. 10.5.2. sp_trace_setevent
        1. 10.5.2.1. Performance Considerations
      3. 10.5.3. sp_trace_setfilter
        1. 10.5.3.1. Filtering a Range
      4. 10.5.4. sp_trace_setstatus
      5. 10.5.5. sp_trace_generateevent
      6. 10.5.6. fn_trace_gettable
      7. 10.5.7. fn_trace_geteventinfo
      8. 10.5.8. fn_trace_getfilterinfo
    6. 10.6. SECURING SQL TRACE
      1. 10.6.1. Tracing Login Creation/Deletion
      2. 10.6.2. Logins Changing Passwords
      3. 10.6.3. When Tracing Login Can View Object Definitions and Parameter Values
        1. 10.6.3.1. Logins Changing Trace Definitions
      4. 10.6.4. Securing the Output of SQL Trace
        1. 10.6.4.1. SQL Trace File Protection
        2. 10.6.4.2. Rowset Provider Protection
    7. 10.7. PROFILER
      1. 10.7.1. Advanced Features of Profiler
        1. 10.7.1.1. Grouping Events
        2. 10.7.1.2. Extracting Event Data
        3. 10.7.1.3. Importing PerfMon Data into Profiler
      2. 10.7.2. Exporting a Trace Definition
      3. 10.7.3. Exporting an Existing Server-Side Trace
      4. 10.7.4. Tips & Tricks
        1. 10.7.4.1. Turn Auto Scroll Off
        2. 10.7.4.2. Edit the Base Template
        3. 10.7.4.3. Firing Pre-Filtered Profiler
    8. 10.8. SUMMARY
  16. 11. Consolidating Data Collection with SQLDiag and the PerfStats Script
    1. 11.1. APPROACHING DATA COLLECTION
    2. 11.2. WHAT IS SQLDiag?
      1. 11.2.1. An Overview of SQLDiag
        1. 11.2.1.1. SQLDiag in SQL Server 2005 and SQL Server 2008
        2. 11.2.1.2. Components of SQLDiag
        3. 11.2.1.3. Registering SQLDiag as a Service
        4. 11.2.1.4. Where to Find SQLDiag
        5. 11.2.1.5. Working with SQLDiag Configuration Files
      2. 11.2.2. Using SQLDIAG
        1. 11.2.2.1. Using SQLDiag on Failover Clusters
        2. 11.2.2.2. Configuring the PerfMon Collector
        3. 11.2.2.3. Configuring the Profiler Collector
        4. 11.2.2.4. Adding Custom Collectors
      3. 11.2.3. Running SQLDiag in Production
        1. 11.2.3.1. Alert-Driven Data Collection with SQLDiag
    3. 11.3. USING THE PERFSTATS SCRIPT
      1. 11.3.1. What Is the PerfStats Script?
      2. 11.3.2. Where to get the PerfStats Script
      3. 11.3.3. Configuring the PerfStats Script
      4. 11.3.4. Running the PerfStats Script
      5. 11.3.5. Analyzing PerfStats Output
        1. 11.3.5.1. Active Queries
        2. 11.3.5.2. Instance-Wide Data
    4. 11.4. SUMMARY
  17. 12. Introducing RML Utilities for Stress Testing and Trace File Analysis
    1. 12.1. WHEN TO USE RML UTILITIES
      1. 12.1.1. Testing New Applications before Going Live
      2. 12.1.2. Validating the Impact of a Change
      3. 12.1.3. Determining the Purpose of Testing
    2. 12.2. WHAT ARE RML UTILITIES?
      1. 12.2.1. History of RML Utilities
      2. 12.2.2. What's in the Download
      3. 12.2.3. Components of RML Utilities
        1. 12.2.3.1. ReadTrace
          1. 12.2.3.1.1. Query Templates
          2. 12.2.3.1.2. Getting Started with ReadTrace
          3. 12.2.3.1.3. Limitations of ReadTrace
        2. 12.2.3.2. Reporter
        3. 12.2.3.3. OStress
          1. 12.2.3.3.1. Stress Mode
          2. 12.2.3.3.2. Replay Mode
          3. 12.2.3.3.3. Getting Started with OStress
          4. 12.2.3.3.4. Advanced OStress
        4. 12.2.3.4. ORCA
    3. 12.3. PERFORMANCE TESTING
      1. 12.3.1. Testing Scenarios
      2. 12.3.2. Ensuring a Fair Test
        1. 12.3.2.1. Environment Preparation
        2. 12.3.2.2. Test Execution
      3. 12.3.3. Capturing a SQL Trace
      4. 12.3.4. Analyzing Large Datasets
    4. 12.4. SUMMARY
  18. 13. Bringing It All Together with SQL Nexus
    1. 13.1. GETTING STARTED
      1. 13.1.1.
        1. 13.1.1.1. Prerequisites
        2. 13.1.1.2. Installation
        3. 13.1.1.3. Startup
    2. 13.2. DATA COLLECTION
      1. 13.2.1. Is the Default Data Collection Good Enough?
        1. 13.2.1.1. Performance Monitor (PerfMon) Log
        2. 13.2.1.2. PerfStats Script
        3. 13.2.1.3. SQL Trace
      2. 13.2.2. Modifying the Data Collection
      3. 13.2.3. Collecting Data for a Specific Instance
      4. 13.2.4. Knowing How Much Data to Collect
    3. 13.3. IMPORTING DATA
    4. 13.4. AVAILABLE REPORTS
    5. 13.5. EXAMPLE SCENARIO USING SQL NEXUS
      1. 13.5.1. Configuring the Data Collection
      2. 13.5.2. Generating the Workload
      3. 13.5.3. Importing the Data
      4. 13.5.4. Looking at the Bottlenecks
      5. 13.5.5. Testing the Resolution
    6. 13.6. ADDING YOUR OWN REPORTS
      1. 13.6.1. Where Are the PerfMon Reports?
    7. 13.7. SUMMARY
  19. 14. Using Management Studio Reports and the Performance Dashboard
    1. 14.1. USING THE STANDARD REPORTS
      1. 14.1.1. Interpreting the Standard Server Reports
        1. 14.1.1.1. Reading the Server Dashboard Report
        2. 14.1.1.2. Configuration Changes and Schema Changes Reports
        3. 14.1.1.3. Scheduler Health Report
        4. 14.1.1.4. Memory Consumption Report
        5. 14.1.1.5. Activity Reports
        6. 14.1.1.6. Performance Executions Reports
        7. 14.1.1.7. Performance Top Queries Reports
        8. 14.1.1.8. Top Transactions Reports
      2. 14.1.2. Interpreting the Database Reports
        1. 14.1.2.1. Disk Usage Reports
        2. 14.1.2.2. Backup and Restore Reports
        3. 14.1.2.3. Index Usage Statistics Report
        4. 14.1.2.4. Index Physical Statistics Report
    2. 14.2. USING THE PERFORMANCE DASHBOARD
      1. 14.2.1. Troubleshooting Common Errors
        1. 14.2.1.1. "The stored procedures and functions required by the performance dashboard have not yet been installed."
        2. 14.2.1.2. "Invalid column name 'cpu_ticks_in_ms'."
        3. 14.2.1.3. "Difference of two datetime columns caused overflow at runtime."
        4. 14.2.1.4. "Index (zero based) must be greater than or equal to zero and less than the size of the argument list."
        5. 14.2.1.5. "The 'version_string' parameter is missing a value."
      2. 14.2.2. Interpreting the Performance Dashboard Reports
        1. 14.2.2.1. Historical Waits Report
        2. 14.2.2.2. Missing Index Report
    3. 14.3. BUILDING CUSTOM REPORTS
      1. 14.3.1. Building a Custom Report with BIDS
      2. 14.3.2. Picking Custom Reports Candidates
    4. 14.4. SUMMARY
  20. 15. Using SQL Server Management Data Warehouse
    1. 15.1. INTRODUCING MANAGEMENT DATA WAREHOUSE
      1. 15.1.1. Background to MDW
      2. 15.1.2. MDW Architecture
        1. 15.1.2.1. Data Collection Sets
        2. 15.1.2.2. Data Warehouse
        3. 15.1.2.3. Reports
    2. 15.2. IMPLEMENTING MDW
      1. 15.2.1. Creating a Management Data Warehouse
      2. 15.2.2. Set Up Data Collection
      3. 15.2.3. System Collection Sets
      4. 15.2.4. Uses for MDW
      5. 15.2.5. Performance Overhead
    3. 15.3. REPORTING FROM MDW
    4. 15.4. CUSTOM DATA COLLECTION AND REPORTING
      1. 15.4.1. Defining Custom Collections
      2. 15.4.2. SSAS Monitoring Scripts for the MDW
    5. 15.5. SUMMARY

Product information

  • Title: Professional SQL Server® 2008 Internals and Troubleshooting
  • Author(s):
  • Release date: January 2010
  • Publisher(s): Wrox
  • ISBN: 9780470484289