P


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



Professional SQL Server 2005 Administration
Professional SQL Server 2005 Administration (Wrox Professional Guides)
ISBN: 0470055200
EAN: 2147483647
Year: 2004
Pages: 193

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net