June 23, 2009 at 12:11 pm
Hello,
I'm embarking on an adventure to try to find a way to automatically rebuild the indexs of Tables and Views with a fragmentation greater than 30%. I've been able make it to the last step of my process but I dont know what to do at that point.
Any comments, advice or assistance is greatly appreciated.
Here is what I've done so far:
(1) Create a table called 'FRAGMENTATION_LIST_dba' that will be used to contain a list of all the table names that have indexes that have an "avg_fragmentation_in_percent" value grater then 30%.
(2) Run a query that:
- Clears the table FRAGMENTATION_LIST_dba
- Populates FRAGMENTATION_LIST_dba table
Here's my code for step 2:
---------------------------------------------------
delete from dbo.X_FRAGMENTATION_LIST_dba
insert into dbo.X_FRAGMENTATION_LIST_dba
SELECT
OBJECT_NAME(object_id) AS 'Table Name'
,dbo.index_name(object_id, index_id) AS 'Index Name'
,avg_fragmentation_in_percent , fragment_count, alloc_unit_type_desc
FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, NULL)
WHERE avg_fragmentation_in_percent > 30
AND index_type_desc IN('CLUSTERED INDEX', 'NONCLUSTERED INDEX')
order by avg_fragmentation_in_percent desc
---------------------------------------------------
(3)
Rebuild the indexes of the Tables and Views listed in the FRAGMENTATION_LIST_dba table.
In this step lies my dilema:
How do I code SQL to rebuild all the indexes in my list????
What would the code look like?
Can anyone lend a hand?
Thanks!!!
Chris
June 23, 2009 at 12:20 pm
There are some threads about the same situation on thsi site...
also check sp_msforeachtable and sp_msforeachdb to check for all tables in each database at one go.
Maninder
www.dbanation.com
June 23, 2009 at 12:43 pm
Mani Singh (6/23/2009)
There are some threads about the same situation on thsi site...also check sp_msforeachtable and sp_msforeachdb to check for all tables in each database at one go.
Thanks Mani Singh!
Would you happen to have a link to one of these threads?
June 23, 2009 at 12:47 pm
select db_name(sind.database_id) as dbname,object_name(sind.object_id,sind.database_id) as objname,sind.index_id,sind.index_type_desc,sind.avg_fragmentation_in_percent,'ALTER INDEX ON '+db_name(sind.database_id)+'.dbo.'+object_name(sind.object_id,sind.database_id)+'.'+si.name+' REBUILD WITH (FILLFACTOR = 85, SORT_IN_TEMPDB = ON STATISTICS_NORECOMPUTE = ON) ' as executethsestatemetns
from sys.dm_db_index_physical_stats(NULL,NULL,NULL,NULL,DEFAULT) sind join sys.indexes si
on si.index_id = sind.index_id and si.object_id = sind.object_id
where sind.database_id>4 and sind.avg_fragmentation_in_percent >=30
and sind.index_id>=1
copy the statements in the last column and paste and execute in another query window
Maninder
www.dbanation.com
June 24, 2009 at 7:16 am
Mani Singh (6/23/2009)
select db_name(sind.database_id) as dbname,object_name(sind.object_id,sind.database_id) as objname,sind.index_id,sind.index_type_desc,sind.avg_fragmentation_in_percent,'ALTER INDEX ON '+db_name(sind.database_id)+'.dbo.'+object_name(sind.object_id,sind.database_id)+'.'+si.name+' REBUILD WITH (FILLFACTOR = 85, SORT_IN_TEMPDB = ON STATISTICS_NORECOMPUTE = ON) ' as executethsestatemetnsfrom sys.dm_db_index_physical_stats(NULL,NULL,NULL,NULL,DEFAULT) sind join sys.indexes si
on si.index_id = sind.index_id and si.object_id = sind.object_id
where sind.database_id>4 and sind.avg_fragmentation_in_percent >=30
and sind.index_id>=1
copy the statements in the last column and paste and execute in another query window
Thanks!!
I'm trying out in my sandbox right now.
June 24, 2009 at 11:49 am
Chris (6/24/2009)
Mani Singh (6/23/2009)
select db_name(sind.database_id) as dbname,object_name(sind.object_id,sind.database_id) as objname,sind.index_id,sind.index_type_desc,sind.avg_fragmentation_in_percent,'ALTER INDEX ON '+db_name(sind.database_id)+'.dbo.'+object_name(sind.object_id,sind.database_id)+'.'+si.name+' REBUILD WITH (FILLFACTOR = 85, SORT_IN_TEMPDB = ON STATISTICS_NORECOMPUTE = ON) ' as executethsestatemetnsfrom sys.dm_db_index_physical_stats(NULL,NULL,NULL,NULL,DEFAULT) sind join sys.indexes si
on si.index_id = sind.index_id and si.object_id = sind.object_id
where sind.database_id>4 and sind.avg_fragmentation_in_percent >=30
and sind.index_id>=1
copy the statements in the last column and paste and execute in another query window
Thanks!!
I'm trying out in my sandbox right now.
June 24, 2009 at 12:00 pm
Mani Singh (6/23/2009)
select db_name(sind.database_id) as dbname,object_name(sind.object_id,sind.database_id) as objname,sind.index_id,sind.index_type_desc,sind.avg_fragmentation_in_percent,'ALTER INDEX ON '+db_name(sind.database_id)+'.dbo.'+object_name(sind.object_id,sind.database_id)+'.'+si.name+' REBUILD WITH (FILLFACTOR = 85, SORT_IN_TEMPDB = ON STATISTICS_NORECOMPUTE = ON) ' as executethsestatemetnsfrom sys.dm_db_index_physical_stats(NULL,NULL,NULL,NULL,DEFAULT) sind join sys.indexes si
on si.index_id = sind.index_id and si.object_id = sind.object_id
where sind.database_id>4 and sind.avg_fragmentation_in_percent >=30
and sind.index_id>=1
copy the statements in the last column and paste and execute in another query window
Well,
I ran the query against just one database and the query did finish after about 30 minuets. But the same tables have the same number of fragmented indexes as if nothing had been rebuilt. I even re-ran again and specified ONLINE = OFF.
So then I just picked a table that was listed in the query and tried to rebuild the index manually. (ie. opened the table in management studio/ went to 'indexes'/ right clicked on the index/ chose 'Rebuild') and the rebuild worked. However, after refreshing the instance and drilling back down into the table to check the index 'fragmentation' property. It still showed the same 50% fragmented as it has always been. Nothing has changed.
Any Ideas?
June 24, 2009 at 12:15 pm
By the way this is the DBCC for the Index I tried to manually rebuild:
DBCC SHOWCONTIG scanning 'APPROVALS' table...
Table: 'APPROVALS' (202639965); index ID: 1, database ID: 5
TABLE level scan performed.
- Pages Scanned................................: 2
- Extents Scanned..............................: 2
- Extent Switches..............................: 1
- Avg. Pages per Extent........................: 1.0
- Scan Density [Best Count:Actual Count].......: 50.00% [1:2]
- Logical Scan Fragmentation ..................: 50.00%
- Extent Scan Fragmentation ...................: 50.00%
- Avg. Bytes Free per Page.....................: 1282.5
- Avg. Page Density (full).....................: 84.15%
DBCC execution completed. If DBCC printed error messages, contact your s
June 24, 2009 at 2:58 pm
Chris (6/24/2009)
DBCC SHOWCONTIG scanning 'APPROVALS' table...Table: 'APPROVALS' (202639965); index ID: 1, database ID: 5
TABLE level scan performed.
- Pages Scanned................................: 2
- Extents Scanned..............................: 2
- Extent Switches..............................: 1
- Avg. Pages per Extent........................: 1.0
- Scan Density [Best Count:Actual Count].......: 50.00% [1:2]
- Logical Scan Fragmentation ..................: 50.00%
- Extent Scan Fragmentation ...................: 50.00%
- Avg. Bytes Free per Page.....................: 1282.5
- Avg. Page Density (full).....................: 84.15%
DBCC execution completed. If DBCC printed error messages, contact your s
Way, way, way too small to even bother with.
Because of the way SQL assigns the first few pages (either 8 or 24), small tables will always appear to be fragmented. It's not a concern. Small indexes will very likely be in memory anyway, and index fragmentation is only an issue when doing large range scans from disk.
The general rule of thumb is that you start worrying about fragmentation when the index reaches around 1000 pages.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
June 24, 2009 at 5:22 pm
Here is one of the better scripts available for this:
http://sqlfool.com/2009/03/automated-index-defrag-script/
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
June 26, 2009 at 6:39 am
GilaMonster (6/24/2009)
Chris (6/24/2009)
DBCC SHOWCONTIG scanning 'APPROVALS' table...Table: 'APPROVALS' (202639965); index ID: 1, database ID: 5
TABLE level scan performed.
- Pages Scanned................................: 2
- Extents Scanned..............................: 2
- Extent Switches..............................: 1
- Avg. Pages per Extent........................: 1.0
- Scan Density [Best Count:Actual Count].......: 50.00% [1:2]
- Logical Scan Fragmentation ..................: 50.00%
- Extent Scan Fragmentation ...................: 50.00%
- Avg. Bytes Free per Page.....................: 1282.5
- Avg. Page Density (full).....................: 84.15%
DBCC execution completed. If DBCC printed error messages, contact your s
Way, way, way too small to even bother with.
Because of the way SQL assigns the first few pages (either 8 or 24), small tables will always appear to be fragmented. It's not a concern. Small indexes will very likely be in memory anyway, and index fragmentation is only an issue when doing large range scans from disk.
The general rule of thumb is that you start worrying about fragmentation when the index reaches around 1000 pages.
Thanks for the rule of thumb Gila. That really helps!!!!!
June 26, 2009 at 7:21 am
Jeffrey Williams (6/24/2009)
Here is one of the better scripts available for this:
Wow, thats an elaborate script!!
Ever get the following error when running it?
"
Procedure dba_indexDefrag_sp dropped
Cannot add rows to sys.sql_dependencies for the stored procedure because it depends on the missing table 'SP_EXECUTESQL'. The stored procedure will still be created; however, it cannot be successfully executed until the table exists.
"
Chris
June 26, 2009 at 9:35 am
Chris (6/26/2009)
Wow, thats an elaborate script!!Ever get the following error when running it?
"
Procedure dba_indexDefrag_sp dropped
Cannot add rows to sys.sql_dependencies for the stored procedure because it depends on the missing table 'SP_EXECUTESQL'. The stored procedure will still be created; however, it cannot be successfully executed until the table exists.
"
Chris
No, actually I have not gotten that error before because I don't use that script. I have my own that I developed before I found hers.
The problem is my fault - I missed the fact that you are on SQL Server 2000 and that script (I believe) was written for 2005 and greater.
Or, are you really running 2005 and just posted in the wrong forum 🙂
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
June 27, 2009 at 8:28 am
I've built one of these for my company and it's very complex so I'm not going to post it here but here are some considerations to look for when gathering the list of indexes to re-index:
- Make sure the table has data (actually, I've read that it should really have more than 8 pages to be effective)
- Make sure if you find that the clustered index on the table needs to be re-indexed you remove the non-clustered indexes on that table from the list otherwise you'll be doing double work because the non-clustered indexes automatically get re-indexed when the clustered does
- if you want the procedure to work with 2000, use DBCC SHOWCONTIG with TABLERESULTS
- Look at Scan Density as well as Logical Frag.
Hope that helps
John
June 27, 2009 at 9:16 am
John (6/27/2009)
- Make sure the table has data (actually, I've read that it should really have more than 8 pages to be effective)
As I mentioned earlier in this thread, the general rule of thumb is that you start worrying about fragmentation when the index reaches around 1000 pages.
- Make sure if you find that the clustered index on the table needs to be re-indexed you remove the non-clustered indexes on that table from the list otherwise you'll be doing double work because the non-clustered indexes automatically get re-indexed when the clustered does
Not in all cases, even on SQL 2000. In even fewer cases on 2005+
I don't have 2000's BoL in front of me, but iirc the NC indexes are rebuilt when the cluster is rebuilt if the cluster is not unique.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 15 posts - 1 through 15 (of 19 total)
You must be logged in to reply to this topic. Login to reply