SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Any Tool / Application to read SQL Server Database Fragramentation ?


Any Tool / Application to read SQL Server Database Fragramentation ?

Author
Message
sqlsurfing
sqlsurfing
SSC Eights!
SSC Eights! (952 reputation)SSC Eights! (952 reputation)SSC Eights! (952 reputation)SSC Eights! (952 reputation)SSC Eights! (952 reputation)SSC Eights! (952 reputation)SSC Eights! (952 reputation)SSC Eights! (952 reputation)

Group: General Forum Members
Points: 952 Visits: 1189
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 :-D
GilaMonster
GilaMonster
SSC Guru
SSC Guru (210K reputation)SSC Guru (210K reputation)SSC Guru (210K reputation)SSC Guru (210K reputation)SSC Guru (210K reputation)SSC Guru (210K reputation)SSC Guru (210K reputation)SSC Guru (210K reputation)

Group: General Forum Members
Points: 210984 Visits: 46251
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, 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


sqlsurfing
sqlsurfing
SSC Eights!
SSC Eights! (952 reputation)SSC Eights! (952 reputation)SSC Eights! (952 reputation)SSC Eights! (952 reputation)SSC Eights! (952 reputation)SSC Eights! (952 reputation)SSC Eights! (952 reputation)SSC Eights! (952 reputation)

Group: General Forum Members
Points: 952 Visits: 1189
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 :-D
GilaMonster
GilaMonster
SSC Guru
SSC Guru (210K reputation)SSC Guru (210K reputation)SSC Guru (210K reputation)SSC Guru (210K reputation)SSC Guru (210K reputation)SSC Guru (210K reputation)SSC Guru (210K reputation)SSC Guru (210K reputation)

Group: General Forum Members
Points: 210984 Visits: 46251
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, 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


sqlsurfing
sqlsurfing
SSC Eights!
SSC Eights! (952 reputation)SSC Eights! (952 reputation)SSC Eights! (952 reputation)SSC Eights! (952 reputation)SSC Eights! (952 reputation)SSC Eights! (952 reputation)SSC Eights! (952 reputation)SSC Eights! (952 reputation)

Group: General Forum Members
Points: 952 Visits: 1189
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 :-D
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search