On Vacation!

Happy New Year to you! I'm on vacation this week, and will see you after the new year!

Merry Christmas!

Merry Christmas to you ! Here's hoping you enjoy your holidays with family, no matter where you are.

Scripts from Index Defrag DBA Minute

Here's the scripts from last week's DBA Minute on Index Fragmentation.

set nocount on;
use WideWorldImporters;
go

if OBJECT_id('dbo.TblHeap') is not null drop table dbo.TblHeap;
if OBJECT_id('dbo.TblHeapWithPK') is not null drop table dbo.TblHeapWithPK;
go

select sysdatetime() as [StartAt];
go

CREATE TABLE dbo.TblHeap (
TblHeapid BIGINT IDENTITY NOT NULL,
col1 VARCHAR(1024) NOT NULL,
col2 uniqueidentifier DEFAULT NEWSEQUENTIALID() -- because it's the ugliest thing I could think of, that's why
);

CREATE TABLE dbo.TblHeapWithPK(
TblHeapid BIGINT IDENTITY primary key,
col1 VARCHAR(1024) NOT NULL,
col2 uniqueidentifier DEFAULT NEWSEQUENTIALID() -- because it's the ugliest thing I could think of, that's why
);
GO

select sysdatetime() as [TablesBuilt];
go

--Insert 10,000 rows.
DECLARE @i INT = 1;

WHILE @i <= 10000
BEGIN
 INSERT dbo.TblHeap (col1) SELECT REPLICATE('ASDF',200) ;
 INSERT dbo.TblHeapWithPK (col1) SELECT REPLICATE('ASDF',200) ;
 SET @i = @i + 1;
END
GO

select sysdatetime() as [TblHeap built];
go

-- what's in the tables?
select top 10 * from dbo.TblHeap;
select top 10 * from dbo.TblHeapWithPK;
go

-- frag level?
select * from sys.dm_db_index_physical_stats(db_id(), object_id('dbo.TblHeap'), NULL, NULL, NULL);
select * from sys.dm_db_index_physical_stats(db_id(), object_id('dbo.TblHeapwithPK'), NULL, NULL, NULL);
go

-- force page splits
update dbo.TblHeap set col1 = REPLICATE('x',1024);
update dbo.TblHeapWithPK set col1 = REPLICATE('x',1024);
update dbo.TblHeap set col1 = REPLICATE('x',512) where ((TblHeapid%3)=0);
update dbo.TblHeapWithPK set col1 = REPLICATE('x',512) where ((TblHeapid%3)=0);
update dbo.TblHeap set col1 = REPLICATE('x',256) where ((TblHeapid%5)=0);
update dbo.TblHeapWithPK set col1 = REPLICATE('x',256) where ((TblHeapid%5)=0);
go

delete from dbo.TblHeap where (TblHeapid % 5 ) <> 0;
delete from dbo.TblHeapWithPK where (TblHeapid % 5 ) <> 0;
go

select * from sys.dm_db_index_physical_stats(db_id(), object_id('dbo.TblHeap'), NULL, NULL, NULL);
select * from sys.dm_db_index_physical_stats(db_id(), object_id('dbo.TblHeapwithPK'), NULL, NULL, NULL);
go

ALTER INDEX ALL ON dbo.TblHeap REORGANIZE --WITH (FILLFACTOR = 80, SORT_IN_TEMPDB = ON, STATISTICS_NORECOMPUTE = ON);
ALTER INDEX ALL ON dbo.TblHeapwithPK REORGANIZE --WITH (FILLFACTOR = 80, SORT_IN_TEMPDB = ON, STATISTICS_NORECOMPUTE = ON);
GO

select * from sys.dm_db_index_physical_stats(db_id(), object_id('dbo.TblHeap'), NULL, NULL, NULL);
select * from sys.dm_db_index_physical_stats(db_id(), object_id('dbo.TblHeapwithPK'), NULL, NULL, NULL);
go

DBA Minute - Index Fragmentation