SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Fragmentation accuracy? 98% fragmented?!!


Fragmentation accuracy? 98% fragmented?!!

Author
Message
Dird
Dird
Ten Centuries
Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

Group: General Forum Members
Points: 1165 Visits: 821
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? Tongue

Dird


Dird
Lynn Pettis
Lynn Pettis
SSC Guru
SSC Guru (90K reputation)SSC Guru (90K reputation)SSC Guru (90K reputation)SSC Guru (90K reputation)SSC Guru (90K reputation)SSC Guru (90K reputation)SSC Guru (90K reputation)SSC Guru (90K reputation)

Group: General Forum Members
Points: 90468 Visits: 38945
Not necessarily. I would add the following columns to your query: ps.page_count, ps.avg_fragment_size_in_pages.

Cool
Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Dird
Dird
Ten Centuries
Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

Group: General Forum Members
Points: 1165 Visits: 821
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?
name   avg_fragmentation_in_percent   page_count   avg_fragment_size_in_pages
I_AGENT_SYSTEM_LOG_1_COMPUTER_ID_PLUS 75.1219474288353 900798 1.31537922373762
I_SERVER_CLIENT_LOG_1_CLIENT_ID 83.1860033243218 359171 1.18837927976813
I_SERVER_CLIENT_LOG_1_LOG_IDX 99.2231889857614 339851 1
I_AGENT_TRAFFIC_LOG_1_LOG_IDX 99.2142514395394 183392 1
I_AGENT_TRAFFIC_LOG_1_TIME_PLUS 56.5594066231009 181335 1.71507613733094
NULL 40.5164150491253 132356 17.1467806710714
NULL 99.812734082397 115290 8.00958732805335
I_AGENT_BEHAVIOR_LOG_1_LOG_IDX 99.2438463832699 113998 1
I_SERVER_CLIENT_LOG_1_TIME 30.3725946589435 112562 3.18376467260642
I_SERVER_CLIENT_LOG_1_ID 99.2134374858897 110735 1


Do you know what the NULL index name represents? Table fragmentation?

Dird


Dird
Lynn Pettis
Lynn Pettis
SSC Guru
SSC Guru (90K reputation)SSC Guru (90K reputation)SSC Guru (90K reputation)SSC Guru (90K reputation)SSC Guru (90K reputation)SSC Guru (90K reputation)SSC Guru (90K reputation)SSC Guru (90K reputation)

Group: General Forum Members
Points: 90468 Visits: 38945
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.

Cool
Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
GilaMonster
GilaMonster
SSC Guru
SSC Guru (212K reputation)SSC Guru (212K reputation)SSC Guru (212K reputation)SSC Guru (212K reputation)SSC Guru (212K reputation)SSC Guru (212K reputation)SSC Guru (212K reputation)SSC Guru (212K reputation)

Group: General Forum Members
Points: 212819 Visits: 46259
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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass


GilaMonster
GilaMonster
SSC Guru
SSC Guru (212K reputation)SSC Guru (212K reputation)SSC Guru (212K reputation)SSC Guru (212K reputation)SSC Guru (212K reputation)SSC Guru (212K reputation)SSC Guru (212K reputation)SSC Guru (212K reputation)

Group: General Forum Members
Points: 212819 Visits: 46259
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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass


Lynn Pettis
Lynn Pettis
SSC Guru
SSC Guru (90K reputation)SSC Guru (90K reputation)SSC Guru (90K reputation)SSC Guru (90K reputation)SSC Guru (90K reputation)SSC Guru (90K reputation)SSC Guru (90K reputation)SSC Guru (90K reputation)

Group: General Forum Members
Points: 90468 Visits: 38945
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
...




Cool
Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Dird
Dird
Ten Centuries
Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

Group: General Forum Members
Points: 1165 Visits: 821
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_id   name   avg_fragmentation_in_percent   page_count   avg_fragment_size_in_pages   forwarded_record_count   avg_page_space_used_in_percent
13 I_AGENT_SYSTEM_LOG_1_COMPUTER_ID_PLUS 75.1255996927442 900878 1.31531934029481 NULL NULL
13 I_AGENT_SYSTEM_LOG_2_COMPUTER_ID_PLUS 0.01 713156 63.7942570891851 NULL NULL
10 I_SERVER_CLIENT_LOG_1_CLIENT_ID 83.1896563722147 359255 1.18832693834348 NULL NULL
5 I_SERVER_CLIENT_LOG_1_LOG_IDX 99.2231889857614 339851 1 NULL NULL
0 NULL 99.1591765729197 331017 8.06532332732323 NULL NULL
0 NULL 22.8974676193602 316604 34.3313814790718 NULL NULL
7 I_AGENT_TRAFFIC_LOG_1_LOG_IDX 99.2142514395394 183392 1 NULL NULL
6 I_AGENT_BEHAVIOR_LOG_1_LOG_IDX 99.2438463832699 113998 1 NULL NULL



Dird


Dird
GilaMonster
GilaMonster
SSC Guru
SSC Guru (212K reputation)SSC Guru (212K reputation)SSC Guru (212K reputation)SSC Guru (212K reputation)SSC Guru (212K reputation)SSC Guru (212K reputation)SSC Guru (212K reputation)SSC Guru (212K reputation)

Group: General Forum Members
Points: 212819 Visits: 46259
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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass


Dird
Dird
Ten Centuries
Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

Group: General Forum Members
Points: 1165 Visits: 821
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


Dird
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search