locked
update table stats in SQL Server 2016 RRS feed

  • Question

  • Hi,

    We are using SQL Server 2016.

    How can I  check the update table stats for top 100 tables?

    Please help.

    Thanks




    • Edited by biltz Sunday, August 26, 2018 5:52 AM
    Sunday, August 26, 2018 5:52 AM

Answers

  • Look at Ola's script to maintain indexes and statistics 

    https://ola.hallengren.com/sql-server-index-and-statistics-maintenance.html

    https://docs.microsoft.com/en-us/sql/t-sql/statements/update-statistics-transact-sql?view=sql-server-2017

    /*

    SAMPLE is useful for special cases in which the query plan, based on default sampling, is not optimal. In most situations, it is not necessary to specify SAMPLE because the query optimizer uses sampling and determines the statistically significant sample size by default, as required to create high-quality query plans.

    Starting with SQL Server 2016 (13.x), sampling of data to build statistics is done in parallel, when using compatibility level 130, to improve the performance of statistics collection. The query optimizer will use parallel sample statistics, whenever a table size exceeds a certain threshold.

    */


    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence



    Wednesday, August 29, 2018 6:51 AM
    Answerer
  • Hi Biltz,

    you will get the number of modifications from each table with the following query:

    -- Information for table dbo.orders
    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(N'dbo.Orders', N'U');
    GO

    Leave the WHERE-clause to get an overview of all statistics objects in the database.


    Uwe Ricken (Blog | Twitter)
    Microsoft Certiied Master - SQL Server 2008
    Microsoft Certified Solution Master - CHARTER Data Platform
    Microsoft Certified Solution Expert - Data Platform
    db Berater GmbH
    Microsoft SQL Server Blog (german only)

    • Proposed as answer by Olaf HelperMVP Monday, August 27, 2018 1:02 PM
    • Marked as answer by Olaf HelperMVP Saturday, November 10, 2018 6:36 AM
    Sunday, August 26, 2018 6:34 AM

All replies

  • Hi Biltz,

    you will get the number of modifications from each table with the following query:

    -- Information for table dbo.orders
    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(N'dbo.Orders', N'U');
    GO

    Leave the WHERE-clause to get an overview of all statistics objects in the database.


    Uwe Ricken (Blog | Twitter)
    Microsoft Certiied Master - SQL Server 2008
    Microsoft Certified Solution Master - CHARTER Data Platform
    Microsoft Certified Solution Expert - Data Platform
    db Berater GmbH
    Microsoft SQL Server Blog (german only)

    • Proposed as answer by Olaf HelperMVP Monday, August 27, 2018 1:02 PM
    • Marked as answer by Olaf HelperMVP Saturday, November 10, 2018 6:36 AM
    Sunday, August 26, 2018 6:34 AM
  • Here you go

    SELECT 
    object_name(si.[object_id]) AS [TableName]
    , CASE 
    WHEN si.[index_id] = 0 then 'Heap'
    WHEN si.[index_id] = 1 then 'CL'
    WHEN si.[index_id] BETWEEN 2 AND 250 THEN 'NC ' + RIGHT('00' + convert(varchar, si.[index_id]), 3)
    ELSE ''
      END AS [IndexType]
    , si.[name] AS [IndexName]
    , si.[index_id] AS [IndexID]
    , CASE
    WHEN si.[index_id] BETWEEN 1 AND 250 AND STATS_DATE (si.[object_id], si.[index_id]) < DATEADD(m, -1, getdate()) 
    THEN '!! More than a month OLD !!'
    WHEN si.[index_id] BETWEEN 1 AND 250 AND STATS_DATE (si.[object_id], si.[index_id]) < DATEADD(wk, -1, getdate()) 
    THEN '! Within the past month !'
    WHEN si.[index_id] BETWEEN 1 AND 250 THEN 'Stats recent'
    ELSE ''
      END AS [Warning]
    , STATS_DATE (si.[object_id], si.[index_id]) AS [Last Stats Update]
    FROM sys.indexes AS si
    WHERE OBJECTPROPERTY(si.[object_id], 'IsUserTable') = 1
    ORDER BY [TableName], si.[index_id]


    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Sunday, August 26, 2018 9:48 AM
    Answerer
  • Thanks

    One more question.

    I have lot of tables which have hundreds of millions records. If I am manually updating the statistics of these tables, what sample percent I should put?

    Also, If I am using sp_updatestats to update all statistics of this database which is around 6TB. what will be the sample percent SQL Server will use?

    Thanks




    • Edited by biltz Wednesday, August 29, 2018 6:41 AM
    Wednesday, August 29, 2018 6:41 AM
  • Look at Ola's script to maintain indexes and statistics 

    https://ola.hallengren.com/sql-server-index-and-statistics-maintenance.html

    https://docs.microsoft.com/en-us/sql/t-sql/statements/update-statistics-transact-sql?view=sql-server-2017

    /*

    SAMPLE is useful for special cases in which the query plan, based on default sampling, is not optimal. In most situations, it is not necessary to specify SAMPLE because the query optimizer uses sampling and determines the statistically significant sample size by default, as required to create high-quality query plans.

    Starting with SQL Server 2016 (13.x), sampling of data to build statistics is done in parallel, when using compatibility level 130, to improve the performance of statistics collection. The query optimizer will use parallel sample statistics, whenever a table size exceeds a certain threshold.

    */


    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence



    Wednesday, August 29, 2018 6:51 AM
    Answerer