May 2, 2016 at 6:23 am
Hi All,
I wanted to know what is the difference between below 2 sets (Set1 & Set2).
Which will reuse the transaction log file space without having to grow the tlog file.
Which is more efficient? How to prove which one is good? is there a query to find the amount of txnlog file space usage while performing the 2 operations.
Second question, is that I am not using SORT_IN_TEMPDB option while rebuild, so, does the huge index rebuild guarentees that the sorts is done only in memory and doesn't spill to tempdb?
How to prove it is not spilling over to tempdb ???
--Set 1
/*
ALTER INDEX ALL ON dbo.t1 REBUILD WITH (FILLFACTOR = 80);
ALTER INDEX ALL ON dbo.t2 REBUILD WITH (FILLFACTOR = 80);
ALTER INDEX ALL ON dbo.t3 REBUILD WITH (FILLFACTOR = 80);
ALTER INDEX ALL ON dbo.t4 REBUILD WITH (FILLFACTOR = 80);
ALTER INDEX ALL ON dbo.t5 REBUILD WITH (FILLFACTOR = 80);
ALTER INDEX ALL ON dbo.test_tbl REBUILD WITH (FILLFACTOR = 80);
ALTER INDEX ALL ON dbo.Sales REBUILD WITH (FILLFACTOR = 80);
*/
-- Set 2
/*
ALTER INDEX ALL ON dbo.t1 REBUILD WITH (FILLFACTOR = 80);
GO
ALTER INDEX ALL ON dbo.t2 REBUILD WITH (FILLFACTOR = 80);
GO
ALTER INDEX ALL ON dbo.t3 REBUILD WITH (FILLFACTOR = 80);
GO
ALTER INDEX ALL ON dbo.t4 REBUILD WITH (FILLFACTOR = 80);
GO
ALTER INDEX ALL ON dbo.t5 REBUILD WITH (FILLFACTOR = 80);
GO
ALTER INDEX ALL ON dbo.test_tbl REBUILD WITH (FILLFACTOR = 80);
GO
ALTER INDEX ALL ON dbo.Sales REBUILD WITH (FILLFACTOR = 80);
GO
*/
-- Below is the query i am using to generate above 2 outputs
use demo
go
DECLARE @TableName VARCHAR(255)
DECLARE @sql NVARCHAR(500)
DECLARE @fillfactor INT
SET @fillfactor = 80
DECLARE TableCursor CURSOR FOR
SELECT OBJECT_SCHEMA_NAME([object_id])+'.'+name AS TableName
FROM sys.tables
OPEN TableCursor
FETCH NEXT FROM TableCursor INTO @TableName
WHILE @@FETCH_STATUS = 0
BEGIN
SET @sql = 'ALTER INDEX ALL ON ' + @TableName + ' REBUILD WITH (FILLFACTOR = ' + CONVERT(VARCHAR(3),@fillfactor) + ');'
print @sql
--print 'GO'
--EXEC (@sql)
FETCH NEXT FROM TableCursor INTO @TableName
END
CLOSE TableCursor
DEALLOCATE TableCursor
GO
Thanks,
Sam
May 2, 2016 at 7:19 am
vsamantha35 (5/2/2016)
I wanted to know what is the difference between below 2 sets (Set1 & Set2).
The first requires one network round trip, the second multiple. That's all.
Second question, is that I am not using SORT_IN_TEMPDB option while rebuild, so, does the huge index rebuild guarentees that the sorts is done only in memory and doesn't spill to tempdb?
Not at all. Without that option, the sort space is allocated in the user database which contains the indexes. With it, the sort space is allocated in TempDB.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
May 2, 2016 at 11:11 am
Thanks Gail. If you don't mind, can you please elaborate the 1st question answer? regarding network round trips? Its little top of my head.
May 2, 2016 at 11:43 am
Literally that. A round trip across the network, client to server and back.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
May 2, 2016 at 12:50 pm
Thank you Gail.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply