I have been working with SQL Server as a Developer a while.
One thing I learnt is SQL Server manages Statistics which help Engine to create optimized execution plan.
I could not figure out what exactly is stores in Statistics? (I read it saves Vector, but what Vector?)
When/In which scenario SQL Server updates Statistics?
How/why some time they go out of sync (old Statistics)
In case of old Statistics is a manual DBA/Developer intervention is required or SQL Server Will get them updated.
As a DBA/Developer how to find out if Statistics OLD? What should we do?
Statistics in this context refers to a sampling that the RDBMS takes of the values of a given index. Roughly speaking, this gives the engine an idea of the distribution of values, and helps it to plan efficient queries. You can see the actual contents of a statistics set using DBCC SHOW_STATISTICS.
DBCC SHOW_STATISTICS (table_name, index_name)
Statistics on an index can become outdated over time as the data in the table -- and therefore the distribution of the index values -- changes. This can result in less than optimal query execution plans, which is why you should aim to keep statistics up-to-date.
You can update statistics manually, or set them to update automatically, using the UPDATE STATISTICS T-SQL command. From the first MSDN link:
When the automatic update statistics option, AUTO_UPDATE_STATISTICS, is on, the query optimizer determines when statistics might be out-of-date and then updates them when they are used by a query.