Find the index fragmentation in a database

Whenever a query has a poor performance, the first step I take is to see if the indexes of the table in the query are fragmented badly.

use DB1
GO

SELECT ps.database_id, ps.OBJECT_ID, ps.index_id, si.name, ps.avg_fragmentation_in_percent,
(SELECT distinct so.name FROM sys.objects so INNER JOIN sys.indexes ON so.object_id = si.object_id) ParentTable
FROM sys.dm_db_index_physical_stats (7, NULL, NULL, NULL, NULL) AS ps
INNER JOIN sys.indexes si ON ps.OBJECT_ID = si.OBJECT_ID
AND ps.index_id = si.index_id
WHERE ps.database_id = DB_ID() AND si.name is not null AND
ps.avg_fragmentation_in_percent > 30
ORDER BY ps.avg_fragmentation_in_percent desc
GO


***Here "7" is the database Id of the curent database against which the query is run, in my e.g. DB1

you can find the database id of the current database by the following script
Use DB1
GO
SELECT DB_ID()
GO
*****************************




In the above results, I will be rebuilding the indexes of these tables.