March 10, 2015 at 9:57 am
Hi all,
I run the below script on my Prod server. I have some questions regarding this if you guys don't mind.
1. How can I make this script better in terms of running time?
2. when I run this script I get out put of all index names around 200 rows. Where should I put 'set NoCount On'?
3. where do I find history of the output so I can delete them?
4. Since my Database is 1+ TB, I always run default which is 'Limited' (or null). Should I run 'detailed' once a while or just 'limited' works?
Any answers would be appreciated
Thanks you.
Below is the query I run:
USE [msdb]
GO
BEGIN TRANSACTION
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0
IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'[Uncategorized (Local)]' AND category_class=1)
BEGIN
EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'[Uncategorized (Local)]'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
END
DECLARE @jobId BINARY(16)
EXEC @ReturnCode = msdb.dbo.sp_add_job @job_name=N'Indexjob_Test_mydb',
@enabled=1,
@notify_level_eventlog=0,
@notify_level_email=0,
@notify_level_netsend=0,
@notify_level_page=0,
@delete_level=0,
@description=N'No description available.',
@category_name=N'[Uncategorized (Local)]',
@owner_login_name=N'ABC\12345', @job_id = @jobId OUTPUT
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'IndexRebuild_Reorganize',
@step_id=1,
@cmdexec_success_code=0,
@on_success_action=1,
@on_success_step_id=0,
@on_fail_action=2,
@on_fail_step_id=0,
@retry_attempts=0,
@retry_interval=0,
@os_run_priority=0, @subsystem=N'TSQL',
@command=N'USE
[Test_mydb]
go
set nocount on
go
----------Table to Hold Fragmented Objects----
If exists (select * from tempdb.sys.all_objects where name like ''#Reorganize'' )
Drop table #Reorganize
create table #Reorganize
(Schemaname varchar(50),
tablename varchar(50),
Indexname varchar(150),
Fragmentation float)
go
If exists (select * from tempdb.sys.all_objects where name like ''#Rebuild'' )
drop table #Rebuild
create table #Rebuild
(Schemaname varchar(100),
tablename varchar(100),
Indexname varchar(150),
Fragmentation float)
go
--------- where fragmentation level is between 5 to 30 in temptable----
insert into #reorganize(Schemaname,tablename,Indexname,Fragmentation)
select s.name,o.name,i.name,ips.avg_fragmentation_in_percent from sys.objects o left outer join sys.schemas s on
o.schema_id= s.schema_id left outer join sys.indexes i on
o.object_id=i.object_id left outer join sys.dm_db_index_physical_stats (db_id(''[Test_mydb]''), NULL, NULL, NULL, NULL) AS IPS
on i.object_id=IPS.object_id and i.index_id=ips.index_id
where o.type=''U'' and i.index_id > 0 and avg_fragmentation_in_percent between 5 and 30
go
insert into #Rebuild(Schemaname,tablename,Indexname,Fragmentation)
select s.name,o.name,i.name,ips.avg_fragmentation_in_percent from sys.objects o left outer join sys.schemas s on
o.schema_id= s.schema_id left outer join sys.indexes i on
o.object_id=i.object_id left outer join sys.dm_db_index_physical_stats (db_id(''[Test_mydb]''), NULL, NULL, NULL, NULL) AS IPS
on i.object_id=IPS.object_id and i.index_id=ips.index_id
where o.type=''U'' and i.index_id > 0 and avg_fragmentation_in_percent > 30 AND page_count > 5000
-----------Cursor for reorganize---------------------
Declare @cmd varchar(1000)
DECLARE @Iname varchar(250)
DECLARE @Jname varchar(250)
declare @sname varchar(150)
declare @tname varchar(150)
DECLARE db_reindex CURSOR for
select indexname,[SCHEMANAME],tablename from #Reorganize
OPEN db_reindex
FETCH NEXT from db_reindex into @Iname,@sname,@tname
WHILE @@FETCH_STATUS = 0
BEGIN
set @Jname= @sname + ''.''+ @tname
set @cmd= ''Alter INdex '' + @Iname + '' on ''+ @Jname + '' reorganize''
execute (@cmd)
FETCH NEXT from db_reindex into @iname,@sname,@tname
select ''Executed Reindex reorganize for '' + @Jname + '' ''+ @Iname
END
CLOSE db_reindex
DEALLOCATE db_reindex
GO
------------Cursor For Rebuild--------------
Declare @cmd Varchar(1000)
DECLARE @Iname varchar(250)
DECLARE @Jname varchar(250)
declare @sname varchar(150)
declare @tname varchar(150)
DECLARE db_reindex CURSOR for
select indexname,[SCHEMANAME],tablename from #Rebuild
OPEN db_reindex
FETCH NEXT from db_reindex into @Iname,@sname,@tname
WHILE @@FETCH_STATUS = 0
BEGIN
set @Jname= @sname + ''.''+ @tname
set @cmd= ''Alter INdex '' + @Iname + '' on ''+ @Jname + '' rebuild WITH ( SORT_IN_TEMPDB = ON )''
execute (@cmd)
FETCH NEXT from db_reindex into @iname,@sname,@tname
select ''Executed Reindex rebuild for '' + @Jname + '' ''+ @Iname
END
CLOSE db_reindex
DEALLOCATE db_reindex
GO
',
@database_name=N'Test_mydb',
@flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
COMMIT TRANSACTION
GOTO EndSave
QuitWithRollback:
IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave:
GO
March 10, 2015 at 10:34 am
Don't re-invent the wheel. Use Ola Hallengren maintenance solution. You can find it here: http://ola.hallengren.com
-- Gianluca Sartori
March 10, 2015 at 10:52 am
Thanks for your reply spaghettidba. It's hard for me to put Ola's script together. I would appreciate if someone post a script here <30 reorganize, >30 Rebuild Index.
spaghettidba, can you answer other questions for me please?
March 10, 2015 at 11:13 am
There are samples on Ola's site.
This should do:
EXEC IndexOptimize
@databases = 'USER_DATABASES',
@FragmentationLow = NULL,
@FragmentationMedium = 'INDEX_REORGANIZE,INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE',
@FragmentationHigh = 'INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE',
@FragmentationLevel1 = 5,
@FragmentationLevel2 = 30
Please note that it does not reorganize or rebuild indexes with less than 5% fragementation (which is a good idea)
-- Gianluca Sartori
March 10, 2015 at 11:45 am
I do not know if this is what you are looking for as my brain seems to be stuck in neutral this after noon
-- This code is used to create a table of code to de-fragment indexes for the entire instance.
-- It populates the table Scrub.dbo.Fragmentation.
IF OBJECT_ID ('Scrub.dbo.Fragmentation') IS NULL
CREATE TABLE Scrub.dbo.Fragmentation(
DBName VARCHAR(128),
TableName VARCHAR(128),
ExecuteCode VARCHAR(1000),
avg_frag_percent DECIMAL(18, 4)
);
GO
USE master;
GO
TRUNCATE TABLE Scrub.dbo.Fragmentation;
DECLARE @strSQL NVARCHAR(2000)
--SET @strSQL = 'IF ''?'' NOT IN (''master'', ''model'', ''msdb'', ''tempdb'', ''SQLdmRepository'', '
-- + '''ReportServer$B1SQLTempDB'', ''ReportServer$B1SQL'', ''distribution'') RETURN; '
SET @strSQL = 'IF ''?'' NOT IN (''Database1'', ''Database2'', ''Database3'', ''Database4'') RETURN; '
+ 'USE ?; '
+ 'INSERT INTO Scrub.dbo.Fragmentation (DBName, TableName, ExecuteCode, avg_frag_percent) '
+ 'SELECT ''?'' AS DBName, t.name AS TableName, '
+ 'CASE WHEN ips.avg_fragmentation_in_percent < 30 '
+ 'THEN ''ALTER INDEX '' + QUOTENAME(inx.name) + '' ON '' + QUOTENAME(t.name) + '' REORGANIZE; --'' '
+ 'ELSE ''ALTER INDEX '' + QUOTENAME(inx.name) + '' ON '' + QUOTENAME(t.name) + '' REBUILD; --'' '
+ 'END AS ExecuteCode, ips.avg_fragmentation_in_percent '
+ 'FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL , NULL, ''LIMITED'') ips '
+ 'INNER JOIN sys.objects t ON ips.object_id = t.object_id '
+ 'INNER JOIN sys.schemas s ON s.schema_id = t.schema_id '
+ 'INNER JOIN sys.indexes inx ON ips.object_id = inx.object_id and ips.index_id = inx.index_id '
+ 'WHERE 1 = 1 '
-- + 'AND ips.avg_fragmentation_in_percent > 50 '
+ 'AND s.name IS NOT NULL AND page_count > 1000 AND ips.index_id > 0 '
+ 'ORDER BY DBName, t.name DESC; ';
--print @strSQL
EXECUTE sp_MSForEachdB @strSQL;
go
SELECT DBName, TableName, ExecuteCode, avg_frag_percent FROM Scrub.dbo.Fragmentation
WHERE avg_frag_percent > 75
ORDER BY DBName, TableName, ExecuteCode DESC;
March 10, 2015 at 12:04 pm
Thanks spaghettidba and djj. Appreciate it!!!!!!!
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply