Links and Notes from SQL Server Index Formulas, Huntington Beach

on April 5, 2016

I gave a day long session, “SQL Server Index Formulas, Problems and Solutions” in Huntington Beach, CA on April 1. The class was a great group of students, and we had a lively discussion and lots of questions.

Here’s a topic we diagrammed in class, as well as links to extra resources.

Index-Formulas-Problems-Solutions-SQL-Saturday-Huntington-Beach

What are the costs/overhead of each nonclustered B tree index? (List made by the class)

  • Inserts, updates, deletes - CPU and IO to keep each index current
  • Space used by the index on disk
  • Space used by the index in memory
  • Overhead of statistics on the index - this need to be maintained by the SQL Server as data changes
  • Index maintenance - resources to check indexes for fragmentation and perform maintenance. Instance performance is impacted while this is running
  • Space in backups / time the backups take to complete
  • Resources required to check for corruption (DBCC CHECKDB)

SQL Server Developer Edition is now free for SQL Server 2014 (and 2016 when it releases)

SQL Server Functions

Database Compatibility Reference

How to change LOB column storage options with sp_tableoptions

Limitations on what you can do in indexed views

Finding forced plans in Query Store

Five things about Fillfactor

Table Pattern: Rotating Log Buffer

Publishing indexed views in transactional replication

Transferring Data Efficiently Using Partition Switching

Join me for this Session in Liverpool, UK!

I will be giving this session at the SQLBits conference on May 4, 2016.