Identifying cause of index fragmentation

Question

Is it possible to identify when an index becomes fragmented or what causes the fragmentation?

I'm trying to identify the cause of an issue I'm having with a particular index.

Answer 1

SELECT avg_fragmentation_in_percent FROM sys.dm_db_index_physical_stats
( 
  DB_ID('testdb'),
  OBJECT_ID('dbo.T1'),
  1,
  NULL,
  NULL
);

I got the result 97.488, meaning that there are over 97
percents of out-of-order pages. 

It is possible the pages splits or DBCC SHINKDATABASE cause the fragmentation

http://logicalread.solarwinds.com/fix-sql-server-index-fragmentation-mc11/#.WCxEzvl96Uk

---

Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

MS SQL optimization: MS SQL Development and Optimization
MS SQL Consulting: Large scale of database and data cleansing
Remote DBA Services: Improves MS SQL Database Performance
SQL Server Integration Services: Business Intelligence

Answer 2

you can go through this excellent blog post by Jonathan of SQLSkills;

https://www.sqlskills.com/blogs/jonathan/tracking-problematic-pages-splits-in-sql-server-2012-extended-events-no-really-this-time/

Answer 3

Is it possible to identify when an index becomes fragmented or what causes the fragmentation?

I'm trying to identify the cause of an issue I'm having with a particular index.

This would be unnecessary attempt I would say unless your table/index is getting fragmented completely within day or two. Normally daily DML operation causes fragmentation and this ofcourse includes some other things as well.

---

Cheers,

Shashank

Please mark this reply as answer if it solved your issue or vote as helpful if it helped so that other forum members can benefit from it

My TechNet Wiki Articles
MVP

Answer 4

Check fillfactor for the index.

Read this blog post by Brad Schultz 

http://bradsruminations.blogspot.co.il/2010/09/t-sql-tuesday-010-little-known-index.html

if you want to relax a bit and have a good laugh as well as learn something.

---

For every expert, there is an equal and opposite expert. - Becker's Law


My blog

My TechNet articles