Any Tool / Application to read SQL Server Database Fragramentation ?

Question

Any Tool / Application to read SQL Server Database Fragramentation ?

sqlsurfing

SSCarpal Tunnel

Points: 4883
More actions
June 7, 2013 at 1:50 pm

#276042

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 😀

Viewing 5 posts - 1 through 4 (of 4 total)

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

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

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 SSCarpal Tunnel Points: 4883 More actions · Answer 2

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 😀

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

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 SSCarpal Tunnel Points: 4883 More actions · Answer 4

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 😀