Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


INDEX DEFRAGMENTATION SCRIPT


INDEX DEFRAGMENTATION SCRIPT

Author
Message
Eric Niemiec
Eric Niemiec
Forum Newbie
Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)

Group: General Forum Members
Points: 8 Visits: 60
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
Balance
Balance
Mr or Mrs. 500
Mr or Mrs. 500 (506 reputation)Mr or Mrs. 500 (506 reputation)Mr or Mrs. 500 (506 reputation)Mr or Mrs. 500 (506 reputation)Mr or Mrs. 500 (506 reputation)Mr or Mrs. 500 (506 reputation)Mr or Mrs. 500 (506 reputation)Mr or Mrs. 500 (506 reputation)

Group: General Forum Members
Points: 506 Visits: 1048
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.
Greg Shinder
Greg Shinder
Valued Member
Valued Member (55 reputation)Valued Member (55 reputation)Valued Member (55 reputation)Valued Member (55 reputation)Valued Member (55 reputation)Valued Member (55 reputation)Valued Member (55 reputation)Valued Member (55 reputation)

Group: General Forum Members
Points: 55 Visits: 443
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


muthukkumaran Kaliyamoorthy
muthukkumaran Kaliyamoorthy
Ten Centuries
Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

Group: General Forum Members
Points: 1225 Visits: 4841
Thanks Greg Shinder.I'll look into this.

I'm glad you liked it.

Muthukkumaran Kaliyamoorthy

Helping SQL DBAs and Developers >>>SqlserverBlogForum

vanceprice
vanceprice
Forum Newbie
Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)

Group: General Forum Members
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?
muthukkumaran Kaliyamoorthy
muthukkumaran Kaliyamoorthy
Ten Centuries
Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

Group: General Forum Members
Points: 1225 Visits: 4841
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

natasha-501269
natasha-501269
Forum Newbie
Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)

Group: General Forum Members
Points: 2 Visits: 110
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)
muthukkumaran Kaliyamoorthy
muthukkumaran Kaliyamoorthy
Ten Centuries
Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

Group: General Forum Members
Points: 1225 Visits: 4841
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

natasha-501269
natasha-501269
Forum Newbie
Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)

Group: General Forum Members
Points: 2 Visits: 110
Thanks , however this script from your link for sql 2000 is exactly what I used... and it does not run successfully.
muthukkumaran Kaliyamoorthy
muthukkumaran Kaliyamoorthy
Ten Centuries
Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

Group: General Forum Members
Points: 1225 Visits: 4841
What error are you getting?

Muthukkumaran Kaliyamoorthy

Helping SQL DBAs and Developers >>>SqlserverBlogForum

Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search