Write script to create Optimization Plan

I have a SQL Server 2000 database that is approximately 40 GB in size.  It's been a while we run optimization plan.  Now we want to optimize the database but only on the tables that are badly fragmented.  We run Fragmented analysis to find out wich tables are index fragmented.  I executed a script to find out all the tables that are fragmented.  

I want to rebuild only those indexes that have become badly
fragmented.  This will reduce the amount of work to be done, and in turn
reduce the need to grow the database.  

How can i do this or how can i write a script that will only rebuild the indexes that have become badly fragmented?

Thank You.
LVL 1
Anandhi1Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

chapmandewCommented:
1.  Identify the tables that are badly fragmented using DBCC SHOWCONTIG WITH TABLE_RESULTS.  Store this in a table.

2.  For each table where the logical fragmentation is > 30, you'll want to consider rebuilding it.  Query your table to find these tables.

3.  Loop through these records (a cursor should work) and reindex the tables one at a time.
0
chapmandewCommented:
it would look something like this:

INSERT INTO ShowcontigResults
(
      ObjectName , ObjectID , IndexName , IndexID , Level , PagesScanned , TableRowCount ,
      MinimumRecordSize , MaximumRecordSize, AverageRecordSize , ForwardRecords ,
      ExtentsScanned  , ExtentSwitches , AverageBytesFreePerPage , AveragePageDensity ,
      ScanDensity , ScanDensity_BestCount , ScanDensity_ActualCount , LogicalScanFragmentation,
      ExtentScanFragmentation
)
EXEC('DBCC SHOWCONTIG WITH TABLERESULTS')

DECLARE  tempcursor
CURSOR
READ_ONLY
FOR

      SELECT
            DISTINCT
            ObjectName
      FROM
            ShowcontigResults
      WHERE
            ObjectName NOT LIKE 'sys%' AND
            ObjectName NOT LIKE 'qs%' AND
            ObjectName NOT LIKE 'dt%' AND
            ExtentSwitches > 0 AND
            (
                  ScanDensity< 80 OR
                  (LogicalScanFragmentation+ExtentScanFragmentation)>100
            )

DECLARE @name sysname
OPEN tempcursor

FETCH NEXT FROM tempcursor INTO @name
WHILE (@@fetch_status <> -1)
BEGIN

      DBCC DBREINDEX(@name,'',80) WITH NO_INFOMSGS
      FETCH NEXT FROM tempcursor INTO @name
END

CLOSE tempcursor
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Anandhi1Author Commented:
Thank you for the script.  I applied this to my test database.  I ran this once and then i run fragmented analysis to see if there are tables that are still fragmented.  I keep seeig fragmented tables.  

Do i have to re run this script until all the fragmented tables are no loger fragmented.  I run few times and i see not much difference in table scan density or the Logical+Extent scan fragmentation.

Please explain.

Thank you so much for your help.
0
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

chapmandewCommented:
did the fragmentation change?
0
Anandhi1Author Commented:
on some of the table it changed.  i must have run at lease 5 to 7 times.  Please see attached file for the fragmetation infomation.  Is DBCC DBREINDEX suppose to drop and re-create the index and it default back to the scan density to fill factor.  On all our table fill factor is set to 80%.

Please see the script i'm running.
CREATE PROCEDURE [dbo].[sp_optimization] AS

begin
delete from ShowcontigResults
end

begin
INSERT INTO ShowcontigResults
(
      ObjectName , ObjectID , IndexName , IndexID , Level , PagesScanned , TableRowCount ,
      MinimumRecordSize , MaximumRecordSize, AverageRecordSize , ForwardRecords ,
      ExtentsScanned  , ExtentSwitches , AverageBytesFreePerPage , AveragePageDensity ,
      ScanDensity , ScanDensity_BestCount , ScanDensity_ActualCount , LogicalScanFragmentation,
      ExtentScanFragmentation
)
EXEC('DBCC SHOWCONTIG WITH TABLERESULTS')
end

begin
update ShowcontigResults set
objectname = (select (t.table_schema + '.' + objectname) FROM information_schema.tables as t
where objectname =  t.table_name)
end

begin
DECLARE  tempcursor CURSOR READ_ONLY FOR

      SELECT  DISTINCT ObjectName FROM ShowcontigResults  WHERE
            ObjectName NOT LIKE 'sys%' AND
            ObjectName NOT LIKE 'qs%' AND
            ObjectName NOT LIKE 'dt%' AND
            ExtentSwitches > 0 AND
            (
                  ScanDensity< 80 OR
                  (LogicalScanFragmentation+ExtentScanFragmentation)>100
            ) AND
      OBJECTNAME NOT IN ('ALCSUM_TEMP', 'CONV_CACPER', 'CONV_CACVEH', 'CONV_CAMAST','CONV_CAMAST_MO',                                                                                                                                                                                                                                                
      'CONV_CANARR_OFF','CONV_FI_ASSOCIATE','CONV_INPER6','CONV_INVEH7','CONV_INVELK',                                                                                                                                                                                                                                                    
      'CONV_VKVELK','DV','IIIARS_20070926', 'inqueue', 'pbtbmaster_varchar', 'PNITEM_CORY',
      'PNMAST_CORY', 'SSCTABx', 'SSCTBSx', 'SSDEDXx', 'Visiphor_History')

OPEN tempcursor
DECLARE @name sysname


FETCH NEXT FROM tempcursor INTO @name
WHILE (@@fetch_status <> -1)
BEGIN
begin
      DBCC DBREINDEX(@name,'',80) WITH NO_INFOMSGS
end
      FETCH NEXT FROM tempcursor INTO @name
END

DEALLOCATE tempcursor
End
GO

fragment-data.xls
0
Anandhi1Author Commented:
Can index be rebuild if table have no clustered index?  I'm just noticing that lot of the tables have no clusterd index.

Please help!
0
chapmandewCommented:
I am pretty sure that a lot of the info provided by dbcc showcontig is only valid if a clustered index is present.
0
Anandhi1Author Commented:
I run individual table to see if i get any improvment on the table.

dbcc showcontig ("tablename")
BCC SHOWCONTIG scanning 'ALMAST' table...
Table: 'ALMAST' (1516584491); index ID: 1, database ID: 7
TABLE level scan performed.
- Pages Scanned................................: 8
- Extents Scanned..............................: 7
- Extent Switches..............................: 6
- Avg. Pages per Extent........................: 1.1
- Scan Density [Best Count:Actual Count].......: 14.29% [1:7]
- Logical Scan Fragmentation ..................: 0.00%
- Extent Scan Fragmentation ...................: 71.43%
- Avg. Bytes Free per Page.....................: 1556.0
- Avg. Page Density (full).....................: 80.78%
DBCC execution completed. If DBCC printed error messages, contact your system administrator.

DBCC DBREINDEX("tablename","PK_ALMAST",80) WITH NO_INFOMSGS
after running dbreindex.
DBCC SHOWCONTIG scanning 'ALMAST' table...
Table: 'ALMAST' (1516584491); index ID: 1, database ID: 7
TABLE level scan performed.
- Pages Scanned................................: 8
- Extents Scanned..............................: 7
- Extent Switches..............................: 6
- Avg. Pages per Extent........................: 1.1
- Scan Density [Best Count:Actual Count].......: 14.29% [1:7]
- Logical Scan Fragmentation ..................: 0.00%
- Extent Scan Fragmentation ...................: 71.43%
- Avg. Bytes Free per Page.....................: 1556.0
- Avg. Page Density (full).....................: 80.78%
DBCC execution completed. If DBCC printed error messages, contact your system administrator.

In this table on index is clustered and all others are non clustered.  is that why i'm not seing any improvement on the scan density?

Thank You.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.