January 30, 2010 at 9:07 pm
Hi,
I'm currently using index rebuild task to rebuild the indexes on weekly basis. So far I did not check whether the indexes are really getting fragmented or not. As suggested by the forum members Now, I'm in a process to implement T-sql script to index defragment. For that, firstly, I started to find out how much fragmentation is there in our heavily used database using the below script (I have selected page_count>1000 because I read in many places that indexes having page count<1000 pages, we do need to worry ). Here I have disabled the Index rebuild maintenance task since last 2 weeks and now I'm trying to find out how much fragmentation is there after 2 weeks instead of blindly running Index rebuild maintenance task on weekly basis
SELECT database_id,object_id, index_id, index_type_desc,avg_fragmentation_in_percent, page_count FROM sys.dm_db_index_physical_stats (9, NULL, NULL, NULL, 'limited')
where avg_fragmentation_in_percent>30 AND page_count>1000
Result:
Database_id Object_idIndex_idindex_type_descavg_fragmentation_in_percentPage_count
9148963657 0 HEAP 99.35 7436
Surprisingly, after 2 weeks, I have noticed that only one index got fragmented. So I'm not sure my analysis is correct or not.
Secondly, I ran the same query by using scan mode DETAILED and got the below result
Database_id Object_idIndex_idindex_type_descavg_fragmentation_in_percentPage_count
9148963657 0 HEAP 99.35 7436
9714693844 1 CLUSTERED INDEX 41.17 4386
9714693844 14 NONCLUSTERED INDEX 67.36 1529
Please advice that whether our indexes really need a weekly basis index rebuild task(which will generate 15 GB log file for the database) or not.
And do we really required to defragment HEAP Indexes? or it's enough to defrag only cluster & non cluster indexes?
Thanks
January 30, 2010 at 10:46 pm
It looks like a majority of your fragmentation is in heaps. Heaps cannot be defragged. You must determine which tables are heaps. Those are the tables that do not have a clustered index. Create a clustered index on those tables and then defrag. At the frag levels you are demonstrating - yes you should defrag on a regular basis.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
January 31, 2010 at 10:21 am
Are you really concerned about having a 15GB log file? How large is this database and how large do you expect it to get?
Having a large transaction log file does not affect the performance of SQL Server. There is no reason to be concerned about the file being that large.
Now, if you are worried about how large the backup files are - again, I would have to ask why are you worried about it? They need to be as large as the transactions being performed on the database, so if they need to be that large - get the storage you need to support it.
Jeffrey Williams
Problems are opportunities brilliantly disguised as insurmountable obstacles.
How to post questions to get better answers faster
Managing Transaction Logs
January 31, 2010 at 11:07 am
1st concern Do we really required to run the Index rebuild task on weekly, if the indexes are not getting fragmented (Here, I have noticed that only couple of indexes got fragmented and I want to use T-sql script to rebuild only those fragmented indexes instead of running weekly Index rebuild maintenance task )
2nd concern: The log file size while running the weekly Index rebuild maintenance task. Because for that particular database, Log shipping is configured and transferring big log file of size 15 GB over the network taking 10 hrs and that's why I have started analyzing whether the indexes really getting fragmented or not.
Here the actual database size is 17 GB and when we run Index rebuild task, it's generating 15 GB of log file almost equal to the database size
Storage is not a problem.
thanks
January 31, 2010 at 11:15 am
It looks like a majority of your fragmentation is in heaps. Heaps cannot be defragged
So if the index of type HEAP has fragmentation 99.35, then if I rebuild that heap, then the fragmentation will not change right? I mean after rebuilding also the fragmentation will be 99.35 right?
thanks
January 31, 2010 at 11:21 am
Okay - you do not need to rebuild every index, just those indexes that are fragmented. Using any one of the scripts available to identify and rebuild or reorganize indexes based on their fragmentation level will reduce the amount of data needing to be transmitted.
If you have a heap - it cannot be defragged. There is no index to rebuild or reorganize.
Jeffrey Williams
Problems are opportunities brilliantly disguised as insurmountable obstacles.
How to post questions to get better answers faster
Managing Transaction Logs
January 31, 2010 at 11:33 am
thanks Jefferey,
Here I considered the page_count>1000. Is that right criteria to consider for finding Index fragmentation that are only having page count>1000?
thanks again
January 31, 2010 at 12:04 pm
Mani-584606 (1/31/2010)
thanks Jefferey,Here I considered the page_count>1000. Is that right criteria to consider for finding Index fragmentation that are only having page count>1000?
thanks again
1000 pages is a common recommendation. In my environments, I will also check those that are >=100 pages.
For the heaps, create a clustered index on the tables that are heaps and then you will be able to defrag them. You will likely notice a change in performance.
And as Jeff said, you should reorg some indexes and rebuild others. Not all indexes always need to be defragged. Just selectively defrag those that are fragmented. There are scripts available for download that allow that kind of control too.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
January 31, 2010 at 12:23 pm
thanks you,
how to find if the statistics are out of date? As our indexes are not getting fragmented, so I'm planning to run update statistics to keep the statistics up to date. For that 1st, I want to find whether the statistics are up to date or not and then update the statistics if required
Thank you SSC..I'm learning many new things from this site
January 31, 2010 at 12:26 pm
Mani-584606 (1/31/2010)
thanks Jefferey,Here I considered the page_count>1000. Is that right criteria to consider for finding Index fragmentation that are only having page count>1000?
thanks again
The classic answer applies here - it depends. In some cases, using as Jason suggested a limit of 100 is right, other cases a value above 10000 would be right.
It really depends upon your system (memory, cpu's, io, etc...), the maintenance window you have and the downstream effects (e.g. log shipping, mirroring, replication, etc....). For a system that is less than 20GB that is not a 24/7 system I would just reindex everything weekly. But, in your case - you have log shipping enabled and seeing problems with transferring that amount of data across the network.
Jeffrey Williams
Problems are opportunities brilliantly disguised as insurmountable obstacles.
How to post questions to get better answers faster
Managing Transaction Logs
January 31, 2010 at 12:31 pm
Mani-584606 (1/31/2010)
thanks you,how to find if the statistics are out of date? As our indexes are not getting fragmented, so I'm planning to run update statistics to keep the statistics up to date. For that 1st, I want to find whether the statistics are up to date or not and then update the statistics if required
Thank you SSC..I'm learning many new things from this site
Review the procedure sp_updatestats. It selects stats to be updated based upon whether or not they need to be updated. You can use the same technique in your own procedure - or call that procedure.
If you decide to use sp_updatestatus, you need to know that it will (by default) use the default sampling rate. In most cases, this is not a problem and wouldn't cause any issues. However, there are a few cases where performing a full scan needs to be done. In those cases, schedule a separate job to just update those statistics on whatever schedule they need.
Jeffrey Williams
Problems are opportunities brilliantly disguised as insurmountable obstacles.
How to post questions to get better answers faster
Managing Transaction Logs
January 31, 2010 at 6:30 pm
Hi,
I have ran the below Query in 'Mydb' database to find out the out of date statistics and I found that all the statistics were 15 days old. The Auto Update statistics option is enabled for this database. Last time I ran the 'Update Statistics maintenance task' is 15 days ago
Query1:
Use [Mydb]
GO
SELECT OBJECT_NAME(A.object_id) AS Object_Name, A.name AS index_name,
STATS_DATE(A.OBJECT_ID, index_id) AS StatsUpdated ,
DATEDIFF(d,STATS_DATE(A.OBJECT_ID, index_id),getdate()) DaysOld
FROM sys.indexes A
INNER JOIN sys.tables B ON A.object_id = B.object_id
WHERE A.name IS NOT NULL
ORDER BY DATEDIFF(d,STATS_DATE(A.OBJECT_ID, index_id),getdate()) DESC
Results:
Object_NameIndex_Name StatsUpdated DaysOld
PRPTRSPK_PRPTRS 01/16/2010 21:19 15
AUTCOTDPPK_AUTCOVMFL 01/16/2010 21:07 15
POLAFLPK_POLAFL 01/16/2010 21:12 15
BAPML PK_BAPML 01/16/2010 21:08 15
BAPMFL IX_BAPMFL 01/16/2010 21:08 15
AILFFLCXEPK_AILFFLCXE NULL NULL
PAULTMFPK_PAULTMF NULL NULL
PAULMDFPK_PAULMDF NULL NULL
and why there is a NULL value for StatsUpdated & DaysOld for some indexes?
actually this query tells us how old the stats are, but not if they are outdated. So how we know whether these Statistics are outdated or not?
Then I ran the below query to update the stats:
Query2:
Use [Mydb]
go
SP_UPDATESTATS
GO
Results:
Updating [dbo].[AUTCOTDP]
[PK_AUTCOVMFL], update is not necessary...
[_WA_Sys_NEXPNUM_000D5AD4], update is not necessary...
[_WA_Sys_NSTANUM_000D5AD4], update is not necessary...
[_WA_Sys_NVEHNUM_000D5AD4], update is not necessary...
[_WA_Sys_NLOCNUM_000D5AD4], update is not necessary...
[_WA_Sys_NCOVNUM_000D5AD4], update is not necessary...
[_WA_Sys_NXPS_000D5AD4], update is not necessary...
[_WA_Sys_HXPS_000D5AD4], update is not necessary...
[_WA_Sys_XSUBTYP_000D5AD4], update is not necessary...
[_WA_Sys_LSUBTYP_000D5AD4], update is not necessary...
[_WA_Sys_HSUBTYP_000D5AD4], update is not necessary...
[_WA_Sys_LMATCDE_000D5AD4], update is not necessary...
[_WA_Sys_LCOVDES_000D5AD4], update is not necessary...
[_WA_Sys_HCOVDES_000D5AD4], update is not necessary...
[_WA_Sys_XCOVIND_000D5AD4], update is not necessary...
So here for all the tables in Mydb database, It's giving me update is not necessary..and then I ran the Query1 and got the same result saying statupdated as 15 days old
Object_NameIndex_Name StatsUpdated DaysOld
PRPTRSPK_PRPTRS 01/16/2010 21:19 15
AUTCOTDPPK_AUTCOVMFL 01/16/2010 21:07 15
POLAFLPK_POLAFL 01/16/2010 21:12 15
Thanks
February 1, 2010 at 6:57 am
The 'Auto Update Statistics' option should be enabled for databases where you need to do this.
The database engine automatically updates them when they go out of date.
In addition, you can update then whenever you defrag of rebuild indexes every fortnight or so, and this will be an explicit updation.
Regards
Akhil
February 3, 2010 at 12:02 pm
How to find exactly that these Statistics are up to date and these Statistics are outdated?
thanks
February 4, 2010 at 7:53 am
Heaps _as such_ cannot be defragged... but creating a clustered index and then dropping it does actually leave behind a defragged heap. This technique has, in one case I saw, reduced a big query (table scan) from ~650,000 reads to ~360,000 reads; a significant improvement. Note that leaving the clustered index in place reduced that same query to ~30 reads, an immense improvement. Statistics taken from Profiler, SQL:BatchRequest.
In most cases, having a well chosen clustered index is going to be better for you. You may, however, need a FILLFACTOR less than the default 100% (0 = 100) if you are updating values in the middle to something else, or are inserting values that are not continuously increasing (or decreasing for a DESC clustered index field).
Viewing 15 posts - 1 through 15 (of 20 total)
You must be logged in to reply to this topic. Login to reply