September 17, 2010 at 7:05 am
Hi,
Can someone please help me to understand this please, I run 2 times a week,The Check Database Integrity, Reorganize Index and defrag them if they >50,why I am getting this results,what should I do to improve Thank you so much
I ran the following code:
SELECT OBJECT_NAME(i.OBJECT_ID) AS TableName, i.name AS IndexName, indexstats.avg_fragmentation_in_percent,
CASE WHEN indexstats.avg_fragmentation_in_percent between 5 and 30 then 'Reorganize Index'
WHEN indexstats.avg_fragmentation_in_percent > 30 then 'Rebuild Index'
End as Take_Action
FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, 'DETAILED') indexstats
INNER JOIN sys.indexes i ON i.OBJECT_ID = indexstats.OBJECT_ID
AND i.index_id = indexstats.index_id
WHERE indexstats.avg_fragmentation_in_percent > 20
ORDER BY 3 DESC
Get the results:
TableNameIndexNameavg_fragmentation_in_percentTake_Action
tabel1 PK_tabel1100 Rebuild Index
tabel2 PK_table287.5 Rebuild Index
tabel3 IX_tabel350 Rebuild Index
tabel4 CAT_table430.2521008403361 Rebuild Index
tabel5 _dta_index_table5__K3_K230 Reorganize Index
September 17, 2010 at 7:14 am
How big are those indexes? 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 17, 2010 at 7:18 am
How can I check that? Thank you
September 17, 2010 at 7:21 am
There's a column page_count in the DMV you're using to check fragmentation.
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 17, 2010 at 7:27 am
page_count
52
8
173
8
28
12990
September 17, 2010 at 7:38 am
I'm quite sure that you've been part of discussions before around the minimum page count where it's considered useful to rebuild indexes....
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 17, 2010 at 7:44 am
Do you have link for that, I would really appreciate, thank you for your help
September 17, 2010 at 8:13 am
Apologies, I was thinking of someone else.
It's generally not recommended to rebuild indexes below 1000 pages as there's little performance gain. Especially for very small indexes (<25 pages) you'll likely see no change in fragmentation if you do rebuild.
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 17, 2010 at 8:23 am
Thank you very much. Do you know any articles that I can read more about that subject.
September 17, 2010 at 8:33 am
Krasavita (9/17/2010)
Thank you very much. Do you know any articles that I can read more about that subject.
Not offhand, but a quick google search should turn up a few.
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 17, 2010 at 8:48 am
Thank you
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply