Index Remained Fragmented EVEN AFTER Index Rebuild Job

I had an interesting thing to find out this week. An Organization had an issue: Their queries are running extremely slow. They did some research on this. They found that even simple queries which uses index seek too are extremely slow. They copied the data into another database and created the indexes and executed again. They were quite fast. There were no other processes running when this test was carried out.

Through another friend of mine they got my contacts and they called me. I got the execution plans from both databases and they were identical. Then with the help from them, I ran a query using sys.dm_db_index_physical_stats against this index and found that it is fragmented. They rebuilt the index and the query started working well again.

Using the same DMV against other tables, I found out that the indexes are fragmented. They sent me the figures they received using dm_db_index_physical_stats Fragmented means some are 80% - 95% fragmented. To be honest with you, I even saw a few indexes showing 100% fragmentation which I have never seen before. I asked them to rebuild the indexes. Later I explained them how these house keeping jobs could be automated.

After a couple of weeks, they called me again. The indexes are fragmented again. I asked them whether they have added the index maintenance job. They answered back saying they have an index maintenance task as part of the database maintenance job and even made it as the first task of the job, [which runs on Sunday nights] but that has not resolved the issue.

The Other side of Filtered Index

 
I see many like the idea of filtered index. This cooooooooooool new feature allows an index to be created on a subset of the data. I have seen many presentations praising this feature.

Most of you who attended the session on “The other side of indexes”  at the last Tech Ed would have enjoyed the session well. Pinal Dave was giving a very good explanation on when to use indexes and when not to use them. He mentioned on Filtered index and explained how it will benefit certain type of queries.


If you have a large table (by rows) your index will be larger.  It adds complexity over the maintenance plans you need to build.  Additionally, statistics around larger indexes can get skewed easily. This may lead to sub-optimal execution plans chosen by SQL Server Query engine.

On of the key features on filtered index is the WHERE clause. The condition may not reference the key columns of the index For example, CREATE INDEX IndexName ON TableName (Column1) WHERE Column2 >100 is a valid statement. This allows a high level of logical partitioning of data. You can create an index for column1 where Column2 is mentioned in the definition but not in the storage.

I am inspired by the feature. Wow filtered index can make wonders.  It will ease some pain points from DBAs. Suddenly a question came into my mind:  Can I create multiple filtered indexes on the entire table by

Gift from Quest

Yesterday, When I came from home I had a pleasant surprise.
Exactly one month ago, I wrote a post on free online training on SQL Server which was hosted by Quest. As I participated and filled the survey, Quest decided to honer me by thanking for my participation.

Of course I learned from the participation, But Quest surprised me with added stuff. Even though I participated only on two sessions, I received a Cd with all the sessions they had on that day. In addition to that I got two banners one on SQL server dynamic management views and the other on catalog views. To add, I also got a T-shirt on my size.
Of course the packet included some marketing stuff as well, but it gave me some advantage. As it was categorized as marketing material it bypassed the customs.

I went though the CD, it had the presentations and some additional documents. REALLY COOL stuff.

In summary, I gained by participating (the knowledge), I am gaining by participating (the goodies) and I will be gaining by participating. (I filled the survey so that future training programs will be more beneficial.)

Are you guys thinking of participating at the next training program?

Free Online Training on SQL Server

How many of you like to join a really technical deep dive seminar? Well, if you are in US you have a high chance of joining the seminar after paying the registration fee. But for those who are in other places now have a chance to attend one online, for free. Quest Software - a leading provider of tools in windows administration, virtualization and of course, SQL Server - is organizing one.

This training mainly on administration of SQL Server, where resource governor, profiler and other Microsoft tools related to performance optimization will be discussed. The afternoon sessions will be too late for most of the Sri Lankans, but the morning session is a good time to attend. (Do you sleep before 9.30 pm? Can you stay up till 12:30 am?)

Interested? Have a look on the agenda here:

http://www.quest.com/events/listdetails.aspx?ContentID=10219

Recently PASS had a 24 hour free online seminar on various subjects. I was able to attend a few, but due to some issues in my online calendar, I couldn’t attend all. They were really good ones. I was able to gain much from them.

For those who have missed them, they have a chance to view them later. The meetings are being archived, and they’ll be available for replay after the PASS Summit in early November.

Share this post :

Storing Hierarchical Data - An Early Solution

SQL Server 2008 can with a new data type to handle hierarchical data – hierarchyid.

How to handle hierarchical data in the previous editions?

Even though it is not supported natively, there are different methods we could employ to handle hierarchical data. Some of them are rely cool and perform as fast as SQL Server 2008.

I have written an article on this:
Storing Hierarchical Data - An Early Solution
 
Even though this site requires paid subscription to read this article, you could go with online trial. You can cancel it if you don't like the site

Table Partitioning

I was asked to speak at the Sri Lankan SQL Server User group Meeting on Table Partitioning.
I did it last week (August 16 2007) and as usual I took another 15 minutes from the next speaker too.

The slides and example are available at
http://sqlserveruniverse.com/files/folders/meeting05/entry301.aspx
http://sqlserveruniverse.com/files/folders/meeting05/entry302.aspx

Interestingly, I found a method to change (Cerate/drop) the identity property of a column during my preparation for this presentation. I was told by many members that they were forced to add a column and drop a column to the table just because they wanted to have the identity property added or dropped.

Is it Interesting? Check out at the examples