HST Techno
Kamran Hussain
Download Complete Steps
Statistics gathering in SQL Server means collecting information about the data in tables—like how many rows there are, what kind of values appear in each column, and how often they show up. This helps the SQL Server engine choose the most efficient way to run queries. Stats can be updated automatically when data changes a lot or manually using commands like UPDATE STATISTICS or sp_updatestats. Keeping statistics up to date ensures better performance and faster query results.
When does it happen?
- Automatically when a lot of data changes
- Manually by running commands
How to Check
select s.name as statsname,stats_date(o.object_id,s.stats_id) as LastUpdated,o.name as tablename,s.auto_created,s.user_created from
sys.stats s join sys.objects o ON s.object_id=o.object_id where o.type='U' Order by LastUpdated;
select object_name(sp.object_id) as TableName,s.name as StatsName
,sp.last_updated,sp.rows,sp.rows_sampled,sp.modification_counter
from
sys.stats s
cross apply
sys.dm_db_stats_properties(s.object_id,s.stats_id)
as sp
where OBJECTPROPERTY(s.object_id,'IsUserTable')=1
order by
sp.modification_counter desc;
SELECT
t.name AS TableName,
s.name AS StatsName,
STATS_DATE(s.object_id, s.stats_id) AS LastUpdated
FROM sys.stats s
JOIN sys.tables t ON s.object_id = t.object_id
WHERE t.is_ms_shipped = 0
ORDER BY LastUpdated DESC;
SELECT
name AS DatabaseName,
is_auto_create_stats_on,
is_auto_update_stats_on,
is_auto_update_stats_async_on
FROM sys.databases
WHERE name = 'DB1';
When the automatic create statistics option, AUTO_CREATE_STATISTICS is ON
SELECT OBJECT_NAME(s.object_id) AS object_name,
COL_NAME(sc.object_id, sc.column_id) AS column_name,
s.name AS statistics_name
FROM sys.stats AS s
INNER JOIN sys.stats_columns AS sc
ON s.stats_id = sc.stats_id
AND s.object_id = sc.object_id
WHERE s.name LIKE '_WA%'
ORDER BY s.name;
Scripts for Execute on All Tables of Database
DECLARE @table NVARCHAR(255);
DECLARE @sql NVARCHAR(MAX);
DECLARE table_cursor CURSOR FOR
SELECT QUOTENAME(SCHEMA_NAME(schema_id)) + '.' + QUOTENAME(name)
FROM sys.tables
WHERE is_ms_shipped = 0;
OPEN table_cursor;
FETCH NEXT FROM table_cursor INTO @table;
WHILE @@FETCH_STATUS = 0
BEGIN
SET @sql = 'UPDATE STATISTICS ' + @table + ' WITH FULLSCAN;';
PRINT @sql; -- Optional: see the statements
EXEC sp_executesql @sql;
FETCH NEXT FROM table_cursor INTO @table;
END
CLOSE table_cursor;
DEALLOCATE table_cursor;
Scripts for Execute on All Tables of Database
-- Update all stats with FULLSCAN on a specific table
DECLARE @sql NVARCHAR(MAX) = ''
SELECT @sql += 'UPDATE STATISTICS ' + QUOTENAME(OBJECT_SCHEMA_NAME(object_id)) + '.' + QUOTENAME(OBJECT_NAME(object_id)) +
' WITH FULLSCAN;' + CHAR(13)
FROM sys.stats
WHERE OBJECT_NAME(object_id) LIKE 'TestTable_%'
EXEC sp_executesql @sql;
Enable Auto Stats on Database
ALTER DATABASE DB1 SET AUTO_CREATE_STATISTICS ON; (Create Stats for Single Column if it not Present)
Only touches modified statistics.
EXEC sp_updatestats; ((500+20% of Rows changes))
Update Stats of Specific Tables
UPDATE STATISTICS dbo.TestTable_1;
UPDATE STATISTICS dbo.TestTable_1 WITH FULLSCAN; -- uses all rows
UPDATE STATISTICS dbo.TestTable_1 WITH SAMPLE 50 PERCENT; -- uses 50% sample