Forum Discussion

Kanishka_Basak's avatar
Kanishka_Basak
Copper Contributor
Dec 22, 2020

DBCC SHOW CONTIG returns 0 records in SQL Server 2014 but 2 records in SQL Server 2016

I have a table with records that has Column Store Index. When I run the following in the SQL Server 2014 Version I get no rows.

DBCC SHOWCONTIG ('dbo.myTable') WITH TABLERESULTS, ALL_INDEXES, NO_INFOMSGS

According to one of the documentations(refer below), that I case across, it made sense as DBCC SHOWCONTIG has some restrictions with certain data types like text,varchar,nvarchar etc, and myTable has some of them.

However, when I run the same on a SQL Server 2016 version, I get 2 rows. What I am trying to understand is, does the restriction of DBCC SHOWCONTIG still hold with 2016 edition, or am I missing anything. Were there any changes made in respect to SQL Server 2016 that might give this result. 

I do have plans to replace DBCC SHOWCONTIG with sys.dm_db_index_physical_stats, but, still some information about this would be great.

https://docs.microsoft.com/en-us/sql/t-sql/database-console-commands/dbcc-showcontig-transact-sql?view=sql-server-ver15

SQL Server 2016 and 2014 Versions that I using;

 

Microsoft SQL Server 2016 (SP2-CU13) (KB4549825) - 13.0.5820.21 (X64) May 22 2020 07:24:35 Copyright (c) Microsoft Corporation Enterprise Edition: Core-based Licensing (64-bit) on Windows Server 2016 Standard 10.0 <X64> (Build 14393: ) (Hypervisor)

 

Microsoft SQL Server 2014 (SP2-CU17-GDR) (KB4505419) - 12.0.5659.1 (X64) May 24 2019 19:09:40 Copyright (c) Microsoft Corporation Enterprise Edition: Core-based Licensing (64-bit) on Windows NT 6.3 <X64> (Build 9600: ) (Hypervisor)

 

  • Kanishka_Basak I know I'm not directly answering your question, but do you know the concepts of fragmentation DBCC SHOWCONTIG deals with don't apply do columnstore indexes?

    Do you know columnstore indexes have completely different problems related to row groups and the maintenance needs to check sys.column_store_row_groups and dm_db_column_store_row_group_physical_stats ?

     

     

    • Kanishka_Basak's avatar
      Kanishka_Basak
      Copper Contributor

      DennesTorres  I am not aware of that, but looks to me that what you mention may be playing a part here? is that correct?

      • DennesTorres's avatar
        DennesTorres
        MVP

        Kanishka_Basak IMHO, more than that. You shouldn't be concerned about SHOWCONTIG at all. For columnstore indexes, the structure is different, the maintenance is different as well. I don't have a link now, but It's not difficult to find more about them and you will need the two DMV's I mentioned.

         

         

Resources