Indexing

  1. Home
  2. Indexing
  3. (Page 6)

Key Lookups and ColumnStore Indexes

Beavis v. Butthead I was a bit surprised that this was a possibility with ColumnStore indexes, since “keys” aren’t really their strong point, but since we’re now able to have both clustered ColumnStore indexes alongside row store nonclustered indexes AND nonclustered ColumnStore indexes on tables with row store clustered indexes, this kind of stuff should…
Read More

ColumnStore Indexes: Rowgroup Elimination and Parameter Sniffing In Stored Procedures

Yazoo Over on his blog, fellow Query Plan aficionado Joe Obbish has a Great Post, Brent® about query patterns that qualify for Rowgroup Elimination. This is really important to performance! It allows scans to skip over stuff it doesn’t need, like skipping over the dialog in, uh… movies with really good fight scenes. Car chases?…
Read More

How to Drop All Your Indexes – Fast

Sometimes I need to reset stuff during performance training classes. I know some of you teach classes, too, and some of you just like doing crazy stuff. So here you go, a stored procedure to lose weight fast: DropIndexes for SQL Server 2016 & Newer Transact-SQL CREATE OR ALTER PROCEDURE dbo.DropIndexes @SchemaName NVARCHAR(255) = 'dbo',…
Read More

Why Missing Index Recommendations Aren’t Perfect

Indexing
6 Comments
Using the good ol’ Stack Overflow public database, get the execution plan for this query – either estimated or actual, doesn’t matter: Transact-SQL SELECT Id FROM dbo.Users WHERE LastAccessDate = '2016/11/10'; 123 SELECT IdFROM dbo.UsersWHERE LastAccessDate = '2016/11/10'; In the execution plan, SQL Server asks for a missing index on the LastAccessDate field: Missing index…
Read More

Will SQL Server Always Do Index Seeks on Numbers?

Indexing
3 Comments
I often get the question of “Will SQL Server do an index seek with ____?” Another way to phrase it is, “Is this query sargable?” We throw the term “sargable” around to mean that SQL Server can do an index seek to satisfy your search arguments. (We’re specifically talking about index seeks here, not scans – not…
Read More

Checking for Strange Client Settings with sys.dm_exec_sessions

In my performance training class, people get really excited when I cover filtered indexes, computed columns, and indexed views. Then they get a really strange expression on their face when I talk about one of the biggest drawbacks: if your connection options aren’t set correctly, your delete/update/insert (DUI) operations will fail. That gave me a really…
Read More

Indexed Views And Data Modifications

Indexing
5 Comments
Love/Hate When indexed views help, they make aggregation queries super fast. There are some kind of horrible limitations and requirements. When they hurt, your modifications slow to a crawl because SQL has to not only keep your horrible indexes synchronized, it also has to keep your indexed view indexes in sync now, too. But SQL…
Read More

Indexes For Kids

Indexing, SQL Server
18 Comments
Kid Tested When you work with something a lot, you tend to extrapolate concepts from it out to everything you see. That one person collecting tickets is a serial zone. That food truck line is a HEAP. The empty subway car that smells like a toilet is Replication. Stuff like that. If you have one…
Read More
Menu