Post reply

Why is the MS calculation for SQL Server table size not reflect Storage in SSMS

  • November 24, 2021 at 11:03 pm

    #3956667

    Hi Jeff

    Apologies for delaying in replying. Ive been ill with a bad cold .

     

    This is what I see of the the table when i run sp_help 'dbo.SIData_222222_88_20211103_1436'

    Attachments:
    You must be logged in to view attached files.

  • November 24, 2021 at 11:13 pm

    #3956669

    i see that is_read_committed_snapshot_on is set to 0

    Attachments:
    You must be logged in to view attached files.

  • November 24, 2021 at 11:14 pm

    #3956671

    I ran the average page fullness using sys.dm_db_index_physical_stats along with some other columns which are displayed in the attached screenshot

     

     

    Attachments:
    You must be logged in to view attached files.

  • November 25, 2021 at 4:17 am

    #3956704

    Weegee71 wrote:

    Hi Jeff

    Apologies for delaying in replying. Ive been ill with a bad cold .

    This is what I see of the the table when i run sp_help 'dbo.SIData_222222_88_20211103_1436'

    At least it's "only" a cold. 😀  Hope you feel better soon and can wolf down a good piece of pumpkin pie and a glass of milk tomorrow. 😀

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".
    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)
    Intro to Tally Tables and Functions

  • November 25, 2021 at 4:28 am

    #3956705

    Man, I thought we might have had it with the RCSI thing.  You've answered all the other questions with the graphics.  I see absolutely nothing in any of that that could be causing this issue.

    The only thing I could ask now is for you to run the following code and then do the same checks that you did before just to see if it's affected the same way.  If it is, you may have uncovered some whacko bug in SQL Server 2019.

    -- DROP TABLE IF EXISTS dbo.JustaTest;
     CREATE TABLE dbo.JustaTest
            (
             ReadingDateTime    DATETIME2(2) NULL
            ,ReadingValue       REAL         NULL
            ) ON [PRIMARY]
    ;
    GO
     INSERT INTO dbo.JustaTest
            (ReadingDateTime,ReadingValue)
     SELECT TOP 10000000 
            GETDATE(),1.0
       FROM      sys.all_columns ac1
      CROSS JOIN sys.all_columns ac2
    ;
    GO

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".
    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)
    Intro to Tally Tables and Functions

  • November 29, 2021 at 10:17 am

    #3957754

    Hi Jeff

    Apoolgies for delaying in replying. Im back at work after feeling poorly

    Now i ran the code you supplied to create the test table. Attached are the screen shots from the checks i ran

    after creating the table

     

    Attachments:
    You must be logged in to view attached files.

  • November 29, 2021 at 12:41 pm

    #3957816

    do you mind running this again and ensuring that the dbname is correct

    select snapshot_isolation_state
         , snapshot_isolation_state_desc
         , is_read_committed_snapshot_on
         , is_memory_optimized_elevate_to_snapshot_on
    from sys.databases
    WHERE database_id = DB_ID('yourdbname')

Viewing 7 posts - 16 through 21 (of 21 total)

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