Indexing

Index Maintenance, SQL Server, Videos

Are you still rebuilding all of your indexes every week – or heaven forbid, every night – and still having performance problems? Odds are, you’re not actually making things better – you’re making them worse! I explain why in this video: For more, read my post: Stop Worrying About Index Fragmentation.

Rebuild or Reorganize: SQL Server Index Maintenance

September 5, 2013

Index Maintenance, SQL Server

106

Index maintenance compacts a structure *and* changes physical order– both can be important! Editors Note: this post was updated in June 2014 to link to an online index rebuild bug that can cause corruption. Once up on a time, there was a database server with 500GB of data and a heavy read workload of dynamic…

Trivial Optimization and Missing Indexes in SQL Server

July 30, 2013

Recently, Jes asked the team an index tuning question: “If a query has an index hint in it, will the optimizer ever suggest a missing index for that query?” I immediately loved the question because I’d never really thought about it before. I typically think of index hints as being a very risky game and…

How to Find ‘Secret Columns’ in Nonclustered Indexes

July 25, 2013

Indexing, sp_BlitzIndex

sp_BlitzIndex® just can’t keep a secret. That’s a feature, not a bug. I wrote sp_BlitzIndex® specifically so that it would rat out some things that SQL Server is doing behind the scenes to secretly help you out– because it’s really better if you know about it. What are secret columns? SQL Server needs a unique…

The Index Your Queries will Never Request (A Clustered Index!)

June 14, 2013

When it comes to indexes, SQL Server is really helpful. It lets you see what indexes queries are asking for both in execution plans, and missing index dynamic management views (“DMVs”). I like to look at the DMV missing index requests using sp_BlitzIndex®. When you look at missing index requests, it’s always important to remember…

Indexing Wide Keys in SQL Server

May 8, 2013

Key length matters in SQL Server indexes. It’s a best practice to keep your index keys as narrow as possible, and SQL Server enforces a maximum key length of 900 bytes on most “normal” clustered and nonclustered indexes. But what happens if you want to optimize the lookup of a wide column? You’re not necessarily…

5 Things About Fillfactor

April 30, 2013

Index Maintenance

Are you a page half full, or a page half empty kind of person? I’ll never think “I’ve seen it all” when it comes to SQL Server– there’s just always someone waiting around the corner who’s found a weird new thing to do with it. But there are some things I really wish I could…

Disabling vs. Dropping Indexes

February 28, 2013

Jes Schultz

In order to improve your applications and your databases, they will need to change over time. The structure of the database changes, the structure of the tables change, the data in the tables change, the application changes, the queries against the data change. Indexes that once helped performance now just bloat your database and cause extra work…

Best Practices + Table Partitioning: Merging Boundary Points

January 31, 2013

Partitioning, SQL Server

One of the many best practices for SQL Server’s table partitioning feature is to create “extra” empty partitions around your data. This is explained in SQL Server Books Online in the page on altering a Partition Function: Always keep empty partitions at both ends of the partition range to guarantee that the partition split (before…

SQL Server Table Partitioning Tutorial: Videos and Scripts

January 28, 2013

partition switching, sql server, table partitioning

Partitioning, SQL Server, Videos

There’s a secret to learning about SQL Server’s table partitioning feature: you need to get your hands on some code and really play with it in a test environment. In this tutorial, Kendra will walk you through test scripts that set up a sample table partitioning environment. These scripts demonstrate gotchas to look out for…

Potential Problems with Partitioning

August 23, 2012

Jeremiah Peschka

Partitioning, SQL Server

Getting started with table partitioning is difficult; there are many decisions to make after you’ve decided to partition data. Correctly configuring partitioning is critical to the long term performance and stability of the database from both a querying and data modification standpoint. There are three key considerations that database architects should devote time to before…

Stop Worrying About SQL Server Fragmentation

August 14, 2012

Index Maintenance, SQL Server

135

I bet you’re worried about your index fragmentation. If your database was a phone book, it would look like this one. And I bet you’re wasting your time, missing the real point. What is Index Fragmentation and How It Happens Let’s take a step back for a second and pretend that your database is a…

SQL Server Index Terms

July 12, 2012

Jes Schultz

Indexing

Let’s have a chat about your indexes in SQL Server. Indexes can be confusing. They are the easiest way to boost performance of queries, but there are so many options and terms around indexes that it can be hard to keep track of them. Let’s chat about the differences between clustered and nonclustered indexes, key…

Hash Partitioning, SQL Server, and Scaling Writes

April 10, 2012

Jeremiah Peschka

Architecture, Partitioning

At first glance, SQL Server’s partitioning seems like it should be an easy way to solve problems inserting data into busy tables. If the writes are spread across many partitions it only makes sense that we can avoid write hot spots in SQL Server, right? The truth is much more complicated than it appears. Asking…

How To Decide if You Should Use Table Partitioning

March 6, 2012

Partitioning, SQL Server

141

Great volumes have been written about table partitioning. It’s a complex feature and you can read for days to just understand how you might apply it. But will it improve performance for you? Table partitioning produces great benefits for some applications, but causes giant headaches for others. How do you know if you should invest your…

Kendra Little Explains How to Design Smarter Indexes

September 23, 2011

How do you know which indexes to create to improve your database performance? Microsoft Certified Master Kendra Little shows you how to find a missing index, decide on the best index definition, and measure the usefulness of your new index. If you know the basics of what a database index does and want to learn…

Indexing for Deletes

September 1, 2011

Jeremiah Peschka

If you’re only indexing to make reads faster, you need to think again. Indexes can make every operation in the database faster, even deletes. The Problem: Deletes are Very Slow I was lazily researching new development techniques one day when I received an email from a client asking why deletes could be slow. I rattled…

Well, it was good, but I dunno about amazing.

Seeks, Scans, and Statistics in the Grocery Store

May 10, 2011

Indexing, T-SQL

Building an execution plan is a lot like going shopping. Before I leave the house, I need to think about a few things: How many items do I need? I take a glance at my grocery list to see roughly how many things I’m shopping for. What stores am I going to visit? I can’t…

SQL Server Index Tuning Tip: Identify Overlaps

July 6, 2009