SQL Server and fragmentation

Just wanted to share a quick tip on querying on sql server fragmentation and fixing things if needed.

On our production server, which was setup by our sysadmin with all defaults as per configuration.
I was also told that there is a weekly job setup for rebuilding indexes if there is a fragmentation. I did accepted everything told at a face value. But now its time for some treasure hunting.

I had to do some sql BOL searching as it was not clear to me how/where I could find such stats. Finally came across sys.db_dm_index_physical_stats function which needs several arguments and based on it it will return details about an object's fragmentation.

Lets look at this function first.

sys.dm_db_index_physical_stats ( DB_ID, OBJECT_ID, INDEX_ID, PARTITION_NO, MODE);

Here db_id, and object_id can take [ null | 0 | default ] as a value and all mean same. To narrow down the results, you need to supply actual value.

Index_id could be [ null | -1 | default ], and all mean same, alternatively you can provide 0 if object is a heap, or actual id of an index if object is an index.

Partition_no is the partition id of index or heap. Alternatively it can be one of [ null | 0 | default ] as a value and all mean same.

Mode is a scan level used to gather the information.
It can be any of [ default | null | limited | sampled | detailed ], where default (null ) is limited.

So to begin with I took one of our biggest table and made the query as follows:

select * from sys.dm_db_index_physical_stats(DB_ID(),OBJECT_ID('dbo.Survey'),1,0,'DETAILED');

avg_fragmentation_in_percent fragment_count page_count
98.2717326087911 203005 205,003

As you can see, above results make anybody worry. It seems like either the job is not in place / is disabled or simply not working,

I tried to do initial defrag:

alter table