SQL Index Archives - Page 35 of 62 - SQL Authority with Pinal Dave

An oyster knife opens one shell beside a row of shells whose meat still needs separate access

Plan Cache Key Lookups: Finding the Worst Ones With XQuery

March 24, 2012

Execution Plan, SQL Cache, SQL Index, SQL XML

Not every key lookup needs fixing. Rank statements by reads, find the lookups with XQuery, then test the smallest index change on one call.

SQL Server – Learning SQL Server Performance: Indexing Basics – Video

March 8, 2012

Pinal Dave

SQL Video

SQL Index

Indexes are the first step in any performance tuning exercise. Our new video course on indexing basics covers creating, analyzing and maintaining indexes.

A long glass greenhouse divided into sections, with only its red-framed middle door lit from inside.

Table Partitioning Quiz: When Does It Make a Query Faster?

January 31, 2012

Pinal Dave

SQL Puzzle

Execution Plan, SQL Index, SQL Performance, Table Partitioning

This Table Partitioning Quiz asks whether a query for last month runs faster once a sales table is partitioned by month. The answer is only when the filter lets SQL Server skip partitions. A tested script shows the partition count, the reads, and a partition SWITCH.

A large open moving box on a wooden floor, only partly filled with folded linens, a red roll of tape beside it.

Reclaiming Space Quiz: Does Deleting Rows Shrink the File?

January 29, 2012

Pinal Dave

SQL Puzzle

Shrinking Database, SQL Data Storage, SQL Index, SQL Performance

This Reclaiming Space Quiz asks what happens to the data file after you delete half the rows of a big table. The file keeps its size, and the space stays free inside it. A tested script shows the numbers, then what a shrink costs.

Two wooden bookshelves side by side, filled with plain books, a red book at the end of one shelf.

Fill Factor Quiz: Is 0 Different from 100?

January 25, 2012

Pinal Dave

SQL Puzzle

Clustered Index, SQL Data Storage, SQL Index, SQL Performance

This Fill Factor Quiz asks whether an index with fill factor 0 leaves more free space than one with 100. It does not: both build full pages. A tested script shows how a fill factor of 70 changes the pages and why it only applies at build time.

A half-knitted cream scarf resting on an armchair with its needles, a red ball of yarn beside it ready to continue.

Rebuild Index Quiz: DROP and CREATE, DROP_EXISTING or REBUILD?

January 22, 2012

Pinal Dave

SQL Puzzle

Maintenance Plan, SQL Index, SQL Performance, SQL Table Operation

This Rebuild Index Quiz asks which way of refreshing an index stays usable and can pause and resume. The answer is ALTER INDEX with ONLINE and RESUMABLE turned on. A tested two-window script shows the pause and the resume.

A tall wooden bookcase standing against a wall, with a red sanding block on the floor in front of it.

Nonclustered Index Rebuild Quiz: Does a Clustered Rebuild Touch Them?

January 12, 2012

Pinal Dave

SQL Puzzle

Clustered Index, SQL Heap, SQL Index, SQL Statistics

This Nonclustered Index Rebuild Quiz asks what happens to the other indexes when you rebuild the clustered index. The answer is nothing, and a tested script shows it. It also covers the cases where SQL Server does rebuild them.