Indexing

Computed columns, Development, SQL Server

During Training We were talking about computed columns, and one of our students mentioned that he uses computed columns that run the REVERSE() function on a column for easier back-searching. What’s back-searching? It’s a word I just made up. The easiest example to think about and demo is Social Security Numbers. One security requirement is…

When Does Index Size Change Index Choice?

January 16, 2018

Indexing, SQL Server

Size Matters? One thing I hear a lot is that the optimizer will take the size of an index into account when choosing which one to use. The tl;dr here is that it may make more of a difference when it comes to index width (think the number of columns in an index) than it…

Heaps, Deletes, and Optimistic Isolation Levels

January 2, 2018

Indexing, SQL Server

The Humble Heap If you don’t know this by now, I’m going to shovel it at you: If you have a table with no clustered index (a Heap), and you delete rows from it, the resulting empty pages may not be deallocated. You’ll have a table with a bunch of empty pages in it, and…

Do Disabled Indexes Affect Missing Index Recommendations?

December 21, 2017

Indexing

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: Transact-SQL SELECT * FROM dbo.Users WHERE DisplayName = 'Hovercraft Full Of Eels'; 123 SELECT *FROM dbo.UsersWHERE DisplayName = 'Hovercraft…

Rebuilding Indexes Can Slow Queries Down

December 18, 2017

Index Maintenance

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. I thought that was…

Partition Level Locking: Explanations From Outer Space

November 30, 2017

Development, Locking, Blocking, and Isolation Levels, Partitioning

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. Microsoft says you can partition for performance. Partitioning large tables or indexes can have the…

Good Reasons to Rebuild or Reorganize Indexes

November 28, 2017

Index Maintenance

dba, development

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…

New System Stored Procedures in SQL Server 2017

October 2, 2017

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…

Live Query Plans and Blocking

September 26, 2017

Execution Plans, Indexing, Locking, Blocking, and Isolation Levels, SQL Server

dba, development, sql server

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…

Key Lookups and ColumnStore Indexes

September 22, 2017

Columnstore Indexes, Execution Plans, Indexing, SQL Server

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…

Optional Parameters and Missing Index Requests

September 14, 2017

Execution Plans, Indexing, SQL Server

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…

Indexing for Windowing Functions: WHERE vs. OVER

September 13, 2017

Execution Plans, Indexing, SQL Server

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…

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

September 8, 2017

Execution Plans, Indexing, SQL Server

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,…

Creating Basic Indexes on the Stack Overflow Public Database

September 7, 2017

Indexing, Stack Overflow

No Comments

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…

Should You Enforce Foreign Key Relationships in the Database?

September 1, 2017

Indexing

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…

ColumnStore Indexes: Rowgroup Elimination and Parameter Sniffing In Stored Procedures

August 23, 2017

Columnstore Indexes, Development, Execution Plans, Indexing, SQL Server, SQL Server 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?…

How to Drop All Your Indexes – Fast

August 11, 2017

Bad Idea Jeans, Indexing

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',…

Five Mistakes Performance Tuners Make

August 8, 2017

Development, Execution Plans, Indexing, SQL Server

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…

Locking When There’s Nothing To Lock

August 3, 2017

Development, Execution Plans, Indexing, Locking, Blocking, and Isolation Levels