abbreviations 11
algebrizer 283
ALTER INDEX statement 89, 90, 92
ALTER statement 61
ALTER TABLE statement 14–15, 27, 253
approximate numeric data type 16
ASCII characters 18
atomic blocks 249
attributes 3
relationship of non-key to key 8–11
auto-commit transactions 203
autocommit transactions 151
AUTO_CREATE_STATISTICS 271
Autogrowth 338
AUTO_UPDATE_STATISTICS 271
AUTO_UPDATE_STATISTICS_ASYNC 271
Azure Access Panel. See Access Panel
DMVs for 358
Extended Events in 358
performance monitoring 356–358
Azure SQL Database Performance Insight 283, 319–324
Azure SQL Database query plans 346
backward compatibility 64
baseline performance metrics 347–362
compared to observed metrics 352–355
batch separator 12
BEGIN ATOMIC_END clause 249
BEGIN TRANSACTION statement 151, 205, 207
binary data 17
bookmark lookups 30
bottlenecks 242, 243, 340, 345
Boyce-Codd normal form 9
brackets 11
bulk data loading
into clustered columnstore index 89–93
bulk update locks 214
business requirements
view structure design based on 53–57
business rules
enforcing with constraints 102–115
Camel Casing 12
CASE statement 247
character data 17
CHECK constraint 65
CHECK constraints 102, 107–111, 121, 160
classification process 325
classifier user-defined functions 329–330
client-side tracing 290–292, 360
CLR. See Common Language Runtime (CLR)
CLR stored procedures 130
clustered columnstore indexes 73–80, 85–88
clustered indexes 25, 46–48, 69, 85
Clustered Index Scan operator 36, 50, 69, 293–294
COALESCE() function 20
column constraints 102
COLUMNPROPERTYEX() function 34
density of 268
dropping 15
limiting to set of values 118–119
NOT NULL 104
column segments
in columnstore indexes 72
columnstore indexes 70–93, 252
adding rows to compressed rowgroups 83
attributes of 85
targeting analytically valuable columns in 83
Columnstore Scan operator 88
COMMIT TRANSACTION 151
COMMIT TRANSACTION statement 205
Common Language Runtime (CLR) 130
common table expressions (CTEs) 116, 248
comparison operators 59
compressed rowgroups 83
COMPRESSION_DELAY setting 83
indexing 34
Compute Scalar operator 30
concurrency 26
concurrent processes
potential problems with 211–216
concurrent queries
connectors. See also receive connectors; See also send connectors
constraints 14, 61, 101, 102–129
CHECK 65, 102, 107–111, 121, 160
column 102
relating to UNIQUE constraint 117–119
PRIMARY KEY 27, 28, 41, 47, 65, 102, 106, 111–112, 120, 125–130
results of DML statements and 123–125
table 102
UNIQUE 102, 105–106, 117–119, 120
covering indexes 41–45, 57, 77
CPU pressure 341
CPU usage 350
CREATE COLUMNSTORE INDEX statement 83
CREATE INDEX statement 29, 39, 43, 48
CREATE SCHEMA statement 12
CTEs. See common table expressions (CTEs)
DAC. See Datacenter Activation Coordination
DAGs. See Database Availability Groups
DAS. See Direct-Attached Storage
data
binary 17
character (string) 17
hiding 53
modifying in views, with multiple tables 62–64
view structure design to select 53–57
data access
real-time 244
memory-optimized tables and 242–255
natively compiled stored procedures and 242, 255–258
database design
based on business requirements 2–4
denormalization 11
writing table create statements 11–15
Database Engine Tuning Advisor 281
database file optimization 336–337
database instances
performance management for 324–346
database file optimization 336–337
query plans 346
storage, IO, and cache troubleshooting 343–346
Database Manipulation Language (DML)
changing definition of 61
dropping 61
relational database schema 2–24
databases. See mailbox databases
logical database model 4
tuning 32
Database Throughput Unit (DTU) 321–322
database workload
managing, in SQL Server 325–331
Resource Governor management queries 330–331
user-defined functions 328–329
workload groups 328
Databse Definition Language (DDL) triggers 159
Data Definition Language (DDL) 123
Data Definition Language (DDL) triggers 169–174
data files 336
data input
coordinating multiple values 110–111
data integrity
data loading
Data Manipulation Language (DML) 123–125
Data Manipulation Language (DML) triggers 159–160, 169. See also triggers
data storage. See also storage architectures; See also storage requirements
datatype
conversion 144
int 107
precedence 144
approximate numeric 16
binary data 17
character (string) data 17
considerations for choosing 18–20
dynamic data masking 20, 21–24
for clustering key 48
not supported in columnstore indexes 71
precise numeric 16
data values 15
data warehouses
clustered columnstore indexes and 73–80
DBCC SHOW_STATISTICS command 266–268
DDL. See Data Definition Language (DDL)
deadlock graphs
Extended Events 240
deadlocks 351
decimals 16
DEFAULT constraints 102, 103–105
DEFAULT keyword 182
degenerate dimensions 75
degree of parallelism (DOP) 328
delayed durability 254
delayed durable transactions 202–203
DELETE statement 63
deltastore structure 72
denormalization 11
design
database
based on business requirements 2–4
writing table create statements 11–15
tables
improving, using normalization 4–11
deterministic calculations 20
deterministic functions 186–188
Developer edition 335
DFS. See Distributed File Share
dimensional formatted data warehouses
using clustered columnstore indexes on 73–80
dimension keys 74
dimensions 74
dirty reads 212
Disk Usage Summary report 353–354
distributed transactions 208–209, 211, 219
DML. See Data Manipulation Language (DML)
DMV. See dynamic management view
DMVs. See dynamic management views (DMVs)
doomed transactions 152
double-quotes 11
DROP command 61
DROP [objectType] IF EXISTS command 61
duplicate key values 46
durable memory-optimized tables 254
dynamic data masking 20, 21–24
dynamic link library (DLL) files 246, 247
dynamic management objects (DMOs) 276–281, 340–343, 347, 348, 355
dynamic management view (DMV) 45
dynamic management views (DMVs) 231–237, 256–259, 276–280, 304–305, 341–342, 345–346, 358
EFS. See Encrypting File System
elastic database client library 332
email() function 23
ENCRYPTION 52
error handling
@@ERROR system function for 148–149
rethrowing errors 150
THROW statement for 157
transaction control logic in 144, 151–158
TRY...CATCH construct for 149–151, 153–154, 157
ERROR_PROCEDURE() function 158
@@ERROR system function 148–149, 154
ESRA. See EdgeSync replication account (ESRA)
estimated query plans 268–270, 314–319
ETDATE() function 20
Evaluation edition 335
EVENTDATA() function 171
EXECUTE AS statement 24
EXECUTE statement 142
explicit transactions 151, 203–204, 205–209
Express edition 334
Extended Events 283–286, 313, 346, 348, 352, 355, 358
actions 360
best practice use cases for 359
compared with SQL Trace 360
events 360
packages 360
sessions 360
Extended Events deadlock graph 240
external resource pools 327
Extract-Transform-Load (ETL) operations 243, 245–246
federated SQL Servers 64
file size 338
filtered rowstore indexes 29, 84
FOREIGN KEY constraints 28–32, 75, 102
limiting column to set of values using 118–119
PRIMARY KEY constraints and 111–112
relating to UNIQUE constraint 117–119
forms 4
FSW. See File Share Witness
FUNCTION query 182
functions
system. See system functions
user-defined. See User-Defined Functions (UDFs)
globally unique identifier (GUID) 18, 48
Globally Unique Identifiers (GUIDs) 128
GO 12
GROUP BY query 182
GUID. See globally unique identifier
hash indexes 252
Hash Match (Aggregate) operator 298–299
Hash Match (Inner Join) operator 299–302
Hash Match join operator 51
Hash Match operator 35, 37, 51, 77
heaps 25
heirarchyId data type 18
high-concurrency databases
hygiene. See message hygiene
IDENTITY property 65, 104, 105, 126–128
implicit transactions 203–205, 216
index create memory 340
adding during coding phase 26
adding during database design phase 26–32
bookmark lookups 30
clustered and non-clustered 73–87
consolidating overlapping 281–282
indexed vs. included columns 41–45
optimizing 28
query plans 30–32, 34–36, 42, 46, 49–52
using dynamic managemet objects to review 276–281
index keys 42
Index Seek (NonClustered) operator 294–295
In-Memory OLTP feature 242
inner data set 301
INSERT INTO clause 105
INSERT statements 248
INSTEAD OF INSERT triggers 168
INSTEAD OF triggers 160, 166–170, 176–179
INSTEAD OF UPDATE triggers 166
int data type 107
internal resource pools 327
Internet of Things 243
interpreted SQL stored procedures 130
interpreted stored procedures 249
IO issues 341
concurrent queries based on 219–228
READ COMMITTED 218, 220–221, 229
READ_COMMITTED_SNAPSHOT 219, 227–228, 230–231
READ UNCOMMITTED 218, 221–222, 229
REPEATABLE READ 218, 222–223, 229
resource and performance impact of 228–230
SERIALIZABLE 218, 223–224, 229
key attributes
relationship to non-key attributes 8–11
Key Lookup (Clustered) operator 294–295
Key Lookup operator 38
artificial 5
natural 6
surrogate 6
linked servers 67
lock compatibility 215
lock escalation events 351
log files 336. See transaction log files
logging tools 355
log sequence numbers (LSNs) 202
lost updates 212
Lync Online. See Skype for Business
maintenance
Management Data Warehouse 352–355
materialized views. See indexed views
max server memory 339
max worker threads 340
measures 75
memory-optimized tables 242–255
analytics workloads 253
natively compile stored procedures and 247–252
performance optimization of 245–255
SQL Server editions supporting 245
memory pressure 342
memory usage monitoring 350
MERGE statement 248
message transport. See transport
Microsoft Azure. See Azure
Microsoft Azure Active Directory. See Azure Active Directory (Azure AD)
min memory per query 340
min server memory 339
names
NAT. See network address translation (NAT)
natively compiled objects 130
natively compiled stored procedures 242
execution statistics for 256–259
unsupported T-SQL constructs for 249
usage scenarios for 255
natural keys 6
Nested Loop operator 78
nested loops 35
NEWSEQUENTIALID() function 128
NICs. See network interface cards (NICs)
non-bulk operations
nonclustered B-tree indexes 252
non-clustered columnstore indexes 73, 78, 80–88
designing in conjuction with clustered 85–88
non-deterministic functions 186–188
non-durable memory-optimized tables 254
non-key attributes
relationship to key attributes 8–11
non-repeatable reads 212
normalization
Boyce-Codd normal form 9
defined 4
rules
covering relationship of non-key attributes to attributes 8–11
NOT NULL columns 104
NULL columns 106
NULL expression 13
objects 3
Office Telemetry. See telemetry
OLTP. See online transaction processing
OLTP databases 28
OLTP tables
using non-clustered columnstore indexes on 81–85
ON clause 14
one-to-one cardinality 10
online transaction processing (OLTP) 1–100
operating system performance metrics 347–352
outer data set 301
Overall Resource Consumption view 314
parallelism 76
parameters
stored procedures 132, 135–139
parameter sniffing 310
partial() function 23
Pascal-casing 12
PERCENT clause 248
performance
data types and 15
performance counters 344–346, 348–351
Performance Monitor 347, 348–351, 355
performance monitoring
baseline performance metrics 347–362
DMOs for 276–280, 340–343, 347, 348, 355
Extended Events for 355, 358, 359–362
Management Data Warehouse 352–355
Performance Monitor for 347, 348–351, 355
vs. logging 355
phantom reads 212
platform-as-a-service. See PaaS
precise numeric data type 16
prefixes 11
PRIMARY KEY constraint 14, 27, 28, 41, 47, 65
PRIMARY KEY constraints 102, 106, 120
FOREIGN KEY constraints and 111–112
quantum 340
queries
dimensional data warehouses 73–80
grouping data in 76
parallelism 76
range 47
Resource Governor management 330–331
slow 351
that return large results 47
Query Menu 31
query optimizer 283
query parsing 283
Query Performance Insight 319–324
Clustered Index Scan operator 293–294
Hash Match (Aggregate) operator 298–299
Hash Match (Inner Join) operator 299–302
Hash Match operator 296
Index Seek (NonClustered) operator 294–295
Key Lookup (Clustered) operator 294–295
Table Scan operator 293
query plans 30–32, 34–36, 35–36, 42, 46, 49–52, 268–270, 283–324
Azure SQL Database 346
Azure SQL Database Performance Insight 319–324
capturing, using extended events and traces 283–292
comparing estimated and actual 314–319
estimated 268
performance impacts of 351
poorly performing operators 291–301
query_post_execution_showplan 284
query_pre_execution_showplan 284
Query Statistics History report 354
query_store_plan_forcing_failed Extended Event 313
range queries 47
READ COMMITTED isolation level 218, 220–221, 229
READ_COMMITTED_SNAPSHOT isolation level 219, 227–228, 230–231
READ UNCOMMITTED isolation level 218, 221–222, 229
real data types 19
real-time analytics
using non-clustered columnstore indexes 81–85
real-time data access 244
Regressed Queries view 314
relational database schema 2–24
designing
based on business requirements 2–4
writing table create statements 11–15
REPEATABLE_READ isolation level 213, 218, 222–223, 229
reporting
resource consuming queries 321–323
resource consumption monitoring 330–331, 359
REVERT statement 24
ROLLBACK TRANSACTION statement 151, 164, 207, 209
row groups 83
in columnstore index 71
Row-Level Security feature 53
rowstore 24
rowstore indexes
uses of 85
rowversion data type 18
SAVE TRANSACTION statement 209
scalar user-defined functions 180–183
SCHEMABINDING 52
SCHEMABINDING clause 249
schema locks 214
schema modification locks 236
schemas
defined 3
designing
based on business requirements 2–4
SCOPE_IDENTITY() function 136
search conditions 35
secondary uniqueness criteria 105–106
security
Row-Level Security feature 53
security audits 359
SELECT clause 183
SELECT INTO clause 248
SELECT statement 317
SELECT statements 52
self-service deployment. See user-driven client deployments
semicolons 12
semi joins 51
SEQUENCE object 65
SEQUENCE objects 128
SERIALIZABLE isolation level 213, 218, 223–224, 229
Server Activity History report 353
servers
linked 67
server-side tracing 286–289, 360
service tiers 335
session state management 244
SET command 134
SET SHOWPLAN_ALL ON statement 315
SET SHOWPLAN_TEXT ON statement 315
SET SHOWPLAN_XML ON statement 315
shared locks 229
SIMPLE recovery model 338
slow queries 351
SMTP. See Single Mail Transfer Protocol (SMTP)
SNAPSHOT isolation level 219, 224–227, 229
snowflake schema 74
spatial data type 18
SPF. See send policy framework (SPF) records
Split-Merge service 333
sp_query_store_flush_db 305
sp_query_store_force_plan 305
sp_query_store_remove_plan 305
sp_query_store_remove_query 305
sp_query_store_reset_exec_stats 305
sp_query_store_unforce_plan 305
sp_trace_create 286
sp_trace_setevent 286
sp_trace_setfilter 286
sp_trace_setstatus 286
SQL Database. See also Azure SQL Database
DMVs for 358
Extended Events in 358
SQL Operating System (SQLOS) Scheduler 340
baseline performance metrics 347–362
Enterprise Edition 67
managing database workload in 325–331
Standard Edition 67
SQL Server 2012
columnstar indexes and 85
SQL Server 2014
columnstar indexes and 85
SQL Server 2016
SQL Server Agent stored procedures 273
SQL Server Integration Services (SSIS) 275
SQL Server Lock Manager 212–213
SQL Server Management Studio 352
SQL Server Profiler 286, 288–289, 290
SQL Server Profiler deadlock graph 239–240
SQL Server Resource Governor 325–331
SQL Trace 283, 286–292, 347, 351, 355, 360
sql_variant 17
Standard edition 334
data distribution and cardinality 267–271
execution. See execution statistics
statistics collection queries 258–259
STATS_DATE system function 272
storage
CLR 130
complex business logic in 243
designing, based on business requirements 131–135
interpreted 249
interpreted SQL 130
natively compiled 130, 242, 247–252, 255–258
parameters 132
server-side tracing 286
SQL Server Agent extended 273
structure of 131
use of 131
string data 17
STRING_SPLIT() function 137–138
surrogate keys 6
syntax
sys.database_connection_stats 358
sys.dm_db_index_physical_stats 279–280
sys.dm_db_index_usage_stats 276–278
sys.dm_db_missing_index_details 280
sys.dm_db_missing_index_groups 280
sys.dm_db_missing_index_group_stats 280
sys.dm_db_resource_stats 358
sys.dm_exec_query_stats 358
sys.dm_exec_session_wait_stats 342
sys.dm_io_virtual_file_stats 343
sys.dm_os_memory_cache_counters 345
sys.dm_os_memory_clerks 345
sys.dm_os_performance_counters 344
sys.dm_os_sys_memory 345
sys.dm_os_waiting_tasks 231, 234–235, 342
sys.dm_os_wait_stats 231, 235–236, 341–342
sys.dm_tran_locks 231, 231–234, 358
sys.event_log 358
sys.fn_trace_getinfo system function 288
sys.master_files 343
sys.query_store_plan 304
sys.query_store_query 304
sys.query_store_query_text 304
sys.query_store_runtime_stats 304
sys.query_store_runtime_stats_interval 305
sys.sp_xtp_control_proc_exec_stats 256
sys.sp_xtp_control_query_exec_stats 257–258
invalid use of, on search arguments 143
system health 359
table constraints 102
ALTER TABLE statement 14–15, 27
dropping 15
creating 11
designing
based on business requirements 2–4
OLTP
using non-clustered columnstore indexes on 81–85
PRIMARY KEY constraints 27
redundancy in 9
relationship of non-key to key attributes 8–11
temporary 244
virtual 62
Table Scan operator 293
table-valued parameters 137–139, 244
table-valued user-defined functions 183–186
table variables 244
temporal extensions 14
temporary tables 244
THROW statement 145–147, 155, 157, 164
timestamp 18
Top Resource Consuming Queries view 308
Tracked Querie view 314
@@TRANCOUNT variable 204, 206, 207, 209
transaction control logic
auto-commit 203
DLM statement results based on 196–203
in high-concurrency databases 211–216
Transact-SQL statements
to add contraints to tables 119–122
triggers 101
complex data integrity and 160–164
designing logic for, based on business requirements 159–169
login 159
running code in response to action 164–165
uses of 160
troubleshooting
TRY...CATCH construct 149–151, 153–154, 157
T-SQL statements 346
UDFs. See User-Defined Functions (UDFs)
uncommitable transactions 152
underscores 12
UNION ALL set operator 64
FOREIGN KEY constraints relating to 117–119
uniqueidentifier data type 18
uniqueness constraints
update locks 213
UPDATE operations
DEFAULT constraints on 104
in columnstore index 72
UPDATE statement 179
UPDATE STATISTICS statement 271, 275
Update Statistics Task dialog box 274
user accounts. See also identities
user-defined functions 50
User-Defined Functions (UDFs) 101, 159, 180–186
user-defined resource pools 327
user identities. See identities
user input
DEFAULT constraints on 103–105
limiting with constraints 107–111
user requirements
version store 227
basic form of 52
designing
based on user or business requirements 53–57
layers of 55
limiting what data can be added to table 61–62
modifying data in, with multiple tables 62–64
options for 52
that reference single table, modifying 58–61
reformatting data in output 54–55
to hide data 53
virtual tables 62
wait types 235
Web edition 334
WIM. See Windows Imaging Format (WIM)
WITH CHECK OPTION clause 52, 61–62
WITH NATIVE_COMPILATION clause 249
WITH TIES in TOP clause 248
XACT_ABORT() function 152, 155–156
XACT_STATE() function 152
XML data type 18
xml_deadlock_report 240
ys.sp_xtp_control_query_exec_stats 256