We help IT Professionals succeed at work.

Lost with Defrag sproc

497 Views
Last Modified: 2012-02-16
I am very new to SQL and inherited a sproc I am not sure is working. I picked a DB out at random to see if the sproc is doing its job and there is object ID’s that have avg_fragmentation of 99%.

 Any help getting this to work correctly would be greatly appreciated.

The Defrag sproc seems to do nothing at all, and I fear that has been the case for a long time.

There is a SQL agent job that is running that has one step that is running EXEC DEFRAG.


So when EXEC Defrag is run it does the following.


SET NOCOUNT ON

      CREATE TABLE #Clients (
            DbName varchar(100) NOT NULL
      )

      INSERT #Clients
      SELECT  [NAme] FROM sys.databases WHERE (database_id % 30) = DAY(GETDATE()) AND [NAME] LIKE 'xs%'


      DECLARE @tablename VARCHAR (128)
      DECLARE @indexName VARCHAR (256)
      DECLARE @execstr   VARCHAR (255)
      DECLARE @objectid  INT
      DECLARE @indexid   INT
      DECLARE @frag      DECIMAL
      DECLARE @maxfrag   DECIMAL
      DECLARE @starttime datetime;
      DECLARE @DbName varchar(100);
      SELECT @maxfrag = 30.0

      WHILE EXISTS (SELECT 1 FROM #Clients)
      BEGIN
            SELECT TOP 1 @DbName = DbName FROM #Clients

            EXEC('use ' + @DbName + '
                        IF EXISTS ( SELECT * FROM sys.procedures WHERE [NAME] = ''_DEFRAG'' )
                              EXEC _DEFRAG
                        ELSE
                              INSERT INTO PRIMARYLMDBSERVER.LDBLog.dbo._fragLog (DBID, DB, table_name, index_name, sdate, edate, process_time, fragmentation)
                              SELECT DB_ID(), DB_NAME(), '''', '''', '''', '''', -1, 0');

            DELETE #Clients WHERE DbName = @DbName
      END

      DROP TABLE #Clients



So I see that during the sproc DEFRAG it also runs another sproc called _Defrag which does the following.




SET NOCOUNT ON

IF ((DB_ID() % 30) = DAY(GETDATE()) )
BEGIN
      DECLARE @tablename VARCHAR (128)
      DECLARE @indexName VARCHAR (256)
      DECLARE @execstr   VARCHAR (255)
      DECLARE @objectid  INT
      DECLARE @indexid   INT
      DECLARE @frag      FLOAT
      DECLARE @maxfrag   DECIMAL
      DECLARE @starttime datetime;
      DECLARE @endtime datetime;
      DECLARE @Max int
            
      SET @Indexid = 0
      SET @Max = 999999

      -- Decide on the maximum fragmentation to allow
      SELECT @maxfrag = 30.0

      -- Create the table
      DECLARE @fraglist TABLE (
            IndexId int,
            TableName varchar (255),
            IndexName varchar (255),
            ObjectId int,
            Fragmentation float )


      INSERT INTO @fraglist  (IndexId, TableName, IndexName, ObjectId, Fragmentation)
      SELECT a.index_id, t.name [TableName], b.name [IndexName], t.object_id, avg_fragmentation_in_percent --, *
      FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, NULL) AS a
      INNER JOIN sys.sysindexes AS b ON a.object_id = b.id AND a.index_id = b.indid
      INNER JOIN sys.tables t ON t.object_id = b.id
      WHERE avg_fragmentation_in_percent > @maxfrag
      AND b.indid > 0 -- eliminate the heaps
      order by b.indid asc -- clustered indexes first

      WHILE @IndexID < @Max
      BEGIN

            SELECT TOP 1 @tablename = TableName, @indexid = IndexId, @indexName = IndexName, @objectId = ObjectId, @frag = fragmentation
            FROM @fraglist
            WHERE IndexID < @Indexid
            ORDER BY IndexID ASC
            
            IF @@Rowcount = 0
                  SET @Max = 0

            SET @starttime = GETDATE();

            SET @execstr = 'ALTER INDEX '+@indexName+' ON '+@tablename+' REORGANIZE;';
            
            EXEC (@execstr)

            SET @endtime = GETDATE();

            INSERT INTO PRIMARYLMDBSERVER.LDBLog.dbo._fragLog (DBID, DB, table_name, index_name, sdate, edate, process_time, fragmentation)
            SELECT DB_ID(), DB_NAME(), RTRIM(@tablename), @indexName, @starttime, @endtime, DATEPART(ms, @endtime) - DATEPART(ms, @starttime), @frag

      END

END
Comment
Watch Question

Database Analyst
CERTIFIED EXPERT
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION
CERTIFIED EXPERT
Top Expert 2012
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION

Author

Commented:
Thank you both this got me in the right direction.  I have a new question but I will leave it to a new post.

Again thank you both very much for the hand.
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.