When you say "
SELECT * FROM MyTable WHERE FooBar > 20" then SQL estimates how many rows will be in the answer and which index will give the most selectivity. It makes that estimate based on the Statistics (which say for INDEX1 "There are 1,000 rows from 1-10, 1200 from 10-20", and so on
If you add / delete, or Update on an index column, lots of rows then the statistics are out of date.
Well ... it depends. I take the view that waiting until Saturday night means that on Friday all our expensive fee earners, company wide, are having to add a few more MS to everything they query, compared to Monday.
So I want to rebuild indexes and statistics every night. But that adds load to the server, and Log data to me LDF files (and TLog backups ...) ...
... so I use a reasonably sophisticated approach to only updating indexes / statistics where necessary, but running that every night. Some heavily used indexes get rebuilt every night, some less often/not at all, but if
"tomorrow" something dramatic happens to the data I know that table will get rebuilt the following night.
If your database is not too big you could just "throw" a bog standard maintenance plan at it. But beware massive log files ... assuming that you have database in FULL Recovery Model (if not, and you are OLTP, WHY NOT? )
I think Minion Backup does index rebuild and statistics update. Its a pretty lightweight install and out-of-the-box has sensible defaults (which you can then tinker with if you need to). But you may already have something robust in place for your backups, in which case what does that have for Index Rebuild and Update Statistics?