A lof of time your index will get framented over time if you do a lot of updates or insert and deletes.
We will look at an example by creating a table, fragmenting the heck out of it and then doing a reorganize and rebuild on the index

First create this table

Now insert 50000 rows

Now create this index

Now let us look at some data by using the sys.dm_db_index_physical_stats DMV. Keep this query handy, we will run it many times

(Result Set)

That is good, almost no fragmentation. Let’s change that shall we?

Okay, now you can see that the index is completely fragmented, we are also using 955 pages to store the data instead of 451
(Result Set)

There are two ways to fix fragmentation, one is to reorganize the index and the other is to rebuild the index. Reorganize is an online operation while rebuild is not unless you specify ONLINE = ON, ONLINE = ON will only work on Enterprise editions of SQL Server.
Here is how to do a reorganize

(Result Set)

As you can see after the reorganize(DBCC INDEXDEFRAG for you SQL Server 2000 folks) fragmentation levels dropped to less than 3 percent.

Just for fun let’s also rebuild(Drop and recreate the index for you SQL Server 2000 folks) the index

(Result Set)

As you can see the rebuild made fragmentation almost 0

Here are two differences between REBUILD ONLINE = ON and REBUILD ONLINE = OFF
ON
Long-term table locks are not held for the duration of the index operation. During the main phase of the index operation, only an Intent Share (IS) lock is held on the source table. This allows queries or updates to the underlying table and indexes to continue. At the start of the operation, a Shared (S) lock is very briefly held on the source object. At the end of the operation, an S lock is very briefly held on the source if a nonclustered index is being created, or an SCH-M (Schema Modification) lock is acquired when a clustered index is created or dropped online, or when a clustered or nonclustered index is being rebuilt. ONLINE cannot be set to ON when an index is being created on a local temporary table.

OFF
Table locks are applied for the duration of the index operation. An offline index operation that creates, rebuilds, or drops a clustered, spatial, or XML index, or rebuilds or drops a nonclustered index, acquires a Schema modification (Sch-M) lock on the table. This prevents all user access to the underlying table for the duration of the operation. An offline index operation that creates a nonclustered index acquires a Shared (S) lock on the table. This prevents updates to the underlying table but allows read operations, such as SELECT statements.

Of course you will not run rebuild/reorganize manually for every index in your database, Michelle Ufford from the SQL Fool blog has a nice post with just a script which can do this automatically, you can find that here: Index Defrag Script

This post is also on our SQL Server Admin Hacks

Take a look at this query.

Can you see the problem?
A person had this query, it would run for over 24 hours. Wow, that is pretty bad, I don’t think I had ever written something that ran over an hour, and the ones I did were mostly defragmentation and update statistics jobs.

The problem is that the following piece of code

is not sargable. First what does it mean to be sargable? A query is said to be sargable if the DBMS engine can take advantage of an index to speed up the execution of the query (using index seeks, not covering indexes). The term is derived from a contraction of Search ARGument Able.

This query is not sargable because there is a function on the column, whenever you use a function on the column you will not get an index seek but an index scan. The difference between an index seek and an index scan can be explained like this: when searching for something in a book, you go to the index in the back find the page number and go to the page, that is an index seek. When looking for something in a book you go from page one until the last page, read all the words on all the ages and get what you need, that was an index scan. Do you see how much more expensive in terms of performance that was?

Let’s get back to the query, what can we do to make this piece of code use an index seek?

You would change it to this:

You can see the complete question on the MSDN forum site here:
http://forums.microsoft.com/msdn/ShowPost.aspx?PostID=3746751&SiteID=1

The Person said that his query went from over 24 hours to 36 seconds. Wow!! That is very significant. hardware cannot help you out if you have bad queries like that.

The same exact day I answered a very similar question, take a look here: http://forums.microsoft.com/msdn/ShowPost.aspx?PostID=3752248&SiteID=1

The person had this

I told him to change it to this

And that solved that query. If you are interested in some more performance, I have written some Query Optimization items on the LessThanDot Wiki. Below are some direct links

Case Sensitive Search
No Functions on Left Side of Operator
Query Optimizations With Dates
Optimization: Set Nocount On
No Math In Where Clause
Don't Use (select *), but List Columns

If you are interested in some blogposts about dates, take a look at these two which I wrote earlier
How Are Dates Stored In SQL Server?
Do You Know How Between Works With Dates?