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


how to List out all tables indexes having index fragmentation more than 50% of databases.


how to List out all tables indexes having index fragmentation more than 50% of databases.

Author
Message
naga.rohitkumar
naga.rohitkumar
SSC Eights!
SSC Eights! (848 reputation)SSC Eights! (848 reputation)SSC Eights! (848 reputation)SSC Eights! (848 reputation)SSC Eights! (848 reputation)SSC Eights! (848 reputation)SSC Eights! (848 reputation)SSC Eights! (848 reputation)

Group: General Forum Members
Points: 848 Visits: 1378
HI to every one
can any one guide me to find out this

if any incident occurred in sql server and i was asked to take a report of the last one hour [or] halfanhour what should i do? and

how to List out all tables indexes having index fragmentation more than 50% of a database.
for this particular period for all tables in a entire database
--

Thanks
Naga.Rohitkumar
GilaMonster
GilaMonster
SSC Guru
SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)

Group: General Forum Members
Points: 86328 Visits: 45232
What do you mean by 'more than 50% of the database'?

If you want indexes that have a logical fragmentation higher than 50%, just query sys.dm_db_index_physical_stats and filter on the logical_fragmentation_in_percent column.

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


naga.rohitkumar
naga.rohitkumar
SSC Eights!
SSC Eights! (848 reputation)SSC Eights! (848 reputation)SSC Eights! (848 reputation)SSC Eights! (848 reputation)SSC Eights! (848 reputation)SSC Eights! (848 reputation)SSC Eights! (848 reputation)SSC Eights! (848 reputation)

Group: General Forum Members
Points: 848 Visits: 1378
HI thanks for ur reply

i mean "List out all tables indexes having index fragmentation more than 50% of a database."

Thanks
Naga.Rohitkumar
GilaMonster
GilaMonster
SSC Guru
SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)

Group: General Forum Members
Points: 86328 Visits: 45232
Repeating the exact same sentence doesn't explain anything.

Do you want indexes that have more than 50% logical fragmentation? If so, see my previous reply. If not, then please explain what 'more than 50% of the database' means.

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


naga.rohitkumar
naga.rohitkumar
SSC Eights!
SSC Eights! (848 reputation)SSC Eights! (848 reputation)SSC Eights! (848 reputation)SSC Eights! (848 reputation)SSC Eights! (848 reputation)SSC Eights! (848 reputation)SSC Eights! (848 reputation)SSC Eights! (848 reputation)

Group: General Forum Members
Points: 848 Visits: 1378
Yes sir
want indexes that have more than 50% logical fragmentation? from all indexes of tables in a database

Thanks
Naga.Rohitkumar
GilaMonster
GilaMonster
SSC Guru
SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)

Group: General Forum Members
Points: 86328 Visits: 45232
Answered that one in the first reply.

GilaMonster (4/11/2013)
If you want indexes that have a logical fragmentation higher than 50%, just query sys.dm_db_index_physical_stats and filter on the logical_fragmentation_in_percent column.


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


naga.rohitkumar
naga.rohitkumar
SSC Eights!
SSC Eights! (848 reputation)SSC Eights! (848 reputation)SSC Eights! (848 reputation)SSC Eights! (848 reputation)SSC Eights! (848 reputation)SSC Eights! (848 reputation)SSC Eights! (848 reputation)SSC Eights! (848 reputation)

Group: General Forum Members
Points: 848 Visits: 1378
thank u sir

Thanks
Naga.Rohitkumar
Neeraj Dwivedi
Neeraj Dwivedi
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1095 Visits: 1334
Use this sql.

SELECT
B.name AS TableName
, C.name AS IndexName
, C.fill_factor AS IndexFillFactor
, D.rows AS RowsCount
, A.avg_fragmentation_in_percent
, A.page_count
FROM sys.dm_db_index_physical_stats(DB_ID(),NULL,NULL,NULL,NULL) A
INNER JOIN sys.objects B
ON A.object_id = B.object_id
INNER JOIN sys.indexes C
ON B.object_id = C.object_id AND A.index_id = C.index_id
INNER JOIN sys.partitions D
ON B.object_id = D.object_id AND A.index_id = D.index_id
WHERE C.index_id > 0
and A.avg_fragmentation_in_percent >50
naga.rohitkumar
naga.rohitkumar
SSC Eights!
SSC Eights! (848 reputation)SSC Eights! (848 reputation)SSC Eights! (848 reputation)SSC Eights! (848 reputation)SSC Eights! (848 reputation)SSC Eights! (848 reputation)SSC Eights! (848 reputation)SSC Eights! (848 reputation)

Group: General Forum Members
Points: 848 Visits: 1378
thanks and i got the same from google and issues is solved if any thing regarding required i will revert back

Thanks
Naga.Rohitkumar
baodad
baodad
Grasshopper
Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)

Group: General Forum Members
Points: 15 Visits: 14
@SSChasing Mays

What an awesomely useful query! Thanks for sharing!
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