November 4, 2008 at 4:12 pm
From what I can tell in 2005 I need to query sys.dm_db_index_physical_stats and check avg_fragmentation_in_percent. Simply put, if avg_fragmentation_in_percent = 30 then REBUILD.
In 2000 there was much more to consider. External fragmentation vs. Internal fragmentation. We checked the amount of External fragmentation and ran either IndexDefrag and Update Stats or DBREINDEX. For Interal fragmentation we ran DBREINDEX.
Is there no need to check External and Internal fragmentation any more and is it really as simple as checking only avg_fragmentation_in_percent and perhaps number of pages?
Thanks, Dave
November 4, 2008 at 5:44 pm
What do you mean by 'external 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
November 4, 2008 at 7:58 pm
I think people use different terms for external fragmentation. It occurs when extents are not contiguous. Check out these links for a better explanation.
You may not be able to see the first one. You might need a subscription to the magazine. If so let me know and I will copy and paste the article.
http://windowsitpro.com/article/articleid/14372/
http://www.databasejournal.com/features/mssql/article.php/1442901
November 4, 2008 at 8:03 pm
using that DMV you can get some more information that is useful such as the page length of the index, , partitioning, and you can look in syscolumns and other places to determine if the data type in the index is capable of being reindexed online or not (if you have Enterprise Ed.)
apart from that a common strategy out there is that between 5 and 25% avg. fragmentation reorg, above that rebuild, preferably online.
I add in a page length calculation, partition management, and whether or not I can rebuild online.
The DMV is a boatload faster than a full-blown dbcc showcontig, if you leverage it properly. Try to avoid putting any nulls into it and you'll see right away you improve the speed by an order of magnitude on the same hardware as your old routines.
Cheers!
~BOT
Craig Outcalt
November 4, 2008 at 11:57 pm
DBADave (11/4/2008)
I think people use different terms for external fragmentation. It occurs when extents are not contiguous. Check out these links for a better explanation.
Extent fragmentation is what that's usually called. External fragmentation (at least the way I see it frequently used) refers to fragmentation of the DB files at the file system level, and no amount of rebuilds is going to fix that.
I'm not sure why that, or the number of extents, isn't in the 2005 DMV
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
November 5, 2008 at 6:27 am
We have logic for partitions and for page length, but nothing for online rebuilds. I just began experimenting with online rebuilds yesterday and was surprised by how well it worked. It defragmented indexes just as good as an off-line rebuild. That's why I started questioning if avg_fragmentation_in_percent pertained to all types of fragmentations. For example, with nonclustered indexes is it referring to leaf-level fragmentation whereas with clustered indexes is it referring to page-level fragmentation. I'm also assuming that heaps should still be excluded from REBUILD and REORGANIZE, but I haven't been able to confirm if Rebuilding or Reorganizing a Heap in 2005 updates the table and column statistics.
Thanks
November 5, 2008 at 6:46 am
I just found some articles by Kalen. She describes Internal and External fragmentation better then I can. Unfortunately I now need to update our 2005 defragmentation scripts based upon her articles.
http://www.sqlmag.com/Article/ArticleID/48129/
In SQL Server 2000 and 7.0, DBCC SHOWCONTIG returns information about four types of fragmentation: internal fragmentation and three types of external fragmentation.(TheWeb sidebar"Going to Pieces," http://www.windowsitpro.com/ sqlserver, InstantDoc ID 48122 explains the difference between internal and external fragmentation in SQL Server and the relationship between the two.) But the values reported for two of these fragmentation types are meaningless when a table or index spans multiple files. In SQL Server 2005, all reported values are meaningful across multiple files. However, not every type of fragmentation is relevant to every structure.
In SQL Server 2005, the avg_page _space_used_in_percent value reports internal fragmentation.This float value is the average, across all pages, for one particular index level for one type of allocation unit in one partition.This is the only type of fragmentation that SQL Server 2005 reports for the LOB_DATA and ROW_OVERFLOW_DATA pages.
The avg_fragmentation_in_percent value reports external fragmentation.This float value represents the logical fragmentation for indexes or the extent fragmentation for heaps in the IN_ROW_ DATA allocation unit. SQL Server returns a value of 0 for LOB_DATA and ROW_ OVERFLOW_ DATA allocation units.
http://www.sqlmag.com/Article/ArticleID/48122
Fragmentation, an indication that data isn't as compact and contiguous as it could be, comes in two general types: internal and external. Internal fragmentation involves how much free space is left on your pages and how compact the data is. External fragmentation involves how contiguous the pages are.
November 5, 2008 at 9:57 am
DBADave (11/5/2008)
I just began experimenting with online rebuilds yesterday and was surprised by how well it worked. It defragmented indexes just as good as an off-line rebuild.
Sure. It's the same operation. Just with online there's a copy of the index left for queries to use while the new one is been built. It's not like rebuild/reorganise. Both online and offline index rebuilds are complete index rebuilds.
I'm also assuming that heaps should still be excluded from REBUILD and REORGANIZE, but I haven't been able to confirm if Rebuilding or Reorganizing a Heap in 2005 updates the table and column statistics.
You can't rebuild a heap in 2005. ALTER INDEX requires an index name and a heap doesn't have one.
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
November 5, 2008 at 10:00 am
Thanks Gail. Do they recommend rebuilding a HEAP in 2005 so the table stats get updated or is something else being done to the HEAP?
November 5, 2008 at 10:11 am
DBADave (11/5/2008)
Thanks Gail. Do they recommend rebuilding a HEAP in 2005 so the table stats get updated or is something else being done to the HEAP?
It's not possible to rebuild a heap in SQL 2005.
What do you mean by 'something else been done'?
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
November 5, 2008 at 12:59 pm
Sorry. I misread your previous heap post. I believe in the case of Update Statistics you still run it against a heap because column statistics are updated. I wasn't sure if REBUILD or REORGANIZE did something similar to a heap.
BOL provides an example on how to reduce fragmentation on a Heap, but I need to determine how to determine heap fragmentation in 2005.
November 5, 2008 at 4:40 pm
Can you help me understand why two rows are returned from DM_DB_INDEX_PHYSICAL_STATS where both rows have the same object_id and
_id (1), but alloc_unit_type_desc is LOB_DATA for one row and IN_ROW_DATA for the other. I believe LOB_DATA indicates a large object data type of either text, ntext, image, xml, varchar(max), nvarchar(max), varbinary(max), and CLR user-defined types.
When I looked at the clustered index it was defined as a single column primary key with an INT data type. Why would I see LOB_DATA for this table?
Object_Id Index_Id Index_Type_Desc Alloc_Unit_Type_Desk
1989582126 1 CLUSTERED INDEX IN_ROW_DATA
1989582126 1 CLUSTERED INDEX LOB_DATA
1989582126 2 NONCLUSTERED INDEX IN_ROW_DATA
1989582126 3 NONCLUSTERED INDEX IN_ROW_DATA
1989582126 4 NONCLUSTERED INDEX IN_ROW_DATA
CREATE TABLE [dbo].[schedule_queue](
[instance] [int] NOT NULL,
[pinstance] [int] NOT NULL,
[status] [char](1) NOT NULL,
[sched_dt_time] [datetime] NOT NULL,
[run_dt_time] [datetime] NULL,
[user_data] [varchar](255) NULL,
[actual_start_dt_time] [datetime] NULL,
[actual_stop_dt_time] [datetime] NULL,
[log_file_name] [varchar](1000) NULL,
[parent_qinstance] [int] NULL,
[parent_flag] [int] NULL,
[log_file] [image] NULL,
CONSTRAINT [PK_SCHEDULE_QUEUE] PRIMARY KEY CLUSTERED
(
[instance] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
November 6, 2008 at 8:21 am
DBADave (11/5/2008)
Thanks Gail. Do they recommend rebuilding a HEAP in 2005 so the table stats get updated or is something else being done to the HEAP?
Actually there are MANY reasons to not have a HEAP table at all. I highly recommend finding an appropriate clustered index and putting it on your HEAP tables. TEST to make sure you don't cause problems with that obviously.
At a minimum, I recommend occasionally creating a clustered index on HEAPS and then dropping that clustered index even if you don't want a CI long term. Note that if you have non-clustered indexes on said HEAP table you may want to drop those before creating the CI and then recreate them after dropping the CI.
Adding/removing the clustered index will remove forwarding pointers and compact the HEAP to contiguous pages (as best as possible given free contiguous space in the database). Both of these are desireable activities.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
November 6, 2008 at 8:33 am
Why don't you try this for updating the statistics
UPDATE STATISTICS DaveTable(anames)
WITH FULLSCAN, NORECOMPUTE
GO
Thanks!!
The_SQL_DBA
MCTS
"Quality is never an accident; it is always the result of high intention, sincere effort, intelligent direction and skillful execution; it represents the wise choice of many alternatives."
November 6, 2008 at 8:36 am
Why don't you try this for updating the statistics
UPDATE STATISTICS DaveTable(anames)
WITH FULLSCAN, NORECOMPUTE
GO
Be careful the above can take some time to run on a massive Database.
Viewing 15 posts - 1 through 15 (of 27 total)
You must be logged in to reply to this topic. Login to reply