I am working with multiple servers, and numerous databases. I have
some large databases, some tables with 100's of millions of rows.
Some of the DB's are 24/7 so recreating the indexes is not really an
option. I am trying to come up with a "smart" index plan. I have
seen many examples of this, and I have one that does seem to work,
except that the DBCC showcontig seems to take forever! I am using a
variation on the following. The DBCC Showcontig seems to lock the
table, is this true?
Is there a faster way to get the DBCC fragmentation information?
Is it really worth the work identifying and defragmenting those
indexes that fall below a threshold or should I just defrag every
index?
Any other help on a "smart" maint plan for index maint would be
appreciated!
OS's : w2k Adv Server fully patched, 2003 Server Fully Patched
I am on SQL 2000, some at SP3a / SP4.
Sample of code below:
http://msdn2.microsoft.com/en-us/library/ms175008.aspx
-- Declare variables
SET NOCOUNT ON;
DECLARE @.tablename varchar(128);
DECLARE @.execstr varchar(255);
DECLARE @.objectid int;
DECLARE @.indexid int;
DECLARE @.frag decimal;
DECLARE @.maxfrag decimal;
-- Decide on the maximum fragmentation to allow for.
SELECT @.maxfrag = 30.0;
-- Declare a cursor.
DECLARE tables CURSOR FOR
SELECT TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE';
-- Create the table.
CREATE TABLE #fraglist (
ObjectName char(255),
ObjectId int,
IndexName char(255),
IndexId int,
Lvl int,
CountPages int,
CountRows int,
MinRecSize int,
MaxRecSize int,
AvgRecSize int,
ForRecCount int,
Extents int,
ExtentSwitches int,
AvgFreeBytes int,
AvgPageDensity int,
ScanDensity decimal,
BestCount int,
ActualCount int,
LogicalFrag decimal,
ExtentFrag decimal);
-- Open the cursor.
OPEN tables;
-- Loop through all the tables in the database.
FETCH NEXT
FROM tables
INTO @.tablename;
WHILE @.@.FETCH_STATUS = 0
BEGIN;
-- Do the showcontig of all indexes of the table
INSERT INTO #fraglist
EXEC ('DBCC SHOWCONTIG (''' + @.tablename + ''')
WITH FAST, TABLERESULTS, ALL_INDEXES, NO_INFOMSGS');
FETCH NEXT
FROM tables
INTO @.tablename;
END;
-- Close and deallocate the cursor.
CLOSE tables;
DEALLOCATE tables;
-- Declare the cursor for the list of indexes to be defragged.
DECLARE indexes CURSOR FOR
SELECT ObjectName, ObjectId, IndexId, LogicalFrag
FROM #fraglist
WHERE LogicalFrag >= @.maxfrag
AND INDEXPROPERTY (ObjectId, IndexName, 'IndexDepth') > 0;
-- Open the cursor.
OPEN indexes;
-- Loop through the indexes.
FETCH NEXT
FROM indexes
INTO @.tablename, @.objectid, @.indexid, @.frag;
WHILE @.@.FETCH_STATUS = 0
BEGIN;
PRINT 'Executing DBCC INDEXDEFRAG (0, ' + RTRIM(@.tablename) + ',
' + RTRIM(@.indexid) + ') - fragmentation currently '
+ RTRIM(CONVERT(varchar(15),@.frag)) + '%';
SELECT @.execstr = 'DBCC INDEXDEFRAG (0, ' + RTRIM(@.objectid) + ',
' + RTRIM(@.indexid) + ')';
EXEC (@.execstr);
FETCH NEXT
FROM indexes
INTO @.tablename, @.objectid, @.indexid, @.frag;
END;
-- Close and deallocate the cursor.
CLOSE indexes;
DEALLOCATE indexes;
-- Delete the temporary table.
DROP TABLE #fraglist;
GO
Do you have a test environment where you can load a copy of prod? If so,
generate the defrag script there but execute it against prod. This way, you
won't be locking up the table in prod with a share lock by running DBCC
SHOWCONTIG.
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
"David Hay" <david.hay@.gmail.com> wrote in message
news:368f7322-7f34-40c7-8e5e-cd32f1af00aa@.w56g2000hsf.googlegroups.com...
I am working with multiple servers, and numerous databases. I have
some large databases, some tables with 100's of millions of rows.
Some of the DB's are 24/7 so recreating the indexes is not really an
option. I am trying to come up with a "smart" index plan. I have
seen many examples of this, and I have one that does seem to work,
except that the DBCC showcontig seems to take forever! I am using a
variation on the following. The DBCC Showcontig seems to lock the
table, is this true?
Is there a faster way to get the DBCC fragmentation information?
Is it really worth the work identifying and defragmenting those
indexes that fall below a threshold or should I just defrag every
index?
Any other help on a "smart" maint plan for index maint would be
appreciated!
OS's : w2k Adv Server fully patched, 2003 Server Fully Patched
I am on SQL 2000, some at SP3a / SP4.
Sample of code below:
http://msdn2.microsoft.com/en-us/library/ms175008.aspx
-- Declare variables
SET NOCOUNT ON;
DECLARE @.tablename varchar(128);
DECLARE @.execstr varchar(255);
DECLARE @.objectid int;
DECLARE @.indexid int;
DECLARE @.frag decimal;
DECLARE @.maxfrag decimal;
-- Decide on the maximum fragmentation to allow for.
SELECT @.maxfrag = 30.0;
-- Declare a cursor.
DECLARE tables CURSOR FOR
SELECT TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE';
-- Create the table.
CREATE TABLE #fraglist (
ObjectName char(255),
ObjectId int,
IndexName char(255),
IndexId int,
Lvl int,
CountPages int,
CountRows int,
MinRecSize int,
MaxRecSize int,
AvgRecSize int,
ForRecCount int,
Extents int,
ExtentSwitches int,
AvgFreeBytes int,
AvgPageDensity int,
ScanDensity decimal,
BestCount int,
ActualCount int,
LogicalFrag decimal,
ExtentFrag decimal);
-- Open the cursor.
OPEN tables;
-- Loop through all the tables in the database.
FETCH NEXT
FROM tables
INTO @.tablename;
WHILE @.@.FETCH_STATUS = 0
BEGIN;
-- Do the showcontig of all indexes of the table
INSERT INTO #fraglist
EXEC ('DBCC SHOWCONTIG (''' + @.tablename + ''')
WITH FAST, TABLERESULTS, ALL_INDEXES, NO_INFOMSGS');
FETCH NEXT
FROM tables
INTO @.tablename;
END;
-- Close and deallocate the cursor.
CLOSE tables;
DEALLOCATE tables;
-- Declare the cursor for the list of indexes to be defragged.
DECLARE indexes CURSOR FOR
SELECT ObjectName, ObjectId, IndexId, LogicalFrag
FROM #fraglist
WHERE LogicalFrag >= @.maxfrag
AND INDEXPROPERTY (ObjectId, IndexName, 'IndexDepth') > 0;
-- Open the cursor.
OPEN indexes;
-- Loop through the indexes.
FETCH NEXT
FROM indexes
INTO @.tablename, @.objectid, @.indexid, @.frag;
WHILE @.@.FETCH_STATUS = 0
BEGIN;
PRINT 'Executing DBCC INDEXDEFRAG (0, ' + RTRIM(@.tablename) + ',
' + RTRIM(@.indexid) + ') - fragmentation currently '
+ RTRIM(CONVERT(varchar(15),@.frag)) + '%';
SELECT @.execstr = 'DBCC INDEXDEFRAG (0, ' + RTRIM(@.objectid) + ',
' + RTRIM(@.indexid) + ')';
EXEC (@.execstr);
FETCH NEXT
FROM indexes
INTO @.tablename, @.objectid, @.indexid, @.frag;
END;
-- Close and deallocate the cursor.
CLOSE indexes;
DEALLOCATE indexes;
-- Delete the temporary table.
DROP TABLE #fraglist;
GO
|||The way to really solve this is to upgrade to 2005 as the equivalent of
Showcontig does not block like in 2000. But the code you have now seems to
be the fastest and lightest way to do this in 2000. I never recommend on
larger dbs to reindex blindly so your approach is what I would recommend
anyway. You can keep a history of the fragmentation by index and use that to
determine which to defrag. Chances are if the usage is roughly the same week
to week the indexes will be roughly the same in terms of fragmentation. So
collect samples for a few weeks and base your rebuilding off of those
numbers for x many future weeks.
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"David Hay" <david.hay@.gmail.com> wrote in message
news:368f7322-7f34-40c7-8e5e-cd32f1af00aa@.w56g2000hsf.googlegroups.com...
>I am working with multiple servers, and numerous databases. I have
> some large databases, some tables with 100's of millions of rows.
> Some of the DB's are 24/7 so recreating the indexes is not really an
> option. I am trying to come up with a "smart" index plan. I have
> seen many examples of this, and I have one that does seem to work,
> except that the DBCC showcontig seems to take forever! I am using a
> variation on the following. The DBCC Showcontig seems to lock the
> table, is this true?
> Is there a faster way to get the DBCC fragmentation information?
> Is it really worth the work identifying and defragmenting those
> indexes that fall below a threshold or should I just defrag every
> index?
> Any other help on a "smart" maint plan for index maint would be
> appreciated!
> OS's : w2k Adv Server fully patched, 2003 Server Fully Patched
> I am on SQL 2000, some at SP3a / SP4.
> Sample of code below:
> http://msdn2.microsoft.com/en-us/library/ms175008.aspx
> -- Declare variables
> SET NOCOUNT ON;
> DECLARE @.tablename varchar(128);
> DECLARE @.execstr varchar(255);
> DECLARE @.objectid int;
> DECLARE @.indexid int;
> DECLARE @.frag decimal;
> DECLARE @.maxfrag decimal;
> -- Decide on the maximum fragmentation to allow for.
> SELECT @.maxfrag = 30.0;
> -- Declare a cursor.
> DECLARE tables CURSOR FOR
> SELECT TABLE_NAME
> FROM INFORMATION_SCHEMA.TABLES
> WHERE TABLE_TYPE = 'BASE TABLE';
> -- Create the table.
> CREATE TABLE #fraglist (
> ObjectName char(255),
> ObjectId int,
> IndexName char(255),
> IndexId int,
> Lvl int,
> CountPages int,
> CountRows int,
> MinRecSize int,
> MaxRecSize int,
> AvgRecSize int,
> ForRecCount int,
> Extents int,
> ExtentSwitches int,
> AvgFreeBytes int,
> AvgPageDensity int,
> ScanDensity decimal,
> BestCount int,
> ActualCount int,
> LogicalFrag decimal,
> ExtentFrag decimal);
> -- Open the cursor.
> OPEN tables;
> -- Loop through all the tables in the database.
> FETCH NEXT
> FROM tables
> INTO @.tablename;
> WHILE @.@.FETCH_STATUS = 0
> BEGIN;
> -- Do the showcontig of all indexes of the table
> INSERT INTO #fraglist
> EXEC ('DBCC SHOWCONTIG (''' + @.tablename + ''')
> WITH FAST, TABLERESULTS, ALL_INDEXES, NO_INFOMSGS');
> FETCH NEXT
> FROM tables
> INTO @.tablename;
> END;
> -- Close and deallocate the cursor.
> CLOSE tables;
> DEALLOCATE tables;
> -- Declare the cursor for the list of indexes to be defragged.
> DECLARE indexes CURSOR FOR
> SELECT ObjectName, ObjectId, IndexId, LogicalFrag
> FROM #fraglist
> WHERE LogicalFrag >= @.maxfrag
> AND INDEXPROPERTY (ObjectId, IndexName, 'IndexDepth') > 0;
> -- Open the cursor.
> OPEN indexes;
> -- Loop through the indexes.
> FETCH NEXT
> FROM indexes
> INTO @.tablename, @.objectid, @.indexid, @.frag;
> WHILE @.@.FETCH_STATUS = 0
> BEGIN;
> PRINT 'Executing DBCC INDEXDEFRAG (0, ' + RTRIM(@.tablename) + ',
> ' + RTRIM(@.indexid) + ') - fragmentation currently '
> + RTRIM(CONVERT(varchar(15),@.frag)) + '%';
> SELECT @.execstr = 'DBCC INDEXDEFRAG (0, ' + RTRIM(@.objectid) + ',
> ' + RTRIM(@.indexid) + ')';
> EXEC (@.execstr);
> FETCH NEXT
> FROM indexes
> INTO @.tablename, @.objectid, @.indexid, @.frag;
> END;
> -- Close and deallocate the cursor.
> CLOSE indexes;
> DEALLOCATE indexes;
> -- Delete the temporary table.
> DROP TABLE #fraglist;
> GO
|||And perhaps this "history of the index fragmentation" may show that some
indexes are fragmented too soon and maybe a new fill factor will be needed.
Hope this helps,
Ben Nevarez
Senior Database Administrator
AIG SunAmerica
"Andrew J. Kelly" wrote:
> The way to really solve this is to upgrade to 2005 as the equivalent of
> Showcontig does not block like in 2000. But the code you have now seems to
> be the fastest and lightest way to do this in 2000. I never recommend on
> larger dbs to reindex blindly so your approach is what I would recommend
> anyway. You can keep a history of the fragmentation by index and use that to
> determine which to defrag. Chances are if the usage is roughly the same week
> to week the indexes will be roughly the same in terms of fragmentation. So
> collect samples for a few weeks and base your rebuilding off of those
> numbers for x many future weeks.
> --
> Andrew J. Kelly SQL MVP
> Solid Quality Mentors
>
> "David Hay" <david.hay@.gmail.com> wrote in message
> news:368f7322-7f34-40c7-8e5e-cd32f1af00aa@.w56g2000hsf.googlegroups.com...
>
|||Yes good point Ben, I should have noted that as well.
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"Ben Nevarez" <BenNevarez@.discussions.microsoft.com> wrote in message
news:93BADA3C-FD11-414E-9044-21E3D63B622B@.microsoft.com...[vbcol=seagreen]
> And perhaps this "history of the index fragmentation" may show that some
> indexes are fragmented too soon and maybe a new fill factor will be
> needed.
> Hope this helps,
> Ben Nevarez
> Senior Database Administrator
> AIG SunAmerica
>
> "Andrew J. Kelly" wrote:
|||Thanks to all for the information. I did run a test in Prod, and it
ran much faster. (Much larger box). I saw a script that loaded a
history table and I'll work on building that.
Thanks again to all responding.
David
Showing posts with label havesome. Show all posts
Showing posts with label havesome. Show all posts
Monday, March 12, 2012
Index Maint DBCC assistance.
I am working with multiple servers, and numerous databases. I have
some large databases, some tables with 100's of millions of rows.
Some of the DB's are 24/7 so recreating the indexes is not really an
option. I am trying to come up with a "smart" index plan. I have
seen many examples of this, and I have one that does seem to work,
except that the DBCC showcontig seems to take forever! I am using a
variation on the following. The DBCC Showcontig seems to lock the
table, is this true?
Is there a faster way to get the DBCC fragmentation information?
Is it really worth the work identifying and defragmenting those
indexes that fall below a threshold or should I just defrag every
index?
Any other help on a "smart" maint plan for index maint would be
appreciated!
OS's : w2k Adv Server fully patched, 2003 Server Fully Patched
I am on SQL 2000, some at SP3a / SP4.
Sample of code below:
http://msdn2.microsoft.com/en-us/library/ms175008.aspx
-- Declare variables
SET NOCOUNT ON;
DECLARE @.tablename varchar(128);
DECLARE @.execstr varchar(255);
DECLARE @.objectid int;
DECLARE @.indexid int;
DECLARE @.frag decimal;
DECLARE @.maxfrag decimal;
-- Decide on the maximum fragmentation to allow for.
SELECT @.maxfrag = 30.0;
-- Declare a cursor.
DECLARE tables CURSOR FOR
SELECT TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE';
-- Create the table.
CREATE TABLE #fraglist (
ObjectName char(255),
ObjectId int,
IndexName char(255),
IndexId int,
Lvl int,
CountPages int,
CountRows int,
MinRecSize int,
MaxRecSize int,
AvgRecSize int,
ForRecCount int,
Extents int,
ExtentSwitches int,
AvgFreeBytes int,
AvgPageDensity int,
ScanDensity decimal,
BestCount int,
ActualCount int,
LogicalFrag decimal,
ExtentFrag decimal);
-- Open the cursor.
OPEN tables;
-- Loop through all the tables in the database.
FETCH NEXT
FROM tables
INTO @.tablename;
WHILE @.@.FETCH_STATUS = 0
BEGIN;
-- Do the showcontig of all indexes of the table
INSERT INTO #fraglist
EXEC ('DBCC SHOWCONTIG (''' + @.tablename + ''')
WITH FAST, TABLERESULTS, ALL_INDEXES, NO_INFOMSGS');
FETCH NEXT
FROM tables
INTO @.tablename;
END;
-- Close and deallocate the cursor.
CLOSE tables;
DEALLOCATE tables;
-- Declare the cursor for the list of indexes to be defragged.
DECLARE indexes CURSOR FOR
SELECT ObjectName, ObjectId, IndexId, LogicalFrag
FROM #fraglist
WHERE LogicalFrag >= @.maxfrag
AND INDEXPROPERTY (ObjectId, IndexName, 'IndexDepth') > 0;
-- Open the cursor.
OPEN indexes;
-- Loop through the indexes.
FETCH NEXT
FROM indexes
INTO @.tablename, @.objectid, @.indexid, @.frag;
WHILE @.@.FETCH_STATUS = 0
BEGIN;
PRINT 'Executing DBCC INDEXDEFRAG (0, ' + RTRIM(@.tablename) + ',
' + RTRIM(@.indexid) + ') - fragmentation currently '
+ RTRIM(CONVERT(varchar(15),@.frag)) + '%';
SELECT @.execstr = 'DBCC INDEXDEFRAG (0, ' + RTRIM(@.objectid) + ',
' + RTRIM(@.indexid) + ')';
EXEC (@.execstr);
FETCH NEXT
FROM indexes
INTO @.tablename, @.objectid, @.indexid, @.frag;
END;
-- Close and deallocate the cursor.
CLOSE indexes;
DEALLOCATE indexes;
-- Delete the temporary table.
DROP TABLE #fraglist;
GODo you have a test environment where you can load a copy of prod? If so,
generate the defrag script there but execute it against prod. This way, you
won't be locking up the table in prod with a share lock by running DBCC
SHOWCONTIG.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
"David Hay" <david.hay@.gmail.com> wrote in message
news:368f7322-7f34-40c7-8e5e-cd32f1af00aa@.w56g2000hsf.googlegroups.com...
I am working with multiple servers, and numerous databases. I have
some large databases, some tables with 100's of millions of rows.
Some of the DB's are 24/7 so recreating the indexes is not really an
option. I am trying to come up with a "smart" index plan. I have
seen many examples of this, and I have one that does seem to work,
except that the DBCC showcontig seems to take forever! I am using a
variation on the following. The DBCC Showcontig seems to lock the
table, is this true?
Is there a faster way to get the DBCC fragmentation information?
Is it really worth the work identifying and defragmenting those
indexes that fall below a threshold or should I just defrag every
index?
Any other help on a "smart" maint plan for index maint would be
appreciated!
OS's : w2k Adv Server fully patched, 2003 Server Fully Patched
I am on SQL 2000, some at SP3a / SP4.
Sample of code below:
http://msdn2.microsoft.com/en-us/library/ms175008.aspx
-- Declare variables
SET NOCOUNT ON;
DECLARE @.tablename varchar(128);
DECLARE @.execstr varchar(255);
DECLARE @.objectid int;
DECLARE @.indexid int;
DECLARE @.frag decimal;
DECLARE @.maxfrag decimal;
-- Decide on the maximum fragmentation to allow for.
SELECT @.maxfrag = 30.0;
-- Declare a cursor.
DECLARE tables CURSOR FOR
SELECT TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE';
-- Create the table.
CREATE TABLE #fraglist (
ObjectName char(255),
ObjectId int,
IndexName char(255),
IndexId int,
Lvl int,
CountPages int,
CountRows int,
MinRecSize int,
MaxRecSize int,
AvgRecSize int,
ForRecCount int,
Extents int,
ExtentSwitches int,
AvgFreeBytes int,
AvgPageDensity int,
ScanDensity decimal,
BestCount int,
ActualCount int,
LogicalFrag decimal,
ExtentFrag decimal);
-- Open the cursor.
OPEN tables;
-- Loop through all the tables in the database.
FETCH NEXT
FROM tables
INTO @.tablename;
WHILE @.@.FETCH_STATUS = 0
BEGIN;
-- Do the showcontig of all indexes of the table
INSERT INTO #fraglist
EXEC ('DBCC SHOWCONTIG (''' + @.tablename + ''')
WITH FAST, TABLERESULTS, ALL_INDEXES, NO_INFOMSGS');
FETCH NEXT
FROM tables
INTO @.tablename;
END;
-- Close and deallocate the cursor.
CLOSE tables;
DEALLOCATE tables;
-- Declare the cursor for the list of indexes to be defragged.
DECLARE indexes CURSOR FOR
SELECT ObjectName, ObjectId, IndexId, LogicalFrag
FROM #fraglist
WHERE LogicalFrag >= @.maxfrag
AND INDEXPROPERTY (ObjectId, IndexName, 'IndexDepth') > 0;
-- Open the cursor.
OPEN indexes;
-- Loop through the indexes.
FETCH NEXT
FROM indexes
INTO @.tablename, @.objectid, @.indexid, @.frag;
WHILE @.@.FETCH_STATUS = 0
BEGIN;
PRINT 'Executing DBCC INDEXDEFRAG (0, ' + RTRIM(@.tablename) + ',
' + RTRIM(@.indexid) + ') - fragmentation currently '
+ RTRIM(CONVERT(varchar(15),@.frag)) + '%';
SELECT @.execstr = 'DBCC INDEXDEFRAG (0, ' + RTRIM(@.objectid) + ',
' + RTRIM(@.indexid) + ')';
EXEC (@.execstr);
FETCH NEXT
FROM indexes
INTO @.tablename, @.objectid, @.indexid, @.frag;
END;
-- Close and deallocate the cursor.
CLOSE indexes;
DEALLOCATE indexes;
-- Delete the temporary table.
DROP TABLE #fraglist;
GO|||The way to really solve this is to upgrade to 2005 as the equivalent of
Showcontig does not block like in 2000. But the code you have now seems to
be the fastest and lightest way to do this in 2000. I never recommend on
larger dbs to reindex blindly so your approach is what I would recommend
anyway. You can keep a history of the fragmentation by index and use that to
determine which to defrag. Chances are if the usage is roughly the same week
to week the indexes will be roughly the same in terms of fragmentation. So
collect samples for a few weeks and base your rebuilding off of those
numbers for x many future weeks.
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"David Hay" <david.hay@.gmail.com> wrote in message
news:368f7322-7f34-40c7-8e5e-cd32f1af00aa@.w56g2000hsf.googlegroups.com...
>I am working with multiple servers, and numerous databases. I have
> some large databases, some tables with 100's of millions of rows.
> Some of the DB's are 24/7 so recreating the indexes is not really an
> option. I am trying to come up with a "smart" index plan. I have
> seen many examples of this, and I have one that does seem to work,
> except that the DBCC showcontig seems to take forever! I am using a
> variation on the following. The DBCC Showcontig seems to lock the
> table, is this true?
> Is there a faster way to get the DBCC fragmentation information?
> Is it really worth the work identifying and defragmenting those
> indexes that fall below a threshold or should I just defrag every
> index?
> Any other help on a "smart" maint plan for index maint would be
> appreciated!
> OS's : w2k Adv Server fully patched, 2003 Server Fully Patched
> I am on SQL 2000, some at SP3a / SP4.
> Sample of code below:
> http://msdn2.microsoft.com/en-us/library/ms175008.aspx
> -- Declare variables
> SET NOCOUNT ON;
> DECLARE @.tablename varchar(128);
> DECLARE @.execstr varchar(255);
> DECLARE @.objectid int;
> DECLARE @.indexid int;
> DECLARE @.frag decimal;
> DECLARE @.maxfrag decimal;
> -- Decide on the maximum fragmentation to allow for.
> SELECT @.maxfrag = 30.0;
> -- Declare a cursor.
> DECLARE tables CURSOR FOR
> SELECT TABLE_NAME
> FROM INFORMATION_SCHEMA.TABLES
> WHERE TABLE_TYPE = 'BASE TABLE';
> -- Create the table.
> CREATE TABLE #fraglist (
> ObjectName char(255),
> ObjectId int,
> IndexName char(255),
> IndexId int,
> Lvl int,
> CountPages int,
> CountRows int,
> MinRecSize int,
> MaxRecSize int,
> AvgRecSize int,
> ForRecCount int,
> Extents int,
> ExtentSwitches int,
> AvgFreeBytes int,
> AvgPageDensity int,
> ScanDensity decimal,
> BestCount int,
> ActualCount int,
> LogicalFrag decimal,
> ExtentFrag decimal);
> -- Open the cursor.
> OPEN tables;
> -- Loop through all the tables in the database.
> FETCH NEXT
> FROM tables
> INTO @.tablename;
> WHILE @.@.FETCH_STATUS = 0
> BEGIN;
> -- Do the showcontig of all indexes of the table
> INSERT INTO #fraglist
> EXEC ('DBCC SHOWCONTIG (''' + @.tablename + ''')
> WITH FAST, TABLERESULTS, ALL_INDEXES, NO_INFOMSGS');
> FETCH NEXT
> FROM tables
> INTO @.tablename;
> END;
> -- Close and deallocate the cursor.
> CLOSE tables;
> DEALLOCATE tables;
> -- Declare the cursor for the list of indexes to be defragged.
> DECLARE indexes CURSOR FOR
> SELECT ObjectName, ObjectId, IndexId, LogicalFrag
> FROM #fraglist
> WHERE LogicalFrag >= @.maxfrag
> AND INDEXPROPERTY (ObjectId, IndexName, 'IndexDepth') > 0;
> -- Open the cursor.
> OPEN indexes;
> -- Loop through the indexes.
> FETCH NEXT
> FROM indexes
> INTO @.tablename, @.objectid, @.indexid, @.frag;
> WHILE @.@.FETCH_STATUS = 0
> BEGIN;
> PRINT 'Executing DBCC INDEXDEFRAG (0, ' + RTRIM(@.tablename) + ',
> ' + RTRIM(@.indexid) + ') - fragmentation currently '
> + RTRIM(CONVERT(varchar(15),@.frag)) + '%';
> SELECT @.execstr = 'DBCC INDEXDEFRAG (0, ' + RTRIM(@.objectid) + ',
> ' + RTRIM(@.indexid) + ')';
> EXEC (@.execstr);
> FETCH NEXT
> FROM indexes
> INTO @.tablename, @.objectid, @.indexid, @.frag;
> END;
> -- Close and deallocate the cursor.
> CLOSE indexes;
> DEALLOCATE indexes;
> -- Delete the temporary table.
> DROP TABLE #fraglist;
> GO|||And perhaps this "history of the index fragmentation" may show that some
indexes are fragmented too soon and maybe a new fill factor will be needed.
Hope this helps,
Ben Nevarez
Senior Database Administrator
AIG SunAmerica
"Andrew J. Kelly" wrote:
> The way to really solve this is to upgrade to 2005 as the equivalent of
> Showcontig does not block like in 2000. But the code you have now seems to
> be the fastest and lightest way to do this in 2000. I never recommend on
> larger dbs to reindex blindly so your approach is what I would recommend
> anyway. You can keep a history of the fragmentation by index and use that
to
> determine which to defrag. Chances are if the usage is roughly the same we
ek
> to week the indexes will be roughly the same in terms of fragmentation. So
> collect samples for a few weeks and base your rebuilding off of those
> numbers for x many future weeks.
> --
> Andrew J. Kelly SQL MVP
> Solid Quality Mentors
>
> "David Hay" <david.hay@.gmail.com> wrote in message
> news:368f7322-7f34-40c7-8e5e-cd32f1af00aa@.w56g2000hsf.googlegroups.com...
>|||Yes good point Ben, I should have noted that as well.
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"Ben Nevarez" <BenNevarez@.discussions.microsoft.com> wrote in message
news:93BADA3C-FD11-414E-9044-21E3D63B622B@.microsoft.com...[vbcol=seagreen]
> And perhaps this "history of the index fragmentation" may show that some
> indexes are fragmented too soon and maybe a new fill factor will be
> needed.
> Hope this helps,
> Ben Nevarez
> Senior Database Administrator
> AIG SunAmerica
>
> "Andrew J. Kelly" wrote:
>|||Thanks to all for the information. I did run a test in Prod, and it
ran much faster. (Much larger box). I saw a script that loaded a
history table and I'll work on building that.
Thanks again to all responding.
David
some large databases, some tables with 100's of millions of rows.
Some of the DB's are 24/7 so recreating the indexes is not really an
option. I am trying to come up with a "smart" index plan. I have
seen many examples of this, and I have one that does seem to work,
except that the DBCC showcontig seems to take forever! I am using a
variation on the following. The DBCC Showcontig seems to lock the
table, is this true?
Is there a faster way to get the DBCC fragmentation information?
Is it really worth the work identifying and defragmenting those
indexes that fall below a threshold or should I just defrag every
index?
Any other help on a "smart" maint plan for index maint would be
appreciated!
OS's : w2k Adv Server fully patched, 2003 Server Fully Patched
I am on SQL 2000, some at SP3a / SP4.
Sample of code below:
http://msdn2.microsoft.com/en-us/library/ms175008.aspx
-- Declare variables
SET NOCOUNT ON;
DECLARE @.tablename varchar(128);
DECLARE @.execstr varchar(255);
DECLARE @.objectid int;
DECLARE @.indexid int;
DECLARE @.frag decimal;
DECLARE @.maxfrag decimal;
-- Decide on the maximum fragmentation to allow for.
SELECT @.maxfrag = 30.0;
-- Declare a cursor.
DECLARE tables CURSOR FOR
SELECT TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE';
-- Create the table.
CREATE TABLE #fraglist (
ObjectName char(255),
ObjectId int,
IndexName char(255),
IndexId int,
Lvl int,
CountPages int,
CountRows int,
MinRecSize int,
MaxRecSize int,
AvgRecSize int,
ForRecCount int,
Extents int,
ExtentSwitches int,
AvgFreeBytes int,
AvgPageDensity int,
ScanDensity decimal,
BestCount int,
ActualCount int,
LogicalFrag decimal,
ExtentFrag decimal);
-- Open the cursor.
OPEN tables;
-- Loop through all the tables in the database.
FETCH NEXT
FROM tables
INTO @.tablename;
WHILE @.@.FETCH_STATUS = 0
BEGIN;
-- Do the showcontig of all indexes of the table
INSERT INTO #fraglist
EXEC ('DBCC SHOWCONTIG (''' + @.tablename + ''')
WITH FAST, TABLERESULTS, ALL_INDEXES, NO_INFOMSGS');
FETCH NEXT
FROM tables
INTO @.tablename;
END;
-- Close and deallocate the cursor.
CLOSE tables;
DEALLOCATE tables;
-- Declare the cursor for the list of indexes to be defragged.
DECLARE indexes CURSOR FOR
SELECT ObjectName, ObjectId, IndexId, LogicalFrag
FROM #fraglist
WHERE LogicalFrag >= @.maxfrag
AND INDEXPROPERTY (ObjectId, IndexName, 'IndexDepth') > 0;
-- Open the cursor.
OPEN indexes;
-- Loop through the indexes.
FETCH NEXT
FROM indexes
INTO @.tablename, @.objectid, @.indexid, @.frag;
WHILE @.@.FETCH_STATUS = 0
BEGIN;
PRINT 'Executing DBCC INDEXDEFRAG (0, ' + RTRIM(@.tablename) + ',
' + RTRIM(@.indexid) + ') - fragmentation currently '
+ RTRIM(CONVERT(varchar(15),@.frag)) + '%';
SELECT @.execstr = 'DBCC INDEXDEFRAG (0, ' + RTRIM(@.objectid) + ',
' + RTRIM(@.indexid) + ')';
EXEC (@.execstr);
FETCH NEXT
FROM indexes
INTO @.tablename, @.objectid, @.indexid, @.frag;
END;
-- Close and deallocate the cursor.
CLOSE indexes;
DEALLOCATE indexes;
-- Delete the temporary table.
DROP TABLE #fraglist;
GODo you have a test environment where you can load a copy of prod? If so,
generate the defrag script there but execute it against prod. This way, you
won't be locking up the table in prod with a share lock by running DBCC
SHOWCONTIG.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
"David Hay" <david.hay@.gmail.com> wrote in message
news:368f7322-7f34-40c7-8e5e-cd32f1af00aa@.w56g2000hsf.googlegroups.com...
I am working with multiple servers, and numerous databases. I have
some large databases, some tables with 100's of millions of rows.
Some of the DB's are 24/7 so recreating the indexes is not really an
option. I am trying to come up with a "smart" index plan. I have
seen many examples of this, and I have one that does seem to work,
except that the DBCC showcontig seems to take forever! I am using a
variation on the following. The DBCC Showcontig seems to lock the
table, is this true?
Is there a faster way to get the DBCC fragmentation information?
Is it really worth the work identifying and defragmenting those
indexes that fall below a threshold or should I just defrag every
index?
Any other help on a "smart" maint plan for index maint would be
appreciated!
OS's : w2k Adv Server fully patched, 2003 Server Fully Patched
I am on SQL 2000, some at SP3a / SP4.
Sample of code below:
http://msdn2.microsoft.com/en-us/library/ms175008.aspx
-- Declare variables
SET NOCOUNT ON;
DECLARE @.tablename varchar(128);
DECLARE @.execstr varchar(255);
DECLARE @.objectid int;
DECLARE @.indexid int;
DECLARE @.frag decimal;
DECLARE @.maxfrag decimal;
-- Decide on the maximum fragmentation to allow for.
SELECT @.maxfrag = 30.0;
-- Declare a cursor.
DECLARE tables CURSOR FOR
SELECT TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE';
-- Create the table.
CREATE TABLE #fraglist (
ObjectName char(255),
ObjectId int,
IndexName char(255),
IndexId int,
Lvl int,
CountPages int,
CountRows int,
MinRecSize int,
MaxRecSize int,
AvgRecSize int,
ForRecCount int,
Extents int,
ExtentSwitches int,
AvgFreeBytes int,
AvgPageDensity int,
ScanDensity decimal,
BestCount int,
ActualCount int,
LogicalFrag decimal,
ExtentFrag decimal);
-- Open the cursor.
OPEN tables;
-- Loop through all the tables in the database.
FETCH NEXT
FROM tables
INTO @.tablename;
WHILE @.@.FETCH_STATUS = 0
BEGIN;
-- Do the showcontig of all indexes of the table
INSERT INTO #fraglist
EXEC ('DBCC SHOWCONTIG (''' + @.tablename + ''')
WITH FAST, TABLERESULTS, ALL_INDEXES, NO_INFOMSGS');
FETCH NEXT
FROM tables
INTO @.tablename;
END;
-- Close and deallocate the cursor.
CLOSE tables;
DEALLOCATE tables;
-- Declare the cursor for the list of indexes to be defragged.
DECLARE indexes CURSOR FOR
SELECT ObjectName, ObjectId, IndexId, LogicalFrag
FROM #fraglist
WHERE LogicalFrag >= @.maxfrag
AND INDEXPROPERTY (ObjectId, IndexName, 'IndexDepth') > 0;
-- Open the cursor.
OPEN indexes;
-- Loop through the indexes.
FETCH NEXT
FROM indexes
INTO @.tablename, @.objectid, @.indexid, @.frag;
WHILE @.@.FETCH_STATUS = 0
BEGIN;
PRINT 'Executing DBCC INDEXDEFRAG (0, ' + RTRIM(@.tablename) + ',
' + RTRIM(@.indexid) + ') - fragmentation currently '
+ RTRIM(CONVERT(varchar(15),@.frag)) + '%';
SELECT @.execstr = 'DBCC INDEXDEFRAG (0, ' + RTRIM(@.objectid) + ',
' + RTRIM(@.indexid) + ')';
EXEC (@.execstr);
FETCH NEXT
FROM indexes
INTO @.tablename, @.objectid, @.indexid, @.frag;
END;
-- Close and deallocate the cursor.
CLOSE indexes;
DEALLOCATE indexes;
-- Delete the temporary table.
DROP TABLE #fraglist;
GO|||The way to really solve this is to upgrade to 2005 as the equivalent of
Showcontig does not block like in 2000. But the code you have now seems to
be the fastest and lightest way to do this in 2000. I never recommend on
larger dbs to reindex blindly so your approach is what I would recommend
anyway. You can keep a history of the fragmentation by index and use that to
determine which to defrag. Chances are if the usage is roughly the same week
to week the indexes will be roughly the same in terms of fragmentation. So
collect samples for a few weeks and base your rebuilding off of those
numbers for x many future weeks.
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"David Hay" <david.hay@.gmail.com> wrote in message
news:368f7322-7f34-40c7-8e5e-cd32f1af00aa@.w56g2000hsf.googlegroups.com...
>I am working with multiple servers, and numerous databases. I have
> some large databases, some tables with 100's of millions of rows.
> Some of the DB's are 24/7 so recreating the indexes is not really an
> option. I am trying to come up with a "smart" index plan. I have
> seen many examples of this, and I have one that does seem to work,
> except that the DBCC showcontig seems to take forever! I am using a
> variation on the following. The DBCC Showcontig seems to lock the
> table, is this true?
> Is there a faster way to get the DBCC fragmentation information?
> Is it really worth the work identifying and defragmenting those
> indexes that fall below a threshold or should I just defrag every
> index?
> Any other help on a "smart" maint plan for index maint would be
> appreciated!
> OS's : w2k Adv Server fully patched, 2003 Server Fully Patched
> I am on SQL 2000, some at SP3a / SP4.
> Sample of code below:
> http://msdn2.microsoft.com/en-us/library/ms175008.aspx
> -- Declare variables
> SET NOCOUNT ON;
> DECLARE @.tablename varchar(128);
> DECLARE @.execstr varchar(255);
> DECLARE @.objectid int;
> DECLARE @.indexid int;
> DECLARE @.frag decimal;
> DECLARE @.maxfrag decimal;
> -- Decide on the maximum fragmentation to allow for.
> SELECT @.maxfrag = 30.0;
> -- Declare a cursor.
> DECLARE tables CURSOR FOR
> SELECT TABLE_NAME
> FROM INFORMATION_SCHEMA.TABLES
> WHERE TABLE_TYPE = 'BASE TABLE';
> -- Create the table.
> CREATE TABLE #fraglist (
> ObjectName char(255),
> ObjectId int,
> IndexName char(255),
> IndexId int,
> Lvl int,
> CountPages int,
> CountRows int,
> MinRecSize int,
> MaxRecSize int,
> AvgRecSize int,
> ForRecCount int,
> Extents int,
> ExtentSwitches int,
> AvgFreeBytes int,
> AvgPageDensity int,
> ScanDensity decimal,
> BestCount int,
> ActualCount int,
> LogicalFrag decimal,
> ExtentFrag decimal);
> -- Open the cursor.
> OPEN tables;
> -- Loop through all the tables in the database.
> FETCH NEXT
> FROM tables
> INTO @.tablename;
> WHILE @.@.FETCH_STATUS = 0
> BEGIN;
> -- Do the showcontig of all indexes of the table
> INSERT INTO #fraglist
> EXEC ('DBCC SHOWCONTIG (''' + @.tablename + ''')
> WITH FAST, TABLERESULTS, ALL_INDEXES, NO_INFOMSGS');
> FETCH NEXT
> FROM tables
> INTO @.tablename;
> END;
> -- Close and deallocate the cursor.
> CLOSE tables;
> DEALLOCATE tables;
> -- Declare the cursor for the list of indexes to be defragged.
> DECLARE indexes CURSOR FOR
> SELECT ObjectName, ObjectId, IndexId, LogicalFrag
> FROM #fraglist
> WHERE LogicalFrag >= @.maxfrag
> AND INDEXPROPERTY (ObjectId, IndexName, 'IndexDepth') > 0;
> -- Open the cursor.
> OPEN indexes;
> -- Loop through the indexes.
> FETCH NEXT
> FROM indexes
> INTO @.tablename, @.objectid, @.indexid, @.frag;
> WHILE @.@.FETCH_STATUS = 0
> BEGIN;
> PRINT 'Executing DBCC INDEXDEFRAG (0, ' + RTRIM(@.tablename) + ',
> ' + RTRIM(@.indexid) + ') - fragmentation currently '
> + RTRIM(CONVERT(varchar(15),@.frag)) + '%';
> SELECT @.execstr = 'DBCC INDEXDEFRAG (0, ' + RTRIM(@.objectid) + ',
> ' + RTRIM(@.indexid) + ')';
> EXEC (@.execstr);
> FETCH NEXT
> FROM indexes
> INTO @.tablename, @.objectid, @.indexid, @.frag;
> END;
> -- Close and deallocate the cursor.
> CLOSE indexes;
> DEALLOCATE indexes;
> -- Delete the temporary table.
> DROP TABLE #fraglist;
> GO|||And perhaps this "history of the index fragmentation" may show that some
indexes are fragmented too soon and maybe a new fill factor will be needed.
Hope this helps,
Ben Nevarez
Senior Database Administrator
AIG SunAmerica
"Andrew J. Kelly" wrote:
> The way to really solve this is to upgrade to 2005 as the equivalent of
> Showcontig does not block like in 2000. But the code you have now seems to
> be the fastest and lightest way to do this in 2000. I never recommend on
> larger dbs to reindex blindly so your approach is what I would recommend
> anyway. You can keep a history of the fragmentation by index and use that
to
> determine which to defrag. Chances are if the usage is roughly the same we
ek
> to week the indexes will be roughly the same in terms of fragmentation. So
> collect samples for a few weeks and base your rebuilding off of those
> numbers for x many future weeks.
> --
> Andrew J. Kelly SQL MVP
> Solid Quality Mentors
>
> "David Hay" <david.hay@.gmail.com> wrote in message
> news:368f7322-7f34-40c7-8e5e-cd32f1af00aa@.w56g2000hsf.googlegroups.com...
>|||Yes good point Ben, I should have noted that as well.
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"Ben Nevarez" <BenNevarez@.discussions.microsoft.com> wrote in message
news:93BADA3C-FD11-414E-9044-21E3D63B622B@.microsoft.com...[vbcol=seagreen]
> And perhaps this "history of the index fragmentation" may show that some
> indexes are fragmented too soon and maybe a new fill factor will be
> needed.
> Hope this helps,
> Ben Nevarez
> Senior Database Administrator
> AIG SunAmerica
>
> "Andrew J. Kelly" wrote:
>|||Thanks to all for the information. I did run a test in Prod, and it
ran much faster. (Much larger box). I saw a script that loaded a
history table and I'll work on building that.
Thanks again to all responding.
David
Wednesday, March 7, 2012
Index defrag and Data defrag
Hi,
I have recently started delving into fragmentation in sql server. I have
some basic/conceptual level queries regarding the same -
1) Is index fragmentation and data fragmentation same/different in case of -
- table with clustered index
- table with no clustered index
2) Is there a separate process for data defragmentation? If yes, then to
which type of table does it apply to - with or without clustered index or any
other type?
3) Will running DBCC DBREINDEX for any kind of index (clustered and
non-clustered) defrag the data pages as well?
Please excuse me if any question seems completely stupid.
Thank you.
Regards,
Salil.
Salil
Start looking at DBCC SHOWCONTIG,DBCC INDEXDEFRAG in the BOL.
"Salil" <Salil@.discussions.microsoft.com> wrote in message
news:5C5D1A94-AD5A-4CF4-9897-01E8F847E5FE@.microsoft.com...
> Hi,
> I have recently started delving into fragmentation in sql server. I have
> some basic/conceptual level queries regarding the same -
> 1) Is index fragmentation and data fragmentation same/different in case
of -
> - table with clustered index
> - table with no clustered index
> 2) Is there a separate process for data defragmentation? If yes, then to
> which type of table does it apply to - with or without clustered index or
any
> other type?
> 3) Will running DBCC DBREINDEX for any kind of index (clustered and
> non-clustered) defrag the data pages as well?
> Please excuse me if any question seems completely stupid.
> Thank you.
> Regards,
> Salil.
>
|||Hi Uri,
I have asked these queries only after not finding anything related to data
fragmentation in BOL. I've already gone through showcontig, indexdefrag and
dbreindex help provided in BOL.
Everything seems to be talking only about index defragmentation.
Salil.
"Uri Dimant" wrote:
> Salil
> Start looking at DBCC SHOWCONTIG,DBCC INDEXDEFRAG in the BOL.
> "Salil" <Salil@.discussions.microsoft.com> wrote in message
> news:5C5D1A94-AD5A-4CF4-9897-01E8F847E5FE@.microsoft.com...
> of -
> any
>
>
|||Salil
http://www.sql-server-performance.co...gmentation.asp
http://www.sql-server-performance.co...showcontig.asp
"Salil" <Salil@.discussions.microsoft.com> wrote in message
news:39B22717-9962-4252-ADD9-F44188DC99E2@.microsoft.com...
> Hi Uri,
> I have asked these queries only after not finding anything related to data
> fragmentation in BOL. I've already gone through showcontig, indexdefrag
and[vbcol=seagreen]
> dbreindex help provided in BOL.
> Everything seems to be talking only about index defragmentation.
> Salil.
> "Uri Dimant" wrote:
have[vbcol=seagreen]
case[vbcol=seagreen]
to[vbcol=seagreen]
or[vbcol=seagreen]
|||Thank you Uri.
I have already gone through these links before making this post.
My confusion still remains.
Salil.
"Uri Dimant" wrote:
> Salil
> http://www.sql-server-performance.co...gmentation.asp
> --
> http://www.sql-server-performance.co...showcontig.asp
>
>
> "Salil" <Salil@.discussions.microsoft.com> wrote in message
> news:39B22717-9962-4252-ADD9-F44188DC99E2@.microsoft.com...
> and
> have
> case
> to
> or
>
>
|||See inline
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Salil" <Salil@.discussions.microsoft.com> wrote in message
news:5C5D1A94-AD5A-4CF4-9897-01E8F847E5FE@.microsoft.com...
> Hi,
> I have recently started delving into fragmentation in sql server. I have
> some basic/conceptual level queries regarding the same -
> 1) Is index fragmentation and data fragmentation same/different in case
of -
> - table with clustered index
> - table with no clustered index
>
Index fragmentation is index fragmentation regardless of the storage of the
data in the table.
If the table has a clustered index, it falls under normal index
fragmentation... However be aware that shrinking the database fragments the
indexes, the worst case being the clustered index.
Heaps ( tables with no clustered index) contain unordered data, so logical
fragmentation is not a problem.
When one speaks of fragmentation there is internal fragmentation ( how full
each page is.) and external fragmentation (how closely the liniked list
(index) order is to the physical order of the pages on the disk)...
The OTHER kind of fragmentation is the kind that Diskkeeper fixes, moving
all of the parts of a physical file into a single contiguous disk segment...
You can ( and most folks do) have a situation where this kind of
fragmentation does not exist, yet within the file, the rows for a table or
index are all around in the physical file ( the previous kind of
fragmentation above.)
Index defrag and dbcc dbreindex fix the first kind of fragmentation NOT the
same thing that diskkeeper works on.
> 2) Is there a separate process for data defragmentation? If yes, then to
> which type of table does it apply to - with or without clustered index or
any
> other type?
Only indexdefrag and dbreindex, for clustered indexes. Some folks create
then drop a clustered index on a heap to clean it up...(This works also.)
> 3) Will running DBCC DBREINDEX for any kind of index (clustered and
> non-clustered) defrag the data pages as well?
The data pages ARE the leaf level of a clustered index, so defragging a
clustered index fixes the data pages. Since there is no ordering on a heap
table, creating then dropping a clustered index on a heap, fixes up the data
pages there as well..
> Please excuse me if any question seems completely stupid.
None of your questions are stupid... It is good that you are smart enough to
ask the questions, and know that there are issues you need to understand.
and this is the perfect place to do just that...
> Thank you.
> Regards,
> Salil.
>
|||Also take a look at this:
http://www.microsoft.com/technet/pro.../ss2kidbp.mspx
Andrew J. Kelly SQL MVP
"Salil" <Salil@.discussions.microsoft.com> wrote in message
news:5C5D1A94-AD5A-4CF4-9897-01E8F847E5FE@.microsoft.com...
> Hi,
> I have recently started delving into fragmentation in sql server. I have
> some basic/conceptual level queries regarding the same -
> 1) Is index fragmentation and data fragmentation same/different in case
of -
> - table with clustered index
> - table with no clustered index
> 2) Is there a separate process for data defragmentation? If yes, then to
> which type of table does it apply to - with or without clustered index or
any
> other type?
> 3) Will running DBCC DBREINDEX for any kind of index (clustered and
> non-clustered) defrag the data pages as well?
> Please excuse me if any question seems completely stupid.
> Thank you.
> Regards,
> Salil.
>
|||Thank you Wayne. The replies helped a lot !
I think I can conclude that running DBREINDEX on clustered indexes with
logical OR extent fragmentation will reduce both kinds of fragmentation.
One last query for now -
Since heaps do not have clustered indexes, will there be any decrease in
logical or extent fragmentation by simply running DBREINDEX WITHOUT creating
and dropping a clustered index ?
Salil.
"Wayne Snyder" wrote:
> See inline
>
> --
> Wayne Snyder, MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> www.mariner-usa.com
> (Please respond only to the newsgroups.)
> I support the Professional Association of SQL Server (PASS) and it's
> community of SQL Server professionals.
> www.sqlpass.org
> "Salil" <Salil@.discussions.microsoft.com> wrote in message
> news:5C5D1A94-AD5A-4CF4-9897-01E8F847E5FE@.microsoft.com...
> of -
> Index fragmentation is index fragmentation regardless of the storage of the
> data in the table.
> If the table has a clustered index, it falls under normal index
> fragmentation... However be aware that shrinking the database fragments the
> indexes, the worst case being the clustered index.
> Heaps ( tables with no clustered index) contain unordered data, so logical
> fragmentation is not a problem.
> When one speaks of fragmentation there is internal fragmentation ( how full
> each page is.) and external fragmentation (how closely the liniked list
> (index) order is to the physical order of the pages on the disk)...
> The OTHER kind of fragmentation is the kind that Diskkeeper fixes, moving
> all of the parts of a physical file into a single contiguous disk segment...
> You can ( and most folks do) have a situation where this kind of
> fragmentation does not exist, yet within the file, the rows for a table or
> index are all around in the physical file ( the previous kind of
> fragmentation above.)
> Index defrag and dbcc dbreindex fix the first kind of fragmentation NOT the
> same thing that diskkeeper works on.
>
> any
> Only indexdefrag and dbreindex, for clustered indexes. Some folks create
> then drop a clustered index on a heap to clean it up...(This works also.)
> The data pages ARE the leaf level of a clustered index, so defragging a
> clustered index fixes the data pages. Since there is no ordering on a heap
> table, creating then dropping a clustered index on a heap, fixes up the data
> pages there as well..
>
> None of your questions are stupid... It is good that you are smart enough to
> ask the questions, and know that there are issues you need to understand.
> and this is the perfect place to do just that...
>
>
|||Thank you Andrew. I have gone through this link earlier.
In fact I had a query regarding this too -
In this article, it is mentioned that logical fragmentation holds no meaning
for heaps (IND ID = 0). Wayne also mentioned the same thing. And as per this
article, Wayne and other articles it seems that the only way to defrag these
would be to create and then drop a clustered index on it.
As per BOL, IND ID = 1 is a clustered index. So I'll obviously have to run
the DBREINDEX process for these indexes.
Will running a DBREINDEX against IND ID = 2,3,4...(not in 0, 255) (i.e.
non-clustered indexes) help in reducing fragmentation?
Salil.
"Andrew J. Kelly" wrote:
> Also take a look at this:
> http://www.microsoft.com/technet/pro.../ss2kidbp.mspx
> --
> Andrew J. Kelly SQL MVP
>
> "Salil" <Salil@.discussions.microsoft.com> wrote in message
> news:5C5D1A94-AD5A-4CF4-9897-01E8F847E5FE@.microsoft.com...
> of -
> any
>
>
|||> Since heaps do not have clustered indexes, will there be any decrease in
> logical or extent fragmentation by simply running DBREINDEX WITHOUT creating
> and dropping a clustered index ?
No, If a table doesn't have a clustered index, the rows are not stored in any particular physical order. This
is why we call such tables "heap".
Defragging or rebuilding non-clustered indexes for such tables does not move or touch the actual data pages. A
non-clustered index are only "pointers" to the data pages, after all...
Also, you need to think about what you mean when you consider data pages fragmented for a heap table. The rows
are not stored in any physical order. So, how can they become un-ordered? Sure, you can have free space on
pages, etc, but they cannot become un-ordered.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Salil" <Salil@.discussions.microsoft.com> wrote in message
news:1D9C65FE-CF51-4AD5-AC6E-25CCFC2C564B@.microsoft.com...[vbcol=seagreen]
> Thank you Wayne. The replies helped a lot !
> I think I can conclude that running DBREINDEX on clustered indexes with
> logical OR extent fragmentation will reduce both kinds of fragmentation.
> One last query for now -
> Since heaps do not have clustered indexes, will there be any decrease in
> logical or extent fragmentation by simply running DBREINDEX WITHOUT creating
> and dropping a clustered index ?
> Salil.
> "Wayne Snyder" wrote:
I have recently started delving into fragmentation in sql server. I have
some basic/conceptual level queries regarding the same -
1) Is index fragmentation and data fragmentation same/different in case of -
- table with clustered index
- table with no clustered index
2) Is there a separate process for data defragmentation? If yes, then to
which type of table does it apply to - with or without clustered index or any
other type?
3) Will running DBCC DBREINDEX for any kind of index (clustered and
non-clustered) defrag the data pages as well?
Please excuse me if any question seems completely stupid.
Thank you.
Regards,
Salil.
Salil
Start looking at DBCC SHOWCONTIG,DBCC INDEXDEFRAG in the BOL.
"Salil" <Salil@.discussions.microsoft.com> wrote in message
news:5C5D1A94-AD5A-4CF4-9897-01E8F847E5FE@.microsoft.com...
> Hi,
> I have recently started delving into fragmentation in sql server. I have
> some basic/conceptual level queries regarding the same -
> 1) Is index fragmentation and data fragmentation same/different in case
of -
> - table with clustered index
> - table with no clustered index
> 2) Is there a separate process for data defragmentation? If yes, then to
> which type of table does it apply to - with or without clustered index or
any
> other type?
> 3) Will running DBCC DBREINDEX for any kind of index (clustered and
> non-clustered) defrag the data pages as well?
> Please excuse me if any question seems completely stupid.
> Thank you.
> Regards,
> Salil.
>
|||Hi Uri,
I have asked these queries only after not finding anything related to data
fragmentation in BOL. I've already gone through showcontig, indexdefrag and
dbreindex help provided in BOL.
Everything seems to be talking only about index defragmentation.
Salil.
"Uri Dimant" wrote:
> Salil
> Start looking at DBCC SHOWCONTIG,DBCC INDEXDEFRAG in the BOL.
> "Salil" <Salil@.discussions.microsoft.com> wrote in message
> news:5C5D1A94-AD5A-4CF4-9897-01E8F847E5FE@.microsoft.com...
> of -
> any
>
>
|||Salil
http://www.sql-server-performance.co...gmentation.asp
http://www.sql-server-performance.co...showcontig.asp
"Salil" <Salil@.discussions.microsoft.com> wrote in message
news:39B22717-9962-4252-ADD9-F44188DC99E2@.microsoft.com...
> Hi Uri,
> I have asked these queries only after not finding anything related to data
> fragmentation in BOL. I've already gone through showcontig, indexdefrag
and[vbcol=seagreen]
> dbreindex help provided in BOL.
> Everything seems to be talking only about index defragmentation.
> Salil.
> "Uri Dimant" wrote:
have[vbcol=seagreen]
case[vbcol=seagreen]
to[vbcol=seagreen]
or[vbcol=seagreen]
|||Thank you Uri.
I have already gone through these links before making this post.
My confusion still remains.
Salil.
"Uri Dimant" wrote:
> Salil
> http://www.sql-server-performance.co...gmentation.asp
> --
> http://www.sql-server-performance.co...showcontig.asp
>
>
> "Salil" <Salil@.discussions.microsoft.com> wrote in message
> news:39B22717-9962-4252-ADD9-F44188DC99E2@.microsoft.com...
> and
> have
> case
> to
> or
>
>
|||See inline
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Salil" <Salil@.discussions.microsoft.com> wrote in message
news:5C5D1A94-AD5A-4CF4-9897-01E8F847E5FE@.microsoft.com...
> Hi,
> I have recently started delving into fragmentation in sql server. I have
> some basic/conceptual level queries regarding the same -
> 1) Is index fragmentation and data fragmentation same/different in case
of -
> - table with clustered index
> - table with no clustered index
>
Index fragmentation is index fragmentation regardless of the storage of the
data in the table.
If the table has a clustered index, it falls under normal index
fragmentation... However be aware that shrinking the database fragments the
indexes, the worst case being the clustered index.
Heaps ( tables with no clustered index) contain unordered data, so logical
fragmentation is not a problem.
When one speaks of fragmentation there is internal fragmentation ( how full
each page is.) and external fragmentation (how closely the liniked list
(index) order is to the physical order of the pages on the disk)...
The OTHER kind of fragmentation is the kind that Diskkeeper fixes, moving
all of the parts of a physical file into a single contiguous disk segment...
You can ( and most folks do) have a situation where this kind of
fragmentation does not exist, yet within the file, the rows for a table or
index are all around in the physical file ( the previous kind of
fragmentation above.)
Index defrag and dbcc dbreindex fix the first kind of fragmentation NOT the
same thing that diskkeeper works on.
> 2) Is there a separate process for data defragmentation? If yes, then to
> which type of table does it apply to - with or without clustered index or
any
> other type?
Only indexdefrag and dbreindex, for clustered indexes. Some folks create
then drop a clustered index on a heap to clean it up...(This works also.)
> 3) Will running DBCC DBREINDEX for any kind of index (clustered and
> non-clustered) defrag the data pages as well?
The data pages ARE the leaf level of a clustered index, so defragging a
clustered index fixes the data pages. Since there is no ordering on a heap
table, creating then dropping a clustered index on a heap, fixes up the data
pages there as well..
> Please excuse me if any question seems completely stupid.
None of your questions are stupid... It is good that you are smart enough to
ask the questions, and know that there are issues you need to understand.
and this is the perfect place to do just that...
> Thank you.
> Regards,
> Salil.
>
|||Also take a look at this:
http://www.microsoft.com/technet/pro.../ss2kidbp.mspx
Andrew J. Kelly SQL MVP
"Salil" <Salil@.discussions.microsoft.com> wrote in message
news:5C5D1A94-AD5A-4CF4-9897-01E8F847E5FE@.microsoft.com...
> Hi,
> I have recently started delving into fragmentation in sql server. I have
> some basic/conceptual level queries regarding the same -
> 1) Is index fragmentation and data fragmentation same/different in case
of -
> - table with clustered index
> - table with no clustered index
> 2) Is there a separate process for data defragmentation? If yes, then to
> which type of table does it apply to - with or without clustered index or
any
> other type?
> 3) Will running DBCC DBREINDEX for any kind of index (clustered and
> non-clustered) defrag the data pages as well?
> Please excuse me if any question seems completely stupid.
> Thank you.
> Regards,
> Salil.
>
|||Thank you Wayne. The replies helped a lot !
I think I can conclude that running DBREINDEX on clustered indexes with
logical OR extent fragmentation will reduce both kinds of fragmentation.
One last query for now -
Since heaps do not have clustered indexes, will there be any decrease in
logical or extent fragmentation by simply running DBREINDEX WITHOUT creating
and dropping a clustered index ?
Salil.
"Wayne Snyder" wrote:
> See inline
>
> --
> Wayne Snyder, MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> www.mariner-usa.com
> (Please respond only to the newsgroups.)
> I support the Professional Association of SQL Server (PASS) and it's
> community of SQL Server professionals.
> www.sqlpass.org
> "Salil" <Salil@.discussions.microsoft.com> wrote in message
> news:5C5D1A94-AD5A-4CF4-9897-01E8F847E5FE@.microsoft.com...
> of -
> Index fragmentation is index fragmentation regardless of the storage of the
> data in the table.
> If the table has a clustered index, it falls under normal index
> fragmentation... However be aware that shrinking the database fragments the
> indexes, the worst case being the clustered index.
> Heaps ( tables with no clustered index) contain unordered data, so logical
> fragmentation is not a problem.
> When one speaks of fragmentation there is internal fragmentation ( how full
> each page is.) and external fragmentation (how closely the liniked list
> (index) order is to the physical order of the pages on the disk)...
> The OTHER kind of fragmentation is the kind that Diskkeeper fixes, moving
> all of the parts of a physical file into a single contiguous disk segment...
> You can ( and most folks do) have a situation where this kind of
> fragmentation does not exist, yet within the file, the rows for a table or
> index are all around in the physical file ( the previous kind of
> fragmentation above.)
> Index defrag and dbcc dbreindex fix the first kind of fragmentation NOT the
> same thing that diskkeeper works on.
>
> any
> Only indexdefrag and dbreindex, for clustered indexes. Some folks create
> then drop a clustered index on a heap to clean it up...(This works also.)
> The data pages ARE the leaf level of a clustered index, so defragging a
> clustered index fixes the data pages. Since there is no ordering on a heap
> table, creating then dropping a clustered index on a heap, fixes up the data
> pages there as well..
>
> None of your questions are stupid... It is good that you are smart enough to
> ask the questions, and know that there are issues you need to understand.
> and this is the perfect place to do just that...
>
>
|||Thank you Andrew. I have gone through this link earlier.
In fact I had a query regarding this too -
In this article, it is mentioned that logical fragmentation holds no meaning
for heaps (IND ID = 0). Wayne also mentioned the same thing. And as per this
article, Wayne and other articles it seems that the only way to defrag these
would be to create and then drop a clustered index on it.
As per BOL, IND ID = 1 is a clustered index. So I'll obviously have to run
the DBREINDEX process for these indexes.
Will running a DBREINDEX against IND ID = 2,3,4...(not in 0, 255) (i.e.
non-clustered indexes) help in reducing fragmentation?
Salil.
"Andrew J. Kelly" wrote:
> Also take a look at this:
> http://www.microsoft.com/technet/pro.../ss2kidbp.mspx
> --
> Andrew J. Kelly SQL MVP
>
> "Salil" <Salil@.discussions.microsoft.com> wrote in message
> news:5C5D1A94-AD5A-4CF4-9897-01E8F847E5FE@.microsoft.com...
> of -
> any
>
>
|||> Since heaps do not have clustered indexes, will there be any decrease in
> logical or extent fragmentation by simply running DBREINDEX WITHOUT creating
> and dropping a clustered index ?
No, If a table doesn't have a clustered index, the rows are not stored in any particular physical order. This
is why we call such tables "heap".
Defragging or rebuilding non-clustered indexes for such tables does not move or touch the actual data pages. A
non-clustered index are only "pointers" to the data pages, after all...
Also, you need to think about what you mean when you consider data pages fragmented for a heap table. The rows
are not stored in any physical order. So, how can they become un-ordered? Sure, you can have free space on
pages, etc, but they cannot become un-ordered.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Salil" <Salil@.discussions.microsoft.com> wrote in message
news:1D9C65FE-CF51-4AD5-AC6E-25CCFC2C564B@.microsoft.com...[vbcol=seagreen]
> Thank you Wayne. The replies helped a lot !
> I think I can conclude that running DBREINDEX on clustered indexes with
> logical OR extent fragmentation will reduce both kinds of fragmentation.
> One last query for now -
> Since heaps do not have clustered indexes, will there be any decrease in
> logical or extent fragmentation by simply running DBREINDEX WITHOUT creating
> and dropping a clustered index ?
> Salil.
> "Wayne Snyder" wrote:
Index defrag and Data defrag
Hi,
I have recently started delving into fragmentation in sql server. I have
some basic/conceptual level queries regarding the same -
1) Is index fragmentation and data fragmentation same/different in case of -
- table with clustered index
- table with no clustered index
2) Is there a separate process for data defragmentation? If yes, then to
which type of table does it apply to - with or without clustered index or an
y
other type?
3) Will running DBCC DBREINDEX for any kind of index (clustered and
non-clustered) defrag the data pages as well?
Please excuse me if any question seems completely stupid.
Thank you.
Regards,
Salil.Salil
Start looking at DBCC SHOWCONTIG,DBCC INDEXDEFRAG in the BOL.
"Salil" <Salil@.discussions.microsoft.com> wrote in message
news:5C5D1A94-AD5A-4CF4-9897-01E8F847E5FE@.microsoft.com...
> Hi,
> I have recently started delving into fragmentation in sql server. I have
> some basic/conceptual level queries regarding the same -
> 1) Is index fragmentation and data fragmentation same/different in case
of -
> - table with clustered index
> - table with no clustered index
> 2) Is there a separate process for data defragmentation? If yes, then to
> which type of table does it apply to - with or without clustered index or
any
> other type?
> 3) Will running DBCC DBREINDEX for any kind of index (clustered and
> non-clustered) defrag the data pages as well?
> Please excuse me if any question seems completely stupid.
> Thank you.
> Regards,
> Salil.
>|||Hi Uri,
I have asked these queries only after not finding anything related to data
fragmentation in BOL. I've already gone through showcontig, indexdefrag and
dbreindex help provided in BOL.
Everything seems to be talking only about index defragmentation.
Salil.
"Uri Dimant" wrote:
> Salil
> Start looking at DBCC SHOWCONTIG,DBCC INDEXDEFRAG in the BOL.
> "Salil" <Salil@.discussions.microsoft.com> wrote in message
> news:5C5D1A94-AD5A-4CF4-9897-01E8F847E5FE@.microsoft.com...
> of -
> any
>
>|||Salil
http://www.sql-server-performance.c...agmentation.asp
--
http://www.sql-server-performance.c..._showcontig.asp
"Salil" <Salil@.discussions.microsoft.com> wrote in message
news:39B22717-9962-4252-ADD9-F44188DC99E2@.microsoft.com...
> Hi Uri,
> I have asked these queries only after not finding anything related to data
> fragmentation in BOL. I've already gone through showcontig, indexdefrag
and[vbcol=seagreen]
> dbreindex help provided in BOL.
> Everything seems to be talking only about index defragmentation.
> Salil.
> "Uri Dimant" wrote:
>
have[vbcol=seagreen]
case[vbcol=seagreen]
to[vbcol=seagreen]
or[vbcol=seagreen]|||Thank you Uri.
I have already gone through these links before making this post.
My confusion still remains.
Salil.
"Uri Dimant" wrote:
> Salil
> http://www.sql-server-performance.c...agmentation.asp
> --
> http://www.sql-server-performance.c..._showcontig.asp
>
>
> "Salil" <Salil@.discussions.microsoft.com> wrote in message
> news:39B22717-9962-4252-ADD9-F44188DC99E2@.microsoft.com...
> and
> have
> case
> to
> or
>
>|||See inline
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Salil" <Salil@.discussions.microsoft.com> wrote in message
news:5C5D1A94-AD5A-4CF4-9897-01E8F847E5FE@.microsoft.com...
> Hi,
> I have recently started delving into fragmentation in sql server. I have
> some basic/conceptual level queries regarding the same -
> 1) Is index fragmentation and data fragmentation same/different in case
of -
> - table with clustered index
> - table with no clustered index
>
Index fragmentation is index fragmentation regardless of the storage of the
data in the table.
If the table has a clustered index, it falls under normal index
fragmentation... However be aware that shrinking the database fragments the
indexes, the worst case being the clustered index.
Heaps ( tables with no clustered index) contain unordered data, so logical
fragmentation is not a problem.
When one speaks of fragmentation there is internal fragmentation ( how full
each page is.) and external fragmentation (how closely the liniked list
(index) order is to the physical order of the pages on the disk)...
The OTHER kind of fragmentation is the kind that Diskkeeper fixes, moving
all of the parts of a physical file into a single contiguous disk segment...
You can ( and most folks do) have a situation where this kind of
fragmentation does not exist, yet within the file, the rows for a table or
index are all around in the physical file ( the previous kind of
fragmentation above.)
Index defrag and dbcc dbreindex fix the first kind of fragmentation NOT the
same thing that diskkeeper works on.
> 2) Is there a separate process for data defragmentation? If yes, then to
> which type of table does it apply to - with or without clustered index or
any
> other type?
Only indexdefrag and dbreindex, for clustered indexes. Some folks create
then drop a clustered index on a heap to clean it up...(This works also.)
> 3) Will running DBCC DBREINDEX for any kind of index (clustered and
> non-clustered) defrag the data pages as well?
The data pages ARE the leaf level of a clustered index, so defragging a
clustered index fixes the data pages. Since there is no ordering on a heap
table, creating then dropping a clustered index on a heap, fixes up the data
pages there as well..
> Please excuse me if any question seems completely stupid.
None of your questions are stupid... It is good that you are smart enough to
ask the questions, and know that there are issues you need to understand.
and this is the perfect place to do just that...
> Thank you.
> Regards,
> Salil.
>|||Also take a look at this:
http://www.microsoft.com/technet/pr...n/ss2kidbp.mspx
Andrew J. Kelly SQL MVP
"Salil" <Salil@.discussions.microsoft.com> wrote in message
news:5C5D1A94-AD5A-4CF4-9897-01E8F847E5FE@.microsoft.com...
> Hi,
> I have recently started delving into fragmentation in sql server. I have
> some basic/conceptual level queries regarding the same -
> 1) Is index fragmentation and data fragmentation same/different in case
of -
> - table with clustered index
> - table with no clustered index
> 2) Is there a separate process for data defragmentation? If yes, then to
> which type of table does it apply to - with or without clustered index or
any
> other type?
> 3) Will running DBCC DBREINDEX for any kind of index (clustered and
> non-clustered) defrag the data pages as well?
> Please excuse me if any question seems completely stupid.
> Thank you.
> Regards,
> Salil.
>|||Thank you Wayne. The replies helped a lot !
I think I can conclude that running DBREINDEX on clustered indexes with
logical OR extent fragmentation will reduce both kinds of fragmentation.
One last query for now -
Since heaps do not have clustered indexes, will there be any decrease in
logical or extent fragmentation by simply running DBREINDEX WITHOUT creating
and dropping a clustered index ?
Salil.
"Wayne Snyder" wrote:
> See inline
>
> --
> Wayne Snyder, MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> www.mariner-usa.com
> (Please respond only to the newsgroups.)
> I support the Professional Association of SQL Server (PASS) and it's
> community of SQL Server professionals.
> www.sqlpass.org
> "Salil" <Salil@.discussions.microsoft.com> wrote in message
> news:5C5D1A94-AD5A-4CF4-9897-01E8F847E5FE@.microsoft.com...
> of -
> Index fragmentation is index fragmentation regardless of the storage of th
e
> data in the table.
> If the table has a clustered index, it falls under normal index
> fragmentation... However be aware that shrinking the database fragments t
he
> indexes, the worst case being the clustered index.
> Heaps ( tables with no clustered index) contain unordered data, so logical
> fragmentation is not a problem.
> When one speaks of fragmentation there is internal fragmentation ( how ful
l
> each page is.) and external fragmentation (how closely the liniked list
> (index) order is to the physical order of the pages on the disk)...
> The OTHER kind of fragmentation is the kind that Diskkeeper fixes, moving
> all of the parts of a physical file into a single contiguous disk segment.
.
> You can ( and most folks do) have a situation where this kind of
> fragmentation does not exist, yet within the file, the rows for a table or
> index are all around in the physical file ( the previous kind of
> fragmentation above.)
> Index defrag and dbcc dbreindex fix the first kind of fragmentation NOT th
e
> same thing that diskkeeper works on.
>
> any
> Only indexdefrag and dbreindex, for clustered indexes. Some folks create
> then drop a clustered index on a heap to clean it up...(This works also.)
>
> The data pages ARE the leaf level of a clustered index, so defragging a
> clustered index fixes the data pages. Since there is no ordering on a heap
> table, creating then dropping a clustered index on a heap, fixes up the da
ta
> pages there as well..
>
> None of your questions are stupid... It is good that you are smart enough
to
> ask the questions, and know that there are issues you need to understand.
> and this is the perfect place to do just that...
>
>|||Thank you Andrew. I have gone through this link earlier.
In fact I had a query regarding this too -
In this article, it is mentioned that logical fragmentation holds no meaning
for heaps (IND ID = 0). Wayne also mentioned the same thing. And as per this
article, Wayne and other articles it seems that the only way to defrag these
would be to create and then drop a clustered index on it.
As per BOL, IND ID = 1 is a clustered index. So I'll obviously have to run
the DBREINDEX process for these indexes.
Will running a DBREINDEX against IND ID = 2,3,4...(not in 0, 255) (i.e.
non-clustered indexes) help in reducing fragmentation?
Salil.
"Andrew J. Kelly" wrote:
> Also take a look at this:
> [url]http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ss2kidbp.mspx[/ur
l]
> --
> Andrew J. Kelly SQL MVP
>
> "Salil" <Salil@.discussions.microsoft.com> wrote in message
> news:5C5D1A94-AD5A-4CF4-9897-01E8F847E5FE@.microsoft.com...
> of -
> any
>
>|||> Since heaps do not have clustered indexes, will there be any decrease in
> logical or extent fragmentation by simply running DBREINDEX WITHOUT creati
ng
> and dropping a clustered index ?
No, If a table doesn't have a clustered index, the rows are not stored in an
y particular physical order. This
is why we call such tables "heap".
Defragging or rebuilding non-clustered indexes for such tables does not move
or touch the actual data pages. A
non-clustered index are only "pointers" to the data pages, after all...
Also, you need to think about what you mean when you consider data pages fra
gmented for a heap table. The rows
are not stored in any physical order. So, how can they become un-ordered? Su
re, you can have free space on
pages, etc, but they cannot become un-ordered.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Salil" <Salil@.discussions.microsoft.com> wrote in message
news:1D9C65FE-CF51-4AD5-AC6E-25CCFC2C564B@.microsoft.com...[vbcol=seagreen]
> Thank you Wayne. The replies helped a lot !
> I think I can conclude that running DBREINDEX on clustered indexes with
> logical OR extent fragmentation will reduce both kinds of fragmentation.
> One last query for now -
> Since heaps do not have clustered indexes, will there be any decrease in
> logical or extent fragmentation by simply running DBREINDEX WITHOUT creati
ng
> and dropping a clustered index ?
> Salil.
> "Wayne Snyder" wrote:
>
I have recently started delving into fragmentation in sql server. I have
some basic/conceptual level queries regarding the same -
1) Is index fragmentation and data fragmentation same/different in case of -
- table with clustered index
- table with no clustered index
2) Is there a separate process for data defragmentation? If yes, then to
which type of table does it apply to - with or without clustered index or an
y
other type?
3) Will running DBCC DBREINDEX for any kind of index (clustered and
non-clustered) defrag the data pages as well?
Please excuse me if any question seems completely stupid.
Thank you.
Regards,
Salil.Salil
Start looking at DBCC SHOWCONTIG,DBCC INDEXDEFRAG in the BOL.
"Salil" <Salil@.discussions.microsoft.com> wrote in message
news:5C5D1A94-AD5A-4CF4-9897-01E8F847E5FE@.microsoft.com...
> Hi,
> I have recently started delving into fragmentation in sql server. I have
> some basic/conceptual level queries regarding the same -
> 1) Is index fragmentation and data fragmentation same/different in case
of -
> - table with clustered index
> - table with no clustered index
> 2) Is there a separate process for data defragmentation? If yes, then to
> which type of table does it apply to - with or without clustered index or
any
> other type?
> 3) Will running DBCC DBREINDEX for any kind of index (clustered and
> non-clustered) defrag the data pages as well?
> Please excuse me if any question seems completely stupid.
> Thank you.
> Regards,
> Salil.
>|||Hi Uri,
I have asked these queries only after not finding anything related to data
fragmentation in BOL. I've already gone through showcontig, indexdefrag and
dbreindex help provided in BOL.
Everything seems to be talking only about index defragmentation.
Salil.
"Uri Dimant" wrote:
> Salil
> Start looking at DBCC SHOWCONTIG,DBCC INDEXDEFRAG in the BOL.
> "Salil" <Salil@.discussions.microsoft.com> wrote in message
> news:5C5D1A94-AD5A-4CF4-9897-01E8F847E5FE@.microsoft.com...
> of -
> any
>
>|||Salil
http://www.sql-server-performance.c...agmentation.asp
--
http://www.sql-server-performance.c..._showcontig.asp
"Salil" <Salil@.discussions.microsoft.com> wrote in message
news:39B22717-9962-4252-ADD9-F44188DC99E2@.microsoft.com...
> Hi Uri,
> I have asked these queries only after not finding anything related to data
> fragmentation in BOL. I've already gone through showcontig, indexdefrag
and[vbcol=seagreen]
> dbreindex help provided in BOL.
> Everything seems to be talking only about index defragmentation.
> Salil.
> "Uri Dimant" wrote:
>
have[vbcol=seagreen]
case[vbcol=seagreen]
to[vbcol=seagreen]
or[vbcol=seagreen]|||Thank you Uri.
I have already gone through these links before making this post.
My confusion still remains.
Salil.
"Uri Dimant" wrote:
> Salil
> http://www.sql-server-performance.c...agmentation.asp
> --
> http://www.sql-server-performance.c..._showcontig.asp
>
>
> "Salil" <Salil@.discussions.microsoft.com> wrote in message
> news:39B22717-9962-4252-ADD9-F44188DC99E2@.microsoft.com...
> and
> have
> case
> to
> or
>
>|||See inline
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Salil" <Salil@.discussions.microsoft.com> wrote in message
news:5C5D1A94-AD5A-4CF4-9897-01E8F847E5FE@.microsoft.com...
> Hi,
> I have recently started delving into fragmentation in sql server. I have
> some basic/conceptual level queries regarding the same -
> 1) Is index fragmentation and data fragmentation same/different in case
of -
> - table with clustered index
> - table with no clustered index
>
Index fragmentation is index fragmentation regardless of the storage of the
data in the table.
If the table has a clustered index, it falls under normal index
fragmentation... However be aware that shrinking the database fragments the
indexes, the worst case being the clustered index.
Heaps ( tables with no clustered index) contain unordered data, so logical
fragmentation is not a problem.
When one speaks of fragmentation there is internal fragmentation ( how full
each page is.) and external fragmentation (how closely the liniked list
(index) order is to the physical order of the pages on the disk)...
The OTHER kind of fragmentation is the kind that Diskkeeper fixes, moving
all of the parts of a physical file into a single contiguous disk segment...
You can ( and most folks do) have a situation where this kind of
fragmentation does not exist, yet within the file, the rows for a table or
index are all around in the physical file ( the previous kind of
fragmentation above.)
Index defrag and dbcc dbreindex fix the first kind of fragmentation NOT the
same thing that diskkeeper works on.
> 2) Is there a separate process for data defragmentation? If yes, then to
> which type of table does it apply to - with or without clustered index or
any
> other type?
Only indexdefrag and dbreindex, for clustered indexes. Some folks create
then drop a clustered index on a heap to clean it up...(This works also.)
> 3) Will running DBCC DBREINDEX for any kind of index (clustered and
> non-clustered) defrag the data pages as well?
The data pages ARE the leaf level of a clustered index, so defragging a
clustered index fixes the data pages. Since there is no ordering on a heap
table, creating then dropping a clustered index on a heap, fixes up the data
pages there as well..
> Please excuse me if any question seems completely stupid.
None of your questions are stupid... It is good that you are smart enough to
ask the questions, and know that there are issues you need to understand.
and this is the perfect place to do just that...
> Thank you.
> Regards,
> Salil.
>|||Also take a look at this:
http://www.microsoft.com/technet/pr...n/ss2kidbp.mspx
Andrew J. Kelly SQL MVP
"Salil" <Salil@.discussions.microsoft.com> wrote in message
news:5C5D1A94-AD5A-4CF4-9897-01E8F847E5FE@.microsoft.com...
> Hi,
> I have recently started delving into fragmentation in sql server. I have
> some basic/conceptual level queries regarding the same -
> 1) Is index fragmentation and data fragmentation same/different in case
of -
> - table with clustered index
> - table with no clustered index
> 2) Is there a separate process for data defragmentation? If yes, then to
> which type of table does it apply to - with or without clustered index or
any
> other type?
> 3) Will running DBCC DBREINDEX for any kind of index (clustered and
> non-clustered) defrag the data pages as well?
> Please excuse me if any question seems completely stupid.
> Thank you.
> Regards,
> Salil.
>|||Thank you Wayne. The replies helped a lot !
I think I can conclude that running DBREINDEX on clustered indexes with
logical OR extent fragmentation will reduce both kinds of fragmentation.
One last query for now -
Since heaps do not have clustered indexes, will there be any decrease in
logical or extent fragmentation by simply running DBREINDEX WITHOUT creating
and dropping a clustered index ?
Salil.
"Wayne Snyder" wrote:
> See inline
>
> --
> Wayne Snyder, MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> www.mariner-usa.com
> (Please respond only to the newsgroups.)
> I support the Professional Association of SQL Server (PASS) and it's
> community of SQL Server professionals.
> www.sqlpass.org
> "Salil" <Salil@.discussions.microsoft.com> wrote in message
> news:5C5D1A94-AD5A-4CF4-9897-01E8F847E5FE@.microsoft.com...
> of -
> Index fragmentation is index fragmentation regardless of the storage of th
e
> data in the table.
> If the table has a clustered index, it falls under normal index
> fragmentation... However be aware that shrinking the database fragments t
he
> indexes, the worst case being the clustered index.
> Heaps ( tables with no clustered index) contain unordered data, so logical
> fragmentation is not a problem.
> When one speaks of fragmentation there is internal fragmentation ( how ful
l
> each page is.) and external fragmentation (how closely the liniked list
> (index) order is to the physical order of the pages on the disk)...
> The OTHER kind of fragmentation is the kind that Diskkeeper fixes, moving
> all of the parts of a physical file into a single contiguous disk segment.
.
> You can ( and most folks do) have a situation where this kind of
> fragmentation does not exist, yet within the file, the rows for a table or
> index are all around in the physical file ( the previous kind of
> fragmentation above.)
> Index defrag and dbcc dbreindex fix the first kind of fragmentation NOT th
e
> same thing that diskkeeper works on.
>
> any
> Only indexdefrag and dbreindex, for clustered indexes. Some folks create
> then drop a clustered index on a heap to clean it up...(This works also.)
>
> The data pages ARE the leaf level of a clustered index, so defragging a
> clustered index fixes the data pages. Since there is no ordering on a heap
> table, creating then dropping a clustered index on a heap, fixes up the da
ta
> pages there as well..
>
> None of your questions are stupid... It is good that you are smart enough
to
> ask the questions, and know that there are issues you need to understand.
> and this is the perfect place to do just that...
>
>|||Thank you Andrew. I have gone through this link earlier.
In fact I had a query regarding this too -
In this article, it is mentioned that logical fragmentation holds no meaning
for heaps (IND ID = 0). Wayne also mentioned the same thing. And as per this
article, Wayne and other articles it seems that the only way to defrag these
would be to create and then drop a clustered index on it.
As per BOL, IND ID = 1 is a clustered index. So I'll obviously have to run
the DBREINDEX process for these indexes.
Will running a DBREINDEX against IND ID = 2,3,4...(not in 0, 255) (i.e.
non-clustered indexes) help in reducing fragmentation?
Salil.
"Andrew J. Kelly" wrote:
> Also take a look at this:
> [url]http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ss2kidbp.mspx[/ur
l]
> --
> Andrew J. Kelly SQL MVP
>
> "Salil" <Salil@.discussions.microsoft.com> wrote in message
> news:5C5D1A94-AD5A-4CF4-9897-01E8F847E5FE@.microsoft.com...
> of -
> any
>
>|||> Since heaps do not have clustered indexes, will there be any decrease in
> logical or extent fragmentation by simply running DBREINDEX WITHOUT creati
ng
> and dropping a clustered index ?
No, If a table doesn't have a clustered index, the rows are not stored in an
y particular physical order. This
is why we call such tables "heap".
Defragging or rebuilding non-clustered indexes for such tables does not move
or touch the actual data pages. A
non-clustered index are only "pointers" to the data pages, after all...
Also, you need to think about what you mean when you consider data pages fra
gmented for a heap table. The rows
are not stored in any physical order. So, how can they become un-ordered? Su
re, you can have free space on
pages, etc, but they cannot become un-ordered.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Salil" <Salil@.discussions.microsoft.com> wrote in message
news:1D9C65FE-CF51-4AD5-AC6E-25CCFC2C564B@.microsoft.com...[vbcol=seagreen]
> Thank you Wayne. The replies helped a lot !
> I think I can conclude that running DBREINDEX on clustered indexes with
> logical OR extent fragmentation will reduce both kinds of fragmentation.
> One last query for now -
> Since heaps do not have clustered indexes, will there be any decrease in
> logical or extent fragmentation by simply running DBREINDEX WITHOUT creati
ng
> and dropping a clustered index ?
> Salil.
> "Wayne Snyder" wrote:
>
Subscribe to:
Posts (Atom)