Microsoft SQL Server 2005 Performance Optimization and Tuning Handbook
Regular price
26.250 KD
inc. VAT
Couldn't load pickup availability
Table of contents
- Cover
- Contentsvii
- Introductionxv
- Chapter 1. Performance and SQL Server 20051
- 1.1 Partitioning tables and indexes1
- 1.2 Building indexes online2
- 1.3 Transact SQL improvements2
- 1.4 Adding the .NET Framework3
- 1.5 Trace and replay objects4
- 1.6 Monitoring resource consumption with SQL OS4
- 1.7 Establishing baseline metrics4
- 1.8 Start using the GUI tools7
- 1.9 Availability and scalability15
- 1.10 Other useful stuff16
- 1.11 Where to begin?17
- Chapter 2. Logical Database Design for Performance19
- 2.1 Introducing logical database design for performance19
- 2.2 Commercial normalization techniques21
- 2.3 Denormalization for performance29
- 2.4 Extreme denormalization in data warehouses48
- Chapter 3. Physical Database Design65
- 3.1 Introducing physical database design65
- 3.2 Data volume analysis67
- 3.3 Transaction analysis69
- 3.4 Hardware environment considerations73
- Chapter 4. SQL Server Storage Structures75
- 4.1 Databases and files75
- 4.2 Creating databases79
- 4.3 Increasing the size of a database83
- 4.4 Decreasing the size of a database84
- 4.5 Modifying filegroup properties90
- 4.6 Setting database options92
- 4.7 Displaying information about databases95
- 4.8 System tables used in database configuration98
- 4.9 Units of storage102
- 4.10 Database pages104
- 4.11 Looking into database pages108
- 4.12 Pages for space management112
- 4.13 Partitioning tables into physical chunks115
- 4.14 The BankingDB database119
- Chapter 5. Indexing121
- 5.1 Data retrieval with no indexes121
- 5.2 Clustered indexes122
- 5.3 Non-clustered indexes127
- 5.4 Online indexes129
- 5.5 The more exotic indexing forms129
- 5.6 The role of indexes in insertion and deletion131
- 5.7 A note with regard to updates141
- 5.8 So how do you create indexes?142
- 5.9 Dropping and renaming indexes157
- 5.10 Displaying information about indexes158
- 5.11 Creating indexes on views167
- 5.12 Creating indexes with computed columns170
- 5.13 Using indexes to retrieve data171
- 5.14 Choosing indexes182
- Chapter 6. Basic Query Tuning193
- 6.1 The SELECT statement194
- 6.2 Using functions200
- 6.3 Comparison conditions201
- 6.4 Joins204
- 6.5 Using subqueries for efficiency210
- 6.6 Specialized metadata objects213
- 6.7 Procedures in Transact SQL214
- Chapter 7. What Is Query Optimization?217
- 7.1 When is a query optimized?218
- 7.2 The steps in query optimization218
- 7.3 Query analysis219
- 7.4 Index selection225
- 7.5 Join order selection246
- 7.6 How joins are processed247
- Chapter 8. Investigating and Influencing the Optimizer257
- 8.1 Text-based query plans and statistics259
- 8.2 Query plans in Management Studio270
- 8.3 Hinting to the optimizer282
- 8.4 Stored procedures and the query optimizer289
- 8.5 Non-stored procedure plans301
- 8.6 The syscacheobjects system table304
- Chapter 9. SQL Server and Windows307
- 9.1 SQL Server and CPU307
- 9.2 SQL Server and memory323
- 9.3 SQL Server and disk I/O335
- Chapter 10. Transactions and Locking355
- 10.1 Why a locking protocol?356
- 10.2 The SQL Server locking protocol358
- 10.3 SQL Server locking in action393
- 10.4 Uncommitted data, non-repeatable reads, phantoms, and more398
- 10.5 Application resource locks406
- 10.6 A summary of lock compatibility407
- Chapter 11. Architectural Performance Options and Choices409
- 11.1 The Management Studio and the .NET Framework410
- 11.2 Striping and mirroring410
- 11.3 Workflow management411
- 11.4 Analysis Services and data warehousing412
- 11.5 Distribution and replication414
- 11.6 Standby failover (hot spare)417
- 11.7 Flashback snapshot databases419
- Chapter 12. Monitoring Performance421
- 12.1 System stored procedures422
- 12.2 System monitor, performance logs, and alerts424
- 12.3 SQL Server 2005 Management Studio427
- 12.4 SQL OS and resource consumption443
- A Syntax Conventions445
- B Database Scripts447
- C Performance Strategies and Tuning Checklist477
- Index487
Database professionals will find that this new edition aids in mastering the latest version of Microsoft’s SQL Server. Developers and database administrators (DBAs) use SQL on a daily basis in application development and the subsequent problem solving and fine tuning. Answers to SQL issues can be quickly located helping the DBA or developer optimize and tune a database to maximum efficiency.
Basic questions are easily located on the topics of filtering, sorting, operators, conditionals, pseudo columns, single row functions, joins, grouping functions, sub queries, composite queries, hierarchies, flashback queries, parallel queries, expressions and regular expressions. Assistance on DML, data types (including collections), XML, DDL for basic database objects such as tales, views and indexes, partitioning, and security is also considered.
* Identifies and discusses the most common issues database administrators (DBAs) face day-to-day
*Provides DBAs with solutions actually used by the authors in enterprise environments
*Explores new features which add more control but reduce performance
Basic questions are easily located on the topics of filtering, sorting, operators, conditionals, pseudo columns, single row functions, joins, grouping functions, sub queries, composite queries, hierarchies, flashback queries, parallel queries, expressions and regular expressions. Assistance on DML, data types (including collections), XML, DDL for basic database objects such as tales, views and indexes, partitioning, and security is also considered.
* Identifies and discusses the most common issues database administrators (DBAs) face day-to-day
*Provides DBAs with solutions actually used by the authors in enterprise environments
*Explores new features which add more control but reduce performance