Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase «««12345»»»

INDEX DEFRAGMENTATION SCRIPT Expand / Collapse
Author
Message
Posted Wednesday, November 30, 2011 7:44 PM


Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, April 14, 2014 6:35 AM
Points: 5, Visits: 42
Nice script.

For performance purposes, have you considered dynamically specifying the FILLFACTOR on your index rebuilds to help with page splits and IO performance?

Not sure what your database default is for FILLFACTOR, but your script is going to apply the default setting to all your index rebuilds when this is not specified. Since you are already checking for the percent fragmentation in your script, you could specify a lower FILLFACTOR for more fragmented non-clustered indexes and possibly even a zero FILLFACTOR on clustered indexes that currently have a near zero percent fragmentation since they are obviously designed to add all new rows to the end of the table.

This approach allows your clustered indexes to have a higher page density (improving overall IO performance) and allows your non-clustered indexes to handle some number of new records before a page split is needed, but not too much to waste a bunch of space on the drives and in memory. Having too low of a page density can also adversely affect performance by requiring the buffer pool to perform more IOs to get the same amount of data off the disk or out of memory.

The FILLFACTOR should be set dynamically as the insert and update patterns on your tables will most likely vary from table to table - figuring out that algorithm is the tricky part. I guess you could even create a table of all your indexes where you specify your best FILLFACTOR based on your trials and refer to that table during your index rebuilds and only use the default on all new indexes that have not been added to your table yet.

-Eric


-Eric
Post #1214328
Posted Thursday, December 01, 2011 2:22 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Today @ 9:07 AM
Points: 484, Visits: 762
if your machine is case sensitive I would check the code carefully.

We also used some of this code as the basis for our current routine - for a production install you might want to consider your local preferences on minimum pages, fragmentation levels (esp whether you ever want to reorganize) and also the other index options that rebuild should consider.
Post #1214421
Posted Thursday, February 02, 2012 10:52 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, March 14, 2014 2:03 PM
Points: 49, Visits: 309
Very nice and useful script. Thank you.
Had problems with similar table names under different schemes as well.
However, there is no need for joins to sys.tables and sys.schemas.

INSERT INTO #TEMPFRAG
SELECT OBJECT_NAME(F.OBJECT_ID)
, I.NAME IND
, F.AVG_FRAGMENTATION_IN_PERCENT
, F.PAGE_COUNT
, s.name -- NOTE CHANGED FROM TABLE_SCHEMA
--, i.allow_page_locks -- I use this to better handle allow_page_locks true / false
FROM SYS.DM_DB_INDEX_PHYSICAL_STATS ('+cast(@DB_ID as varchar)+',NULL,NULL,NULL,NULL) F
JOIN SYS.INDEXES I ON(F.OBJECT_ID=I.OBJECT_ID) AND I.INDEX_ID=F.INDEX_ID and i.is_disabled = 0 -- Note I'm skipping disabled indexes
join sys.tables t on f.object_id = t.object_id -- NOTE NEW LINE TO REPLACE USE OF INFORMATION_SCHEMA
join sys.schemas s on s.schema_id = t.schema_id -- NOTE NEW LINE TO COMPLETE JOIN
--WHERE INDEX_ID<> 0
AND F.DATABASE_ID=DB_ID()
AND OBJECTPROPERTY(I.OBJECT_ID,''ISSYSTEMTABLE'')=0


You can use OBJECT_SCHEMA_NAME function instead:

INSERT INTO #tempfrag (schema_name,table_name,index_name,frag,pages)
SELECT OBJECT_SCHEMA_NAME(v.object_id) AS SchemaName,
OBJECT_NAME(v.object_id) AS TableName,
i.name AS IndexName,
v.avg_fragmentation_in_percent AS Fragmentation,
v.page_count AS PageCount
FROM SYS.DM_DB_INDEX_PHYSICAL_STATS (11,NULL,NULL,NULL,NULL) v
INNER JOIN
SYS.INDEXES i ON v.object_id = i.object_id
AND
v.index_id = i.index_id
AND
i.is_disabled = 0

Post #1245972
Posted Thursday, February 02, 2012 10:29 PM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Monday, March 24, 2014 3:11 AM
Points: 1,151, Visits: 4,600
Thanks Greg Shinder.I'll look into this.

I'm glad you liked it.


Muthukkumaran Kaliyamoorthy

Helping SQL DBAs and Developers >>>SqlserverBlogForum
Post #1246243
Posted Thursday, February 23, 2012 2:58 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, April 12, 2013 3:02 PM
Points: 2, Visits: 27
I am new to TSQL.
What would be the best way to run this sp for all user databases on a SQL 2008 server?
Post #1256958
Posted Thursday, February 23, 2012 9:36 PM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Monday, March 24, 2014 3:11 AM
Points: 1,151, Visits: 4,600
vanceprice (2/23/2012)
I am new to TSQL.
What would be the best way to run this sp for all user databases on a SQL 2008 server?


Welcome to T-SQL language.
Loop the sys.databases and use this SProc inside the loop.

Take an example of my Index defragmentation script and Try yourself using sys.databases.
If you face any problem open a new thread and ask your quires.We will help you.


Muthukkumaran Kaliyamoorthy

Helping SQL DBAs and Developers >>>SqlserverBlogForum
Post #1257084
Posted Thursday, March 08, 2012 12:02 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, April 09, 2014 11:08 AM
Points: 2, Visits: 49
I've got the following error when used your script in sql 2000. Any idea?Thanks

Server: Msg 1038, Level 15, State 3, Line 1
Cannot use empty object or column names. Use a single space if necessary.
Server: Msg 1038, Level 15, State 1, Line 1
Cannot use empty object or column names. Use a single space if necessary.
Server: Msg 1038, Level 15, State 1, Line 1
Cannot use empty object or column names. Use a single space if necessary.

(1 row(s) affected)
Post #1263870
Posted Friday, March 09, 2012 9:17 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Monday, March 24, 2014 3:11 AM
Points: 1,151, Visits: 4,600
natasha-501269 (3/8/2012)
I've got the following error when used your script in sql 2000. Any idea?Thanks

Server: Msg 1038, Level 15, State 3, Line 1
Cannot use empty object or column names. Use a single space if necessary.
Server: Msg 1038, Level 15, State 1, Line 1
Cannot use empty object or column names. Use a single space if necessary.
Server: Msg 1038, Level 15, State 1, Line 1
Cannot use empty object or column names. Use a single space if necessary.

(1 row(s) affected)


It will not work for SQL 2000.

Use this for SQL 2000

Rebuild Index script for SQL 2000


Muthukkumaran Kaliyamoorthy

Helping SQL DBAs and Developers >>>SqlserverBlogForum
Post #1264470
Posted Friday, March 09, 2012 12:12 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, April 09, 2014 11:08 AM
Points: 2, Visits: 49
Thanks , however this script from your link for sql 2000 is exactly what I used... and it does not run successfully.
Post #1264567
Posted Friday, March 09, 2012 10:18 PM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Monday, March 24, 2014 3:11 AM
Points: 1,151, Visits: 4,600
What error are you getting?

Muthukkumaran Kaliyamoorthy

Helping SQL DBAs and Developers >>>SqlserverBlogForum
Post #1264686
« Prev Topic | Next Topic »

Add to briefcase «««12345»»»

Permissions Expand / Collapse