3

I have couple questions about auto stats in SQL Server:

  1. I know I can use the following statement to check if the database auto stats is disabled, but is there a statement to check if a specific index's auto stats is disabled?

    SELECT DATABASEPROPERTYEX('Databasename','IsAutoUpdateStatistics')
    
  2. If I disable auto stats for an index before my reorg jobs, will it prevent blocking issues in SQL Server?

In the following link there is a statement that you can disable auto stats on a database level https://msdn.microsoft.com/en-us/library/ms188775.aspx

0

1 Answer 1

4

is there a statement to check if a specific index's auto stats is disabled?

You can use sys.sp_autostats or sys.stats to get this information.

sys.sp_autostats can also be used to enable or disable automatic updates for a particular statistics object.

For example, using the AdventureWorks sample database:

EXECUTE sys.sp_autostats 
    @tblname = N'[Production].[TransactionHistory]',
    @flagc = NULL,
    @indname = NULL;

The AUTOSTATS column shows if the statistics will be automatically refreshed:

sp_autostats output

SELECT
    S.name,
    S.stats_id,
    S.auto_created,
    S.user_created,
    S.no_recompute
FROM sys.stats AS S
WHERE
    S.[object_id] = OBJECT_ID(N'[Production].[TransactionHistory]', N'U');

The no_recompute column shows if automatic statistics updates will be skipped:

sys.stats output

If I disable auto stats for an index before my reorg jobs, will it prevent blocking issues?

Reorganizing an index does not update statistics. You probably need to clarify your question to explain the exact problem you are encountering.

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.