October 31, 2012 at 11:56 pm
Hi
I have a database having 260+ tables and many indexes and states.
Is there any way to get index fragmentation having more then 30% fragmentation through query.
so i can concentrate directly on that only and re-organize index.
its urgent. pls.
thanks for your time.
November 1, 2012 at 12:21 am
Try this one
select a.*,b.AverageFragmentation from
(
SELECT
tbl.name AS [Table_Name],
tbl.object_id,
i.name AS [Name],
i.index_id,
CAST(CASE i.index_id WHEN 1 THEN 1 ELSE 0 END AS bit) AS [IsClustered],
CAST(case when i.type=3 then 1 else 0 end AS bit) AS [IsXmlIndex],
CAST(case when i.type=4 then 1 else 0 end AS bit) AS [IsSpatialIndex]
FROM
sys.tables AS tbl
INNER JOIN sys.indexes AS i ON (i.index_id > 0 and i.is_hypothetical = 0) AND (i.object_id=tbl.object_id)
)a
inner join
(
SELECT
tbl.object_id,
i.index_id,
fi.avg_fragmentation_in_percent AS [AverageFragmentation]
FROM
sys.tables AS tbl
INNER JOIN sys.indexes AS i ON (i.index_id > 0 and i.is_hypothetical = 0) AND (i.object_id=tbl.object_id)
INNER JOIN sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, 'LIMITED') AS fi ON fi.object_id=CAST(i.object_id AS int) AND fi.index_id=CAST(i.index_id AS int)
)b
on a.object_id=b.object_id and a.index_id=b.index_id
November 1, 2012 at 2:00 am
thanks for yr query.
i execute query i got :
spt_values1451152215spt_valuesclust 110050
spt_values1451152215ix2_spt_values_nu_nc200080
its only 2 rows and spt_values is not my table. i have many indexes those not come here. how can i get details of all those indexes ?
November 1, 2012 at 2:01 am
your running it on master database ...
select your database then run Query
-----------------------------------------------------------------------------
संकेत कोकणे
November 1, 2012 at 2:08 am
there is no 'sys.dm_db_index_physical_stats' sp in my database. its in master only.
i have passed DB_ID of my local database.
is there any other way to run it in my database ?
November 1, 2012 at 2:14 am
the scriptprovided by BriPan works perfect for me .
do not provide any dbid in script. just add one step
use 'YourDatabaseName'
go
select a.*,b.AverageFragmentation from
(
SELECT
tbl.name AS [Table_Name],
tbl.object_id,
i.name AS [Name],
i.index_id,
CAST(CASE i.index_id WHEN 1 THEN 1 ELSE 0 END AS bit) AS [IsClustered],
CAST(case when i.type=3 then 1 else 0 end AS bit) AS [IsXmlIndex],
CAST(case when i.type=4 then 1 else 0 end AS bit) AS [IsSpatialIndex]
FROM
sys.tables AS tbl
INNER JOIN sys.indexes AS i ON (i.index_id > 0 and i.is_hypothetical = 0) AND (i.object_id=tbl.object_id)
)a
inner join
(
SELECT
tbl.object_id,
i.index_id,
fi.avg_fragmentation_in_percent AS [AverageFragmentation]
FROM
sys.tables AS tbl
INNER JOIN sys.indexes AS i ON (i.index_id > 0 and i.is_hypothetical = 0) AND (i.object_id=tbl.object_id)
INNER JOIN sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, 'LIMITED') AS fi ON fi.object_id=CAST(i.object_id AS int) AND fi.index_id=CAST(i.index_id AS int)
)b
on a.object_id=b.object_id and a.index_id=b.index_id
-----------------------------------------------------------------------------
संकेत कोकणे
November 1, 2012 at 2:32 am
sorry yaar but need little more help.
i just copied and pasted your script in my query window and changed my database name with 'YourDatabaseName'
i got following error :
Msg 102, Level 15, State 1, Line 25
Incorrect syntax near '('.
it was coming yesterday too, when i googled i found that sys.dm_db_index_physical_stats sp is in master database only.
November 1, 2012 at 2:49 am
sys.dm_db_index_physical_stats is not an SP its a DMV and depending what DB_ID() you pass in for the first parameter it will look through that DB for index fragmentation.
Take a look in my signature for the link to Ola's maintenance scripts and get a copy of the index and statistics maintenance script, no point reinventing the wheel when this script does everything you need and more.
November 1, 2012 at 2:56 am
sys.dm_db_index_physical_stats can be used in all DB
just right click on YourDatabase then select New Query
in that query window run query which i have posted.
Just write DBname with USE you will not get error.
use Login --(Login is my Database Name)
November 2, 2012 at 11:27 pm
its great.
thanks a lot
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply