MCITP Self-Paced Training Kit (Exam 70-444): Optimizing and Maintaining a Database Administration Solution Using Microsoft SQL Server 2005: Optimizing ... SQL Server(tm) 2005 (Pro-Certification)

775 Pages·2006·6.76 MB·English

by  Orin Thomas

PUBLISHED BY
Microsoft Press
A Division of Microsoft Corporation
One Microsoft Way
Redmond, Washington 98052-6399
Copyright © 2006 by Microsoft Corporation and Ian McLean
All rights reserved. No part of the contents of this book may be reproduced or transmitted in any form or by
any means without the written permission of the publisher.
ISBN-13: 978-0-7356-2254-8
ISBN-10: 0-7356-2254-X
Library of Congress Control Number 2006932077
Printed and bound in the United States of America.
1 2 3 4 5 6 7 8 9 QWT 0 9 8 7 6 5
Distributed in Canada by H.B. Fenn and Company Ltd.
A CIP catalogue record for this book is available from the British Library.
Microsoft Press books are available through booksellers and distributors worldwide. For further information
about international editions, contact your local Microsoft Corporation office or contact Microsoft Press Inter-
national directly at fax [phone]. Visit our Web site at www.microsoft.com/mspress. Send comments
to [email protected].
Microsoft, Microsoft Press, Active Directory, ActiveX, BizTalk, Excel, Internet Explorer, JScript, MSDN,
Visual Basic, Visual C++, Visual C#, Visual J#, Visual SourceSafe, Visual Studio, Windows, Windows NT,
and Windows Server are either registered trademarks or trademarks of Microsoft Corporation in the United
States and/or other countries. Other product and company names mentioned herein may be the trademarks of
their respective owners.
The example companies, organizations, products, domain names, e-mail addresses, logos, people, places, and
events depicted herein are fictitious. No association with any real company, organization, product, domain
name, e-mail address, logo, person, place, or event is intended or should be inferred.
This book expresses the author’s views and opinions. The information contained in this book is provided with-
out any express, statutory, or implied warranties. Neither the authors, Microsoft Corporation, nor its resellers,
or distributors will be held liable for any damages caused or alleged to be caused either directly or indirectly
by this book.
Acquisitions Editor: Ken Jones
Project Editor: Laura Sackerman
Technical Editors: Rozanne Murphy Whalen and Dan Whalen
Indexer: William Meyers
Copy Editor: Roger LeBlanc
Body Part No. X12-48798
For Oksana and Rooslan: I love you.
Orin Thomas
To my daughter-in-law, Harjit, and my son-in-law, James,
both of whom chose to become related to me of their own free will.
Ian McLean
A04B62254X.fm Page vi Sunday, August 28, 2005 4:34 PM
Contents at a Glance
1 Troubleshooting Database and Server Performance. . . . . . . . . . . . . . . . . .1
2 Analyzing Queries. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 81
3 Failure Diagnosis. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 155
4 Disaster Recovery . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 193
5 Performance Monitoring. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 249
6 Database Maintenance. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 303
7 SQL Server Integration Services . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 367
8 Design Data Integrity. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 419
9 Business Requirements. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 453
10 Replication. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 523
11 Security Strategies. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 583
12 Detecting and Responding to Attacks. . . . . . . . . . . . . . . . . . . . . . . . . . . 647
Appendix . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 675
vii

Table of Contents
Acknowledgments . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . xxiii
Introduction. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .xxv
Hardware Requirements. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . xxv
Software Requirements. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .xxvi
Using the CD and DVD. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .xxvi
How to Install the Practice Tests . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .xxvi
How to Use the Practice Tests . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . xxvii
How to Uinstall the Practice Tests. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .xxviii
Microsoft Certified Professional Program. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .xxviii
Technical Support . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .xxix
Evaluation Edition Software Support. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .xxix
1 Troubleshooting Database and Server Performance. . . . . . . . . . . . . . . . . .1
Before You Begin. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .1
Lesson 1: Troubleshooting Physical Server Performance . . . . . . . . . . . . . . . . . . . . . . . .3
Using System Monitor and Performance Logs and Alerts . . . . . . . . . . . . . . . . . .3
Evaluating Memory Usage. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .5
Evaluating Disk Usage. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .8
Evaluating Processor Usage. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .10
Evaluating Network Usage. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .11
Evaluating User Connections. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .12
Solving Resource Problems . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .13
Generating Performance Counter Logs and Alerts . . . . . . . . . . . . . . . . . . . . . . .18
Lesson Summary . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .25
Lesson Review . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .25
Lesson 2: Troubleshooting Connectivity to a SQL Server Instance . . . . . . . . . . . . . . .28
Troubleshooting Tools. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .28
Analyzing Tempdb Database Issues . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .33
Monitoring Instance Memory Usage. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .35
Data Caching . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .35
Analyzing Statement Recompiles . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .36
What do you think of this book? Microsoft is interested in hearing your feedback about this publication so we can
We want to hear from you! continually improve our books and learning resources for you. To participate in a brief
online survey, please visit: www.microsoft.com/learning/booksurvey/
ix
x Table of Contents
Configuring Connections. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 38
SQL CLR Memory Usage . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 40
Configuring CPU Parallelism. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 41
Monitoring Waits and Queues . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 43
Using SQL Server Profiler. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 46
Lesson Summary. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 50
Lesson Review . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 51
Lesson 3: Troubleshooting Database Performance. . . . . . . . . . . . . . . . . . . . . . . . . . . . 53
Resolving Space Issues. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 53
Monitoring Auto-Grow and Auto-Shrink . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 55
Updating Statistics . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 55
Evaluating Index Usage . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 57
Auditing and Analyzing Poorly Written Queries . . . . . . . . . . . . . . . . . . . . . . . . . 61
Monitoring Transaction Log Size . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 63
Monitoring Database Growth. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 64
Investigating Locks and Deadlocks . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 67
Optimizing RAID Configuration. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 69
Troubleshooting Database and Transaction Log Storage. . . . . . . . . . . . . . . . . . 70
Using the Database Engine Tuning Advisor . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 70
Lesson Summary. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 74
Lesson Review . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 74
Chapter Review. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 76
Chapter Summary. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 76
Key Terms. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 77
Case Scenario . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 77
Case Scenario: Resolving Physical Server and Database Bottlenecks. . . . . . . . 78
Suggested Practices. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 79
Troubleshoot Physical Server Performance. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 79
Troubleshoot Instance Performance . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 79
Troubleshoot Database Performance. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 80
Take a Practice Test. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 80
2 Analyzing Queries. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 81
Before You Begin . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 82
Lesson 1: Identifying Poorly Performing Queries . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 83
Using Query Editor. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 83
Using SQL Server Profiler. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 86
Table of Contents xi
Using the Database Tuning Advisor . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .87
Using SQL Trace. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .88
Using DMVs . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .89
Identifying a Badly Performing Query. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .93
Lesson Summary . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .96
Lesson Review . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .97
Lesson 2: Analyzing a Query Plan to Detect Inefficiencies in Query Logic . . . . . . . .98
Detecting Excessive I/O Activity . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .98
Monitoring Table Scans . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .102
Monitoring CPU Utilization . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .107
Obtaining Query Plan Statistics. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .108
Lesson Summary . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .110
Lesson Review . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .110
Lesson 3: Maintaining and Optimizing Indexes . . . . . . . . . . . . . . . . . . . . . . . . . . . . .112
Defragmenting an Index . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .112
Reorganizing and Rebuilding an Index . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .113
Adding an Index . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .116
Specifying the Fill Factor . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .117
Using the PAD_INDEX Option. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .118
Using Clustered and Nonclustered Indexes. . . . . . . . . . . . . . . . . . . . . . . . . . . . .118
Using Covering Indexes . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .121
Using Indexed Views. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .123
Creating XML Indexes. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .124
Creating Partitioned Indexes. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .126
Performing Index Analysis . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .127
Lesson Summary . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .130
Lesson Review . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .130
Lesson 4: Enforcing Appropriate Stored Procedure Logging and Output. . . . . . . .132
Handling Exceptions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .134
Examining the Default Log Trace File. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .134
Lesson Summary . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .135
Lesson Review . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .136
Lesson 5: Troubleshooting Concurrency Issues. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .137
Using the SQL Server Locks Performance Counters . . . . . . . . . . . . . . . . . . . . .137
Evaluating the Transactions/sec Performance Counter. . . . . . . . . . . . . . . . . . .142
Using Alerts to Trigger the Notification Process. . . . . . . . . . . . . . . . . . . . . . . . .144
Using SQL Server Profiler to Troubleshoot Concurrency Issues. . . . . . . . . . . .146
xii Table of Contents
Saving a Deadlock Graph . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 146
Lesson Summary. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 148
Lesson Review . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 149
Chapter Review. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 151
Chapter Summary. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 151
Key Terms. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 151
Case Scenario . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 152
Case Scenario: Dealing with Compatibility Problems
and Fragmented Indexes . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 152
Suggested Practices. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 153
Identify Poorly Performing Queries. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 153
Analyze a Query Plan to Detect Inefficiencies in Query Logic . . . . . . . . . . . . 153
Maintain and Optimize Indexes. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 154
Enforce Appropriate Stored Procedure Logging and Output . . . . . . . . . . . . . 154
Troubleshoot Concurrency Issues. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 154
Take a Practice Test. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 154
3 Failure Diagnosis. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 155
Before You Begin . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 155
Lesson 1: Diagnosing Database Failures. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 157
Log File Viewer . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 157
Filtering Logs. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 158
Understanding Database Engine Errors . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 159
Diagnosing Common Problems Using Logs. . . . . . . . . . . . . . . . . . . . . . . . . . . . 162
Filtering a Log Using the Log File Viewer. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 165
Lesson Summary. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 166
Lesson Review . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 167
Lesson 2: Diagnosing Physical Server Failures . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 169
Diagnosing Volumes and Disks. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 170
Diagnosing RAM and Processor Problems. . . . . . . . . . . . . . . . . . . . . . . . . . . . . 174
Diagnosing Other Hardware Problems. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 175
Using CHKDSK. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 176
Lesson Summary. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 176
Lesson Review . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 176
Lesson 3: SQL Server Service Failures . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 178
SQL Server 2005 Services. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 178
Service Password Expiration . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 184