Links and Notes from SQL Server Index Formulas, Huntington Beach

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.

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)

• https://blogs.technet.microsoft.com/dataplatforminsider/2016/03/31/microsoft-sql-server-developer-edition-is-now-free/
• Membership in Visual Studio Dev Essentials (free) is required

SQL Server Functions

• https://www.simple-talk.com/sql/t-sql-programming/sql-server-functions-the-basics/
• Long article, there is a section on “Avoiding Row by Row Behavior with TVFs”

Database Compatibility Reference

• https://msdn.microsoft.com/en-us/library/bb510680.aspx
• Includes details on levels 120 and 130 (cardinality estimator)

How to change LOB column storage options with sp_tableoptions

• https://msdn.microsoft.com/en-us/library/ms173530.aspx

Limitations on what you can do in indexed views

• https://msdn.microsoft.com/en-us/library/ms191432.aspx

Finding forced plans in Query Store

• /2016/03/25/no_plan-and-no_index-breaking-a-forced-query-store-plan/

Five things about Fillfactor

• https://www.brentozar.com/archive/2013/04/five-things-about-fillfactor/

Table Pattern: Rotating Log Buffer

• http://kejser.org/table-pattern-rotating-log-ring-buffer/

Publishing indexed views in transactional replication

• https://msdn.microsoft.com/en-us/library/ms152559.aspx

Transferring Data Efficiently Using Partition Switching

• https://technet.microsoft.com/en-us/library/ms191160.aspx
• This contains rules related to foreign keys on source and target tables

Join me for this Session in Liverpool, UK!

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