The great SQL Performance saga continued today, taking a turn for the….ah…..different…

Facts:

• The servers are exhibiting high disk I/O activity.
• So much disk activity, that all queries are nearly brought to a grinding halt while the disks are thundering away.
• The Query Plans for all I/O intense queries use index seeks – no scanning at all.

This leads me to conclude that the behaviour of the server is fine, and that the system is doing everything expected of it. Which leads me to ask the question why is an index seek taking so long to run off the disk?

Well it turns out there is in fact a way to determine the level of fragmentation of SQL indexes.

I’d written a simple query based on this content to calculate the amount of fragmentation on the live server’s indexes, and needless to say i’m a bit shocked. I sincerely thought this is something that the “qualified” DBA consultant on the other end was capable of doing, however in his defense he might have forgotten to run it and then moved onto the next high-paying job, leaving us to sweep up the shambles.

I’m no DBA….Although if I’m correct i’ll be justified in calling myself one and, i’ll also have a few more letters to add to the end of my job title and hopefully a zero to throw on the end of my salary….all for doing SFA.

FWIW, here is the query which revealed all the gory details:

select	i.object_id, i.index_id, i.name, s.avg_fragmentation_in_percent, s.avg_fragment_size_in_pages
from	sys.indexes i 
JOIN	sys.dm_db_index_physical_stats(DB_id('DATABASE_NAME_GOES_HERE'), null, null,null, null) s
	on i.object_id = s.object_id 	
	AND i.index_id = s.index_id
	where     (
		s.avg_fragmentation_in_percent > 0 
		OR s.avg_fragment_size_in_pages > 0
	)
	AND i.index_id <> 0