Overview

This is an in-depth informational book about SQL Server, placing emphasis on the more complex aspects of the product, including using the new tools and features, administering SQL Server, analyzing and optimizing queries, implementing data warehouses, ensuring high availability, and tuning SQL Server performance. Each chapter begins with a brief summary of the major changes or new features or capabilities of SQL Server related to that topic, then provides a behind-the-scenes look into SQL Server, showing what goes on behind the various wizards and GUI-based tools. Students will learn what the underlying SQL commands are, to allow them to fully unlock the power and capabilities of SQL Server.

Full Product Details

Author:   Ray Rankins ,  Paul Bertucci ,  Chris Gallelli ,  Alex Silverstein
Publisher:   Pearson Education (US)
Imprint:   Sams Publishing
Dimensions:   Width: 18.00cm , Height: 6.00cm , Length: 23.00cm
Weight:   2.456kg
ISBN:  

9780672337291

ISBN 10:   0672337290
Pages:   2000
Publication Date:   25 June 2015
Audience:   Professional and scholarly ,  Professional & Vocational
Format:   Paperback
Publisher's Status:   Active
Availability:   In Print  
This item will be ordered in for you from one of our suppliers. Upon receipt, we will promptly dispatch it out to you. For in store availability, please contact us.

Table of Contents

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

Reviews

Author Information

Ray Rankins is owner and president of Gotham Consulting Services, Inc. (http://www.gothamconsulting.com), near Saratoga Springs, New York. Ray has been working with Sybase and Microsoft SQL Server for more than 27 years and has experience in database administration, database design, project management, application development, consulting, courseware development, and training. He has worked in a variety of industries, including financial, manufacturing, health care, retail, insurance, communications, public utilities, and state and federal government. His expertise is in database performance and tuning, query analysis, advanced SQL programming and stored procedure development, database design, data architecture, and database application design and development, with recent specialization in Sybase to SQL Server migrations. Ray’s presentations on these topics at user group conferences have been very well received. Ray is coauthor of Microsoft SQL Server 2012 Unleashed, Microsoft SQL Server 2008 R2 Unleashed, Microsoft SQL Server 2005 Unleashed, Microsoft SQL Server 2000 Unleashed, Microsoft SQL Server 6.5 Unleashed, Sybase SQL Server 11 Unleashed, and Sybase SQL Server 11 DBA Survival Guide, all published by Sams Publishing. As an instructor, Ray brings his real-world experience into the classroom, teaching courses on SQL, advanced SQL programming and optimization, database design, database administration, and database performance and tuning. Ray can be reached at rrankins@gothamconsulting.com.   Paul Bertucci is the founder of Data by Design, LLC (www.dataxdesign.com), a global database consulting firm with offices in the United States and Paris, France. He recently spent 6 years as the Chief Architect and Director of the global Shared Services team for Autodesk, Inc. running BI/DW/ODS, Big Data, Identity Management, SOA, Integration (EAI & ETL), MDM, Collaboration/Social, SaaS application platforms, and Enterprise Architecture teams. Prior to Autodesk, he was the Chief Data Architect at Symantec for 4 years. He is also co-founder and CTO for Diginome, Inc. (www.diginome.com), a data provenance/integrity software company. Paul has more than 30 years of experience with database design, data architecture, big data, data replication, performance and tuning, master data management (MDM), data provenance/DataDNA, distributed data systems, data integration, high-availability, enterprise architecture, identity management, SOA, SaaS, and systems integration for numerous Fortune 500 companies, including Intel, Coca-Cola, Apple, Toshiba, Lockheed, Wells Fargo, Safeway, Sony, Charles Schwab, Cisco Systems, Sybase, Symantec, Veritas, and Honda, to name a few. He has authored numerous database articles, data standards, and high-profile database courses, such as Sybase’s “Performance and Tuning” and “Physical Database Design” courses. Other Sams Publishing books that he has authored include the highly popular Microsoft SQL Server 2000 Unleashed, Teach Yourself ADO.NET in 24 Hours, Microsoft SQL Server High Availability, Microsoft SQL Server 2005 Unleashed, Microsoft SQL Server 2008 R2 Unleashed, and Microsoft SQL Server 2012 Unleashed. Mr. Bertucci is a frequent speaker at industry conferences such as Informatica World, Oracle World, and the MDM Summit, and at Microsoft-oriented conferences such as SQL Saturday’s, Silicon Valley Code-Camp, PASS conferences, Tech Ed’s, and SQL Server User Groups. He has deployed numerous systems with Microsoft SQL Server, Sybase, DB2, Postgres, MySQL, NoSQL, Paraccel, Hadoop and Oracle database eng

Tab Content 6

Author Website:  

Customer Reviews

Recent Reviews

No review item found!

Add your own review!

Countries Available

All regions