![from the adventureworks2012 database. from the adventureworks2012 database.](https://sxi.io/wp-content/uploads/2019/08/SSMS.png)
Starting with SQL Server 2012, statistics are not created by scanning all the rows in the table when a partitioned index is created or rebuilt. Doing so may cause degraded performance or excessive memory consumption during these operations.
![from the adventureworks2012 database. from the adventureworks2012 database.](https://www.mssqltips.com/tipimages2/2638_Attach1.jpg)
Creating and rebuilding nonaligned indexes on a table with more than 1,000 partitions is possible, but is not supported.in database because the 'PRIMARY' filegroup is full."
FROM THE ADVENTUREWORKS2012 DATABASE. FREE
So although the filegroup might have free pages available, the user can still encounter error 1105: "Could not allocate space for object.
![from the adventureworks2012 database. from the adventureworks2012 database.](https://i1.wp.com/www.techveze.com/wp-content/uploads/2015/04/70-462-f4.png)
The result set returned by the sys.dm_db_index_physical_stats function includes the following columns. For partitioned indexes, sys.dm_db_index_physical_stats also provides fragmentation information for each partition.
![from the adventureworks2012 database. from the adventureworks2012 database.](https://i3.ytimg.com/vi/NyP1Ev0dM3M/hqdefault.jpg)
By using the system function sys.dm_db_index_physical_stats, you can detect fragmentation in a specific index, all indexes on a table or indexed view, all indexes in a database, or all indexes in all databases. The first step in deciding which defragmentation method to use is to analyze the index to determine the degree of fragmentation. Compaction is based on the existing fill factor value. Reorganizing also compacts the index pages. It defragments the leaf level of clustered and non-clustered indexes on tables and views by physically reordering the leaf-level pages to match the logical, left to right, order of the leaf nodes. Reorganizing an index uses minimal system resources. When ALL is specified, all indexes on the table are dropped and rebuilt in a single transaction. This removes fragmentation, reclaims disk space by compacting the pages based on the specified or existing fill factor setting, and reorders the index rows in contiguous pages. Rebuilding an index drops and re-creates the index. For partitioned indexes built on a partition scheme, you can use either of these methods on a complete index or a single partition of an index. You can remedy index fragmentation by reorganizing or rebuilding an index. Heavily fragmented indexes can degrade query performance and cause your application to respond slowly. Fragmentation exists when indexes have pages in which the logical ordering, based on the key value, does not match the physical ordering inside the data file. Over time these modifications can cause the information in the index to become scattered in the database (fragmented). The SQL Server Database Engine automatically maintains indexes whenever insert, update, or delete operations are made to the underlying data.
FROM THE ADVENTUREWORKS2012 DATABASE. HOW TO
This topic describes how to reorganize or rebuild a fragmented index in SQL Server 2017 by using SQL Server Management Studio or Transact-SQL. Reorganize and Rebuild Indexes in the Database