0

I have SQL Server Compact Edition 4.0 database that get ~1K records every day. But over time some queries lost their efficency. I re-checked indexes and statistics and found that the statistics were not updated long time. sp_show_statistics shows me the following:

TABLE       INDEX               UPDATED             ROWS    ROWS__SAMPLED   STEPS   DENSITY         AV_KEY_LEN  RECOMPUTE   STALE
Events      PK_Events           18.08.2015 7:45:08  1876754 1876754         5       5,328349E-07    4           True        False
Files       PK_Files            09.08.2015 18:15:00 411589  411589          3       2,429608E-06    4           True        False
Revisions   PK_Revisions        07.07.2015 8:10:05  376296  376296          5       2,657482E-06    4           True        False
Revisions   UQ_Revisions_Guid   07.07.2015 8:10:50  376296  376296          200     2,657482E-06    16          True        False
...

From MSDN (sp_show_statistics):

RECOMPUTE. A Boolean value. True indicates that this statistic will be automatically updated the next time it is used, if it is determined to be stale.

All indexes were created without STATISTICS_NORECOMPUTE option. Database is used intensively every day. One more detail: Revisions table now has 442067 records, but statistics for this table were build on 376296 rows as I see in sp_show_statistics output.

How I can keep the statistics up-to-date? Thanks!

Yuriy Gavrishov
  • 4,341
  • 2
  • 17
  • 29

1 Answers1

0

You can use "UPDATE STATISTICS"

https://technet.microsoft.com/en-us/library/ms174025(v=sql.110).aspx

ErikEJ
  • 40,951
  • 5
  • 75
  • 115
  • Thanks, but I’d be interested to know how to keep statistics up-to-date automatically. – Yuriy Gavrishov Oct 08 '15 at 13:09
  • The statistics are automatically updated by the Query processor if they become stale. I think (have not been able to find any evidence, but it can be tested) that SqlCeEngine.Compact would force a receration of statistics. More info on the meaning of "Stale" here: https://technet.microsoft.com/en-us/library/ms174108(v=sql.110).aspx – ErikEJ Oct 08 '15 at 13:26