Defragment table?
-
I know Oracle has a DEFRAG function to run on a table, but I have not found anything in SQL Server like this.
I am hoping there is a function/utility out there already, or is the only option to drop and re-create the table?
If there is no function, has someone already written an application/script to save schema and data while rebuilding tables?
thanks
-
If the table has a clustered index, just rebuild it. If not, create one, then you can drop it. The data is stored in clustered index order, so building or rebuilding one will defrag the table.
Steve
-
You can check if it is necessary by running DBCC SHOWCONTIG (see BOL)
DBCC SHOWCONTIG ('Your table name') with fast,ALL_INDEXES
- Scan Density [Best Count:Actual Count].......: 12.52% [26658:212906] must be close to 100%
- Logical Scan Fragmentation ..................: 49.84% must be close to 0%
if not run the dbcc dbreindex
DBCC DBREINDEX ('Your table name)
-
All the tables have a clustered index on them already, so I can't add a new clustered index to them. I wanted to make this process as dynamic as possible, without having to change the procedure whenever a new table was added.
I have started a script to loop through and drop existing clustered indexes and readd them.
Thanks for the responses.
-
Don't drop/recreate the clustered indexes !
use dbcc dbreindex (check BOL)
DECLARE @TableName nvarchar(261)
DECLARE @SQLStatement nvarchar(4000)
DECLARE TableList CURSOR LOCAL FAST_FORWARD READ_ONLY FOR
SELECT
QUOTENAME(TABLE_SCHEMA) +
N'.' +
QUOTENAME(TABLE_NAME)
FROM
INFORMATION_SCHEMA.TABLES
WHERE EXISTS
(
SELECT *
FROM sysindexes
WHERE id =
OBJECT_ID(QUOTENAME(TABLE_SCHEMA) +
N'.' +
QUOTENAME(TABLE_NAME)) AND
indid IN(0,1) AND
rows > 10000
)
OPEN TableList
WHILE 1 = 1
BEGIN
FETCH NEXT FROM TableList INTO @TableName
IF @@FETCH_STATUS = -1 BREAK
RAISERROR ('Reindexing %s', 0, 1, @TableName) WITH NOWAIT
DBCC DBREINDEX (@TableName, 1)
EXEC(@SQLStatement)
END
CLOSE TableList
DEALLOCATE TableList
Johan
Learn to play, play to learn !Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:- How to post Performance Problems
- How to post data/code to get the best help[/url]- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
-
Instead or querying sysindexes and INFORMATION_SCHEMA, check Books Online for the DBCC SHOWCONTIG command. It has an option called TABLERESULTS which return the information in table form.
EG:
CREATE TABLE #Fraglist (
ObjectName sysname NOT NULL
, ObjectId int NOT NULL
, IndexName sysname NOT NULL
, IndexId int NOT NULL
, Lvl int NOT NULL
, CountPages int NOT NULL
, CountRows int NOT NULL
, MinRecSize int NOT NULL
, MaxRecSize int NOT NULL
, AvgRecSize int NOT NULL
, ForRecCount int NOT NULL
, Extents int NOT NULL
, ExtentSwitches int NOT NULL
, AvgFreeBytes int NOT NULL
, AvgPageDensity int NOT NULL
, ScanDensity decimal(9,4) NOT NULL
, BestCount int NOT NULL
, ActualCount int NOT NULL
, LogicalFrag decimal(9,4) NOT NULL
, ExtentFrag decimal(9,4) NOT NULL
)
INSERT INTO #FragList
EXEC('DBCC SHOWCONTIG WITH TABLERESULTS, ALL_INDEXES, NO_INFOMSGS')
SELEC * FROM #FragList
WHERE LogicalFrag > @FragPercent
--------------------
Colt 45 - the original point and click interface
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply