Microsoft® SQL Server® 2012 Bible

1007 Chapter 45: Indexing Strategies 45 B-tree indexes exist on index pages and have a root level, one or more intermediate leve ...

1008 Part VII: Performance Tuning and Optimization is the table. All the data for the table is stored in the leaf pages of the c ...

1009 Chapter 45: Indexing Strategies 45 Nonclustered Indexes SQL Server nonclustered indexes are also implemented as a B-tree da ...

1010 Part VII: Performance Tuning and Optimization Since SQL Server 2005, additional unsorted columns can be included in the lea ...

1011 Chapter 45: Indexing Strategies 45 example, if several new Chapmans moved into the area and the Cha page 515 had to now acc ...

1012 Part VII: Performance Tuning and Optimization FIGURE 45-3 Use the output from DBCC SHOW_STATISTICS to determine the last ti ...

1013 Chapter 45: Indexing Strategies 45 clustered key if the base table is clustered. The clustered keys are used in the nonclus ...

1014 Part VII: Performance Tuning and Optimization The query optimizer chooses the access method with the least overall cost. Se ...

1015 Chapter 45: Indexing Strategies 45 Performance data for each path, listed in Table 45-1, was captured by watching the T-SQL ...

1016 Part VII: Performance Tuning and Optimization Path No Path Desc Execution Plan Rows Cost Reads Missing Index Duration (ms) ...

1017 Chapter 45: Indexing Strategies 45 Without a where clause and every column selected, the query must read every row from the ...

1018 Part VII: Performance Tuning and Optimization FIGURE 45-6 A clustered index seek navigates the B-tree index and locates the ...

1019 Chapter 45: Indexing Strategies 45 FIGURE 45-7 The clustered index seek’s predicate has a start and an end, which indicates ...

1020 Part VII: Performance Tuning and Optimization At fi rst glance it would seem that this query should generate the same query ...

1021 Chapter 45: Indexing Strategies 45 There’s no index with a key column of StartDate. This means that the query optimizer can ...

1022 Part VII: Performance Tuning and Optimization Query Path 5 — Bookmark Lookup This bookmark lookup query path is a two-edged ...

1023 Chapter 45: Indexing Strategies 45 FIGURE 45-10 The nonclustered index does not contain one or more columns that the query ...

1024 Part VII: Performance Tuning and Optimization It’s a common saying that Select * is a poor practice because it returns too ...

1025 Chapter 45: Indexing Strategies 45 Query Path 5’s second query selected the StartDate column. Because StartDate isn’t part ...

1026 Part VII: Performance Tuning and Optimization FIGURE 45-12 With the StartDate column included in the index, the queries are ...