page reads, query performance, 285
page-level output caching, 99
pages, flushing data pages to disk, 451
parameters, XQuery, 84–85
partition functions
creating, 345
defining range of records included in a partition, 346
partitioning, 344–348
exercise creating partition function, 364
function catalog views, 348
functions of, 345
object catalog views, 348
overview of, 344
schemes, 346
vertical and horizontal types, 344
passwords
authentication and, 9
policies for data access, 12–13
peer-to-peer replication, 359–360
effective use of, 360
overview of, 359
performance baselines
creating, 380
exercise creating for queries, 324–325
Performance Monitor (Perf Mon). See System Monitor
performance, 372–391
case scenario diagnosing performance, 391
case scenario evaluating cursor performance, 140
check constraints and, 202
common issues, 384
DMV (dynamic management views) for analyzing, 380
excessive blocking, 384
exercise using SQL Server Profiler, 386–390
I/O and CPU bottlenecks, 384
inefficient or slow-running queries, 384
maximizing cursor performance, 120
limited space for tempdb, 385
memory issues, 385
orphaned connections, 385
overview of, 372
performance baselines, 380
read uncommitted isolation level and, 238
SQL Server Profiler for analyzing, 372–377
System Monitor for analyzing, 377–379
trace events, 461
permissions
SSAS security, 156
T-SQL for verifying, 210
Perspective class, AMO OLAP, 155
phantom reads
concurrency problems, 235
exercise protecting against, 273, 276
repeatable read isolation and, 240
serializable isolation level and, 242
PIVOT operators, in SELECT statements, 54
plan guides
case scenario creating plan guide, 93
exercise creating, 78
query hints, 73
Point in Time, storage engine, 444
policies, password, 12–13
port affinity, TCP/IP, 414
ports
configuring TCP/IP port affinity, 414
SQL Server 2005 configured for ports, 416
practice tests
Exam 70–442, xxiv
how to use, xxv
installing, xxv
optional modes, xxv
uninstalling, xxvi
Preparation Guide, for Exam 70–442, xix
primary keys
declarative data integrity and, 195
DROP INDEX statement and, 347
join operations and, 49
primary XML indexes, 469
ProactiveCaching class, AMO OLAP, 155
procedural data integrity, 208–216
data validation in stored procedures, 209
defined, 195
DML triggers and, 209
exercise comparing with declarative data integrity, 213
overview of, 208
SQL injection attacks and, 211–213
T-SQL for verifying permissions, 210
procedure cache
as a memory pool, 458
query engine and, 459
Procedure Cache Manager, 455
Process, AMO permissions, 156
processor affinity, 413
Prolog, XQuery namespace declaration, 81
providers. See data providers
proxy code, WSDL document for generating, 23
Publisher object, RMO 149, 151