I’m going through Pluralsight course on how to Troubleshoot Query Plan for SQL Server.

I’ll just put down some notes for myself here. I’m sure these can be found online easily, but this is my notepad and I use it for quick search and for reference. So here it goes.

See statistics created for a table:

EXEC sp_helpstats 'tablename';

What does the histogram look like? What were rows sampled versus total rows? (for statistics name use the line above):

DBCC SHOW_STATISTICS([tablename], [statisticName]);

See statistics per column – examine the stats.

SELECT  [s].[name],
        [p].[object_id],
        [p].[stats_id],
        [p].[last_updated],
        [p].[rows],
        [p].[rows_sampled],
        [p].[steps],
        [p].[unfiltered_rows],
        [p].[modification_counter]
FROM    [sys].[stats] AS s
CROSS APPLY sys.dm_db_stats_properties
    ([s].object_id, [s].stats_id) AS [p]
WHERE   [s].[object_id] = OBJECT_ID('charge');

Force a full resampling for the statistics, using the whole table:

UPDATE STATISTICS dbo.[tablename] ([statisticsName]) 
WITH FULLSCAN;

RESAMPLE updates statistics using the most recent sampling:

UPDATE STATISTICS dbo.[charge] ([_WA_Sys_00000002_0DAF0CB0]) 
WITH RESAMPLE;

Updating by a specific sample number (percent or rows allowed):

UPDATE STATISTICS dbo.[charge] ([_WA_Sys_00000002_0DAF0CB0]) 
WITH SAMPLE 20 PERCENT;

Execute UPDATE STATISTICS against all user-defined and internal tables in the current database.sp_updatestats updates only the statistics that require updating

EXEC sp_updatestats;