?
Solved

rebuild process hangs..

Posted on 2014-10-22
7
Medium Priority
?
180 Views
Last Modified: 2014-10-29
this code is automated to run everyday.. but only last 2 days it is hanging and thus locking other processes also.. could this code be made more efficient to avoid locks or graciously quit without locking other process, in a failure or delay?
(I believe this is public code from online, perhaps Microsoft)
~~~
USE ROSER
GO
SET NOCOUNT ON;
DECLARE @objectid int;
DECLARE @indexid int;
DECLARE @partitioncount bigint;
DECLARE @schemaname sysname;
DECLARE @objectname sysname;
DECLARE @indexname sysname;
DECLARE @partitionnum bigint;
DECLARE @partitions bigint;
DECLARE @frag float;
DECLARE @command varchar(8000);
 
SELECT
    object_id AS objectid,
    index_id AS indexid,
    partition_number AS partitionnum,
    avg_fragmentation_in_percent AS frag
INTO #CollectFragInfo
FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL , NULL, 'LIMITED')
WHERE avg_fragmentation_in_percent > 10.0 AND index_id > 0 and page_count>99;
-- Declare the cursor for the list of partitions to be processed.
DECLARE partitions CURSOR FOR SELECT * FROM #CollectFragInfo;
 
-- Open the cursor.
OPEN partitions;
 
-- Loop through the Indexes to be defragged.
FETCH NEXT
   FROM partitions
   INTO @objectid, @indexid, @partitionnum, @frag;
 
WHILE @@FETCH_STATUS = 0
    BEGIN;
        SELECT @objectname = o.name, @schemaname = s.name
        FROM sys.objects AS o
        JOIN sys.schemas as s ON s.schema_id = o.schema_id
        WHERE o.object_id = @objectid;
 
        SELECT @indexname = name
        FROM sys.indexes
        WHERE  object_id = @objectid AND index_id = @indexid;
 
        SELECT @partitioncount = count (*)
        FROM sys.partitions
        WHERE object_id = @objectid AND index_id = @indexid;
 
 
IF @frag >= 10.0
    BEGIN;
    SELECT @command = 'ALTER INDEX ' + @indexname +' ON ' + @schemaname + '.' + @objectname + ' REBUILD';
    IF @partitioncount > 1
        SELECT @command = @command + ' PARTITION=' + CONVERT (CHAR, @partitionnum);
    EXEC (@command);
    END;
 
FETCH NEXT FROM partitions INTO @objectid, @indexid, @partitionnum, @frag;
END;

CLOSE partitions;
DEALLOCATE partitions;
0
Comment
Question by:25112
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
7 Comments
 
LVL 51

Expert Comment

by:Vitor Montalvão
ID: 40398685
It's rebuilding indexes when fragmentation it's higher than 10% (IF @frag >= 10.0). Recommendation it's to rebuild only when fragmentation it's higher than 30%. Check the MSDN article about this.
You can also rebuild online. It will take longer but will avoid locks. Just add the ONLINE=ON option to the command.
(...)
IF @frag >= 30.0
     BEGIN;
     SELECT @command = 'ALTER INDEX ' + @indexname +' ON ' + @schemaname + '.' + @objectname + ' REBUILD 
WITH (ONLINE = ON)';
     IF @partitioncount > 1
         SELECT @command = @command + ' PARTITION=' + CONVERT (CHAR, @partitionnum);
     EXEC (@command);
     END;
(...)

Open in new window

0
 
LVL 5

Author Comment

by:25112
ID: 40403362
thank you.

now, this job had never failed for 6 weeks, and suddenly started this way for now 3 days in a row. what can cause this kind of locking to happen all of a sudden?
0
 
LVL 51

Expert Comment

by:Vitor Montalvão
ID: 40403815
There's some good reasons for that happens and I'll bet in one of the following:
- Database growth
- Another processes running in concurrence
0
The Orion Papers

Are you interested in becoming an AWS Certified Solutions Architect?

Discover a new interactive way of training for the exam.

 
LVL 5

Author Comment

by:25112
ID: 40404336
Vitor, I tried your 2 reco, but this morning it again failed.. any thoughts what we can try next?

the 2 steps we implemented:
>30
=ONLINE option.

there is no errors captured in error log.

the process behind this has to be manually killed by command to relieve the lock, and until then there is no relief.
0
 
LVL 51

Expert Comment

by:Vitor Montalvão
ID: 40404528
The process still fails or ends with success?
0
 
LVL 5

Author Comment

by:25112
ID: 40404806
still fails.. it causes lock that affects the application.. manual intervention is then needed to kill the process runs the above code and only then relief.. after 6 weeks, surprising this happens everyday.. pl suggest.
0
 
LVL 51

Accepted Solution

by:
Vitor Montalvão earned 2000 total points
ID: 40405868
Depending on the fragmentation level you won't need to rebuild indexes every day. In 90% of the cases once by week it's enough, so I would recommend you to execute that job weekly on a day and hour where no one is working. Usually Sundays evening so it can take all night long if necessary.
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…
How to fix incompatible JVM issue while installing Eclipse While installing Eclipse in windows, got one error like above and unable to proceed with the installation. This video describes how to successfully install Eclipse. How to solve incompa…

771 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question