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 queries. Data was updated frequently throughout the day and index tuning was a…
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 avoid them whenever I can-- after all if someone drops the index you've hinted, any query hinting a non-existent index will start to fail. (That's a really bad day!)
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…
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 one of the biggest things: these missing index requests won't ever ask for or recommend a specific clustered index.
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.
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 stop finding.One of those things I hate finding is bad fillfactor settings. Fillfactor can…
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 for inserts, updates, and deletes.
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…
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 and the cool features that can dramatically change your database's performance.
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 embarking on a partitioning implementation: the design of the partitioning key, a thorough study of querying practices, and an examination of data modification practices.
I bet you're worried about your index fragmentation.
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 phone book organized by last name, first name.
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…
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…
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 time in table partitioning?
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 the smart way to design indexes, this talk is for you.
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…
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…
Sometimes less is more - and indexes are definitely one of those times. Learn how to identify overlapping indexes and why they're so bad for performance, and learn about SQL Server 2005's new INCLUDE feature.
Last week, I blogged about the basics of SQL Server index fragmentation: why it happens, how to fix it, and how often people are fixing it. I left you with a cliffhanger: it seemed that the frequency of defrag jobs didn't appear to affect fragmentation levels:
Databases with no index defragmentation were an average of 5% fragmented
Monthly - 17% fragmented
Weekly - 3% fragmented
Daily - 6% fragmented
We asked the community for index fragmentation information for data mining purposes, and the results are in - and interesting. Over the next couple of weeks, I'll talk about some of the results we found in the field.
What Is SQL Server Fragmentation?
