Inside Microsoft SQL Server 2000

Over 60 SQL Server MVPs contributed to this book, which was assembled and edited by Kalen Delaney and SQL Server legends Louis Davidson (Database Design and Architecture), Greg Low (Business Intelligence.), Brad McGhee (Performance Tuning and Optimization), Paul Nielson (Database Development), and Paul Randal and Kimberly Tripp (Database Administration.)

Table of Contents Foreword xxi Prefaces xxiii System Requirements xxxv Part I Overview Chapter 1 The Evolution of Microsoft SQL Server: 1989 to 2000 3 SQL Server: The Early Years 4 Ron s Story 6 Kalen s Story 7 Microsoft SQL Server Ships 8 Development Roles Evolve 10 OS/2 and Friendly Fire 11 SQL Server 4.2 13 OS/2 2.0 Release on Hold 14 Version 4.2 Released 14 SQL Server for Windows NT 15 Success Brings Fundamental Change 20 The End of Joint Development 21 The Charge to SQL95 23 The Next Version 25 The Secret of the Sphinx 26 Software for the New Century 28 Chapter 2 A Tour of SQL Server 31 The SQL Server Engine 33 Transact-SQL 33 DBMS-Enforced Data Integrity 37 Declarative Data Integrity 38 Datatypes 39 CHECK Constraints and Rules 40 Defaults 40 Triggers 40 Transaction Processing 42 Atomicity 42 Consistency 43 Isolation 43 Durability 43 Symmetric Server Architecture 44 Traditional Process/Thread Model 44 SQL Server Process/Thread Model 45 Multiuser Performance 45 Security 46 Monitoring and Managing Security 47 High Availability 47 Distributed Data Processing 48 Data Replication 50 Systems Management 52 SQL Server Enterprise Manager 52 Distributed Management Objects 54 Windows Management Instrumentation 54 SQL-DMO and Visual Basic Scripting 55 SQL Server Agent 55 SQL Server Utilities and Extensions 57 Web Assistant Wizard and Internet Enabling 57 SQL Profiler 58 SQL Server Service Manager 59 System Monitor Integration 60 Client Network Utility 60 Server Network Utility 61 SQL Server Installation 61 OSQL and ISQL 61 SQL Query Analyzer 61 Bulk Copy and Data Transformation Services 62 SNMP Integration 63 SQL Server Books Online 63 Client Development Interfaces 63 ODBC 64 OLE DB 64 ADO 64 DB-Library 65 ESQL/C 65 Server Development Interface 65 Summary 66 Part II Architectural Overview Chapter 3 SQL Server Architecture 69 The SQL Server Engine 69 The Net-Library 70 Open Data Services 74 The Relational Engine and the Storage Engine 77 The Access Methods Manager 81 The Row Operations Manager and the Index Manager 82 The Page Manager and the Text Manager 86 The Transaction Manager 87 The Lock Manager 90 Other Managers 91 Managing Memory 91 The Buffer Manager and Memory Pools 92 Access to In-Memory Pages 92 Access to Free Pages (Lazywriter) 93 Checkpoints 95 Accessing Pages Using the Buffer Manager 97 Large Memory Issues 97 The Log Manager 101 Transaction Logging and Recovery 101 Locking and Recovery 104 Page LSNs and Recovery 104 The SQL Server Kernel and Interaction with the Operating System 106 Threading and Symmetric Multiprocessing 107 The Worker Thread Pool 109 Disk I/O in Windows NT/2000 111 Summary 111 Part III Using Microsoft SQL Server Chapter 4 Planning for and Installing SQL Server 115 SQL Server Editions 116 Embedded SQL Server 119 Hardware Guidelines 119 Use Hardware on the Windows Hardware Compatibility List 119 Performance = Fn (Processor Cycles, Memory, I/O Throughput) 120 Invest in Benchmarking 121 Hardware Components 121 The Processor 122 Memory 124 Disk Drives, Controllers, and Disk Arrays 125 RAID Solutions 120 More About Drives and Controllers 137 Uninterruptible Power Supply 138 The Disk Subsystem 139 Fallback Server Capability 140 Other Hardware Considerations 141 The Operating System 141 The File System 142 Security and the User Context 143 Licensing 145 SQL Server Processor License 145 Server Licenses and CALs 145 Multiplexing: Use of Middleware, Transaction Servers, and Multitiered Architectures 146 Multiple Instances 147 Network Protocols 150 Collation 150 Character Sets 151 Sort Orders 152 Multiple Instances 157 Installing Named Instances 158 Named Instance Server Connectivity 159 Installing SQL Server 160 Upgrading from a Previous Version 161 Basic Configuration After Installation 162 Starting the SQL Server Service 162 Changing the System Administrator Password 163 Configuring SQL Server s Error Log 164 Working with Multiple Instances 164 Remote and Unattended Installation 166 Remote Installation 167 Unattended Installation 167 Changing Installation Options 168 Adding Additional Components 169 Summary 170 Chapter 5 Databases and Database Files 171 Special System Databases 172 master 172 model 173 tempdb 173 pubs 174 Northwind 174 msdb 175 Database Files 175 Creating a Database 176 A CREATE DATABASE Example 178 Expanding and Shrinking a Database 179 Automatic File Expansion 179 Manual File Expansion 179 Automatic File Shrinkage 179 Manual File Shrinkage 180 Changes in Log Size 182 Log Truncation 185 Using Database Filegroups 186 The Default Filegroup 187 A FILEGROUP CREATION Example 189 Altering a Database 190 ALTER DATABASE Examples 191 Databases Under the Hood 192 Space Allocation 194 Setting Database Options 197 State Options 198 Cursor Options 202 Auto Options 202 SQL Options 203 Recovery Options 205 Other Database Considerations 205 Databases vs. Schemas 205 Using Removable Media 206 Detaching and Reattaching a Database 207 Compatibility Levels 208 Backing Up and Restoring a Database 209 Types of Backups 210 Recovery Models 211 Choosing a Backup Type 214 Restoring a Database 215 Summary 219 Chapter 6 Tables 221 Creating Tables 222 Naming Tables and Columns 223 Reserved Keywords 224 Delimited Identifiers 224 Naming Conventions 225 Datatypes 226 Much Ado About NULL 235 User-Defined Datatypes 239 Identity Property 241 Internal Storage 245 Data Pages 246 Examining Data Pages 248 The Structure of Data Rows 252 Column Offset Arrays 254 Storage of Fixed-Length and Variable-Length Rows 255 Page Linkage 259 Text and Image Data 260 sql_variant Datatype 266 Constraints 270 PRIMARY KEY and UNIQUE Constraints 271 FOREIGN KEY Constraints 277 Constraint-Checking Solutions 286 Restrictions on Dropping Tables 287 Self-Referencing Tables 287 CHECK Constraints 289 Default Constraints 294 More About Constraints 298 Altering a Table 306 Changing a Datatype 306 Adding a New Column 308 Adding, Dropping, Disabling, or Enabling a Constraint 308 Dropping a Column 309 Enabling or Disabling a Trigger 309 Temporary Tables 309 Private Temporary Tables (#) 310 Global Temporary Tables (##) 310 Direct Use of tempdb 311 Constraints on Temporary Tables 311 System Tables 312 Summary 315 Chapter 7 Querying Data 317 The SELECT Statement 317 Joins 320 Outer Joins 324 The Obsolete *= OUTER JOIN Operator 329 Cross Joins 335 Dealing with NULL 336 NULL in the Real World 340 IS NULL and = NULL 343 Subqueries 345 Correlated Subqueries 350 Views and Derived Tables 357 Altering Views 361 Partitioned Views 362 Other Search Expressions 365 LIKE 365 BETWEEN 371 Aggregate Functions 371 Datacube Aggregate Variations 378 TOP 392 UNION 395 Summary 401 Chapter 8 Indexes 403 Index Organization 404 Clustered Indexes 406 Nonclustered Indexes 406 Creating an Index 408 Constraints and Indexes 410 The Structure of Index Pages 411 Clustered Index Rows with a Uniqueifier 412 Index Row Formats 415 Index Space Requirements 427 B-Tree Size 427 Actual vs. Estimated Size 428 Managing an Index 431 Types of Fragmentation 431 Detecting Fragmentation 432 Removing Fragmentation 435 Special Indexes 437 Prerequisites 437 Indexes on Computed Columns 441 Indexed Views 443 Using an Index 446 Looking for Rows 446 Joining 446 Sorting 446 Grouping 448 Maintaining Uniqueness 448 Summary 448 Chapter 9 Modifying Data 449 Basic Modification Operations 449 INSERT 450 UPDATE 463 DELETE 465 Modifying Data Through Views 467 Data Modification Internals 476 Inserting Rows 477 Splitting Pages 477 Deleting Rows 481 Updating Rows 489 Table-Level vs. Index-Level Data Modification 495 Logging 497 Locking 497 Summary 498 Chapter 10 Programming with Transact-SQL 499 Transact-SQL as a Programming Language 500 Programming at Multiple Levels 501 Transact-SQL Programming Constructs 503 Variables 503 Control-of-Flow Tools 510 CASE 511 PRINT 514 RAISERROR 515 FORMATMESSAGE 518 Operators 519 Scalar Functions 527 Table-Valued Functions 562 Transact-SQL Examples and Brainteasers 563 Generating Test Data 563 Getting Rankings 567 Finding Differences Between Intervals 573 Selecting Instead of Iterating 578 Full-Text Searching 579 Full-Text Indexes 581 Setting Up Full-Text Indexes 582 Maintaining Full-Text Indexes 585 Querying Full-Text Indexes 589 Performance Considerations for Full-Text Indexes 597 Summary 598 Chapter 11 Batches, Stored Procedures, and Functions 599 Batches 600 Routines 604 Stored Procedures 605 Nested Stored Procedures 607 Recursion in Stored Procedures 608 Stored Procedure Parameters 613 User-Defined Functions 617 Table Variables 617 Scalar-Valued Functions 618 Table-Valued Functions 621 System Table-Valued Functions 624 Managing User-Defined Functions 624 Rewriting Stored Procedures as Functions 627 Rolling Your Own System Routines 628 Your Own System Procedures 628 Your Own System Functions 630 Executing Batches, or What s Stored About Stored Procedures (and Functions)? 632 Step One: Parse Commands and Create the Sequence Tree 632 Step Two: Compile the Batch 632 Step Three: Execute 632 Step Four: Recompile Execution Plans 634 Storage of Routines 636 Encrypting Routines 637 Altering a Routine 640 Temporary Stored Procedures 640 Private Temporary Stored Procedures 640 Global Temporary Stored Procedures 641 Procedures Created from Direct Use of tempdb 641 Autostart Stored Procedures 642 System Stored Procedures 643 General System Procedures 644 Catalog Stored Procedures 645 SQL Server Agent Stored Procedures 646 Replication Stored Procedures 646 Extended Stored Procedures 647 Execute("any string") 653 Summary 655 Chapter 12 Transactions and Triggers 657 Transactions 657 Explicit and Implicit Transactions 659 Error Checking in Transactions 660 Transaction Isolation Levels 666 Other Characteristics of Transactions 676 Nested Transaction Blocks 677 Savepoints 681 Triggers 682 After Triggers 682 Instead-of Triggers 688 Managing Triggers 692 Using Triggers to Implement Referential Actions 692 Recursive Triggers 700 Summary 701 Chapter 13 Special Transact-SQL Operations: Working with Cursors and Large Objects 703 Cursor Basics 704 Cursors and ISAMs 707 Problems with ISAM-Style Applications 710 Cursor Models 711 Transact-SQL Cursors 711 API Server Cursors 712 Client Cursors 713 Default Result Sets 714 API Server Cursors vs. Transact-SQL Cursors 714 Appropriate Use of Cursors 715 Row-by-Row Operations 716 Query Operations 716 Scrolling Applications 717 Choosing a Cursor 718 Cursor Membership, Scrolling, and Sensitivity to Change 720 Working with Transact-SQL Cursors 725 DECLARE 726 OPEN 728 FETCH 729 UPDATE 730 DELETE 731 CLOSE 731 DEALLOCATE 732 The Simplest Cursor Syntax 732 Fully Scrollable Transact-SQL Cursors 732 Concurrency Control with Transact-SQL Cursors 735 Cursor Variables 743 Obtaining Cursor Information 744 Working with Text and Image Data 750 WRITETEXT 752 READTEXT 755 UPDATETEXT 761 Summary 769 Part IV Performance and Tuning Chapter 14 Locking 773 The Lock Manager 773 The Lock Manager and Isolation Levels 774 Spinlocks 775 Deadlocks 775 Lock Types for User Data 779 Lock Modes 780 Lock Granularity 780 Lock Duration 790 Lock Ownership 790 Viewing Locks 791 Lock Compatibility 798 Internal Locking Architecture 800 Lock Blocks 802 Lock Owner Blocks 804 Syslockinfo Table 804 Bound Connections 807 Row-Level vs. Page-Level Locking 811 Lock Escalation 812 Locking Hints and Trace Flags 813 Summary 813 Chapter 15 The Query Processor 815 The SQL Manager 816 Compilation and Optimization 816 Compilation 817 Optimization 818 How the Query Optimizer Works 820 Join Selection 839 Other Processing Strategies 845 Maintaining Statistics 846 The Procedure Cache 852 Using Stored Procedures and Caching Mechanisms 855 Ad Hoc Caching 856 Autoparameterization 856 The sp_executesql Procedure 857 The Prepare and Execute Method 857 Sharing Cached Plans 857 Examining the Plan Cache 858 Multiple Plans in Cache 860 When to Use Stored Procedures and Other Caching Mechanisms 862 Recompiling Stored Procedures 862 Other Benefits of Stored Procedures 863 Execution 865 Summary 865 Chapter 16 Query Tuning 867 The Development Team 868 Application and Database Design 868 Normalize Your Database 869 Evaluate Your Critical Transactions 871 Keep Table Row Lengths and Keys Compact 873 Planning for Peak Usage 874 Perceived Response Time for Interactive Systems 874 Prototyping, Benchmarking, and Testing 876 Development Methodologies 878 Creating Useful Indexes 880 Choose the Clustered Index Carefully 881 Make Nonclustered Indexes Highly Selective 882 Tailor Indexes to Critical Transactions 883 Pay Attention to Column Order 885 Index Columns Used in Joins 885 Create or Drop Indexes as Needed 887 The Index Tuning Wizard 887 Monitoring Query Performance 889 STATISTICS IO 889 STATISTICS TIME 893 Showplan 893 Using Query Hints 908 Stored Procedure Optimization 912 Concurrency and Consistency Tradeoffs 914 Resolving Blocking Problems 915 Indexes and Blocking 917 Resolving Deadlock Problems 919 Cycle Deadlock Example 919 Conversion Deadlock Example 919 Preventing Deadlocks 922 Handling Deadlocks 922 Volunteering to Be the Deadlock Victim 923 Watching Locking Activity 923 Identifying the Culprit 924 Lock Hints 931 Segregating OLTP and DSS Applications 934 Environmental Concerns 935 Case Sensitivity 935 Nullability and ANSI Compliance Settings 936 Locale-Specific SET Options 942 Summary 942 Chapter 17 Configuration and Performance Monitoring 943 Operating System Configuration Settings 944 Task Management 944 Resource Allocation 944 PAGEFILE.SYS Location 945 File System Selection 945 Nonessential Services 946 Network Protocols 946 SQL Server Configuration Settings 946 Serverwide Options 947 Buffer Manager Options 958 Startup Parameters on SQLSERVR.EXE 960 System Maintenance 960 Monitoring System Behavior 961 SQL Profiler 961 System Monitor 976 Other Performance Monitoring Considerations 985 Summary 985 Bibliography and Suggested Reading 987 Index 993