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.
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
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
- 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.