Indexing

Home
Indexing

Best Practices + Table Partitioning: Merging Boundary Points

January 31, 2013

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

Kendra Little

Partitioning, SQL Server, Videos

partition switching, sql server, table partitioning

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

Brent Ozar

Index Maintenance, SQL Server

137

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

Kendra Little

Partitioning, SQL Server

145

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

Kendra Little

Indexing, SQL Server

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

Indexing, SQL Server

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

Brent Ozar

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

Brent Ozar

Indexing

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.

Index Fragmentation Findings: Part 2, Size Matters

February 10, 2009

Brent Ozar

Index Maintenance, SQL Server

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…

Index Fragmentation Findings: Part 1, The Basics

February 6, 2009

Brent Ozar Unlimited Team

Index Maintenance, SQL Server

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? SQL Server stores data in pages. To make things easy, think of…

SQL Server Partitioning: Not the Best Practices for Everything

June 12, 2008

Brent Ozar

Partitioning, SQL Server

When SQL 2005 was in beta, I clapped my hands and jumped for joy the first time I played with partitioning. I’ve done a lot of data warehouse work, and partitioning lets the DBA break down massive tables into smaller, easy-to-manage chunks. We can put heavily accessed parts of the table onto fast storage, and…

SQL Server training for developers: primary keys & indexes

April 16, 2006

Brent Ozar

Indexing, SQL Server

I had to do some developer training last week and I wrote up a paper on the basics of primary keys and indexes. Sure, there’s tons of similar stuff around the net, but this is MINE, baby. Our Table: Phone Numbers For our evil training purposes, let’s say we work for the phone company, and…