DAC (dedicated administrator connection), 38
damaged databases, salvaging, 206–209
Data Access option, 463
Data Conversion transforms, 376
Data File(s) Size (KB) counter, 232
data flow for SIS packages, 365–368, 365, 373, 375–376
data management, 398–399, 400
cleansing, 426
data assessment, 400–401
documenting actions, 400
exam essentials, 446
integrity. See integrity
review questions, 447–453
summary, 445–446
validation
queries for. See joins
sets for. See sets
verification, 402–404
Data Mining Model Training destination, 378
Data Mining Query task, 374
Data Mining Query transforms, 376
data-mining solution tools, 403
DATA_PURITY option, 302
data recovery. See recovery
data stewardship, 433, 477
Data Transformation Services (DTS), 360–362
data types, 439–440
alias, 443–444
CLR UDTs, 444–445
Transact-SQL, 440–443
data viewers, 383–385, 384–385
database consistency checks, 296–297
DBCC CHECKALLOC, 297–299
DBCC CHECKCATALOG, 299–300
DBCC CHECKCONSTRAINTS, 300
DBCC CHECKDB, 301–304
DBCC CHECKFILEGROUP, 305–306
DBCC CHECKIDENT, 304–305
DBCC CHECKTABLE, 306–307
DBCC CLEANTABLE, 307–308
DBCC DBREINDEX, 309
DBCC DROPCLEANBUFFERS, 309–310
DBCC FREEPROCCACHE, 310
DBCC INDEXDEFRAG, 310–312
DBCC SHOWCONTIG, 312–314
DBCC SHRINKDATABASE, 314–315
DBCC SHRINKFILE, 315–316
DBCC UPDATEUSAGE, 316–317
running, 318–320
VLDB environment in, 317–318
Database Consistency History reports, 49
database console commands (DBCCs). See database consistency checks
database engine performance object counters
baselines, 231–234
thresholds, 237–238
Database Engine Tuning Advisor (DTA), 52
database level DDL triggers, 542–543
database level DMVs, 52–56
database level replication, 512
Database Mail, 532
Database Maintenance Plan Wizard, 324–335, 326–335
database master keys, 573
DATABASE resource, locks for, 127
database scope for DDL triggers, 270
database snapshots, 189
Database Tuning Advisor (DTA), 53, 71–76, 73–76
databases
failures
backups. See backups
login configuration, 204–205
lost data, 205
recovery models, 180–182
restoring, 194–204, 203
salvaging, 206–209
scripts, 205–206, 206
lock options, 138
maintaining. See maintaining databases
mirroring, 172
in performance, 4
query performance options, 71
roles, 584–585
security. See security
shrinking, 314–315
SSRS, 348–350, 348–349
DataReader destination, 378
@datasrc argument, 459
date and time data types, 441
datetime data type, 441
db_ roles, 584–585
DBCC CHECKALLOC command, 297–299
DBCC CHECKCATALOG command, 299–300
DBCC CHECKCONSTRAINTS command, 300
DBCC CHECKDB command, 50, 207–208, 301–304
DBCC CHECKFILEGROUP command, 207, 305–306
DBCC CHECKIDENT command, 304–305
DBCC CHECKTABLE command, 207, 306–307
DBCC CLEANTABLE command, 51, 307–308
DBCC DBREINDEX command, 107, 309
DBCC DROPCLEANBUFFERS command, 51, 309–310
DBCC FREEPROCCACHE command, 51, 310
DBCC FREESYSTEMCACHE command, 51
DBCC HELP command, 51
DBCC INDEXDEFRAG command, 51, 106, 310–312
DBCC LOGINFO command, 51
DBCC MEMORYSTATUS command, 44
DBCC OPENTRAN command, 51
DBCC PROCCACHE command, 51
DBCC SHOW_STATISTICS command, 51, 112
DBCC SHOWCONTIG command, 51, 52, 312–314
DBCC SHRINKDATABASE command, 314–315
DBCC SHRINKFILE command, 315–316
DBCC SQLPERF command, 117, 120
DBCC TRACEOFF command, 33, 51
DBCC TRACEON command, 33, 51
DBCC TRACESTATUS command, 33
DBCC UPDATEUSAGE command, 51, 316–317
DBCC USEROPTIONS command, 51
dbcreator role, 580
DBREINDEX command, 107, 309
DDL_APPLICATION_ROLE_EVENTS category, 254
DDL_ASSEMBLY_EVENTS category, 254, 277
DDL_AUTHORIZATION_DATABASE_EVENTS category, 254
DDL_DATABASE_LEVEL_EVENTS category, 254, 271, 277
DDL_DATABASE_SECURITY_EVENTS category, 254–255, 271, 277
DDL_ENDPOINT_EVENTS category, 256, 277
DDL_PARTITION_EVENTS category, 255, 271, 277
DDL_SERVER_LEVEL_EVENTS category, 256, 276
DDL_SERVER_SECURITY_EVENTS category, 256, 277
DDL_SSB_EVENTS category, 255, 271–272, 278
DDL_SYNONYM_EVENTS category, 255, 272, 278
DDL_TABLE_VIEW_EVENTS category, 255, 272, 278
DDL_TRIGGER_EVENTS category, 256, 272, 278
DDL triggers, 270–274, 542–545
DDL_TYPE_EVENTS category, 256, 272, 278
DDL_XML_SCHEMA_COLLECTION_EVENTS category, 256, 272, 278
DDOS (distributed denial-of-service) attacks, 530
Deadlock event class, 142, 241
Deadlock Chain event class, 142, 240
Deadlock Graph event class, 142, 150, 150, 240
deadlocks, 141–142, 142
error logs for, 143–146
event classes for, 142–143, 143
Performance Logs and Alerts for, 12–18, 13–15, 17–18
priority, 146–147
troubleshooting, 147–151, 150
debugging SSIS packages, 383–385, 384–385
decimal data type, 440
dedicated administrator connection (DAC), 38
dedicated snapshot folders, 491
DEFAULT definitions, 437–439
default trace, 29
defragmenting indexes, 310–312
delays in transactional replication, 477
delegation, linked servers for, 468–469, 468
Delegation tab, 464, 465
DeMorgan's Law, 412
denial-of-service (DOS) attacks, 529–530
DENY statement, 579
dependency diagrams, 320–321
deploying updates, 566
Derived Column transforms, 376
destinations for SSIS packages, 378
development environments in security, 537
development tools for quality data, 403
differential backups, 185–187
Dimension Processing destination, 378
directories
permissions, 589
virtual, 338
dirty reads, 123–124
DISABLE TRIGGER statement, 263
disabling
lock escalation, 139
logins, 581
row-level/page-level locks, 139
triggers, 263
Disallow adhoc access option, 462
Disconnect Users in the Database When Restoring Backups option, 173, 173
Disk Bytes/sec counter, 237
disk drives, fault tolerant, 165–169, 166–169
disk I/O counters
baselines, 230–231
thresholds, 236–237
% Disk Read Time counter, 230
% Disk Time counter, 23, 237
Disk Usage reports, 48, 48
% Disk Write Time counter, 230
diskadmin role, 580
distrib.exe (Distribution Agent), 175, 479
distributed denial-of-service (DDOS) attacks, 530
Distributed Management Task Force (DMTF), 253
distributed queries, 458, 469–474, 470, 472–473
Distribution Agent (distrib.exe), 175, 479
Distribution Agent Location page, 487
Distribution Agent Security page, 488, 488
Distribution Clean Up job, 504
distributions, replication, 174
Distributor page, 482
distributors, replication, 174, 474, 475
DML triggers, 263–264
AFTER, 264–266, 265
execution order, 264
INSTEAD OF, 266–269
DMTF (Distributed Management Task Force), 253
DMVs. See dynamic management views (DMVs)
Do Not Save Sensitive option, 379
documenting
actions, 400
changes, 539
Domain user account, 558
domains
integrity, 434–436, 436–437
permissions, 589
doors, access codes for, 524
DOS (denial-of-service) attacks, 529–530
double hops, 468, 468
doubly linked lists, 102, 102
DROP_EXISTING clause, 107
DROP INDEX command, 108
DROPCLEANBUFFERS command, 51, 309–310
dropping indexes, 108
DTA (Database Tuning Advisor), 52, 71–76, 73–76
DTS (Data Transformation Services), 360–362
dtutil utility, 383
duration of synchronization, baselines for, 513
dynamic management views (DMVs), 19–20, 19
common language runtime, 33–34
database-level, 52–56
full-text indexes, 34
I/O, 35
latches, 119–120
locks, 134–137, 137
for poorly performing queries, 79–81
queries, 54–55
query notifications, 35
replication, 35–36
service broker, 36
SQL operating system, 36–38
transactions, 56
Dynamic parameter option, 462