February 19, 2009 at 9:31 am
Hi all,
I have a procedure (MS SQL Server 2000) which has to insert into a table in different database and then will delete those data from the related tables. There are more than 6 tables.. with primary key and foreign keys.. But I have one table which is giving the procedure a lot of problem.
I have attached the ExecutionPlan for the same.
The delete from other table with more records than it takes less time but this take more than the others..
Any suggestions ..
Table Tx1 has total rows of 56848137
It has a joint primary key in 7 columns.
It has the clustered index.
I have another table Tx2 which has 56848332
it also has the same configuration.
When I delete the Tx2 table with the conditions deletes 6 rows in 2 secs.
but when I try to delete the Tx1 it takes nearly 10 mins..
I have done reindexing also .. but no changes..
Thanks,
Harsha
February 19, 2009 at 11:32 am
How many rows are you deleting from Tx1? Any blocking?
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
February 19, 2009 at 11:40 am
your attached file give error while downloading.
The following error occurred...
Sorry the application encountered an unexpected error. Information about this error has been logged. If you continue to receive this message please contact the board administrator.
But anyway, If you are trying to delete too many rows then prefer to delete them in small chunk and then commit and start again the delete process. Once I got a process where I had to delete nearly 26 million rows from a table and by above method I ended up saving loads of time.
SQL DBA.
February 19, 2009 at 11:44 am
Another way could be set your index to use row lock. SQL Server is by default is set to page lock. By doing this you will have very very few locks while deleting than compared to normal process.
USE databasename
GO
EXEC sp_indexoption 'tablename.indexname',
'disallowpagelocks',
TRUE
It worked wonders for me but your requirement would be different than mine. So, do test it if it works for you or not.
SQL DBA.
February 19, 2009 at 11:58 am
SanjayAttray (2/19/2009)
SQL Server is by default is set to page lock.
SQL will use row, page, extent or table as necessary depending on the number of rows been affected and the amount of available memory
EXEC sp_indexoption 'tablename.indexname',
'disallowpagelocks',
TRUE
Be very careful doing that.
Firstly, if page locks are disabled, index reorganisations will fail.
Second, if page locks are disabled and enough rows are affected to make row locks too expensive, then SQL will lock at the table level.
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
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply