MCITP Administrator: Microsoft SQL Server 2005 Optimization and Maintenance (Exam 70-444) Study Guide
@@PACK_SENT function, 32
packages. See SQL Server Integration Services (SSIS)
PAD_INDEX option, 97
Page Faults/sec counter, 230–231
page-level locks, disabling, 139
Page Life Expectancy counter, 43–44, 238
PAGE resource, locks for, 128
Page Splits/sec counter, 233
pages, restoring, 207–208
Pages Scanned statistic, 312
Pages/sec counter
baselines, 230
thresholds, 236
PAGLOCK hint, 140
parameters, agent profiles, 509
partial database backups, 188–189
partial database restores, 197–198
PARTIAL option, 197
partially compatible jobs, 321
Partition Processing destination, 378
pass-through tables, 458
PASSWORD option, 192
Password Safe mechanism, 573
passwords, 532–534
changing, 558, 576–577
complexity, 557, 577
encrypting, 570
expiration, 576
for keys, 573
requirements, 576–577
patches
importance, 531–532
service packs, 564
peer-to-peer replication
multiple master approach, 433–434, 433
transactional, 176–177, 177
Percent Log Used counter, 232
Percentage Sampling transforms, 377
performance
counters. See performance object counters
indexes, 101–105
instance. See instance performance
overhead, 222–223
queries. See queries
replication, 511–513
replication baselines, 513
SSIS for, 386
SSRS, 343
tools, 222–224
troubleshooting. See troubleshooting
Performance Collector, 324
Performance Counters Limit Dialog window, 17, 17
Performance Logs and Alerts tool, 6, 8–9, 12–18, 13–15, 17–18
performance object counters
baselines
database engine, 231–234
disk I/O, 230–231
memory, 230
network I/O, 231
processors, 229
latches, 119
locks, 133
SQLH2, 323
System Monitor, 7
thresholds
database engine, 237–238
disk I/O, 236–237
memory, 236
network I/O, 237
processors, 235–236
Performance reports, 27
permissions
auditing, 557–559
domains and directory, 589
server, 578–580
verifying, 586–589, 586–588
phantom values, 124
physical integrity checks, 301–304
PHYSICAL_ONLY option
DBCC CHECKDB, 302
DBCC CHECKTABLE, 307
physical operation of query plans, 83
physical server monitoring, 223–224
alerts. See alerts
baselines, 225–226, 229
database engine, 231–234
disk I/O, 230–231
memory, 230
network I/O, 231
processors, 229
benchmarks, 226
event notifications, 274–280, 275
exam essentials, 281–282
hardware, 227
networks, 227
operating systems, 227–228
performance, 20–24
proactive, 224–225
review questions, 283–291
solutions, 226–227
sources, 228
summary, 281
thresholds, 234
database engine, 237–238
disk I/O, 236–237
memory, 236
network I/O, 237
processors, 235–236
traces, 238–242
triggers. See triggers
piecemeal restores, 197–198
Pivot transforms, 377
plain-text values, 570
Plan Cache counter, 234
plan guides, 92–94
plans
backup strategies, 179–180
query. See queries
plenum spaces, 525
point of sale (POS) applications, 481
Pool Non-Paged Bytes counter, 230
Pool Paged Bytes counter, 230
poorly performing queries, 76–77
DMVs, 79–81
plans for, 77–78, 77
traces for, 78
POS (point of sale) applications, 481
precision, data types, 441
predefined alerts, 494–498, 495–497
preproduction environments in security, 537–538
primary key constraints, 435
principle of least privilege, 533
priority, deadlocks, 146–147
Private Buffer Memory counter, 386
Private Buffers in Use counter, 386
Private Bytes counter, 231
private keys, 571
% Privileged Time counter, 229, 235
proactive monitoring, 224–225
proactive troubleshooting, 2
PROCCACHE command, 51
% Processor Time counter, 21–22, 229, 235
procedure cache, freeing, 310
Process Info page, 29, 132, 132
processadmin role, 580
Processes blocked counter, 232
Processor Queue Length counter, 21, 229, 236
processors
performance object counters
baselines, 229
thresholds, 235–236
troubleshooting, 21–22, 39–42, 41
production environments in security, 537
production support environment in security, 538
profiles, agents, 508–511
Project Configuration dialog box, 369
@provider argument, 459
Provider Options dialog box, 461, 461
@provstr argument, 459
public keys, 571
Publication page, 486, 486
Publication Database page, 482
Publication Type page, 483, 483
publications, replication, 175, 475, 512
publishers, replication, 175, 427, 474, 475
pull subscriptions, 475
push subscriptions, 475, 486–490, 486–490