July 7, 2011 at 8:44 am
Hi all,
I have recently implemented a stored procedure to rebuild or reorganize indexes on our SQL Server instances based on the levels of fragmentation. The way I went about doing this was creating the stored procedure in the master database with a 'sp_' prefix and then marking it a system object so that I am able to execute in the context of another database. This is necessary because it makes use of various system tables to detect the level of fragmentation present. This has been working fine.
I am not sure, however, that I totally like this implementation, although I couldn't really find a better way to do it. Certainly, I could have created the proc in every exisiting database and created the proc in every future database to avoid having it in master, however that can result in a lot of maintenance. I also thought about creating it in every database and then creating in model so that each new DB would then be created with it, but I don't entirely like that idea either. I have read some fairly well known SQL blogs that implemented something like this the same way I did, however I just don't really like creating things in the master database because you may be subjecting yourself to problems with service packs, etc. I was curious to see how others out there have dealt with this type of situation. Any feedback/suggestions would be appreciated.
Thanks,
Adam
July 7, 2011 at 8:52 am
I am sure the implementation is very good, but I don't like re-inventing the wheel.
Why are you using a home maid procedure when there's Ola Hallengren's fine script[/url]?
Also, I am sure you don't need to place the procedure in the master database: you can create a "Tools" database and run the script against the desired DB. However, Ola's script handles this just fine.
Hope this helps
Gianluca
-- Gianluca Sartori
July 7, 2011 at 9:07 am
Thanks for the response - I should note the script isn't entirely home made, I picked it up from MS and altered it slightly to fit our needs.
Also, I'm not quite sure how I would go about creating a proc in a database and then having it execute within the context of another database because I would have to prefix it with the database name. Could you elaborate on this more? I actually tried this solution as well and forgot to mention it in my original post and would prefer to go this route if possible. For example, I created an 'Admin' database, I could execute the proc, but it wouldn't actually rebuild/reorg the indexes when I ran it in the context of another database. For example, I would call 'EXEC Admin.dbo.usp_RebuildIndexes 'DB_name'' and nothing would happen. The proc would execute fine, but nothing was rebuilt/reorganized. For reference, the query I'm using in the proc to build the list of indexes to rebuild/reorg is:
SELECT
object_id AS objectid,
index_id AS indexid,
partition_number AS partitionnum,
avg_fragmentation_in_percent AS frag
FROM sys.dm_db_index_physical_stats (DB_ID(@DatabaseName), NULL, NULL , NULL, 'SAMPLED')
WHERE avg_fragmentation_in_percent > 10.0 AND index_id > 0 AND page_count > 1000;
I pass in the @DatabaseName variable when I execute the proc.
Thanks,
Adam
July 7, 2011 at 9:17 am
The entire body of the procedure I'm using is:
CREATE PROCEDURE [dbo].[usp_RebuildIndexes] (@DatabaseName varchar(255))
AS
SET NOCOUNT ON;
DECLARE @objectid int;
DECLARE @indexid int;
DECLARE @partitioncount bigint;
DECLARE @schemaname nvarchar(130);
DECLARE @objectname nvarchar(130);
DECLARE @indexname nvarchar(130);
DECLARE @partitionnum bigint;
DECLARE @partitions bigint;
DECLARE @frag float;
DECLARE @command nvarchar(4000);
DECLARE @statscommand nvarchar(4000);
-- Conditionally select tables and indexes from the sys.dm_db_index_physical_stats function
-- and convert object and index IDs to names.
SELECT
object_id AS objectid,
index_id AS indexid,
partition_number AS partitionnum,
avg_fragmentation_in_percent AS frag
INTO #work_to_do
FROM sys.dm_db_index_physical_stats (DB_ID(@DatabaseName), NULL, NULL , NULL, 'SAMPLED')
WHERE avg_fragmentation_in_percent > 10.0 AND index_id > 0 AND page_count > 1000;
-- Declare the cursor for the list of partitions to be processed.
DECLARE partitions CURSOR FOR SELECT * FROM #work_to_do;
-- Open the cursor.
OPEN partitions;
-- Loop through the partitions.
WHILE (1=1)
BEGIN;
FETCH NEXT
FROM partitions
INTO @objectid, @indexid, @partitionnum, @frag;
IF @@FETCH_STATUS < 0 BREAK;
SELECT @objectname = QUOTENAME(o.name), @schemaname = QUOTENAME(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 = QUOTENAME(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;
-- 30 is an arbitrary decision point at which to switch between reorganizing and rebuilding.
IF @frag < 30.0
BEGIN
SET @command = N'ALTER INDEX ' + @indexname + N' ON ' + QUOTENAME(@DatabaseName)+ N'.' + QUOTENAME(@schemaname) + N'.' + QUOTENAME(@objectname) + N' REORGANIZE';
SET @statscommand = 'UPDATE STATISTICS '+ QUOTENAME(@DatabaseName)+ N'.' + QUOTENAME(@schemaname) + N'.' + QUOTENAME(@objectname) + '(' + @indexname + ') WITH INDEX, RESAMPLE'
PRINT @command
PRINT @statscommand
END
IF @frag >= 30.0
BEGIN
SET @command = N'ALTER INDEX ' + @indexname + N' ON '+ QUOTENAME(@DatabaseName)+ N'.' + QUOTENAME(@schemaname) + N'.' + QUOTENAME(@objectname) + N' REBUILD';
PRINT @command
SET @statscommand = ' '
END
IF @partitioncount > 1
BEGIN
SET @command = @command + N' PARTITION=' + CAST(@partitionnum AS nvarchar(10));
END
EXEC (@command);
EXEC (@statscommand)
END;
-- Close and deallocate the cursor.
CLOSE partitions;
DEALLOCATE partitions;
-- Drop the temporary table.
DROP TABLE #work_to_do;
July 7, 2011 at 9:21 am
I think you would have to use dynamic sql: (untried)
DECLARE @DatabaseName nvarchar(128)
SET @DatabaseName = 'model'
DECLARE @sql nvarchar(max)
SET @sql = '
SELECT
object_id AS objectid,
index_id AS indexid,
partition_number AS partitionnum,
avg_fragmentation_in_percent AS frag
FROM '+ @DatabaseName +'.sys.dm_db_index_physical_stats ('+ CAST(DB_ID(@DatabaseName) AS varchar(10)) + ', NULL, NULL , NULL, ''SAMPLED'')
WHERE avg_fragmentation_in_percent > 10.0 AND index_id > 0 AND page_count > 1000;'
EXEC(@sql)
I know that throwing away a good piece of code is a pain, but let me insist in my suggestion: use Ola's script. You'll thank me later.
-- Gianluca Sartori
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply