P

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