Find Fragmentation in a Database

I had users reporting slow response on Database. The Query which they use to run hit few tables in a Database. The Query use to run in  less then 10Sec but after fresh data load overnight the Query started to take more than 20sec to run.

Below is Script when executed against a single database would give you a nice user friendly fragmentation report.
 
--------------------------------------------------------------------------------------------------

SELECT
  SCHEMA_NAME(OBJECTPROPERTY(ps.object_id, 'SchemaId')) "Schema",
  OBJECT_NAME(ps.object_id) "ObjectName",
  si.name "IndexName",
  ps.index_id "IndexID",
  ps.index_type_desc "IndexType",
  ps.partition_number "Partition",
  ps.avg_fragmentation_in_percent "Fragmentation(%)",
  ps.page_count "PageCount"
FROM
  sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL , NULL, N'Limited') ps
INNER JOIN
  sys.indexes si
ON
  ps.object_id = si.object_id
AND
  ps.index_id = si.index_id
WHERE
  ps.avg_fragmentation_in_percent > 30
AND
  ps.page_count > 100
ORDER BY
  [Fragmentation(%)] DESC;

--------------------------------------------------------------------------------------------------

Based on the Output...Maintenance plan is Ran which does below
If Fragmentation(%) < 5   - No Change 
If Fragmentation(%) between 5  and 30   then reorganise that index
If Fragmentation(%) > 30  then rebuild index.

Once the maintenance plan completed the user saw a GOOD response.