Skip to main content
bad typo
Source Link
Tom V
  • 15.7k
  • 7
  • 64
  • 87

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 enableddisabled and aren't on memory optimized tables.

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 enabled and aren't on memory optimized tables.

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.

Source Link
Tom V
  • 15.7k
  • 7
  • 64
  • 87

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 enabled and aren't on memory optimized tables.