March 29, 2010 at 9:49 am
I have a Stored Procedure that is run after every full backup of my database. This Stored Procedure analyzed the percentage of use of the Indexes in my database. The process checks if the indexes are blocked in a page.
My process works fine, it takes around 3 hours to complete.
I’m posting the code for your review and comments; I would like to know if this is a healthy way to run a Indexes Maintenance in a Database.
Kind regards,
ALTER procedure [dbo].[vw_sp_RebuildReorganizeIndex]
AS
BEGIN
DECLARE @Databasevarchar(255)
,@Tablevarchar(255)
,@Indexvarchar(255)
,@Percentfloat
,@IndexBlocked varchar(255)
createTable #Index
(
TableNamevarchar(255)
, IndexNamevarchar(255)
, [Percent]float
)
Createtable #PageLockIndex
(
TableNamevarchar(255)
,IndexNamevarchar(255)
)
set nocount on;
set transaction isolation level read uncommitted
Insert #Index
SELECTOBJECT_NAME(i.object_id), i.name, dm.avg_fragmentation_in_percent
FROMsys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, 'detailed') dm
INNER JOIN sys.indexes i ON i.object_id = dm.object_id
ANDi.index_id = dm.index_id
Wheredm.avg_fragmentation_in_percent >= 5
order by dm.avg_fragmentation_in_percent desc
insert #PageLockIndex
SELECT OBJECT_NAME(object_id), [name] FROM sys.indexes WHERE allow_page_locks = 0
DECLARE TableIndexCursor CURSOR FOR
SelectTableName, IndexName, [Percent]
From#Index
whereIndexName is not NULL
andTableName <> 'Log'
OPEN TableIndexCursor
FETCH NEXT FROM TableIndexCursor INTO @Table, @Index, @Percent
WHILE @@FETCH_STATUS <> -1
BEGIN
-- Find if Index is blocked in page
Select@IndexBlocked = pl.IndexName
From#index i
Join#PageLockIndex pl on i. TableName = pl.TableName
Wherepl.IndexName = @index
IF @Percent >= 30
Begin
If @IndexBlocked is null
Begin
execute(' Alter Index ' + @Index + ' on ' + '[' + @Table + ']' + ' Rebuild WITH ( fillfactor = 90 )')
End
Else
execute (' Alter Index ' + @Index + ' on ' + '[' + @Table + ']' + ' SET (ALLOW_PAGE_LOCKS = ON)')
execute (' Alter Index ' + @Index + ' on ' + '[' + @Table + ']' + ' Rebuild WITH ( fillfactor = 90 )')
End
ELSE
If @IndexBlocked is null
Begin
execute (' Alter Index ' + @Index + ' on ' + '[' + @Table + ']' + ' Reorganize')
End
Else
execute (' Alter Index ' + @Index + ' on ' + '[' + @Table + ']' + ' SET (ALLOW_PAGE_LOCKS = ON)')
execute (' Alter Index ' + @Index + ' on ' + '[' + @Table + ']' + ' Reorganize')
FETCH NEXT FROM TableIndexCursor INTO @Table, @Index, @Percent
END
CLOSE TableIndexCursor
DEALLOCATE TableIndexCursor
drop table #Index
drop table #PageLockIndex
END
March 29, 2010 at 10:55 am
What exactly is your definition of a "Blocked Index"?
I've never heard that term before and I find myself wondering what it has to do with allow_page_locks being on or off, since allow_page_locks only indicates whether or not a page can be locked to allow for index-related data searching.
Books Online
allow_page_locks - Determines whether page locks are used in accessing index data
March 29, 2010 at 11:13 am
I do it in order to rebuild or reorganize an index that is block during the process.
is that wrong to do?
March 29, 2010 at 11:25 am
MTY-1082557 (3/29/2010)
I do it in order to rebuild or reorganize an index that is block during the process.is that wrong to do?
Hard to say if it's "wrong" based on what you've given us.
Blocked during which process exactly? I too am curious why you would want to exclude/include indexes in your list to reorganize/rebuild based on the allow_page_locks setting. Other than that odd filter you have, I don't see anything glaringly wrong. (Not that it's necessarly "wrong", just a little odd based on what you've told so far).
Books online has something halfway similar to what you are doing too: http://msdn.microsoft.com/en-us/library/ms188917(SQL.90).aspx (see Example "D").
The Redneck DBA
March 29, 2010 at 11:44 am
With process I meant this stored procedure. Part of my maintenance plan for Indexes is to check the percentage of their use (fragmentation) and Rebuild or Reorganize them. But, sometimes my process stopp because it found blocked indexes.
I’m not basing my process on allow_page_locks settings. I just turn this option on if an Index is blocked. So I can rebuild/reorganize it and continue with the next index.
😀
March 29, 2010 at 11:46 am
Ditto to what Jason said. I'm just confused because I don't know what a blocked index is and what allowing page locks has to do with it.
If you could clarify where you came up with the concept of a blocked index, what it is and how you know it's blocked (other than the allow_page_locks column), it would help me understand what you're achieving.
Other than that, your code looks good. I doubt, however, it's doing what you think it's doing. I base that decision on your choice of where clause filter.
March 29, 2010 at 11:47 am
Ah. We posted at the same time. I see your explanation now.
Do you have the text of the Blocked Index errors you were getting?
March 29, 2010 at 12:09 pm
I didn’t find it. This happened in October 2009. Then I fixed it with the code that you see now. The index is “blocked” because the column that is referred in the Index is blocked. Seems like it doesn’t happen often, but I wanted to control in on my stored procedure to complete the maintenance.
I see that it can be a “normal” behavior. I always want to know what the expert think
March 29, 2010 at 12:29 pm
Actually, I just figured out what's going on in terms that I can understand.
My explanation to myself is:
It's not that you have a blocked index, because honestly, an Index in and of itself cannot be blocked. Only queries and processes can be blocked.
Therefore, what's going on is your Rebuilds and Reorg queries are blocked by other locks and queries. So when you change your locking mechanism on your database (or on the tables, in this particular case), it allows the R/R query to continue because the query simply "works around" the existing table / row locks by going down to the page level and fixing all pages that aren't currently locked. Then, when the currently locked pages free up, the R/R query does them and finishes up.
At least, that's what I think is going on. Feel free to correct me if my mechanics are wrong.
Nice code, BTW. I forgot to mention that.
March 29, 2010 at 12:32 pm
Thank you Brandie
And Yes you are right. Thank you to put the right words. I couldn’t explain it better. You are correct 😀
Viewing 10 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply