Sql-server – the impact of temporarily setting ALLOW_PAGE_LOCKS ON

fragmentationlockingsql serversql-server-2012

The MSDN ALTER INDEX page says "An index cannot be reorganized when ALLOW_PAGE_LOCKS is set to OFF."

I'm modifying our index reorg/rebuild process to be sensitive to indexes that have ALLOW_PAGE_LOCKS turned OFF. When I detect the index has the option turned off, I perform the following steps:

BEGIN TRANSACTION
BEGIN TRY
    ALTER INDEX [MyIndex] ON [dbo].[MyTable] SET (ALLOW_PAGE_LOCKS = ON);
    ALTER INDEX [MyIndex] ON [dbo].[MyTable] REBUILD
        WITH (
            SORT_IN_TEMPDB = ON
            , MAXDOP = 1
            , PAD_INDEX = OFF
            , FILLFACTOR = 90
            , ALLOW_PAGE_LOCKS = OFF
            , ALLOW_ROW_LOCKS = ON
            , IGNORE_DUP_KEY = OFF
            , DATA_COMPRESSION = NONE
            );
    ALTER INDEX [MyIndex] ON [dbo].[MyTable] SET (ALLOW_PAGE_LOCKS = OFF);
    COMMIT TRANSACTION
END TRY
BEGIN CATCH
    ROLLBACK TRANSACTION
END CATCH

Presuming the database is not actually "in-use" at the time this rebuild process starts, does this pattern present any issues? Does setting PAGE LOCKS ON cause any side-effects?

Note – the options, such as FILLFACTOR = 90 are taken from the current definition of the index just prior to starting the reorg/rebuild process, and a decision is made whether to reorg or rebuild based on the number of pages in the index and the degree of fragmentation. This question is NOT about these settings being "good" or "bad", it is ONLY about the impact of temporarily turning ALLOW_PAGE_LOCKS to `ON', to allow the index to be defragmented.

Also, I realize the ALTER INDEX ... REBUILD statement has ALLOW_PAGE_LOCKS = OFF, while the very next statement also turns it off… this is to allow the ALTER INDEX ... REORG statement, which does not have any options present, to run properly, while also resetting the ALLOW_PAGE_LOCKS setting off once it's completed.

I've wrapped the alter index statements in a transaction for only the indexes that have ALLOW_PAGE_LOCKS OFF so that if anything goes wrong during the rebuild process the index is not modified.

Also, note, we are not using Ola Hallengren's solution due to corporate policy.

EDIT:

From @Shanky's comment, I've confirmed that ALTER INDEX ... REBUILD does NOT require ALLOW_PAGE_LOCKS ON; this option is only required when reorganizing an index. I've modified my code to do the transaction-wrapping shown above only when performing a reorganize where the index already has ALLOW_PAGE_LOCKS turned off.

Best Answer

Presuming the database is not actually "in-use" at the time this rebuild process starts

Assuming what you are presuming, I don't what side-effect it could have, outside of the obvious allowing for index REORG to happen :-).

Of course, I have no proof, but not sure what proof to look for that nothing bad will happen when nothing is happening in the DB outside of this REORG operation.

I am also not aware of there being any analogy here to Foreign Keys being untrusted, and hence not considered by the Query Optimizer, if created using the WITH NOCHECK option to skip validation (and not trusted until altering using WITH CHECK).

Regarding the notion of index property vs structure of the index pages: I would say that this could be answered by altering a large index to just turn this option ON and OFF. If it happens instantly, then it is not writing the change to all pages. And, it is also probably a moot point since even if there is a change, it will be handled via the ALTER INDEX [MyIndex] ON [dbo].[MyTable] SET (ALLOW_PAGE_LOCKS = OFF); and completed before the COMMIT. Still, I can't imagine this being anything other than a simple index property, since it can't change on a per-page basis. Of course, this can also be seen by using DBCC PAGE to view the contents of an index before and after you make this change.

Related Solutions

Sql-server – How to disable or delete an index

Is there any way to tell SQL to stop using an index?

That would be disabling an index:

alter index IX_YourIndex
on dbo.YourTable
disable;
go

But that, too, will be running into concurrency issues on a highly transactional object, as that operation above still requires a schema-modify (Sch-M) lock on the object.

Or is it possible to run a query that will attempt to obtain an exclusive lock on the index, wait indefinitely, and then delete the index?

That would be the default behavior of SQL Server. The only "command timeout" is going to be enforced by client-side software. I'm going to assume that you're using some sort of GUI to try to delete/disable the index (SSMS maybe?). Either way, if you were to put this in a new query window (SSMS), it would wait indefinitely for the locked resource to become available so it can be granted the necessary locking:

drop index IX_YourIndex
on dbo.YourTable;
go

This is one of the reasons why it's almost always easiest to do these types of operations during a slow time, or a maintenance window. That typically can ensure little to no user activity, making these simple tasks...simple and quick.

And if you want to see what/who your drop index request is waiting on, you can run a query similar to this:

select
    session_id,
    command,
    wait_type,
    wait_time,
    blocking_session_id,
    wait_resource
from sys.dm_exec_requests
where command = 'drop index';

Sql-server – deteriorating stored procedure running times

What is up with FROM part JOIN model ON 1=1? This the same as FROM part, model, which is a cartesian join and will result in a very large number of rows. Is that join supposed to be like that?

You will likely help us help you if you provide details about the tables involved. Please "script" the definition of the tables, along with any indexes defined on those tables.

This sounds like a classic case of parameter sniffing resulting in good plan/bad plan choices for various scenarios in your data.

You may be able to get more reliable performance by making SQL Server cache different plans for different scenarios by using sp_executesql, as in the following example:

CREATE PROCEDURE [dbo].[create_grid_materials2] 
(
    @partlistid bigint
    , @pid bigint
    , @masterid bigint
)
AS
BEGIN
    begin
        DECLARE @cmd NVARCHAR(MAX);

        SET @cmd = '   
        INSERT INTO material (partid, personid, modelID)
        SELECT 
            partid = part.id
            , personid = @pid
            , modelid = model.id  
        FROM part
            INNER JOIN model ON 1=1
        WHERE (
            model.masterid = ' + CONVERT(NVARCHAR(50), @masterid) + ' 
                AND model.modelSetID IS NULL
                AND part.partlistid = ' + CONVERT(NVARCHAR(50), @partlistid) + '
                AND (
                    part.partType = 100 
                    or part.partType=120 
                    or part.partType = 130
                )
            )
            AND NOT EXISTS (
                SELECT 1 
                FROM material AS a1 
                WHERE a1.partid = part.id 
                    AND a1.personid=@pid 
                    AND a1.modelid=model.id
                )';
        DECLARE @Params VARCHAR(200);
        SET @Params = '@pid INT';
        EXEC sys.sp_executesql @cmd
            , @Params
            , @pid = @pid;
    end
End

The above code will cause a new plan to be generated for each combination of @partlistid, and @masterid.

The presumption here is some combinations of those two variables lead to a very small number of rows, whereas some combinations lead to a very large number of rows.

Forcing a plan for each combination allows SQL Server to generate more efficient plans for each. I've explicitly not included @pid since you probably want to try it with a fairly small number of combinations first; adding a third variable to the mix will make for an exponentially larger number of possible plans.

Best Answer

Related Solutions

Sql-server – How to disable or delete an index

Sql-server – deteriorating stored procedure running times

Related Question