How to check if indexing is running?

  • I have reporting database, which is refreshed every 3 hour. I usually get a Transaction deadlock error whenever a user tries to generate a reporting.

    We have checked, it is when the indexing on the DB is running, the user gets this error.

    Now is it possible to check if the indexing is still running?

  • By 'indexing' do you mean index creation? Rebuilding indexes? Something else?

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • Ya it rebuild index. When we use the DBCC DBREINDEX 'tablename', it takes some time to rebuild it. And if in the mean time if a user queries the same table, gets a transaction deadlock error.

    Let me know if I am missing something here.

    Thanks for your quick turnaround.

  • Ajit-297150 (3/15/2010)


    Ya it rebuild index. When we use the DBCC DBREINDEX 'tablename', it takes some time to rebuild it. And if in the mean time if a user queries the same table, gets a transaction deadlock error.

    Let me know if I am missing something here.

    Thanks for your quick turnaround.

    So, what would you like to do? Give the user a graceful warning? You could add something to the procs that use the table and have it check for the SQL JOB that's doing the reindexing.

    If you're using the Enterprise edition of SQL Server, you can do the reindexing with the ONLINE option and that should take care of things. If you're not, then consider breaking the larger table up into smaller tables (NOT table partitioning which also requires the Enterprise edition) with their own indexes and use a "partitioned view" to put it all back together again. The advantage will be that you probably won't have to reindex all the tables if they are in "date order" because the data won't change much if at all. Only the "active" data will change and since it will be in a much smaller table, the indexes will also be smaller which means they'll reindex much quick. Users may still get an error here and there, but not as many as before.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Why are you performing a reindex operation each 3 hours?

    Maybe it is enough to run the operation over night.

    Nervertheless DBCC DBREINDEX performs a DROP and a CREATE Index operation and on Primaray Keys an exclusive table lock will be held.

  • I think you can use READPAST or WITHNOLOCK hint in the query.

  • ESAT ERKEC (3/16/2010)


    I think you can use READPAST or WITHNOLOCK hint in the query.

    Well, you can ...

    On rare occasions it may even be a good idea. However, there are significant potential drawbacks to this approach

    http://sqlblog.com/blogs/andrew_kelly/archive/2009/04/10/how-dirty-are-your-reads.aspx

  • How about using something like the following to allow a number of retries before timing out of the query the user is running encounters a lock (adjust timing and reries to your own circumstances?

    SET NOCOUNT ON;

    DECLARE @Retry TINYINT;

    DECLARE @Retried TINYINT;

    DECLARE @ErrorMessage NVARCHAR(4000);

    DECLARE @ErrorSeverity INT;

    DECLARE @ErrorState INT;

    DECLARE @Error INT;

    SET LOCK_TIMEOUT 1500

    SET @Retry = 1;

    SET @Retried = 0;

    /**********************

    *Declarations for the process here

    *

    **********************/

    WHILE @Retry = 1 AND @Retried <= 5

    BEGIN

    BEGIN TRY

    SET @Retry = 0;

    /**********************

    *SELECT Statement(s) accessing Facts and Dims where

    *you want to retry if resources are locked, for example

    *when the ETL process has them

    **********************/

    END TRY

    BEGIN CATCH

    SELECT@ErrorMessage = ERROR_MESSAGE(),

    @ErrorSeverity = ERROR_SEVERITY(),

    @ErrorState = ERROR_STATE(),

    @Error = ERROR_NUMBER();

    --SELECT @Retried

    IF @Error = 1222 AND @Retried < 5--Lock Timeout

    BEGIN

    SET @Retried = @Retried + 1;

    SET @Retry = 1;

    WAITFOR DELAY '00:00:05';--Pause for 5 seconds before retrying

    END

    ELSE

    BEGIN

    IF @Error = 1205 AND @Retried < 5 --Deadlock

    BEGIN

    SET @Retried = @Retried + 1;

    SET @Retry = 1;

    WAITFOR DELAY '00:00:05';--Pause for 5 seconds before retrying

    END

    ELSE-- Boot it back 'upstairs'

    RAISERROR (@ErrorMessage, -- Message text.

    @ErrorSeverity, -- Severity.

    @ErrorState -- State.

    );

    END

    END CATCH

    END -- @Retry = 1 AND @Retries <= 5

  • Ajit, are you aware that SQL Server automatically indexes every row as it is added to a table. There is no need to do a separate index build in SQL Server.

    The DBCC REINDEX command is designed to do maintenance on an index that is fragmented. If you do not understand what a fragmented index is, then do a few searches on Google and read what you find.

    Why do you think you need to do a DBCC REINDEX after you have refreshed your reporting data? If it is because you are getting poor query performance, the problem may be due to out of date statistics. Although rebuilding an index will refresh its statistics, you can refresh statistics without needing an index rebuild.

    Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.

    When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara

  • May be you can do/schedule the reindexing in OFF peak hours.

    -------Bhuvnesh----------
    I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

  • Andrew Gothard-467944 (3/16/2010)


    ESAT ERKEC (3/16/2010)


    I think you can use READPAST or WITHNOLOCK hint in the query.

    Well, you can ...

    On rare occasions it may even be a good idea. However, there are significant potential drawbacks to this approach

    http://sqlblog.com/blogs/andrew_kelly/archive/2009/04/10/how-dirty-are-your-reads.aspx%5B/quote%5D

    Those hints are only capable of ignoring shared locks. Rebuilding an index offline acquires and holds a schema-modification lock.

  • Paul White (3/17/2010) Rebuilding an index offline acquires and holds a schema-modification lock.

    why index offline holds a schema-modification lock ? please explain

    -------Bhuvnesh----------
    I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

  • Bhuvnesh (3/17/2010)


    Paul White (3/17/2010) Rebuilding an index offline acquires and holds a schema-modification lock.

    why index offline holds a schema-modification lock ? please explain

    Books Online: ALTER INDEX (Transact-SQL)

    Table locks are applied for the duration of the index operation. An offline index operation that creates, rebuilds, or drops a clustered, spatial, or XML index, or rebuilds or drops a nonclustered index, acquires a Schema modification (Sch-M) lock on the table. This prevents all user access to the underlying table for the duration of the operation.

  • To check for the indexing:

    You could always execute "sp_who2" into a temp table and check to see if there is an input_buffer with "DBCC DBREINDEX 'tablename'" for the tables that are affected by your report.

    This is in lieu of checking to see if the job is running.

  • I started a reindex job it ran for 1hr and 19min. then error "data type text, ntext, image, varchar(max), nvarchar(max), varbinary(max)" there is more but not that important to my question. Did the reindex finish or stop when it encountered this error? I can't find detailed log files.

Viewing 15 posts - 1 through 15 (of 15 total)

You must be logged in to reply to this topic. Login to reply