May 19, 2016 at 2:38 am
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.
May 19, 2016 at 7:59 am
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?
May 20, 2016 at 12:07 am
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
May 20, 2016 at 4:43 am
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?
May 20, 2016 at 5:09 am
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];
May 21, 2016 at 7:21 pm
Andre 425568 (5/19/2016)
HiI 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
Change is inevitable... Change for the better is not.
May 23, 2016 at 1:02 am
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
May 23, 2016 at 1:04 am
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
May 23, 2016 at 1:18 am
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
May 23, 2016 at 9:39 am
That seems over-complicated.
Have you tried
ALTER TABLE tablename REBUILD ?
May 23, 2016 at 10:13 am
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) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
May 23, 2016 at 4:59 pm
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
Change is inevitable... Change for the better is not.
May 24, 2016 at 2:09 am
I did rebuild before this, but still there were tables with over 80% fragmentation
May 24, 2016 at 8:12 am
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
Change is inevitable... Change for the better is not.
May 26, 2016 at 3:32 am
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