April 3, 2014 at 1:29 pm
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
April 3, 2014 at 3:50 pm
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 Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
April 3, 2014 at 9:20 pm
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
April 4, 2014 at 3:31 am
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 Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
April 4, 2014 at 5:47 am
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
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply