July 22, 2014 at 11:11 pm
Hi All,
I wonder is there any good script for index maintenance ? ..especially how to define of fillfactor number on certain index ...
as we know in the production there will be a lot of tables in 1 database ..and we may use indexes select /update/delete.So it will be different number of fill factor for indexes...
At the moment I use this script but it will always set the same number of fillfactor for ALL of INDEXES ...
Here is the script :
/***************************************************************************************************/
-- Cursor WHILE loop for all db-s on the SQL Server instance
DECLARE @CurrentDB sysname
DECLARE curDatabase CURSOR FAST_FORWARD FOR
SELECT name FROM master.sys.databases
--SELECT * FROM master.sys.databases
WHERE name ='DB1'
OPEN curDatabase
FETCH NEXT FROM curDatabase INTO @CurrentDB
WHILE ( @@FETCH_STATUS = 0)
BEGIN
/********************************/
--print @CurrentDB
--
declare @sql1 varchar(100)
select @sql1 = 'USE ' + @CurrentDB + ';' + '
'
--EXEC sp_sqlexec @sql1
--print @sql1
declare @sql2 varchar(max)
select @sql2 =
-- Ensure a USE <databasename> statement has been executed first.
'
SELECT DB_NAME() AS DataBaseName;
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 > 10.0 AND page_count> 100 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;
-- 50 is an arbitrary decision point at which to switch between reorganizing and rebuilding.
IF @frag < 50.0
SET @command = N''ALTER INDEX '' + @indexname + N'' ON '' + @schemaname + N''.'' + @objectname + N'' REORGANIZE'';
IF @frag >= 50.0
SET @command = N''ALTER INDEX '' + @indexname + N'' ON '' + @schemaname + N''.'' + @objectname + N'' REBUILD WITH (FILLFACTOR=90)'';
IF @partitioncount > 1
SET @command = @command + N'' PARTITION='' + CAST(@partitionnum AS nvarchar(10));
EXEC (@command);
--PRINT (@command);
PRINT N''Executed: '' + @command;
-- These section commented out for reorganizing only and no rebuilding.
-- SET @command = N''ALTER INDEX '' + @indexname + N'' ON '' + @schemaname + N''.'' + @objectname + N'' REORGANIZE'';
----PRINT (@command);
--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;
'
declare @sql varchar(max)
select @sql = @sql1 + @sql2
EXEC sp_sqlexec @sql
--print @sql
FETCH NEXT FROM curDatabase INTO @CurrentDB
END -- curDatabase WHILE loop
CLOSE curDatabase
DEALLOCATE curDatabase
July 23, 2014 at 12:17 am
The best scripts around are Ola Hallengren's, http://ola.hallengren.com.
😎
July 23, 2014 at 12:20 am
Eirikur Eiriksson (7/23/2014)
The best scripts around are Ola Hallengren's, http://ola.hallengren.com.😎
I have checked on that script ..but dont u think it is very complicated and difficult to be understood ? if i dont understand all the statements ..i dont dare to implement it ...
July 23, 2014 at 12:42 am
murnilim9 (7/23/2014)
I have checked on that script ..but dont u think it is very complicated and difficult to be understood ? if i dont understand all the statements ..i dont dare to implement it ...
I agree with your sentiment of "I don't understand it so I won't implement it" - but a) it is a very good script and b) if you take your time and break it down into chunks, it is understandable.
It is daunting when you see a script of that length for the first time and tempting to put down the mental shutters - do persevere with it - it is a very good wheel that does not need re-inventing.
-------------------------------Posting Data Etiquette - Jeff Moden [/url]Smart way to ask a question
There are naive questions, tedious questions, ill-phrased questions, questions put after inadequate self-criticism. But every question is a cry to understand (the world). There is no such thing as a dumb question. ― Carl Sagan
I would never join a club that would allow me as a member - Groucho Marx
July 23, 2014 at 12:56 am
Stuart Davies (7/23/2014)
murnilim9 (7/23/2014)
I have checked on that script ..but dont u think it is very complicated and difficult to be understood ? if i dont understand all the statements ..i dont dare to implement it ...
I agree with your sentiment of "I don't understand it so I won't implement it" - but a) it is a very good script and b) if you take your time and break it down into chunks, it is understandable.
It is daunting when you see a script of that length for the first time and tempting to put down the mental shutters - do persevere with it - it is a very good wheel that does not need re-inventing.
You give me a good motivation !!!
thank you so muchhh.,.Will do!
July 23, 2014 at 1:58 am
Ola also provides a excellent documentation for all his maintenance solutions, just click the link on the left side under "Menu"-title. The documentation has all the parameters explained as well as a number of examples.
Besides the documentation, definitely check the code also, it's a good way to pick up some good coding practice 🙂
July 23, 2014 at 2:21 am
Hi,
if you need to set a different fillfactor for a specific group of tables I think you could duplicate your existing script to manage two different sets of tables, the first to set for example the ff to 90 and the second to set the ff to 80.
This is a very interesting discussion about ff :
http://www.brentozar.com/archive/2013/04/five-things-about-fillfactor/
In my opinion the fact that the ff default during the creation of an index is 100 and that Microsoft advises that an incorrect fillfactor could cause many more performances issues than a ff of 100 should be considered, so a couple of values for two different sets of tables that you really checked and tuned with a specific ff could be enough to reach your target.
Kind regards.
July 23, 2014 at 3:49 am
You might also want to take a look at Michelle Ufford's scripts[/url].
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
July 24, 2014 at 10:35 pm
Stuart Davies (7/23/2014)
murnilim9 (7/23/2014)
I have checked on that script ..but dont u think it is very complicated and difficult to be understood ? if i dont understand all the statements ..i dont dare to implement it ...
I agree with your sentiment of "I don't understand it so I won't implement it" - but a) it is a very good script and b) if you take your time and break it down into chunks, it is understandable.
It is daunting when you see a script of that length for the first time and tempting to put down the mental shutters - do persevere with it - it is a very good wheel that does not need re-inventing.
Hey ... I just applied OLA'script in my testing server and it works fine 🙂 but in his example of "EXEC stored proc such as :
EXECUTE dbo.IndexOptimize
@databases = 'AdventureWorks',
@FragmentationLow = NULL,
@FragmentationMedium = 'INDEX_REORGANIZE,INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE',
@FragmentationHigh = 'INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE',
@FragmentationLevel1 = 5,
@FragmentationLevel2 = 30,
@Fillfactor = 70
@Indexes = 'AdventureWorks.Production.Product'
It will set fillfactor to all indexes in that particular table ..but what about if I want to set fillfactor to only 1 index in that table ?? because it is fragmented very very fast and cause high pagesplit/sec
Cheers
July 25, 2014 at 6:29 am
murnilim9 (7/24/2014)
Hey ... I just applied OLA'script in my testing server and it works fine 🙂 but in his example of "EXEC stored proc such as :
EXECUTE dbo.IndexOptimize
@databases = 'AdventureWorks',
@FragmentationLow = NULL,
@FragmentationMedium = 'INDEX_REORGANIZE,INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE',
@FragmentationHigh = 'INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE',
@FragmentationLevel1 = 5,
@FragmentationLevel2 = 30,
@Fillfactor = 70
@Indexes = 'AdventureWorks.Production.Product'
It will set fillfactor to all indexes in that particular table ..but what about if I want to set fillfactor to only 1 index in that table ?? because it is fragmented very very fast and cause high pagesplit/sec
Cheers
Most people would use these scripts without specifying a FillFactor parameter, so it maintains the fill factor that the index was originally created with. As you say, there's often no "one size fits all" fill factor and it'll depend on the nature of the key.
July 27, 2014 at 6:08 pm
HowardW (7/25/2014)
murnilim9 (7/24/2014)
Hey ... I just applied OLA'script in my testing server and it works fine 🙂 but in his example of "EXEC stored proc such as :
EXECUTE dbo.IndexOptimize
@databases = 'AdventureWorks',
@FragmentationLow = NULL,
@FragmentationMedium = 'INDEX_REORGANIZE,INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE',
@FragmentationHigh = 'INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE',
@FragmentationLevel1 = 5,
@FragmentationLevel2 = 30,
@Fillfactor = 70
@Indexes = 'AdventureWorks.Production.Product'
It will set fillfactor to all indexes in that particular table ..but what about if I want to set fillfactor to only 1 index in that table ?? because it is fragmented very very fast and cause high pagesplit/sec
Cheers
Most people would use these scripts without specifying a FillFactor parameter, so it maintains the fill factor that the index was originally created with. As you say, there's often no "one size fits all" fill factor and it'll depend on the nature of the key.
thx for your response but could you be more specific ? the example maybe ?
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply