Prev: Insert missing records
Next: update statements with DTS package
From: Chris on 19 Jul 2008 23:37

I executed dbcc showcontig (with tableresults) into #ShowContg tmp table -
run query below & it shows > 100 tables with extent fragementation > 80%

The server is SQL 2005 - the database is in 80 compatibility. Is a Rebuild
Indexes Maint. Plan task which runs nightly. Why so much fragmentation still?

SELECT TOP 10
ObjectName,
IndexName,
ExtentFrag
FROM #ShowContig
WHERE ObjectName NOT LIKE 'dt%' AND
ObjectName NOT LIKE 'sys%'
ORDER BY ExtentFrag DESC

tia
Chris
From: Uri Dimant on 20 Jul 2008 03:56
Chris
Those tables are probably heaps (no clustered index) , moreover I would not
worry about fragmentation if my table has less than 1000
pages
SELECT db_name(database_id) AS DatabaseName,

OBJECT_NAME(i.object_id) AS TableName

,i.name AS TableIndexName

,phystat.avg_fragmentation_in_percent,

'ALTER INDEX '+i.name+ ' ON '+OBJECT_NAME(i.object_id)+' REORGANIZE;'

FROM

sys.dm_db_index_physical_stats(db_id(), NULL, NULL, NULL, 'DETAILED')
phystat inner JOIN sys.indexes i

ON i.object_id = phystat.object_id

AND i.index_id = phystat.index_id WHERE phystat.avg_fragmentation_in_percent
> 40

and page_count>=1000







"Chris" <Chris(a)discussions.microsoft.com> wrote in message
news:A588A42E-7530-4C6B-B651-E7365779B694(a)microsoft.com...
>
> I executed dbcc showcontig (with tableresults) into #ShowContg tmp table -
> run query below & it shows > 100 tables with extent fragementation > 80%
>
> The server is SQL 2005 - the database is in 80 compatibility. Is a Rebuild
> Indexes Maint. Plan task which runs nightly. Why so much fragmentation
> still?
>
> SELECT TOP 10
> ObjectName,
> IndexName,
> ExtentFrag
> FROM #ShowContig
> WHERE ObjectName NOT LIKE 'dt%' AND
> ObjectName NOT LIKE 'sys%'
> ORDER BY ExtentFrag DESC
>
> tia
> Chris


From: Dan Guzman on 20 Jul 2008 09:39
> I executed dbcc showcontig (with tableresults) into #ShowContg tmp table -
> run query below & it shows > 100 tables with extent fragementation > 80%

Do you have multiple data files? It is expected that extent fragmentation
will be high with multiple files because each time the next extent is in a
different file, the extent is counted as out-of-order. If you need more
detailed interpretation, please post the actual DBCC output from a sample
table.

--
Hope this helps.

Dan Guzman
SQL Server MVP
http://weblogs.sqlteam.com/dang/

"Chris" <Chris(a)discussions.microsoft.com> wrote in message
news:A588A42E-7530-4C6B-B651-E7365779B694(a)microsoft.com...
>
> I executed dbcc showcontig (with tableresults) into #ShowContg tmp table -
> run query below & it shows > 100 tables with extent fragementation > 80%
>
> The server is SQL 2005 - the database is in 80 compatibility. Is a Rebuild
> Indexes Maint. Plan task which runs nightly. Why so much fragmentation
> still?
>
> SELECT TOP 10
> ObjectName,
> IndexName,
> ExtentFrag
> FROM #ShowContig
> WHERE ObjectName NOT LIKE 'dt%' AND
> ObjectName NOT LIKE 'sys%'
> ORDER BY ExtentFrag DESC
>
> tia
> Chris

From: TheSQLGuru on 20 Jul 2008 12:11
Also, how much free space do you have in your database? Try doubling the
size of the database and seeing if defrag operation does better.

--
Kevin G. Boles
Indicium Resources, Inc.
SQL Server MVP
kgboles a earthlink dt net


"Chris" <Chris(a)discussions.microsoft.com> wrote in message
news:A588A42E-7530-4C6B-B651-E7365779B694(a)microsoft.com...
>
> I executed dbcc showcontig (with tableresults) into #ShowContg tmp table -
> run query below & it shows > 100 tables with extent fragementation > 80%
>
> The server is SQL 2005 - the database is in 80 compatibility. Is a Rebuild
> Indexes Maint. Plan task which runs nightly. Why so much fragmentation
> still?
>
> SELECT TOP 10
> ObjectName,
> IndexName,
> ExtentFrag
> FROM #ShowContig
> WHERE ObjectName NOT LIKE 'dt%' AND
> ObjectName NOT LIKE 'sys%'
> ORDER BY ExtentFrag DESC
>
> tia
> Chris


 | 
Pages: 1
Prev: Insert missing records
Next: update statements with DTS package