Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 1234»»»

Fragmentation accuracy? 98% fragmented?!! Expand / Collapse
Author
Message
Posted Sunday, May 26, 2013 10:14 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, July 1, 2014 3:20 AM
Points: 196, Visits: 650
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? :P

Dird



Dird // Junior DBA
11g OCA
10.5 newbie
Post #1456870
Posted Sunday, May 26, 2013 11:04 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Yesterday @ 9:58 AM
Points: 20,744, Visits: 32,556
Not necessarily. I would add the following columns to your query: ps.page_count, ps.avg_fragment_size_in_pages.


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)
Post #1456874
Posted Sunday, May 26, 2013 11:23 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, July 1, 2014 3:20 AM
Points: 196, Visits: 650
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 // Junior DBA
11g OCA
10.5 newbie
Post #1456879
Posted Sunday, May 26, 2013 11:27 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Yesterday @ 9:58 AM
Points: 20,744, Visits: 32,556
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.


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)
Post #1456881
Posted Sunday, May 26, 2013 11:27 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 6:05 AM
Points: 40,258, Visits: 36,681
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 2008, MVP
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

Post #1456882
Posted Sunday, May 26, 2013 11:30 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 6:05 AM
Points: 40,258, Visits: 36,681
Dup


Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
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

Post #1456883
Posted Sunday, May 26, 2013 11:33 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Yesterday @ 9:58 AM
Points: 20,744, Visits: 32,556
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
...





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)
Post #1456884
Posted Sunday, May 26, 2013 11:34 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, July 1, 2014 3:20 AM
Points: 196, Visits: 650
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 // Junior DBA
11g OCA
10.5 newbie
Post #1456885
Posted Sunday, May 26, 2013 11:44 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 6:05 AM
Points: 40,258, Visits: 36,681
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 2008, MVP
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

Post #1456887
Posted Sunday, May 26, 2013 11:48 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, July 1, 2014 3:20 AM
Points: 196, Visits: 650
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 // Junior DBA
11g OCA
10.5 newbie
Post #1456888
« Prev Topic | Next Topic »

Add to briefcase 1234»»»

Permissions Expand / Collapse