Recreating tables from a list of tables

  • Hi

    I need a script to recreate a list of tables stored in a table. The fields have to be in the same order as the original table, and must also include primary and foreign keys, constraints and indexes. I then want to add a new field which will be an identity column. I then also want to create an insert into the new table using the new identity column.

    The reason for this is we have masses of deleted rows which is causing fragmentation, so I want to script these tables, rename the original tables, create the new tables and insert the records using the new identity column, then update the values of the existing Primary key field with those in the identity column. Then create the primary Keys, Foreign Keys and Indexes. I do not want a manual process as we are talking multiple clients , hundreds of tables. I already have a script identifying which tables need to be recreated, that is stored in a table.

    Does anyone perhaps have an existing script for this.

  • Taking a step back, what problem are you trying to solve? You say you have fragmentation caused by deleting rows so are your indexes performing poorly? or is it there some other effect?

  • The problem is that the Database is originally from an ETL from a Adabas database, so we have hundreds of tables being created, most of the child tables have a lot of null values only, so as part of the ETL process the delete rows and then we update rows as well with extra values, or updated values.

    So in a table with 900000 rows we may eventually end up with 500000 rows in the table which means a lot of gaps in the primary keys ID field, the updates are resolved by doing table rebuild, but the deleted rows, or missing IDs are causing some of our tables to be up to 70%, in some cases even more fragmented. We are wording on fixing this already in the ETL phase but for now we have a number of clients with up to 99% fragmentation, which we bring down to say 70% with a rebuild.

    I have only been working here close to 3 years so this issue has been here before I even started here

    Hope this makes sense

  • ok, thanks for the additional info.

    Can you tell me a little bit about your tables, are they heaps? Do you have clustered and/or non-clustered indexes on your tables?

    Can you tell me how you are calculating the 70-99% fragmentation figure?

  • You could try modifying this: -

    SET NOCOUNT ON;

    IF OBJECT_ID('tempdb..#indexes') IS NOT NULL

    BEGIN

    DROP TABLE [#indexes];

    END;

    SELECT CASE WHEN [a].[type_desc] <> 'HEAP'

    THEN CASE WHEN [a].[is_primary_key] = 1

    OR [a].[is_unique] = 1

    THEN CHAR(13) + CHAR(10) + 'CONSTRAINT [' + [a].[index_name] + '] ' + CASE WHEN [a].[is_primary_key] = 1 THEN ' PRIMARY KEY '

    ELSE CASE WHEN [a].[is_unique] = 1 THEN ' UNIQUE '

    ELSE ''

    END

    END + [a].[type_desc] COLLATE Latin1_General_CI_AS

    + CASE WHEN [a].[type_desc] = 'NONCLUSTERED' THEN ''

    ELSE ' '

    END + ' (' + [a].[index_columns_key] + ')' + CASE WHEN [a].[index_columns_include] <> '---'

    THEN ' INCLUDE (' + [a].[index_columns_include] + ')'

    ELSE ''

    END + CASE WHEN [a].[fill_factor] <> 0

    THEN ' WITH FILLFACTOR = ' + CONVERT(VARCHAR(30), [a].[fill_factor])

    ELSE ''

    END + ','

    ELSE CHAR(13) + CHAR(10) + 'CREATE INDEX [' + [a].[index_name] + '] ' + ' ON [' + [a].[object_name] + ']' + ' ('

    + [a].[index_columns_key] + ')' + CASE WHEN [a].[index_columns_include] <> '---'

    THEN ' INCLUDE (' + [a].[index_columns_include] + ')'

    ELSE ''

    END + CASE WHEN [a].[fill_factor] <> 0

    THEN ' WITH FILLFACTOR = ' + CONVERT(VARCHAR(30), [a].[fill_factor])

    ELSE ''

    END + ';'

    END

    ELSE ''

    END AS [INDEX_DEF],

    CASE WHEN [a].[type_desc] <> 'HEAP' THEN CASE WHEN [a].[is_primary_key] = 1

    OR [a].[is_unique] = 1 THEN 'CONSTRAINT'

    ELSE 'INDEX'

    END

    ELSE ''

    END AS [TYPE],

    [a].[object_id]

    INTO [#indexes]

    FROM ( SELECT [conObj].[object_id],

    [conObj].[name] AS [object_name],

    [conInde].[index_id],

    ISNULL([conInde].[name], '---') AS [index_name],

    [partitions].[Rows],

    [partitions].[SizeMB],

    INDEXPROPERTY([conObj].[object_id], [conInde].[name], 'IndexDepth') AS [IndexDepth],

    [conInde].[type],

    [conInde].[type_desc],

    [conInde].[fill_factor],

    [conInde].[is_unique],

    [conInde].[is_primary_key],

    [conInde].[is_unique_constraint],

    ISNULL([Index_Columns].[index_columns_key], '---') AS [index_columns_key],

    ISNULL([Index_Columns].[index_columns_include], '---') AS [index_columns_include]

    FROM [sys].[objects] [conObj]

    JOIN [sys].[indexes] [conInde] ON [conObj].[object_id] = [conInde].[object_id]

    JOIN ( SELECT [object_id],

    [index_id],

    SUM([row_count]) AS [Rows],

    CONVERT(NUMERIC(19, 3), CONVERT(NUMERIC(19, 3), SUM([in_row_reserved_page_count] + [lob_reserved_page_count]

    + [row_overflow_reserved_page_count])) / CONVERT(NUMERIC(19, 3), 128)) AS [SizeMB]

    FROM [sys].[dm_db_partition_stats]

    GROUP BY [object_id],

    [index_id]

    ) AS [partitions] ON [conInde].[object_id] = [partitions].[object_id]

    AND [conInde].[index_id] = [partitions].[index_id]

    CROSS APPLY ( SELECT LEFT([Index_Columns].[index_columns_key], LEN([Index_Columns].[index_columns_key]) - 1) AS [index_columns_key],

    LEFT([Index_Columns].[index_columns_include], LEN([Index_Columns].[index_columns_include]) - 1) AS [index_columns_include]

    FROM ( SELECT ( SELECT [name] + ',' + ' '

    FROM [sys].[index_columns]

    JOIN [sys].[columns] ON [Index_Columns].[column_id] = [columns].[column_id]

    AND [Index_Columns].[object_id] = [columns].[object_id]

    WHERE [is_included_column] = 0

    AND [conInde].[object_id] = [Index_Columns].[object_id]

    AND [conInde].[index_id] = [index_id]

    ORDER BY [key_ordinal]

    FOR

    XML PATH('')

    ) AS [index_columns_key],

    ( SELECT [name] + ',' + ' '

    FROM [sys].[index_columns]

    JOIN [sys].[columns] ON [Index_Columns].[column_id] = [columns].[column_id]

    AND [Index_Columns].[object_id] = [columns].[object_id]

    WHERE [is_included_column] = 1

    AND [conInde].[object_id] = [Index_Columns].[object_id]

    AND [conInde].[index_id] = [index_id]

    ORDER BY [index_column_id]

    FOR

    XML PATH('')

    ) AS [index_columns_include]

    ) AS [Index_Columns]

    ) AS [Index_Columns]

    ) [a];

    SELECT [tab].[name] AS [Name],

    SUBSTRING([creat].[def], 1, LEN([creat].[def]) - 1) + ');' + ISNULL(CHAR(13) + CHAR(10) + [permissions].[def], '') + CHAR(13) + CHAR(10)

    + ISNULL([ind].[def], '') + ISNULL([trig].[def], '') AS [Definition]

    FROM [sys].[objects] [tab]

    CROSS APPLY ( SELECT ( (SELECT CASE WHEN [col].[is_computed] = 1 THEN CHAR(13) + CHAR(10) + '[' + UPPER([col].[name]) + '] ' + 'AS ' + UPPER([col].[name])

    ELSE CHAR(13) + CHAR(10) + '[' + UPPER([col].[name]) + '] ' + UPPER(TYPE_NAME([col].[system_type_id]))

    + CASE WHEN TYPE_NAME([col].[system_type_id]) IN ( 'decimal', 'numeric' )

    THEN '(' + CONVERT(VARCHAR, [col].[precision]) + ',' + CONVERT(VARCHAR, [col].[scale]) + ') '

    + CASE WHEN [col].[is_nullable] = 0 THEN ' NOT NULL'

    ELSE ' NULL'

    END

    WHEN TYPE_NAME([col].[system_type_id]) IN ( 'float', 'real' )

    THEN CASE WHEN [col].[precision] = 53 THEN CASE WHEN [col].[is_nullable] = 0 THEN ' NOT NULL'

    ELSE ' NULL'

    END

    ELSE '(' + CONVERT(VARCHAR, [col].[precision]) + ') '

    + CASE WHEN [col].[is_nullable] = 0 THEN ' NOT NULL'

    ELSE ' NULL'

    END

    END

    WHEN TYPE_NAME([col].[system_type_id]) IN ( 'char', 'varchar' )

    THEN CASE WHEN [col].[max_length] = -1 THEN '(max)' + CASE WHEN [col].[is_nullable] = 0 THEN ' NOT NULL'

    ELSE ' NULL'

    END

    ELSE '(' + CONVERT(VARCHAR, [col].[max_length]) + ') '

    + CASE WHEN [col].[is_nullable] = 0 THEN ' NOT NULL'

    ELSE ' NULL'

    END

    END

    WHEN TYPE_NAME([col].[system_type_id]) IN ( 'nchar', 'nvarchar' )

    THEN CASE WHEN [col].[max_length] = -1 THEN '(max)' + CASE WHEN [col].[is_nullable] = 0 THEN ' NOT NULL'

    ELSE ' NULL'

    END

    ELSE '(' + CONVERT(VARCHAR, ( [col].[max_length] / 2 )) + ') '

    + CASE WHEN [col].[is_nullable] = 0 THEN ' NOT NULL'

    ELSE ' NULL'

    END

    END

    WHEN TYPE_NAME([col].[system_type_id]) IN ( 'datetime', 'money', 'text', 'image' )

    THEN ' ' + CASE WHEN [col].[is_nullable] = 0 THEN ' NOT NULL'

    ELSE ' NULL'

    END

    ELSE +CASE WHEN COLUMNPROPERTY([col].[object_id], [col].[name], 'IsIdentity') = 0 THEN ' '

    ELSE ' IDENTITY(' + CONVERT(VARCHAR, ISNULL(IDENT_SEED(OBJECT_NAME([col].[object_id])), 1))

    + ',' + CONVERT(VARCHAR, ISNULL(IDENT_INCR(OBJECT_NAME([col].[object_id])), 1)) + ')'

    END + CASE WHEN [col].[is_nullable] = 0 THEN ' NOT NULL'

    ELSE ' NULL'

    END

    END + CASE WHEN [col].[default_object_id] = 0 THEN ''

    ELSE ' CONSTRAINT [' + [DEF].[name] + '] DEFAULT ' + ISNULL([DEF].[definition], '')

    END

    END + ','

    FROM [sys].[columns] [col]

    LEFT OUTER JOIN [sys].[default_constraints] [DEF] ON [col].[default_object_id] = [DEF].[object_id]

    WHERE [col].[object_id] = [tab].[object_id]

    ORDER BY [col].[column_id]

    FOR

    XML PATH(''),

    TYPE

    ).[value]('.', 'NVARCHAR(MAX)') )

    ) [cols] ( [def] )

    OUTER APPLY ( SELECT ( (SELECT [allCons].[INDEX_DEF]

    FROM [#indexes] AS [allCons]

    WHERE [allCons].[TYPE] = 'CONSTRAINT'

    AND [allCons].[object_id] = [tab].[object_id]

    FOR

    XML PATH(''),

    TYPE).[value]('.', 'NVARCHAR(MAX)') )

    ) [cons] ( [def] )

    OUTER APPLY ( SELECT ( (SELECT CHAR(13) + CHAR(10) + ISNULL('CONSTRAINT [' + [objects].[name] + '] ' + ' CHECK ' + ISNULL([definition], '') + ',', '')

    FROM [sys].[objects]

    INNER JOIN [sys].[check_constraints] ON [objects].[object_id] = [check_constraints].[object_id]

    WHERE [objects].[type] = 'C'

    AND [objects].[parent_object_id] = [tab].[object_id]

    FOR

    XML PATH(''),

    TYPE).[value]('.', 'NVARCHAR(MAX)') )

    ) [chec] ( [def] )

    OUTER APPLY ( SELECT ( ( SELECT CHAR(13) + CHAR(10) + 'CONSTRAINT [' + OBJECT_NAME([constid]) + ']' + ' FOREIGN KEY (' + COL_NAME([fkeyid], [fkey])

    + ') REFERENCES [' + OBJECT_SCHEMA_NAME([rkeyid]) + '].[' + OBJECT_NAME([rkeyid]) + ']' + '(' + COL_NAME([rkeyid],

    [rkey]) + '),'

    FROM [sys].[sysforeignkeys]

    WHERE [fkeyid] = [tab].[object_id]

    FOR

    XML PATH(''),

    TYPE).[value]('.', 'NVARCHAR(MAX)') )

    ) [fk] ( [def] )

    OUTER APPLY ( SELECT ( (SELECT [allCons].[INDEX_DEF]

    FROM [#indexes] AS [allCons]

    WHERE [allCons].[TYPE] = 'INDEX'

    AND [allCons].[object_id] = [tab].[object_id]

    FOR

    XML PATH(''),

    TYPE).[value]('.', 'NVARCHAR(MAX)') )

    ) [ind] ( [def] )

    OUTER APPLY ( SELECT STUFF((SELECT CHAR(13) + CHAR(10) + [p].[state_desc] COLLATE Latin1_General_CI_AS + SPACE(1) + [p].[permission_name] COLLATE Latin1_General_CI_AS

    + ' ON [' + OBJECT_SCHEMA_NAME([p].[major_id]) COLLATE Latin1_General_CI_AS + '].[' + OBJECT_NAME([p].[major_id]) COLLATE Latin1_General_CI_AS

    + '] TO [' + [dp].[name] COLLATE Latin1_General_CI_AS + '];'

    FROM [sys].[database_permissions] [p]

    INNER JOIN [sys].[database_principals] [dp] ON [p].[grantee_principal_id] = [dp].[principal_id]

    WHERE [major_id] = [tab].[object_id]

    FOR XML PATH(''),

    TYPE).value('.', 'NVARCHAR(MAX)'), 1, 2, '')

    ) [permissions] ( [def] )

    CROSS APPLY ( SELECT 'CREATE TABLE [' + OBJECT_SCHEMA_NAME([tab].[object_id]) + '].[' + OBJECT_NAME([tab].[object_id]) + '] ( ' + [cols].[def]

    + ISNULL([cons].[def], '') + ISNULL([chec].[def], '') + ISNULL([fk].[def], '')

    ) [creat] ( [def] )

    OUTER APPLY ( SELECT ( (SELECT CHAR(13) + CHAR(10) + CHAR(13) + CHAR(10) + 'GO' + CHAR(13) + CHAR(10) + OBJECT_DEFINITION([o].[object_id]) + CHAR(13)

    + CHAR(10) + 'GO'

    FROM [sys].[objects] [o]

    WHERE [o].[type] = 'TR'

    AND [o].[parent_object_id] = [tab].[object_id]

    FOR

    XML PATH(''),

    TYPE).[value]('.', 'NVARCHAR(MAX)') )

    ) [trig] ( [def] )

    WHERE [tab].[type] = 'U'

    AND [tab].[is_ms_shipped] = 0

    AND OBJECTPROPERTY([tab].[object_id], 'IsUserTable') = 1

    AND [tab].[object_id] NOT IN ( SELECT [major_id]

    FROM [sys].[extended_properties]

    WHERE [minor_id] = 0

    AND [minor_id] = 0

    AND [class] = 1

    AND [name] = N'microsoft_database_tools_support' )

    AND [tab].[name] <> 'dtproperties'

    ORDER BY [tab].[name];


    Forever trying to learn
    My blog - http://www.cadavre.co.uk/
    For better, quicker answers on T-SQL questions, click on the following...http://www.sqlservercentral.com/articles/Best+Practices/61537/
    For better, quicker answers on SQL Server performance related questions, click on the following...http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

  • Andre 425568 (5/19/2016)


    Hi

    I need a script to recreate a list of tables stored in a table. The fields have to be in the same order as the original table, and must also include primary and foreign keys, constraints and indexes. I then want to add a new field which will be an identity column. I then also want to create an insert into the new table using the new identity column.

    The reason for this is we have masses of deleted rows which is causing fragmentation, so I want to script these tables, rename the original tables, create the new tables and insert the records using the new identity column, then update the values of the existing Primary key field with those in the identity column. Then create the primary Keys, Foreign Keys and Indexes. I do not want a manual process as we are talking multiple clients , hundreds of tables. I already have a script identifying which tables need to be recreated, that is stored in a table.

    Does anyone perhaps have an existing script for this.

    That would be pretty much an error prone waste of time. It doesn't matter what the IDENTITY values are. If you want to defragment your tables, do that but don't worry about the IDENTITY values. There's really nothing to be gained by such a systemic change.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • We found that fixing this brings fragmentation down by anything from 0-60 % after doing table rebuilds, after rebuilds we staill have fragmentation on tables and indexes. We are using Non Clustered Indexes, which means we have heap tables, If we had clustered indexes this would not have been such an issue

  • Heap tables yes, basically all our indexes except for one is Non Clustered. This is part of the problem. I have inherited it like this

  • Douglas

    This the script I use, works quite nicely. I did the table rebuilds this weekend at one of our clients and then did index rebuilds. My forwarded records are all gone which I expected. But there are still tables with a huge number of fragmentation, so I redid some of the manually, renamed the old table, created a table with the same fields, added a autonumbering field, imported the records, updated the ID column from the added autonumbering field, then deleted the autonumbering field, did a rebuild of the table, deleted the original table, added PK, FK and indexes. In some cases it decreased fragemntation by up to 60 %. I only did two of the tables this weekend manually to check the results. We do not use autonumbering as a decision was used before I started to work with Sequences. Our newer tables use autonumbering though

    --DROP TABLE [dbo].[DB_Stats]

    --CREATE TABLE [dbo].[DB_Stats](

    --[TableName] varchar(50) NULL,

    --[IndexName] varchar(150) NULL,

    --[StatsDate] [datetime] NULL,

    --[NoOfRows] [numeric](20, 0) NULL,

    --[TotalNoOfPages] [numeric](20, 0) NULL,

    --[TotalNoUsedPages] [numeric](20, 0) NULL,

    --[TotalNoUnusedPages] [numeric](20, 0) NULL,

    --[IndexType] [varchar](50) NULL,

    --[NoOfForwardedRows] [numeric](20, 0) NULL,

    --[FragmentationPercentage] [float] NULL

    --) ON [PRIMARY]

    --GO

    --CREATE TABLE [dbo].[DB_Stats_Archive](

    --[TableName] varchar(50) NULL,

    --[IndexName] varchar(150) NULL,

    --[StatsDate] [datetime] NULL,

    --[NoOfRows] [numeric](20, 0) NULL,

    --[TotalNoOfPages] [numeric](20, 0) NULL,

    --[TotalNoUsedPages] [numeric](20, 0) NULL,

    --[TotalNoUnusedPages] [numeric](20, 0) NULL,

    --[IndexType] [varchar](50) NULL,

    --[NoOfForwardedRows] [numeric](20, 0) NULL,

    --[FragmentationPercentage] [float] NULL

    --) ON [PRIMARY]

    --GO

    INSERT INTO [DB_Stats_Archive]

    ([TableName]

    ,[IndexName]

    ,[StatsDate]

    ,[NoOfRows]

    ,[TotalNoOfPages]

    ,[TotalNoUsedPages]

    ,[TotalNoUnusedPages]

    ,[IndexType]

    ,[NoOfForwardedRows]

    ,[FragmentationPercentage])

    select [TableName]

    ,[IndexName]

    ,[StatsDate]

    ,[NoOfRows]

    ,[TotalNoOfPages]

    ,[TotalNoUsedPages]

    ,[TotalNoUnusedPages]

    ,[IndexType]

    ,[NoOfForwardedRows]

    ,[FragmentationPercentage]

    from [DB_Stats]

    GO

    truncate table [DB_Stats]

    GO

    SELECT Database_id,

    Index_id,

    index_level,

    index_depth,

    OBJECT_NAME( [object_id] ) AS TABLENAME,

    Index_type_desc,

    Avg_fragmentation_in_percent,

    forwarded_record_count

    INTO #Forward

    FROM sys.dm_db_index_physical_stats( DB_ID( ), NULL, NULL, NULL, 'detailed' ) AS SDDIPS

    WHERE index_id = 0 AND

    forwarded_record_count <> 0

    ORDER BY OBJECT_NAME( [object_id] )

    SELECT DBSCHEMAS.[name] AS 'Schema',

    DBTABLES.[name] AS 'Table',

    DBINDEXES.[name] AS 'Index',

    INDEXSTATS.avg_fragmentation_in_percent,

    INDEXSTATS.page_count

    INTO #Index

    FROM sys.dm_db_index_physical_stats ( DB_ID( ), NULL, NULL, NULL, NULL ) AS INDEXSTATS

    INNER JOIN sys.tables DBTABLES

    ON DBTABLES.[object_id] = INDEXSTATS.[object_id]

    INNER JOIN sys.schemas DBSCHEMAS

    ON DBTABLES.[schema_id] = DBSCHEMAS.[schema_id]

    INNER JOIN sys.indexes AS DBINDEXES

    ON DBINDEXES.[object_id] = INDEXSTATS.[object_id] AND

    INDEXSTATS.index_id = DBINDEXES.index_id

    WHERE INDEXSTATS.database_id = DB_ID( )

    ORDER BY DBTABLES.[name],DBINDEXES.[name]

    SELECT T.NAME AS TABLENAME,

    P.rows AS ROWCOUNTS,

    SUM( A.total_pages ) AS TOTALPAGES,

    SUM( A.used_pages ) AS USEDPAGES,

    ( SUM( A.total_pages ) - SUM( A.used_pages ) ) AS UNUSEDPAGES,

    isnull( I.name, ' ' ) AS NAME,

    I.type_desc

    INTO #Pages

    FROM sys.tables T

    INNER JOIN sys.indexes I

    ON T.OBJECT_ID = I.object_id

    INNER JOIN sys.partitions P

    ON I.object_id = P.OBJECT_ID AND

    I.index_id = P.index_id

    INNER JOIN sys.allocation_units A

    ON P.partition_id = A.container_id

    WHERE T.is_ms_shipped = 0 AND

    I.OBJECT_ID > 255

    GROUP BY T.Name,P.Rows,I.name,I.type_desc

    ORDER BY T.Name,I.name

    INSERT INTO [dbo].[DB_Stats]

    ([TableName]

    ,[IndexName]

    ,[StatsDate]

    ,[NoOfRows]

    ,[TotalNoOfPages]

    ,[TotalNoUsedPages]

    ,[TotalNoUnusedPages]

    ,[IndexType]

    ,[NoOfForwardedRows]

    ,[FragmentationPercentage])

    SELECT

    [Table]AS TableName,

    isnull([Index],' ')AS IndexName,

    GETDATE( )AS StatsDate,

    CAST( '0' AS NUMERIC( 20, 0 ))AS NoOfRows,

    CAST( '0' AS NUMERIC( 20, 0 ))AS TotalNoOfPages,

    CAST( '0' AS NUMERIC( 20, 0 ))AS TotalNoUsedPages,

    CAST( '0' AS NUMERIC( 20, 0 ))AS TotalNoUnusedPages,

    CAST (' ' as varchar(50))AS IndexType,

    CAST( '0' AS NUMERIC( 20, 0 ))AS NoOfForwardedRows,

    avg_fragmentation_in_percentAS FragmentationPercentage

    FROM #Index

    UPDATE DB_Stats

    SET NoOfForwardedRows = A.forwarded_record_count

    FROM #Forward A,

    DB_Stats B

    WHERE A.TABLENAME = B.TableName AND

    B.IndexName = ' '

    UPDATE DB_Stats

    SET IndexType = A.type_desc

    FROM #Pages A,

    DB_Stats B

    WHERE A.TABLENAME = B.TableName AND

    A.NAME = B.IndexName

    UPDATE DB_Stats

    SET NoOfRows = A.ROWCOUNTS

    FROM #Pages A,

    DB_Stats B

    WHERE A.TABLENAME = B.TableName AND

    A.NAME = B.IndexName

    UPDATE DB_Stats

    SET TotalNoOfPages = A.TOTALPAGES

    FROM #Pages A,

    DB_Stats B

    WHERE A.TABLENAME = B.TableName AND

    A.NAME = B.IndexName

    UPDATE DB_Stats

    SET TotalNoUsedPages = A.USEDPAGES

    FROM #Pages A,

    DB_Stats B

    WHERE A.TABLENAME = B.TableName AND

    A.NAME = B.IndexName

    UPDATE DB_Stats

    SET TotalNoUnusedPages = A.UNUSEDPAGES

    FROM #Pages A,

    DB_Stats B

    WHERE A.TABLENAME = B.TableName AND

    A.NAME = B.IndexName

    GO

    SELECT *

    FROM [DB_Stats]

    ORDER BY TableName,IndexName

    DROP TABLE #Forward

    GO

    DROP TABLE #Index

    GO

    DROP TABLE #Pages

    GO

  • That seems over-complicated.

    Have you tried

    ALTER TABLE tablename REBUILD ?

  • Andre 425568 (5/23/2016)


    We found that fixing this brings fragmentation down by anything from 0-60 % after doing table rebuilds, after rebuilds we staill have fragmentation on tables and indexes. We are using Non Clustered Indexes, which means we have heap tables, If we had clustered indexes this would not have been such an issue

    Absolutely correct. So why not determine the best clustering for every table and add that index to the table? That will solve multiple performance issues all at once, including perhaps the fragmentation issue.

    SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.

  • Andre 425568 (5/23/2016)


    We found that fixing this brings fragmentation down by anything from 0-60 % after doing table rebuilds, after rebuilds we staill have fragmentation on tables and indexes. We are using Non Clustered Indexes, which means we have heap tables, If we had clustered indexes this would not have been such an issue

    It wasn't the IDENTITY changes that caused the reduction in fragmentation. It was the fact that you rebuilt the tables and, presumably, the indexes.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • I did rebuild before this, but still there were tables with over 80% fragmentation

  • Andre 425568 (5/24/2016)


    I did rebuild before this, but still there were tables with over 80% fragmentation

    For smaller tables, SQL Server doesn't always rebuild an index even if you told it to do so.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • I got fragmentation stats after the Table rebuild and then after index rebuild. Still had a lot of fragmentation.

    Ideally I would love to have Clustered indexes but it was already a fight just to get table rebuilding done. I have to really push for any db changes as I am not in authority here. We sort of have to take it step by step. I don't even want to talk about what else is wrong on our databases, will be writing for the next two hours, so its just trying to fix things one by one.

    So for now with non clustered indexes which is the way things are for now, and a battle to be fought later, my only way of bringing down fragmentation further is recreating the tables so there are not gaps in the primary keys.

Viewing 15 posts - 1 through 15 (of 21 total)

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