YouTube
GitHub

Indexing

  1. Home
  3. Indexing
    4. (Page 4)
Brent Ozar reading

New System Stored Procedures in SQL Server 2017

Brent Ozar
Columnstore Indexes, SQL Server 2017
No Comments
Got a few new stored procs for columnstore indexes: sp_add_columnstore_column_dictionary – input params for @table_id, @column_id sp_is_columnstore_column_dictionary_enabled – as above, plus output param for @is_enabled bit sp_remove_columnstore_column_dictionary – input params for @table_id, @column_id And a couple for snapshot views (which appear to be related to the new snapshot messages in sys.messages) sp_refresh_single_snapshot_view – input param for @view_name…
Read More

Key Lookups and ColumnStore Indexes

Erik Darling
Columnstore Indexes, Execution Plans, Indexing, SQL Server
9 Comments
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

Optional Parameters and Missing Index Requests

Erik Darling
Execution Plans, Indexing, SQL Server
, , ,
9 Comments
That’s when it all gets blown away At one point or another in everyone’s SQL-querying career, they end up writing a query that goes something like this: Transact-SQL SELECT something FROM stuff WHERE (@thing1 is NULL or whatever = @thing1) AND ... 1234 SELECT somethingFROM stuffWHERE (@thing1 is NULL or whatever = @thing1)AND ... These…
Read More

How Much Can One Column Change A Query Plan? Part 2

Erik Darling
Execution Plans, Indexing, SQL Server
, , ,
3 Comments
What happened in Part 1? Join Elimination, naturally. Until the end. My copy of the Stack Overflow database doesn’t have a single foreign key in it, anywhere. If we go down the rabbit hole a couple steps, we end up at a very quotable place, with Rob Farley. 2. Duplicated rows Not necessarily duplicated completely,…
Read More

ColumnStore Indexes: Rowgroup Elimination and Parameter Sniffing In Stored Procedures

Erik Darling
Columnstore Indexes, Development, Execution Plans, Indexing, SQL Server, SQL Server 2017
5 Comments
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

Brent Ozar
Bad Idea Jeans, Indexing
23 Comments
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

Five Mistakes Performance Tuners Make

Erik Darling
Development, Execution Plans, Indexing, SQL Server
, , ,
9 Comments
There’s no Top in the title And that’s because a TOP without an ORDER BY is non-deterministic, and you’ll get yelled at on the internet for doing that. This is just a short collection of things that I’ve done in the past, and still find people doing today when troubleshooting performance. Sure, this list could…
Read More

Locking When There’s Nothing To Lock

Erik Darling
Development, Execution Plans, Indexing, Locking, Blocking, and Isolation Levels
, , ,
7 Comments
Demo Day We use StackOverflow for demos a lot. For all the reasons Brent mentions in his Great Post, Brent©, it’s pretty awesome. Where things get tricky is with locking demos. Sometimes the modifications can take a long time. This may be by design if you need to show long-held locks by sessions that aren’t…
Read More

Why Missing Index Recommendations Aren’t Perfect

Brent Ozar
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

ColumnStore Indexes And Recursive CTEs

Erik Darling
Columnstore Indexes, Indexing, SQL Server
, , ,
7 Comments
Alone Together When I think about SQL Server features, I often picture a high school cafeteria. AGs are making fun of Mirroring, index rebuilds are walking around taking everyone’s lunch, dta is making a glue sandwich, and no one knows who Service Broker is even though they’ve been going to school together since Kindergarten. At…
Read More

Will SQL Server Always Do Index Seeks on Numbers?

Brent Ozar
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

Brent Ozar
Configuration Settings, Indexing
11 Comments
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
Menu
{"cart_token":"","hash":"","cart_data":""}