book

Mastering SQL Server® 2008

by Michael Lee, Gentry Bieker

January 2009

Intermediate to advanced

792 pages

23h 8m

English

Sybex

Read now

Unlock full access

Who Should Read This BookThe Mastering SeriesWhat Is Covered in This BookHow to Contact the Authors

SQL Server in the Enterprise WorldInstalling SQL Server 2008The SQL Server Tool SetThe Bottom Line
Essential InternalsThe SQL Server System CatalogDatatypingThe Bottom Line
Planning a DatabaseCreating DatabasesManaging Databases, Files, and FilegroupsThe Bottom Line
Designing Data StorageCreating and Dropping SQL Server TablesSelecting Data from a TableModifying DataThe Bottom Line
Procedural StructuresStored ProceduresViewsUser-Defined FunctionsUsing SynonymsThe Bottom Line
Understanding Data Integrity ControlsWorking with ConstraintsWorking with DML TriggersRule and Default ObjectsThe Bottom Line
Returning XML from SQL Server Using FOR XMLThe xml DatatypeValidating XML with SchemasImplementing XQueryXML IndexesThe Bottom Line
The SQL Server Security InfrastructureImplementing SQL Server Principals and AuthenticationImplementing Permissions in SQL ServerThe Bottom Line
Transaction ArchitectureBackup and Restore StrategiesPerforming BackupsPerforming RestoresOther IssuesThe Bottom Line
Using System MonitorUsing SQL Profiler & TraceDDL TriggersEvent NotificationsThe Bottom Line
SQL Agent and AutomationCreating JobsCreating AlertsCreating OperatorsScripting the SQL AgentThe Bottom Line
Overview of SQL Server Availability and Distribution OptionsDatabase Log ShippingDatabase MirroringReplicationThe Bottom Line
Understanding the SQL Server Key InfrastructureUsing Transparent Data EncryptionEncrypting Data Using SQL ServerModule SigningThe Bottom Line
Index ArchitectureCreating and Managing IndexesSpecial Index ConsiderationsThe Bottom Line
The SQL Server Locking ArchitectureTransaction ManagementDistributed TransactionsGetting Transaction and Locking InformationThe Bottom Line
The Resource GovernorUnderstanding Policy-Based ManagementThe Bottom Line
Understanding ADO.NETSpecial Operations in ADO.NETIntroducing Language Integrated Query (LINQ)LINQ and the Entity FrameworkThe Bottom Line
CLR ConceptsCreating Managed Objects in SQL ServerDeploying Managed Objects in SQL ServerManaging CLR SecuritySecurity ConsiderationsThe Bottom Line
Spatial Data ConceptsImplementing the Geometry TypeImplementing the Geography TypeSpatial IndexesThe Bottom Line
The Essentials of Service-Oriented ArchitectureUnderstanding the Service BrokerImplementing Service BrokerThe Bottom Line
Full-Text ArchitectureCreating Full-Text IndexesWriting Full-Text QueriesThe Bottom Line
Chapter 1: Overview of Microsoft SQL Server 2008Chapter 2: Understanding SQL Server ArchitectureChapter 3: Databases, Files, and Resource ManagementChapter 4: Tables and CRUD OperationsChapter 5: Procedural ProgrammingChapter 6: Managing Data IntegrityChapter 7: SQL Server and XMLChapter 8: Managing User SecurityChapter 9: Data RecoveryChapter 10: Monitoring SQL Server ActivityChapter 11: Using the SQL Server Agent ServiceChapter 12: Data Availability and DistributionChapter 13: SQL Server Data SecurityChapter 14: Indexing Strategies for Query OptimizationChapter 15: Transactions and LockingChapter 16: Using the Resource Governor and Policy-Based ManagementChapter 17: SQL Server and the .NET ClientChapter 18: SQL Server and the Common Language RuntimeChapter 19: Using Spatial DataChapter 20: Service-Oriented Architecture and the Service BrokerChapter 21: Full-Text Search Services
Designing the Data WarehouseUnderstanding Analysis Services
Configuring Reporting ServicesCreating a Simple ReportDeploying the Report

Content preview from Mastering SQL Server® 2008

Chapter 14 Indexing Strategies for Query Optimization

SQL Server applications are all about executing queries, so you need to do everything you can to make sure that those queries execute as quickly and efficiently as possible. Because SQL Server is a shared service with multiple users, tuning an application from the perspective of one user is not enough. You must also consider the impact that many users accessing data concurrently will have on performance.

Tuning a SQL Server application is a cost-based approach where you will have to evaluate the aggregate costs of the resources consumed and attempt to mitigate those costs by using optimization techniques including indexing, archiving, and load balancing to name a few. While there are many ...