book

SQL Server 2025 Query Performance Tuning: Troubleshoot and Optimize Query Performance

Name: SQL Server 2025 Query Performance Tuning: Troubleshoot and Optimize Query Performance
Author: Grant Fritchey
ISBN: 9798868818653

by Grant Fritchey

January 2026

Intermediate to advanced

768 pages

15h 16m

English

Apress

Read now

Unlock full access

SQL Server 2025 Query Performance Tuning
Introduction
Acknowledgments
Table of Contents
About the Author
About the Technical Reviewer
1. Query Performance Tuning
The Query Performance Tuning ProcessPerformance IssuesA Repetitive ProcessUnderstanding What Defines “Good Enough”Establishing Comparison PointsMost Likely Performance IssuesCommon Performance IssuesInsufficient or Poor IndexesInaccurate or Missing StatisticsBad T-SQLProblematic Execution PlansExcessive BlockingDeadlocksNon–Set-Based OperationsIncorrect Database DesignPoor Execution Plan ReuseFrequent Recompilation of QueriesSummary
2. Execution Plan Generation and the Query Optimizer
The Query Optimization ProcessOptimization PreparationParsingBindingOptimizationSimplificationTrivial Plan MatchOptimization PhasesGenerating Parallel Execution PlansExecution Plan CachingAging of the Execution PlanSummary
3. Methods for Capturing Query Performance Metrics
Methods to Capture Query Performance MetricsInclude Client StatisticsConnection PropertiesSET STATISTICS TIME/IOQueryTimeStats in the Execution PlanTrace Events (Profiler)Dynamic Management ViewsActively Executing QueriesPreviously Executed QueriesQuery StoreExtended EventsCreating an Extended Events SessionAdding and Configuring EventsAdding Global Fields to EventsUsing Predicates with EventsOptional Event FieldsDefining TargetsUsing the event_file TargetUsing the histogram TargetWorking with SessionsAdding Causality TrackingScripting Extended EventsLive Data Explorer WindowFiltering Live DataAggregating Live DataGeneral Recommendations for Using Extended EventsSet Max File Size AppropriatelyAvoid Debug EventsAvoid the Use of No_Event_LossSummary
4. Analyzing Query Behavior Using Execution Plans
Estimated vs. Actual Execution PlansCapturing Execution PlansSQL Server Management StudioDynamic Management ViewsQuery StoreExtended EventsWhat Is Inside an Execution PlanReading an Execution PlanWhat Do You Look for in an Execution Plan?First OperatorWarningsMost Costly OperationsFat PipesExtra OperatorsScansEstimate vs. ActualAfter the SignpostsTools That Assist You with Execution PlansSQL Server Management StudioFind NodeCompare PlansLive Query StatisticsThird PartySolarWinds Plan ExplorerSupratimasPaste The PlanAISummary

5. Statistics, Data Distribution, and Cardinality
Statistics in the Query Optimization ProcessStatistics on Rowstore Indexed ColumnsBenefits of Updated StatisticsDrawbacks of Outdated StatisticsStatistics on Nonindexed ColumnsBenefits of Statistics on a Nonindexed ColumnComparing Performance with Missing StatisticsAnalyzing StatisticsHeaderDensityHistogramCardinalityStatistics on a Multicolumn IndexStatistics on a Filtered IndexControlling the Cardinality EstimatorStatistics MaintenanceAuto Create StatisticsAuto Update StatisticsAuto Update Statistics AsynchronouslyManual MaintenanceManage Statistics SettingsCreate Statistics ManuallyAnalyzing the Effectiveness of Statistics for a QueryResolving a Missing Statistics IssueResolving an Outdated Statistics IssueRecommendations on StatisticsBackward Compatibility of StatisticsAuto Create StatisticsAuto Update StatisticsAuto Update Statistics AsynchronouslyAmount of Sampling to Collect StatisticsSummary
6. Using the Query Store for Query Performance and Execution Plans
Query Store Function and DesignInformation Collected by the Query StoreQuery Runtime DataControlling the Query StoreCapture ModeQuery Store ReportingPlan ForcingForcing Query HintsOptimized Plan ForcingQuery Store for UpgradesSummary
7. Execution Plan Cache Behavior
Querying the Plan CacheExecution Plan Caching and Plan ReuseAd Hoc WorkloadOptimize for Ad Hoc WorkloadsSimple ParameterizationForced ParameterizationPrepared WorkloadStored ProcedureWhen Stored Procedures Are CompiledPerformance Benefits of Stored ProceduresNonperformance Benefits of Stored Proceduressp_executesqlPrepare/Execute ModelQuery Hash and Query Plan HashExecution Plan Cache RecommendationsExplicitly Parameterize Values in Your QueryUse Stored Procedures Where You CanUse sp_executesql As an Alternative to Stored ProceduresTake Advantage of the Prepare/Execute ModelAvoid Ad Hoc QueriesEnable Optimize for Ad HocSummary
8. Query Recompilation
Benefits and Drawbacks of RecompilationIdentifying the Statement Being RecompiledAnalyzing Causes of RecompilationDeferred Object ResolutionRecompilation on a TableRecompilation on a Temporary TableAvoiding RecompilesAvoid Interleaving DDL and DML StatementsReduce Recompilation Caused by Statistics ChangesUse the KEEPFIXED PLAN HintDisable Automatic Statistics Maintenance on a TableUse Table VariablesUse Temporary Tables Across Multiple ScopesAvoid Changing SET Options Within a BatchControlling Recompile ResultsPlan ForcingQuery HintsPlan GuidesHint ForcingSummary
9. Index Architecture
What Is a Rowstore Index?The Benefits of IndexesIndex OverheadWhat Is a Columnstore Index?Columnstore Index StorageIndex-Design RecommendationsType of Query Processing Being PerformedDetermine Filtering CriteriaUse Narrow IndexesConsider Selectivity of the DataDetermine the Data TypeConsider Column OrderDetermine Data StorageAsk an AIRowstore Index BehaviorClustered IndexesHeap TablesRelationships with Nonclustered IndexesClustered Index RecommendationsCreate the Clustered Index FirstKeep Clustered Indexes NarrowRebuild the Clustered Index in a Single StepWhere Possible, Make the Clustered Index UniqueWhen to Use a Clustered IndexAccessing the Data DirectlyRetrieving Pre-sorted DataPoor Design Practices for a Clustered IndexFrequently Updated ColumnsWide KeysNonclustered IndexesNonclustered Index MaintenanceDefining the Lookup OperationNonclustered Index RecommendationsWhen to Use a Nonclustered IndexWhen Not to Use a Nonclustered IndexColumnstore Index BehaviorColumnstore RecommendationsMissing IndexesSummary
10. Index Behaviors
Covering IndexesA Pseudoclustered IndexRecommendationsIndex IntersectionIndex JoinsFiltered IndexesIndexed ViewsBenefitOverheadUsage ScenariosIndex CompressionIndex CharacteristicsDifferent Column Sort OrderIndex on Computed ColumnsCREATE INDEX Statement Processed As a QueryParallel Index CreationOnline Index CreationConsidering the Database Engine Tuning AdvisorOPTIMIZE_FOR_SEQUENTIAL_KEYResumable Indexes and ConstraintsSpecial Index TypesFull-TextSpatialXMLVectorSummary
11. Key Lookups and Solutions
Purpose of LookupsPerformance Issues Caused by LookupsAnalysis of the Causes of LookupsTechniques to Resolve LookupsCreate a Clustered IndexUse a Covering IndexTake Advantage of Index JoinsSummary
12. Dealing with Index Fragmentation
Causes of Rowstore FragmentationHow Fragmentation Occurs in Rowstore IndexesPage Split from an UPDATE StatementPage Split by an INSERT StatementHow Fragmentation Occurs in Columnstore IndexesFragmentation OverheadRowstore OverheadColumnstore OverheadAnalyzing the Amount of FragmentationAnalyzing the Fragmentation of a Small TableFragmentation ResolutionsDrop and Recreate the IndexRecreating the Index with the DROP_EXISTING ClauseExecute the ALTER INDEX REBUILD CommandExecute the ALTER INDEX REORGANIZE CommandDefragmentation and PartitionsSignificance of the Fill FactorAutomatic MaintenanceSummary
13. Parameter-Sensitive Queries: Causes and Solutions
How Does Parameter Sniffing Work?Identifying Queries That Are Sensitive to Parameter ValuesMechanisms for Addressing Plan-Sensitive QueriesDisable Parameter SniffingLocal VariablesRecompileOPTIMIZE FOR Query HintForce PlanMultiplanSummary
14. Query Design Analysis
Query Design RecommendationsKeep Your Result Sets SmallLimit the Columns in Your SELECT ListFilter Your Data Through a WHERE ClauseUse Indexes EffectivelyUse Effective Search ConditionsBETWEEN vs. IN/ORLIKE Condition!< Condition vs. >= ConditionAvoid Operations on ColumnsCustom Scalar UDFMinimize Optimizer HintsJOIN HintINDEX HintsUsing Domain and Referential IntegrityNOT NULL ConstraintUser-Defined ConstraintsDeclarative Referential IntegritySummary
15. Reduce Query Resource Use
Avoiding Resource-Intensive QueriesUse Appropriate Data TypesTest EXISTS Over COUNT(*) to Verify Data ExistenceFavor UNION ALL Over UNIONEnsure Indexes Are Used for Aggregate and Sort OperationsBe Cautious with Local Variables in a Batch QueryStored Procedure Names Actually MatterReducing Network Overhead Where PossibleExecute Multiple Queries in SetsUse SET NOCOUNTTechniques to Reduce Transaction Cost of a QueryReduce Logging OverheadReduce Lock OverheadMark the Database As READ_ONLYUse Snapshot IsolationPrevent SELECT Statements from Requesting a LockSummary
16. Blocking and Blocked Processes
Blocking FundamentalsA Short Discussion of TerminologyIntroducing BlockingTransactions and ACID PropertiesAtomicitySET XACT_ABORT ONExplicit RollbackConsistencyIsolationDurabilityLock TypesRow LocksKey LocksPage LocksExtent LocksHeap or B-Tree LocksRowgroup LocksTable LocksTransaction LocksDatabase LocksLock Operations and ModesLock EscalationLock ModesShared (S) ModeUpdate (U) ModeExclusive (X) ModeIntent Shared (IS), Intent Exclusive (IX), and Shared with Intent Exclusive (SIX) ModesSchema Modification (Sch-M) and Schema Stability (Sch-S) ModesBulk Update (BU) ModeKey-Range ModeLock CompatibilityIsolation LevelsRead UncommittedRead CommittedRepeatable ReadSerializableSnapshotOptimized LockingTransactionID LockingLock After QualificationEffect of Indexes on LockingEffect of a Nonclustered IndexEffects of a Clustered IndexCapturing Blocking InformationCapturing Blocking Information Using T-SQLExtended Events and the blocked_process_report EventRecommendations to Reduce BlockingSummary
17. Causes and Solutions for Deadlocks
Deadlock FundamentalsChoosing the Deadlock VictimAnalyzing the Causes of DeadlocksCapturing Deadlock InformationTrace FlagExtended EventsAnalyzing the Deadlock GraphError Handling for DeadlocksMechanisms to Prevent DeadlocksAccess Resources in the Same OrderDecrease the Amount of Resources AccessedConvert a Nonclustered Index to a Clustered IndexUse a Covering IndexMinimize Lock ContentionImplement Row VersioningDecrease the Isolation LevelEnable Optimized LockingUse Locking HintsTune the QueriesSummary
18. Row-by-Row Processing from Cursors and Other Causes
Cursor FundamentalsCursor LocationClient-Side CursorsServer-Side CursorsCursor ConcurrencyRead-OnlyOptimisticScroll LocksCursor TypesForward-Only CursorsStatic CursorsKeyset-Driven CursorsDynamic CursorsWHILE LoopCursor Cost ComparisonCost Comparison Based on LocationClient-Side CursorsServer-Side CursorsCost Comparison Based on ConcurrencyRead-OnlyOptimisticScroll LocksCost Comparison Based on Cursor TypeForward-Only CursorsFast-Forward-Only CursorStatic CursorsKeyset-Driven CursorsDynamic CursorDefault Result SetBenefitsMultiple Active Result SetsDrawbacksCursor OverheadCursor RecommendationsSummary
19. Memory-Optimized OLTP Tables and Procedures
In-Memory OLTP FundamentalsSystem RequirementsBasic SetupCreating TablesIn-Memory Table VariablesIn-Memory IndexesHash IndexNonclustered IndexesColumnstore IndexStatistics MaintenanceNatively Compiled Stored ProceduresRecommendationsBaselinesCorrect WorkloadMemory Optimization AdvisorNative Compilation AdvisorSummary
20. Graph Databases
Introduction to Graph DatabasesQuerying Graph DataShortest PathPerformance Considerations of Graph DataSummary
21. Intelligent Query Processing
Adaptive Query ProcessingInterleaved ExecutionQuery Processing FeedbackMemory GrantsCardinality EstimatesDegree of Parallelism (DOP) FeedbackFeedback PersistenceApproximate Query ProcessingAPPROX_COUNT_DISTINCTAPPROX_PERCENTILE_CONT and APPROX_PERCENTILE_DISCTable Variable Deferred CompilationScalar User-Defined Function InliningSummary
22. Automated Tuning in Azure and SQL Server
Automatic Plan CorrectionTuning RecommendationsEnabling Automatic TuningAzure PortalSQL ServerAutomatic Tuning in ActionAzure SQL Database Automatic Index ManagementSummary
23. A Query Tuning Methodology
Database DesignUse Entity-Integrity ConstraintsMaintain Domain and Referential Integrity ConstraintsAdopt Index-Design Best PracticesAvoid the Use of the “sp_” Prefix for Stored Procedure NamesMinimize the Use of TriggersPut Tables into In-Memory StorageUse Columnstore IndexesTake Advantage of Graph StorageUse Appropriate Data TypesConfiguration SettingsMemory Configuration OptionsCost Threshold for ParallelismMax Degree of ParallelismOptimize for Ad Hoc WorkloadsBlocked Process ThresholdDatabase CompressionDatabase AdministrationKeep Statistics Up to DateMaintain a Minimum Amount of Index FragmentationAvoid Database Functions Such As AUTO_CLOSE or AUTO_SHRINKQuery DesignUse the Command SET NOCOUNT ONExplicitly Define the Owner of an ObjectAvoid Non-sargable Search ConditionsAvoid Large IN ClausesAvoid a Large Number of OR ClausesAvoid Arithmetic Expressions on Filter ClausesAvoid Optimizer HintsStay Away from Nesting ViewsEnsure No Implicit Data Type ConversionsMinimize Logging OverheadAdopt Best Practices for Reusing Execution PlansCaching Execution Plans EffectivelyMinimize Recompilation of Execution PlansAdopt Best Practices for Database TransactionsEliminate or Reduce the Overhead of Database CursorsUse Natively Compiled Stored ProceduresTake Advantage of Columnstore for Analytical QueriesEnable the Query StoreSummary
Index

Content preview from SQL Server 2025 Query Performance Tuning: Troubleshoot and Optimize Query Performance

G. FritcheySQL Server 2025 Query Performance Tuninghttps://doi.org/10.1007/979-8-8688-1865-3_12

12. Dealing with Index Fragmentation

Grant Fritchey¹

(1)

Sapulpa, OK, USA

As people work with the data in your databases, they make changes. They may be inserting new information, deleting information they no longer need, or modifying other information or all of the above. As the data changes, your indexes are directly affected, both rowstore and columnstore, albeit in different ways. For your rowstore indexes, the acts of modifying the data can lead to page splits, pages getting rearranged, and pages getting emptied, all summed up in a term called fragmentation ...

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

SQL Server 2022 Query Performance Tuning: Troubleshoot and Optimize Query Performance

Publisher Resources

ISBN: 9798868818653Purchase Link Publisher Website

Cloud Computing

Data Engineering

Data Science

AI & ML

Programming Languages

Software Architecture

IT/Ops

Security

Design

Business

Soft Skills

SQL Server 2025 Query Performance Tuning: Troubleshoot and Optimize Query Performance

by Grant Fritchey

12. Dealing with Index Fragmentation

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.