env:Windows Server 2016
SQL Server 2016 SP2
有一張表格要補上clustered index與non-clustered index。這是一張既有表格且有4億筆資料,裡面也有一支non-clustered index。
統計資訊的更新程度是80000000筆。
1.執行create non-clustered index後再執行clustered index
command:
CREATE NONCLUSTERED INDEX [IX_TESTId_CreatOn]
ON [dbo].[TESTTB] ([TESTId],[CreatOn])
INCLUDE ([Id],[LISTId],[Amount],[Revenue],[Session])
WITH(ONLINE=ON, DATA_COMPRESSION=ON)
GO
CREATE CLUSTERED INDEX [IX_CreatOn] ON [dbo].[TESTTB]
(
[CreatOn] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = ON, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, DATA_COMPRESSION=PAGE) ON [PRIMARY]
GO
在建立clustered index花費許多時間,主要是掃描既有索引造成。利用SSMS建立cluster index會提示其他所引也會重建。
執行次序 | servername | db_date | table_name | idx_id | index_name | row_count | data_used_mb | Exec(Mins) |
1 | TESTDB01 | TESTDB01 | dbo.TESTTB | 7 | IX_TESTId_CreatOn | 404502153 | 17368 | 51 |
2 | TESTDB01 | TESTDB01 | dbo.TESTTB | 1 | IX_CreatOn | 404502153 | 47358 | 94 |
2.這次讓clustered index優先建立
command:
CREATE CLUSTERED INDEX [IX_CreatOn] ON [dbo].[TESTTB]
(
[CreatOn] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = ON, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, DATA_COMPRESSION=PAGE) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [IX_TESTId_CreatOn]
ON [dbo].[TESTTB] ([TESTId],[CreatOn])
INCLUDE ([Id],[LISTId],[Amount],[Revenue],[Session])
WITH(ONLINE=ON, DATA_COMPRESSION=ON)
GO
這次可以發現建立clustered index時間大幅縮短。
執行次序 | servername | db_date | table_name | idx_id | index_name | row_count | data_used_mb | Exec(Mins) |
1 | TESTDB01 | TESTDB01 | dbo.TESTTB | 1 | IX_CreatOn | 404502153 | 47358 | 50 |
2 | TESTDB01 | TESTDB01 | dbo.TESTTB | 7 | IX_TESTId_CreatOn | 404502153 | 17368 | 43 |
結論:
若要在既有表格建立索引,clustered index要優先處理。
若表格內已經有許多non-clustered index,是否要建立空表後再進行資料複製移轉?這是值得考慮的問題。