index fragmentation basics
-
Hi All,
I have a basic question on index fragmentation. Even after index rebuild , fragmentation level doesn't change.
Any reason why? I am thinking about the number of pages but I am not able convince myself the reasoning behind why it is doesn't remove the fragmentation. I used Ola hallengren and even manual index rebuild commands but nothing changed.
Below is the sample code.
use master
go
create database test_db
go
use test_db
go
CREATE TABLE [dbo].[t1](
[c1] [int] NULL,
[c2] [char](1000) NULL
)
GO
CREATE CLUSTERED INDEX [cl_idx_t1] ON [dbo].[t1]
(
[c1] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
GO
INSERT [dbo].[t1] ([c1], [c2]) VALUES (1, N'a ')
GO
INSERT [dbo].[t1] ([c1], [c2]) VALUES (2, N'b ')
GO
INSERT [dbo].[t1] ([c1], [c2]) VALUES (3, N'c ')
GO
INSERT [dbo].[t1] ([c1], [c2]) VALUES (4, N'd ')
GO
INSERT [dbo].[t1] ([c1], [c2]) VALUES (5, N'e ')
GO
INSERT [dbo].[t1] ([c1], [c2]) VALUES (6, N'f ')
GO
INSERT [dbo].[t1] ([c1], [c2]) VALUES (7, N'g ')
GO
INSERT [dbo].[t1] ([c1], [c2]) VALUES (8, N'h ')
GO
-------------------------------------------------
CREATE TABLE [dbo].[t2](
[c1] [int] NULL,
[c2] [char](100) NULL
)
GO
CREATE CLUSTERED INDEX [cl_idx_t2] ON [dbo].[t2]
(
[c1] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
GO
INSERT [dbo].[t2] ([c1], [c2]) VALUES (1, N'a ')
GO
INSERT [dbo].[t2] ([c1], [c2]) VALUES (2, N'b ')
GO
INSERT [dbo].[t2] ([c1], [c2]) VALUES (3, N'c ')
GO
INSERT [dbo].[t2] ([c1], [c2]) VALUES (4, N'd ')
GO
INSERT [dbo].[t2] ([c1], [c2]) VALUES (4, N'd ')
GO
INSERT [dbo].[t2] ([c1], [c2]) VALUES (4, N'd ')
GO
INSERT [dbo].[t2] ([c1], [c2]) VALUES (4, N'd ')
GO
INSERT [dbo].[t2] ([c1], [c2]) VALUES (4, N'd ')
GO
INSERT [dbo].[t2] ([c1], [c2]) VALUES (4, N'd ')
GO
INSERT [dbo].[t2] ([c1], [c2]) VALUES (4, N'd ')
GO
INSERT [dbo].[t2] ([c1], [c2]) VALUES (4, N'd ')
GO
INSERT [dbo].[t2] ([c1], [c2]) VALUES (4, N'd ')
GO
INSERT [dbo].[t2] ([c1], [c2]) VALUES (4, N'd ')
GO
INSERT [dbo].[t2] ([c1], [c2]) VALUES (4, N'd ')
GO
INSERT [dbo].[t2] ([c1], [c2]) VALUES (4, N'd ')
GO
INSERT [dbo].[t2] ([c1], [c2]) VALUES (4, N'd ')
GO
INSERT [dbo].[t2] ([c1], [c2]) VALUES (4, N'd ')
GO
INSERT [dbo].[t2] ([c1], [c2]) VALUES (4, N'd ')
GO
INSERT [dbo].[t2] ([c1], [c2]) VALUES (4, N'd ')
GO
INSERT [dbo].[t2] ([c1], [c2]) VALUES (4, N'd ')
GO
INSERT [dbo].[t2] ([c1], [c2]) VALUES (4, N'd ')
GO
INSERT [dbo].[t2] ([c1], [c2]) VALUES (4, N'd ')
GO
INSERT [dbo].[t2] ([c1], [c2]) VALUES (4, N'd ')
GO
INSERT [dbo].[t2] ([c1], [c2]) VALUES (4, N'd ')
GO
INSERT [dbo].[t2] ([c1], [c2]) VALUES (4, N'd ')
GO
INSERT [dbo].[t2] ([c1], [c2]) VALUES (4, N'd ')
GO
INSERT [dbo].[t2] ([c1], [c2]) VALUES (4, N'd ')
GO
INSERT [dbo].[t2] ([c1], [c2]) VALUES (4, N'd ')
GO
INSERT [dbo].[t2] ([c1], [c2]) VALUES (4, N'd ')
GO
INSERT [dbo].[t2] ([c1], [c2]) VALUES (4, N'd ')
GO
INSERT [dbo].[t2] ([c1], [c2]) VALUES (4, N'd ')
GO
INSERT [dbo].[t2] ([c1], [c2]) VALUES (4, N'd ')
GO
INSERT [dbo].[t2] ([c1], [c2]) VALUES (4, N'd ')
GO
INSERT [dbo].[t2] ([c1], [c2]) VALUES (4, N'd ')
GO
INSERT [dbo].[t2] ([c1], [c2]) VALUES (4, N'd ')
GO
INSERT [dbo].[t2] ([c1], [c2]) VALUES (4, N'd ')
GO
INSERT [dbo].[t2] ([c1], [c2]) VALUES (4, N'd ')
GO
INSERT [dbo].[t2] ([c1], [c2]) VALUES (4, N'd ')
GO
INSERT [dbo].[t2] ([c1], [c2]) VALUES (4, N'd ')
GO
INSERT [dbo].[t2] ([c1], [c2]) VALUES (4, N'd ')
GO
INSERT [dbo].[t2] ([c1], [c2]) VALUES (4, N'd ')
GO
INSERT [dbo].[t2] ([c1], [c2]) VALUES (4, N'd ')
GO
INSERT [dbo].[t2] ([c1], [c2]) VALUES (4, N'd ')
GO
INSERT [dbo].[t2] ([c1], [c2]) VALUES (4, N'd ')
GO
INSERT [dbo].[t2] ([c1], [c2]) VALUES (4, N'd ')
GO
INSERT [dbo].[t2] ([c1], [c2]) VALUES (4, N'd ')
GO
INSERT [dbo].[t2] ([c1], [c2]) VALUES (4, N'd ')
GO
INSERT [dbo].[t2] ([c1], [c2]) VALUES (4, N'd ')
GO
INSERT [dbo].[t2] ([c1], [c2]) VALUES (4, N'd ')
GO
INSERT [dbo].[t2] ([c1], [c2]) VALUES (4, N'd ')
GO
INSERT [dbo].[t2] ([c1], [c2]) VALUES (4, N'd ')
GO
INSERT [dbo].[t2] ([c1], [c2]) VALUES (4, N'd ')
GO
INSERT [dbo].[t2] ([c1], [c2]) VALUES (4, N'd ')
GO
INSERT [dbo].[t2] ([c1], [c2]) VALUES (4, N'd ')
GO
INSERT [dbo].[t2] ([c1], [c2]) VALUES (4, N'd ')
GO
INSERT [dbo].[t2] ([c1], [c2]) VALUES (4, N'd ')
GO
INSERT [dbo].[t2] ([c1], [c2]) VALUES (4, N'd ')
GO
INSERT [dbo].[t2] ([c1], [c2]) VALUES (4, N'd ')
GO
INSERT [dbo].[t2] ([c1], [c2]) VALUES (4, N'd ')
GO
INSERT [dbo].[t2] ([c1], [c2]) VALUES (4, N'd ')
GO
INSERT [dbo].[t2] ([c1], [c2]) VALUES (4, N'd ')
GO
INSERT [dbo].[t2] ([c1], [c2]) VALUES (4, N'd ')
GO
INSERT [dbo].[t2] ([c1], [c2]) VALUES (4, N'd ')
GO
INSERT [dbo].[t2] ([c1], [c2]) VALUES (4, N'd ')
GO
INSERT [dbo].[t2] ([c1], [c2]) VALUES (4, N'd ')
GO
INSERT [dbo].[t2] ([c1], [c2]) VALUES (4, N'd ')
GO
INSERT [dbo].[t2] ([c1], [c2]) VALUES (4, N'd ')
GO
INSERT [dbo].[t2] ([c1], [c2]) VALUES (5, N'e ')
GO
-------------------------------------------------
CREATE TABLE [dbo].[t3](
[c1] [int] NULL,
[c2] [char](10) NULL
)
GO
CREATE CLUSTERED INDEX [cl_idx_t3] ON [dbo].[t3]
(
[c1] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
GO
INSERT [dbo].[t3] ([c1], [c2]) VALUES (1, N'a ')
GO
INSERT [dbo].[t3] ([c1], [c2]) VALUES (1, N'a ')
GO
INSERT [dbo].[t3] ([c1], [c2]) VALUES (1, N'a ')
GO
INSERT [dbo].[t3] ([c1], [c2]) VALUES (1, N'a ')
GO
INSERT [dbo].[t3] ([c1], [c2]) VALUES (1, N'a ')
GO
INSERT [dbo].[t3] ([c1], [c2]) VALUES (1, N'a ')
GO
INSERT [dbo].[t3] ([c1], [c2]) VALUES (2, N'b ')
GO
INSERT [dbo].[t3] ([c1], [c2]) VALUES (2, N'b ')
GO
INSERT [dbo].[t3] ([c1], [c2]) VALUES (3, N'c ')
GO
INSERT [dbo].[t3] ([c1], [c2]) VALUES (3, N'c ')
GO
INSERT [dbo].[t3] ([c1], [c2]) VALUES (3, N'c ')
GO
INSERT [dbo].[t3] ([c1], [c2]) VALUES (3, N'c ')
GO
INSERT [dbo].[t3] ([c1], [c2]) VALUES (3, N'c ')
GO
INSERT [dbo].[t3] ([c1], [c2]) VALUES (3, N'c ')
GO
INSERT [dbo].[t3] ([c1], [c2]) VALUES (3, N'c ')
GO
INSERT [dbo].[t3] ([c1], [c2]) VALUES (3, N'c ')
GO
INSERT [dbo].[t3] ([c1], [c2]) VALUES (3, N'c ')
GO
INSERT [dbo].[t3] ([c1], [c2]) VALUES (3, N'c ')
GO
INSERT [dbo].[t3] ([c1], [c2]) VALUES (3, N'c ')
GO
INSERT [dbo].[t3] ([c1], [c2]) VALUES (3, N'c ')
GO
INSERT [dbo].[t3] ([c1], [c2]) VALUES (3, N'c ')
GO
INSERT [dbo].[t3] ([c1], [c2]) VALUES (3, N'c ')
GO
INSERT [dbo].[t3] ([c1], [c2]) VALUES (3, N'c ')
GO
INSERT [dbo].[t3] ([c1], [c2]) VALUES (3, N'c ')
GO
INSERT [dbo].[t3] ([c1], [c2]) VALUES (3, N'c ')
GO
INSERT [dbo].[t3] ([c1], [c2]) VALUES (3, N'c ')
GO
INSERT [dbo].[t3] ([c1], [c2]) VALUES (3, N'c ')
GO
INSERT [dbo].[t3] ([c1], [c2]) VALUES (3, N'c ')
GO
INSERT [dbo].[t3] ([c1], [c2]) VALUES (3, N'c ')
GO
INSERT [dbo].[t3] ([c1], [c2]) VALUES (3, N'c ')
GO
INSERT [dbo].[t3] ([c1], [c2]) VALUES (3, N'c ')
GO
INSERT [dbo].[t3] ([c1], [c2]) VALUES (3, N'c ')
GO
INSERT [dbo].[t3] ([c1], [c2]) VALUES (3, N'c ')
GO
INSERT [dbo].[t3] ([c1], [c2]) VALUES (3, N'c ')
GO
INSERT [dbo].[t3] ([c1], [c2]) VALUES (3, N'c ')
GO
INSERT [dbo].[t3] ([c1], [c2]) VALUES (3, N'c ')
GO
INSERT [dbo].[t3] ([c1], [c2]) VALUES (3, N'c ')
GO
INSERT [dbo].[t3] ([c1], [c2]) VALUES (3, N'c ')
GO
INSERT [dbo].[t3] ([c1], [c2]) VALUES (3, N'c ')
GO
INSERT [dbo].[t3] ([c1], [c2]) VALUES (3, N'c ')
GO
INSERT [dbo].[t3] ([c1], [c2]) VALUES (3, N'c ')
GO
INSERT [dbo].[t3] ([c1], [c2]) VALUES (3, N'c ')
GO
INSERT [dbo].[t3] ([c1], [c2]) VALUES (3, N'c ')
GO
INSERT [dbo].[t3] ([c1], [c2]) VALUES (3, N'c ')
GO
INSERT [dbo].[t3] ([c1], [c2]) VALUES (3, N'c ')
GO
INSERT [dbo].[t3] ([c1], [c2]) VALUES (3, N'c ')
GO
INSERT [dbo].[t3] ([c1], [c2]) VALUES (3, N'c ')
GO
INSERT [dbo].[t3] ([c1], [c2]) VALUES (3, N'c ')
GO
INSERT [dbo].[t3] ([c1], [c2]) VALUES (3, N'c ')
GO
INSERT [dbo].[t3] ([c1], [c2]) VALUES (3, N'c ')
GO
INSERT [dbo].[t3] ([c1], [c2]) VALUES (3, N'c ')
GO
INSERT [dbo].[t3] ([c1], [c2]) VALUES (3, N'c ')
GO
INSERT [dbo].[t3] ([c1], [c2]) VALUES (3, N'c ')
GO
INSERT [dbo].[t3] ([c1], [c2]) VALUES (3, N'c ')
GO
INSERT [dbo].[t3] ([c1], [c2]) VALUES (3, N'c ')
GO
INSERT [dbo].[t3] ([c1], [c2]) VALUES (3, N'c ')
GO
INSERT [dbo].[t3] ([c1], [c2]) VALUES (3, N'c ')
GO
INSERT [dbo].[t3] ([c1], [c2]) VALUES (3, N'c ')
GO
INSERT [dbo].[t3] ([c1], [c2]) VALUES (3, N'c ')
GO
INSERT [dbo].[t3] ([c1], [c2]) VALUES (3, N'c ')
GO
INSERT [dbo].[t3] ([c1], [c2]) VALUES (3, N'c ')
GO
INSERT [dbo].[t3] ([c1], [c2]) VALUES (3, N'c ')
GO
INSERT [dbo].[t3] ([c1], [c2]) VALUES (3, N'c ')
GO
INSERT [dbo].[t3] ([c1], [c2]) VALUES (3, N'c ')
GO
INSERT [dbo].[t3] ([c1], [c2]) VALUES (3, N'c ')
GO
INSERT [dbo].[t3] ([c1], [c2]) VALUES (3, N'c ')
GO
INSERT [dbo].[t3] ([c1], [c2]) VALUES (3, N'c ')
GO
INSERT [dbo].[t3] ([c1], [c2]) VALUES (3, N'c ')
GO
INSERT [dbo].[t3] ([c1], [c2]) VALUES (3, N'c ')
GO
INSERT [dbo].[t3] ([c1], [c2]) VALUES (3, N'c ')
GO
INSERT [dbo].[t3] ([c1], [c2]) VALUES (3, N'c ')
GO
INSERT [dbo].[t3] ([c1], [c2]) VALUES (3, N'c ')
GO
INSERT [dbo].[t3] ([c1], [c2]) VALUES (3, N'c ')
GO
INSERT [dbo].[t3] ([c1], [c2]) VALUES (3, N'c ')
GO
INSERT [dbo].[t3] ([c1], [c2]) VALUES (3, N'c ')
GO
INSERT [dbo].[t3] ([c1], [c2]) VALUES (3, N'c ')
GO
INSERT [dbo].[t3] ([c1], [c2]) VALUES (3, N'c ')
GO
INSERT [dbo].[t3] ([c1], [c2]) VALUES (3, N'c ')
GO
INSERT [dbo].[t3] ([c1], [c2]) VALUES (3, N'c ')
GO
INSERT [dbo].[t3] ([c1], [c2]) VALUES (3, N'c ')
GO
INSERT [dbo].[t3] ([c1], [c2]) VALUES (3, N'c ')
GO
INSERT [dbo].[t3] ([c1], [c2]) VALUES (3, N'c ')
GO
INSERT [dbo].[t3] ([c1], [c2]) VALUES (3, N'c ')
GO
INSERT [dbo].[t3] ([c1], [c2]) VALUES (3, N'c ')
GO
INSERT [dbo].[t3] ([c1], [c2]) VALUES (3, N'c ')
GO
INSERT [dbo].[t3] ([c1], [c2]) VALUES (3, N'c ')
GO
INSERT [dbo].[t3] ([c1], [c2]) VALUES (3, N'c ')
GO
INSERT [dbo].[t3] ([c1], [c2]) VALUES (3, N'c ')
GO
INSERT [dbo].[t3] ([c1], [c2]) VALUES (3, N'c ')
GO
INSERT [dbo].[t3] ([c1], [c2]) VALUES (3, N'c ')
GO
INSERT [dbo].[t3] ([c1], [c2]) VALUES (3, N'c ')
GO
INSERT [dbo].[t3] ([c1], [c2]) VALUES (3, N'c ')
GO
INSERT [dbo].[t3] ([c1], [c2]) VALUES (3, N'c ')
GO
INSERT [dbo].[t3] ([c1], [c2]) VALUES (3, N'c ')
GO
INSERT [dbo].[t3] ([c1], [c2]) VALUES (3, N'c ')
GO
INSERT [dbo].[t3] ([c1], [c2]) VALUES (3, N'c ')
GO
INSERT [dbo].[t3] ([c1], [c2]) VALUES (3, N'c ')
GO
INSERT [dbo].[t3] ([c1], [c2]) VALUES (3, N'c ')
GO
INSERT [dbo].[t3] ([c1], [c2]) VALUES (3, N'c ')
GO
INSERT [dbo].[t3] ([c1], [c2]) VALUES (3, N'c ')
GO
INSERT [dbo].[t3] ([c1], [c2]) VALUES (3, N'c ')
GO
INSERT [dbo].[t3] ([c1], [c2]) VALUES (3, N'c ')
GO
INSERT [dbo].[t3] ([c1], [c2]) VALUES (3, N'c ')
GO
INSERT [dbo].[t3] ([c1], [c2]) VALUES (3, N'c ')
GO
INSERT [dbo].[t3] ([c1], [c2]) VALUES (3, N'c ')
GO
INSERT [dbo].[t3] ([c1], [c2]) VALUES (3, N'c ')
GO
INSERT [dbo].[t3] ([c1], [c2]) VALUES (3, N'c ')
GO
INSERT [dbo].[t3] ([c1], [c2]) VALUES (3, N'c ')
GO
INSERT [dbo].[t3] ([c1], [c2]) VALUES (3, N'c ')
GO
INSERT [dbo].[t3] ([c1], [c2]) VALUES (3, N'c ')
GO
INSERT [dbo].[t3] ([c1], [c2]) VALUES (3, N'c ')
GO
INSERT [dbo].[t3] ([c1], [c2]) VALUES (3, N'c ')
GO
INSERT [dbo].[t3] ([c1], [c2]) VALUES (3, N'c ')
GO
INSERT [dbo].[t3] ([c1], [c2]) VALUES (3, N'c ')
GO
INSERT [dbo].[t3] ([c1], [c2]) VALUES (3, N'c ')
GO
INSERT [dbo].[t3] ([c1], [c2]) VALUES (3, N'c ')
GO
INSERT [dbo].[t3] ([c1], [c2]) VALUES (3, N'c ')
GO
INSERT [dbo].[t3] ([c1], [c2]) VALUES (3, N'c ')
GO
INSERT [dbo].[t3] ([c1], [c2]) VALUES (3, N'c ')
GO
INSERT [dbo].[t3] ([c1], [c2]) VALUES (3, N'c ')
GO
INSERT [dbo].[t3] ([c1], [c2]) VALUES (3, N'c ')
GO
INSERT [dbo].[t3] ([c1], [c2]) VALUES (3, N'c ')
GO
INSERT [dbo].[t3] ([c1], [c2]) VALUES (3, N'c ')
GO
INSERT [dbo].[t3] ([c1], [c2]) VALUES (3, N'c ')
GO
INSERT [dbo].[t3] ([c1], [c2]) VALUES (3, N'c ')
GO
INSERT [dbo].[t3] ([c1], [c2]) VALUES (3, N'c ')
GO
INSERT [dbo].[t3] ([c1], [c2]) VALUES (3, N'c ')
GO
INSERT [dbo].[t3] ([c1], [c2]) VALUES (3, N'c ')
GO
INSERT [dbo].[t3] ([c1], [c2]) VALUES (3, N'c ')
GO
INSERT [dbo].[t3] ([c1], [c2]) VALUES (3, N'c ')
GO
INSERT [dbo].[t3] ([c1], [c2]) VALUES (3, N'c ')
GO
INSERT [dbo].[t3] ([c1], [c2]) VALUES (3, N'c ')
GO
INSERT [dbo].[t3] ([c1], [c2]) VALUES (3, N'c ')
GO
INSERT [dbo].[t3] ([c1], [c2]) VALUES (3, N'c ')
GO
INSERT [dbo].[t3] ([c1], [c2]) VALUES (3, N'c ')
GO
INSERT [dbo].[t3] ([c1], [c2]) VALUES (3, N'c ')
GO
INSERT [dbo].[t3] ([c1], [c2]) VALUES (3, N'c ')
GO
INSERT [dbo].[t3] ([c1], [c2]) VALUES (3, N'c ')
GO
INSERT [dbo].[t3] ([c1], [c2]) VALUES (3, N'c ')
GO
INSERT [dbo].[t3] ([c1], [c2]) VALUES (3, N'c ')
GO
INSERT [dbo].[t3] ([c1], [c2]) VALUES (3, N'c ')
GO
INSERT [dbo].[t3] ([c1], [c2]) VALUES (3, N'c ')
GO
INSERT [dbo].[t3] ([c1], [c2]) VALUES (3, N'c ')
GO
INSERT [dbo].[t3] ([c1], [c2]) VALUES (3, N'c ')
GO
INSERT [dbo].[t3] ([c1], [c2]) VALUES (3, N'c ')
GO
INSERT [dbo].[t3] ([c1], [c2]) VALUES (3, N'c ')
GO
INSERT [dbo].[t3] ([c1], [c2]) VALUES (3, N'c ')
GO
INSERT [dbo].[t3] ([c1], [c2]) VALUES (3, N'c ')
GO
INSERT [dbo].[t3] ([c1], [c2]) VALUES (3, N'c ')
GO
INSERT [dbo].[t3] ([c1], [c2]) VALUES (3, N'c ')
GO
INSERT [dbo].[t3] ([c1], [c2]) VALUES (3, N'c ')
GO
INSERT [dbo].[t3] ([c1], [c2]) VALUES (3, N'c ')
GO
INSERT [dbo].[t3] ([c1], [c2]) VALUES (3, N'c ')
GO
INSERT [dbo].[t3] ([c1], [c2]) VALUES (3, N'c ')
GO
INSERT [dbo].[t3] ([c1], [c2]) VALUES (3, N'c ')
GO
INSERT [dbo].[t3] ([c1], [c2]) VALUES (3, N'c ')
GO
INSERT [dbo].[t3] ([c1], [c2]) VALUES (3, N'c ')
GO
INSERT [dbo].[t3] ([c1], [c2]) VALUES (3, N'c ')
GO
INSERT [dbo].[t3] ([c1], [c2]) VALUES (3, N'c ')
GO
INSERT [dbo].[t3] ([c1], [c2]) VALUES (3, N'c ')
GO
INSERT [dbo].[t3] ([c1], [c2]) VALUES (3, N'c ')
GO
INSERT [dbo].[t3] ([c1], [c2]) VALUES (3, N'c ')
GO
INSERT [dbo].[t3] ([c1], [c2]) VALUES (3, N'c ')
GO
INSERT [dbo].[t3] ([c1], [c2]) VALUES (3, N'c ')
GO
INSERT [dbo].[t3] ([c1], [c2]) VALUES (3, N'c ')
GO
INSERT [dbo].[t3] ([c1], [c2]) VALUES (3, N'c ')
GO
INSERT [dbo].[t3] ([c1], [c2]) VALUES (3, N'c ')
GO
INSERT [dbo].[t3] ([c1], [c2]) VALUES (3, N'c ')
GO
INSERT [dbo].[t3] ([c1], [c2]) VALUES (3, N'c ')
GO
INSERT [dbo].[t3] ([c1], [c2]) VALUES (3, N'c ')
GO
INSERT [dbo].[t3] ([c1], [c2]) VALUES (3, N'c ')
GO
INSERT [dbo].[t3] ([c1], [c2]) VALUES (3, N'c ')
GO
INSERT [dbo].[t3] ([c1], [c2]) VALUES (3, N'c ')
GO
INSERT [dbo].[t3] ([c1], [c2]) VALUES (3, N'c ')
GO
INSERT [dbo].[t3] ([c1], [c2]) VALUES (3, N'c ')
GO
INSERT [dbo].[t3] ([c1], [c2]) VALUES (3, N'c ')
GO
INSERT [dbo].[t3] ([c1], [c2]) VALUES (3, N'c ')
GO
INSERT [dbo].[t3] ([c1], [c2]) VALUES (3, N'c ')
GO
INSERT [dbo].[t3] ([c1], [c2]) VALUES (3, N'c ')
GO
INSERT [dbo].[t3] ([c1], [c2]) VALUES (3, N'c ')
GO
INSERT [dbo].[t3] ([c1], [c2]) VALUES (3, N'c ')
GO
INSERT [dbo].[t3] ([c1], [c2]) VALUES (3, N'c ')
GO
INSERT [dbo].[t3] ([c1], [c2]) VALUES (3, N'c ')
GO
INSERT [dbo].[t3] ([c1], [c2]) VALUES (3, N'c ')
GO
INSERT [dbo].[t3] ([c1], [c2]) VALUES (3, N'c ')
GO
INSERT [dbo].[t3] ([c1], [c2]) VALUES (3, N'c ')
GO
INSERT [dbo].[t3] ([c1], [c2]) VALUES (3, N'c ')
GO
INSERT [dbo].[t3] ([c1], [c2]) VALUES (3, N'c ')
GO
INSERT [dbo].[t3] ([c1], [c2]) VALUES (3, N'c ')
GO
INSERT [dbo].[t3] ([c1], [c2]) VALUES (3, N'c ')
GO
INSERT [dbo].[t3] ([c1], [c2]) VALUES (3, N'c ')
GO
INSERT [dbo].[t3] ([c1], [c2]) VALUES (3, N'c ')
GO
INSERT [dbo].[t3] ([c1], [c2]) VALUES (3, N'c ')
GO
INSERT [dbo].[t3] ([c1], [c2]) VALUES (3, N'c ')
GO
INSERT [dbo].[t3] ([c1], [c2]) VALUES (3, N'c ')
GO
INSERT [dbo].[t3] ([c1], [c2]) VALUES (3, N'c ')
GO
INSERT [dbo].[t3] ([c1], [c2]) VALUES (3, N'c ')
GO
INSERT [dbo].[t3] ([c1], [c2]) VALUES (3, N'c ')
GO
INSERT [dbo].[t3] ([c1], [c2]) VALUES (3, N'c ')
GO
INSERT [dbo].[t3] ([c1], [c2]) VALUES (3, N'c ')
GO
INSERT [dbo].[t3] ([c1], [c2]) VALUES (3, N'c ')
GO
INSERT [dbo].[t3] ([c1], [c2]) VALUES (3, N'c ')
GO
INSERT [dbo].[t3] ([c1], [c2]) VALUES (3, N'c ')
GO
INSERT [dbo].[t3] ([c1], [c2]) VALUES (3, N'c ')
GO
INSERT [dbo].[t3] ([c1], [c2]) VALUES (3, N'c ')
GO
INSERT [dbo].[t3] ([c1], [c2]) VALUES (3, N'c ')
GO
INSERT [dbo].[t3] ([c1], [c2]) VALUES (3, N'c ')
GO
INSERT [dbo].[t3] ([c1], [c2]) VALUES (3, N'c ')
GO
INSERT [dbo].[t3] ([c1], [c2]) VALUES (3, N'c ')
GO
INSERT [dbo].[t3] ([c1], [c2]) VALUES (3, N'c ')
GO
INSERT [dbo].[t3] ([c1], [c2]) VALUES (3, N'c ')
GO
INSERT [dbo].[t3] ([c1], [c2]) VALUES (3, N'c ')
GO
INSERT [dbo].[t3] ([c1], [c2]) VALUES (3, N'c ')
GO
INSERT [dbo].[t3] ([c1], [c2]) VALUES (3, N'c ')
GO
INSERT [dbo].[t3] ([c1], [c2]) VALUES (3, N'c ')
GO
INSERT [dbo].[t3] ([c1], [c2]) VALUES (3, N'c ')
GO
INSERT [dbo].[t3] ([c1], [c2]) VALUES (3, N'c ')
GO
INSERT [dbo].[t3] ([c1], [c2]) VALUES (3, N'c ')
GO
INSERT [dbo].[t3] ([c1], [c2]) VALUES (3, N'c ')
GO
INSERT [dbo].[t3] ([c1], [c2]) VALUES (3, N'c ')
GO
INSERT [dbo].[t3] ([c1], [c2]) VALUES (3, N'c ')
GO
INSERT [dbo].[t3] ([c1], [c2]) VALUES (3, N'c ')
GO
INSERT [dbo].[t3] ([c1], [c2]) VALUES (3, N'c ')
GO
INSERT [dbo].[t3] ([c1], [c2]) VALUES (3, N'c ')
GO
INSERT [dbo].[t3] ([c1], [c2]) VALUES (3, N'c ')
GO
INSERT [dbo].[t3] ([c1], [c2]) VALUES (3, N'c ')
GO
INSERT [dbo].[t3] ([c1], [c2]) VALUES (3, N'c ')
GO
INSERT [dbo].[t3] ([c1], [c2]) VALUES (3, N'c ')
GO
INSERT [dbo].[t3] ([c1], [c2]) VALUES (3, N'c ')
GO
INSERT [dbo].[t3] ([c1], [c2]) VALUES (3, N'c ')
GO
INSERT [dbo].[t3] ([c1], [c2]) VALUES (3, N'c ')
GO
INSERT [dbo].[t3] ([c1], [c2]) VALUES (3, N'c ')
GO
INSERT [dbo].[t3] ([c1], [c2]) VALUES (3, N'c ')
GO
INSERT [dbo].[t3] ([c1], [c2]) VALUES (3, N'c ')
GO
INSERT [dbo].[t3] ([c1], [c2]) VALUES (3, N'c ')
GO
INSERT [dbo].[t3] ([c1], [c2]) VALUES (3, N'c ')
GO
INSERT [dbo].[t3] ([c1], [c2]) VALUES (3, N'c ')
GO
INSERT [dbo].[t3] ([c1], [c2]) VALUES (3, N'c ')
GO
INSERT [dbo].[t3] ([c1], [c2]) VALUES (3, N'c ')
GO
INSERT [dbo].[t3] ([c1], [c2]) VALUES (3, N'c ')
GO
INSERT [dbo].[t3] ([c1], [c2]) VALUES (3, N'c ')
GO
INSERT [dbo].[t3] ([c1], [c2]) VALUES (3, N'c ')
GO
INSERT [dbo].[t3] ([c1], [c2]) VALUES (3, N'c ')
GO
INSERT [dbo].[t3] ([c1], [c2]) VALUES (3, N'c ')
GO
INSERT [dbo].[t3] ([c1], [c2]) VALUES (3, N'c ')
GO
INSERT [dbo].[t3] ([c1], [c2]) VALUES (3, N'c ')
GO
INSERT [dbo].[t3] ([c1], [c2]) VALUES (3, N'c ')
GO
INSERT [dbo].[t3] ([c1], [c2]) VALUES (3, N'c ')
GO
INSERT [dbo].[t3] ([c1], [c2]) VALUES (3, N'c ')
GO
INSERT [dbo].[t3] ([c1], [c2]) VALUES (3, N'c ')
GO
INSERT [dbo].[t3] ([c1], [c2]) VALUES (3, N'c ')
GO
INSERT [dbo].[t3] ([c1], [c2]) VALUES (3, N'c ')
GO
INSERT [dbo].[t3] ([c1], [c2]) VALUES (3, N'c ')
GO
INSERT [dbo].[t3] ([c1], [c2]) VALUES (3, N'c ')
GO
INSERT [dbo].[t3] ([c1], [c2]) VALUES (3, N'c ')
GO
INSERT [dbo].[t3] ([c1], [c2]) VALUES (3, N'c ')
GO
INSERT [dbo].[t3] ([c1], [c2]) VALUES (3, N'c ')
GO
INSERT [dbo].[t3] ([c1], [c2]) VALUES (3, N'c ')
GO
INSERT [dbo].[t3] ([c1], [c2]) VALUES (3, N'c ')
GO
INSERT [dbo].[t3] ([c1], [c2]) VALUES (3, N'c ')
GO
INSERT [dbo].[t3] ([c1], [c2]) VALUES (3, N'c ')
GO
INSERT [dbo].[t3] ([c1], [c2]) VALUES (3, N'c ')
GO
select count(*) From t1;--8
select count(*) from t2;--68
select count(*) from t3;--262
use test_db
go
select
db_name() as dbname,
object_name(a.object_id) as TableName,
b.name as IndexName,
a.index_id,
a.index_type_desc,
a.index_level,
a.index_depth,
a.page_count,
a.record_count as "RowCount",
a.avg_fragmentation_in_percent
--from sys.dm_db_index_physical_stats(DB_ID(), OBJECT_ID('dbo.Sales'), 1,null,'DETAILED') as a /* 1 means clustered idx. also change the table name */
--from sys.dm_db_index_physical_stats(DB_ID(), null, null,null,'SAMPLED') as a
from sys.dm_db_index_physical_stats(DB_ID(), null, null,null,'DETAILED') as a
JOIN sys.indexes AS b ON a.object_id = b.object_id AND a.index_id = b.index_id
--where a.index_id in (0,1) --- 0- heap, 1 - clustered idx
where a.index_level = 0 ; -- leaf level
go
use sqldba_utils
go
EXECUTE dbo.IndexOptimize
@Databases = 'test_db', ---only db1
@FragmentationLevel1 = 5,
@FragmentationLevel2 = 30,
@FragmentationLow = NULL, --do nothing if frag%< 5%--based above 2 settings @FragmentationLevel1 & @FragmentationLevel2
@FragmentationMedium = 'INDEX_REORGANIZE,INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE', --means if fragmentation 5%-30%
@FragmentationHigh = 'INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE',--means if fragmentation >30%
@MinNumberOfPages=1, --default is >=1000 pages -- u might not see logging or fragmentation still show up as it is because pagecount <1000
@LogToTable = 'Y'
go
use test_db
go
select
db_name() as dbname,
object_name(a.object_id) as TableName,
b.name as IndexName,
a.index_id,
a.index_type_desc,
a.index_level,
a.index_depth,
a.page_count,
a.record_count as "RowCount",
a.avg_fragmentation_in_percent
--from sys.dm_db_index_physical_stats(DB_ID(), OBJECT_ID('dbo.Sales'), 1,null,'DETAILED') as a /* 1 means clustered idx. also change the table name */
--from sys.dm_db_index_physical_stats(DB_ID(), null, null,null,'SAMPLED') as a
from sys.dm_db_index_physical_stats(DB_ID(), null, null,null,'DETAILED') as a
JOIN sys.indexes AS b ON a.object_id = b.object_id AND a.index_id = b.index_id
--where a.index_id in (0,1) --- 0- heap, 1 - clustered idx
where a.index_level = 0 ; -- leaf level
go
--manual tried to rebuild?
ALTER INDEX [cl_idx_t1] ON [dbo].[t1] REBUILD WITH (SORT_IN_TEMPDB = OFF, ONLINE = ON, RESUMABLE = OFF);
ALTER INDEX [cl_idx_t2] ON [dbo].[t2] REBUILD WITH (SORT_IN_TEMPDB = OFF, ONLINE = ON, RESUMABLE = OFF);
ALTER INDEX [cl_idx_t3] ON [dbo].[t3] REBUILD WITH (SORT_IN_TEMPDB = OFF, ONLINE = ON, RESUMABLE = OFF); -
Hi,
page count 2??
The Ola scripts work from above 1.000 pages.
"
MinNumberOfPages
Set a size, in pages; indexes with fewer number of pages are skipped for index maintenance. The default is 1000 pages. This is based on Microsoft’s recommendation.
IndexOptimize checks page_count in sys.dm_db_index_physical_stats to determine the size of the index.
"
But, please read a lot about index maintenance with ssd devices. And take a look at the statistic updates instead.
Kind regards,
Andreas
-
My question is what are you trying to accomplish? How frequently do these indexes get fragmented, and what benefit are you trying to achieve by reindexing them?
As Andreas said, the number of pages is minimal. Rebuilding may have no effect on the indexes.
Did you try to drop an re-create the clustered index, as well as the rest of the indexes? Did that make a difference?
See what happens when you change the datatypes from char to varchar, and remove the spaces in each of the inserts. Then check your fragmentation. Does that give you a clue as to what is happening?
Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/ -
My recommendation is this... go to all of the BBFAATT (a "Moden-ism for Books, Blogs, Forums, Articles, AI, Talks, and 'Tubes and is pronounced as "bee-bee-fat" and can be bleated like a goat when trying to be sarcastic about the sources) that talks about the supposed importance of logical fragmentation and then send me the links for the ones that use coat to prove how logical fragmentation is an important performance impinging factor. I wish you luck in advance on that.
Shifting gears a bit and not bearing any malice towards you what-so-ever, it sounds like you don't have much knowledge about index maintenance. Don't feel bad there because a lot of people are just starting to find out that the supposed "Best Practice" Index Maintenance that most of the world has adopted and has been following for more than the last 2 decades are not a "Best Practice", were never meant to be a "Best Practice" and, when used in the manner that most people use them, are actually a WORST practice. They frequently cause perpetual fragmentation of indexes that shouldn't be fragmenting except every month or two.
If you think that's not true, read the final two paragraphs of the following article paying particular attention to the last sentence. This article was written by the fellow (Paul Randal) that wrote defrag code. He wrote it way back in 2009 and no one listened, including me until the 18th of Jan 2016 where I learned the lesson the hard way and stopped doing all index maintenance for just shy of 4 years and performance actually improved in the first 3 months and stayed that way for the rest of the time. I don't recommend that but it was a great experiment in showing just how useless defragmenting based soley on logical fragmentation actually is.
Here's Paul's article on the subject. Like I said, the last 2 paragraphs say it all.
And, for the love of Pete, don't do index maintenance on a fragmented index unless you had the time to study the method of fragmentation that it's experiencing because you can cause serious "Morning After" blocking, like what happened to me back in January of 2016.
The old "Best Practices" and even the "new" supposed "Best Practices" are so bad, they're the primary reason why people say that page splits and fragmentation is a big problem when using Random GUIDs. It's not and I have the proof. It's all because of the way people's index maintenance puts the screws to them.
The bottom line is that it's better to do NO index maintenance than it is to doing it wrong... and you're unwittingly doing it wrong. So is 98% of the rest of the world. 😉
I also strongly agree with what Adreas said about doing statistics maintenance, instead.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
The intention was to know the reason do we need to bother about fragmentation for tables with 10's or few 100's of pages. I see dev team asking question to us. That's the reason I wanted to know. if there is a proper way to explain that's not the issue.
I am aware of 1000 minimum pages for modern system for index fragmentation to be considered.
Thank you.
Sam
- vsamantha35 wrote:
The intention was to know the reason do we need to bother about fragmentation for tables with 10's or few 100's of pages. I see dev team asking question to us. That's the reason I wanted to know. if there is a proper way to explain that's not the issue.
I am aware of 1000 minimum pages for modern system for index fragmentation to be considered.
Thank you.
Sam
Back to my original question, as well as Jeff's response. What is your current reindexing strategy, and what benefits have you documented from performing reindexing?
The fact that the dev team is questioning these things indicates that you may be doing things that are actually negatively affecting your environment.
Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/ - vsamantha35 wrote:
The intention was to know the reason do we need to bother about fragmentation for tables with 10's or few 100's of pages. I see dev team asking question to us. That's the reason I wanted to know. if there is a proper way to explain that's not the issue.
I am aware of 1000 minimum pages for modern system for index fragmentation to be considered.
Thank you.
Sam
Understood. I like to turn things around on people that ask such questions... Have THEM prove that it's an issue. 😉
Paul Randal (SQLSkills.com) is the guy that wrote the defragmentation code. I don't have the link for the article where he recommends not doing the < 1000 pages thing and says now that 5000 may actually be a better number. Go take a look.
p.s. You could have saved us all a bit of time if you had phrased your original question like you did above. 😉
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) - Michael L John wrote:vsamantha35 wrote:
The intention was to know the reason do we need to bother about fragmentation for tables with 10's or few 100's of pages. I see dev team asking question to us. That's the reason I wanted to know. if there is a proper way to explain that's not the issue.
I am aware of 1000 minimum pages for modern system for index fragmentation to be considered.
Thank you.
Sam
Back to my original question, as well as Jeff's response. What is your current reindexing strategy, and what benefits have you documented from performing reindexing?
The fact that the dev team is questioning these things indicates that you may be doing things that are actually negatively affecting your environment.
We use Ola hallengren script for index maintenance and it takes more than 1 day.
EXECUTE [SQLDBA_utils].[dbo].[IndexOptimize]
@Databases = 'db1,db2,db3',
@FragmentationLow = NULL,
@FragmentationMedium = 'INDEX_REBUILD_ONLINE,INDEX_REORGANIZE,INDEX_REBUILD_OFFLINE',
@FragmentationHigh = 'INDEX_REBUILD_ONLINE,INDEX_REORGANIZE,INDEX_REBUILD_OFFLINE',
@FragmentationLevel1 = 30,
@FragmentationLevel2 = 60,
@OnlyModifiedStatistics= 'Y',
@Indexes= 'ALL_INDEXES',
--@Resumable = 'Y',
@MaxDOP = 6,
@WaitAtLowPriorityMaxDuration = 2,
@WaitAtLowPriorityAbortAfterWait='BLOCKERS',
@LogToTable = 'Y' - vsamantha35 wrote:Michael L John wrote:vsamantha35 wrote:
The intention was to know the reason do we need to bother about fragmentation for tables with 10's or few 100's of pages. I see dev team asking question to us. That's the reason I wanted to know. if there is a proper way to explain that's not the issue.
I am aware of 1000 minimum pages for modern system for index fragmentation to be considered.
Thank you.
Sam
Back to my original question, as well as Jeff's response. What is your current reindexing strategy, and what benefits have you documented from performing reindexing?
The fact that the dev team is questioning these things indicates that you may be doing things that are actually negatively affecting your environment.
We use Ola hallengren script for index maintenance and it takes more than 1 day.
EXECUTE [SQLDBA_utils].[dbo].[IndexOptimize]
@Databases = 'db1,db2,db3',
@FragmentationLow = NULL,
@FragmentationMedium = 'INDEX_REBUILD_ONLINE,INDEX_REORGANIZE,INDEX_REBUILD_OFFLINE',
@FragmentationHigh = 'INDEX_REBUILD_ONLINE,INDEX_REORGANIZE,INDEX_REBUILD_OFFLINE',
@FragmentationLevel1 = 30,
@FragmentationLevel2 = 60,
@OnlyModifiedStatistics= 'Y',
@Indexes= 'ALL_INDEXES',
--@Resumable = 'Y',
@MaxDOP = 6,
@WaitAtLowPriorityMaxDuration = 2,
@WaitAtLowPriorityAbortAfterWait='BLOCKERS',
@LogToTable = 'Y'And, to re-emphasize Michael's good question, how much better is performance after you run that as compared to just before you run that? And how's your log file doing after running that? And how fast are you index refragmenting after that? And how many bad page splits are you having in a normal business hour before all that v.s. after all that? And how about blocking? Have you setup PerfMon to watch for blocking before and after during normal business hours? If you haven't, you should. That's how I discovered that such index maintenance was actually causing more issues than they solved (which were nearly none).
And, worse yet, your fragmentation is just getting to the point where some really bad page splitting is coming to an end because the page splits made some room and then you turn around and set a lot of your indexes back up for really bad page splits again by removing all that nice free space by defragmenting them.
Again, measure performance during normal business hours before defragmenting and then do the same after defragmenting. I believe you'll find out that it's not worth it. I'll also say that, with the exception of seriously evenly distributed indexes (like Random GUID keyed indexes), measuring logical fragmentation is the wrong way to measure fragmentation. You need to measure page density and it needs to be measured across the index... not just the average because the average won't tell you what fragmentation type you're suffering for every index.
It is fortunate that you've opted for rebuilds prior to REORGANIZE because REORGANIZE can setup the index for serious page splits. It does NOT work the way most people think that it does.
If you want an introduction to all the stuff that Michael and I are talking about, spend the 82 minutes to watch the following 'tube. Despite the name, it's not just about GUIDs. I just use those because they're the "poster child" for fragmentation. And it's not trying to tell you to use Random GUIDs, either. They're just being used to explain why you should not normally use REORGANIZE and to demonstrate what REORGANIZE really does to your indexes. It also shows what happens on your "append only" indexes that get hot-spot rows updated shortly after they're inserted.
https://www.youtube.com/watch?v=rvZwMNJxqVo
If you're listening with a headset, be aware that they added 3 advertisements at the 15:00, 30:15, and 45:25. The advertisements are important to help support this event but I wanted to let you know that they're sudden and they're loud! They WILL lift your headset!
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Thank you Sir. Will watch it.
- vsamantha35 wrote:Michael L John wrote:vsamantha35 wrote:
The intention was to know the reason do we need to bother about fragmentation for tables with 10's or few 100's of pages. I see dev team asking question to us. That's the reason I wanted to know. if there is a proper way to explain that's not the issue.
I am aware of 1000 minimum pages for modern system for index fragmentation to be considered.
Thank you.
Sam
Back to my original question, as well as Jeff's response. What is your current reindexing strategy, and what benefits have you documented from performing reindexing?
The fact that the dev team is questioning these things indicates that you may be doing things that are actually negatively affecting your environment.
We use Ola hallengren script for index maintenance and it takes more than 1 day.
EXECUTE [SQLDBA_utils].[dbo].[IndexOptimize]
@Databases = 'db1,db2,db3',
@FragmentationLow = NULL,
@FragmentationMedium = 'INDEX_REBUILD_ONLINE,INDEX_REORGANIZE,INDEX_REBUILD_OFFLINE',
@FragmentationHigh = 'INDEX_REBUILD_ONLINE,INDEX_REORGANIZE,INDEX_REBUILD_OFFLINE',
@FragmentationLevel1 = 30,
@FragmentationLevel2 = 60,
@OnlyModifiedStatistics= 'Y',
@Indexes= 'ALL_INDEXES',
--@Resumable = 'Y',
@MaxDOP = 6,
@WaitAtLowPriorityMaxDuration = 2,
@WaitAtLowPriorityAbortAfterWait='BLOCKERS',
@LogToTable = 'Y'regarding these 2 options
@FragmentationMedium = 'INDEX_REBUILD_ONLINE,INDEX_REORGANIZE,INDEX_REBUILD_OFFLINE',
@FragmentationHigh = 'INDEX_REBUILD_ONLINE,INDEX_REORGANIZE,INDEX_REBUILD_OFFLINE',
I would remove INDEX_REORGANIZE from it - and unless you have tested and proved that INDEX_REBUILD_ONLINE is the best for your indexes, I would also remove it - it is a lot slower than the OFFLINE version and requires a lot more disk space.
it may be the case that you are best served with having INDEX_REBUILD_ONLINE for a small set of indexes, and OFFLINE for the majority of the others - your log table may help you determine this.
REORGANIZE should only be used with LOB_COMPACTION option for columnstore indexes
-
I would remove INDEX_REORGANIZE from it - and unless you have tested and proved that INDEX_REBUILD_ONLINE is the best for your indexes, I would also remove it - it is a lot slower than the OFFLINE version and requires a lot more disk space.
it may be the case that you are best served with having INDEX_REBUILD_ONLINE for a small set of indexes, and OFFLINE for the majority of the others - your log table may help you determine this.
REORGANIZE should only be used with LOB_COMPACTION option for columnstore indexes
What are the things I should be looking at from the Commandlog table to speed up my index maintenance. Currently, index maintenance job is taking more than 24 hours and we have a set time limit to run for only 24 hours and if it is taking more than 24 hours then bail out. is it highest duration indexes to have a separate job and run INDEX_REBUILD_OFFLINE on them? small indexes i.e. less duration with INDEX_REBUILD_ONLINE?
Sharing sample log file. We have lot of LOB columns as part of table structures and also random guids for some tables which has pagesplits.
Date and time: 2023-03-01 01:51:12
Command: ALTER INDEX [SVR1_1WNR] ON [UAT_ORS].[dbo].[C_B_CONTACT_HXRF] REORGANIZE WITH (LOB_COMPACTION = ON)
Comment: ObjectType: Table, IndexType: NonClustered, ImageText: No, NewLOB: No, FileStream: No, ColumnStore: No, AllowPageLocks: Yes, PageCount: 247414, Fragmentation: 6.25429
Outcome: Succeeded
Duration: 00:02:42
Date and time: 2023-03-01 01:53:54
Date and time: 2023-03-01 01:54:06
Command: ALTER INDEX [SVR1_1WNS] ON [UAT_ORS].[dbo].[C_B_CONTACT_HXRF] REBUILD WITH (SORT_IN_TEMPDB = OFF, ONLINE = ON (WAIT_AT_LOW_PRIORITY (MAX_DURATION = 2, ABORT_AFTER_WAIT = BLOCKERS)), MAXDOP = 4, RESUMABLE = OFF)
Comment: ObjectType: Table, IndexType: NonClustered, ImageText: No, NewLOB: No, FileStream: No, ColumnStore: No, AllowPageLocks: Yes, PageCount: 460375, Fragmentation: 35.4196
Outcome: Succeeded
Duration: 00:00:40
Date and time: 2023-03-01 01:54:46
Date and time: 2023-03-01 00:20:45
Version: 14.0.3381.3
Edition: Enterprise Edition: Core-based Licensing (64-bit)
Platform: Windows
Procedure: [SQLDBA_utils].[dbo].[IndexOptimize]
Parameters: @Databases = 'UAT_ORS', @FragmentationLow = NULL, @FragmentationMedium = 'INDEX_REORGANIZE,INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE', @FragmentationHigh = 'INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE', @FragmentationLevel1 = 5, @FragmentationLevel2 = 30, @MinNumberOfPages = 1000, @MaxNumberOfPages = NULL, @SortInTempdb = 'N', @MaxDOP = 4, @FillFactor = NULL, @PadIndex = NULL, @LOBCompaction = 'Y', @UpdateStatistics = NULL, @OnlyModifiedStatistics = 'N', @StatisticsModificationLevel = NULL, @StatisticsSample = NULL, @StatisticsResample = 'N', @PartitionLevel = 'Y', @MSShippedObjects = 'N', @Indexes = 'ALL_INDEXES', @TimeLimit = NULL, @Delay = NULL, @WaitAtLowPriorityMaxDuration = 2, @WaitAtLowPriorityAbortAfterWait = 'BLOCKERS', @Resumable = 'N', @AvailabilityGroups = NULL, @LockTimeout = NULL, @LockMessageSeverity = 16, @DatabaseOrder = NULL, @DatabasesInParallel = 'N', @LogToTable = 'Y', @Execute = 'Y'
Version: 2018-07-16 18:32:21
Source: https://ola.hallengren.com
Date and time: 2023-03-01 00:20:45
Database: [UAT_ORS]
Status: ONLINE
Standby: No
Updateability: READ_WRITE
User access: MULTI_USER
Is accessible: Yes
Recovery model: SIMPLE
Date and time: 2023-03-01 00:24:11
Command: ALTER INDEX [NI_C_B_ACCOUNT_0] ON [UAT_ORS].[dbo].[C_B_ACCOUNT] REORGANIZE WITH (LOB_COMPACTION = ON)
Comment: ObjectType: Table, IndexType: NonClustered, ImageText: No, NewLOB: No, FileStream: No, ColumnStore: No, AllowPageLocks: Yes, PageCount: 1107, Fragmentation: 5.14905
Outcome: Succeeded
Duration: 00:00:02
Date and time: 2023-03-01 00:24:13
Date and time: 2023-03-01 00:24:13
Command: ALTER INDEX [NI_C_B_ACCOUNT_2] ON [UAT_ORS].[dbo].[C_B_ACCOUNT] REORGANIZE WITH (LOB_COMPACTION = ON)
Comment: ObjectType: Table, IndexType: NonClustered, ImageText: No, NewLOB: No, FileStream: No, ColumnStore: No, AllowPageLocks: Yes, PageCount: 1318, Fragmentation: 9.02883
Outcome: Succeeded
Duration: 00:00:01
Date and time: 2023-03-01 00:24:14
Date and time: 2023-03-01 00:24:14
Command: ALTER INDEX [SVR1_11JFKFD] ON [UAT_ORS].[dbo].[C_B_ACCOUNT] REORGANIZE WITH (LOB_COMPACTION = ON)
Comment: ObjectType: Table, IndexType: NonClustered, ImageText: No, NewLOB: No, FileStream: No, ColumnStore: No, AllowPageLocks: Yes, PageCount: 1277, Fragmentation: 5.09005
Outcome: Succeeded
Duration: 00:00:01
Date and time: 2023-03-01 00:24:15
Date and time: 2023-03-01 00:24:15
Command: ALTER INDEX [SVR1_15K3899] ON [UAT_ORS].[dbo].[C_B_ACCOUNT] REORGANIZE WITH (LOB_COMPACTION = ON)
Comment: ObjectType: Table, IndexType: NonClustered, ImageText: No, NewLOB: No, FileStream: No, ColumnStore: No, AllowPageLocks: Yes, PageCount: 1152, Fragmentation: 25.0868
Outcome: Succeeded
Duration: 00:00:01
Date and time: 2023-03-01 00:24:16
Date and time: 2023-03-01 00:24:16
Command: ALTER INDEX [SVR1_18I5KBF] ON [UAT_ORS].[dbo].[C_B_ACCOUNT] REORGANIZE WITH (LOB_COMPACTION = ON)
Comment: ObjectType: Table, IndexType: NonClustered, ImageText: No, NewLOB: No, FileStream: No, ColumnStore: No, AllowPageLocks: Yes, PageCount: 1141, Fragmentation: 10.6047
Outcome: Succeeded
Duration: 00:00:01
Date and time: 2023-03-01 00:24:17
Date and time: 2023-03-01 00:24:17
Command: ALTER INDEX [SVR1_1XIN] ON [UAT_ORS].[dbo].[C_B_ACCOUNT] REORGANIZE WITH (LOB_COMPACTION = ON)
Comment: ObjectType: Table, IndexType: NonClustered, ImageText: No, NewLOB: No, FileStream: No, ColumnStore: No, AllowPageLocks: Yes, PageCount: 1029, Fragmentation: 26.2391
Outcome: Succeeded
Duration: 00:00:01
Date and time: 2023-03-01 00:24:18
Date and time: 2023-03-01 00:24:18
Command: ALTER INDEX [SVR1_62T14V] ON [UAT_ORS].[dbo].[C_B_ACCOUNT] REORGANIZE WITH (LOB_COMPACTION = ON)
Comment: ObjectType: Table, IndexType: NonClustered, ImageText: No, NewLOB: No, FileStream: No, ColumnStore: No, AllowPageLocks: Yes, PageCount: 1426, Fragmentation: 25.8065
Outcome: Succeeded
Duration: 00:00:01
Date and time: 2023-03-01 00:24:19
Date and time: 2023-03-01 00:24:20
Command: ALTER INDEX [SVR1_7NZE5O] ON [UAT_ORS].[dbo].[C_B_ACCOUNT] REORGANIZE WITH (LOB_COMPACTION = ON)
Comment: ObjectType: Table, IndexType: NonClustered, ImageText: No, NewLOB: No, FileStream: No, ColumnStore: No, AllowPageLocks: Yes, PageCount: 1585, Fragmentation: 5.29968
Outcome: Succeeded
Duration: 00:00:01
Date and time: 2023-03-01 00:24:21
Date and time: 2023-03-01 00:28:02
Command: ALTER INDEX [SVR1_1XKW] ON [UAT_ORS].[dbo].[C_B_ACCOUNT_HVXR] REORGANIZE WITH (LOB_COMPACTION = ON)
Comment: ObjectType: Table, IndexType: NonClustered, ImageText: No, NewLOB: No, FileStream: No, ColumnStore: No, AllowPageLocks: Yes, PageCount: 74463, Fragmentation: 28.6357
Outcome: Succeeded
Duration: 00:01:20
Date and time: 2023-03-01 00:29:22
---from commandlog table
use sqldba_utils
go
SELECT TOP (1000) [ID]
,[DatabaseName]
--,[SchemaName]
,[ObjectName]
--,[ObjectType]
,[IndexName]
,[IndexType]
--,[StatisticsName]
--,[PartitionNumber]
--,[ExtendedInfo]
,[Command]
--,[CommandType]
,[StartTime]
,[EndTime]
,[ErrorNumber]
,[ErrorMessage]
FROM [sqldba_utils].[dbo].[CommandLog]
-- where ObjectName like 'Sales%' --tablename -- check if rebuild ran on "sales" table
where databasename ='db1'
and starttime > '2023-02-01 20:58:00' --- get the date n time before running index rebuild
--and ObjectName in ('Sales','testtbl')
--and starttime >=getdate()-2
order by starttime asc
go/*
ID DatabaseName ObjectName IndexName IndexType Command StartTime EndTime ErrorNumber ErrorMessage
1 db1 testtbl CL_testtbl_c1 1 ALTER INDEX [CL_testtbl_c1] ON [dbo].[testtbl] REBUILD WITH (SORT_IN_TEMPDB = OFF, ONLINE = ON, RESUMABLE = OFF) 2023-02-01 20:59:02.5258695 2023-02-01 20:59:02.8427589 0 NULL
2 db1 testtbl NCL_testtbl_c2 2 ALTER INDEX [NCL_testtbl_c2] ON [dbo].[testtbl] REORGANIZE WITH (LOB_COMPACTION = ON) 2023-02-01 20:59:02.8427589 2023-02-01 20:59:03.1257682 0 NULL
*/
Thanks,
Sam
-
You can speed this up significantly.
STOP REINDEXING.
Back to the original question. Why are you reindexing? What benefit are you seeing from reindexing?
Reindexing is not a best practice.
Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/ -
I have to second Michael's recommendation yet again. Stop doing index maintenance on your RowStore indexes until you can prove some significant performance benefit. Use the time to do Statistics Maintenance, instead.
I'll also state that a lot of people say that index maintenance was a benefit when it was actually the fact that rebuilds also rebuild the statistics.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Here is the link provided by Ola to Microsoft's Index best practices.
It never states reorg at 5% and rebuild at 30%. That was removed almost 3 years ago.
Microsoft does say this:
Customers often incorrectly attribute this improvement to the index rebuild itself, taking it to be result of reduced fragmentation and increased page density. In reality, the same benefit can often be achieved at much cheaper resource cost by updating statistics instead of rebuilding indexes.
and
To avoid unnecessary resource utilization that may be detrimental to query workloads, Microsoft does not recommend performing index maintenance indiscriminately. Instead, performance benefits from index maintenance should be determined empirically for each workload using the recommended strategy, and weighed against resource costs and workload impact needed to achieve these benefits.
and
Measure the specific impact of reorganizing or rebuilding indexes on query performance in your workload. Query Store is a good way to measure the "before maintenance" and "after maintenance" performance using the A/B testing technique.
Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply