Post reply

I cannot finish COUNT (*) query on a table with more than billion records.

  • September 15, 2015 at 2:41 am

    #300486

    hi,

    I have a table in PRODUCTION server with more than a billion records. I am running the below query to check the exact number of records in it.

    select count (*) from [dbo].[tbl_name] with (nolock). but the query is not completing.

    The session_ID for the above query is blocking itself and the wait_type is CXPACKET, status : SUSPENDED

    SQL server = 2012 (ent)

    DB = 2005 (compatibility mode)

    Question 2: i also need to archive this table but cannot find a suitable approach as it is very busy and takes ages for any process to run.

    All help is appreciated

  • September 15, 2015 at 2:51 am

    #1826645

    With such a big table, you might want to use table partitioning for archiving purposes.

    Regarding the question: there are alternatives to get the row count:

    SQL Server–HOW-TO: quickly retrieve accurate row count for table

    Need an answer? No, you need a question
    My blog at https://sqlkover.com.
    MCSE Business Intelligence - Microsoft Data Platform MVP

  • September 15, 2015 at 3:38 am

    #1826662

    You can use COUNT_BIG(*) but it may take AGES to run ... getting the Row Count from the clustered index / heap might well be a better solution - unless you have a WHERE clause 🙁

    EDIT: sorry, misread the question. COUNT() is good for 2,147,483,647 so no need for COUNT_BIG()

  • September 15, 2015 at 7:55 am

    #1826747

    Pretty easy to gets a count in big tables. Use sys.partitions, sys.tables and sys.allocation_units.

  • September 16, 2015 at 12:49 am

    #1826978

    tauseef.jan (9/15/2015)

    hi,

    I have a table in PRODUCTION server with more than a billion records. I am running the below query to check the exact number of records in it.

    select count (*) from [dbo].[tbl_name] with (nolock). but the query is not completing.

    The session_ID for the above query is blocking itself and the wait_type is CXPACKET, status : SUSPENDED

    SQL server = 2012 (ent)

    DB = 2005 (compatibility mode)

    Question 2: i also need to archive this table but cannot find a suitable approach as it is very busy and takes ages for any process to run.

    All help is appreciated

    Quick question, is the table a heap or clustered index?

    😎

  • September 16, 2015 at 3:12 am

    #1827004

    it is a indexed table.

  • September 16, 2015 at 3:41 am

    #1827008

    I like to use sp_spaceused with the table name.

    That stored procedure uses dm_dp_partition_stats internally.

  • September 16, 2015 at 4:39 am

    #1827013

    tauseef.jan (9/16/2015)

    it is a indexed table.

    You have now a couple of useful suggestions. Did you try any of them?

    Need an answer? No, you need a question
    My blog at https://sqlkover.com.
    MCSE Business Intelligence - Microsoft Data Platform MVP

  • September 22, 2015 at 9:23 am

    #1828464

    Why not just check the rowcnt value in sys.sysindexes for the OBJECT_ID(tablename)

    select max(rows) from sys.sysindexes where id = object_id('<table_name>')

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply