Indexing

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

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

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 Borland

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…

How To Decide if You Should Use Table Partitioning

March 6, 2012

139

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

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

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

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