SQL Server 2016 and Azure SQL Database have a lot of little perks. One little thing that changed is that the maximum key size allowed for nonclustered indexes was raised from 900 bytes to 1700 bytes.
I was looking through some terms in SQL Server documentation the other day, thinking about what it’s like to learn about SQL Server’s indexes when you’re new to the field. I jotted down a note: B-tree = Rowstore = Disk Based.
And then I realized that’s not quite right.
SQL Server’s “index usage stats” dynamic management view is incredibly useful– but does it tell you what you THINK it tells you?
I explain the quirks of how sys.dm_db_index_usage_stats works and why the information is so valuable.
Recently I got a fun question about an “upsert” pattern as a “Dear SQL DBA” question. The question is about TSQL, so it lent itself to being answered in a blog post where I can show repro code and screenshots.
Microsoft has just released a new round of cumulative updates for SQL Server 2012, and the release notes indicate that a fix to the sys.dm_db_index_usage_stats dynamic management view has been included in these updates. This is good news for index tuners using SQL Server 2012.
As of SP2 + CU12 and SP3 + CU3, SQL Server 2012 will persist index usage information even when ALTER INDEX REBUILD is run.
Recently, I was thinking about nonclustered indexes in SQL Server, and how included columns are stored.
Is SQL Server smart enough to optimize the storage for small indexes with includes? Find out in this free seven minute video.
Sometimes you need to script out all the indexes in a database. Maybe you’re concerned something has changed since they were last checked in. Or maybe the indexes aren’t checked into source control, and you’re working on fixing that. (Important!)
Either way, sometimes you need to do it, and it’s not fun through the GUI. I needed to write some fresh demo code for this recently, and I needed it to give the details for partitioned tables using data compression, and I thought I’d share.
Copyright (c) 2025, Catalyze SQL, LLC; all rights reserved. Opinions expressed on this site are solely those of Kendra Little of Catalyze SQL, LLC. Content policy: Short excerpts of blog posts (3 sentences) may be republished, but longer excerpts and artwork cannot be shared without explicit permission.