Will Anderson

My feedback

  1. 84 votes
    Vote
    Sign in
    Check!
    (thinking…)
    Reset
    or sign in with
    • facebook
    • google
      Password icon
      Signed in as (Sign out)
      You have left! (?) (thinking…)
      3 comments  ·  Web Apps  ·  Flag idea as inappropriate…  ·  Admin →
      Will Anderson supported this idea  · 
    • 91 votes
      Vote
      Sign in
      Check!
      (thinking…)
      Reset
      or sign in with
      • facebook
      • google
        Password icon
        Signed in as (Sign out)
        You have left! (?) (thinking…)
        6 comments  ·  Web Apps  ·  Flag idea as inappropriate…  ·  Admin →
        Will Anderson supported this idea  · 
      • 43 votes
        Vote
        Sign in
        Check!
        (thinking…)
        Reset
        or sign in with
        • facebook
        • google
          Password icon
          Signed in as (Sign out)
          You have left! (?) (thinking…)
          8 comments  ·  Web Apps  ·  Flag idea as inappropriate…  ·  Admin →
          Will Anderson commented  · 

          Coming from on prem, this is one of the biggest pain points for me. Any updates?

        • 17 votes
          Vote
          Sign in
          Check!
          (thinking…)
          Reset
          or sign in with
          • facebook
          • google
            Password icon
            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
              Signed in as (Sign out)
              You have left! (?) (thinking…)
              1 comment  ·  SQL Database  ·  Flag idea as inappropriate…  ·  Admin →
              Will Anderson shared this idea  · 
            • 13 votes
              Vote
              Sign in
              Check!
              (thinking…)
              Reset
              or sign in with
              • facebook
              • google
                Password icon
                Signed in as (Sign out)
                You have left! (?) (thinking…)
                1 comment  ·  SQL Database  ·  Flag idea as inappropriate…  ·  Admin →
                Will Anderson supported this idea  · 
              • 609 votes
                Vote
                Sign in
                Check!
                (thinking…)
                Reset
                or sign in with
                • facebook
                • google
                  Password icon
                  Signed in as (Sign out)
                  You have left! (?) (thinking…)
                  23 comments  ·  Cloud Services (Web and Worker Role)  ·  Flag idea as inappropriate…  ·  Admin →
                  Will Anderson supported this idea  · 

                Feedback and Knowledge Base