Defrag All Indexes On a Database

  • hakim.ali

    SSCarpal Tunnel

    Points: 4237

    Comments posted to this topic are about the item Defrag All Indexes On a Database

    Hakim Ali
    www.sqlzen.com

  • dbajunior

    SSCrazy

    Points: 2307

    When I try to run this script, I get the errors below. Note! These same 'Incorrect syntax near '' ' happen at times on other scripts I get from SSC and try to run.

    It may be an issue with my SSMS setup. Any one have some ideas on this? Thanks in advance!

    Msg 102, Level 15, State 1, Line 10

    Incorrect syntax near '?'.

    Msg 102, Level 15, State 1, Line 11

    Incorrect syntax near '?'.

    Msg 102, Level 15, State 1, Line 12

    Incorrect syntax near '?'.

    Msg 102, Level 15, State 1, Line 13

    Incorrect syntax near '?'.

    Msg 102, Level 15, State 1, Line 13

    Incorrect syntax near '?'.

  • Dave62

    SSCertifiable

    Points: 6706

    dbajunior (5/25/2012)


    When I try to run this script, I get the errors below. Note! These same 'Incorrect syntax near '' ' happen at times on other scripts I get from SSC and try to run.

    It may be an issue with my SSMS setup. Any one have some ideas on this? Thanks in advance!

    Msg 102, Level 15, State 1, Line 10

    Incorrect syntax near '?'.

    Msg 102, Level 15, State 1, Line 11

    Incorrect syntax near '?'.

    Msg 102, Level 15, State 1, Line 12

    Incorrect syntax near '?'.

    Msg 102, Level 15, State 1, Line 13

    Incorrect syntax near '?'.

    Msg 102, Level 15, State 1, Line 13

    Incorrect syntax near '?'.

    May be copy and paste issues. I've had this problem and have seen others post the same issue and solution.

    The problem seems to be that special/unwanted characters get copied from the web page and cause errors if pasted directly into SSMS.

    The solution that has worked for me and others is to paste the code into Notepad first then copy from Notepad to SSMS. The process will remove the special/unwanted characters and hopefully the errors in SSMS.

    Good luck!

  • dbajunior

    SSCrazy

    Points: 2307

    Thanks for the tip! In Notepad++, the code (a sample of it) displayed like this:

    declare @Database_Name nvarchar(100);????????????????????set @DATABASE_NAME = 'AdventureWorks2012'

    declare @Rebuild_Threshold_Percent nvarchar(10);????????set @REBUILD_THRESHOLD_PERCENT = 30

    declare @Reorganize_Threshold_Percent nvarchar(10);????????set @REORGANIZE_THRESHOLD_PERCENT = 30

    declare @Execute_Rebuild bit;????????????????????????????set @EXECUTE_REBUILD = 0????????-- set to 1 to rebuild

    declare @Execute_Reorganize bit;????????????????????????set @EXECUTE_REORGANIZE = 0????????-- set to 1 to reorganize. If rebuilding, no need to do this.

    After replacing '?' with a space, and pasting the code back to SSMS, it worked!

  • hakim.ali

    SSCarpal Tunnel

    Points: 4237

    Yes, it looks like the website replaces spaces with some other character that is not ASCII 32. You don't even have to go via Notepad, just in SSMS highlight one of these non-spaces, CTRL+H to replace all instances of these with a space, and it works.

    Anybody have a better workaround and explanation?

    Hakim Ali
    www.sqlzen.com

  • Knut Boehnert

    SSCrazy

    Points: 2947

    hakim.ali (5/25/2012)


    Yes, it looks like the website replaces spaces with some other character that is not ASCII 32. You don't even have to go via Notepad, just in SSMS highlight one of these non-spaces, CTRL+H to replace all instances of these with a space, and it works.

    Anybody have a better workaround and explanation?

    Unfortunately not a better workaround but what has already been stated. The trouble I think stems from Unicode encoding used in web pages that is invalid in SSMS and the difference between American US English expectations and the world being what it is (as in not really all American US English).

    Congratulations to a very nice script - I have seen bits across the web and was working on a similiar version for our databases - your script just saved me about 12 hours work, Hakim. Thanks a lot! :w00t:

  • hakim.ali

    SSCarpal Tunnel

    Points: 4237

    Knut Boehnert (5/25/2012)


    Congratulations to a very nice script - I have seen bits across the web and was working on a similiar version for our databases - your script just saved me about 12 hours work, Hakim. Thanks a lot! :w00t:

    Thank you, very kind.

    Hakim Ali
    www.sqlzen.com

  • crazy4sql

    SSCoach

    Points: 19590

    Thanks for the lovely script.

    I customised it with rebuild for more than 30% and reorg for more than 50% and only for those where page count is > 50. All these changes are marked as bold in original script.

    /****************************************************************************************************

    Hakim Ali (hakim.ali@sqlzen.com) 2012/05/04

    ****************************************************************************************************/

    -----------------------------------------------------------------------------------------------------

    ------------------------------------------------------- SET DESIRED VALUES HERE ---------------------

    -----------------------------------------------------------------------------------------------------

    declare @Database_Name nvarchar(100); set @DATABASE_NAME = 'AdventureWorks2012'

    declare @Rebuild_Threshold_Percent nvarchar(10); set @REBUILD_THRESHOLD_PERCENT = 30

    declare @Reorganize_Threshold_Percent nvarchar(10); set @REORGANIZE_THRESHOLD_PERCENT = 50

    declare @Execute_Rebuild bit; set @EXECUTE_REBUILD = 0 -- set to 1 to rebuild

    declare @Execute_Reorganize bit; set @EXECUTE_REORGANIZE = 0 -- set to 1 to reorganize. If rebuilding, no need to do this.

    -----------------------------------------------------------------------------------------------------

    -----------------------------------------------------------------------------------------------------

    -----------------------------------------------------------------------------------------------------

    ----------------------------------

    -- Initial checking

    ----------------------------------

    declare @Error nvarchar(500)

    declare @SQL nvarchar(max)

    declare @Online nvarchar(50)

    set @Database_Name = ltrim(rtrim(isnull(@Database_Name,'')))

    set @Error = 'Database name required.'

    if (@Database_Name = '')

    begin

    raiserror(@Error,16,1)

    goto the_end

    end

    set @Error = 'Database "' + @Database_Name + '" does not exist.'

    if not exists (select name from sys.databases where name = @Database_Name)

    begin

    raiserror(@Error,16,1)

    goto the_end

    end

    set @Online = ''

    if (@@version like '%enterprise edition%')

    begin

    set @Online = ' with (online = on)'

    end

    set @SQL = '

    ----------------------------------

    -- Create table to hold results

    ----------------------------------

    declare @fragmented_indexes table

    ( ID int identity(1,1)

    ,Database_Name nvarchar(1000)

    ,[Schema_Name] nvarchar(1000)

    ,Table_Name nvarchar(1000)

    ,Index_Name nvarchar(1000)

    ,Fragmentation_Percent money

    ,Num_Rows int

    ,Page_Count int

    ,Index_Type nvarchar(1000)

    ,Reorganize_SQL nvarchar(4000)

    ,Rebuild_SQL nvarchar(4000)

    )

    ----------------------------------

    -- Populate table

    ----------------------------------

    insert into @fragmented_indexes

    ( Database_Name

    ,[Schema_Name]

    ,Table_Name

    ,Index_Name

    ,Fragmentation_Percent

    ,Num_Rows

    ,Page_Count

    ,Index_Type

    )

    select distinct

    Database_Name

    = db_name(database_id)

    ,[Schema_Name]

    = sch.name

    ,Table_Name

    = parent.Name

    ,Index_Name

    = indx.name

    ,Fragmentation_Percent

    = left(isnull(phys_stats.avg_fragmentation_in_percent,0),5)

    ,Num_Rows

    = x.rowcnt

    ,Page_Count

    = phys_stats.page_count

    ,Index_Type

    = phys_stats.index_type_desc

    from sys.dm_db_index_physical_stats(

    db_id('''+@Database_Name+'''),

    default,

    default,

    default,

    ''detailed''

    ) phys_stats

    inner join ['+@Database_Name+'].sys.indexes indx

    on indx.object_id = phys_stats.object_id

    and indx.index_id = phys_stats.index_id

    inner join ['+@Database_Name+'].sys.objects parent

    on parent.object_id = phys_stats.object_id

    inner join ['+@Database_Name+'].dbo.sysindexes x

    on x.id = indx.object_id

    inner join ['+@Database_Name+'].sys.schemas sch

    on sch.schema_id = parent.schema_id

    where 1 = 1

    and isnull(indx.name,'''') <> ''''

    and x.rowcnt > 0

    and phys_stats.page_count > 50

    and (phys_stats.avg_fragmentation_in_percent >= '+@Rebuild_Threshold_Percent+'

    or

    phys_stats.avg_fragmentation_in_percent >= '+@Reorganize_Threshold_Percent+')

    update @fragmented_indexes

    set Reorganize_SQL =

    ''alter index [''+Index_Name+''] on [''+Database_Name+''].[''+[Schema_Name]+''].[''+Table_Name+''] reorganize''

    ,Rebuild_SQL =

    ''alter index [''+Index_Name+''] on [''+Database_Name+''].[''+[Schema_Name]+''].[''+Table_Name+''] rebuild'+@Online+'''

    ----------------------------------

    -- View results

    ----------------------------------

    select ID

    ,Database_Name

    ,[Schema_Name]

    ,Table_Name

    ,Index_Name

    ,Fragmentation_Percent

    ,Num_Rows

    ,Page_Count

    ,Index_Type

    from @fragmented_indexes

    order by Database_Name

    ,convert(money,Fragmentation_Percent) desc

    ,[Schema_Name]

    ,Table_Name

    '

    ----------------------------------

    -- If rebuild/reorganize option set...

    ----------------------------------

    if (@Execute_Rebuild = 1)

    begin

    set @SQL = @SQL + '

    declare @current_sql nvarchar(max)

    while exists (select top 1 Rebuild_SQL from @fragmented_indexes)

    begin

    set @current_sql = (select top 1 Rebuild_SQL from @fragmented_indexes)

    execute sp_executesql @current_sql

    --select @current_sql

    delete @fragmented_indexes where Rebuild_SQL = @current_sql

    end

    '

    end

    else if (@Execute_Reorganize = 1)

    begin

    set @SQL = @SQL + '

    declare @current_sql nvarchar(max)

    while exists (select top 1 Reorganize_SQL from @fragmented_indexes)

    begin

    set @current_sql = (select top 1 Reorganize_SQL from @fragmented_indexes)

    execute sp_executesql @current_sql

    --select @current_sql

    delete @fragmented_indexes where Reorganize_SQL = @current_sql

    end

    '

    end

    ----------------------------------

    -- Go!

    ----------------------------------

    execute sp_executesql @SQL

    the_end:

    ----------
    Ashish

  • akljfhnlaflkj

    SSC Guru

    Points: 76202

    Thanks for the script.

Viewing 9 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply