Post reply

UPDATE STATS FULL SCAN and MEMORY

  • April 9, 2014 at 9:26 pm

    #290118

    Hi,

    I just have a quick question that maybe someone can clear up for me.

    I noticed today a session that was executing a FULL SCAN update as follows:

    UPDATE STATISTICS [XXXX].[XXXX].[XXXX] [_WA_Sys_00000009_318D45CA] WITH FULLSCAN

    When I checked the sys.dm_exec_query_memory_grants DMV for the session I could see the following values:

    requested_memory_kb granted_memory_kb used_memory_kb max_used_memory_kb

    145,705,216 145,705,216 139,977,336 139,980,408

    When I checked the Properties of the Statistic I can see it is on a varchar(3) field when there are only 3 different values in there - all char(1)

    The total size of the data in the table according to the Disk Usage By Top Table Report is 199,680,712KB

    So my question is this...

    For the UPDATE STATS on this one column with FULL SCAN, does SQL Server read the entire table into the Buffer Pool. If so then if the table had 199,680,712KB of data then why did the session request 145,705,216KB.

    Or does SQL Server just read the column and ClusteredIndex Key into the Buffer Pool?

    Thanks in advance.

  • April 10, 2014 at 2:41 am

    #1704778

    Depends on what indexes are available.

    The memory request is not the buffer pool space. It's workspace memory for things like sorts, hashes or other query operator operations which need memory.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

Viewing 2 posts - 1 through 1 (of 1 total)

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