Last Minute Coupon Code for 2016 SQLPASS Summit
Category:
indexing
I can’t wait to teach my full day course, SQL Server Index Formulas: Problems and Solutions in Seattle at the PASS Summit on Tuesday, October 25th. Woot! Less than 3 weeks away!
Category: indexing
Do Clustered Index Columns Count Against Max Nonclustered Key Size?
Category:
indexing
, sql-server
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.
Which Indexes are Disk Based in SQL Server?
Category:
indexing
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.
Index Usage Stats Insanity - the oddities of sys.dm db index usage stats (Dear SQL DBA)
Category:
indexing
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.
The Case of the Blocking Merge Statement (LCK_M_RS_U locks)
Category:
indexing
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.
Index Usage Stats Bug Fixed in SQL Server 2012 SP2 CU12 / SP3 CU3
Category:
indexing
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.
Indexes: Do Included Columns Always Go on a Non-Root Page? (video)
Category:
indexing
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.