Disable auto stats in SQL Server

Question

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

Answer 1

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:

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:

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.