sp_updatestats vs Update statistics

Question

Is there any difference between updating the statistics of a table using sp_updatestats with out resample and updating stats of table using UPDATE STATISTICS without sample options(FULLSCAN,SAMPLE PERCENT,RESAMPLE)

exec sp_updatestats vs update statistics tablename

updating the tables using sp_updatestats with default value NO will update the statistics with default sampling rate.

similarly updating statistics of table using UPDATE STATISTICS without sample options(FULLSCAN,SAMPLE PERCENT,RESAMPLE) will also update the table statistics with default sampling.

So is there any difference between both methods? Am I missing anything here ?

Update :

I know that sp_updatestats runs on all the tables but using UPDATE STATISTICS we can update statistics of specific table.

Answer 1

Is there any difference between updating the statistics of a table using sp_updatestats with out resample and updating stats of table using UPDATE STATISTICS without sample options(FULLSCAN,SAMPLE PERCENT,RESAMPLE)

Adding to what is already mentioned by @Gameiswar, the other difference which I know with sp_updatestats, when you run it for all tables of database it ONLY updates statistics of tables which has at least one row changed. BOL says

For disk-based tables, sp_updatestats updates only the statistics that require updating based on the rowmodctr information in the sys.sysindexes catalog view, thus avoiding unnecessary updates of statistics on unchanged rows.

So when you run sp_updatestats and it says statistics for all tables have been updated this is incorrect/misleading only for tables which has at least one row changed was stats updated.

While going with UPDATE STATISTICS you have quite lot of options to use for stats update.

Plus when you rebuild the index with full scan or with default options the stats are updated for that index so no need to rebuild stats again for it.

PS: Whatever the difference may be, I would not use sp_updatestats as the mechanism it uses to update stats is not good, why should i update stats for table if just one row has changed and I have 100K rows in table, it will just consume resources and cause more issue.

EDIT:

If you want to update stats selectively and do not want to run sp_updatestas use below query to filter out outdated stats. This query uses DMF and would work from SQL Server 2008 R2 SP2 , SQL Server 2012 Sp1 and above, Copied from Erin Stellato's blog

NOTE: Realize that different tables may have different thresholds and you will need to tweak the query above for your databases. For some tables, waiting until 15% or 20% of the rows have been modified may be ok. But for others, you may need to update at 10% or even 5%, depending on the actual values and their skew.

SELECT [sch].[name] + '.' + [so].[name] AS [TableName] ,
[ss].[name] AS [Statistic],
[sp].[last_updated] AS [StatsLastUpdated] ,
[sp].[rows] AS [RowsInTable] ,
[sp].[rows_sampled] AS [RowsSampled] ,
[sp].[modification_counter] AS [RowModifications]
FROM [sys].[stats] [ss]
JOIN [sys].[objects] [so] ON [ss].[object_id] = [so].[object_id]
JOIN [sys].[schemas] [sch] ON [so].[schema_id] = [sch].[schema_id]
OUTER APPLY [sys].[dm_db_stats_properties]([so].[object_id],
[ss].[stats_id]) sp
WHERE [so].[type] = 'U'
AND [sp].[modification_counter] > 0--change accordingly
ORDER BY [sp].[last_updated] DESC;

If you want to avoid all such hassle you can also use Ola Hallengren update stats script which takes care of lot of such things.

Answer 2

The code for sp_updatestats is fairly straightforward, you can look at it by creating a copy of the mssqlsystemresource database. Just copy the mdf & ldf files somewhere and attach them as a database with another name.

As can be seen in BOL the statement takes 1 parameter, resample:

ALTER procedure [sys].[sp_updatestats]
    @resample char(8)='NO'
as

I don't think it's a good idea to post the entire procedure here, you could have a look yourself, but basically it's a simple procedure.

Inside the procedure, it loops over the tables in the database, does some basic checks, such as checking a table doesn't have it's clustered index disabled, checks if a table is hekathon (to determine if the list of statistics on a table should be read from sys.stats or sys.indexes), checks if it's a hekathon table again to add fullscan in case of a hekathon table and then executes a simple UPDATE STATISTICS.

Basically one of these three statements is executed on all statistics in your database

-- When resample is yes
UPDATE STATISTICS [sysname].[sysname] [sysname] WITH RESAMPLE
-- When resample is no
UPDATE STATISTICS [sysname].[sysname] [sysname] 
-- For a hekathon table
UPDATE STATISTICS [sysname].[sysname] [sysname] WITH FULLSCAN

Except if the statistics have autostats disabled it also adds a NORECOMPUTE clause

So there isn't a lot of difference between simply calling an UPDATE STATISTICS on each of your statistics if they don't have autostats disabled and aren't on memory optimized tables.

Answer 3

There are differences in how we use them ..

Sp_updatestats:
sp_updatestats executes UPDATE STATISTICS, by specifying the ALL keyword, on all user-defined and internal tables in the database. We can't ask SQL to Update only stats for single table/index using this option,

Update Statistics :
With Update Statistics you can update statistics PerTable/Index..

Is there any difference between updating the statistics of a table using sp_updatestats with out resample and updating stats of table using UPDATE STATISTICS without sample options(FULLSCAN,SAMPLE PERCENT,RESAMPLE)

If 'resample' is not specified, sp_updatestats updates statistics by using the default sampling.

For Update statistics,When none of the sample options (SAMPLE, FULLSCAN, RESAMPLE) are specified, the query optimizer samples the data and computes the sample size by default.

Answer 4

Sampling rate of SP_Updatestats is very low like 0.05%. Especially on VLDB's It is advisable to use DBCC Update statistics on each table to update stats with appropriate sampling. Best would be to perform DBCC update statistics with Full scan. If this is not possible, use sampling rate of 25%.