Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Rebuild Info Expand / Collapse
Author
Message
Posted Wednesday, January 23, 2013 7:04 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Wednesday, March 12, 2014 12:13 AM
Points: 258, Visits: 690
When executing the query it shows that the fragmentation in percent is 70%
but after rebuilding the indexes the fragmentation still shows the same

Please help me in understanding it & solving it








************************************
Every Dog has a Tail !!!!!
Post #1410553
Posted Wednesday, January 23, 2013 7:12 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 12:24 PM
Points: 42,774, Visits: 35,872
How big's the index? How many pages?


Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
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

Post #1410557
Posted Wednesday, January 23, 2013 7:13 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Wednesday, March 12, 2014 12:13 AM
Points: 258, Visits: 690
Gila, How do i get to know abt it??
can you help me with the query








************************************
Every Dog has a Tail !!!!!
Post #1410561
Posted Wednesday, January 23, 2013 7:15 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: 2 days ago @ 6:20 AM
Points: 5,218, Visits: 5,067
page count column from sys.dm_db_index_physical_stats

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




Want an answer fast? Try here
How to post data/code for the best help - Jeff Moden
Need a string splitter, try this - Jeff Moden
How to post performance problems - Gail Shaw
CrossTabs-Part1 & Part2 - Jeff Moden
SQL Server Backup, Integrity Check, and Index and Statistics Maintenance - Ola Hallengren
Managing Transaction Logs - Gail Shaw
Troubleshooting SQL Server: A Guide for the Accidental DBA - Jonathan Kehayias and Ted Krueger

Post #1410563
Posted Wednesday, January 23, 2013 7:17 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Wednesday, March 12, 2014 12:13 AM
Points: 258, Visits: 690
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 !!!!!
Post #1410564
Posted Wednesday, January 23, 2013 7:19 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Yesterday @ 7:47 AM
Points: 872, Visits: 2,784
Page is 8Kb, so 5000 pages is 40,000Kb.



The SQL Guy @ blogspot

@SeanPearceSQL

About Me
Post #1410566
Posted Wednesday, January 23, 2013 7:23 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 12:24 PM
Points: 42,774, Visits: 35,872
Is it a heap or an index? Is autoshrink on?


Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
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

Post #1410568
Posted Wednesday, January 23, 2013 7:27 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Wednesday, March 12, 2014 12:13 AM
Points: 258, Visits: 690
Its Heap & how do i check for Auto Shrink?







************************************
Every Dog has a Tail !!!!!
Post #1410571
Posted Wednesday, January 23, 2013 7:30 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Wednesday, March 12, 2014 12:13 AM
Points: 258, Visits: 690
In Database Properties Auto Shrink = False







************************************
Every Dog has a Tail !!!!!
Post #1410572
Posted Wednesday, January 23, 2013 7:34 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: 2 days ago @ 6:20 AM
Points: 5,218, Visits: 5,067
If its a heap, you need to do

ALTER TABLE tablename REBUILD




Want an answer fast? Try here
How to post data/code for the best help - Jeff Moden
Need a string splitter, try this - Jeff Moden
How to post performance problems - Gail Shaw
CrossTabs-Part1 & Part2 - Jeff Moden
SQL Server Backup, Integrity Check, and Index and Statistics Maintenance - Ola Hallengren
Managing Transaction Logs - Gail Shaw
Troubleshooting SQL Server: A Guide for the Accidental DBA - Jonathan Kehayias and Ted Krueger

Post #1410578
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse