November 16, 2009 at 3:55 pm
Ok, the fragmentation I am talking about can be taken out of this query:
SELECT avg_fragmentation_in_percent
FROM sys.dm_db_index_physical_stats (DB_ID(), OBJECT_ID(N'dbo.Company'),
NULL, NULL, 'LIMITED') AS s
JOIN sys.indexes AS i ON s.object_id = i.object_id AND s.index_id = i.index_id
WHERE i.name = 'PK_Company'
BTW, why for the clustered index I am getting two records even though the mode is "LIMITED"?
This fragmentation gets removed by alter index reorganize statement.
November 16, 2009 at 5:30 pm
Roust_m (11/12/2009)
Ian Scarlett (11/12/2009)
4. Part of the table has been allocated in a mixed extent.as for the fourth, the size of the row is under 1000 bytes, so this should not happen.
Eh? What does row size have to do with it?
The first eight pages allocated to a table or indexed view come from mixed extents (unless you have trace flag 1118 on, or have rebuilt the clustered index since the table reached 25 pages in size).
One possibility to explain getting two records in LIMITED mode is that the table is partitioned. It would have helped had you posted the results of the query as well 😉
November 16, 2009 at 5:45 pm
Paul White (11/16/2009)
Roust_m (11/12/2009)
Ian Scarlett (11/12/2009)
4. Part of the table has been allocated in a mixed extent.as for the fourth, the size of the row is under 1000 bytes, so this should not happen.
Eh? What does row size have to do with it?
The first eight pages allocated to a table or indexed view come from mixed extents (unless you have trace flag 1118 on, or have rebuilt the clustered index since the table reached 25 pages in size).
One possibility to explain getting two records in LIMITED mode is that the table is partitioned. It would have helped had you posted the results of the query as well 😉
All the indexes get rebuilt weekly, so there should be no mixed extents. Besides the table size is pretty big, up to 12GB in some databases.
This is are the results of the query:
SELECT *
FROM sys.dm_db_index_physical_stats (DB_ID(), OBJECT_ID(N'dbo.Company'),
NULL, NULL, 'LIMITED') AS s
JOIN sys.indexes AS i ON s.object_id = i.object_id AND s.index_id = i.index_id
WHERE i.name = 'PK_Company'
database_id,object_id,index_id,partition_number,index_type_desc,alloc_unit_type_desc,
index_depth,index_level,avg_fragmentation_in_percent,fragment_count,avg_fragment_size_in_pages,page_count,
avg_page_space_used_in_percent,record_count,ghost_record_count,version_ghost_record_count,min_record_size_in_bytes,
max_record_size_in_bytes,avg_record_size_in_bytes,forwarded_record_count,compressed_page_count,object_id,name,index_id,type,
type_desc,is_unique,data_space_id,ignore_dup_key,is_primary_key,is_unique_constraint,
fill_factor,is_padded,is_disabled,is_hypothetical,allow_row_locks,allow_page_locks,has_filter,filter_definition
68,517576882,1,1,CLUSTERED INDEX,IN_ROW_DATA,
4,0,70.7971409579434,485143,1.37991272676304,669455,
NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,517576882,PK_Company,1,1,
CLUSTERED,1,1,0,1,0,
95,0,0,0,1,1,0,NULL
68,517576882,1,1,CLUSTERED INDEX,ROW_OVERFLOW_DATA,
1,0,0,NULL,NULL,1,
NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,517576882,PK_Company,1,1,
CLUSTERED,1,1,0,1,0,
95,0,0,0,1,1,0,NULL
(2 row(s) affected)
November 16, 2009 at 6:21 pm
You get two rows - one per type of allocation unit. One for IN_ROW_DATA and one for ROW_OVERFLOW_DATA. See http://msdn.microsoft.com/en-us/library/ms189051.aspx for descriptions.
November 17, 2009 at 8:06 am
Someone mentioned updates, and that has my vote for causing the fragmentation. If you update a variable field with a value that is longer than the original value, you have the potential of causing a page split because your total size of all rows on that page is now greater than will fit on said page. I have seen this type of stuff MANY times. A common scenario is inserting a row without having all of the values required, and then coming back later (often in the same sproc that did the original insert!!) and updating with newly acquired values for those missing columns.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
Viewing 5 posts - 16 through 19 (of 19 total)
You must be logged in to reply to this topic. Login to reply