Please start any new threads on our new site at We've got lots of great SQL Server experts to answer whatever question you can come up with.

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | Active Topics | Members | Search | Forum FAQ
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 revise Delete Query to reduce table scan
 Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Starting Member

19 Posts

Posted - 02/15/2011 :  11:42:16  Show Profile  Reply with Quote
Hi all
I am getting an alert with my idera tool showing that there is a table scan occurring with the script below and that I should revise it. However, I am foggy on how to pull the column out of the predicate so that that the query does exactly the same but with a different logic flow.Will you show me how to revise this?

CREATE PROCEDURE [dbo].[deleteExpiredJobs]
DELETE FROM RunningJobs WHERE DATEADD(s, Timeout, StartDate) < GETDATE()


Flowing Fount of Yak Knowledge

2937 Posts

Posted - 02/15/2011 :  12:40:02  Show Profile  Reply with Quote
A function on a column having an index will cause that index to be unused.

Change your to this

select * FROM RunningJobs WHERE StartDate > DATEADD(s, -Timeout,GETDATE())

Please verify the result using the select clause and then only convert it to a delete statement.


Go to Top of Page

Starting Member

19 Posts

Posted - 02/16/2011 :  12:14:01  Show Profile  Reply with Quote
Thanks for your code. I tried it but I am still getting a table scan even with an index on the startdate column.

Go to Top of Page

Premature Yak Congratulator

22864 Posts

Posted - 02/18/2011 :  10:16:31  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
Try this and see

select * FROM RunningJobs WHERE Startdate<getdate() and DATEADD(s, Timeout, StartDate) < GETDATE()


Failing to plan is Planning to fail
Go to Top of Page

Not Just a Number

15586 Posts

Posted - 02/18/2011 :  10:32:50  Show Profile  Reply with Quote
Do you have an index on startdate?

Post the DDL



Hint: Want your questions answered fast? Follow the direction in this link

Want to help yourself?

Go to Top of Page

Starting Member

4 Posts

Posted - 02/19/2011 :  00:28:26  Show Profile  Visit yuanyelss's Homepage  Reply with Quote

Edited by - yuanyelss on 02/19/2011 00:29:24
Go to Top of Page

Flowing Fount of Yak Knowledge

2937 Posts

Posted - 02/19/2011 :  03:19:19  Show Profile  Reply with Quote
Originally posted by mcolli00

Thanks for your code. I tried it but I am still getting a table scan even with an index on the strtdate column.


My best bet here would be that your query seems to be covering more than 20% of the total rows in the table.
If that's the case then the optimizer will go for a scan even if an index is defined on the column.


Go to Top of Page
  Previous Topic Topic Next Topic  
 Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.04 seconds. Powered By: Snitz Forums 2000