0

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?

Pritesh
  • 1,938
  • 7
  • 32
  • 46

2 Answers2

4

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.

McGarnagle
  • 101,349
  • 31
  • 229
  • 260
  • 1. Statistics will not be maintained for table with no index? 2. When Insert/Update happen SQL Server by itself will update Statistics? 3. As DBA/Developer how to find if Statistics outdated? 4. When Statistics are updated does table get locked? – Pritesh Jul 04 '12 at 03:17
  • 1
    @Pritesh 1. correct, 2. correct if auto-update is on, 3. I'm not sure, but I think Google can help you with this (eg: http://www.sqlservercentral.com/blogs/glennberry/2010/08/30/how-old-are-your-database-statistics_3F00_/) 4. you can update statistics asynchronously using *AUTO_UPDATE_STATISTICS_ASYNC* – McGarnagle Jul 04 '12 at 03:24
1

You can see if your statistics are up-to-date with:

select  object_name(ind.object_id) as TableName
,       ind.name as IndexName
,       stats_date(ind.object_id, ind.index_id) as StatisticsDate
FROM    sys.indexes ind
order by 
        stats_date(ind.object_id, ind.index_id) desc
Andomar
  • 232,371
  • 49
  • 380
  • 404
  • suppose stats_date(ind.object_id, ind.index_id) value is 4 hours behind but how can we be sure was there need for Statistics to be updated in last 4 hours? – Pritesh Jul 04 '12 at 06:50
  • Four hours is fine, we update our statistics once a week. You shouldn't need to update them more often. – Andomar Jul 04 '12 at 07:06