As you perform inserts updates and deletes, your indexes will become fragmented both internally and externally.
Internal fragmentation is you have a high percentage of free space on your index pages, meaning that SQL Server needs to read more pages when scanning the index.
External fragmentation is when the pages of the index are not in order any more, so SQL Server has to do more work, especially in IO terms to read the index.
If your indexes become too fragmented, at best, your queries will be less efficient but at worst, SQL Server will just stop using the indexes all together, meaning virtually all queries would have to perform a table scan or clustered index scan. This will hurt your performance a lot!
When you reorganise an index, then SQL Server uses the existing index pages and just shuffles data around on those ages. This will alleviate internal fragmentation and can also remove a small amount of external fragmentation. It is a lighter weight operation than rebuild and is always online.
When you rebuild an index, SQL Server actually resorts the data of the index and uses a new set of index pages. This will obviously alleviate both internal and external fragmentation but is a more heavy weight operation and by default causes the index to go offline, although it can be performed as an online operation, depending on your SQL Server version and settings.
Please do not expect to have 0 fragmentation after a Rebuild however. Unless you use a MAXDOP query hint, SQL Server will parallelise the rebuild operation and the more processors involved, the more fragmentation there is likely to be, because each processor or core, will rebuild their section or fragment of the index individually, without regard for each other. This is a trade off between best fragmentation levels and time taken to rebuild the index. For near 0 fragmentation, use MAXDOP 1 and sort the results in TempDB.
There are better ways - just reorganizing all indexes nightly can be quite wasteful. Why even bother reorganizing an index that is 12% fragmented? Why reorganize a 10GB index every night if it takes 30 minutes and you only reduce fragmentation by 2% or 3%? How much effort should you spend reorganizing an index that is largely or completely in memory anyway - sure you save some disk space, but you have to pull the index out of buffer to do so - is the space savings worth the performance hit that will cause?
There are free scripts out there that help make some of these decisions for you, and it's easy to override the defaults:
SentryOne also has a tool (not free) called SQL Sentry which goes quite a bit further than these by offering full historical reporting, broad to granular rules and schedules on which indexes to reorganize/rebuild and when (from server-wide to individual index and even partition), support for concurrent operations (should your maintenance window be tight), drag-and-drop calendar for scheduling, and the ability to assess exactly how the work you're doing affects performance.
Best Answer
SQL Server does not maintain when an Index was last rebuild, instead it keeps information when stats were last updated.
That can be found using the
STATS_DATEfunction.You can use Ola's Index maintenance solution or Michelle Ufford's - Index Defrag Script. These scripts are widely tested in the community and are much flexible so that you can adapt as per your needs in your environment.
SQL Server SP2 for 2008R2 and up has a new DMF
sys.dm_db_stats_propertieswhich tells you when your stats were last updated with other info like