SQL Server 2008 - bit of a newbie to managing and optimizing SQL Server...
I have a table that will contain many records and will continue to grow over time by maybe up to 30K rows per month.
Two of the columns on this table are the start datetim and finish datetime for that record. In my application I will regularly be fetching records for a specific datetime such as the last 30 days.
If I look at the query executio nplan for a query such as
SELECT * FROM table WHERE FinishDate > DATEADD(day, -30, GETDATE())
OR
SELECT * FROM table WHERE FinishDate > @D1
then I notice that even if I have an index on the date column SQL Server 2008 does not use it. (it suggests adding an index with all the other columns as Included Columns which makes the index stupidly huge)
Should I be concerned that performance will degrade as the table grows - if it's not using the index then will it take longer to query each month as the table grows?
can I get SQL Server 2008 to use an index when using > or < on a date field?
This Question has been solved and asker verified All Experts Exchange premium technology solutions are available to subscription members.
Experts Exchange has been collecting answers to technology questions since 1996…3 million and counting! If you have a question, chances are we already have your answer.
If you can't find the exact answer you're looking for, ask our exclusive community of 50,000 experts. You’ll get a personalized answer from a trusted professional.
Thousands of free tech tips, tricks, how-to’s and tutorials are available in our peer reviewed articles section. See for yourself how smart our experts are, no login required.
Save solutions to your questions, answers you’ve discovered through searching plus helpful articles in your personal knowledgebase for easy future access.
Access the answers to your technology questions today.
30-day free trial. Register in 60 seconds.
Members of the expert community talk about why the experience at Experts Exchange is different than what you will find anywhere else.
Try it out and discover for yourself.
30-day free trial. Register in 60 seconds.
Join the community of experts here and help other tech pros by answering question in your area of expertise. You can earn FREE access to all Experts Exchange's premium features and resources.
it seems that suggesting the index degrades performance:
running the following query and showing the execution plan gives this:
select * from tablename with (nolock, index(i_finishdate))
where FinishDate> DATEADD(day, -30, GETDATE());
select * from AuctionListing
where FinishDate> DATEADD(day, -30, GETDATE());
the first query with the index hint costs 92% of the batch.
if the query's WHERE clause uses = instead of > then SQL Server uses the index, but won't for >
so, telling it to use the index degrades performance significantly with 10K rows...
try not using the function dateadd for a start... Using the function is possibly causing a little bit of grief, and depending on how many records, it is probably going to be quicker using a table scan - you will need some volumes there, and whilst it might grow, it might not yet be big enough.
So, start with your original, and then it check against :
select * from tablename where FinishDate> GETDATE() - 30;
then try specifying the index:
select * from tablename WITH (INDEX(i_finishdate)) where FinishDate> GETDATE() - 30;
then if the update statistics has been run - and should be part of maintenance plans (but for now just do update statistics tablename;) can use the forceseek - which basically tells the rather poor cardinality check to "go away" and use the flipping index seek...
select * from tablename WITH (forceseek) where FinishDate> GETDATE() - 30;
and if need be you can combine forceseek with sepcifying the index - but you are overriding default behaviour which is normally pretty good so long as you have good representative data - and the index should be picked up... By the way, if using the forceseek, might want auto statistics, or run update statistics on a regular basis...
yeah, have to flush buffers in between....
should do ON A DEV server the same steps before hand to ensure that each query "looks" like it is being run for the first time (did I say ON A DEV server ?):
DBCC FREEPROCCACHE WITH NO_INFOMSGS;
DBCC DROPCLEANBUFFERS WITH NO_INFOMSGS;
then update statistics on the table(s) you are querying... ie
UPDATE STATISTICS ON <tablename>
and probably should do with fullscan to be sure / consistant from test to test, and if you really want to be fussy, probably a rebuild of indexes in between - but probably do not want to go that far - it can take a while...
Business Accounts
Answer for Membership
by: knightEknightPosted on 2008-09-11 at 20:13:31ID: 22456871
You can explicitely suggest the index for the query to use like this:
SELECT * FROM table WITH (Nolock,index(nameOfYourIn
WHERE FinishDate > DATEADD(day, -30, GETDATE())