Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I have a table that constantly doing insert, and I am trying to run a delete script to remove old record.

The delete script looks like this:

DECLARE @StarDate DATETIME = '2013-01-13';
DECLARE @DeleteTop Int = 2000

WHILE @@ROWCOUNT <> 0
BEGIN
    DELETE TOP (@DeleteTop) FROM VehicleLocation 
    WHERE 
    MessageGenerationDate < @StarDate
    AND (NOT EXISTS 
    (SELECT NULL FROM VehicleLocationTP 
    WHERE VehicleLocationTP.VehicleLocationKey = VehicleLocation.VehicleLocationKey) 
    AND NOT EXISTS 
    (SELECT NULL FROM VehicleLocationGF WHERE VehicleLocationGF.VehicleLocationKey = VehicleLocation.VehicleLocationKey) 
    AND NOT EXISTS 
    (SELECT NULL FROM VehicleLocationInpt WHERE VehicleLocationInpt.VehicleLocationKey = VehicleLocation.VehicleLocationKey) 
    AND NOT EXISTS 
    (SELECT NULL FROM VehicleLocationFare WHERE VehicleLocationFare.VehicleLocationKey = VehicleLocation.VehicleLocationKey) 
    AND NOT EXISTS 
    (SELECT NULL FROM VehicleLocationOBD WHERE VehicleLocationOBD.VehicleLocationKey = VehicleLocation.VehicleLocationKey) 
    AND NOT EXISTS 
    (SELECT NULL FROM VehicleLocationAPC WHERE VehicleLocationAPC.VehicleLocationKey = VehicleLocation.VehicleLocationKey));
END;

When running this script, it causing LCK_M_IX wait type on the insert, causing the insert being blocked.

However, if I remove the variables, and simply go for

DELETE TOP (2000) FROM VehicleLocation...

I will no longer see the LCK_M_IX wait type.

What kind of optimization SQL-Server was trying to perform? Is it better to avoid using variables and instead specific the value explicit?

This question is related to a similar question: http://stackoverflow.com/questions/2293772/t-sql-query-performance-puzzle-why-does-using-a-variable-make-a-difference

share|improve this question
add comment

2 Answers

up vote 1 down vote accepted

Sometimes yes it's good to use statics. In this case the SQL Server doesn't know what the value is in the variable when it's creating the plan so it's escalating to a table lock I'm guessing. You could force the locks to be row locks on the delete statement which should fix the problem.

share|improve this answer
    
Do you think if I create a stored procedure for it instead, would it be complied in a way that SQL can optimize it better? –  dsum Apr 10 '13 at 20:19
    
The variable is still an unknown when the plan is made, so the locking engine may still go nuts. Forcing the locking should fix this and doesn't have really any downsides as you are doing a small number of deletes. –  mrdenny Apr 10 '13 at 20:22
    
I found this post, also indicate it is the statistic that pays a factor: stackoverflow.com/questions/313935/… –  dsum Apr 19 '13 at 15:09
add comment

I'd venture a guess that using dynamic SQL would override this issue. Haven't tried it though but I tend to use them a lot for similar situations. Note that the types for the parameters have been changed, to avoid casting them and formatting the date within the string that's used in dynamic SQL.

Like so:

DECLARE @StarDate VARCHAR(25) = '2013-01-13'
DECLARE @DeleteTop VARCHAR(25) = '2000'

DECLARE @SQL VARCHAR(MAX)
SET @SQL = 
'WHILE @@ROWCOUNT <> 0
BEGIN
    DELETE TOP ('@DeleteTop+') FROM VehicleLocation 
    WHERE 
    MessageGenerationDate < '''+@StarDate+'''
    AND (NOT EXISTS 
    (SELECT NULL FROM VehicleLocationTP 
    WHERE VehicleLocationTP.VehicleLocationKey = VehicleLocation.VehicleLocationKey) 
    AND NOT EXISTS 
    (SELECT NULL FROM VehicleLocationGF WHERE VehicleLocationGF.VehicleLocationKey = VehicleLocation.VehicleLocationKey) 
    AND NOT EXISTS 
    (SELECT NULL FROM VehicleLocationInpt WHERE VehicleLocationInpt.VehicleLocationKey = VehicleLocation.VehicleLocationKey) 
    AND NOT EXISTS 
    (SELECT NULL FROM VehicleLocationFare WHERE VehicleLocationFare.VehicleLocationKey = VehicleLocation.VehicleLocationKey) 
    AND NOT EXISTS 
    (SELECT NULL FROM VehicleLocationOBD WHERE VehicleLocationOBD.VehicleLocationKey = VehicleLocation.VehicleLocationKey) 
    AND NOT EXISTS 
    (SELECT NULL FROM VehicleLocationAPC WHERE VehicleLocationAPC.VehicleLocationKey =       VehicleLocation.VehicleLocationKey));
END'

EXEC(@SQL)
share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.