August 27, 2010 at 7:11 am
while this query on production database its taking a lot of time to produce the results on fragmentation
select
object_name(i.object_id) AS [Table Name],
i.name As [Index Name],
index_type_desc AS [Index Type],
avg_fragmentation_in_percent As [Fragmentation in Percentage],
PHY.FRAGMENT_COUNT,page_count
,
avg_page_space_used_in_percent
from
sys.dm_db_index_physical_stats
(DB_id('mydatabase'),NULL,NULL,NULL,'limited') phy INNER JOIN
sys.indexes i ON
i.object_id=phy.object_id and i.index_id=phy.index_id
where object_name(i.object_id)='mytable'
--where avg_fragmentation_in_percent > 0
--ORDER BY avg_fragmentation_in_percent Desc
--ORDER BY OBJECT_NAME(i.object_id) Asc
Can any one help on this, do i need to do any other ..???
please help
August 27, 2010 at 9:29 am
Limited is the quickest mode on that, so switching it to Sampled or Detailed isn't going to help.
Are you hitting contention?
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
August 29, 2010 at 5:10 pm
Sharon Kumar (8/27/2010)
where object_name(i.object_id)='mytable'
By puting the conversion from i.object_id to table name, you are forcing the query paser to bypass indexes on the table and do a cluster index scan (at best). If you can try change this around to : where i.object_id = object_id('mytable').
This can then use the index and do an index seek.
Cheers
Leo
Leo
Nothing in life is ever so complicated that with a little work it can't be made more complicated.
August 30, 2010 at 9:32 am
You've got a hardcoded 'mytable' in there; try to see if there's a significant speed difference in putting the object_id of 'mytable' directly into the DMV parameters as a number instead of using the join.
I'd be curious to see if the DMV with db_id, NULLs, 'LIMITED' is _always_ gathering stats for every single table and then executing the join.
If that doesn't help, also put in direct index ids as well, one at a time.
SELECT st.name, si.name, st.object_id, si.index_id
FROM sys.indexes si (nolock)
INNER JOIN sys.tables st (nolock)
ON st.object_id = si.object_id
WHERE st.name = 'mytable'
AND si.name = 'myindex'
For less cut and pasting later, change the DMV to be called by dynamic SQL with a parameter of the table's object id.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply