May 28, 2008 at 5:01 am
Hi all,
Here i want to know the details which i got on running dbcc showcontig command.
I know an extent has 8 pages, below are 15 pages and they are spread in 4 extents
What is Extent switches and avg. Pages per Extent and Scan Density [Best Count:Actual Count].......: 40.00% [2:5]
and all,
- Pages Scanned................................: 15
- Extents Scanned..............................: 4
- Extent Switches..............................: 4
- Avg. Pages per Extent........................: 3.8
- Scan Density [Best Count:Actual Count].......: 40.00% [2:5]
- Logical Scan Fragmentation ..................: 6.67%
- Extent Scan Fragmentation ...................: 25.00%
- Avg. Bytes Free per Page.....................: 2663.6
- Avg. Page Density (full).....................: 67.09%
can any body help me in getting this knowledge of defragmenting indexes
May 28, 2008 at 7:38 am
See Books Online:http://msdn.microsoft.com/en-us/library/aa258803(SQL.80).aspx
Pages Scanned
Number of pages in the table or index.
Extents Scanned
Number of extents in the table or index.
Extent Switches
Number of times the DBCC statement moved from one extent to another while the statement traversed the pages of the table or index.
Avg. Pages per Extent
Number of pages per extent in the page chain.
Scan Density [Best Count: Actual Count]
Is a percentage. It is the ratio Best Count to Actual Count. This value is 100 if everything is contiguous; if this value is less than 100, some fragmentation exists.
Best Count is the ideal number of extent changes if everything is contiguously linked. Actual Count is the actual number of extent changes.
Logical Scan Fragmentation
Percentage of out-of-order pages returned from scanning the leaf pages of an index. This number is not relevant to heaps. An out-of-order page is one for which the next page indicated in an IAM is a page different from the page pointed to by the next page pointer in the leaf page.
Extent Scan Fragmentation
Percentage of out-of-order extents in scanning the leaf pages of an index. This number is not relevant to heaps. An out-of-order extent is one for which the extent that contains the current page for an index is not physically the next extent after the extent that contains the previous page for an index.
Note:
This number is meaningless when the index spans multiple files.
Avg. Bytes Free per Page
Average number of free bytes on the pages scanned. The larger the number, the less full the pages are. Lower numbers are better if the index will not have many random inserts. This number is also affected by row size; a large row size can cause a larger number.
Avg. Page density (full)
Average page density, as a percentage. This value takes into account row size. Therefore, the value is a more accurate indication of how full your pages are. The larger the percentage, the better.
"Got no time for the jibba jabba!"
-B.A. Baracus
May 28, 2008 at 4:36 pm
I think that you should use the dymanic management view sys.dm_db_index_physical_stats instead of DBCC SHOWCONTIG.
http://msdn.microsoft.com/en-us/library/ms188917.aspx
DBCC SHOWCONTIG will be removed in a future version of SQL Server.
http://msdn.microsoft.com/en-us/library/ms175008.aspx
I have a stored procedure that is using sys.dm_db_index_physical_stats that you could use to defragment your indexes.
http://ola.hallengren.com/sql-server-index-and-statistics-maintenance.html
You could also use the script in Books Online.
http://msdn.microsoft.com/en-us/library/ms188917.aspx
Ola Hallengren
May 28, 2008 at 11:29 pm
Thanks all,
I will start working on development servers and then will move to production servers.
Syed Sanaullah Khadri
DBA
May 28, 2008 at 11:47 pm
yes use sys.dm_db_index_physical_stats.
"Keep Trying"
May 29, 2008 at 3:40 am
Use this to view fragmentation in percent
SELECT
OBJECT_NAME(object_id) AS 'Object Name'
,index_id
,index_type_desc
,avg_fragmentation_in_percent
FROM
sys.dm_db_index_physical_stats (DB_ID(),NULL, NULL, NULL, 'LIMITED')
WHERE
avg_fragmentation_in_percent > 30
ORDER BY
OBJECT_NAME(object_id)
Basit Ali Farooq
MCITP Database Administrator
Microsoft Certified Professional Developer (Web Applications)
Microsoft Certified Database Administrator
Microsoft Certified Systems Engineer
Microsoft Certified Systems Administrator
CIW Security Analyst
Cisco Certified Network Associate
May 29, 2008 at 3:42 am
Or use this for fragmentation
USE MASTER
GO
SET QUOTED_IDENTIFIER OFF SET ANSI_NULLS ON
GO
CREATE PROCEDURE sp_DBA_DBCCShowFragAll
AS
BEGIN
DECLARE UserTables INSENSITIVE CURSOR
FOR
Select name FROM sysobjects --select table names
WHERE type = 'U'
ORDER BY name
FOR READ ONLY
OPEN UserTables
DECLARE @TableName varchar(50),
@MSG varchar(255),
@id int
FETCH NEXT FROM UserTables INTO @TableName --pass tbl names
WHILE (@@FETCH_STATUS = 0)--loop through tablenames
BEGIN
SELECT @MSG = 'DBCC SHOWCONTIG For table: ' + @TableName
PRINT @MSG --print some info
SET @id = object_id(@tablename)--set variable to pass
DBCC SHOWCONTIG (@id) --execute
PRINT ''
FETCH NEXT FROM UserTables INTO @TableName
END
CLOSE UserTables
DEALLOCATE UserTables
END
GO
SET QUOTED_IDENTIFIER OFF SET ANSI_NULLS ON
GO
Basit Ali Farooq
MCITP Database Administrator
Microsoft Certified Professional Developer (Web Applications)
Microsoft Certified Database Administrator
Microsoft Certified Systems Engineer
Microsoft Certified Systems Administrator
CIW Security Analyst
Cisco Certified Network Associate
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply