Solved

Rebulit index for all table in all SQL server 2005 database

Posted on 2013-01-21
7
335 Views
Last Modified: 2013-03-06
Dear all,

Any script for me to rebuilt index for all index of all table in all database?

I want to do it all in one file.

I am using SQL server 2005.

DBA100.
0
Comment
Question by:marrowyung
  • 3
  • 2
  • 2
7 Comments
 
LVL 31

Assisted Solution

by:James Murrell
James Murrell earned 100 total points
ID: 38800442
EXEC sp_MSforeachtable @command1="print '?' DBCC DBREINDEX ('?', ' ', 80)"
 GO


should help its what i use
0
 
LVL 1

Author Comment

by:marrowyung
ID: 38800452
The following script should be run without any problem (e.g. : accidently delete/modify any data)

Any one script can do the job for all user database ?



SET NOCOUNT ON;
DECLARE @objectid int;
DECLARE @indexid int;
DECLARE @partitioncount bigint;
DECLARE @schemaname nvarchar(130);
DECLARE @objectname nvarchar(130);
DECLARE @indexname nvarchar(130);
DECLARE @partitionnum bigint;
DECLARE @partitions bigint;
DECLARE @frag float;
DECLARE @command nvarchar(4000);
-- Conditionally select tables and indexes from the sys.dm_db_index_physical_stats function
-- and convert object and index IDs to names.
SELECT
    object_id AS objectid,
    index_id AS indexid,
    partition_number AS partitionnum,
    avg_fragmentation_in_percent AS frag
INTO #work_to_do
FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL , NULL, 'LIMITED')
WHERE avg_fragmentation_in_percent > 10.0 AND index_id > 0;
-- Declare the cursor for the list of partitions to be processed.
DECLARE partitions CURSOR FOR SELECT * FROM #work_to_do;
-- Open the cursor.
OPEN partitions;
-- Loop through the partitions.
WHILE (1=1)
    BEGIN;
        FETCH NEXT
           FROM partitions
           INTO @objectid, @indexid, @partitionnum, @frag;
        IF @@FETCH_STATUS < 0 BREAK;
        SELECT @objectname = QUOTENAME(o.name), @schemaname = QUOTENAME(s.name)
        FROM sys.objects AS o
        JOIN sys.schemas as s ON s.schema_id = o.schema_id
        WHERE o.object_id = @objectid;
        SELECT @indexname = QUOTENAME(name)
        FROM sys.indexes
        WHERE  object_id = @objectid AND index_id = @indexid;
        SELECT @partitioncount = count (*)
        FROM sys.partitions
        WHERE object_id = @objectid AND index_id = @indexid;
-- 30 is an arbitrary decision point at which to switch between reorganizing and rebuilding.
        IF @frag < 30.0
              SET @command = N'ALTER INDEX ' + @indexname + N' ON ' + @schemaname + N'.' + @objectname + N' REORGANIZE';
        IF @frag >= 30.0
            SET @command = N'ALTER INDEX ' + @indexname + N' ON ' + @schemaname + N'.' + @objectname + N' REBUILD';
        IF @partitioncount > 1
                    SET @command = @command + N' PARTITION=' + CAST(@partitionnum AS nvarchar(10));
                            EXEC (@command);
        --PRINT N'Executed: ' + @command;
    END;
-- Close and deallocate the cursor.
CLOSE partitions;
DEALLOCATE partitions;
-- Drop the temporary table.
DROP TABLE #work_to_do;
GO
0
 
LVL 31

Expert Comment

by:James Murrell
ID: 38800455
The one i posted
0
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 
LVL 75

Accepted Solution

by:
Anthony Perkins earned 400 total points
ID: 38801890
Any script for me to rebuilt index for all index of all table in all database?
...
WHERE avg_fragmentation_in_percent > 10.0 AND index_id > 0;

I am confused you ask for code to rebuild all tables in all databases and then post a script that only does it for tables that are fragmented by a certain amount.

So if you want the first go with the first solution posted by cs97jjm3, except that it needs to be included in another undocumented function sp_msforeachdb to do all databases.

If on the other hand you want to only do those tables that have fragmented indexes then download and install the scripts from here:
http://ola.hallengren.com/sql-server-index-and-statistics-maintenance.html

And then your script becomes as simple as the first example:
EXECUTE dbo.IndexOptimize
@Databases = 'ALL_DATABASES',
@FragmentationLow = NULL,
@FragmentationMedium = 'INDEX_REORGANIZE,INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE',
@FragmentationHigh = 'INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE',
@FragmentationLevel1 = 5,
@FragmentationLevel2 = 30

Open in new window

0
 
LVL 1

Author Comment

by:marrowyung
ID: 38813235
acperkins,

"EXECUTE dbo.IndexOptimize
@Databases = 'ALL_DATABASES',
@FragmentationLow = NULL,
@FragmentationMedium = 'INDEX_REORGANIZE,INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE',
@FragmentationHigh = 'INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE',
@FragmentationLevel1 = 5,
@FragmentationLevel2 = 30 "

Yeah, that one is listed as the example. It works for you, right?
0
 
LVL 1

Author Comment

by:marrowyung
ID: 38956917
it seems that running this:

use <userdatabase>

EXECUTE dbo.IndexOptimize
@Databases = 'ALL_DATABASES',
@FragmentationLow = NULL,
@FragmentationMedium = 'INDEX_REORGANIZE,INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE',
@FragmentationHigh = 'INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE',
@FragmentationLevel1 = 5,
@FragmentationLevel2 = 30

the avg fragmentation_in_precent still the same, checking by this:
SELECT DB_NAME(a.database_id) as [Database Name]
            ,OBJECT_NAME(SO.id) as [Table Name]
            ,a.object_id as [Table Object ID]
            ,b.name as [Index Name]
            ,a.index_id
            ,a.avg_fragmentation_in_percent
FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, NULL) AS a
    JOIN sys.indexes AS b
      ON a.object_id = b.object_id
      JOIN sys.sysobjects AS SO
      on b.object_id = SO.id
      AND a.index_id = b.index_id
where a.database_id = db_id() and SO.xtype = 'U'
order by a.avg_fragmentation_in_percent DESC
GO

any reason ?
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 38960265
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
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.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

803 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