February 21, 2008 at 12:07 pm
hi gurus! i have a question, I read in a forum http://blog.sqlauthority.com/2008/01/10/sql-server-2005-display-fragmentation-information-of-data-and-indexes-of-database-table/#comment-33889 that i could use this code to check for fragmentation in tables.
SELECT OBJECT_NAME(i.object_id) AS TableName,
i.name AS IndexName,
indexstats.avg_fragmentation_in_percent
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
I ran this and got my list of tables, but then i tried
DBCC SHOWCONTIG (tablename, indexname) and got a different result.
Can anyone tell me why? I am using sql server 2005
February 21, 2008 at 1:24 pm
Hello. Not sure why -- but, have you tried running the statement in another mode... replace 'DETAILED' with either 'LIMITED' or NULL? Maybe these will give you results closer to what dbcc showcontig produced.
February 21, 2008 at 1:34 pm
thanks for your reply, tried it same results... how weird is that? :unsure:
February 21, 2008 at 2:00 pm
Logical Scan Fragmentation and Extent Scan Fragmentation only calculate external fragmentation, which means your pages are logically out of order. It does not factor in internal fragmentation, which means there is additional space on pages that is not being used.
When you run the query you are pulling both internal and external fragmentation. The query should yield higher results. Does it?
February 21, 2008 at 2:11 pm
much higher... ok so now i am a little confused... so the dbcc showcontig is for external fragmentation and the above query for internal and external...Now... is having internal fragmentation as bad as external?, is this crucial to know if the indexes should be rebuilt?
February 21, 2008 at 2:32 pm
Internal fragmentation is not as bad as external fragmentation. Actually, internal fragmentation is expected to happen. Internal fragmentation occurs when you have data in the table and a few rows of that data get deleted. Now you have a gap where the records were deleted. This will happen until the fill factor is met. When you resolve internal fragmentation, you are simply making the indexes use the unused space.
More info on fragmentation:
http://www.sqlservercentral.com/articles/Administering/fragmentation101/1346/
I got a few scripts from a Microsoft book that demonstrates the basic guidelines for when to rebuild and when to reorganize indexs.
DECLARE @dbname VARCHAR(20)
SET @dbname = 'test'
SELECT object_name(dt.object_id),si.name,
dt.avg_fragmentation_in_percent, dt.avg_page_space_used_in_percent
FROM
(SELECT object_id, index_id, avg_fragmentation_in_percent, avg_page_space_used_in_percent
FROM sys.dm_db_index_physical_stats (db_id(@dbname),null,null,null,'DETAILED')
WHERE index_id <> 0
)as dt --it does not return info about heaps
INNER JOIN sys.indexes si
ON si.object_id = dt.object_id
AND si.index_id = dt.index_id
--If avg_fragmentation_in_percent > 10 then the database has External Fragmentation
--If avg_page_space_used_in_percent < 75 then the database has Internal Fragmentation
DECLARE @dbname VARCHAR(20)
SET @dbname = 'Test'
--These indexes should be reorganized, not rebuilt
-- Alter Index ... Reorganize
select *
from sys.dm_db_index_physical_stats (db_id(@dbname),null,null,null,'DETAILED')
where avg_page_space_used_in_percent 10 and
avg_fragmentation_in_percent < 15
--These indexes should be rebuilt, not reorganized
-- Alter Index ... Rebuild
select *
from sys.dm_db_index_physical_stats (db_id(@dbname),null,null,null,'DETAILED')
where avg_page_space_used_in_percent 15
February 21, 2008 at 2:40 pm
thank you so much for your explanation, i understand perfectly now 🙂
February 22, 2008 at 2:55 am
From BOL
"The algorithm for calculating fragmentation is more precise in SQL Server 2005 than in SQL Server 2000. As a result, the fragmentation values will appear higher. "
"Keep Trying"
February 22, 2008 at 4:58 am
There seems to be some missing characters in your code can you please send me the actual code.
Thanks
Murali
February 22, 2008 at 6:45 am
There seems to be some missing characters in your code can you please send me the actual code.
Your absolutely right, somehow my code got stripped. I will edit my OP and also repost the query to determine if you need to rebuild or reorganize indices.
Well I reposted and it still stripped it. I am going to attach the queries via txt file.
February 26, 2008 at 8:27 am
hi question if you replace 'Detailed' with 'limited' in your queries do you get only external fragmentation?
February 27, 2008 at 1:31 am
BOL states:
Scanning Modes
The mode in which the function is executed determines the level of scanning performed to obtain the statistical data that is used by the function. mode is specified as LIMITED, SAMPLED, or DETAILED. The function traverses the page chains for the allocation units that make up the specified partitions of the table or index. sys.dm_db_index_physical_stats requires only an Intent-Shared (IS) table lock, regardless of the mode that it runs in. For more information about locking, see Lock Modes.
The LIMITED mode is the fastest and scans the smallest number of pages. It scans all pages for a heap, but only the parent-level pages for an index, which are the pages above the leaf-level.
The SAMPLED mode returns statistics based on a 1 percent sample of all the pages in the index or heap. If the index or heap has fewer than 10,000 pages, DETAILED mode is used instead of SAMPLED.
The DETAILED mode scans all pages and returns all statistics.
The modes are progressively slower from LIMITED to DETAILED, because more work is performed in each mode. To quickly gauge the size or fragmentation level of a table or index, use the LIMITED mode. It is the fastest and will not return a row for each nonleaf level in the IN_ROW_DATA allocation unit of the index.
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
February 27, 2008 at 7:50 am
You might want to add the page_count when you check for fragmentation so you don't spend time rebuilding indexes on small tables that don't fill more than one page.
--These indexes should be rebuilt, not reorganized
-- Alter Index ... Rebuild
select *
from sys.dm_db_index_physical_stats (db_id(@dbname),null,null,null,'DETAILED')
where (avg_page_space_used_in_percent < 60
OR avg_fragmentation_in_percent > 15)
and page_count > 1
February 27, 2008 at 9:40 am
hi i have a question, i just got told by the senior DBA that to consider that query i have to put at least the page count > 50 is that correct?
February 27, 2008 at 11:07 am
hi i have a question, i just got told by the senior DBA that to consider that query i have to put at least the page count > 50 is that correct?
Edited: Today @ 4:41 PM by DBA
Well, this depends on what you want to see. The page count is the number of pages for a given index. If you say that you want > 50 you are saying that you only want to look at fragmentation on indexes that have a moderate number of data pages.
I would agree that 50is a fair number. You could go higher or lower, but the main point is you dont want to reorganize/rebuild indexes that have no data in them, do you?
Viewing 15 posts - 1 through 15 (of 33 total)
You must be logged in to reply to this topic. Login to reply