September 18, 2014 at 6:07 am
HI,
I Had reindexed the db's all the db's using the script below but still i seen some non clustered index are not changed may i know the reason & how to reduce the percentage of them & improve the performance
script
-----------------------------
exec master.sys.sp_MSforeachdb 'USE [?]
SELECT db_name() as DatabaseName, OBJECT_NAME (sysst.object_id) as ObjectName,
sysst.index_id, sysind.name as IndexName,
avg_fragmentation_in_percent, index_type_desc
FROM sys.dm_db_index_physical_stats (db_id(), NULL, NULL, NULL, NULL) AS sysst
JOIN sys.indexes AS sysind
ON sysst.object_id = sysind.object_id AND sysst.index_id = sysind.index_id
WHERE sysst.index_id <> 0 and avg_fragmentation_in_percent > 30'
go
----------------------------------
thanks
Santosh
September 18, 2014 at 6:26 am
How big are the indexes in question? How many pages?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
September 23, 2014 at 5:07 am
Its around 82 pages
September 23, 2014 at 5:38 am
For an index that small I really wouldn't worry about fragmentation, it won't affect performance.
Also the script you posted just returns the fragmentation of the indexes, it won't rebuild them.
September 23, 2014 at 5:58 am
santoshkal (9/23/2014)
Its around 82 pages
Way too small to even bother trying to defragment.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply