Microsoft® SQL Server® 2012 Bible

1027 Chapter 45: Indexing Strategies 45 FIGURE 45-13 The index seek operator can have a seek predicate, which uses the B-tree; a ...

1028 Part VII: Performance Tuning and Optimization The following code redefi nes the indexes: one index keyed on ProductID and o ...

1029 Chapter 45: Indexing Strategies 45 For infrequent queries, Query Path 7, with its multiple indexes, is more than adequate a ...

1030 Part VII: Performance Tuning and Optimization Query Path 9 — Filter by Unordered Composite Index One common indexing myth i ...

1031 Chapter 45: Indexing Strategies 45 Query Path 10 — Non-SARG-Able Expressions SQL Server’s Query Optimizer examines the cond ...

1032 Part VII: Performance Tuning and Optimization ■ (^) If the predicate includes a function, such as a string function, a scan ...

1033 Chapter 45: Indexing Strategies 45 Generate a trace script using File ➪ Export ➪ Script Trace Defi nition ➪ for SQL Server ...

1034 Part VII: Performance Tuning and Optimization Creating Base Indexes Even before tuning, the locations of a few key indexes ...

1035 Chapter 45: Indexing Strategies 45 Specialty Indexes Beyond the standard clustered and nonclustered indexes, SQL Server off ...

1036 Part VII: Performance Tuning and Optimization Another situation that might benefi t from fi ltered indexes is building a un ...

1037 Chapter 45: Indexing Strategies 45 SET ANSI_Padding ON; SET ANSI_Warnings ON; SET ArithAbort ON; SET Concat_Null_Yields_Nul ...

1038 Part VII: Performance Tuning and Optimization FROM Production.WorkOrder W JOIN Production.Product P ON P.ProductID = W.Prod ...

1039 Chapter 45: Indexing Strategies 45 Summary To intelligently create indexes you need to thoroughly understand not only the t ...

c45.indd 1040c45.indd 1040 7/31/2012 10:16:45 AM7/31/2012 10:16:45 AM http://www.it-ebooks.info ...

1041 CHAPTER 46 Maximizing Query Plan Reuse IN THIS CHAPTER Compiling SQL Using the Query Plan Cache T he SQL language is a decl ...

1042 Part VIII: Performance Tuning and Optimization First, the Parser dissects the T-SQL code, ensures that it is valid code, an ...

1043 Chapter 46: Maximizing Query Plan Reuse 46 Viewing the Plan Cache Dynamic Management Views (DMVs) expose the contents of th ...

1044 Part VIII: Performance Tuning and Optimization The DMV sys.dm_exec_query_stats provides a statement-level view of cached SQ ...

1045 Chapter 46: Maximizing Query Plan Reuse 46 Single statement query recompiles simply cause the query to recompile the next t ...

1046 Part VIII: Performance Tuning and Optimization Summary The Query Optimizer is the technology that analyzes a query, the ind ...