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»»

how to List out all tables indexes having index fragmentation more than 50% of databases. Expand / Collapse
Author
Message
Posted Thursday, April 11, 2013 1:22 AM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Thursday, July 10, 2014 11:09 PM
Points: 549, Visits: 1,181
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 & Regards
NAGA.ROHITKUMAR
Post #1441131
Posted Thursday, April 11, 2013 3:32 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:11 PM
Points: 42,470, Visits: 35,541
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 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 #1441149
Posted Thursday, April 11, 2013 3:37 AM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Thursday, July 10, 2014 11:09 PM
Points: 549, Visits: 1,181
HI thanks for ur reply

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


Thanks & Regards
NAGA.ROHITKUMAR
Post #1441153
Posted Thursday, April 11, 2013 3:39 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:11 PM
Points: 42,470, Visits: 35,541
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 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 #1441154
Posted Thursday, April 11, 2013 3:43 AM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Thursday, July 10, 2014 11:09 PM
Points: 549, Visits: 1,181
Yes sir
want indexes that have more than 50% logical fragmentation? from all indexes of tables in a database


Thanks & Regards
NAGA.ROHITKUMAR
Post #1441158
Posted Thursday, April 11, 2013 5:05 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:11 PM
Points: 42,470, Visits: 35,541
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 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 #1441183
Posted Thursday, April 11, 2013 5:13 AM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Thursday, July 10, 2014 11:09 PM
Points: 549, Visits: 1,181
thank u sir

Thanks & Regards
NAGA.ROHITKUMAR
Post #1441187
Posted Friday, April 12, 2013 6:44 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 @ 1:21 PM
Points: 809, Visits: 1,160
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
Post #1441672
Posted Saturday, April 13, 2013 12:42 AM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Thursday, July 10, 2014 11:09 PM
Points: 549, Visits: 1,181
thanks and i got the same from google and issues is solved if any thing regarding required i will revert back

Thanks & Regards
NAGA.ROHITKUMAR
Post #1442013
Posted Thursday, July 11, 2013 12:58 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, April 8, 2014 3:17 PM
Points: 1, Visits: 14
@SSChasing Mays

What an awesomely useful query! Thanks for sharing!
Post #1472739
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse