June 19, 2009 at 8:11 am
Hi,
Like Oracle, does SQL Server have the concept of high water mark. Should I be concerned about the table performance, if I delete all data from a huge table.
Thanks,
Suhas.
June 19, 2009 at 8:15 am
Worth checking if the indexes need rebuilding or reorganising after the Deletion..
-- CHECK if the indexes need reorganising
DECLARE @db_id SMALLINT;
DECLARE @object_id INT;
SET @db_id = DB_ID(N'@dbname');
SET @object_id = OBJECT_ID(N'@dbname.dbo.@tablename');
IF @db_id IS NULL
BEGIN;
PRINT N'Invalid database';
END;
ELSE IF @object_id IS NULL
BEGIN;
PRINT N'Invalid object';
END;
ELSE
BEGIN;
SELECT a.index_id, name, avg_fragmentation_in_percent,page_count
FROM sys.dm_db_index_physical_stats (@db_id, @object_id,NULL, NULL, NULL) AS a
JOIN sys.indexes AS b ON a.object_id = b.object_id AND a.index_id = b.index_id
where a.avg_fragmentation_in_percent>5 and a.avg_fragmentation_in_percent30
END;
GO
June 19, 2009 at 8:18 am
deleting all data from tables will be much slower than truncating the table. Delete logs each and every transaction in the log file where as truncate logs minimally.
June 19, 2009 at 8:22 am
Do u mean performance of the table after deletion or during Deletion????
Use Truncate instead of Delete as Pradeep has mention it's much faster..
It's worth checking the indexes after the deletetion as well.
June 19, 2009 at 8:59 am
Performance of the table after deletion. I'll try out the index reorganizing and index rebuilding queries.
I am working on test databases, and I am frequently populating the tables and emptying them. Also I need these tables, so dropping tables/database is not an option.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply