Truncate table updating statistics

Use this function only when you determine there is an immediate need for updated CBO statistics for a particular table or limited set of tables.

For example, use this function when the following occur: Warning: Large tables can cause very long run times.

Test Table FROM 'C:\scripts\Test Table100.dat' WITH (DATAFILETYPE='native') GO truncate table Test Table GO BULK INSERT dbo.

Test Table FROM 'C:\scripts\Test Table1000.dat' WITH (DATAFILETYPE='native') GO truncate table Test Table GO :r ".\scripts\Test Table Individual10.sql" GO truncate table Test Table GO :r ".\scripts\Test Table Individual100.sql" GO truncate table Test Table GO :r ".\scripts\Test Table Individual1000.sql" GO truncate table Test Table GO :r ".\scripts\Test Table Multiple10.sql" GO truncate table Test Table GO :r ".\scripts\Test Table Multiple100.sql" GO truncate table Test Table GO :r ".\scripts\Test Table Multiple1000.sql" GO As you would expect the individual inserts statements performed the worst by quite a large margin when looking at the total duration and used marginally more CPU than the other two options.

If the table has a DBSTATC entry, then the Analyse needed flag is turned off in the table, if set.

(The flag is reset only if you use Accuracy High.) A success message is displayed when the update is completed, and the Last statistics fields are updated to show the current time and date.

I've created scripts for each of these cases, including data files for the bulk insert, which you can download from the following link.

Now I think it's safe to say which update will perform better but let's see how much better. UPDATE Test Upd Table SET col1='23' WHERE pk=10 UPDATE Test Upd Table SET col2='25' WHERE pk=10 UPDATE Test Upd Table SET col3='27' WHERE pk=10 UPDATE Test Upd Table SET col6=getdate()-1 WHERE pk=10 UPDATE Test Upd Table SET col7=getdate()-1 WHERE pk=10 UPDATE Test Upd Table SET col8=getdate()-1 WHERE pk=10 UPDATE Test Upd Table SET col11='test23' WHERE pk=10 UPDATE Test Upd Table SET col12='test25' WHERE pk=10 UPDATE Test Upd Table SET col13='test27' WHERE pk=10 GO UPDATE Test Upd Table SET col1='23',col2='25',col3='27', col6=getdate()-1,col7=getdate()-1,col8=getdate()-1, col11='test23',col12='test25',col13='test27' WHERE pk=15 GO Looking at the SQL Profiler statistics below it's pretty obvious just how much better the performance is when performing this update using a single statement.

If the runtime is acceptable, then repeat the statistics refresh with High, if desired.

The system updates the CBO statistics for the table.

The last thing we will need in order to run this test is a script to call each of the test cases.

This script is shown below and can be called from SSMS, but notice the use of the :r command which means you will have to run this script in sqlcmd mode for it to execute successfully.

Search for truncate table updating statistics:

truncate table updating statistics-16truncate table updating statistics-13truncate table updating statistics-71

Leave a Reply

Your email address will not be published. Required fields are marked *

One thought on “truncate table updating statistics”