| Author |
Topic |
|
muratos
Starting Member
22 Posts |
 Posted - 2011-07-29 : 03:53:50
|
| Hi,We have an interesting situation here. We are using a paid program to monitor our servers. There is a constant warning that some of the tables are badly fragmented at 95%s. All these tables have clustered indexes on them.According to our knowledge, there is no such thing as "table fragmentation" on tables with clustered indexes. Only indexes can be fragmented. This is true only for heaps without any indexes. So, we rebuilt the indexes and they look ok. But, in no way, we can not rid of these warnings. Is it possible that these tables are really fragmented on OS level or it is a bug from the program?I have checked the disks where SQL data and logs reside and they show only around 4% fragmentations which I think ignorable.What are your thoughts?ThanksMurathttp://ipad2keyboard.nethttp://ipad2covers.org |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2011-07-29 : 11:27:46
|
quote:
According to our knowledge, there is no such thing as "table fragmentation" on tables with clustered indexes.
That is incorrect. Both clustered and nonclustered indexes can suffer from fragmentation.I don't have any knowledge of the program that you are using so I can't say why it is still showing warning.To check your fragmentation level for the indexes, run this:SELECT *FROM sys.dm_db_index_physical_stats (DB_ID(dbNameGoesHere), NULL, NULL, NULL, 'DETAILED')WHERE avg_fragmentation_in_percent > 10 AND avg_fragmentation_in_percent < 100 AND index_id > 0 AND index_level = 0Make sure you replace dbNameGoesHere with the one you want to check. Also, if your database is sizable, you may want to switch DETAILED to SAMPLED. DETAILED provides the best information, however it comes at a cost of performance.You can use my custom code to cleanup the fragmentation: http://weblogs.sqlteam.com/tarad/archive/2009/11/03/DefragmentingRebuilding-Indexes-in-SQL-Server-2005-and-2008Again.aspxBut please note that you cannot defragment indexes with fewer than 1000 pages. If your paid program doesn't know this, then perhaps it'll always be in alarm for those tables.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Subscribe to my blog |
 |
|
|
muratos
Starting Member
22 Posts |
Posted - 2011-08-01 : 03:22:49
|
| Thanks Tara, your information is exact and very helpful. I think our program uses wrong terminology. It is better to use "Index fragmentation" than "Table fragmentation" for tables with clustered and/or non-clustered indexes.One more additional question from me which I believe it will be useful for community. I use DBCC Indexdefrag to fragment indexes, but there is also Alter Table .. Rebuilt index with Online option. Both seems to be doing same tasks. As far as I know, the only difference is that IndexDefrag is a logged operation and it is safer against query interruptions. So, even the running query is cut before completion, it can continue defragmenting remaining parts. But Rebuilt with Online option works in All-or-Nothing mode. Am I right? Any corrections or some extra info which we should know?p.s : I didn't mention DBCC DBReindex because it will be deprecated and works in only Offline mode. So, the usage is not suggested. Right? |
|
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2011-08-01 : 09:13:22
|
quote:
Originally posted by muratos
Thanks Tara, your information is exact and very helpful. I think our program uses wrong terminology. It is better to use "Index fragmentation" than "Table fragmentation" for tables with clustered and/or non-clustered indexes.One more additional question from me which I believe it will be useful for community. I use DBCC Indexdefrag to fragment indexes, but there is also Alter Table .. Rebuilt index with Online option. Both seems to be doing same tasks. As far as I know, the only difference is that IndexDefrag is a logged operation and it is safer against query interruptions. So, even the running query is cut before completion, it can continue defragmenting remaining parts. But Rebuilt with Online option works in All-or-Nothing mode. Am I right? Any corrections or some extra info which we should know?p.s : I didn't mention DBCC DBReindex because it will be deprecated and works in only Offline mode. So, the usage is not suggested. Right?
Online index rebuild is only available in the Enterprise or Developer editions of SQL server 2008.CODO ERGO SUM |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
jeffw8713
Aged Yak Warrior
819 Posts |
Posted - 2011-08-01 : 16:13:19
|
| Just a bit of clarification:Tara stated that you cannot rebuild indexes with less than 1000 pages. That is not correct - you can rebuild those indexes, it's just that it may not be very useful to do so. For tables that have only a couple of pages - rebuilding or reorganizing them won't make any difference because SQL Server uses mixed extents for those tables.Both REORGANIZE (INDEXDEFRAG) and REBUILD (DBREINDEX) are fully logged operations. In certain circumstances, an index rebuild can be minimally logged - but a reorganize will always be fully logged. Reorganize is an online operation - meaning it will not block queries from using that index. However, it will also only ever use a single processor (no parallel processing) and will generally take a lot longer to run and does defragment the index as much as a rebuild will.A REBUILD will take the index offline unless you use the ONLINE=ON parameter (Enterprise Edition feature). Also, in Enterprise Edition, a rebuild will be a parallel operation and will generally take a lot less time to rebuild the index than a reorganize takes.What the ONLINE capability allows for is the use of the old index until the new index has been built. Once the new index is built, a schema lock is taken and the old index deleted and the new index inserted in it's place.Jeff |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
jeffw8713
Aged Yak Warrior
819 Posts |
Posted - 2011-08-01 : 22:46:40
|
| Tara - rebuild or defragment, same thing. And yes, you can defragment a table with less than a 1000 pages and it will defragment. It may or may not be useful - which is why you need to test to see if it has any benefit.The general recommendation is to not bother with rebuilding an index with less than a 1000 pages because there is no performance benefit in doing so. In some cases, that could be as high as 10000 pages - depending on the system and usage of the table.Again, the only time you cannot defragment (rebuild) an index is when it is small enough that SQL Server stores it in mixed extents which cannot be defragmented. You can also defragment (rebuild) a heap now - it is a rebuild option on ALTER TABLE in 2008. Might be 2008 R2.Jeff |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2011-08-01 : 23:33:55
|
| You are using the terms interchangeably and they are not the same, but I guess it's not really your point. When you use the term defragment to indicate the actual action, it is actually reorganize and not rebuild. But what I am saying in my post is actually about removing the fragmentation, which I've heard you can't do. You can decrease it, but you can't get rid of it on small tables. I've never looked closely at it though, so I'm only going by my training.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Subscribe to my blog |
|
|
|
Sachin.Nand
2937 Posts |
Posted - 2011-08-02 : 03:39:32
|
| To add to what jeff said it is not just 1000 pages where rebuild wont be helpful even on small indexes spanning thousands of pages a rebuild will hardly be of any help.PBUH |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2011-08-02 : 12:44:04
|
| To add to this discussion, you should know that rebuilding doesn't help until you've got quite a bit of fragmentation even in large tables. It has been suggested by Microsoft this value is as high as 75%. I like to play on the safe side though, and keep my SQL job at 50% before it rebuilds. I did try out 75% for a bit though, and there was no noticeable performance increase by rebuilding at any lower value. Whether or not MS is right about the value though is up to you to performance test.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Subscribe to my blog |
|
|
|
denis_the_thief
Aged Yak Warrior
596 Posts |
Posted - 2011-08-03 : 15:16:19
|
quote:
Originally posted by jeffw8713
Tara - rebuild or defragment, same thing. And yes, you can defragment a table with less than a 1000 pages and it will defragment.
I tried it myself. I took a table with 28 pages - I could not get the Fragment Count below 5 nor the avg_fragmentation_in_percent below 15%. I tried both Reorganize and Rebuild with various Fill Factors. I know it's negligable with a table of 28 Pages.Anyone know what the formula is used to calculate avg_fragmentation_in_percent is?And anyone know why fragments still exist after a rebuild? Sure they me negligable compared to the number of Pages - but why can't SQL Server just rebuild the index without any Fragments? |
|
|
|
jeffw8713
Aged Yak Warrior
819 Posts |
Posted - 2011-08-03 : 17:13:26
|
| At only 28 pages - you probably are still allocated across mixed extents. That would be why you cannot remove all fragmentation - but, it could also be the type of data you are storing.I tested with around 50 pages - and was able to get to 0% fragmentation.Either way, less than a 1000 pages - or even less than 10000 pages depending on the system may not have any benefits. It depends on the system.Jeff |
|
|
|
|
Fragmented Table Question