[Webinar] Streamline your web hosting managementRegister Today

x
?
Solved

Write script to create Optimization Plan

Posted on 2008-11-11
8
Medium Priority
?
356 Views
Last Modified: 2012-05-05
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.
0
Comment
Question by:Anandhi1
  • 4
  • 4
8 Comments
 
LVL 60

Expert Comment

by:chapmandew
ID: 22934608
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
 
LVL 60

Accepted Solution

by:
chapmandew earned 2000 total points
ID: 22934639
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
 
LVL 1

Author Comment

by:Anandhi1
ID: 22961392
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
Take Control of Web Hosting For Your Clients

As a web developer or IT admin, successfully managing multiple client accounts can be challenging. In this webinar we will look at the tools provided by Media Temple and Plesk to make managing your clients’ hosting easier.

 
LVL 60

Expert Comment

by:chapmandew
ID: 22961416
did the fragmentation change?
0
 
LVL 1

Author Comment

by:Anandhi1
ID: 22961589
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
 
LVL 1

Author Comment

by:Anandhi1
ID: 22962505
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
 
LVL 60

Expert Comment

by:chapmandew
ID: 22962554
I am pretty sure that a lot of the info provided by dbcc showcontig is only valid if a clustered index is present.
0
 
LVL 1

Author Comment

by:Anandhi1
ID: 22964102
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

Featured Post

Receive 1:1 tech help

Solve your biggest tech problems alongside global tech experts with 1:1 help.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
MSSQL DB-maintenance also needs implementation of multiple activities. However, unprecedented errors can hamper the database management. In that case, deploying Stellar SQL Database Toolkit ensures fast and accurate database and backup repair as wel…
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Suggested Courses

591 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question