复合索引的索引碎片高居不下

Question

有一张表建了复合索引，但是索引碎片一直高居不下，一直在70%，通过以下sql重建索引页没变化。

dbcc dbreindex(N'[dbo].[T_USER]', N'', 0)

请问这个正常么？能通过重建索引降低索引碎片么？

Answer 1

索引的列定义是怎么样的？使用了什么字段？数据量大不大？

---

Please Mark As Answer if it is helpful.

Answer 2

建索引最好看你的业务规则，比如索引字段建立在自增字段上，那么基本上没有碎片，除非删除数据，如果表数据只增不删

那么基本上没有索引碎片，但是如果你的索引字段是根据业务来的比如订单号，你的索引字段是从小到大排列

那么后插入的数据如果小于表当前的最后一条记录的订单号，那么后插入的数据就要排在前面，索引页就要移位

产生碎片，而自增ID一般跟业务无关，所以索引碎片会比较少

以上是个人见解，当然为了查询效率，将索引字段建在订单号上也无可厚非

Answer 3

Sql puts small table on shared extents, therefore no way to defrag index on that table.

Answer 4

HOTELCODE, COMPANYCODE, FLAG

根据这三个字段建立的复合索引，是根据sql的查询条件来定义的这个索引，就是这个索引的碎片即使重建索引，索引碎片也在60%~70%之间不下来。

数据量不大的，就两千多条数据，但这个表要跟其他表关联的。

Answer 5

这几个字段貌似都没有排序规律，比如从小到大，或从大到小

Answer 6

Sql puts small table on shared extents, therefore no way to defrag index on that table.

不一定

Answer 7

没有排序规律的，只是因为sql里的查询条件，为了让查询更快，才建立的这个复合索引。hotelcode是8位数的，包含字母和数字。companycode是公司名，flag就是1和0.这个是造成索引碎片下不来的原因么?

Answer 8

As I said, can't completely defrag index on small table which is on shared extents.

Answer 9

没有排序规律的，只是因为sql里的查询条件，为了让查询更快，才建立的这个复合索引。hotelcode是8位数的，包含字母和数字。companycode是公司名，flag就是1和0.这个是造成索引碎片下不来的原因么?

Fragmentation is nothing to do with values in indexed columns.

Answer 10

Could you pls. help to explain what is ' on shared extents' ? sorry for my poor understanding.

Answer 11

Sql puts small table on shared extents, therefore no way to defrag index on that table.

不一定

What do you mean? You can completely defrag index on shared extents? Like to learn how.

Answer 12

这几个字段貌似都没有排序规律，比如从小到大，或从大到小

Why not? As long as they are not blob. 

Answer 13

我说的排序规律是一定的,当然你说下面是有排序规律,我说的规律是没有一定的规律

hotelcode是8位数的

12345678

12345679

12345611

这个问题不争论了

LZ,关于这个问题:What do you mean? You can completely defrag index on shared extents

可以看一下我的文章:

SQLSERVER新建表的时候页面分配情况是怎样的？

如果不是都在统一区，那么页面就不能移动，rmiao大侠的意思

Answer 14

应该跟字段的值还是有关系的吧？比如说一个Index的大小放了900字节，在加上Include Column比如有3000个字节，那么这个PAGE应该只能放两条记录，会空下来很多，这个应该是页内碎片把。如果说错了请指正。

---

Please Mark As Answer if it is helpful.

Answer 15

我说的排序规律是一定的,当然你看过说下面是有排序规律,我说的规律是没有一定的规律

hotelcode是8位数的

12345678

12345679

12345611

这个问题不争论了

LZ,关于这个问题:What do you mean? You can completely defrag index on shared extents

可以看一下我的文章:

SQLSERVER新建表的时候页面分配情况是怎样的？

如果不是都在统一区，那么页面就不能移动，rmiao大侠的意思

谢谢哈，我懂了rmiao大侠的意思。还有个问题，我们在重建几张表的索引之后（为了降低索引碎片而重建索引-dbcc dbreindex），前端开发人员反映重建索引之后前端查询比较慢，在大约十个小时后，前台的查询才快起来。就是说重建索引之后当时前端查询比较慢，差不多十个小时后，前端查询才显示出重建索引的结果。 这个什么情况啊？

Answer 16

相信自己的测试结果，办法就是：重建完索引之后，叫前端给出sql，自己在ssms里执行

SET STATISTICS TIME ON 
SET STATISTICS PROFILE ON
SET STATISTICS IO ON 

自己测试，丰衣足食

Answer 17

应该跟字段的值还是有关系的吧？比如说一个Index的大小放了900字节，在加上Include Column比如有3000个字节，那么这个PAGE应该只能放两条记录，会空下来很多，这个应该是页内碎片把。如果说错了请指正。

---

Please Mark As Answer if it is helpful.

有道理

Answer 18

谢谢桦仔 下次重建索引的时候我测试下。

Answer 19

不客气o(∩_∩)o

Answer 20

我说的排序规律是一定的,当然你看过说下面是有排序规律,我说的规律是没有一定的规律

hotelcode是8位数的

12345678

12345679

12345611

这个问题不争论了

LZ,关于这个问题:What do you mean? You can completely defrag index on shared extents

可以看一下我的文章:

SQLSERVER新建表的时候页面分配情况是怎样的？

如果不是都在统一区，那么页面就不能移动，rmiao大侠的意思

谢谢哈，我懂了rmiao大侠的意思。还有个问题，我们在重建几张表的索引之后（为了降低索引碎片而重建索引-dbcc dbreindex），前端开发人员反映重建索引之后前端查询比较慢，在大约十个小时后，前台的查询才快起来。就是说重建索引之后当时前端查询比较慢，差不多十个小时后，前端查询才显示出重建索引的结果。 这个什么情况啊？

Reindex will update stats, sql optimizer may recreate execution plans based on new stats which takes time. Therefore first run of the query may take longer to get new plan, same query will run faster afterwards.

Answer 21

我说的排序规律是一定的,当然你看过说下面是有排序规律,我说的规律是没有一定的规律

hotelcode是8位数的

12345678

12345679

12345611

这个问题不争论了

LZ,关于这个问题:What do you mean? You can completely defrag index on shared extents

可以看一下我的文章:

SQLSERVER新建表的时候页面分配情况是怎样的？

如果不是都在统一区，那么页面就不能移动，rmiao大侠的意思

谢谢哈，我懂了rmiao大侠的意思。还有个问题，我们在重建几张表的索引之后（为了降低索引碎片而重建索引-dbcc dbreindex），前端开发人员反映重建索引之后前端查询比较慢，在大约十个小时后，前台的查询才快起来。就是说重建索引之后当时前端查询比较慢，差不多十个小时后，前端查询才显示出重建索引的结果。 这个什么情况啊？

Reindex will update stats, sql optimizer may recreate execution plans based on new stats which takes time. Therefore first run of the query may take longer to get new plan, same query will run faster afterwards.

原来如此啊，需要重新生成执行计划，恍然大悟啊，谢谢rmiao大侠啊。

Answer 22

Happy SQLing!