book

SQL Server 2008 Query Performance Tuning Distilled

Name: SQL Server 2008 Query Performance Tuning Distilled
ISBN: 9781430219026

by Grant Fritchey, Sajal Dam

March 2009

Intermediate to advanced

562 pages

15h 36m

English

Apress

Read now

Unlock full access

Cover Page
Title Page
Copyright
Contents at a Glance
Contents
About the Author
About The Technical Reviewer
Acknowledgments
Introduction
Who This Book Is ForHow This Book Is StructuredDownloading the CodeContacting the Author
CHAPTER 1: SQL Query Performance tuning
The Performance-Tuning ProcessThe Core ProcessIterating the ProcessPerformance vs. PricePerformance Targets"Good Enough" TuningPerformance BaselineWhere to Focus EffortsSQL Server Performance KillersPoor IndexingInaccurate StatisticsExcessive Blocking and DeadlocksNon-Set-Based OperationsPoor Query DesignPoor Database DesignExcessive FragmentationNonreusable Execution PlansPoor Execution PlansFrequent Recompilation of Execution PlansImproper Use of CursorsImproper Configuration of the Database LogExcessive Use or Improper Configuration of tempdbSummary

CHAPTER 2: System Performance Analysis
Performance Monitor ToolDynamic Management ViewsHardware Resource BottlenecksIdentifying BottlenecksBottleneck ResolutionMemory Bottleneck AnalysisSQL Server Memory ManagementAvailable BytesPages/sec and Page Faults/sec CountersBuffer Cache Hit RatioPage Life ExpectancyCheckpoint Pages/secLazy writes/secMemory Grants PendingTarget Server Memory (KB) and Total Server Memory (KB)Memory Bottleneck ResolutionsOptimizing Application WorkloadAllocating More Memory to SQL ServerIncreasing System MemoryChanging from a 32-bit to a 64-bit ProcessorEnabling 3GB of Process SpaceUsing Memory Beyond 4GB Within SQL ServerDisk Bottleneck AnalysisDisk Counters% Disk TimeCurrent Disk Queue LengthDisk Transfers/secDisk Bytes/secAvg. Disk Sec/Read and Avg. Disk Sec/WriteDisk Bottleneck ResolutionsOptimizing Application WorkloadUsing a Faster Disk DriveUsing a RAID ArrayUsing a SAN SystemAligning Disks ProperlyUsing a Battery-Backed Controller CacheAdding System MemoryCreating Multiple Files and FilegroupsPlacing the Table and Index on Separate DisksSaving Log Files to a Separate Physical DiskPartitioning TablesProcessor Bottleneck Analysis% Processor Time% Privileged TimeProcessor Queue LengthContext Switches/secBatch Requests/secSQL Compilations/secSQL Recompilations/secProcessor Bottleneck ResolutionsOptimizing Application WorkloadEliminating Excessive Compiles/RecompilesUsing More or Faster ProcessorsUsing a Large L2/L3 CacheRunning More Efficient Controllers/DriversNot Running Unnecessary SoftwareNetwork Bottleneck AnalysisBytes Total/sec% Net UtilizationNetwork Bottleneck ResolutionsOptimizing Application WorkloadAdding Network AdaptersModerating and Avoiding InterruptionsSQL Server Overall PerformanceMissing IndexesDatabase BlockingNonreusable Execution PlansGeneral BehaviorCreating a BaselineCreating a Reusable List of Performance CountersCreating a Counter Log Using the List of Performance CountersMinimizing Performance Monitor OverheadSystem Behavior Analysis Against BaselineSummary
CHAPTER 3: SQL Query Performance Analysis
The SQL Profiler ToolProfiler TracesEventsData ColumnsFiltersTrace TemplatesTrace DataTrace AutomationCapturing a Trace Using the GUICapturing a Trace Using Stored ProceduresCombining Trace and Performance Monitor OutputSQL Profiler RecommendationsLimiting the Number of Events and Data ColumnsDiscarding Start Events for Performance AnalysisLimiting the Trace Output SizeAvoiding Online Data Column SortingRunning Profiler RemotelyLimiting the Use of Certain EventsQuery Performance Metrics Without ProfilerCostly QueriesIdentifying Costly QueriesIdentifying Slow-Running QueriesExecution PlansAnalyzing a Query Execution PlanIdentifying the Costly Steps in an Execution PlanAnalyzing Index EffectivenessAnalyzing Join EffectivenessActual vs. Estimated Execution PlansPlan CacheQuery CostClient StatisticsExecution TimeSTATISTICS IOSummary
CHAPTER 4: Index Analysis
What Is an Index?The Benefit of IndexesIndex OverheadIndex Design RecommendationsExamine the WHERE Clause and Join Criteria ColumnsUse Narrow IndexesExamine Column UniquenessExamine the Column Data TypeConsider Column OrderConsider the Type of IndexClustered IndexesHeap TablesRelationship with Nonclustered IndexesClustered Index RecommendationsNonclustered IndexesNonclustered Index MaintenanceDefining the Bookmark LookupNonclustered Index RecommendationsClustered vs. Nonclustered IndexesBenefits of a Clustered Index over a Nonclustered IndexBenefits of a Nonclustered Index over a Clustered IndexAdvanced Indexing TechniquesCovering IndexesIndex IntersectionsIndex JoinsFiltered IndexesIndexed ViewsIndex CompressionSpecial Index TypesFull-TextSpatialXMLAdditional Characteristics of IndexesDifferent Column Sort OrderIndex on Computed ColumnsIndex on BIT Data Type ColumnsCREATE INDEX Statement Processed As a QueryParallel Index CreationOnline Index CreationConsidering the Database Engine Tuning AdvisorSummary
CHAPTER 5: Database Engine Tuning Advisor
Database Engine Tuning Advisor MechanismsDatabase Engine Tuning Advisor ExamplesTuning a QueryTuning a Trace WorkloadDatabase Engine Tuning Advisor LimitationsSummary
CHAPTER 6: Bookmark Lookup Analysis
Purpose of Bookmark LookupsDrawbacks of Bookmark LookupsAnalyzing the Cause of a Bookmark LookupResolving Bookmark LookupsUsing a Clustered IndexUsing a Covering IndexUsing an Index JoinSummary
CHAPTER 7: Statistics Analysis
The Role of Statistics in Query OptimizationStatistics on an Indexed ColumnBenefits of Updated StatisticsDrawbacks of Outdated StatisticsStatistics on a Nonindexed ColumnBenefits of Statistics on a Nonindexed ColumnDrawback of Missing Statistics on a Nonindexed ColumnAnalyzing StatisticsDensityStatistics on a Multicolumn IndexStatistics on a Filtered IndexStatistics MaintenanceAutomatic MaintenanceManual MaintenanceStatistics Maintenance StatusAnalyzing the Effectiveness of Statistics for a QueryResolving a Missing Statistics IssueResolving an Outdated Statistics IssueRecommendationsBackward Compatibility of StatisticsAuto Create StatisticsAuto Update StatisticsAutomatic Update Statistics AsynchronouslyAmount of Sampling to Collect StatisticsSummary
CHAPTER 8: Fragmentation Analysis
Causes of FragmentationPage Split by an UPDATE StatementPage Split by an INSERT StatementFragmentation OverheadAnalyzing the Amount of FragmentationAnalyzing the Fragmentation of a Small TableFragmentation ResolutionsDropping and Re-creating the IndexRe-creating the Index with the DROP_EXISTING ClauseExecuting the ALTER INDEX REBUILD StatementExecuting the ALTER INDEX REORGANIZE StatementSignificance of the Fill FactorAutomatic MaintenanceSummary
CHAPTER 9: Execution Plan Cache Analysis
Execution Plan GenerationParserAlgebrizerOptimizationExecution Plan CachingComponents of the Execution PlanQuery PlanExecution ContextAging of the Execution PlanAnalyzing the Execution Plan CacheExecution Plan ReuseAd Hoc WorkloadPrepared WorkloadPlan Reusability of an Ad Hoc WorkloadPlan Reusability of a Prepared WorkloadQuery Plan Hash and Query HashExecution Plan Cache RecommendationsExplicitly Parameterize Variable Parts of a QueryCreate Stored Procedures to Implement Business FunctionalityCode with sp_executesql to Avoid Stored Procedure MaintenanceImplement the Prepare/Execute Model to Avoid Resending a Query StringAvoid Ad Hoc QueriesPrefer sp_executesql over EXECUTE for Dynamic QueriesParameterize Variable Parts of Queries with CareDo Not Allow Implicit Resolution of Objects in QueriesSummary
CHAPTER 10: Stored Procedure Recompilation
Benefits and Drawbacks of RecompilationIdentifying the Statement Causing RecompilationAnalyzing Causes of RecompilationSchema or Bindings ChangesStatistics ChangesDeferred Object ResolutionSET Options ChangesExecution Plan AgingExplicit Call to sp_recompileExplicit Use of the RECOMPILE ClauseAvoiding RecompilationsDo Not Interleave DDL and DML StatementsAvoiding Recompilations Caused by Statistics ChangeUsing Table VariablesAvoiding Changing SET Options Within a Stored ProcedureUsing OPTIMIZE FOR Query HintUsing Plan GuidesSummary
CHAPTER 11: Query Design Analysis
Query Design RecommendationsOperating on Small Result SetsLimit the Number of Columns in select_listUse Highly Selective WHERE ClausesUsing Indexes EffectivelyAvoid Nonsargable Search ConditionsAvoid Arithmetic Operators on the WHERE Clause ColumnAvoid Functions on the WHERE Clause ColumnAvoiding Optimizer HintsJOIN HintINDEX HintsUsing Domain and Referential IntegrityNOT NULL ConstraintDeclarative Referential IntegrityAvoiding Resource-Intensive QueriesAvoid Data Type ConversionUse EXISTS over COUNT(*) to Verify Data ExistenceUse UNION ALL Instead of UNIONUse Indexes for Aggregate and Sort ConditionsAvoid Local Variables in a Batch QueryBe Careful Naming Stored ProceduresReducing the Number of Network Round-TripsExecute Multiple Queries TogetherUse SET NOCOUNTReducing the Transaction CostReduce Logging OverheadReduce Lock OverheadSummary
CHAPTER 12: Blocking Analysis
Blocking FundamentalsUnderstanding BlockingAtomicityConsistencyIsolationDurabilityDatabase LocksLock GranularityLock EscalationLock ModesLock CompatibilityIsolation LevelsRead UncommittedRead CommittedRepeatable ReadSerializableSnapshotEffect of Indexes on LockingEffect of a Nonclustered IndexEffect of a Clustered IndexEffect of Indexes on the Serializable Isolation LevelCapturing Blocking InformationCapturing Blocking Information with SQLProfiler Trace and the Blocked Process Report EventBlocking ResolutionsOptimize the QueriesDecrease the Isolation LevelPartition the Contended DataCovering Index on Contended DataRecommendations to Reduce BlockingAutomation to Detect and Collect Blocking InformationSummary
CHAPTER 13: Deadlock Analysis
Deadlock FundamentalsChoosing the Deadlock VictimUsing Error Handling to Catch a DeadlockDeadlock AnalysisCollecting Deadlock InformationAnalyzing the DeadlockAvoiding DeadlocksAccessing Resources in the Same Chronological OrderDecreasing the Number of Resources AccessedMinimizing Lock ContentionSummary
CHAPTER 14: Cursor Cost Analysis
Cursor FundamentalsCursor LocationCursor ConcurrencyCursor TypesCursor Cost ComparisonCost Comparison on Cursor LocationCost Comparison on Cursor ConcurrencyCost Comparison on Cursor TypeDefault Result SetBenefitsDrawbacksAnalyzing SQL Server Overhead with CursorsAnalyzing SQL Server Overhead with T-SQL CursorsCursor RecommendationsSummary
CHAPTER 15: Database Workload Optimization
Workload Optimization FundamentalsWorkload Optimization StepsSample WorkloadCapturing the WorkloadAnalyzing the WorkloadIdentifying the Costliest QueryDetermining the Baseline Resource Use of the Costliest QueryOverall Resource UseDetailed Resource UseAnalyzing and Optimizing External FactorsAnalyzing the Batch-Level Options Used by the ApplicationAnalyzing the Effectiveness of StatisticsAnalyzing the Need for DefragmentationAnalyzing the Internal Behavior of the Costliest QueryAnalyzing the Query Execution PlanIdentifying the Costly Steps in the Execution PlanAnalyzing the Effectiveness of the Processing StrategyOptimizing the Costliest QueryModifying an Existing IndexAnalyzing the Application of a Join HintAvoiding the Clustered Index Scan OperationModifying the ProcedureAnalyzing the Effect on Database WorkloadIterating Through Optimization PhasesSummary
CHAPTER 16: SQL Server Optimization Checklist
Database DesignBalancing Under- and OvernormalizationBenefiting from Entity-Integrity ConstraintsBenefiting from Domain and Referential Integrity ConstraintsAdopting Index-Design Best PracticesAvoiding the Use of the sp_ Prefix for Stored Procedure NamesMinimizing the Use of TriggersQuery DesignUse the Command SET NOCOUNT ONExplicitly Define the Owner of an ObjectAvoid Nonsargable Search ConditionsAvoid Arithmetic Operators on the WHERE Clause ColumnAvoid Optimizer HintsStay Away from Nesting ViewsEnsure No Implicit Data Type ConversionsMinimize Logging OverheadAdopt Best Practices for Reusing Execution PlansAdopt Best Practices for Database TransactionsEliminate or Reduce the Overhead of Database CursorsConfiguration SettingsAffinity MaskMemory Configuration OptionsCost Threshold for ParallelismMax Degree of ParallelismOptimize for Ad Hoc WorkloadsQuery Governor Cost LimitFill Factor (%)Blocked Process ThresholdDatabase File LayoutDatabase CompressionDatabase AdministrationKeep the Statistics Up-to-DateMaintain a Minimum Amount of Index DefragmentationCycle the SQL Error Log FileAvoid Automatic Database Functions Such As AUTO_CLOSE or AUTO_SHRINKMinimize the Overhead of SQL TracingDatabase BackupIncremental and Transaction Log Backup FrequencyBackup DistributionBackup CompressionSummary
Index

Overview

SQL Server 2008 Query Performance Tuning Distilled presents a direct trouble–shooting methodology for identifying poorly-performing stored procedures and queries, isolating the causes of that poor performance, and fixing the underlying problems. Each chapter is dedicated to one of the top causes of poorly performing queries and shows methods for identifying and dealing with the problems in that chapter's domain. Emphasis is always put upon or placed upon practical methods that you can put to immediate use in your day-to-day work. SQL Server 2008 functionality, tips, and tricks are emphasized in each subject area.

Emphasizes the practical. Does not bury readers in theory.
Gives readers practical techniques to immediately apply in their daily work.
Dedicates a chapter to each of the most common, performance-related problem areas.

Become an O’Reilly member and get unlimited access to this title plus top books and audiobooks from O’Reilly and nearly 200 top publishers, thousands of courses curated by job role, 150+ live events each month,
and much more.

Read now

Unlock full access

More than 5,000 organizations count on O’Reilly

O’Reilly covers everything we've got, with content to help us build a world-class technology community, upgrade the capabilities and competencies of our teams, and improve overall team performance as well as their engagement.

Julian F.

Head of Cybersecurity

I wanted to learn C and C++, but it didn't click for me until I picked up an O'Reilly book. When I went on the O’Reilly platform, I was astonished to find all the books there, plus live events and sandboxes so you could play around with the technology.

Addison B.

Field Engineer

I’ve been on the O’Reilly platform for more than eight years. I use a couple of learning platforms, but I'm on O'Reilly more than anybody else. When you're there, you start learning. I'm never disappointed.

Amir M.

Data Platform Tech Lead

I'm always learning. So when I got on to O'Reilly, I was like a kid in a candy store. There are playlists. There are answers. There's on-demand training. It's worth its weight in gold, in terms of what it allows me to do.

Mark W.

Embedded Software Engineer

Inside Microsoft® SQL Server® 2008: T-SQL Querying

Publisher Resources

ISBN: 9781430219026Purchase book

Cloud Computing

Data Engineering

Data Science

AI & ML

Programming Languages

Software Architecture

IT/Ops

Security

Design

Business

Soft Skills