poniedziałek, 21 listopada 2011

Statystyki

Wyszukiwanie dat aktualizacji statystyk dla konkretnej tabeli
SELECT 
 T.object_id, 
 T.name, 
 S.name AS stats_name, 
 S.auto_created, 
 STATS_DATE(T.object_id, S.stats_id) AS [stats_date] 
FROM sys.tables T join sys.stats S  ON T.object_id = S.object_id 
WHERE T.type = 'U' AND T.name = 'INVENTTRANS'
ORDER BY [stats_date] DESC
To samo dla wersji SQL 2000
SELECT 
 o.name AS [table_name], 
 i.name AS [stats_name], 
 STATS_DATE(o.id, i.indid) AS [stats_date] 
FROM dbo.sysobjects o join dbo.sysindexes i ON o.id = i.id
WHERE o.name = 'INVENTTRANS'
ORDER BY [stats_date] DESC
Szczegółowe informacje o statystyce
DBCC SHOW_STATISTICS(INVENTTRANS, '_WA_Sys_00000041_5793BE78')
Aktualizacja statystyk
UPDATE STATISTICS table_or_indexed_view_name 
    [ 
        { 
            { index_or_statistics__name }
          | ( { index_or_statistics_name } [ ,...n ] ) 
                }
    ] 
    [    WITH 
        [ 
            [ FULLSCAN ] 
            | SAMPLE number { PERCENT | ROWS } ] 
            | RESAMPLE 
            |  [ ,...n ]
        ] 
        [ [ , ] [ ALL | COLUMNS | INDEX ] 
        [ [ , ] NORECOMPUTE ] 
    ] ;