May 26, 2013 at 12:52 pm
Lynn Pettis (5/26/2013)
Looks to me like you may have some big records in some of those tables with an avg_fragment_size_in_pages = 1. I'd be interested in the following as well:min_record_size_in_bytes
max_record_size_in_bytes
avg_record_size_in_bytes
All the same values =/
index_idnameavg_fragmentation_in_percentpage_countavg_fragment_size_in_pagesavg_page_space_used_in_percentmin_record_size_in_bytesmax_record_size_in_bytesavg_record_size_in_bytes
10I_SERVER_CLIENT_LOG_1_CLIENT_ID83.21077784048423597011.1880495167885464.1157771188535444444
5I_SERVER_CLIENT_LOG_1_LOG_IDX99.2231889857614339851167.861588831233444444
0NULL99.15434425437333310178.0653233273232395.630874722016322543833753.168
0NULL22.894942813139131660434.33138147907180.94225352112675696618296.949
7I_AGENT_TRAFFIC_LOG_1_LOG_IDX99.2142514395394183392166.9136026686434444444
4I_AGENT_TRAFFIC_LOG_1_TIME_PLUS56.59452072910011814841.7140374571452974.96939708426494949
16I_AGENT_BEHAVIOR_LOG_2_COMPUTER_ID_PLUS0.01178038168.27788279773299.8516431924883929292
6I_AGENT_BEHAVIOR_LOG_1_LOG_IDX99.2438463832699113998167.846170002471444444
4I_SERVER_CLIENT_LOG_1_TIME30.43790698912331127183.1768552182858484.5172967630344171717
3I_SERVER_CLIENT_LOG_1_ID99.2134374858897110735167.91404744255131313
7I_AGENT_SYSTEM_LOG_1_LOG_IDX99.232478287214794069167.2255621447986444444
3I_AGENT_TRAFFIC_LOG_1_ID99.227192131410859782166.9353965900667131313
6I_AGENT_TRAFFIC_LOG_1_ALERT99.188705441528259781166.9368297504324131313
May 26, 2013 at 12:55 pm
Dird (5/26/2013)
The AutoShrink setting for the database (properties -> options -> AutoShrink). It's not what you were talking about?
Yes. Sounded like you were saying that autoshrink didn't work (disabled) in SQL 2008 R2.
From the results, looks like lots of page splits plus lots of deletes leaving you with high fragmentation and low page usage. Those indexes need a rebuild.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
May 26, 2013 at 1:01 pm
GilaMonster (5/26/2013)
From the results, looks like lots of page splits plus lots of deletes leaving you with high fragmentation and low page usage. Those indexes need a rebuild.
Oh, no. I just meant it's set to false/off (disabled) by default & hasn't been changed.
Where are you seeing that? Which columns are the most important to look at?
Does the GUI (index properties -> fragmentation) give accurate figures or is that just the value of avg_fragmentation_in_percent?
May 26, 2013 at 1:40 pm
Dird (5/26/2013)
Where are you seeing that? Which columns are the most important to look at?
avg_fragmentation_in_percent and avg page space used. You want them low and high respectively, yours are high fragmentation and low page density, so you definitely need to rebuild those.
Does the GUI (index properties -> fragmentation) give accurate figures or is that just the value of avg_fragmentation_in_percent?
Yes, completely accurate, it's the value of avg_fragmentation_in_percent
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
May 26, 2013 at 2:46 pm
GilaMonster (5/26/2013)
avg_fragmentation_in_percent and avg page space used.
Which do you tend to rate as the more important of them? For example, the common stat seems to be > 30% then rebuild but with the one index there you have:
avg_fragmentation_in_percent = 56%; but
avg_page_space_used_in_percent = 74%
From that I'm reading < 30% wasted (26) but lots of page splits? So if you were doing A-Z and inserted a "C" (calm) value causing a page split this has then been followed by other "C" (cat) meaning that, although there's a page split (moving the C values to a new page) the space is still being utilised OK.
Is the way I'm reading it right or? If so would your focus be on avg_fragmentation_in_percent in order to minimise page splits?
May 26, 2013 at 5:07 pm
Dird (5/26/2013)
If so would your focus be on avg_fragmentation_in_percent in order to minimise page splits?
Err....
Reducing avg_fragmentation_in_percent doesn't minimise page splits. Page splits cause fragmentation, resulting in high avg_fragmentation_in_percent.
I look at them both, you have lots of indexes with high fragmentation and low (~50% or lower) page space used, both of those are strong indications those indexes need rebuilding.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
May 26, 2013 at 5:57 pm
It's the same thing -_ rebuild index -> avg_fragmentation_in_percent reduces along with page splits. I'll play around/Google at work on Tuesday for some TSQL for a rebuilding job.
Thanks for the info.
May 26, 2013 at 8:27 pm
Dird (5/26/2013)
It's the same thing -_ rebuild index -> avg_fragmentation_in_percent reduces along with page splits. I'll play around/Google at work on Tuesday for some TSQL for a rebuilding job.Thanks for the info.
I'll save you some time: http://ola.hallengren.com/sql-server-index-and-statistics-maintenance.html
May 27, 2013 at 2:52 am
Dird (5/26/2013)
It's the same thing -_ rebuild index -> avg_fragmentation_in_percent reduces along with page splits.
No.
Page splits cause fragmentation. Rebuilding an index does not reduce page splits, it reduces fragmentation (an effect of page splits). You're mixing up cause and effect.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
May 27, 2013 at 3:41 am
They're 1 and the same. Can you reduce fragmentation without reducing page splits?
Thanks for that Lynn, will look about including it 😮
May 27, 2013 at 3:58 am
Dird (5/27/2013)
They're 1 and the same.
No, they're not.
Can you reduce fragmentation without reducing page splits?
Fragmentation is a property of the index, a number, a measurement of how out of order the pages in an index is.
A page split is an event, an occurrence, something that happens to the index. It is the process of SQL moving half the rows from a page, allocating a new one and linking the new page into the index.
Index rebuilds put the index back into order, that is reduce the number of out of order pages in an index, thereby reducing the fragmentation property of the index.
Saying page splits and fragmentation are the same is like saying that a car jumping a red traffic light is the same as the total number of accidents on the road that day.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
May 27, 2013 at 4:42 am
Comparisson not really relevent but doesn't matter. You can't move your knee without moving your shin.
May 27, 2013 at 4:51 am
Dird (5/27/2013)
Comparisson not really relevent but doesn't matter. You can't move your knee without moving your shin.
Yes,the body can come down, leaving the rest mostly in place.
That being said, you're being insulting to certainly the single MOST knowladgeble person on this site.
She's MCM certified, that means she KNOWS what she's talking about. And that starts from the smallest internals & inner workings of SS all the way up the stack.
May 27, 2013 at 4:54 am
It wasn't my intention to be insulting. Just that if fragmentation is a result of pagesplits (ignoring autoshrink etc) then I would assume you could say "reduce fragmentation" or "reduce the number of pagesplits" interchangeably since as far as I understand an index rebuild would reduce both.
May 27, 2013 at 5:52 am
Dird (5/27/2013)
I would assume you could say "reduce fragmentation" or "reduce the number of pagesplits" interchangeably since as far as I understand an index rebuild would reduce both.
No, you can't. Page splits isn't something where there's some counter stored somewhere that when you rebuild is set to 0. A page split is an event, fragmentation is a property.
A page split occurs when SQL needs to insert into a page that's full. It splits the data on the page in half, half on the old page, half on a newly allocated page. This new page is then linked into the existing index. When that new page is out of order, this results in fragmentation. Page splits don't always result in fragmentation though.
Rebuilding the index sets the fragmentation back close to 0 (not exactly 0, but close)
A rebuild doesn't reduce page splits, in fact, it can result in there being more page splits after the rebuild than there were before because the pages are fuller after a rebuild than before.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 15 posts - 16 through 30 (of 31 total)
You must be logged in to reply to this topic. Login to reply