May 26, 2013 at 10:14 am
Hi guys,
Today/2am this morning I received my first call out (yay!) for a terrible server I hadn't heard of before. Besides the main poor points (backups on the same drive as datafiles, no compression & a filegroup dedicated to logging user activity somehow taking up 120GB over the past 1 month when the total database is 200gb including the 120gb logging; so 80gb without) I also looked at the filegroups dedicated to indexes. What I found was surprising & leads me to believe it's inaccurate, the following query:
SELECT ps.database_id, ps.OBJECT_ID,ps.index_id, b.name, ps.avg_fragmentation_in_percent
FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, NULL) AS ps
INNER JOIN sys.indexes AS b ON ps.OBJECT_ID = b.OBJECT_ID
AND ps.index_id = b.index_id
WHERE ps.database_id = DB_ID()
ORDER BY ps.OBJECT_ID
Lead to values like (note: I only copied the index names & fragmentation columns):
I_SEM_AGENT_USN 90.5616676317313
I_SEM_AGENT_COMP 98.3505154639175
I_SEM_AGENT_GRP 86.5384615384615
I_SEM_AGENT_RT 76.453488372093
I_SEM_AGENT_ID_PLUS 88.4057971014493
I_SEM_AGENT_PATTERN 97.7766013763896
I_SEM_AGENT_AGENT_VERSION 85.8789625360231
I_SEM_AGENT_STATUS 99.2708333333333
I_SEM_AGENT_AGENT_ID 98.9637305699482
Judging by this it would lead me to believe that only 1-2% of each page is being used? Which seems a little strange to me...does anybody notice an error in the query that may lead to the values being wrong? Or this column isn't supposed to be trusted? Or I need to be doing some rebuilds next weekend? 😛
Dird
May 26, 2013 at 11:04 am
Not necessarily. I would add the following columns to your query: ps.page_count, ps.avg_fragment_size_in_pages.
May 26, 2013 at 11:23 am
Lynn Pettis (5/26/2013)
Not necessarily. I would add the following columns to your query: ps.page_count, ps.avg_fragment_size_in_pages.
Here's some results with the extra columns...I take it you asked for these to make sure the indexes didnt just have 1 page?
nameavg_fragmentation_in_percentpage_countavg_fragment_size_in_pages
I_AGENT_SYSTEM_LOG_1_COMPUTER_ID_PLUS75.12194742883539007981.31537922373762
I_SERVER_CLIENT_LOG_1_CLIENT_ID83.18600332432183591711.18837927976813
I_SERVER_CLIENT_LOG_1_LOG_IDX99.22318898576143398511
I_AGENT_TRAFFIC_LOG_1_LOG_IDX99.21425143953941833921
I_AGENT_TRAFFIC_LOG_1_TIME_PLUS56.55940662310091813351.71507613733094
NULL40.516415049125313235617.1467806710714
NULL99.8127340823971152908.00958732805335
I_AGENT_BEHAVIOR_LOG_1_LOG_IDX99.24384638326991139981
I_SERVER_CLIENT_LOG_1_TIME30.37259465894351125623.18376467260642
I_SERVER_CLIENT_LOG_1_ID99.21343748588971107351
Do you know what the NULL index name represents? Table fragmentation?
Dird
May 26, 2013 at 11:27 am
The table is a heap (no clustered index). The index_id will be a 0 (zero). You may want to add this column to the query also: ps.forwarded_record_count.
May 26, 2013 at 11:27 am
Dird (5/26/2013)
Judging by this it would lead me to believe that only 1-2% of each page is being used?
That's not what fragmentation means. Nothing wrong with the query or the accuracy of the column, just the interpretation.
Fragmentation is a measure of what percentage of the pages in the index are 'out of order'. Out of order being where the logical order of the index (as defined by the index key values) and the physical order of the index within the data file are not the same. So a high logical fragmentation tells you nothing about what percentage of the pages are full of data, it tells you what percentage of the pages in the index have a physical order that doesn't match the logical order.
With a fragmentation that high, I would suspect there's some database shrink operations?
To see on average how full the pages are, you need to look at the avg_page_space_used column.
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 11:30 am
Dup
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 11:33 am
To use avg_page_space_used_in_percent you will also have to change your query to run in DETAILED mode, like this:
...
FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, 'DETAILED') AS ps
...
May 26, 2013 at 11:34 am
Oh OK, so it's just about the amount of page splits? Added the specified columns (assume the one is what Gail was requesting):
index_idnameavg_fragmentation_in_percentpage_countavg_fragment_size_in_pagesforwarded_record_countavg_page_space_used_in_percent
13I_AGENT_SYSTEM_LOG_1_COMPUTER_ID_PLUS75.12559969274429008781.31531934029481NULLNULL
13I_AGENT_SYSTEM_LOG_2_COMPUTER_ID_PLUS0.0171315663.7942570891851NULLNULL
10I_SERVER_CLIENT_LOG_1_CLIENT_ID83.18965637221473592551.18832693834348NULLNULL
5I_SERVER_CLIENT_LOG_1_LOG_IDX99.22318898576143398511NULLNULL
0NULL99.15917657291973310178.06532332732323NULLNULL
0NULL22.897467619360231660434.3313814790718NULLNULL
7I_AGENT_TRAFFIC_LOG_1_LOG_IDX99.21425143953941833921NULLNULL
6I_AGENT_BEHAVIOR_LOG_1_LOG_IDX99.24384638326991139981NULLNULL
Dird
May 26, 2013 at 11:44 am
Dird (5/26/2013)
Oh OK, so it's just about the amount of page splits?
Not necessarily. Other things than page splits can cause fragmentation, like database/file shrink operations.
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 11:48 am
Autoshrink is disabled in 10.5. There wouldn't be any manual shrinking because there's no real maintenance for our SQL Servers =//
The 'DETAILED' option is so slow >_<
Dird
May 26, 2013 at 11:51 am
Dird (5/26/2013)
Autoshrink is disabled in 10.5. There wouldn't be any manual shrinking because there's no real maintenance for our SQL Servers =//The 'DETAILED' option is so slow >_<
Dird
You won't get the avg_page_space_used_in_percent unless you run it using 'DETAILED'. In 'LIMITED" (or NULL in your case) this value is always null.
May 26, 2013 at 11:53 am
Lynn Pettis (5/26/2013)
You won't get the avg_page_space_used_in_percent unless you run it using 'DETAILED'. In 'LIMITED" (or NULL in your case) this value is always null.
Yep, 17 minutes & counting for the result set -_
May 26, 2013 at 11:58 am
Dird (5/26/2013)
Autoshrink is disabled in 10.5.
???
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 12:01 pm
The AutoShrink setting for the database (properties -> options -> AutoShrink). It's not what you were talking about?
Here's the extra column in the query:
index_idnameavg_fragmentation_in_percentpage_countavg_fragment_size_in_pagesforwarded_record_countavg_page_space_used_in_percent
10I_SERVER_CLIENT_LOG_1_CLIENT_ID83.19381286074483593261.1882827200545NULL64.1397949098097
5I_SERVER_CLIENT_LOG_1_LOG_IDX99.22318898576143398511NULL67.8164319248826
7I_AGENT_TRAFFIC_LOG_1_LOG_IDX99.21425143953941833921NULL66.8855942673585
4I_AGENT_TRAFFIC_LOG_1_TIME_PLUS56.57114188682571813841.71474489265355NULL74.9793427230047
4I_AGENT_TRAFFIC_LOG_2_TIME_PLUS01454481212.06666666667NULL99.5304917222634
0NULL40.516415049125313235617.146780671071403.01700024709661
7I_AGENT_TRAFFIC_LOG_2_LOG_IDX01305731450.81111111111NULL99.9996911292315
0NULL99.81287684524221153738.00923290524124095.6287867556214
6I_AGENT_BEHAVIOR_LOG_1_LOG_IDX99.24384638326991139981NULL67.7993946132938
May 26, 2013 at 12:17 pm
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
These also need to be run using 'DETAILED'.
Viewing 15 posts - 1 through 15 (of 31 total)
You must be logged in to reply to this topic. Login to reply