Will Anderson

My feedback

  1. 17 votes
    Vote
    Sign in
    Check!
    (thinking…)
    Reset
    or sign in with
    • facebook
    • google
      Password icon
      I agree to the terms of service
      Signed in as (Sign out)
      You have left! (?) (thinking…)
      1 comment  ·  SQL Database  ·  Flag idea as inappropriate…  ·  Admin →
      Will Anderson commented  · 

      Currently using the script below with elastic jobs to defragment indexes. However would be ace if this was built in and automated.

      DECLARE @TableName varchar(255)
      DECLARE @IndexName varchar(255)
      DECLARE @Fragmentation int

      DECLARE TableCursor CURSOR FOR
      (
      SELECT
      OBJECT_NAME(ps.object_id) AS TableName
      ,i.name AS IndexName
      ,round(ips.avg_fragmentation_in_percent,0) as Fragmentation
      FROM sys.dm_db_partition_stats ps
      INNER JOIN sys.indexes i
      ON ps.object_id = i.object_id
      AND ps.index_id = i.index_id
      CROSS APPLY sys.dm_db_index_physical_stats(DB_ID(), ps.object_id, ps.index_id, null, 'LIMITED') ips
      where ips.page_count > 200 and ips.avg_fragmentation_in_percent > 10 AND i.name <> ''
      --ORDER BY ps.object_id, ps.index_id
      )

      OPEN TableCursor
      FETCH NEXT FROM TableCursor INTO @TableName, @IndexName, @Fragmentation
      WHILE @@FETCH_STATUS = 0

      BEGIN

      --print(@Fragmentation)

      IF @Fragmentation <= 30
      BEGIN
      PRINT('ALTER INDEX ['+@IndexName+'] ON ['+ @TableName +'] REORGANIZE')
      EXEC('ALTER INDEX ['+@IndexName+'] ON ['+ @TableName +'] REORGANIZE')
      END
      ELSE IF @Fragmentation >= 30
      BEGIN
      PRINT('ALTER INDEX ['+@IndexName+'] ON ['+@TableName+'] REBUILD with (ONLINE=ON)')
      EXEC('ALTER INDEX ['+@IndexName+'] ON ['+@TableName+'] REBUILD with (ONLINE=ON)')
      END

      FETCH NEXT FROM TableCursor INTO @TableName, @IndexName, @Fragmentation
      END

      CLOSE TableCursor
      DEALLOCATE TableCursor

    • 7 votes
      Vote
      Sign in
      Check!
      (thinking…)
      Reset
      or sign in with
      • facebook
      • google
        Password icon
        I agree to the terms of service
        Signed in as (Sign out)
        You have left! (?) (thinking…)
        1 comment  ·  SQL Database  ·  Flag idea as inappropriate…  ·  Admin →
        Will Anderson shared this idea  · 
      • 11 votes
        Vote
        Sign in
        Check!
        (thinking…)
        Reset
        or sign in with
        • facebook
        • google
          Password icon
          I agree to the terms of service
          Signed in as (Sign out)
          You have left! (?) (thinking…)
          1 comment  ·  (General Feedback) » azure.microsoft.com  ·  Flag idea as inappropriate…  ·  Admin →
          Will Anderson supported this idea  · 
        • 9 votes
          Vote
          Sign in
          Check!
          (thinking…)
          Reset
          or sign in with
          • facebook
          • google
            Password icon
            I agree to the terms of service
            Signed in as (Sign out)
            You have left! (?) (thinking…)
            0 comments  ·  (General Feedback) » azure.microsoft.com  ·  Flag idea as inappropriate…  ·  Admin →
            Will Anderson supported this idea  · 
          • 600 votes
            Vote
            Sign in
            Check!
            (thinking…)
            Reset
            or sign in with
            • facebook
            • google
              Password icon
              I agree to the terms of service
              Signed in as (Sign out)
              You have left! (?) (thinking…)
              22 comments  ·  Cloud Services (Web and Worker Role)  ·  Flag idea as inappropriate…  ·  Admin →
              Will Anderson supported this idea  · 

            Feedback and Knowledge Base