why the index can take a long time to create on small table?
0
votes
0
answers
182
views
SQL Server 2019
Index was created with online = on.
Table has about 35000 rows with 7 columns with int, uniqueidentifier and datetime data types.
Table has only clustered index.
sp_whoisactive doesn't show any waits and locks during index creation process.
-sql
CREATE TABLE Table(
ID uniqueidentifier NOT NULL,
BeginTime datetime NOT NULL,
EndTime datetime NULL,
CreatePost uniqueidentifier NOT NULL,
ClosePost uniqueidentifier NULL,
OperationProtocol uniqueidentifier NOT NULL,
StageType int NOT NULL,
CONSTRAINT PK_Table PRIMARY KEY CLUSTERED
(
ID ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
)
CREATE INDEX IXNC_Table ON Table(OperationProtocol, StageType) INCLUDE (BeginTime) WITH (FILLFACTOR=100, ONLINE=ON);
I stopped creation in ssms, but stopping took more than 5 minutes and killing session didn't help.
I guess that the problem is in databases metadata but how can I check and fix it?
P.S. Next day on a database with a low load, an index was created without any difficalties.
P.P.S Can someone suggest which way to look to find the cause of this issue?
Asked by Il26
(1 rep)
Aug 10, 2023, 11:19 AM
Last activity: Aug 11, 2023, 03:47 AM
Last activity: Aug 11, 2023, 03:47 AM