locked
SQL Server Maintenance RRS feed

  • Question

  • Select index_id, avg_fragmentation_in_percent, avg_page_space_used_in_percent
    From sys.dm_db_index_physical_stats(@dbID,@objectID,null,null,null) Where index_level=0

    Every-time I am running above query to any table but found avg_page_space_used_in_percent is null while I have inserted many records many time on table at testing environment.
    What is the reason and issue?

    Kumar J

    Sunday, September 4, 2016 5:46 AM

Answers

  • Hi Kumar

    You can get the details of index frag.. with different like SAMPLE,LIMITED,DETAIL.

    so as per BOL  see the one higlighted that mightbe reason.

    Please go through and understand-

    https://msdn.microsoft.com/en-us/library/ms188917.aspx

    avg_page_space_used_in_percent float Average percentage of available data storage space used in all pages.

    For an index, average applies to the current level of the b-tree in the IN_ROW_DATA allocation unit.

    For a heap, the average of all data pages in the IN_ROW_DATA allocation unit.

    For LOB_DATA or ROW_OVERFLOW DATA allocation units, the average of all pages in the allocation unit.

    NULL when mode = LIMITED.


    Regards, S_NO "_"

    • Marked as answer by kumar J Sunday, September 4, 2016 7:45 PM
    Sunday, September 4, 2016 6:16 AM
  • Also go through the

    A quick look at: dm_db_index_physical_stats
    https://www.simple-talk.com/blogs/quick-look-at-dm_db_index_physical_stats/


    Regards, S_NO "_"

    • Marked as answer by kumar J Sunday, September 4, 2016 7:48 PM
    Sunday, September 4, 2016 6:17 AM

All replies

  • Hi Kumar

    You can get the details of index frag.. with different like SAMPLE,LIMITED,DETAIL.

    so as per BOL  see the one higlighted that mightbe reason.

    Please go through and understand-

    https://msdn.microsoft.com/en-us/library/ms188917.aspx

    avg_page_space_used_in_percent float Average percentage of available data storage space used in all pages.

    For an index, average applies to the current level of the b-tree in the IN_ROW_DATA allocation unit.

    For a heap, the average of all data pages in the IN_ROW_DATA allocation unit.

    For LOB_DATA or ROW_OVERFLOW DATA allocation units, the average of all pages in the allocation unit.

    NULL when mode = LIMITED.


    Regards, S_NO "_"

    • Marked as answer by kumar J Sunday, September 4, 2016 7:45 PM
    Sunday, September 4, 2016 6:16 AM
  • Also go through the

    A quick look at: dm_db_index_physical_stats
    https://www.simple-talk.com/blogs/quick-look-at-dm_db_index_physical_stats/


    Regards, S_NO "_"

    • Marked as answer by kumar J Sunday, September 4, 2016 7:48 PM
    Sunday, September 4, 2016 6:17 AM
  • Thank u sir.

    Kumar J

    • Marked as answer by kumar J Sunday, September 4, 2016 7:48 PM
    • Unmarked as answer by kumar J Sunday, September 4, 2016 7:48 PM
    Sunday, September 4, 2016 7:46 PM