I am filling my table in my SQL Server database with some data and I have partitions. I have one clustered index in the table. The problem I am facing is that I am not getting any fragmentation which I need in order to test. After reading some articles, I came to conclusion that I need to use file groups, but I am not sure how to achieve that.
SQL Server – Testing Index Fragmentation
sql serversql-server-2008
Related Question
- Sql-server – Fragmented clustered primary key (sequential GUID) index after processing – SQL Server
- SQL Server Index – Difference Between Average and Total Fragmentation
- SQL Server – Effect of Logical Fragmentation on Range Scan
- Sql-server – Proper table design for sparse primary key
- SQL Server – Index Fragmentation After Disabling Page-Level Locking
- SQL Server Indexes – How to Fix Indexes to Reduce Fragmentation Percentage
- Sql-server – the correct and performant pattern for loading (and querying) a large clustered columnstore table
Best Answer
If you're trying to artificially induce fragmentation, insert the data "backwards". That is, if your clustering key is an integer column ascending, load the data in descending order by that same integer column. You should get page splits aplenty.