Links and Notes from “SQL Server Index Formulas”, Huntington Beach

Index-Formulas-Problems-Solutions-SQL-Saturday-Huntington-BeachI 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

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)

Links we discussed in class

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. Learn more about the course here.

Previous Post
Can I Force A Hinted Plan with Query Store in SQL Server 2016?
Next Post
New SSMS Features: Click + Drag and Click + Mouse Scroll

Related Posts

2 Comments. Leave new

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Menu