Microsoft SQL Server 2014 Unleashed

Introduction   1Who This Book Is For   2What This Book Covers   2Conventions Used in This Book   4Good Luck!   5 Part I Welcome to Microsoft SQL Server 1 SQL Server 2014 Overview   9SQL Server Components and Features   9The SQL Server Database Engine   10SQL Server 2014 Administration and Management Tools   12Replication   15Merge Replication   16SQL Server AlwaysOn Features   17SQL Server Service Broker   18Full-Text and Semantic Search   18SQL Server Integration Services (SSIS)   20SQL Server Analysis Services (SSAS)   21SQL Server Reporting Services (SSRS)   23Master Data Services   23Data Quality Services   24SQL Server 2014 Editions   24SQL Server 2014 Standard Edition   25SQL Server 2014 Enterprise Edition   26Differences Between the Enterprise and Standard Editions of SQL Server   26Other SQL Server 2014 Editions   28SQL Server Licensing   30Web Edition Licensing   31Developer Edition Licensing   32Express Edition Licensing   32Choosing a Licensing Model   32Mixing Licensing Models   32Licensing SQL Server of High Availability   32Licensing SQL Server in a Virtual Environment   34Summary   352 What's New in SQL Server 2014   37New SQL Server 2014 Features   37Memory-Optimized Tables/In-Memory OLTP   38New Cardinality Estimation Logic   38Delayed Durability for Transactions   38Buffer Pool Extension   38SQL Server Data Tools for Business Intelligence   39SQL Server 2014 Enhancements   39Resource Governor Enhancements   39Security Enhancements   39Backup and Restore Enhancements   40Indexing Enhancements   40Monitoring Enhancements   41SQL Server AlwaysOn and Availability Groups Enhancements   42New Transact-SQL Enhancements   42Deprecated and Discontinued Features   42Summary   45 Part II SQL Server Tools and Utilities 3 SQL Server Management Studio   49What's New in SSMS   50The Integrated Environment   50Window Management   50Integrated Help   53Administration Tools   56Registered Servers   56Object Explorer   58Activity Monitor   60Log File Viewer   62SQL Server Utility   64Development Tools   69The Query Editor   69Managing Projects in SSMS   77Integrating SSMS with Source Control   78Using SSMS Templates   80Using SSMS Snippets   84T-SQL Debugging   85Multiserver Queries   86Summary   874 SQL Server Command-Line Utilities   89What's New in SQL Server Command-Line Utilities   90The sqlcmd Command-Line Utility   91Executing the sqlcmd Utility   93Using Scripting Variables with sqlcmd   95The dta Command-Line Utility   96The tablediff Command-Line Utility   99The bcp Command-Line Utility   102The sqldiag Command-Line Utility   103The sqlservr Command-Line Utility   105The sqlLocalDB Command-Line Utility   106Summary   1085 SQL Server Profiler   111What's New with SQL Server Profiler   111SQL Server Profiler Architecture   112Creating Traces   113Events   115Data Columns   117Filters   120Executing Traces and Working with Trace Output   122Saving and Exporting Traces   123Saving Trace Output to a File   123Saving Trace Output to a Table   124Saving the Profiler GUI Output   124Importing Trace Files   125Importing a Trace File into a Trace Table   125Analyzing Trace Output with the Database Engine Tuning Advisor   128Replaying Trace Data   128Defining Server-Side Traces   131Monitoring Running Traces   141Stopping Server-Side Traces   143Profiler Usage Scenarios   145Analyzing Slow Stored Procedures or Queries   145Deadlocks   146Identifying Ad Hoc Queries   148Identifying Performance Bottlenecks   148Monitoring Auto-Update Statistics   150Monitoring Application Progress   151Summary   1536 SQL Distributed Replay   155What's New for Distributed Replay   155Overview of Distributed Replay   155Distributed Replay Components   156Distributed Replay Administrative Tool   157Distributed Replay Controller   157Distributed Replay Clients   158Target Server   158Configuring Distributed Replay   158Controller Configuration File   159Client Configuration File   159Preprocess Configuration File   160Replay Configuration File   161Replay the Trace Data   163Configure Permissions and Security   163Capture the Workload   165Preprocess the Trace File   166Apply the Workload   167Summary   169 Part III SQL Server Administration 7 SQL Server System and Database Administration   173What's New in SQL Server System and Database Administration   173System Administrator Responsibilities   174System Databases   174The master Database   175The resource Database   176The model Database   176The msdb Database   176The distribution Database   176The tempdb Database   177Maintaining System Databases   177System Tables   178System Views   179Compatibility Views   180Catalog Views   182Information Schema Views   184Dynamic Management Views   186System Stored Procedures   189Useful System Stored Procedures   189Summary   1918 Installing SQL Server 2014   193What's New in Installing SQL Server 2014   193Installation Requirements   193Hardware Requirements   194Software Requirements   195Installation Walkthrough   198Install Screens, Step-by-Step   198Installing SQL Server Documentation   217Installing SQL Server Using a Configuration File   219Running an Automated or Manual Install   224Installing SQL Server Using Sysprep   226Preparing a SQL Server Sysprep Image   226Completing a SQL Server Sysprep Image   229Modifying a SQL Server Sysprep Image   231Common Uses of SQL Server Sysprep Images   232Installing Service Packs and Cumulative Updates   233Applying a Service Pack or Cumulative Update During a New Installation   233Summary   2369 Upgrading to SQL Server 2014   237What's New in Upgrading SQL Server   237The SQL Server 2014 Upgrade Matrix   237Identifying Products and Features to be Upgraded   240Using the SQL Server Upgrade Advisor (UA)   241Getting Started with the UA   241The Analysis Wizard   243The Report Viewer   249Destination: SQL Server 2014   250Side-by-Side Upgrades   251Upgrading In-Place   259Upgrading the Database Engine   260Installing Product Updates (Slipstreaming) During Upgrades   263Upgrading Using a Configuration File   264Upgrading from Pre-SQL Server 2005 Versions   266Upgrading Other SQL Server Components   266Upgrading Analysis Services   266Upgrading SQL Server Analysis Services   266Upgrading Reporting Services   266Upgrading SSIS Packages   269Migrating DTS Packages   271Summary   27110 Client Installation and Configuration   273What's New in Client Installation and Configuration   273Client/Server Networking Considerations   274Server Network Protocols   275The Server Endpoint Layer   277The Role of SQL Browser   280Client Installation   281Installing the Client Tools   281Installing SNAC   282Client Configuration   284Client Configuration Using SSCM   284Connection Encryption   287Client Data Access Technologies   289Provider Choices   290Connecting Using the Various Providers and Drivers   291General Networking Considerations and Troubleshooting   296Summary   29911 Database Backup and Restore   301What's New in Database Backup and Restore   301Developing a Backup and Restore Plan   302Types of Backups   303Full Database Backups   304Differential Database Backups   304Partial Backups   305Differential Partial Backups   305File and Filegroup Backups   305Copy-Only Backups   306Transaction Log Backups   306Recovery Models   306Full Recovery   307Bulk-Logged Recovery   308Simple Recovery   309Backup Devices   310Disk Devices   310Tape Devices   310Network Shares   311Media Sets and Families   311Creating Backup Devices   311Backing Up a Database   312Creating Database Backups with SSMS   312Creating Database Backups with T-SQL   315Backing Up the Transaction Log   318Creating Transaction Log Backups with SSMS   318Creating Transaction Log Backups with T-SQL   319Backup Scenarios   320Full Database Backups Only   320Full Database Backups with Transaction Log Backups   321Differential Backups   322Partial Backups   323File/Filegroup Backups   325Mirrored Backups   326Copy-Only Backups   326Compressed Backups   327Encrypted Backups   328System Database Backups   329Restoring Databases and Transaction Logs   330Restores with T-SQL   330Restoring by Using SSMS   334Restore Information   339Restore Scenarios   342Restoring to a Different Database   342Restoring a Snapshot   344Restoring a Transaction Log   344Restoring to the Point of Failure   345Restoring to a Point in Time   347Online Restores   349Restoring the System Databases   349Additional Backup Considerations   351Frequency of Backups   352Using a Standby Server   352Snapshot Backups   353Considerations for Very Large Databases   354Maintenance Plans   354Summary   35512 Database Mail   357What's New in Database Mail   357Setting Up Database Mail   358Creating Mail Profiles and Accounts   359Using T-SQL to Update and Delete Mail Objects   362Setting System-Wide Mail Settings   363Testing Your Setup   364Sending and Receiving with Database Mail   364The Service Broker Architecture   364Sending Email   365Receiving Email   371Using SQL Server Agent Mail   371Job Mail Notifications   371Creating an Operator   371Enabling SQL Agent Mail   371Creating the Job   372Testing the Job-Completion Notification   373Alert Mail Notifications   373Creating an Alert   373Testing the Alert Notification   374Related Views and Procedures   375Viewing the Mail Configuration Objects   375Viewing Mail Message Data   376Summary   37713 SQL Server Agent   379What's New in Scheduling and Notification   380Configuring the SQL Server Agent   380Configuring SQL Server Agent Properties   380Configuring the SQL Server Agent Startup Account   382Configuring Email Notification   384SQL Server Agent Proxy Account   385Viewing the SQL Server Agent Error Log   387SQL Server Agent Security   388Managing Operators   389Managing Jobs   391Defining Job Properties   391Defining Job Steps   392Defining Multiple Job Steps   394Defining Job Schedules   395Defining Job Notifications   397Viewing Job History   398Managing Alerts   399Defining Alert Properties   399Defining Alert Responses   402Scripting Jobs and Alerts   404Multiserver Job Management   405Creating a Master Server   406Enlisting Target Servers   407Creating Multiserver Jobs   407Event Forwarding   407Summary   40814 SQL Server Policy-Based Management   409What's New in Policy-Based Management   409Introduction to Policy-Based Management   410Policy-Based Management Concepts   411Facets   411Conditions   414Policies   415Categories   415Targets   415Execution Modes   415Central Management Servers   416Implementing Policy-Based Management   418Creating a Condition Based on a Facet   418Creating a Policy   420Creating a Category   422Evaluating Policies   424Importing and Exporting Policies   425Sample Templates and Real-World Examples   426Sample Policy Templates   426Evaluating Recovery Models   427Ensuring Object Naming Conventions   427Checking Best Practices Compliance   427Policy-Based Management Best Practices   427Summary   42815 Security and User Administration   429What's New in Security and User Administration   429An Overview of SQL Server Security   430Authentication Methods   433Windows Authentication Mode   433Mixed Authentication Mode   433Setting the Authentication Mode   433Managing Principals   434Logins   434SQL Server Security: Users   437The dbo User   438The guest User   439The INFORMATION_SCHEMA User   439The sys User   439User/Schema Separation   440Roles   441Fixed Server Roles   442Fixed Database Roles   443The public Role   445User-Defined Database Roles   446User-Defined Server Roles   448Application Roles   448Managing Securables   449Managing Permissions   450Managing SQL Server Logins   452Using SSMS to Manage Logins   452Using T-SQL to Manage Logins   456Managing SQL Server Users   457Using SSMS to Manage Users   458Using T-SQL to Manage Users   460Managing Database Roles   461Using SSMS to Manage Database Roles   461Using T-SQL to Manage Database Roles   462Managing Server Roles   462Using SSMS to Manage Server Roles   463Using T-SQL to Manage Server Roles   463Managing SQL Server Permissions   464Using SSMS to Manage Permissions   464Using SSMS to Manage Permissions at the Server Level   465Using SSMS to Manage Permissions at the Database Level   467Using SSMS to Manage Permissions at the Object Level   470Using T-SQL to Manage Permissions   472The Execution Context   473Explicit Context Switching   473Implicit Context Switching   474Summary   47516 Data Encryption   477What's New in Data Encryption   478An Overview of Data Encryption   478SQL Server Key Management   480Extensible Key Management   482Column-Level Encryption   483Encrypting Columns Using a Passphrase   484Encrypting Columns Using a Certificate   486Transparent Data Encryption   490Implementing Transparent Data Encryption   491Managing TDE in SSMS   493Backing Up TDE Certificates and Keys   495The Limitations of TDE   496Column-Level Encryption Versus Transparent Data Encryption   496Summary   49817 Managing Linked Servers   499What's New in Managing Linked Servers   500Linked Servers   500Distributed Queries   501Distributed Transactions   502Adding, Dropping, and Configuring Linked Servers   503sp_addlinkedserver   503sp_linkedservers   510sp_dropserver   512sp_serveroption   512Mapping Local Logins to Logins on Linked Servers   513sp_addlinkedsrvlogin   514sp_droplinkedsrvlogin   515sp_helplinkedsrvlogin   516Obtaining General Information About Linked Servers   517Executing a Stored Procedure via a Linked Server   518Setting Up Linked Servers Using SQL Server Management Studio   519Summary   52318 SQL Server Configuration Options   525What's New in Configuring, Tuning, and Optimizing SQL Server Options   525SQL Server Instance Architecture   526Configuration Options   527Fixing an Incorrect Option Setting   535Setting Configuration Options with SSMS   535Obsolete Configuration Options   535Configuration Options and Performance   536access check cache bucket count   536access check cache quota   536ad hoc distributed queries   537affinity I/O mask   537affinity mask   539Agent XP   540backup checksum default   541backup compression default   541blocked process threshold   542c2 audit mode   542clr enabled   543common criteria compliance enabled   543contained database authentication   543cost threshold for parallelism   544cross db ownership chaining   545cursor threshold   545Database Mail XPs   546default full-text language   546default language   548default trace enabled   550disallow results from triggers   551EKM provider enabled   551filestream_access_level   551fill factor   552index create memory   552in-doubt xact resolution   553lightweight pooling   553locks   554max degree of parallelism   554max server memory and min server memory   554max text repl size   556max worker threads   557media retention   558min memory per query   558nested triggers   559network packet size   559Ole Automation Procedures   560optimize for ad hoc workloads   560PH_timeout   561priority boost   561query governor cost limit   562query wait   562recovery interval   563remote access   564remote admin connections   564remote login timeout   564remote proc trans   565remote query timeout   565scan for startup procs   565show advanced options   566user connections   566user options   567XP-Related Configuration Options   568Summary   56919 Working with and Deploying to Azure SQL Database   571Setting Up Subscriptions, Servers, and Databases   571Setting Up Your Windows Azure Subscription   572Creating a Logical Database Server   574Managing Your Server   576Configuring Your Firewall   577Using SQL Server Management Studio   578Using Management Portal   579Working with Databases   580Understanding SQL Database Service Tiers   580Managing Databases Using T-SQL   584Migrating Data into SQL Database   586Copying Databases   587Exporting Databases   588Backing Up and Restoring Databases   590Using SQL Database Backup, Replication, and Recovery   590Using Database Copies for Backup and Restore   592Using BACPAC Files for Backup and Restore   593Managing Logins, Users, and Roles   595Understanding Roles   595Managing Logins and Users   596Considerations for SQL Database Client Applications   598Connectivity Limitations   598Connection String Differences   599Understanding SQL Database Billing   599Baseline Billing   599Tracking Your Usage   601Understanding SQL Database Limitations   603Unsupported and Partially Supported Functionality   603References   606Summary   606 Part IV Database Administration 20 Creating and Managing Databases   609What's New in Creating and Managing Databases   610Data Storage in SQL Server   610Database Files   611Primary Files   612Secondary Files   612Using Filegroups   613Using Partitions   616Transaction Log Files   616Creating Databases   617Using SSMS to Create a Database   618Using T-SQL to Create Databases   621Setting Database Options   622The Database Options   623Using T-SQL to Set Database Options   625Retrieving Option Information   626Managing Databases   629Managing File Growth   629Expanding Databases   630Shrinking Databases   631Moving Databases   636Restoring a Database to a New Location   636Using ALTER DATABASE   636Detaching and Attaching Databases   637Contained Databases   639Creating a Contained Database   640Connecting to a Contained Database   642Summary   64321 Creating and Managing Tables   645What's New in SQL Server 2014   645Creating Tables   646Using Object Explorer to Create Tables   646Using Database Diagrams to Create Tables   647Using T-SQL to Create Tables   648Defining Columns   650Data Types   651Column Properties   657Column Sets   663Working with Sparse Columns   664Sparse Columns: Good or Bad?   667Defining Sparse Columns in SSMS   667Defining Table Location   668Defining Table Constraints   670Modifying Tables   672Using T-SQL to Modify Tables   672Using Object Explorer and the Table Designer to Modify Tables   675Using Database Diagrams to Modify Tables   678Dropping Tables   680Using Partitioned Tables   681Creating a Partition Function   682Creating a Partition Scheme   684Creating a Partitioned Table   686Adding and Dropping Table Partitions   689Switching Table Partitions   693Using FILESTREAM Storage   697Enabling FILESTREAM Storage   698Setting Up a Database for FILESTREAM Storage   701Using FILESTREAM Storage for Data Columns   702Using FileTables   705FileTable Prerequisites   705Creating FileTables   707Copying Files to the FileTable   707Creating Temporary Tables   709Summary   71022 Creating and Managing Indexes   711What's New in Creating and Managing Indexes   711Types of Indexes   712Clustered Indexes   712Nonclustered Indexes   714Creating Indexes   716Creating Indexes with T-SQL   716Creating Indexes with SSMS   720Managing Indexes   722Managing Indexes with T-SQL   723Managing Indexes with SSMS   726Dropping Indexes   727Online Indexing Operations   727Indexes on Views   729Summary   73023 Implementing Data Integrity   731What's New in Data Integrity   731Types of Data Integrity   732Domain Integrity   732Entity Integrity   732Referential Integrity   732Enforcing Data Integrity   732Implementing Declarative Data Integrity   732Implementing Procedural Data Integrity   733Using Constraints   733The PRIMARY KEY Constraint   733The UNIQUE Constraint   735The FOREIGN KEY Referential Integrity Constraint   736The CHECK Constraint   740Creating Constraints   742Managing Constraints   747Rules   750Defaults   751Declarative Defaults   751Bound Defaults   753When a Default Is Applied   754Restrictions on Defaults   755Summary   75624 Creating and Managing Views   757What's New in Creating and Managing Views   757Definition of Views   757Using Views   758Simplifying Data Manipulation   759Focusing on Specific Data   760Abstracting Data   761Controlling Access to Data   762Creating Views   764Creating Views Using T-SQL   765ENCRYPTION   767Creating Views Using the View Designer   769Managing Views   772Altering Views with T-SQL   772Dropping Views with T-SQL   773Managing Views with SSMS   773Data Modifications and Views   773Partitioned Views   774Modifying Data Through a Partitioned View   778Distributed Partitioned Views   779Indexed Views   780Creating Indexed Views   781Indexed Views and Performance   783To Expand or Not to Expand   786Summary   78725 Creating and Managing Stored Procedures   789What's New in Creating and Managing Stored Procedures   789Advantages of Stored Procedures   789Creating Stored Procedures   791Creating Procedures in SSMS   792Executing Stored Procedures   799Executing Procedures in SSMS   800Execution Context and the EXECUTE AS Clause   802Using the WITH RESULT SETS Clause   804Deferred Name Resolution   807Identifying the Objects Referenced Within Stored Procedures   809Viewing Stored Procedures   811Modifying Stored Procedures   814Viewing and Modifying Stored Procedures with SSMS   815Using Input Parameters   816Setting Default Values for Parameters   817Passing Object Names as Parameters   820Using Wildcards in Parameters   822Using Table-Valued Parameters   823Using Output Parameters   825Returning Procedure Status   826Debugging Stored Procedures Using SQL Server Management Studio   827Startup Procedures   830Natively Compiled Stored Procedures   834T-SQL Stored Procedure Coding Guidelines   838Summary   83926 Creating and Managing User-Defined Functions  841Why Use User-Defined Functions?   841Types of User-Defined Functions   844Scalar Functions   844Table-Valued Functions   847Creating and Managing User-Defined Functions   849Creating User-Defined Functions   849Viewing and Modifying User-Defined Functions   860Managing User-Defined Function Permissions   868Rewriting Stored Procedures as Functions   869Summary   87127 Creating and Managing Triggers   873What's New in Creating and Managing Triggers   874Using DML Triggers   874Creating DML Triggers   875Using AFTER Triggers   877Using inserted and deleted Tables   881INSTEAD OF Triggers   885Using DDL Triggers   893Creating DDL Triggers   897Managing DDL Triggers   901Using Nested Triggers   903Using Recursive Triggers   903Summary   90528 Transaction Management and the Transaction Log   907What's New in Transaction Management   907What Is a Transaction?   907How SQL Server Manages Transactions   908Defining Transactions   909AutoCommit Transactions   909Explicit User-Defined Transactions   910Implicit Transactions   916Implicit Transactions Versus Explicit Transactions   918Transactions and T-SQL Batches   919Transactions and Stored Procedures   921Transactions and Triggers   926Triggers and Transaction Nesting   927Triggers and Multistatement Transactions   930Using Savepoints in Triggers   931Transactions and Locking   933READ_COMMITTED_SNAPSHOT Isolation   934Coding Effective Transactions   934Transaction Logging and the Recovery Process   935The Checkpoint Process   939Automatic Checkpoints   941Indirect Checkpoints   942Manual Checkpoints   944The Recovery Process   945Managing the Transaction Log   947Long-Running Transactions   952Distributed Transactions   954Summary   95529 Database Snapshots   957What's New with Database Snapshots   958What Are Database Snapshots?   958Limitations and Restrictions of Database Snapshots   962Copy-on-Write Technology   964When to Use Database Snapshots   965Reverting to a Snapshot for Recovery Purposes   965Safeguarding a Database Prior to Making Mass Changes   966Providing a Testing (or Quality Assurance) Starting Point (Baseline)   967Providing a Point-in-Time Reporting Database   967Providing a Highly Available and Offloaded Reporting Database from a Database Mirror   968Setup and Breakdown of a Database Snapshot   970Creating a Database Snapshot   970Removing a Database Snapshot   974Reverting to a Database Snapshot for Recovery   975Reverting a Source Database from a Database Snapshot   975Database Snapshots Maintenance and Security Considerations   977Security for Database Snapshots   977Snapshot Sparse File Size Management   977Number of Database Snapshots per Source Database   977Summary   97830 Database Maintenance   979What's New in Database Maintenance   980The Maintenance Plan Wizard   980Backing Up Databases   983Checking Database Integrity   987Shrinking Databases   988Maintaining Indexes and Statistics   990Scheduling a Maintenance Plan   993Managing Maintenance Plans Without the Wizard   997Executing a Maintenance Plan   1001Maintenance Without a Maintenance Plan   1002Database Maintenance Policies   1003Summary   1003 Part V SQL Server Performance and Optimization 31 Understanding SQL Server Data Structures   1007What's New for Data Structures   1007Understanding Data Structures   1008Database Files and Filegroups   1008Primary Data File   1010Secondary Data Files   1010The Log File   1011File Management   1011Using Filegroups   1012FILESTREAM Filegroups   1015Database Pages   1017Page Types   1017Data Pages   1018Row-Overflow Pages   1024LOB Data Pages   1025Index Pages   1028Space Allocation Structures   1029Extents   1029Global and Shared Global Allocation Map Pages   1030Page Free Space Pages   1031Index Allocation Map Pages   1031Differential Changed Map Pages   1032Bulk Changed Map Pages   1032Data Compression   1033Row-Level Compression   1033Page-Level Compression   1035The CI Record   1038Implementing Page Compression   1038Evaluating Page Compression   1039Managing Data Compression with SSMS   1042Understanding Table Structures   1043Heap Tables   1045Clustered Tables   1047Understanding Index Structures   1048Clustered Indexes   1049Nonclustered Indexes   1052Columnstore Indexes   1057Data Modification and Performance   1062Inserting Data   1062Deleting Rows   1065Updating Rows   1066Summary   106832 Indexes and Performance   1069What's New for Indexes and Performance   1069Index Utilization   1070Index Selection   1072Evaluating Index Usefulness   1073Index Statistics   1076The Statistics Histogram   1078How the Statistics Histogram Is Used   1080Index Densities   1081Estimating Rows Using Index Statistics   1082Generating and Maintaining Index and Column Statistics   1085SQL Server Index Maintenance   1093Setting the Fill Factor   1103Reapplying the Fill Factor   1105Disabling Indexes   1106Managing Indexes with SSMS   1107Index Design Guidelines   1108Clustered Index Indications   1109Nonclustered Index Indications   1111Index Covering   1112Included Columns   1114Wide Indexes Versus Multiple Indexes   1115Indexed Views   1116Indexes on Computed Columns   1117Filtered Indexes and Statistics   1119Creating and Using Filtered Indexes   1120Creating and Using Filtered Statistics   1122Choosing Indexes: Query versus Update Performance   1124Identifying Missing Indexes   1125The Database Engine Tuning Advisor   1125Missing Index Dynamic Management Objects   1126Missing Index Feature Versus Database Engine Tuning Advisor   1128Identifying Unused Indexes   1129Summary   113133 In-Memory Optimization and the Buffer Pool Extension   1133Overview of In-Memory OLTP   1134In-Memory OLTP Concepts and Terminology   1136In-Memory Optimization Requirements   1137Limitations of In-Memory OLTP   1137Using In-Memory OLTP   1138Enabling a Database for In-Memory OLTP   1138Creating Memory-Optimized Tables   1140Memory-Optimized Tables Row Structure   1142Indexes on Memory-Optimized Tables   1143Garbage Collection   1151Maintaining Statistics on Memory-Optimized Tables   1153Memory-Optimized Index Design Guidelines   1154Using Memory-Optimized Tables   1156Interpreted T-SQL Support for In-Memory OLTP   1156Native Compilation   1157Natively Compiled Stored Procedures   1159Memory-Optimized Table Variables   1162Transactions and Memory-Optimized Tables   1162Monitoring Transactions on Memory-Optimized Tables   1170Logging, Checkpoint, and Recovery for In-Memory OLTP   1170Transaction Logging   1171Checkpoint   1171Recovery   1174Managing Memory for In-Memory OLTP   1175Monitoring Memory Usage   1176Managing Memory with the Resource Governor   1177Backup and Recovery of Memory-Optimized Databases   1178Migrating to In-Memory OLTP   1179Using the AMR Tool   1180Using the Table Memory Optimization Advisor to Migrate Disk-Based Tables   1181Dynamic Management Views for In-Memory OLTP   1183The Buffer Pool Extension   1185Summary   118634 Understanding Query Optimization   1187What's New in Query Optimization   1188What Is the Query Optimizer?   1188Query Compilation and Optimization   1189Compiling DML Statements   1189Optimization Steps   1190Query Analysis   1191Identifying Search Arguments   1191Identifying OR Clauses   1191Identifying Join Clauses   1192Row Estimation and Index Selection   1193Evaluating SARG and Join Selectivity   1193Estimating Access Path Cost   1199Using Multiple Indexes   1206Optimizing with Indexed Views   1213Optimizing with Filtered Indexes   1216Evaluating Cardinality Estimates   1218Join Selection   1219Join Processing Strategies   1219Determining the Optimal Join Order   1224Subquery Processing   1226Execution Plan Selection   1228Query Plan Caching   1231Query Plan Reuse   1231Query Plan Aging   1234Recompiling Query Plans   1234Monitoring the Plan Cache   1235Other Query Processing Strategies   1243Predicate Transitivity   1244GROUP BY Optimization   1244Queries with DISTINCT   1245Queries with UNION   1245