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

Any Tool / Application to read SQL Server Database Fragramentation ? Expand / Collapse
Author
Message
Posted Friday, June 07, 2013 1:50 PM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Yesterday @ 4:25 PM
Points: 106, Visits: 767
Hi I was wondering does anyone know of any tools that are available for SQL Server 2008 R2 to see internal fragmentation of a database. I'm interested in the logical fragmentation.

Our Datafiles are allocated at a set size, so I believe there would not be much the OS File level fragmentation -- but we deleted half our data in the alllocated Datafiles and I wanted to see how the fragmentation is. (SQL Server side.. indexes have been full rebuilt)

thanks


--------------------------------------------------
...0.05 points per day since registration... slowly crawl up to 1 pt per day hopefully
Post #1461211
Posted Friday, June 07, 2013 2:27 PM


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 @ 7:56 AM
Points: 41,524, Visits: 34,440
If your indexes have just been rebuilt, then you probably don't have much logical fragmentation.

Red Gate's got a tool to show you index fragmentation. Free tool last time I checked, but it just checks the same DMVs you can.



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 #1461219
Posted Friday, June 07, 2013 2:53 PM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Yesterday @ 4:25 PM
Points: 106, Visits: 767
GilaMonster (6/7/2013)
If your indexes have just been rebuilt, then you probably don't have much logical fragmentation.

Red Gate's got a tool to show you index fragmentation. Free tool last time I checked, but it just checks the same DMVs you can.


Thanks Gail, I've been struggling with slow backups after deleting nearly 40%-50% of our database and rebuilt all indexes. (I posted a question awhile back - http://www.sqlservercentral.com/Forums/Topic1449898-391-2.aspx#bm1450875) I decided to re-visit it again. I wanted to see the internals if after the full index re-builds of all tables things are truly "de-fragmented"

I haven't figured out why, a lot of nice people chimed in and gave some suggestions and recommendations, but unfortunately nothing that helped. I probably am missing something, either not asking the right question to narrow it down. I did have another idea... I dont' have the diskspace (and time yet...) for a test server, but working on maybe move everything into new Filegroup and datafiles.


--------------------------------------------------
...0.05 points per day since registration... slowly crawl up to 1 pt per day hopefully
Post #1461232
Posted Friday, June 07, 2013 3:23 PM


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 @ 7:56 AM
Points: 41,524, Visits: 34,440
sqlsurfing (6/7/2013)
I wanted to see the internals if after the full index re-builds of all tables things are truly "de-fragmented"


If you've rebuilt the indexes, then the indexes are about as defragmented as they're going to get. You don't need any tools to see index fragmentation, the index physical stats DMV shows that.



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 #1461241
Posted Friday, June 07, 2013 8:34 PM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Yesterday @ 4:25 PM
Points: 106, Visits: 767
GilaMonster (6/7/2013)
sqlsurfing (6/7/2013)
I wanted to see the internals if after the full index re-builds of all tables things are truly "de-fragmented"


If you've rebuilt the indexes, then the indexes are about as defragmented as they're going to get. You don't need any tools to see index fragmentation, the index physical stats DMV shows that.


Thanks Gail, sounds like trying to dig around for a program to read the fragmentation is a moot point. Appreciate the feedack.


--------------------------------------------------
...0.05 points per day since registration... slowly crawl up to 1 pt per day hopefully
Post #1461253
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse