MCITP Administrator: Microsoft SQL Server 2005 Optimization and Maintenance (Exam 70-444) Study Guide

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