how do we measure the defrag will help

Question

how do we measure the defrag will help

Gangadhara MS

Hall of Fame

Points: 3377
More actions
April 3, 2014 at 1:29 pm

#284126

Hi,
I did defragmentation for the Index which are having considerable fragmentation( > 30 % rebuild and < 30% reorg). But i need to measure the effectiveness of this on my server,how do we do ? Any pointers to URL or real things will be great help for me.
Thanks,
DHARA

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Grant Fritchey SSC Guru Points: 397721 More actions · Answer 1

You measure the speed at which queries that access that index are performing before and after you defragment the indexes.

----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software

MyDoggieJessie SSC-Forever Points: 44461 More actions · Answer 2

Grant, what would be the best way to achieve that? SET STATISTICS TIME ON?

______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience

Grant Fritchey SSC Guru Points: 397721 More actions · Answer 3

MyDoggieJessie (4/3/2014)
Grant, what would be the best way to achieve that? SET STATISTICS TIME ON?

That's one way, sure. I'd probably use extended events, or, on a 2008 system, trace. That way you get a better measure. You'd also want to see the reads because, at least in theory, for any kind of scan or range scan, you'll be hitting fewer pages because of the compression from the defrag.

----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software

MyDoggieJessie SSC-Forever Points: 44461 More actions · Answer 4

Thanks Grant, I am already using a combination of trace and IO, was just wondering if there was something better (i.e. extended events) - thanks for that

______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience