Rebuild Info

Question

Rebuild Info

OnlyOneRJ

Hall of Fame

Points: 3894
More actions
January 23, 2013 at 7:04 am

#267546

When executing the query it shows that the fragmentation in percent is 70%
but after rebuilding the indexes the fragmentation still shows the same :w00t:
Please help me in understanding it & solving it
************************************
Every Dog has a Tail !!!!! :-D

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

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

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

How big's the index? How many pages?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

OnlyOneRJ Hall of Fame Points: 3894 More actions · Answer 2

Gila, How do i get to know abt it??

can you help me with the query

************************************
Every Dog has a Tail !!!!! :-D

Ant-Green SSC Guru Points: 113445 More actions · Answer 3

page count column from sys.dm_db_index_physical_stats

also contained on the index properties GUI in SSMS in the fragmentation tab

OnlyOneRJ Hall of Fame Points: 3894 More actions · Answer 4

I executed below query where in found the Pages count is 5000

but how big index is it, i didnt come to know

=========================

SELECT

s.[name] AS [Schema],

o.[name] AS [Table],

ips.index_type_desc AS [Index Type],

i.[name] AS [Index Name],

i.is_primary_key AS [Primary Key],

i.is_unique AS [Unique],

ips.index_depth,

i.fill_factor AS [Fill factor],

ips.avg_fragmentation_in_percent,

ips.fragment_count,

ips.avg_fragment_size_in_pages,

ips.page_count,

o.create_date,

o.modify_date

FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, NULL) ips

INNER JOIN sys.objects o ON ips.object_id = o.object_id

INNER JOIN sys.schemAS s ON o.schema_id = s.schema_id

INNER JOIN sys.indexes i ON i.object_id = ips.object_id

AND i.index_id = ips.index_id

AND ips.avg_fragmentation_in_percent > 40

=========================

Please suggest

************************************
Every Dog has a Tail !!!!! :-D

Sean Pearce SSCoach Points: 15999 More actions · Answer 5

Page is 8Kb, so 5000 pages is 40,000Kb.

The SQL Guy @ blogspot[/url]

@SeanPearceSQL

About Me[/url]

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 6

Is it a heap or an index? Is autoshrink on?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

OnlyOneRJ Hall of Fame Points: 3894 More actions · Answer 7

Its Heap & how do i check for Auto Shrink?

************************************
Every Dog has a Tail !!!!! :-D

OnlyOneRJ Hall of Fame Points: 3894 More actions · Answer 8

In Database Properties Auto Shrink = False

************************************
Every Dog has a Tail !!!!! :-D

Ant-Green SSC Guru Points: 113445 More actions · Answer 9

If its a heap, you need to do

ALTER TABLE tablename REBUILD

OnlyOneRJ Hall of Fame Points: 3894 More actions · Answer 10

Are u sure i need to do

Alter Table tablename Rebuild???

Error -Invalid syntax near Rebuild...

************************************
Every Dog has a Tail !!!!! :-D

Ant-Green SSC Guru Points: 113445 More actions · Answer 11

Ant-Green

SSC Guru

Points: 113445

January 23, 2013 at 7:41 am

#1580090

What edition of SQL Server?

Perry Whittle SSC Guru Points: 233853 More actions · Answer 12

Execute the following against your database and substitute the table name in the WHERE predicate and post the results back please

SELECTOBJECT_NAME(i.object_id) AS TableName

, ISNULL(i.name, 'HEAP') AS IndexName

, i.type_desc AS IndexType

, p.partition_number AS PartitionNo

, p.rows AS NumRows

, au.type_desc AS InType

, au.total_pages AS NumPages

, au.total_pages * 8 AS TotKBs

, au.used_pages * 8 AS UsedKBs

, au.data_pages * 8 AS DataKBs

FROM sys.indexes i INNER JOIN sys.partitions p

ON i.object_id = p.object_id AND i.index_id = p.index_id

INNER JOIN sys.allocation_units au ON

CASE

WHEN au.type IN (1,3) THEN p.hobt_id

WHEN au.type = 2 THEN p.partition_id

END = au.container_id

WHERE OBJECT_NAME(i.object_id) = 'yourtablename'

-----------------------------------------------------------------------------------------------------------

"Ya can't make an omelette without breaking just a few eggs" 😉

OnlyOneRJ Hall of Fame Points: 3894 More actions · Answer 13

Microsoft SQL Server 2005 - 9.00.5000.00 (X64)

Dec 10 2010 10:38:40

(Build 3790: Service Pack 2)

************************************
Every Dog has a Tail !!!!! :-D

OnlyOneRJ Hall of Fame Points: 3894 More actions · Answer 14

Perry Whittle (1/23/2013)
Execute the following against your database and substitute the table name in the WHERE predicate and post the results back please
SELECTOBJECT_NAME(i.object_id) AS TableName
, ISNULL(i.name, 'HEAP') AS IndexName
, i.type_desc AS IndexType
, p.partition_number AS PartitionNo
, p.rows AS NumRows
, au.type_desc AS InType
, au.total_pages AS NumPages
, au.total_pages * 8 AS TotKBs
, au.used_pages * 8 AS UsedKBs
, au.data_pages * 8 AS DataKBs
FROM sys.indexes i INNER JOIN sys.partitions p
ON i.object_id = p.object_id AND i.index_id = p.index_id
INNER JOIN sys.allocation_units au ON
CASE
WHEN au.type IN (1,3) THEN p.hobt_id
WHEN au.type = 2 THEN p.partition_id
END = au.container_id
WHERE OBJECT_NAME(i.object_id) = 'yourtablename'

TableNameIndexNameIndexTypePartitionNoNumRowsInTypeNumPagesTotKBsUsedKBsDataKBs

TestHEAPHEAP11024112IN_ROW_DATA4465357203539235384

************************************
Every Dog has a Tail !!!!! :-D