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
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
|
Ask a Question About this Product More... |
|
