May 24, 2011 at 2:35 am
Hi All
We're currently using this script to conditionally reorganize our indexes in SQL Server 2005 Standard (the tables must be online at all times, hence no index rebuilds):
USE [dbname];
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);
-- 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(), NULL, NULL , NULL, 'LIMITED')
WHERE avg_fragmentation_in_percent > 30.0 AND index_id > 0;
-- 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;
SET @command = N'ALTER INDEX ' + @indexname + N' ON ' + @schemaname + N'.' + @objectname + N' REORGANIZE';
IF @partitioncount > 1
SET @command = @command + N' PARTITION=' + CAST(@partitionnum AS nvarchar(10));
EXEC (@command);
PRINT N'Executed: ' + @command;
END;
-- Close and deallocate the cursor.
CLOSE partitions;
DEALLOCATE partitions;
-- Drop the temporary table.
DROP TABLE #work_to_do;
GO
However, we're running the script once for each database as separate job steps. Can someone explain how we might be able to run this script for all databases in a single script, without using a cursor within a cursor.
Many thanks! 😉
May 24, 2011 at 3:15 am
I would suggest using Ola Hallengren's Index Maintenance script:
http://ola.hallengren.com/scripts/IndexOptimize.sql
I don't want to take away from your script, but... why reinvent the wheel?
-- Gianluca Sartori
May 24, 2011 at 3:22 am
I've actually considered using Ola's script, given it's popularity. However, I like to make sure I understand all of the scripts that I use and I sure ain't at Ola's level...yet 🙂
May 24, 2011 at 3:24 am
I agree with above comments. I am using OLA script since long and recently he upgraded the script with additional features.
Use the script, it will make your life more easy and OLA always ready to help if you find any problem or confusion in his script.
----------
Ashish
May 24, 2011 at 3:29 am
Fair point.
If you don't want to use a cursor, you can use sp_MSForEachDB.
EXEC sp_MSForEachDB '
USE [?];
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);
-- 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(), NULL, NULL , NULL, ''LIMITED'')
WHERE avg_fragmentation_in_percent > 30.0 AND index_id > 0;
-- 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;
SET @command = N''ALTER INDEX '' + @indexname + N'' ON '' + @schemaname + N''.'' + @objectname + N'' REORGANIZE'';
IF @partitioncount > 1
SET @command = @command + N'' PARTITION='' + CAST(@partitionnum AS nvarchar(10));
EXEC (@command);
PRINT N''Executed: '' + @command;
END;
-- Close and deallocate the cursor.
CLOSE partitions;
DEALLOCATE partitions;
-- Drop the temporary table.
DROP TABLE #work_to_do;
GO'
As you can see, the code becomes a bit ugly and hard to maintain. Moreover, sp_MSForEachDB returns also system databases.
Hope this helps,
Gianluca
-- Gianluca Sartori
May 24, 2011 at 4:17 am
Thanks Gianluca.
I usually steer clear of undocumented procedures, particularly as sp_MSforeachdb tends to miss databases every now and again. However, in this case, I may have to conceed.
Any other suggestions?
May 24, 2011 at 4:20 am
Logically if I have to filter the database name and then do the activity one by one in each database then cursor is the only option I can see here.
----------
Ashish
May 24, 2011 at 4:24 am
Phineas Gage (5/24/2011)
... sp_MSforeachdb tends to miss databases every now and again.
Hmmm, I didn't know this. Do you have a reference?
-- Gianluca Sartori
May 24, 2011 at 4:27 am
crazy4sql (5/24/2011)
Logically if I have to filter the database name and then do the activity one by one in each database then cursor is the only option I can see here.
You could also add something like this inside the script:
IF '?' NOT IN ('master', 'model', 'msdb', 'tempdb')
BEGIN
--reindex
END
IMHO it would only make the code even worse, but it's an option.
-- Gianluca Sartori
May 24, 2011 at 4:37 am
Gianluca Sartori (5/24/2011)
Phineas Gage (5/24/2011)
... sp_MSforeachdb tends to miss databases every now and again.Hmmm, I didn't know this. Do you have a reference?
It's something that I've read every now and again...Aaron Bertrand's blog here, for example (although he did come up with a workaround).
Personally, I'd like a solution that is both documented and supported, if possible.
May 24, 2011 at 5:58 am
Phineas Gage (5/24/2011)
Gianluca Sartori (5/24/2011)
Phineas Gage (5/24/2011)
... sp_MSforeachdb tends to miss databases every now and again.Hmmm, I didn't know this. Do you have a reference?
It's something that I've read every now and again...Aaron Bertrand's blog here, for example (although he did come up with a workaround).
Personally, I'd like a solution that is both documented and supported, if possible.
Thanks for the pointer. Very interesting, indeed.
-- Gianluca Sartori
May 26, 2011 at 7:18 am
1) sp_msforeachdb IS documented - you can generate the script of it's code and there is your documentation. You can also fix it with your own improvements.
2) ola.hallengren.com's stuff is VERY WELL documented. Do yourself a favor and GET HIS STUFF, learn it and USE IT. Again you have full source code so it IS supported - by you.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply