Indexing Archives - Page 7 of 12 - Brent Ozar Unlimited®

Performance Tuning

Do Disabled Indexes Affect Missing Index Recommendations?

December 21, 2017

I'm so glad you asked! Let's take a look. Open up the Stack Overflow database, turn on actual execution plans, and run a query that will cause SQL Server to beg and plead for an index:
[crayon-6a65210a651cb572384894/]
And we get a missing index recommendation in the plan:

Now create the index, and try the query again:
[crayon-6a65210a651d6735868194/]
Oh, don't act like you haven't done that. And sure enough, SQL Server uses the index:

Rebuilding Indexes Can Slow Queries Down

December 18, 2017

Today, Jonathan Kehayias wrote about how fragmentation impacts execution plans. I'm really into performance tuning, so I read the post carefully, really interested to see how it impacted query runtime. You should read it too - it's a great post with a solid demo.

But oddly, he didn't mention the query speeds.

Performance Tuning

Partition Level Locking: Explanations From Outer Space

November 30, 2017

It's not that I don't like partitioning
It's just that most of my time talking about it is convincing people not to use it.

They always wanna use it for the wrong reasons, and I can sort of understand why.

Good Reasons to Rebuild or Reorganize Indexes

November 28, 2017

No, not that
It's not the F word.

Not like this embarrassing page that was somehow updated in 2017, which still tells you to reorg at 5% and rebuild at 30% fragmentation, like your data is still on a drive that looks like a record player and might still start making a clicking noise when it fails.

New System Stored Procedures in SQL Server 2017

October 2, 2017

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

Live Query Plans and Blocking

September 26, 2017

You'd think this would be cool After all, you've got a query plan being executed LIVE ON NATIONAL TELEVISION! But it's not. It's actually one of those weird reasons why you still need other ways of looking at DMVs for issues. Demo Library Let's take a closer look! First, we need an index. Indexes are…

Performance Tuning

Why Columnstore Indexes May Still Do Key Lookups

September 22, 2017

I was a bit surprised that key lookups were 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 get a closer look.

Performance Tuning

Optional Parameters and Missing Index Requests

September 14, 2017

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:
[crayon-6a65210a71658311361710/]
These are often called optional parameters, and if you spend any time looking at queries, this will make you shudder for many reasons. Poor cardinality estimates, full scans, etc.

Performance Tuning

Indexing for Windowing Functions: WHERE vs. OVER

September 13, 2017

Life Is Messy
Demo queries have this nasty habit of being clean. Even using a pit of despair like Adventure Works or World Wide Importers, it's easy to craft demo queries that fit the scenario you need to make yourself look like a genius. Stack Overflow, in all its simplicity, makes this even easier (lucky me!) because there's nothing all that woogy or wonky to dance around.

Performance Tuning

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

September 8, 2017

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

Performance Tuning

Creating Basic Indexes on the Stack Overflow Public Database

September 7, 2017

My public SQL Server database copy of the Stack Overflow data dump only includes clustered indexes by default. I want to keep your database size as small as possible for quick downloading.

But like any database - when you create it, should you add your own indexes to make queries go faster? The answer lies in knowing your workloads, but we don't usually know our database workloads until they start.

Should You Enforce Foreign Key Relationships in the Database?

September 1, 2017

It's one of those fierce religious wars that divides families and tears loved ones apart. First, if two tables are related, should we tell SQL Server about it? If SQL Server knows that there's a relationship, then it can make better decisions when building execution plans. The problem is that SQL Server has to be…

Performance Tuning

ColumnStore Indexes: Rowgroup Elimination and Parameter Sniffing In Stored Procedures

August 23, 2017

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?

Performance Tuning

How to Drop All Your Indexes – Fast

August 11, 2017

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 [crayon-6a65210a72f6d868265856/] DropIndexes for SQL Server 2008-2014 This one doesn't include…

Performance Tuning

Five Mistakes Performance Tuners Make

August 8, 2017

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 be a lot longer, but I only have the attention span to blog.

Performance Tuning

Locking When There’s Nothing To Lock

August 3, 2017

Demo Day

Performance Tuning

Why Missing Index Recommendations Aren’t Perfect

August 2, 2017

Using the good ol' Stack Overflow public database, get the execution plan for this query - either estimated or actual, doesn't matter:
[crayon-6a65210a759c3568977991/]
In the execution plan, SQL Server asks for a missing index on the LastAccessDate field:

If you right-click on the plan and click Show Execution Plan XML, you get more technical details:

Performance Tuning

ColumnStore Indexes And Recursive CTEs

July 27, 2017

Alone Together

When I think about SQL Server features, I often picture a high school cafeteria.

Performance Tuning

A Better Way To Select Star

July 5, 2017

Mindless Self Promotion
I liked writing this blog post so much that I wrote an entire presentation on it. If you'd like to see it at GroupBy, click the link and vote.

Update: You can watch the video for this session here.

Performance Tuning

Query Plans: Trivial Optimization vs Simple Parameterization

June 16, 2017

Facemaking
You know when you think you know something, and the obviousness of it makes you all the more confident that you know it?

That's usually the first sign that there's a giant gotcha waiting for you.