-4

SQL Server version is 2014 Developer SP1. Trace Flags 272, 610, 1118, 1206, 1222, 8048, 9481 are globally turned on.

We had to turn on flag 9481 since the new cardinality estimator badly effected many of the query plans. The query has no sort, hash or paralellism. When I run the query no results returned for minutes.

Ideal memory is also reported over 1.3TB from sys.dm_exec_query_memory_grants dmv during execution. (WhyGiganticDesiredMemory.queryanalysis)

I ran a very similar query. In ActualPlan.queryanalysis Desired memory is 124GB and granted is 44GB vs in ActualPlanWithLessMemoryGrant.queryanalysis Desired is only 10MB.

The only difference between them is the left most Index seek underneath the Nested Loops removed from the latter ( I added an invalid filter 1 = 0 in the join condition to eliminate the table from the plan).

My query looks like this:

SELECT  CAST('C' AS CHAR(1)) AS [Action]
      , f.LoadId
      , u.UnvPclId
      , p.*    --- [about 480 columns, Daniel Hutmacher's edit]
FROM    [tExtract].[ExtractCounty] f
INNER JOIN [tControl].[VersionControl] vc ON Id = 1
INNER JOIN [tTax].[Property] pk WITH ( NOLOCK, FORCESEEK ( UpdateVersion_CntyCd ( UpdateVersion ) ) ) ON pk.UpdateVersion > vc.StartRowVersion
                                                                                                         AND pk.UpdateVersion <= vc.EndRowVersion
                                                                                                         AND pk.CntyCd = f.CntyCd
INNER JOIN [tTax].[Property] p WITH ( NOLOCK, FORCESEEK, INDEX = 1 ) ON p.[CntyCd] = pk.[CntyCd]
                                                                        AND p.[PclId] = pk.[PclId]
                                                                        AND p.[PclSeqNbr] = pk.[PclSeqNbr]
LEFT OUTER JOIN tCommon.UnvPclId u WITH ( NOLOCK, FORCESEEK ( 1 ( CntyCd, Edition, PclId, PclSeqNbr ) ) ) ON u.Edition = f.Edition
                                                                                                             AND u.CntyCd = f.CntyCd
                                                                                                             AND u.PclId = p.PclId
                                                                                                             AND u.PclSeqNbr = p.PclSeqNbr
                                                                                                             AND 1 = 0

WHERE   f.SchemaId = 1 /*tTax*/
        AND f.FullExtract = 0
        AND EXISTS ( SELECT 1
                     FROM   [tExtract].[TableNotCompleted] dt
                     WHERE  dt.TableId = 57 /*Property*/)

Estimated Query Plan

High Serial Desired Memory

Memory grant as query running

WhyGiganticDesiredMemory.queryanalysis

ActualPlanWithLessMemoryGrant.queryanalysis

ActualPlan.queryanalysis

  • 1
    Comments are not for extended discussion; this conversation has been moved to chat. Please ensure any useful updates from the chat discussion are reflected in the question body. Thank you. – Paul White Feb 26 '16 at 5:48
17

The memory grant is for prefetching and batch sorting on the nested loops joins. See the "Optimized" and "WithUnorderedPrefetch" properties.

There is a documented trace flag to turn Optimized off - TF 2340; otherwise, the optimizer decides based on cardinality estimates.

You may be able to tweak the query to lower the expected row counts such that the optimizer decides not to prefetch or batch sort. Such a rewrite may also do away with the 'need' for so many hints as well.

More information:

The underlying issue has been addressed in SQL Server 2016 and will be back-ported to SQL Server 2014 at some stage. From the linked post by Pedro Lopes:

...in SQL Server 2016 RC0 we have changed the behavior to maintain the advantage of the optimization, but now the max grant limit is based on the available memory grant space.


Answer copied from your identical question on SQLPerformance.com

5

My guess here is when you see badly constructed execution plans on huge tables, chances are that you have outdated statistics. When Statistics are outdated, Bad execution plans are used which seriously affects performance of the SQL Server in General. By default, the update auto stats option updates statistics when 20% + 500 records are modified... So if you have 100 Million records it will only update statistics once 20 Million records have been modified which can be a very long time (depending on the frequency of insert, updates and deletes) before statistics are updated. I had a recent similar issue where I had to build a maintenance that would Re-index, Reorganize and update statistics using a percentage of rows for sampling.

My maintenance takes care of checking for fragmentation and statistics. I use this to check for Fragmentation of Indexes and loop through every one of the result:

SELECT t.name AS TableName, sch.name as SchemaName, i.name AS IndexName, s.avg_fragmentation_in_percent, ROW_NUMBER() OVER (PARTITION BY t.name order by t.name) as RowNum, TR.RowCnt
            FROM SYS.TABLES t
            INNER JOIN sys.schemas sch on t.schema_id=sch.schema_id
            JOIN SYS.INDEXES i ON t.object_id = i.object_id
            JOIN SYS.DM_DB_INDEX_PHYSICAL_STATS(DB_ID(),NULL,NULL,NULL,NULL) s ON t.object_id = s.object_id AND i.index_id = s.index_id
            LEFT JOIN (SELECT o.OBJECT_ID, ddps.row_count as RowCnt
                            FROM sys.indexes AS i
                            INNER JOIN sys.objects AS o ON i.OBJECT_ID = o.OBJECT_ID
                            INNER JOIN sys.dm_db_partition_stats AS ddps ON i.OBJECT_ID = ddps.OBJECT_ID
                            AND i.index_id = ddps.index_id
                            WHERE i.index_id < 2
                            AND o.is_ms_shipped = 0) TR ON TR.object_id = t.object_id
            WHERE t.type  = 'U'
            AND s.avg_fragmentation_in_percent > 5
            AND i.name is not null

and Then use this to go through the Statistics and update the statistics that need to be updated:

SELECT  OBJECT_NAME(sp.OBJECT_ID) AS TableName, SchemaName=sch.name,
                s.name AS StatName,
                CASE    WHEN pa.rows_in_table between 500 AND  500000 AND sp.modification_counter>(pa.rows_in_table*0.15) AND ISNULL(sp.last_updated,'1900-01-01')<DATEADD(DAY,-3,GETDATE()) then 100
                        WHEN pa.rows_in_table BETWEEN 500000 AND 1000000 AND sp.modification_counter>(pa.rows_in_table*0.10) AND ISNULL(sp.last_updated,'1900-01-01')<DATEADD(DAY,-3,GETDATE()) then 50
                        WHEN pa.rows_in_table BETWEEN 1000001 AND 5000000 AND sp.modification_counter>(pa.rows_in_table*0.05) AND ISNULL(sp.last_updated,'1900-01-01')<DATEADD(DAY,-3,GETDATE()) then 25
                        WHEN pa.rows_in_table BETWEEN 5000001 AND 10000000 AND sp.modification_counter>(pa.rows_in_table*0.025) AND ISNULL(sp.last_updated,'1900-01-01')<DATEADD(DAY,-3,GETDATE()) then 10
                        WHEN pa.rows_in_table BETWEEN 10000001 AND 50000000 AND sp.modification_counter>(pa.rows_in_table*0.02) AND ISNULL(sp.last_updated,'1900-01-01')<DATEADD(DAY,-3,GETDATE()) then 2
                        WHEN pa.rows_in_table BETWEEN 50000001 AND 100000000 AND sp.modification_counter>(pa.rows_in_table*0.01) AND ISNULL(sp.last_updated,'1900-01-01')<DATEADD(DAY,-3,GETDATE()) then 1
                        WHEN pa.rows_in_table>100000000 AND sp.modification_counter>(pa.rows_in_table*0.005) AND ISNULL(sp.last_updated,'1900-01-01')<DATEADD(DAY,-3,GETDATE()) then 3000000
                        ELSE 0 END StatsToUpdate
        FROM SYS.STATS AS s
        OUTER APPLY SYS.DM_DB_STATS_PROPERTIES(s.OBJECT_ID,s.stats_id) AS sp
        INNER JOIN (SELECT ta.OBJECT_ID, ta.schema_id, sum(pa.rows) rows_in_table
                        FROM SYS.TABLES ta
                        INNER JOIN SYS.PARTITIONS pa ON pa.OBJECT_ID = ta.OBJECT_ID
                        INNER JOIN SYS.SCHEMAS sc   ON ta.SCHEMA_ID = sc.SCHEMA_ID
                        WHERE ta.is_ms_shipped = 0 AND pa.index_id IN (1,0) 
                    GROUP BY ta.OBJECT_ID, ta.schema_id) as pa  ON pa.OBJECT_ID = s.OBJECT_ID
        INNER JOIN sys.schemas sch on pa.schema_id=sch.schema_id
        WHERE ISNULL(modification_counter,0) <> 0
        AND CASE    WHEN pa.rows_in_table between 500 AND  500000 AND sp.modification_counter>(pa.rows_in_table*0.15) AND ISNULL(sp.last_updated,'1900-01-01')<DATEADD(DAY,-3,GETDATE()) then 100
                        WHEN pa.rows_in_table BETWEEN 500000 AND 1000000 AND sp.modification_counter>(pa.rows_in_table*0.10) AND ISNULL(sp.last_updated,'1900-01-01')<DATEADD(DAY,-3,GETDATE()) then 50
                        WHEN pa.rows_in_table BETWEEN 1000001 AND 5000000 AND sp.modification_counter>(pa.rows_in_table*0.05) AND ISNULL(sp.last_updated,'1900-01-01')<DATEADD(DAY,-3,GETDATE()) then 25
                        WHEN pa.rows_in_table BETWEEN 5000001 AND 10000000 AND sp.modification_counter>(pa.rows_in_table*0.025) AND ISNULL(sp.last_updated,'1900-01-01')<DATEADD(DAY,-3,GETDATE()) then 10
                        WHEN pa.rows_in_table BETWEEN 10000001 AND 50000000 AND sp.modification_counter>(pa.rows_in_table*0.02) AND ISNULL(sp.last_updated,'1900-01-01')<DATEADD(DAY,-3,GETDATE()) then 2
                        WHEN pa.rows_in_table BETWEEN 50000001 AND 100000000 AND sp.modification_counter>(pa.rows_in_table*0.01) AND ISNULL(sp.last_updated,'1900-01-01')<DATEADD(DAY,-3,GETDATE()) then 1
                        WHEN pa.rows_in_table>100000000 AND sp.modification_counter>(pa.rows_in_table*0.005) AND ISNULL(sp.last_updated,'1900-01-01')<DATEADD(DAY,-3,GETDATE()) then 3000000
                        ELSE 0 END>0

Note that I chose these numbers for the sampling of the update stats, but they need to changed on a case by case basis.

  • note that in your example, the modification threshold would be met at 20,000,500 modifications to the leading column of the statistics object. once met, the statistic is marked as stale, the plan is invalidated, and the next time a query plan is compiled using that statistic, an auto update (if enabled) will resample the statistic and compile a new plan (unless auto update async is enabled). also, rebuilding an index necessarily samples the underlying stat with fullscan – swasheck Feb 25 '16 at 21:32

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.