Package Deployment Utility, 167–168
Package Installer Wizard, 168–169
packages, SSIS
creating, 158–163
definition of, 158
deploying, 166–170
digital signatures for, 178
execution of, 171–175, 178
exporting, 166, 170
importing, 165–166, 170
managing, 163–165
resources used by, 178
saving, 177
scheduling execution of, 173–175
security for, 176–177, 178
templates for, 162–163
PAEs (Physical Address Extensions), 345, 357, 393
page faults, 356
page file, 355–356
parallel index operations, 482
parallel redo, database mirroring, 560
parallelism, support for, 14
Pareto Principle, 377
parsing phase of query compilation, 439, 447–448
partial backup, 593
partial database restore, 615
partial differential backup, 594
partition function, 384–385, 487
partition indexes, 382–389, 481, 486–488, 490
partition scheme, 385–386
partition tables
adding data to, 489–492
creating, 386–389, 487–489, 623
definition of, 382–383, 481, 623
deleting data from, 492–495
in production system, 495–496
reasons to use, 383, 486–487
recommendations for, from DTA, 383, 496
support for, 15, 487
partition views, 624
partitioned disks, 373
partitioned table query plan, 478–479
passive (inactive) node, for clustering, 654, 657–658
password expiration, SQL Native Client used by, 5
peer-to-peer replication, 530, 542–544
PerfMon.exe program, 290
performance. See also monitoring
after upgrading, 72–74
of backup and recovery, 613–614
baselines for, taking, 418
for CLR, 290–292
CPU configuration for
cache, 349–350
hyperthreading, 348–349
multi-core systems, 351–353
processors, 347–348
data affecting, 343–344
for enterprise data management, 52
evaluating, 405
fragmentation affecting, 374–375
goals for
attaining in production, 344–345
defining, 340–341
hardware configuration for, 346–347
I/O configuration for, 362–365
of log shipping, 650
memory configuration for
page faults, 356
page file, 355–356
physical address space, 355
physical memory, 354
scenarios for, 359–362
64-bit systems, 359
32-bit systems, 357–359
virtual address space, 357
VMM, 355
monitoring, tools for, 344–345
optimizing SQL Server for, 345–346
priorities for, focusing on, 342–343
queries affecting, 343
schema affecting, 344
for Service Broker applications, 267–268, 272–273
of SQL Profiler, 430–431
SQL Server configuration for, 372–374
for SSAS, 198–201
for SSIS, 156–158
storage system design for, 365–372
throughput of system resources, 346
of traces, 430–431
tuning cycle for, 340
user considerations, 343
Performance Condition alerts, 115–117
Performance Monitor
correlating log data from, with trace data, 428–429
definition of, 344, 404, 405
disk activity counters, 408–414
memory usage counters, 414–417
processor usage counters, 406–408
performance tuning
actual execution plan, obtaining, 459–462
baselines for, 453
column statistics, 476–477
cycle for, 340
data modification plans, 477–478
database I/O information, 453–454
estimated execution plan, viewing, 455–459
index access methods
clustered index scan, ordered, 469–471
clustered index scan, unordered, 467–468
nonclustered index scan, ordered, 471
nonclustered index scan, unordered, 468–469
table scan, 465–467
join algorithms, 462–465
partitioned table query plan, 478–479
query plan, viewing (Showplans), 455
query processing, 439–442
permissions. See also security
for administrators, configuring, 42
for backup and restore, 601
column-level permissions, 318–319
for dynamic management views and functions, 433
granular permissions, 304–305
for Service Broker objects, 256–257
for SSAS database roles, 208–210
too many, causing database failure, 593
troubleshooting, 314
Phillipoom, Bruce (Raymond James Financial), 706
Physical Address Extensions (PAEs), 345, 357, 393
physical address space, 355
physical memory (RAM), 354. See also memory
plan cache, clearing, 467
poison messages, in Service Broker, 258–261, 263
primary server, for log shipping, 629
principal database, 548
principal server, 548
priority boost, 399
private network, for clustering, 659
privileged mode, 399
problems (troubleshooting). See also logs; support; trace flags
for clustering, 697–699
for Database Mail, 142
for database mirroring, 577–579
for log shipping, 638–639, 641
permissions, 314
for Service Broker applications, 263–272
for SQL Server Agent, 141–143
Process ID (SPID), monitoring, 92, 94
processadmin role, 297
processes
disconnecting, 93
monitoring, 92, 95–97
Processor licensing model, 16
processor usage counters, Performance Monitor, 406–408
processors
bottlenecks, resolving, 408
cache, 349–350
configuring use of, 88
monitoring usage of, 406–408
multiprocessor systems
counters for, 406
licensing for, 16
number of
performance considerations for, 345
setting, 42
used by SQL Server, 13
planning requirements for, 20
SMP (symmetric multi-processor), 353
types of, 347–348
Product Support Services (PSS), 47–49
Production DBA, 1–2
Professional SQL Server Analysis Services with MDX (Harinath and Quinn), 181
Professional SQL Server 2005
Integration Services (Knight et al.), 145, 273
Profiler, SQL Server
definition of, 345, 404
granting access to, 302
managing trace data, 424–430
monitoring CLR using, 291
monitoring database mirroring using, 577
monitoring Service Broker using, 267–268
monitoring SSAS using, 198–201
new features in, 424–425
performance of, 430–431
recompilation events, monitoring, 446
templates for, 419
projects
connection for, creating, 322
creating, 321–322
definition of, 321
integrating with source-control system, 321, 323
queries for, creating, 323
protocols
configuring, 75, 78
Multiprotocol, 65
Simple Mail Transfer Protocol (SMTP), 127, 131
write ahead logging (WAL) protocol, 594
XML for Analysis (XML/A) protocol, 184
proxy accounts, for SQL Server Agent, 119–122
PSS (Product Support Services), 47–49
PSSDiag.exe program, 101
public network, for clustering, 659
publications
"DDL Event Groups for Use with Event Notifications" (Books Online topic), 433
"Dynamic Management Views and Functions" (Books Online topic), 434
"I/O Architecture" (Books Online topic), 379
Professional SQL Server Analysis Services with MDX (Harinath and Quinn), 181
Professional SQL Server 2005
Integration Services (Knight et al.), 145, 273
"SQL Trace Terminology" (Books Online topic), 418
"Using SQL Server Profiler" (Books Online topic), 425
white paper on tempdb database, 453
Pubs database, 37, 64
pure transaction-log backup, 595