- ホーム
- > 洋書
- > 英文書
- > Computer / Databases
Full Description
Buy the print version of?Microsoft SQL Server 2012 Unleashed and get the eBook version for free! eBook version includes chapters 44-60 not included in the print. See inside the book for access code and details.?With up-to-the-minute content, this is the industry's most complete, useful guide to SQL Server 2012.?You'll find start-to-finish coverage of SQL Server's core database server and management capabilities: all the real-world information, tips, guidelines, and samples you'll need to create and manage complex database solutions. The additional online chapters add extensive coverage of SQL Server Integration Services, Reporting Services, Analysis Services, T-SQL programming, .NET Framework integration, and much more.?Authored by four expert SQL Server administrators, designers, developers, architects, and consultants, this book reflects immense experience with SQL Server in production environments. Intended for intermediate-to-advanced-level SQL Server professionals, it focuses on the product's most complex and powerful capabilities, and its newest tools and features.Understand SQL Server 2012's newest features, licensing changes, and capabilities of each edition Manage SQL Server 2012 more effectively with SQL Server Management Studio, the SQLCMD command-line query tool, and Powershell Use Policy-Based Management to centrally configure and operate SQL Server Utilize the new Extended Events trace capabilities within SSMS Maximize performance by optimizing design, queries, analysis, and workload management Implement new best practices for SQL Server high availability Deploy AlwaysOn Availability Groups and Failover Cluster Instances to achieve enterprise-class availability and disaster recovery Leverage new business intelligence improvements, including Master Data Services, Data Quality Services and Parallel Data Warehouse Deliver better full-text search with SQL Server 2012's new Semantic Search Improve reporting with new SQL Server 2012 Reporting Services featuresDownload the following from informit.com/title/9780672336928: Sample databases and code examples??
Contents
Introduction 1Who This Book Is For 2What This Book Covers 2Conventions Used in This Book 4Good Luck! 6Part I Welcome to Microsoft SQL Server1 SQL Server 2012 Overview 9SQL Server Components and Features 9The SQL Server Database Engine 10SQL Server 2012 Administration and Management Tools 12Replication 16Merge Replication 17Database Mirroring 17SQL Server AlwaysOn Features 18SQL Server Service Broker 19Full-Text and Semantic Search 20SQL Server Integration Services (SSIS) 21SQL Server Analysis Services (SSAS) 22SQL Server Reporting Services (SSRS) 24Master Data Services 25Data Quality Services 26SQL Server 2012 Editions 26SQL Server 2012 Standard Edition 26SQL Server 2012 Enterprise Edition 27Differences Between the Enterprise and Standard Editions of SQL Server 28Other SQL Server 2012 Editions 29SQL Server Licensing Models 32Web Edition Licensing 33Developer Edition Licensing 33Express Edition Licensing 33Choosing a Licensing Model 33Mixing Licensing Models 34Passive Server/Failover Licensing 34Virtual Server Licensing 34Summary 352 What's New in SQL Server 2012 37New SQL Server 2012 Features 37New and Improved Storage Features 38New Transact-SQL Constructs 39New Scalability and Performance Features 40New Security Features 40New Availability Features 40Statistical Semantic Search 41Data Quality Services 41SQL Server 2012 Enhancements 42SQL Server Management Studio Enhancements 42Resource Governor Enhancements 43Spatial Data Enhancements 43Integration Services Enhancements 43Service Broker Enhancements 44Full-Text Search Enhancements 44Analysis Services Enhancements 45Reporting Services Enhancements 45Master Data Services Enhancements 46Deprecated Features 46Summary 473 Examples of SQL Server Implementations 49Application Terms 50OLTP Application Examples 51OLTP ERP Example 51OLTP Shopping Cart Example 53DSS Application Examples 55DSS Example One 55DSS Example Two 56DSS Example Three 57Summary 59Part II SQL Server Tools and Utilities4 SQL Server Management Studio 63What's New in SSMS 63The Integrated Environment 64Window Management 64Integrated Help 67Administration Tools 71Registered Servers 71Object Explorer 72Activity Monitor 75Log File Viewer 77SQL Server Utility 78Development Tools 84The Query Editor 84Managing Projects in SSMS 92Integrating SSMS with Source Control 93Using SSMS Templates 95Using SSMS Snippets 99T-SQL Debugging 100Multiserver Queries 102Summary 1025 SQL Server Command-Line Utilities 105What's New in SQL Server Command-Line Utilities 106The sqlcmd Command-Line Utility 107Executing the sqlcmd Utility 108Using Scripting Variables with sqlcmd 111The dta Command-Line Utility 111The tablediff Command-Line Utility 114The bcp Command-Line Utility 117The sqldiag Command-Line Utility 118The sqlservr Command-Line Utility 120The SqlLocalDB Command-Line Utility 121Summary 1236 SQL Server Profiler 125What's New with SQL Server Profiler 125SQL Server Profiler Architecture 126Creating Traces 127Events 129Data Columns 131Filters 134Executing Traces and Working with Trace Output 136Saving and Exporting Traces 137Saving Trace Output to a File 137Saving Trace Output to a Table 137Saving the Profiler GUI Output 138Importing Trace Files 138Importing a Trace File into a Trace Table 139Analyzing Trace Output with the Database EngineTuning Advisor 142Replaying Trace Data 142Defining Server-Side Traces 144Monitoring Running Traces 155Stopping Server-Side Traces 156Profiler Usage Scenarios 158Analyzing Slow Stored Procedures or Queries 158Deadlocks 159Identifying Ad Hoc Queries 161Identifying Performance Bottlenecks 162Monitoring Auto-Update Statistics 164Monitoring Application Progress 164Summary 166Part III SQL Server Administration7 SQL Server System and Database Administration 169What's New in SQL Server System and Database Administration 169System Administrator Responsibilities 170System Databases 171The master Database 171The resource Database 172The model Database 172The msdb Database 172The distribution Database 172The tempdb Database 173Maintaining System Databases 173System Tables 174System Views 175Compatibility Views 176Catalog Views 178Information Schema Views 180Dynamic Management Views 182System Stored Procedures 185Useful System Stored Procedures 186Summary 1878 Installing SQL Server 2012 189What's New in Installing SQL Server 2012 189Installation Requirements 190Hardware Requirements 190Software Requirements 191Installation Walkthrough 195Install Screens, Step-by-Step 196Installing SQL Server Documentation 216Installing SQL Server Using a Configuration File 218Running an Automated or Manual Install 224Installing Service Packs and Cumulative Updates 225Applying a Service Pack or Cumulative Update Duringa New Installation 225Applying a Service Pack or Cumulative Update to anExisting Installation 227Installing a Service Pack from the Command Line 231Summary 2329 Upgrading to SQL Server 2012 233What's New in Upgrading SQL Server 233SQL Server 2012 233The SQL Server 2012 Upgrade Matrix 234Identifying Products and Features to be Upgraded 236Using the SQL Server Upgrade Advisor (UA) 237Getting Started with the UA 237The Analysis Wizard 239The Report Viewer 244Destination: SQL Server 2012 245Side-by-Side Upgrade 246Upgrading In-Place 254Upgrading the Database Engine 256Installing Product Updates (Slipstreaming)During Upgrades 258Upgrading Using a Configuration File 260Upgrading from Pre-SQL Server 2005 Versions 261Upgrading Other SQL Server Components 261Upgrading Analysis Services 262Upgrading SQL Server Analysis Services 262Upgrading Reporting Services 262Performing an In-Place Upgrade of Reporting Services 262Migrating to Reporting Services 2012 264Upgrading SSIS Packages 265Migrating DTS Packages 266Summary 26710 Client Installation and Configuration 269What's New in Client Installation and Configuration 269Client/Server Networking Considerations 270Server Network Protocols 271The Server Endpoint Layer 273The Role of SQL Browser 276Client Installation 277Installing the Client Tools 277Installing SNAC 278Client Configuration 280Client Configuration Using SSCM 280Connection Encryption 283Client Data Access Technologies 285Provider Choices 286Connecting Using the Various Providers and Drivers 287General Networking Considerations and Troubleshooting 293Summary 29511 Database Backup and Restore 297What's New in Database Backup and Restore 297Developing a Backup and Restore Plan 298Types of Backups 299Full Database Backups 300Differential Database Backups 300Partial Backups 301Differential Partial Backups 301File and Filegroup Backups 301Copy-Only Backups 302Transaction Log Backups 302Recovery Models 302Full Recovery 303Bulk-Logged Recovery 304Simple Recovery 305Backup Devices 306Disk Devices 306Tape Devices 307Network Shares 307Media Sets and Families 307Creating Backup Devices 308Backing Up a Database 308Creating Database Backups with SSMS 308Creating Database Backups with T-SQL 311Backing Up the Transaction Log 314Creating Transaction Log Backups with SSMS 314Creating Transaction Log Backups with T-SQL 315Backup Scenarios 316Full Database Backups Only 316Full Database Backups with Transaction Log Backups 317Differential Backups 318Partial Backups 319File/Filegroup Backups 321Mirrored Backups 322Copy-Only Backups 323Compressed Backups 323System Database Backups 324Restoring Databases and Transaction Logs 324Restores with T-SQL 325Restoring by Using SSMS 329Restore Information 333Restore Scenarios 337Restoring to a Different Database 337Restoring a Snapshot 338Restoring a Transaction Log 338Restoring to the Point of Failure 339Restoring to a Point in Time 342Online Restores 342Restoring the System Databases 344Additional Backup Considerations 346Frequency of Backups 346Using a Standby Server 347Snapshot Backups 348Considerations for Very Large Databases 348Maintenance Plans 348Summary 34912 Database Mail 351What's New in Database Mail 351Setting Up Database Mail 352Creating Mail Profiles and Accounts 353Using T-SQL to Update and Delete Mail Objects 356Setting System-Wide Mail Settings 357Testing Your Setup 357Sending and Receiving with Database Mail 358The Service Broker Architecture 358Sending Email 358Receiving Email 364Using SQL Server Agent Mail 364Job Mail Notifications 365Creating an Operator 365Enabling SQL Agent Mail 365Creating the Job 365Testing the Job-Completion Notification 366Alert Mail Notifications 367Creating an Alert 367Testing the Alert Notification 368Related Views and Procedures 368Viewing the Mail Configuration Objects 368Viewing Mail Message Data 369Summary 37113 SQL Server Agent 373What's New in Scheduling and Notification 374Configuring the SQL Server Agent 374Configuring SQL Server Agent Properties 374Configuring the SQL Server Agent Startup Account 376Configuring Email Notification 378SQL Server Agent Proxy Account 380Viewing the SQL Server Agent Error Log 381SQL Server Agent Security 382Managing Operators 383Managing Jobs 385Defining Job Properties 385Defining Job Steps 386Defining Multiple Jobs Steps 389Defining Job Schedules 390Defining Job Notifications 391Viewing Job History 392Managing Alerts 393Defining Alert Properties 393Defining Alert Responses 397Scripting Jobs and Alerts 399Multiserver Job Management 400Creating a Master Server 401Enlisting Target Servers 401Creating Multiserver Jobs 402Event Forwarding 402Summary 40314 Administering SQL Server 2012 with PowerShell 405What's New with PowerShell 405Overview of PowerShell 406Start Using PowerShell Now 407Common Terminology 407Object-Based Functionality 408SQL Server Management Objects 408WMI 409Installing PowerShell 409PowerShell Console 409Scriptable and Interactive 410Default Security 411Execution Policy 411Profiles 411Built-In Help Features 411PowerShell Scripting Basics 414A Few Basic Cmdlets 414Creating a PowerShell Script 415Adding Comments 415Variables 416Escaping Characters 417Special Variable $_ 418Joining Variables and Strings 418Passing Arguments 419Using Param 419Arrays 420Operators 421Conditional Statements 421Functions 422Looping Statements 423Filtering Cmdlets 424Formatting Cmdlets 425Dealing with CSV Files 426Dealing with Dates and Times 427-WhatIf/-Confirm Parameters 428PowerShell in SQL Server 2012 428Adding PowerShell Support 428Accessing SQL Server PowerShell 429SQL Server PowerShell 430SQL Provider 431SQL Cmdlets 432SQL Server Agent Support 432Step-by-Step Examples 432General Tasks 433Scheduling Scripts 433Common OS-Related Tasks 435SQL Server-Specific Tasks 437Using the Provider 438Creating a Database Table 438Performing a Database Backup 440Checking Server Settings 441Checking the Database Usage 442Getting Table Properties 443Cmdlet Example: Invoke-SqlCmd 443Cmdlet Example: Invoke-PolicyEvaluation 444Joining Columns 444Retrieving an Entry 445Summary 44515 SQL Server Policy-Based Management 447What's New in Policy-Based Management 447Introduction to Policy-Based Management 448Policy-Based Management Concepts 449Facets 449Conditions 452Policies 453Categories 453Targets 453Execution Modes 453Central Management Servers 454Implementing Policy-Based Management 456Creating a Condition Based on a Facet 456Creating a Category 460Evaluating Policies 462Importing and Exporting Policies 462Sample Templates and Real-World Examples 464Sample Policy Templates 464Evaluating Recovery Models 464Implementing Surface Area Configuration Checks 464Ensuring Object Naming Conventions 465Checking Best Practices Compliance 465Policy-Based Management Best Practices 465Summary 466Part IV SQL Server Security Administration16 Security and Compliance 469Exposure and Risk 470Across the Life Cycle 471The Security Big Picture 472Identity Access Management Components 474Data Security Compliance and SQL Server 476SQL Server Auditing 478Setting Up Auditing via T-SQL 484SQL Injection Is Easy to Do 485Summary 48717 Security and User Administration 489What's New in Security and User Administration 489An Overview of SQL Server Security 490Authentication Methods 493Windows Authentication Mode 493Mixed Authentication Mode 493Setting the Authentication Mode 493Managing Principals 494Logins 494SQL Server Security: Users 497The dbo User 498The guest User 498The INFORMATION_SCHEMA User 499The sys User 499User/Schema Separation 500Roles 501Fixed Server Roles 501Fixed Database Roles 502The public Role 505User-Defined Database Roles 506User-Defined Server Roles 507Application Roles 508Managing Securables 508Managing Permissions 509Managing SQL Server Logins 511Using SSMS to Manage Logins 512Using T-SQL to Manage Logins 516Managing SQL Server Users 517Using SSMS to Manage Users 517Using T-SQL to Manage Users 519Managing Database Roles 520Using SSMS to Manage Database Roles 520Using T-SQL to Manage Database Roles 521Managing Server Roles 522Using SSMS to Manage Server Roles 522Using T-SQL to Manage Server Roles 522Managing SQL Server Permissions 524Using SSMS to Manage Permissions 524Using SSMS to Manage Permissions at the Server Level 524Using SSMS to Manage Permissions at the Database Level 527Using SSMS to Manage Permissions at the Object Level 529Using T-SQL to Manage Permissions 531The Execution Context 533Explicit Context Switching 533Implicit Context Switching 534Summary 53518 Data Encryption 537What's New in Data Encryption 538An Overview of Data Security 538An Overview of Data Encryption 539SQL Server Key Management 541Extensible Key Management 543Column-Level Encryption 544Encrypting Columns Using a Passphrase 545Encrypting Columns Using a Certificate 547Transparent Data Encryption 551Implementing Transparent Data Encryption 552Managing TDE in SSMS 554Backing Up TDE Certificates and Keys 556The Limitations of TDE 557Column-Level Encryption Versus TransparentData Encryption 557Summary 558Part V Database Administration19 Creating and Managing Databases 563What's New in Creating and Managing Databases 564Data Storage in SQL Server 564Database Files 565Primary Files 566Secondary Files 566Using Filegroups 567Using Partitions 570Transaction Log Files 570Creating Databases 572Using SSMS to Create a Database 572Using T-SQL to Create Databases 576Setting Database Options 577The Database Options 577Using T-SQL to Set Database Options 580Retrieving Option Information 581Managing Databases 583Managing File Growth 583Expanding Databases 584Shrinking Databases 585Moving Databases 589Restoring a Database to a New Location 590Using ALTER DATABASE 590Detaching and Attaching Databases 591Contained Databases 594Creating a Contained Database 594Connecting to a Contained Database 597Summary 59820 Creating and Managing Tables 599What's New in SQL Server 2012 599Creating Tables 600Using Object Explorer to Create Tables 600Using Database Diagrams to Create Tables 601Using T-SQL to Create Tables 602Defining Columns 605Data Types 605Column Properties 612Column Sets 618Working with Sparse Columns 619Sparse Columns: Good or Bad? 623Defining Sparse Columns in SSMS 623Defining Table Location 623Defining Table Constraints 625Modifying Tables 627Using T-SQL to Modify Tables 627Using Object Explorer and the Table Designer to Modify Tables 630Using Database Diagrams to Modify Tables 633Dropping Tables 635Using Partitioned Tables 636Creating a Partition Function 637Creating a Partition Scheme 640Creating a Partitioned Table 641Adding and Dropping Table Partitions 644Switching Table Partitions 648Using FILESTREAM Storage 652Enabling FILESTREAM Storage 653Setting Up a Database for FILESTREAM Storage 656Using FILESTREAM Storage for Data Columns 657Using FileTables 660FileTable Prerequisites 660Creating FileTables 661Copying Files to the FileTable 662Creating Temporary Tables 663Summary 66421 Creating and Managing Indexes 665What's New in Creating and Managing Indexes 665Types of Indexes 666Clustered Indexes 666Nonclustered Indexes 668Creating Indexes 669Creating Indexes with T-SQL 670Creating Indexes with SSMS 673Managing Indexes 676Managing Indexes with T-SQL 677Managing Indexes with SSMS 680Dropping Indexes 681Online Indexing Operations 681Indexes on Views 683Summary 68422 Implementing Data Integrity 685What's New in Data Integrity 685Types of Data Integrity 686Domain Integrity 686Entity Integrity 686Referential Integrity 686Enforcing Data Integrity 686Implementing Declarative Data Integrity 686Implementing Procedural Data Integrity 687Using Constraints 687The PRIMARY KEY Constraint 687The UNIQUE Constraint 689The FOREIGN KEY Referential Integrity Constraint 690The CHECK Constraint 694Creating Constraints 695Managing Constraints 700Rules 704Defaults 705Declarative Defaults 705Bound Defaults 707When a Default Is Applied 707Restrictions on Defaults 709Summary 71023 Creating and Managing Views 711What's New in Creating and Managing Views 711Definition of Views 711Using Views 712Simplifying Data Manipulation 713Focusing on Specific Data 714Abstracting Data 715Controlling Access to Data 716Creating Views 718Creating Views Using T-SQL 719ENCRYPTION 721Creating Views Using the View Designer 723Managing Views 725Altering Views with T-SQL 726Dropping Views with T-SQL 726Managing Views with SSMS 727Data Modifications and Views 727Partitioned Views 728Modifying Data Through a Partitioned View 732Distributed Partitioned Views 732Indexed Views 734Creating Indexed Views 734Indexed Views and Performance 737To Expand or Not to Expand 740Summary 74124 Creating and Managing Stored Procedures 743What's New in Creating and Managing Stored Procedures 743Advantages of Stored Procedures 743Creating Stored Procedures 745Creating Procedures in SSMS 746Executing Stored Procedures 753Executing Procedures in SSMS 754Execution Context and the EXECUTE AS Clause 755Using the WITH RESULT SETS Clause 758Deferred Name Resolution 761Identifying Objects Referenced in Stored Procedures 763Viewing Stored Procedures 765Modifying Stored Procedures 769Viewing and Modifying Stored Procedures with SSMS 769Using Input Parameters 771Setting Default Values for Parameters 772Passing Object Names as Parameters 775Using Wildcards in Parameters 777Using Table-Valued Parameters 778Using Output Parameters 780Returning Procedure Status 781Debugging Stored Procedures Using SQL Server Management Studio 783Using System Stored Procedures 785Startup Procedures 789Summary 79325 Creating and Managing User-Defined Functions 795Why Use User-Defined Functions? 795Types of User-Defined Functions 798Scalar Functions 798Table-Valued Functions 801Creating and Managing User-Defined Functions 803Creating User-Defined Functions 803Viewing and Modifying User-Defined Functions 814Managing User-Defined Function Permissions 823Rewriting Stored Procedures as Functions 824Creating and Using CLR Functions 825Adding CLR Functions to a Database 825Deciding Between Using T-SQL or CLR Functions 827Summary 82826 Creating and Managing Triggers 829What's New in Creating and Managing Triggers 830Using DML Triggers 830Creating DML Triggers 831Using AFTER Triggers 833Using inserted and deleted Tables 837INSTEAD OF Triggers 841Using DDL Triggers 849Creating DDL Triggers 853Managing DDL Triggers 857Using CLR Triggers 859Using Nested Triggers 862Using Recursive Triggers 862Summary 86327 Transaction Management and the Transaction Log 865What's New in Transaction Management 865What Is a Transaction? 865How SQL Server Manages Transactions 866Defining Transactions 867AutoCommit Transactions 867Explicit User-Defined Transactions 868Implicit Transactions 874Implicit Transactions Versus Explicit Transactions 877Transactions and Batches 877Transactions and Stored Procedures 879Transactions and Triggers 884Triggers and Transaction Nesting 885Triggers and Multistatement Transactions 888Using Savepoints in Triggers 889Transactions and Locking 890READ_COMMITTED_SNAPSHOT Isolation 892Coding Effective Transactions 892Transaction Logging and the Recovery Process 893The Checkpoint Process 894Automatic Checkpoints 896Indirect Checkpoints 897Manual Checkpoints 899The Recovery Process 900Managing the Transaction Log 902Long-Running Transactions 906Distributed Transactions 909Summary 91028 Database Snapshots 911What's New with Database Snapshots 912What Are Database Snapshots? 912Limitations and Restrictions of Database Snapshots 917Copy-on-Write Technology 918When to Use Database Snapshots 919Reverting to a Snapshot for Recovery Purposes 919Safeguarding a Database Prior to Making Mass Changes 921Providing a Testing (or Quality Assurance) StartingPoint (Baseline) 921Providing a Point-in-Time Reporting Database 922Providing a Highly Available and Offloaded ReportingDatabase from a Database Mirror 923Setup and Breakdown of a Database Snapshot 924Creating a Database Snapshot 925Removing a Database Snapshot 930Reverting to a Database Snapshot for Recovery 930Reverting a Source Database from a Database Snapshot 930Using Database Snapshots with Testing and QA 931Database Snapshots Maintenance and Security Considerations 932Security for Database Snapshots 932Snapshot Sparse File Size Management 932Number of Database Snapshots per Source Database 933Summary 93329 Database Maintenance 935What's New in Database Maintenance 936The Maintenance Plan Wizard 936Backing Up Databases 937Checking Database Integrity 941Shrinking Databases 942Maintaining Indexes and Statistics 943Scheduling a Maintenance Plan 947Managing Maintenance Plans Without the Wizard 950Executing a Maintenance Plan 954Maintenance Without a Maintenance Plan 955Database Maintenance Policies 956Summary 956Part VI SQL Server Performance and Optimization30 Data Structures, Indexes, and Performance 959What's New for Data Structures, Indexes, and Performance 960Understanding Data Structures 960Database Files and Filegroups 961Primary Data File 962Secondary Data Files 963The Log File 963File Management 963Using Filegroups 965FILESTREAM Filegroups 967Database Pages 969Page Types 970Data Pages 971Row-Overflow Pages 977LOB Data Pages 978Index Pages 980Space Allocation Structures 981Extents 981Global and Shared Global Allocation Map Pages 982Page Free Space Pages 983Index Allocation Map Pages 983Differential Changed Map Pages 984Bulk Changed Map Pages 984Data Compression 985Row-Level Compression 985Page-Level Compression 988The CI Record 990Implementing Page Compression 990Evaluating Page Compression 991Managing Data Compression with SSMS 994Understanding Table Structures 995Heap Tables 997Clustered Tables 999Understanding Index Structures 1000Clustered Indexes 1001Nonclustered Indexes 1004Columnstore Indexes 1008Data Modification and Performance 1013Inserting Data 1013Deleting Rows 1016Updating Rows 1017Index Utilization 1018Index Selection 1020Evaluating Index Usefulness 1021Index Statistics 1024The Statistics Histogram 1026How the Statistics Histogram Is Used 1028Index Densities 1029Estimating Rows Using Index Statistics 1030Generating and Maintaining Index and Column Statistics 1031SQL Server Index Maintenance 1040Setting the Fill Factor 1050Reapplying the Fill Factor 1052Disabling Indexes 1052Managing Indexes with SSMS 1054Index Design Guidelines 1054Clustered Index Indications 1055Nonclustered Index Indications 1057Index Covering 1059Included Columns 1061Wide Indexes Versus Multiple Indexes 1062Indexed Views 1062Indexes on Computed Columns 1064Filtered Indexes and Statistics 1065Creating and Using Filtered Indexes 1067Creating and Using Filtered Statistics 1069Choosing Indexes: Query Versus Update Performance 1070Identifying Missing Indexes 1071The Database Engine Tuning Advisor 1072Missing Index Dynamic Management Objects 1072Missing Index Feature Versus Database EngineTuning Advisor 1074Identifying Unused Indexes 1075Summary 107831 Understanding Query Optimization 1079What's New in Query Optimization 1080What Is the Query Optimizer? 1080Query Compilation and Optimization 1081Compiling DML Statements 1081Optimization Steps 1082Query Analysis 1083Identifying Search Arguments 1083Identifying OR Clauses 1083Identifying Join Clauses 1084Row Estimation and Index Selection 1085Evaluating SARG and Join Selectivity 1085Estimating Access Path Cost 1090Using Multiple Indexes 1097Optimizing with Indexed Views 1104Optimizing with Filtered Indexes 1107Join Selection 1109Join Processing Strategies 1109Determining the Optimal Join Order 1114Subquery Processing 1115Execution Plan Selection 1118Query Plan Caching 1121Query Plan Reuse 1121Query Plan Aging 1123Recompiling Query Plans 1124Monitoring the Plan Cache 1125Other Query Processing Strategies 1133Predicate Transitivity 1133Group by Optimization 1134Queries with DISTINCT 1134Queries with UNION 1135Queries Using Columnstore Indexes 1136Parallel Query Processing 1137Parallel Query Configuration Options 1138Identifying Parallel Queries 1139Common Query Optimization Problems 1140Out-of-Date or Insufficient Statistics 1140Poor Index Design 1141Search Argument Problems 1141Large Complex Queries 1143Triggers 1143Managing the Optimizer 1144Optimizer Hints 1145Forced Parameterization 1151Using the USE PLAN Query Hint 1153Using Plan Guides 1155Limiting Query Plan Execution with the Query Governor 1163Summary 116632 Query Analysis 1167What's New in Query Analysis 1168Query Analysis in SSMS 1168Execution Plan ToolTips 1169Logical and Physical Operator Icons 1173Analyzing Stored Procedures 1181Saving and Viewing Graphical Execution Plans 1181Displaying Execution Plan XML 1183Missing Index Hints 1184SSMS Client Statistics 1188Using the SET SHOWPLAN Options 1190SHOWPLAN_TEXT 1190SHOWPLAN_ALL 1192SHOWPLAN_XML 1193Using sys.dm_exec_query_plan 1194Query Statistics 1196STATISTICS IO 1196STATISTICS TIME 1199Using datediff() to Measure Runtime 1202STATISTICS PROFILE 1203STATISTICS XML 1203Query Analysis with SQL Server Profiler 1204Summary 120633 Locking and Performance 1207What's New in Locking and Performance 1207The Need for Locking 1207Transaction Isolation Levels in SQL Server 1208Read Uncommitted Isolation 1209Read Committed Isolation 1210Read Committed Snapshot Isolation 1211Repeatable Read Isolation 1212Serializable Read Isolation 1213Snapshot Isolation 1213The Lock Manager 1216Monitoring Lock Activity in SQL Server 1217Querying the sys.dm_tran_locks View 1217Viewing Locking Activity with SQL Server Profiler 1221Monitoring Locks with Performance Monitor 1223SQL Server Lock Types 1225Shared Locks 1226Update Locks 1227Exclusive Locks 1228Intent Locks 1228Schema Locks 1229Bulk Update Locks 1230SQL Server Lock Granularity 1230Serialization and Key-Range Locking 1232Using Application Locks 1236Index Locking 1239Row-Level Versus Page-Level Locking 1240Lock Escalation 1241Lock Compatibility 1243Locking Contention and Deadlocks 1243Identifying Locking Contention 1244Setting the Lock Timeout Interval 1247Minimizing Locking Contention 1248Deadlocks 1249Table Hints for Locking 1263Transaction Isolation-Level Hints 1264Lock Granularity Hints 1266Lock Type Hints 1266Optimistic Locking 1266Optimistic Locking Using the rowversion Data Type 1267Optimistic Locking with Snapshot Isolation 1269Summary 127234 Database Design and Performance 1273What's New in Database Design and Performance 1273Basic Tenets of Designing for Performance 1274Logical Database Design Issues 1275Normalization Conditions 1275Normalization Forms 1275Benefits of Normalization 1277Drawbacks of Normalization 1277Denormalizing a Database 1277Denormalization Guidelines 1278Essential Denormalization Techniques 1279Database Filegroups and Performance 1285RAID Technology 1287RAID Level 0 1287RAID Level 1 1289RAID Level 10 1290RAID Level 5 1290SQL Server and SAN Technology 1292What Is a SAN? 1292SAN Considerations for SQL Server 1293Summary 129435 Monitoring SQL Server Performance 1295What's New in Monitoring SQL Server Performance 1296Performance Monitoring Tools 1297The Data Collector and the MDW 1297SQL Server Utility 1319SQL Server Extended Events 1323Windows Performance Monitor 1359A Performance Monitoring Approach 1371Monitoring the Network Interface 1372Monitoring the Processors 1374Monitoring Memory 1379Monitoring the Disk System 1382Monitoring SQL Server's Disk Activity 1384Monitoring Other SQL Server Performance Items 1385Summary 138636 SQL Server Database Engine Tuning Advisor 1387What's New in SQL Server Database Engine Tuning Advisor 1387SQL Server Instance Architecture 1388Database Engine Tuning Advisor 1389The Database Engine Tuning Advisor GUI 1390The Database Engine Tuning Advisor Command Line 1397Summary 140537 Managing Workloads with the Resource Governor 1407What's New for Resource Governor 1408Overview of Resource Governor 1408Resource Governor Components 1410Classification 1410Resource Pools 1410Workload Groups 1412Configuring Resource Governor 1413Enabling Resource Governor 1413Defining Resource Pools 1415Defining Workload Groups 1417Creating a Classifier Function 1421Monitoring Resource Usage 1424Modifying Your Resource Governor Configuration 1428Deleting Workload Groups 1429Deleting Resource Pools 1430Modifying a Classifier Function 1431Summary 143138 A Performance and Tuning Methodology 1433The Full Architectural Landscape 1434Primary Performance and Tuning Handles 1435A Performance and Tuning Methodology 1436Designing In Performance and Tuning from the Start 1437Code and Test 1440Performance and Tuning for an Existing Implementation 1442Performance and Tuning Design Guidelines 1448Hardware and Operating System Guidelines 1448SQL Server Instance Guidelines 1450Database-Level Guidelines 1451Table Design Guidelines 1451Indexing Guidelines 1453View Design Guidelines 1455Transact-SQL Guidelines 1455Application Design Guidelines 1459Distributed Data Guidelines 1460High-Availability Guidelines 1460Tools of the Performance and Tuning Trade 1461Microsoft Out-of-the-Box 1461Third-Party Performance and Tuning Tools 1462Summary 1464Part VII SQL Server High Availability39 SQL Server High Availability Fundamentals 1467What's New in High Availability 1468What Is High Availability? 1469The Fundamentals of HA 1471Hardware Factors 1471Backup Considerations 1471Operating System Upgrades 1472Vendor Agreements Followed 1472Training Kept Up-to-Date 1472Quality Assurance Done Well 1472Standards/Procedures Followed 1472Server Instance Isolation 1472Building Solutions with One or More HA Options 1474Failover Cluster Services (FCS) 1475SQL Clustering 1476AlwaysOn Failover Clustering Instance (FCI) 1478AlwaysOn Availability Groups 1479Data Replication 1480Change Data Capture 1482Log Shipping 1482Database Mirroring 1484Combining Failover with Scale-Out Options 1485Other HA Techniques That Yield Great Results 1486High Availability from the Windows Server Family Side 1489Microsoft Virtual Machines and Hyper-V 1489Summary 149040 Data Replication 1493What's New in Data Replication 1494What Is Replication? 1495The Publisher, Distributor, and SubscriberMagazine Metaphor 1497Publications and Articles 1498Filtering Articles 1498Replication Scenarios 1503The Central Publisher Replication Model 1503The Central Publisher with Remote DistributorReplication Model 1504The Publishing Subscriber Replication Model 1506The Central Subscriber Replication Model 1507The Multiple Publishers with Multiple SubscribersReplication Model 1507The Updating Subscribers Replication Model 1509The Peer-to-Peer Replication Model 1511Subscriptions 1511Anonymous Subscriptions (Pull Subscriptions) 1513The Distribution Database 1513Replication Agents 1515The Snapshot Agent 1516The Log Reader Agent 1518The Distribution Agent 1519The Merge Agent 1520Other Specialized Agents 1520Planning for SQL Server Data Replication 1521Autonomy, Timing, and Latency of Data 1522Methods of Data Distribution 1522SQL Server Replication Types 1523Snapshot Replication 1523Transactional Replication 1524Merge Replication 1525Basing the Replication Design on User Requirements 1526Data Characteristics 1528Setting Up Replication 1529Creating a Distributor and Enabling Publishing 1529Creating a Publication 1535Horizontal and Vertical Filtering 1542Creating Subscriptions 1544Scripting Replication 1550Monitoring Replication 1555Replication Monitoring SQL Statements 1555Monitoring Replication within SQL Server ManagementStudio 1557Troubleshooting Replication Failures 1559Peer-to-Peer Replication 1560The Performance Monitor 1566Replication in Heterogeneous Environments 1566Backup and Recovery in a Replication Configuration 1567Some Thoughts on Performance 1568Log Shipping 1569Data Replication and Database Mirroring or AlwaysOnFeatures for Fault Tolerance and High Availability 1569Change Data Capture 1570The Change Data Capture Tables 1571Enabling CDC for a Database 1572Enabling CDC for a Table 1573Querying the CDC Tables 1575CDC and DDL Changes to Source Tables 1581CDC and AlwaysOn Availability Groups 1582Change Tracking 1582Implementing Change Tracking 1583Identifying Tracked Changes 1585Identifying Changed Columns 1589Change Tracking Overhead 1590Summary 159141 Database Mirroring 1593What's New in Database Mirroring 1594What Is Database Mirroring? 1594Copy-on-Write Technology 1596When to Use Database Mirroring 1597Roles of the Database Mirroring Configuration 1597Playing Roles and Switching Roles 1598Database Mirroring Operating Modes 1598Setting Up and Configuring Database Mirroring 1599Getting Ready to Mirror a Database 1600Creating the Endpoints 1603Granting Permissions 1605Creating the Database on the Mirror Server 1605Identifying the Other Endpoints for Database Mirroring 1607Configuring Database Mirroring by Using the Wizard 1609Monitoring a Mirrored Database Environment 1616Removing Mirroring 1619Testing Failover from the Principal to the Mirror 1621Client Setup and Configuration for Database Mirroring 1623Using Replication and Database Mirroring Together 1624Using Database Snapshots from a Mirror for Reporting 1625Summary 162742 SQL Server Failover Clustering 1629What's New in SQL Server Failover Clustering 1630How Microsoft SQL Server Failover Clustering Works 1630Understanding WSFC 1632Extending WSFC with NLB 1636How WSFC Sets the Stage for SQL Server Clustering 1637Installing SQL Server Failover Clustering 1638Configuring SQL Server Database Disks 1639Installing Network Interfaces 1641Installing WSFC 1641Cluster Events 1642Installing SQL Server within WSFC 1642Fail Over to Another Node 1655The Client Connection Impact of a Failover 1655Potential Problems to Watch Out for with SQL ServerFailover Clustering 1658Summary 165943 SQL Server AlwaysOn and Availability Groups 1661SQL Server AlwaysOn and Availability Groups 1661Windows Failover Cluster Services 1662AlwaysOn Failover Clustering Instances 1662AlwaysOn Availability Groups 1663Combining Failover with Scale-Out Options 1666Building a Multinode AlwaysOn Configuration 1666Adding Replicas 1681Dashboard and Monitoring 1681Summary 1683Part VIII SQL Server Application Development44 What's New for Transact-SQL in SQL Server 2012 1687THROW Statement 1687The SEQUENCE Object 1693SEQUENCE Objects Versus Identity Columns 1701New Conversion Functions 1702The PARSE Function 1702The TRY_PARSE Function 1703The TRY_CONVERT Function 1704New Logical Functions 1705The CHOOSE Function 1705The IIF Function 1706New String Functions 1706CONCAT 1706FORMAT 1708New date and time Functions 1714Some Recently Added Date and Time Features YouMay Have Missed 1717Date and Time Conversions 1720SQL Server 2012 Enhancements to Windowing Functions 1722The OVER Clause 1722Ranking Functions 1723Calculating Aggregates with the OVER Clause 1729SQL Server 2012 Enhancements to Windowing Functions 1730Ad Hoc Query Paging 1744New Features and Enhancements to Spatial Data 1747The Spatial Data Types 1747Spatial Instance Types 1748Other SQL Server 2012 Enhancements 1749Representing Spatial Data in SQL Server 2012 1750Working with Geometry Data 1751Working with Geography Data 1754Spatial Data Support in SSMS 1756Spatial Data Types: Where to Go from Here? 1759Summary 175945 Transact-SQL Programming Guidelines, Tips, and Tricks 1761General T-SQL Coding Recommendations 1762Provide Explicit Column Lists 1762Qualify Object Names with a Schema Name 1764Avoid SQL Injection Attacks When Using Dynamic SQL 1768Comment Your T-SQL Code 1778General T-SQL Performance Recommendations 1779UNION Versus UNION ALL Performance 1779Use IF EXISTS Instead of SELECT COUNT(*) 1779Avoid Unnecessary ORDER BY or DISTINCT Clauses 1779Temp Tables Versus Table Variables Versus Common Table Expressions 1780Avoid Unnecessary Function Executions 1781Cursors and Performance 1782Variable Assignment in UPDATE Statements 1784In Case You Missed It: Recently Added T-SQL Features 1788TOP Enhancements 1788The OUTPUT Clause 1793MERGE Statement 1798MERGE Statement Best Practices and Guidelines 1804Insert over DML 1805Common Table Expressions 1807Recursive Queries with CTEs 1809Using the Hierarchyid Data Type 1818Creating a Hierarchy 1818Populating the Hierarchy 1819Querying the Hierarchy 1821Modifying the Hierarchy 1825Using Row Numbers for Paging Results 1830De-Duping Data with Ranking Functions 1834PIVOT and UNPIVOT 1836The APPLY Operator 1841CROSS APPLY 1841OUTER APPLY 1843The TABLESAMPLE Clause 1843Variable Assignment in DECLARE Statements 1847Compound Assignment Operators 1848GROUP BY Clause Enhancements 1848GROUPING SETS 1849The grouping_id() Function 1852General T-SQL Tips and Tricks 1854Date Calculations 1854Using CONTEXT_INFO 1857Working with Outer Joins 1859Generating T-SQL Statements with T-SQL 1869Summary 186946 Advanced Stored Procedure Programming and Optimization 1871T-SQL Stored Procedure Coding Guidelines 1871Calling Stored Procedures from Transactions 1874Handling Errors in Stored Procedures 1877Using Cursors in Stored Procedures 1881Using CURSOR Variables in Stored Procedures 1886Nested Stored Procedures 1892Recursive Stored Procedures 1893Using Temporary Tables in Stored Procedures 1897Temporary Table Performance Tips 1899Using the table Data Type 1901Stored Procedure Performance 1903Query Plan Caching 1904The SQL Server Plan Cache 1904Shared Query Plans 1905Automatic Query Plan Recompilation 1905Forcing Recompilation of Query Plans 1908Using Dynamic SQL in Stored Procedures 1912Using sp_executesql 1915Installing and Using .NET CLR Stored Procedures 1918Adding CLR Stored Procedures to a Database 1919T-SQL or CLR Stored Procedures? 1920Using xp_cmdshell 1921Summary 192247 SQL Server and the .NET Framework 1925What's New in SQL Server 2012 and the .NET Framework 1925Getting Comfortable with ADO.NET 4.5 and SQL Server 2012 1926Essential ADO.NET Development Techniques 1926Developing with LINQ to SQL 1933Getting Started with LINQ to SQL 1933Going Deeper 1935Uncovering LINQ to SQL with LINQPad 1938Introducing the Entity Framework 1942Getting Started 1943Data Operations with EF Entities 1946Using WCF Data Services 1951Getting Set Up 1951Essentials 1951Building Your Data Service 1951CRUD Operations 1958Summary 196348 SQLCLR: Developing SQL Server Objects in .NET 1965What's New for SQLCLR in SQL Server 2012 1965Developing Custom Managed Database Objects 1968An Introduction to Custom Managed Database Objects 1968Managed Object Permissions 1969Developing Managed Objects with Visual Studio 2012 1971Developing Managed Stored Procedures 1972Developing Managed User-Defined Functions (UDFs) 1977Developing Managed User-Defined Types (UDTs) 1987Developing Managed User-Defined Aggregates (UDAs) 1995Developing Managed Triggers 1998Using Transactions 2004Using the Related System Catalogs 2006Summary 200749 Using XML in SQL Server 2012 2009What's New in Using XML in SQL Server 2012 2009Understanding XML 2011Relational Data as XML: The FOR XML Modes 2011RAW Mode 2012AUTO Mode 2018EXPLICIT Mode 2022PATH Mode 2026FOR XML and the xml Data Type 2029XML as Relational Data: Using OPENXML 2031Using the xml Data Type 2035Defining and Using xml Columns 2036Using XML Schema Collections 2039The Built-in xml Data Type Methods 2045Indexing and Full-Text Indexing of xml Columns 2063Indexing xml Columns 2064Full-Text Indexing xml Columns 2077Summary 207850 SQL Server Service Broker 2079What's New in Service Broker 2079Understanding Distributed Messaging 2080The Basics of Service Broker 2080Example System Design 2084Understanding Service Broker Constructs 2085Defining Messages and Choosing a Message Type 2085Setting Up Contracts for Communication 2090Creating Queues for Message Storage 2091Defining Services to Send and Receive Messages 2093Planning Conversations Between Services 2094Service Broker Routing and Security 2105Using Certificates for Conversation Encryption 2105A Final Note on the Sample System 2113Troubleshooting SSB Applications with ssbdiagnose.exe 2113AlwaysOn Availability Group Support 2114Related System Catalogs 2115Summary 211751 SQL Server Full-Text Search 2119What's New in SQL Server 2012 Full-Text Search 2120Installing SQL FTS 2121Upgrade Options in SQL Server 2012 2123How SQL Server FTS Works 2124Indexing 2125Searching 2131Implementing SQL Server 2012 Full-Text Catalogs 2131Setting Up a Full-Text Index 2134Using T-SQL Commands to Build Full-Text Indexes 2134Using the Full-Text Indexing Wizard to Build Full-TextIndexes and Catalogs 2152Full-Text Searches 2155CONTAINS and CONTAINSTABLE 2155FREETEXT and FREETEXTTABLE 2162Semantic Search 2164Full-Text Search Maintenance 2166Full-Text Search Performance 2167Full-Text Search Troubleshooting 2167Summary 216952 Working with SQL Azure 2171Setting Up Subscriptions, Servers, and Databases 2171Setting Up Your Windows Azure Subscription 2172Creating a Logical Server 2174Managing Your Server 2176Configuring Your Firewall 2176Using SQL Server Management Studio 2177Using Management Portal 2179Working with Databases 2179Understanding SQL Database Editions 2179Managing Databases Using T-SQL 2183Migrating Data into SQL Database 2184Copying Databases 2186Backing Up and Restoring Databases 2187Using Database Copies for Backup and Restore 2187Using BACPAC Files for Backup and Restore 2187Managing Logins, Users, and Roles 2189Understanding Roles 2190Managing Logins and Users 2190Using SQL Database with ADO.NET 2192Connecting to SQL Database Using a CustomWindows Application 2192Connectivity Limitations 2196Understanding SQL Database Billing 2203Baseline Billing 2203Tracking Your Usage 2204Understanding SQL Database Limitations 2206Unsupported and Partially Supported Functionality 2206References 2209Summary 2209Part IX SQL Server Business Intelligence Features53 SQL Server 2012 Analysis Services 2213What's New in SSAS 2213Understanding SSAS and OLAP 2215Understanding the SSAS Environment Wizards 2217OLAP Versus OLTP 2221An Analytics Design Methodology 2223An Analytics Mini-Methodology 2223An OLAP Requirements Example: CompSales International 2225CompSales International Requirements 2225OLAP Cube Creation 2227Using SQL Server SSDT 2228Creating an OLAP Database 2229Defining Dimensions and Hierarchies 2235Creating the Other Dimensions 2244Creating the Cube 2246Building and Deploying the Cube 2249Populating the Cube with Data 2251Aggregating Data Within the Cube 2252Browsing Data in the Cube 2257Delivering Data to Users 2261ADO MD 2263Query Analysis and Optimization 2264Generating a Relational Database 2266Limitations of a Relational Database 2267Cube Perspectives 2267Data Mining 2268Security and Roles 2278Tabular Models and SSAS 2279OLAP Performance 2280Summary 228154 SQL Server Integration Services 2283What's New with SSIS 2284SSIS Basics 2285SSIS Architecture and Concepts 2290SSIS Tools and Utilities 2293A Data Transformation Requirement 2298Running the SSIS Import and Export Wizard 2300The SSIS Designer 2309The Package Execution Utility 2318The dtexec Utility 2320Running Packages 2321Running Package Examples 2325The dtutil Utility 2326dtutil Examples 2328Change Data Capture and SSIS 2333Using bcp 2333Fundamentals of Exporting and Importing Data 2335File Data Types 2338Format Files 2339Using Views 2348Logged and Nonlogged Operations 2348Batches 2349Parallel Loading 2349Supplying Hints to bcp 2350Summary 235255 SQL Server 2012 Reporting Services 2353What's New in SSRS 2012 2353Discontinued Functionality and Breaking Changes 2354Reporting Services Architecture 2355HTTP Architecture 2358Installing and Configuring SSRS 2358The Installation Sequence 2359SSRS Configuration Using RSCM 2362Developing Reports 2367Tools of the Trade 2367Report Basics 2368Overview of the Report Development Process 2369Data Planning and Preparation 2369Using Shared Data Sources 2370Using Datasets 2370Using Shared Datasets 2371Developing Reports Using SSDT 2371Working with the Tablix 2379Understanding Expressions 2380Report Design Fundamentals 2383Designing Reports Using Report Builder 2394Report Builder and Report Model Security 2409Enabling Report Builder 2410Management and Security 2410Securing Reports 2410Subscriptions 2412Report Execution Options 2414Performance and Monitoring 2416SSRS Trace Log 2416Execution Log 2416Windows Event Log 2417Performance Counters 2417Summary 241756 Master Data Services and Data Quality Services 2419Master Data Services 2419Data Quality Services 2420What's New in MDS and DQS 2420Master Data Management 2421Master Data Services Terms and Concepts 2423Master Data Services 2426Data Quality Services 2446Summary 246057 Parallel Data Warehouse 2463What's New in Parallel Data Warehouse 2463Understanding MPP and PDW 2464MPP Architecture 2465The PDW 2466Data on a PDW 2468PDW and Big Data (Hadoop) 2471xVelocity Columnstore Indexes 2471Columnstore Indexes 2472Summary 2477Part X Bonus Chapters58 Managing Linked Servers 2481What's New in Managing Linked Servers 2482Linked Servers 2483Distributed Queries 2483Distributed Transactions 2484Adding, Dropping, and Configuring Linked Servers 2485sp_addlinkedserver 2485sp_linkedservers 2492sp_dropserver 2494sp_serveroption 2494Mapping Local Logins to Logins on Linked Servers 2495sp_addlinkedsrvlogin 2496sp_droplinkedsrvlogin 2497sp_helplinkedsrvlogin 2498Obtaining General Information About Linked Servers 2499Executing a Stored Procedure via a Linked Server 2500Setting Up Linked Servers Using SQL Server Management Studio 2501Summary 250559 SQL Server Disaster Recovery Planning 2507What's New in SQL Server DisasterRecovery Planning 2508How to Approach Disaster Recovery 2508Disaster Recovery Patterns 2510Recovery Objectives 2514A Data-Centric Approach to Disaster Recovery 2516Microsoft SQL Server Options for Disaster Recovery 2516Data Replication 2517Log Shipping 2519Database Mirroring and Snapshots 2519Change Data Capture 2521AlwaysOn Availability Groups 2521The Overall Disaster Recovery Process 2522The Focus of Disaster Recovery 2523Planning and Executing a Disaster Recovery 2530Have You Detached a Database Recently? 2530Third-Party Disaster Recovery Alternatives 2530Summary 253160 SQL Server Configuration Options 2533What's New in Configuring, Tuning, and OptimizingSQL Server Options 2533SQL Server Instance Architecture 2534Configuration Options 2535Fixing an Incorrect Option Setting 2542Setting Configuration Options with SSMS 2543Obsolete Configuration Options 2543Configuration Options and Performance 2544access check cache bucket count 2544access check cache quota 2544ad hoc distributed queries 2545affinity I/O mask 2545affinity mask 2547Agent XP 2548backup compression default 2549blocked process threshold 2550c2 audit mode 2550clr enabled 2550common criteria compliance enabled 2551contained database authentication 2551cost threshold for parallelism 2552cross db ownership chaining 2553cursor threshold 2553Database Mail XPs 2554default full-text language 2554default language 2556default trace enabled 2558disallow results from triggers 2558EKM provider enabled 2559filestream_access_level 2559fill factor 2559index create memory 2560in-doubt xact resolution 2560lightweight pooling 2561locks 2561max degree of parallelism 2562max server memory and min server memory 2562max text repl size 2564max worker threads 2565media retention 2566min memory per query 2566nested triggers 2567network packet size 2567Ole Automation Procedures 2568optimize for ad hoc workloads 2568PH_timeout 2569priority boost 2569query governor cost limit 2570query wait 2571recovery interval 2571remote access 2572remote admin connections 2572remote login timeout 2573remote proc trans 2573remote query timeout 2573scan for startup procs 2575show advanced options 2575user connections 2575user options 2577XP-Related Configuration Options 2577Summary 2578Index 2579
