book

Professional SQL Server® 2008 Internals and Troubleshooting

Name: Professional SQL Server® 2008 Internals and Troubleshooting
ISBN: 9780470484289

by Christian Bolton, Justin Langford, Brent Ozar, James Rowland-Jones, Jonathan Kehayias, Cindy Gross, Steven Wort

January 2010

Intermediate to advanced

617 pages

16h 42m

English

Wrox

Read now

Unlock full access

Copyright
ABOUT THE AUTHORS
CREDITS
ACKNOWLEDGMENTS
INTRODUCTION
WHO THIS BOOK IS FORWHAT THIS BOOK COVERSUnderstanding InternalsAdditional Troubleshooting ToolsHOW THIS BOOK IS STRUCTUREDChapter 1: SQL Server ArchitectureChapter 2: Understanding MemoryChapter 3: SQL Server Waits and Extended EventsChapter 4: Working with StorageChapter 5: CPU and Query ProcessingChapter 6: Locking and LatchesChapter 7: Knowing TempdbThe Troubleshooting Tools ChaptersChapter 8: Defining Your Approach to TroubleshootingChapter 9: Viewing Server Performance with PerfMon and the PAL ToolChapter 10: Tracing SQL Server with SQL Trace and ProfilerChapter 11: Consolidating Data Collection with SQLDiag and the PerfStats ScriptChapter 12: Introducing RML Utilities for Stress Testing and Trace File AnalysisChapter 13: Bringing It All Together with SQL NexusChapter 14: Using Management Studio Reports and the Performance DashboardChapter 15: Using SQL Server Management Data WarehouseChapter 16: Shortcuts to Efficient Data Collection and Quick AnalysisCONVENTIONSSOURCE CODEERRATAP2P.WROX.COM
1. SQL Server Architecture
1.1. DATABASE TRANSACTIONS1.1.1. ACID Properties1.1.1.1. Atomicity1.1.1.2. Consistency1.1.1.3. Isolation1.1.1.4. Durability1.1.2. SQL Server Transactions1.2. THE LIFE CYCLE OF A QUERY1.2.1. The Relational and Storage Engines1.2.2. The Buffer Pool1.2.3. A Basic Select Query1.2.3.1. SQL Server Network Interface1.2.3.2. TDS (Tabular Data Stream) Endpoints1.2.3.3. Protocol Layer1.2.3.4. Command Parser1.2.3.4.1. Plan Cache1.2.3.5. Optimizer1.2.3.6. Query Executor1.2.3.7. Access Methods1.2.3.8. Buffer Manager1.2.3.9. Data Cache1.2.3.10. A Basic select Statement Life Cycle Summary1.2.4. A Simple Update Query1.2.4.1. Transaction Manager1.2.4.2. Buffer Manager1.2.5. Recovery1.2.5.1. Dirty Pages1.2.5.2. Lazywriter1.2.5.3. Checkpoint Process1.2.5.3.1. Recovery Interval1.2.5.4. Recovery Models1.2.5.4.1. Full1.2.5.4.2. Bulk-Logged1.2.5.4.3. Simple1.3. THE SQLOS (SQL OPERATING SYSTEM)1.4. SUMMARY
2. Understanding Memory
2.1. WINDOWS AND MEMORY2.1.1. Physical Memory2.1.1.1. SIMMs and DIMMs2.1.1.2. DRAM2.1.1.3. SDRAM2.1.1.4. DDR SDRAM2.1.1.5. Dual-Channel Memory2.1.1.6. Registered and Unbuffered Modules2.1.1.7. Error-Correcting Code (ECC) Memory2.1.1.8. FB-DIMMs2.1.1.9. Maximum Supported Physical Memory2.1.2. Virtual Address Space2.1.3. Virtual Memory Manager2.1.4. Tuning 32-Bit Systems2.1.4.1. /3GB and increaseUserVA2.1.4.1.1. Paged Pool and Nonpaged Pool2.1.4.1.2. System PTEs2.1.4.2. /PAE Switch2.1.4.3. Address Windowing Extensions (AWE)2.1.4.4. /PAE and /3GB Together?2.1.4.4.1. Windows Server 20082.1.4.5. MemToLeave, or ReservedMemory2.1.4.5.1. Note on 64-Bit Systems2.1.5. Tuning 64-Bit Systems2.1.5.1. AWE on 64-Bit Systems2.2. SQL SERVER MEMORY2.2.1. Memory Nodes2.2.2. Memory Clerks, Caches, and the Buffer Pool2.2.2.1. Memory Clerks2.2.2.2. Caches2.2.2.3. Buffer Pool2.2.2.4. Min and Max Server Memory2.2.2.4.1. Looking at the Buffer Pool's Maximum Usage2.2.2.4.2. Determining the Maximum Potential for Non-Buffer Pool Usage2.2.2.5. Plan Cache2.2.2.6. Query Memory/Workspace Memory2.2.2.6.1. The Query Wait Option2.2.2.6.2. Query Memory Diagnostics2.3. SUMMARY
3. SQL Server Waits and Extended Events
3.1. WAITS3.1.1. SQL Server Execution Model3.1.2. Understanding Wait Statistics3.1.3. Wait Types3.1.3.1. Resource Wait Types3.1.3.1.1. Memory Waits3.1.3.1.2. Disk I/O Waits3.1.3.1.3. Blocking Waits3.1.3.1.4. CPU3.1.3.1.5. Network Waits3.1.3.2. System Waits3.1.3.3. Preemptive Wait Types3.2. EXTENDED EVENTS3.2.1. Architecture3.2.1.1. Extended Events Engine3.2.1.2. Event Firing3.2.1.3. Dynamic Management Views and Functions3.2.1.3.1. Metadata Views3.2.1.3.2. Session Definition Views3.2.1.3.3. Active Session Views3.2.1.4. Extended Event Metadata3.2.1.4.1. Packages3.2.1.4.2. Events3.2.1.4.3. Actions3.2.1.4.4. Targets3.2.1.4.5. Predicates3.2.1.4.6. Maps3.2.2. Event Sessions3.2.2.1. system_health Session3.2.2.2. Managing Extended Events Sessions3.2.3. Using the Extended Events Manager3.3. EXAMINING WAITS WITH EXTENDED EVENTS3.4. SUMMARY
4. Working with Storage
4.1. TYPES OF STORAGE4.1.1. Understanding Individual Drives4.1.1.1. Parallel ATA (PATA, IDE, EIDE) Magnetic Drives4.1.1.2. Serial ATA (SATA) Magnetic Drives4.1.1.3. Serial ATA (SATA) Solid-State Drives4.1.1.4. Serial Attached SCSI (SAS) Magnetic Drives4.1.1.5. Fibre Channel (FC) Magnetic Drives4.1.2. Protecting Data with RAID4.1.2.1. RAID 1: Mirroring4.1.2.2. RAID 0: Striping4.1.2.3. RAID 5 and 6: Striping with Parity4.1.2.4. RAID 0+1 and RAID 10: Mirroring and Striping4.1.2.5. Choosing between Performance and Price4.1.3. Direct Attached Storage4.1.4. Storage Area Networks4.1.4.1. Fibre Channel Storage Area Networks4.1.4.2. iSCSI Storage Area Networks4.1.4.3. Multipathing: Multiple Routes to the Storage4.1.4.4. Choosing Shared or Dedicated Drives4.2. STORAGE PERFORMANCE TESTING4.2.1. Choosing a Storage Testing Tool4.2.1.1. Using SQLIO4.2.1.2. Using SQLIOSim4.2.2. Interpreting Storage Test Results4.2.2.1. Hitting the 100/200/400MB/sec Wall4.2.2.2. Cache Is Not Always King4.2.2.3. Knowing What's Happening in the Black Box4.2.2.4. Testing the Worst-Case Scenario4.2.2.5. Wash, Rinse, and Repeat4.3. CONFIGURING SOFTWARE FOR STORAGE4.3.1. Configuring Windows Server4.3.1.1. Partition Alignment4.3.1.2. NTFS Allocation Unit Size4.3.1.3. Configuring HBA Queue Depth4.3.1.4. Adapting to Virtual Servers4.3.2. Configuring SQL Server 20084.3.2.1. Choosing Which Files to Place on Which Disks4.3.2.2. Using Compression to Gain Performance4.3.3. Corruption4.3.3.1. Detecting Corruption4.3.3.2. Recovering from Corruption4.3.3.2.1. Fix the Underlying Cause First4.3.3.2.2. Focus on the Worst Problem First4.3.3.2.3. Dealing with Damaged System Tables4.3.3.2.4. Dealing with Corrupt Clustered Indexes4.3.3.2.5. Dealing with Data Purity Errors4.3.3.2.6. Dealing With Corrupt Non-Clustered Indexes4.3.3.2.7. The Final Steps After Any Repairs4.4. SUMMARY
5. CPU and Query Processing
5.1. THE CPU5.2. THE CPU AND SQL SERVER5.2.1. Processor Speed5.2.2. Hyper-Threading5.2.3. Licensing with Multicore and Hyper-Threading5.2.4. Cache5.2.5. Multicore Processors5.2.5.1. Multicore Terminology5.3. SYSTEM ARCHITECTURE5.3.1. Symmetric Multiprocessing5.3.2. NonUniform Memory Access5.3.2.1. AMD or Intel?5.3.2.2. Server, Workstation, or Desktop5.4. QUERY PROCESSING5.4.1. Parsing5.4.2. Binding5.5. QUERY OPTIMIZATION5.5.1. Parallel Plans5.5.2. Algebrizer Trees5.5.3. sql_handle or plan_handle5.5.4. Statistics5.5.4.1. Auto_create_statistics5.5.4.2. Auto_update_statistics5.5.4.3. Auto_update_statistics_asynchronously5.5.5. Plan Caching and Recompilation5.5.5.1. Parameterization5.5.5.2. Looking into the Plan Cache5.5.5.3. Compilation/Recompilation5.5.6. Influencing Optimization5.5.6.1. Query Hints5.5.6.1.1. FAST number_rows5.5.6.1.2. {Loop | Merge | Hash } JOIN5.5.6.1.3. MAXDOP n5.5.6.1.4. OPTIMIZE FOR5.5.6.1.5. RECOMPILE5.5.6.1.6. USE PLAN N 'xml plan'5.5.6.2. Plan Guides5.6. QUERY PLANS5.6.1. Query Plan Operators5.6.1.1. Join Operators5.6.1.1.1. Nested Loop5.6.1.1.2. Merge5.6.1.1.3. Hash5.6.1.2. Spool Operators5.6.1.3. Scan and Seek Operators5.6.1.4. Lookup Operators5.6.2. Reading Query Plans5.7. EXECUTING YOUR QUERIES5.7.1. SQLOS5.7.1.1. Memory Nodes5.7.1.1.1. Soft NUMA5.7.1.2. CPU Nodes5.7.1.2.1. Processor Affinity5.7.1.3. Schedulers5.7.1.4. Tasks5.7.1.5. Workers5.7.1.6. Threads5.7.1.7. Scheduling5.8. SUMMARY

6. Locking and Latches
6.1. TRANSACTIONS6.1.1. Atomic6.1.2. Consistent6.1.3. Isolated6.1.4. Durable6.2. CONSEQUENCE OF CONCURRENT ACCESS6.2.1. Lost Updates6.2.2. Dirty Reads6.2.3. Non-Repeatable Reads6.2.4. Phantom Reads6.2.5. Double Reads6.2.6. Halloween Effect6.3. LOCKING6.3.1. Viewing Locks — sys.dm_tran_locks6.3.2. Lock Granularity6.3.3. Lock Modes6.3.3.1. Shared Lock Mode (S)6.3.3.2. Update Lock Mode (U)6.3.3.3. Exclusive Lock Mode (X)6.3.3.4. Schema Lock Modes (Sch-S), (Sch-M)6.3.3.5. Intent Lock Modes (IS), (IU), (IX)6.3.3.6. Conversion Lock Modes (SIX), (SIU), (UIX)6.3.3.7. Bulk Update Lock Mode (BU)6.3.4. Lock Hierarchy6.3.5. Lock Compatibility6.3.6. Lock Escalation6.3.6.1. Controlling Lock Escalation6.3.6.2. Detecting Lock Escalation6.3.7. Deadlocking6.4. PESSIMISTIC CONCURRENCY6.4.1. Pessimistic Isolation Levels6.4.1.1. Read Uncommitted6.4.1.2. Read Committed6.4.1.3. Repeatable Read6.4.1.4. Serializable6.4.2. Concurrency vs. Isolation6.5. OPTIMISTIC CONCURRENCY6.5.1. Optimistic Isolation Levels6.5.1.1. Read Committed Snapshot Isolation6.5.1.2. Snapshot Isolation6.5.1.3. The Snapshot Achilles Heel — Update Conflicts6.5.1.4. Other Limitations of Snapshot Isolation6.5.2. How Row Versioning Works6.5.3. Row Versioning Deep Dive6.5.3.1. Reading the XSN and Pointer6.5.3.2. Rogue Transaction6.5.3.3. Garbage Collection6.5.4. Monitoring Row Versioning6.5.4.1. sys.dm_tran_top_version_generators6.6. LATCHES6.6.1. Latch Types6.6.1.1. Non-Buffer (Non-BUF) Latches6.6.1.2. Buffer (BUF) Latches6.6.1.3. IO (BUF) Latches6.6.2. BUF Latch Architecture6.6.2.1. Buffer Manager6.6.2.2. Hash Table6.6.2.3. Buffer6.6.2.4. BUF Arrays and BUF Structures6.6.2.5. Data Pages6.6.3. Latch Modes6.6.3.1. Keep (KP) Latch6.6.3.2. Shared (SH) Latch6.6.3.3. Update (UP) Latch6.6.3.4. Exclusive (EX) Latch6.6.3.5. Destroy (DT) Latch6.6.4. Grant Order6.6.5. Latch Waits and Blocking6.7. SUB-LATCHES AND SUPER-LATCHES6.8. LATCHING IN ACTION6.8.1. Without Latching6.8.2. With Latching6.9. SUMMARY
7. Knowing Tempdb
7.1. OVERVIEW AND USAGE7.1.1. User Temporary Objects7.1.1.1. Temp Tables vs. Table Variables7.1.1.1.1. Statistics7.1.1.1.2. Indexes7.1.1.1.3. Schema Modifications7.1.2. Internal Temporary Objects7.1.3. The Version Store7.1.3.1. Version Store Overhead7.1.3.2. Append-Only Stores7.2. TROUBLESHOOTING COMMON ISSUES7.2.1. Latch Contention7.2.1.1. What Is a Latch?7.2.1.2. Allocation Pages7.2.1.2.1. PFS (Page Free Space)7.2.1.2.2. GAM (Global Allocation Map)7.2.1.2.3. SGAM (Shared Global Allocation Map)7.2.1.3. Allocation Page Contention7.2.1.4. Allocation Page Contention Example7.2.1.5. Resolving and/or Avoiding Allocation Page Contention Problems7.2.1.5.1. Multiple Tempdb Data Files7.2.1.5.2. Temporary Object Reuse7.2.1.5.3. Trace Flag 11187.2.2. Monitoring Tempdb Performance7.2.2.1. I/O Performance7.2.2.1.1. Performance Monitor7.2.2.1.2. SQL Server DMVs7.2.2.1.3. Thresholds7.2.3. Troubleshooting Space Issues7.2.3.1. sys.dm_db_file_space_usage7.2.3.2. sys.dm_db_task_space_usage7.2.3.3. sys.dm_db_session_space_usage7.2.4. Transaction Log Growing Too Big?7.3. CONFIGURATION BEST PRACTICES7.3.1. Tempdb File Placement7.3.1.1. Scenario 1: Single Instance Failover Cluster with 12 Disks in a Shared Disk Array7.3.1.1.1. The Quorum Disk7.3.1.1.2. More Disks in an Array vs. Workload Separation7.3.1.2. Scenario 2: Single Instance Connected to an Enterprise SAN7.3.2. Tempdb Initial Sizing and Autogrowth7.3.2.1. How Big Should I Set My Tempdb Database?7.3.2.2. What Should I Set Autogrow To?7.3.3. Configuring Multiple Tempdb Data Files7.4. SUMMARY
8. Defining Your Approach To Troubleshooting
8.1. APPROACHING THE PROBLEM CORRECTLY8.1.1. Having the Right Attitude8.1.1.1. Remain Calm8.1.1.2. Avoid Prejudice8.1.1.3. Avoid Rushing or Plodding8.1.1.4. Think Ahead8.1.2. Dealing with Management8.1.3. When to Call for Outside Help8.2. DEFINING THE PROBLEM8.2.1. Tips for Identifying a Problem8.2.2. Bite-Size Chunks8.2.2.1. Connectivity Issues8.2.2.2. Performance Issues8.2.2.3. Memory or Compilation Issues8.2.3. Service-Level Agreements8.2.4. Defining Exit Criteria8.2.5. Understanding Your Baselines8.2.6. Events and Alerts8.3. GATHERING DATA8.3.1. Understanding the Data Gathering Process8.3.2. Tools and Utilities8.3.2.1. SQLDiag8.3.2.2. PSSDiag8.3.2.3. Windows Event logs (Application, System, and Security)8.3.2.4. Custom Application Logs8.3.2.5. User Dumps8.3.2.6. PerfMon8.3.2.7. NetMon8.3.2.8. Management Data Warehouse8.3.2.9. Policy-Based Management8.4. ANALYZING DATA8.4.1. SQL Nexus8.4.2. Profiler8.4.3. Database Tuning Advisor8.4.4. Visual Studio Database Edition8.5. TESTING SOLUTIONS8.6. TROUBLESHOOTING OTHER COMPONENTS8.6.1. Failover Clustering8.6.2. Replication8.6.2.1. Snapshot Agent8.6.2.2. Log Reader Agent8.6.2.3. Distribution Agent8.6.2.4. Merge Agent8.6.2.5. Cleanup Agents8.6.2.6. Replication Monitor8.6.3. Analysis Services8.6.3.1. XMLA Queries8.6.3.2. Processing Performance8.6.3.3. MDX Queries8.6.3.4. Unexpected MDX Query Results8.6.3.5. Security8.6.3.6. Connectivity8.6.3.7. HTTP8.6.3.8. Kerberos8.7. SUMMARY
9. Viewing Server Performance with PerfMon and the PAL Tool
9.1. PERFORMANCE MONITOR OVERVIEW9.1.1. Reliability and Performance Monitor in Windows Server 20089.1.1.1. Resource Overview9.1.1.2. Data Collector Sets9.1.1.3. Reliability Monitor9.1.1.4. Usability Enhancements in Windows 2008 PerfMon9.1.1.4.1. Auto-Scaling Counters9.1.1.4.2. Show/Hide Counters9.1.2. New Counters for SQL Server 2008 in PerfMon9.1.3. Troubleshooting SQL Server Problems9.1.3.1. Data-Driven Troubleshooting9.1.3.2. Choosing the Right Tool for the Job9.2. GETTING STARTED WITH PERFMON9.2.1. Monitoring Real-Time Server Activity9.2.2. Starting Out with Data Collector Sets9.2.3. Working with User-Defined Data Collector Sets9.2.3.1. Configuring Collector Properties9.2.3.2. Configuring Properties for Performance Counters9.2.3.3. PerfMon Log Formats9.2.3.4. Running PerfMon Remotely9.2.4. What to Be Aware of When Running PerfMon9.2.5. The Impact of Running PerfMon9.2.5.1. Sample Interval9.2.5.2. Number of Counters9.2.5.3. Disk Performance9.2.6. Servers with Very Poor Performance9.2.7. Common PerfMon Problems9.2.7.1. Using PerfMon on 64-bit Systems Using WOW9.2.7.2. Remote Monitoring Failures9.2.7.3. Missing SQL Server Counters9.2.7.4. Missing Counters or Numbers Instead of Names9.3. GETTING MORE FROM PERFORMANCE MONITOR9.3.1. Identifying SQL Server Bottlenecks9.3.1.1. Types of Bottleneck9.3.1.1.1. Configuration-Based Bottlenecks9.3.1.1.2. Schema-Based Bottlenecks9.3.1.2. Prescriptive Guidance9.3.1.3. Investigating CPU Problems9.3.1.3.1. CPU Performance Counters9.3.1.3.2. Common Causes of CPU Problems9.3.1.4. Investigating Memory-Related Problems9.3.1.4.1. Types of Memory Pressure9.3.1.4.2. Virtual Address Space9.3.1.4.3. Memory Performance Counters9.3.1.5. Disk or Storage Problems9.3.1.6. SQL Server Performance Problems9.3.2. Wait Stats Analysis9.3.3. Getting a Performance Baseline9.4. GETTING STARTED WITH PERFORMANCE ANALYSIS FOR LOGS (PAL)9.4.1. Templates and PAL9.4.2. Capturing PerfMon Logs9.4.3. Using PAL for Log Analysis9.5. OTHER PERFMON LOG ANALYSIS TOOLS9.5.1. Using SQL Server to Analyze PerfMon Logs9.5.2. Combining PerfMon Logs and SQL Profiler Traces9.5.3. Using Relog9.5.3.1. Extracting Performance Data for a Specific Timeframe9.5.3.2. Extracting Specific Performance Counters9.5.3.3. Converting Log Files to New Formats9.5.4. Using LogMan9.5.5. Using LogParser9.6. SUMMARY
10. Tracing SQL Server with SQL Trace and Profiler
10.1. TRACING 10110.1.1. Why Trace10.1.2. When to Trace10.1.3. Where to Trace10.1.4. What to Trace10.1.5. How to Trace10.2. THE ARCHITECTURE OF SQL TRACE10.3. EVENT CLASSIFICATION AND HIERARCHIES10.4. SQL TRACE CATALOG VIEWS10.4.1. sys.traces10.4.1.1. Is_rowset10.4.1.2. Is_rollover10.4.1.3. Is_shutdown10.4.1.4. Is_default10.4.1.5. Black Box vs. Default Trace10.4.2. sys.trace_categories10.4.3. sys.trace_events10.4.4. sys.trace_columns10.4.5. sys.trace_subclass_values10.4.6. sys.trace_event_bindings10.5. SQL TRACE PROCEDURES AND FUNCTIONS10.5.1. sp_trace_create10.5.2. sp_trace_setevent10.5.2.1. Performance Considerations10.5.3. sp_trace_setfilter10.5.3.1. Filtering a Range10.5.4. sp_trace_setstatus10.5.5. sp_trace_generateevent10.5.6. fn_trace_gettable10.5.7. fn_trace_geteventinfo10.5.8. fn_trace_getfilterinfo10.6. SECURING SQL TRACE10.6.1. Tracing Login Creation/Deletion10.6.2. Logins Changing Passwords10.6.3. When Tracing Login Can View Object Definitions and Parameter Values10.6.3.1. Logins Changing Trace Definitions10.6.4. Securing the Output of SQL Trace10.6.4.1. SQL Trace File Protection10.6.4.2. Rowset Provider Protection10.7. PROFILER10.7.1. Advanced Features of Profiler10.7.1.1. Grouping Events10.7.1.2. Extracting Event Data10.7.1.3. Importing PerfMon Data into Profiler10.7.2. Exporting a Trace Definition10.7.3. Exporting an Existing Server-Side Trace10.7.4. Tips & Tricks10.7.4.1. Turn Auto Scroll Off10.7.4.2. Edit the Base Template10.7.4.3. Firing Pre-Filtered Profiler10.8. SUMMARY
11. Consolidating Data Collection with SQLDiag and the PerfStats Script
11.1. APPROACHING DATA COLLECTION11.2. WHAT IS SQLDiag?11.2.1. An Overview of SQLDiag11.2.1.1. SQLDiag in SQL Server 2005 and SQL Server 200811.2.1.2. Components of SQLDiag11.2.1.3. Registering SQLDiag as a Service11.2.1.4. Where to Find SQLDiag11.2.1.5. Working with SQLDiag Configuration Files11.2.2. Using SQLDIAG11.2.2.1. Using SQLDiag on Failover Clusters11.2.2.2. Configuring the PerfMon Collector11.2.2.3. Configuring the Profiler Collector11.2.2.4. Adding Custom Collectors11.2.3. Running SQLDiag in Production11.2.3.1. Alert-Driven Data Collection with SQLDiag11.3. USING THE PERFSTATS SCRIPT11.3.1. What Is the PerfStats Script?11.3.2. Where to get the PerfStats Script11.3.3. Configuring the PerfStats Script11.3.4. Running the PerfStats Script11.3.5. Analyzing PerfStats Output11.3.5.1. Active Queries11.3.5.2. Instance-Wide Data11.4. SUMMARY
12. Introducing RML Utilities for Stress Testing and Trace File Analysis
12.1. WHEN TO USE RML UTILITIES12.1.1. Testing New Applications before Going Live12.1.2. Validating the Impact of a Change12.1.3. Determining the Purpose of Testing12.2. WHAT ARE RML UTILITIES?12.2.1. History of RML Utilities12.2.2. What's in the Download12.2.3. Components of RML Utilities12.2.3.1. ReadTrace12.2.3.1.1. Query Templates12.2.3.1.2. Getting Started with ReadTrace12.2.3.1.3. Limitations of ReadTrace12.2.3.2. Reporter12.2.3.3. OStress12.2.3.3.1. Stress Mode12.2.3.3.2. Replay Mode12.2.3.3.3. Getting Started with OStress12.2.3.3.4. Advanced OStress12.2.3.4. ORCA12.3. PERFORMANCE TESTING12.3.1. Testing Scenarios12.3.2. Ensuring a Fair Test12.3.2.1. Environment Preparation12.3.2.2. Test Execution12.3.3. Capturing a SQL Trace12.3.4. Analyzing Large Datasets12.4. SUMMARY
13. Bringing It All Together with SQL Nexus
13.1. GETTING STARTED13.1.1.13.1.1.1. Prerequisites13.1.1.2. Installation13.1.1.3. Startup13.2. DATA COLLECTION13.2.1. Is the Default Data Collection Good Enough?13.2.1.1. Performance Monitor (PerfMon) Log13.2.1.2. PerfStats Script13.2.1.3. SQL Trace13.2.2. Modifying the Data Collection13.2.3. Collecting Data for a Specific Instance13.2.4. Knowing How Much Data to Collect13.3. IMPORTING DATA13.4. AVAILABLE REPORTS13.5. EXAMPLE SCENARIO USING SQL NEXUS13.5.1. Configuring the Data Collection13.5.2. Generating the Workload13.5.3. Importing the Data13.5.4. Looking at the Bottlenecks13.5.5. Testing the Resolution13.6. ADDING YOUR OWN REPORTS13.6.1. Where Are the PerfMon Reports?13.7. SUMMARY
14. Using Management Studio Reports and the Performance Dashboard
14.1. USING THE STANDARD REPORTS14.1.1. Interpreting the Standard Server Reports14.1.1.1. Reading the Server Dashboard Report14.1.1.2. Configuration Changes and Schema Changes Reports14.1.1.3. Scheduler Health Report14.1.1.4. Memory Consumption Report14.1.1.5. Activity Reports14.1.1.6. Performance Executions Reports14.1.1.7. Performance Top Queries Reports14.1.1.8. Top Transactions Reports14.1.2. Interpreting the Database Reports14.1.2.1. Disk Usage Reports14.1.2.2. Backup and Restore Reports14.1.2.3. Index Usage Statistics Report14.1.2.4. Index Physical Statistics Report14.2. USING THE PERFORMANCE DASHBOARD14.2.1. Troubleshooting Common Errors14.2.1.1. "The stored procedures and functions required by the performance dashboard have not yet been installed."14.2.1.2. "Invalid column name 'cpu_ticks_in_ms'."14.2.1.3. "Difference of two datetime columns caused overflow at runtime."14.2.1.4. "Index (zero based) must be greater than or equal to zero and less than the size of the argument list."14.2.1.5. "The 'version_string' parameter is missing a value."14.2.2. Interpreting the Performance Dashboard Reports14.2.2.1. Historical Waits Report14.2.2.2. Missing Index Report14.3. BUILDING CUSTOM REPORTS14.3.1. Building a Custom Report with BIDS14.3.2. Picking Custom Reports Candidates14.4. SUMMARY
15. Using SQL Server Management Data Warehouse
15.1. INTRODUCING MANAGEMENT DATA WAREHOUSE15.1.1. Background to MDW15.1.2. MDW Architecture15.1.2.1. Data Collection Sets15.1.2.2. Data Warehouse15.1.2.3. Reports15.2. IMPLEMENTING MDW15.2.1. Creating a Management Data Warehouse15.2.2. Set Up Data Collection15.2.3. System Collection Sets15.2.4. Uses for MDW15.2.5. Performance Overhead15.3. REPORTING FROM MDW15.4. CUSTOM DATA COLLECTION AND REPORTING15.4.1. Defining Custom Collections15.4.2. SSAS Monitoring Scripts for the MDW15.5. SUMMARY

Content preview from Professional SQL Server® 2008 Internals and Troubleshooting

Chapter 15. Using SQL Server Management Data Warehouse

WHAT'S IN THIS CHAPTER

Purpose of the Management Data Warehouse (MDW)
Configuring the MDW
Creating custom collectors
Reporting on the MDW

The Management Data Warehouse provides a solution to medium and long-term database performance data collection and reporting. When SQL Server 2005 was released, the user community became very excited about the visibility of systems internals provided by dynamic management views (DMVs) and dynamic management functions (DMFs). However, the DMVs have a limitation because data is not persisted between service restarts. This is a limitation because data that is useful for performance analysis, trending, and capacity analysis is lost each time the SQL Server service is restarted.

Performance Data Collection is the method of collecting SQL Server performance data organized as sets, from targets for consolidation and reporting in the MDW. Three System Data Collection Sets are created during the MDW setup and these provide server performance overview. Additionally MDW provides a framework for custom data collection that is useful for independent software vendors or applications that were developed in-house. The Management Data Warehouse is a relational data warehouse configured and managed within SQL Server Management Studio.

INTRODUCING MANAGEMENT DATA WAREHOUSE

The Management Data Warehouse is intended as a centralized repository for performance data from servers across an enterprise, providing an out-of-the-box ...

Become an O’Reilly member and get unlimited access to this title plus top books and audiobooks from O’Reilly and nearly 200 top publishers, thousands of courses curated by job role, 150+ live events each month,
and much more.

Read now

Unlock full access

More than 5,000 organizations count on O’Reilly

O’Reilly covers everything we've got, with content to help us build a world-class technology community, upgrade the capabilities and competencies of our teams, and improve overall team performance as well as their engagement.

Julian F.

Head of Cybersecurity

I wanted to learn C and C++, but it didn't click for me until I picked up an O'Reilly book. When I went on the O’Reilly platform, I was astonished to find all the books there, plus live events and sandboxes so you could play around with the technology.

Addison B.

Field Engineer

I’ve been on the O’Reilly platform for more than eight years. I use a couple of learning platforms, but I'm on O'Reilly more than anybody else. When you're there, you start learning. I'm never disappointed.

Amir M.

Data Platform Tech Lead

I'm always learning. So when I got on to O'Reilly, I was like a kid in a candy store. There are playlists. There are answers. There's on-demand training. It's worth its weight in gold, in terms of what it allows me to do.

Mark W.

Embedded Software Engineer

Professional Microsoft® SQL Server® 2008 Administration

Publisher Resources

ISBN: 9780470484289Purchase book

Cloud Computing

Data Engineering

Data Science

AI & ML

Programming Languages

Software Architecture

IT/Ops

Security

Design

Business

Soft Skills

Professional SQL Server® 2008 Internals and Troubleshooting

by Christian Bolton, Justin Langford, Brent Ozar, James Rowland-Jones, Jonathan Kehayias, Cindy Gross, Steven Wort

Chapter 15. Using SQL Server Management Data Warehouse

INTRODUCING MANAGEMENT DATA WAREHOUSE

Become an O’Reilly member and get unlimited access to this title plus top books and audiobooks from O’Reilly and nearly 200 top publishers, thousands of courses curated by job role, 150+ live events each month,
and much more.