Using (or not) query HINTS to speed up slow statement

  • The following statement returns 9000 rows in about 5 minutes on both SQL Server 2014 and SQL Server 2019. It basically reads Corporations along with their "IsArchived" attribute and their current name after fetching the "NameID" from an historical table. All required indexed are defined and rebuilt. Statistics are updated.

    SELECT
    C.CorporationID,
    EA.IsArchived,
    EN.Name
    FROM Corporation C
    LEFT JOIN EntityAttributes EA ON EA.EntityID = C.CorporationID
    OUTER APPLY (
    SELECT TOP 1 NameID FROM CorporationHistory
    WHERE CorporationID = C.CorporationID AND EffectiveDate <= @RefDate
    ORDER BY EffectiveDate DESC
    ) CH
    LEFT JOIN EntityName EN ON EN.EntityNameID = CH.NameID
    WHERE EA.IsArchived = 'F' AND EN.Name IS NOT NULL

    However, there are several ways to make this statement run in less than 1 second and I wonder if some of you ever experienced a similar situation.

    1. Add OPTION (FORCE ORDER)

    2. Add either LOOP, MERGE or HASH to either of the two LEFT JOINs

    3. Add a dummy join LOOP, MERGE or HASH to the statement (LEFT LOOP JOIN (SELECT NULL X) XX ON XX.X IS NULL)

    4. Comment the WHERE clause or either "EA.IsArchived = 'F'" or "EN.Name IS NOT NULL" in the WHERE clause (theoretical since it changes the result)

    5. Replace "EA.IsArchived = 'F'" with "ISNULL(EA.IsArchived, 'F') = 'F'" in the WHERE clause (even though there is a NOT NULL constraint on the field)

    Every of these options generate similar execution plans with small differences while the original statement is the only one using lazy spools.

    It seems to me that this is one of the rare cases where SQL Server is unable to generate a good plan (but I have been proven wrong so many times thinking this was the case!)

    I am strongly inclined to use solution #5 but I would like to understand why I have to do it.

    Any comments?

  • cmartel 20772 wrote:

    ... All required indexed are defined ...

    Can't just take your word for that, still need to see DDL for the tables, including all index definitions.

    SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who give you the shirt off *someone else's* back.

  • #5 is completely wrong approach.  You should never use ISNULL() in a WHERE clause.  Instead move the condition from the WHERE clause to the LEFT JOIN.  You need to that anyway, since otherwise it's effectively an INNER JOIN.

    SELECT
    C.CorporationID,
    EA.IsArchived,
    EN.Name
    FROM Corporation C
    LEFT JOIN EntityAttributes EA ON EA.EntityID = C.CorporationID AND
    EA.IsArchived = 'F'
    OUTER APPLY (
    SELECT TOP 1 NameID FROM CorporationHistory
    WHERE CorporationID = C.CorporationID AND EffectiveDate <= @RefDate
    ORDER BY EffectiveDate DESC
    ) CH
    LEFT JOIN EntityName EN ON EN.EntityNameID = CH.NameID
    WHERE EN.Name IS NOT NULL

    SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who give you the shirt off *someone else's* back.

  • It doesn't make any sense to write the SQL as you have.

    You have a LEFT JOIN EntityAttributes EA, but you also have WHERE EA.IsArchived = 'F'

    That means your LEFT JOIN EntityAttributes EA is really an INNER JOIN

    You have a LEFT JOIN EntityName EN, but you also have AND EN.Name IS NOT NULL in the WHERE

    That means your LEFT JOIN EntityName EN is really an INNER JOIN.

    So it also means the OUTER APPLY is a CROSS APPLY as your LEFT JOIN EntityName EN relates to result from the OUTER APPLY.

    Personally, I would never write a SQL statement like that. It will just be confusing to anyone who has to edit it at a later date and possibly even to the optimiser .

    Logically your query is the same as this:

    SELECT C.CorporationID,
    EA.IsArchived,
    EN.Name
    FROM Corporation C
    INNER JOIN EntityAttributes EA
    ON EA.EntityID = C.CorporationID
    AND EA.IsArchived = 'F'
    CROSS APPLY (SELECT TOP (1) NameID
    FROM CorporationHistory ch
    WHERE ch.CorporationID = C.CorporationID
    AND ch.EffectiveDate <= @RefDate
    ORDER BY EffectiveDate DESC) CH
    INNER JOIN EntityName EN
    ON EN.EntityNameID = CH.NameID
    AND EN.Name IS NOT NULL

     

  • Thanks Scott and Jonathan. I agree that the statement is confusing for both developers and SQL Server. My problem is that this statement (which is actually an extract from a bigger statement) originates from a user-defined reports generator and the WHERE and ORDER BY clauses are generated according to filters defined by the user. This is why filtering is not performed at join level.

    Thus, final statements are rarely optimal even though they usually perform quite well. The actual statement is therefore an example of statements that fall into the "performs very bad" bucket. Fortunately, there are very few of them. When this happens, I always have the choice to provide a custom statement for the specific report but of course, that solution lacks flexibility and maintainability.

    Have a nice weekend.

    • This reply was modified 1 year, 2 months ago by  cmartel 20772.
    • This reply was modified 1 year, 2 months ago by  cmartel 20772. Reason: typo
  • Do you have an index on:

    CorporationHistory ( CorporationID, EffectiveDate )?

    If not, you need one.

    I specialize in tuning SQL Server.  It's easy to overlook indexes if you don't have a lot of experience with it.  Sorry, but that's why I don't generally take peoples' word that "I have all the right indexes, trust me."

    SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who give you the shirt off *someone else's* back.

  • I do have this index. It is clustered and unique (but I also tested non-clustered and non-unique versions of it). Every "fast" plan is using it.

    I will take some time in the next few days to write statements that create and fill tables Corporation, EntityAttributes, CorporationHistory and EntityName and try to reproduce the issue. I will send you the script if you wish.

  • You should be able to script this out.  Make sure the "Script indexes" option is on.  Then Script the table; that should give you DDL for the table and all its indexes.

    SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who give you the shirt off *someone else's* back.

  • Actually, if you want to comprehensively review the indexes on those table, run this script twice (once for 'Corporation%' tables and once for 'Entity%' tables) and post both result sets from each run.  Be sure that "@list_missing_indexes" is set to 1 for the runs.

    SET DEADLOCK_PRIORITY -8; /*"tell" SQL, if this task gets into a deadlock somehow, 
    cancel THIS task, NOT any other one*/
    DECLARE @filegroup_name nvarchar(128)
    DECLARE @list_missing_indexes bit
    DECLARE @list_missing_indexes_summary bit
    DECLARE @max_compression tinyint
    DECLARE @order_by smallint --1=table_name; 2=size; -2=size DESC;.
    DECLARE @table_name_pattern sysname

    SET @list_missing_indexes = 1 --NOTE: can take some time, set to 0 if you do not want to wait.
    SET @list_missing_indexes_summary = 0
    SET @order_by = -2 /* -2=size desc; 1=table_name asc; */
    SET @filegroup_name = NULL /* null=all; 'PRIMARY'/'<other_group_name>'=that filegroup only*/
    SET @table_name_pattern = 'Corporation%' --<<--one run with this
    --SET @table_name_pattern = 'Entity%' --<<--another run with this
    --SET @max_compression = 0

    SET NOCOUNT ON;
    SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

    DECLARE @debug smallint
    DECLARE @include_schema_in_table_names bit
    DECLARE @include_system_tables bit
    DECLARE @list_filegroup_and_drive_summary bit
    DECLARE @total decimal(19, 3)

    SET @list_filegroup_and_drive_summary = 0
    SET @include_schema_in_table_names = 0

    SET @include_system_tables = 0
    SET @debug = 0

    IF OBJECT_ID('tempdb.dbo.#index_specs') IS NOT NULL
    DROP TABLE dbo.#index_specs
    IF OBJECT_ID('tempdb.dbo.#index_missing') IS NOT NULL
    DROP TABLE dbo.#index_missing
    IF OBJECT_ID('tempdb.dbo.#index_usage') IS NOT NULL
    DROP TABLE dbo.#index_usage

    CREATE TABLE dbo.#index_specs (
    object_id int NOT NULL,
    index_id int NOT NULL,
    min_compression int NULL,
    max_compression int NULL,
    drive char(1) NULL,
    alloc_mb decimal(9, 1) NOT NULL,
    alloc_gb AS CAST(alloc_mb / 1024.0 AS decimal(9, 2)),
    used_mb decimal(9, 1) NOT NULL,
    used_gb AS CAST(used_mb / 1024.0 AS decimal(9, 2)),
    rows bigint NULL,
    table_mb decimal(9, 1) NULL,
    table_gb AS CAST(table_mb / 1024.0 AS decimal(9, 2)),
    size_rank int NULL,
    approx_max_data_width bigint NULL,
    UNIQUE CLUSTERED ( object_id, index_id )
    ) --SELECT * FROM #index_specs
    --SELECT create_date FROM sys.databases WITH (NOLOCK) WHERE name = N'tempdb'

    PRINT 'Started @ ' + CONVERT(varchar(30), GETDATE(), 120)

    DECLARE @is_compression_available bit
    DECLARE @sql varchar(max)

    IF (CAST(SERVERPROPERTY('ProductVersion') AS varchar(30)) LIKE '9.%'
    OR (CAST(SERVERPROPERTY('Edition') AS varchar(40)) NOT LIKE '%Developer%' AND
    CAST(SERVERPROPERTY('Edition') AS varchar(40)) NOT LIKE '%Enterprise%'))
    AND (CAST(SERVERPROPERTY('ProductVersion') AS varchar(30)) NOT LIKE '1[345]%.%')
    SET @is_compression_available = 0
    ELSE
    SET @is_compression_available = 1

    SET @sql = '
    INSERT INTO #index_specs ( object_id, index_id,' +
    CASE WHEN @is_compression_available = 0 THEN '' ELSE '
    min_compression, max_compression,' END + '
    alloc_mb, used_mb, rows )
    SELECT
    base_size.object_id,
    base_size.index_id, ' +
    CASE WHEN @is_compression_available = 0 THEN '' ELSE '
    base_size.min_compression,
    base_size.max_compression,' END + '
    (base_size.total_pages + ISNULL(internal_size.total_pages, 0)) / 128.0 AS alloc_mb,
    (base_size.used_pages + ISNULL(internal_size.used_pages, 0)) / 128.0 AS used_mb,
    base_size.row_count AS rows
    FROM (
    SELECT
    dps.object_id,
    dps.index_id, ' +
    CASE WHEN @is_compression_available = 0 THEN '' ELSE '
    MIN(p.data_compression) AS min_compression,
    MAX(p.data_compression) AS max_compression,' END + '
    SUM(dps.reserved_page_count) AS total_pages,
    SUM(dps.used_page_count) AS used_pages,
    SUM(CASE WHEN dps.index_id IN (0, 1) THEN dps.row_count ELSE 0 END) AS row_count
    FROM sys.dm_db_partition_stats dps ' +
    CASE WHEN @is_compression_available = 0 THEN '' ELSE '
    INNER JOIN sys.partitions p WITH (NOLOCK) ON
    p.partition_id = dps.partition_id ' END + '
    --WHERE dps.object_id > 100
    WHERE OBJECT_NAME(dps.object_id) LIKE ''' + @table_name_pattern + '''
    GROUP BY
    dps.object_id,
    dps.index_id
    ) AS base_size
    LEFT OUTER JOIN (
    SELECT
    it.parent_id,
    SUM(dps.reserved_page_count) AS total_pages,
    SUM(dps.used_page_count) AS used_pages
    FROM sys.internal_tables it WITH (NOLOCK)
    INNER JOIN sys.dm_db_partition_stats dps WITH (NOLOCK) ON
    dps.object_id = it.parent_id
    WHERE it.internal_type IN ( ''202'', ''204'', ''211'', ''212'', ''213'', ''214'', ''215'', ''216'' )
    GROUP BY
    it.parent_id
    ) AS internal_size ON base_size.index_id IN (0, 1) AND internal_size.parent_id = base_size.object_id
    '
    IF @debug >= 1
    PRINT @sql
    EXEC(@sql)

    UPDATE [is]
    SET approx_max_data_width = index_cols.approx_max_data_width
    FROM #index_specs [is]
    INNER JOIN (
    SELECT index_col_ids.object_id, index_col_ids.index_id,
    SUM(CASE WHEN c.max_length = -1 THEN 16 ELSE c.max_length END) AS approx_max_data_width
    FROM (
    SELECT ic.object_id, ic.index_id, ic.column_id
    --,object_name(ic.object_id)
    FROM sys.index_columns ic
    WHERE
    ic.object_id > 100
    UNION
    SELECT i_nonclus.object_id, i_nonclus.index_id, ic_clus.column_id
    --,object_name(i_nonclus.object_id)
    FROM sys.indexes i_nonclus
    CROSS APPLY (
    SELECT ic_clus2.column_id
    --,object_name(ic_clus2.object_id),ic_clus2.key_ordinal
    FROM sys.index_columns ic_clus2
    WHERE
    ic_clus2.object_id = i_nonclus.object_id AND
    ic_clus2.index_id = 1 AND
    ic_clus2.key_ordinal > 0 --technically superfluous, since clus index can't have include'd cols anyway
    ) AS ic_clus
    WHERE
    i_nonclus.object_id > 100 AND
    i_nonclus.index_id > 1
    ) AS index_col_ids
    INNER JOIN sys.columns c ON c.object_id = index_col_ids.object_id AND c.column_id = index_col_ids.column_id
    GROUP BY index_col_ids.object_id, index_col_ids.index_id
    ) AS index_cols ON index_cols.object_id = [is].object_id AND index_cols.index_id = [is].index_id

    UPDATE ispec
    SET table_mb = ispec_ranking.table_mb,
    size_rank = ispec_ranking.size_rank
    FROM #index_specs ispec
    INNER JOIN (
    SELECT *, ROW_NUMBER() OVER(ORDER BY table_mb DESC, rows DESC, OBJECT_NAME(object_id)) AS size_rank
    FROM (
    SELECT object_id, SUM(alloc_mb) AS table_mb, MAX(rows) AS rows
    FROM #index_specs
    GROUP BY object_id
    ) AS ispec_allocs
    ) AS ispec_ranking ON
    ispec_ranking.object_id = ispec.object_id

    IF @list_missing_indexes = 1
    BEGIN
    SELECT
    IDENTITY(int, 1, 1) AS ident,
    DB_NAME(mid.database_id) AS Db_Name,
    CONVERT(varchar(10), GETDATE(), 120) AS capture_date,
    ispec.size_rank, ispec.table_mb,
    dps.row_count,
    CASE WHEN @include_schema_in_table_names = 1 THEN OBJECT_SCHEMA_NAME(mid.object_id /*, mid.database_id*/) + '.'
    ELSE '' END + OBJECT_NAME(mid.object_id /*, mid.database_id*/) AS Table_Name,
    REPLACE(mid.equality_columns, ',', ';') AS equality_columns,
    REPLACE(mid.inequality_columns, ',', ';') AS inequality_columns,
    REPLACE(mid.included_columns, ',', ';') AS included_columns,
    user_seeks, user_scans, cj1.max_days_active, unique_compiles,
    last_user_seek, last_user_scan,
    CAST(avg_total_user_cost AS decimal(9, 2)) AS avg_total_user_cost,
    CAST(avg_user_impact AS decimal(9, 2)) AS [avg_user_impact%],
    system_seeks, system_scans, last_system_seek, last_system_scan,
    CAST(avg_total_system_cost AS decimal(9, 2)) AS avg_total_system_cost,
    CAST(avg_system_impact AS decimal(9, 2)) AS [avg_system_impact%],
    mid.statement, mid.object_id, mid.index_handle
    INTO #index_missing
    FROM sys.dm_db_missing_index_details mid WITH (NOLOCK)
    CROSS JOIN (
    SELECT DATEDIFF(DAY, create_date, GETDATE()) AS max_days_active FROM sys.databases WITH (NOLOCK) WHERE name = 'tempdb'
    ) AS cj1
    LEFT OUTER JOIN sys.dm_db_missing_index_groups mig WITH (NOLOCK) ON
    mig.index_handle = mid.index_handle
    LEFT OUTER JOIN sys.dm_db_missing_index_group_stats migs WITH (NOLOCK) ON
    migs.group_handle = mig.index_group_handle
    LEFT OUTER JOIN sys.dm_db_partition_stats dps WITH (NOLOCK) ON
    dps.object_id = mid.object_id AND
    dps.index_id IN (0, 1)
    OUTER APPLY (
    SELECT CASE WHEN dps.row_count >= 1000000 THEN 'M' WHEN dps.row_count >= 1000 THEN 'K' ELSE '' END AS row_count_suffix
    ) AS ca1
    OUTER APPLY (
    SELECT ispec.table_mb, ispec.size_rank
    FROM dbo.#index_specs ispec
    WHERE
    ispec.object_id = mid.object_id AND
    ispec.index_id IN (0, 1)
    ) AS ispec
    --order by
    --DB_NAME, Table_Name, equality_columns
    WHERE
    1 = 1
    AND mid.database_id = DB_ID()
    AND OBJECT_NAME(mid.object_id) LIKE @table_name_pattern
    AND OBJECT_NAME(mid.object_id) NOT LIKE 'tmp%'
    ORDER BY
    --avg_total_user_cost * (user_seeks + user_scans) DESC,
    Db_Name,
    CASE WHEN @order_by IN (-2, 2) THEN ispec.size_rank * -SIGN(@order_by) ELSE 0 END,
    Table_Name,
    equality_columns, inequality_columns,
    user_seeks DESC
    SELECT *
    FROM #index_missing
    ORDER BY ident
    IF @list_missing_indexes_summary = 1
    BEGIN
    SELECT
    derived.Size_Rank, derived.table_mb,
    derived.Table_Name, derived.Equality_Column, derived.Equality#, derived.User_Seeks,
    ISNULL((SELECT SUM(user_seeks)
    FROM #index_missing im2
    CROSS APPLY DBA.dbo.DelimitedSplit8K (inequality_columns, ',') ds
    WHERE im2.Size_Rank = derived.Size_Rank AND
    LTRIM(RTRIM(ds.Item)) = derived.Equality_Column
    ), 0) AS Inequality_Seeks,
    derived.User_Scans, derived.Last_User_Seek, derived.Last_User_Scan,
    derived.Max_Days_Active, derived.Avg_Total_User_Cost, derived.Approx_Total_Cost
    FROM (
    SELECT
    Size_Rank, MAX(table_mb) AS table_mb, Table_Name, LTRIM(RTRIM(ds.Item)) AS Equality_Column,
    SUM(user_seeks) AS User_Seeks, SUM(user_scans) AS User_Scans,
    MAX(last_user_seek) AS Last_User_Seek, MAX(last_user_scan) AS Last_User_Scan,
    MIN(max_days_active) AS Max_Days_Active,
    MAX(avg_total_user_cost) AS Avg_Total_User_Cost,
    (SUM(user_seeks) + SUM(user_scans)) * MAX(avg_total_user_cost) AS Approx_Total_Cost,
    MAX(ds.ItemNumber) AS Equality#
    FROM #index_missing
    CROSS APPLY DBA.dbo.DelimitedSplit8K (equality_columns, ',') ds
    WHERE equality_columns IS NOT NULL
    GROUP BY size_rank, Table_Name, LTRIM(RTRIM(ds.Item))
    ) AS derived
    ORDER BY Size_Rank, Table_Name, Approx_Total_Cost DESC
    END --IF
    END --IF

    PRINT 'Index Usage Stats @ ' + CONVERT(varchar(30), GETDATE(), 120)

    --IF @debug > 0
    -- SELECT OBJECT_NAME(object_id), * FROM #index_specs ORDER BY 1 /*used_mb DESC*/-- list index usage stats (seeks, scans, etc.)
    SELECT
    IDENTITY(int, 1, 1) AS ident,
    DB_NAME() AS db_name,
    --ispec.drive AS drv,
    ispec.size_rank, ispec.alloc_mb - ispec.used_mb AS unused_mb,
    FILEGROUP_NAME(i.data_space_id) AS main_fg_name,
    CASE WHEN @include_schema_in_table_names = 1 THEN OBJECT_SCHEMA_NAME(i.object_id /*, DB_ID()*/) + '.'
    ELSE '' END + OBJECT_NAME(i.object_id /*, i.database_id*/) AS Table_Name,
    key_cols AS key_cols,
    LEN(nonkey_cols) - LEN(REPLACE(nonkey_cols, ',', '')) + 1 AS nonkey_count,
    nonkey_cols AS nonkey_cols,
    ius.user_seeks, ius.user_scans, --ius.user_seeks + ius.user_scans AS total_reads,
    ius.user_lookups, ius.user_updates,
    dps.row_count,
    ispec.table_gb, ispec.alloc_gb AS index_gb,
    SUBSTRING('NY', CAST(i.is_primary_key AS int) + CAST(i.is_unique_constraint AS int) + 1, 1) +
    CASE WHEN i.is_unique = CAST(i.is_primary_key AS int) + CAST(i.is_unique_constraint AS int) THEN ''
    ELSE '.' + SUBSTRING('NY', CAST(i.is_unique AS int) + 1, 1) END AS [Uniq?],
    REPLACE(i.name, oa1.table_name, '~') AS index_name,
    --fc_row_count.formatted_value AS row_count,
    i.index_id,
    ispec.approx_max_data_width AS [data_width],
    CAST(CAST(ispec.used_mb AS float) * 1024.0 * 1024.0 / NULLIF(dps.row_count, 0) AS int) AS cmptd_row_size,
    CASE
    WHEN ispec.max_compression IS NULL THEN '(Not applicable)'
    WHEN ispec.max_compression = 2 THEN 'Page'
    WHEN ispec.max_compression = 1 THEN 'Row'
    WHEN ispec.max_compression = 0 THEN ''
    ELSE '(Unknown)' END AS max_compression,
    dios.leaf_delete_count + dios.leaf_insert_count + dios.leaf_update_count as leaf_mod_count,
    dios.range_scan_count, dios.singleton_lookup_count,
    DATEDIFF(DAY, STATS_DATE ( i.object_id , i.index_id ), GETDATE()) AS stats_days_old,
    DATEDIFF(DAY, CASE
    WHEN o.create_date > cj1.sql_startup_date AND o.create_date > o.modify_date THEN o.create_date
    WHEN o.modify_date > cj1.sql_startup_date AND o.modify_date > o.create_date THEN o.modify_date
    ELSE cj1.sql_startup_date END, GETDATE()) AS max_days_active,
    dios.row_lock_count, dios.row_lock_wait_in_ms,
    dios.page_lock_count, dios.page_lock_wait_in_ms,
    ius.last_user_seek, ius.last_user_scan,
    ius.last_user_lookup, ius.last_user_update,
    fk.Reference_Count AS fk_ref_count,
    i.fill_factor,
    ius2.row_num,
    ius.system_seeks, ius.system_scans, ius.system_lookups, ius.system_updates,
    ius.last_system_seek, ius.last_system_scan, ius.last_system_lookup, ius.last_system_update,
    GETDATE() AS capture_date
    INTO #index_usage
    FROM sys.indexes i WITH (NOLOCK)
    INNER JOIN sys.objects o WITH (NOLOCK) ON
    o.object_id = i.object_id
    CROSS JOIN (
    SELECT create_date AS sql_startup_date FROM sys.databases WITH (NOLOCK) WHERE name = 'tempdb'
    ) AS cj1
    OUTER APPLY (
    SELECT CASE WHEN EXISTS(SELECT 1 FROM #index_specs [is] WHERE [is].object_id = i.object_id AND [is].index_id = 1)
    THEN 1 ELSE 0 END AS has_clustered_index
    ) AS cj2
    LEFT OUTER JOIN dbo.#index_specs ispec ON
    ispec.object_id = i.object_id AND
    ispec.index_id = i.index_id
    OUTER APPLY (
    SELECT STUFF((
    SELECT
    ', ' + COL_NAME(ic.object_id, ic.column_id)
    FROM sys.index_columns ic WITH (NOLOCK)
    WHERE
    ic.key_ordinal > 0 AND
    ic.object_id = i.object_id AND
    ic.index_id = i.index_id
    ORDER BY
    ic.key_ordinal
    FOR XML PATH('')
    ), 1, 2, '')
    ) AS key_cols (key_cols)
    OUTER APPLY (
    SELECT STUFF((
    SELECT
    ', ' + COL_NAME(ic.object_id, ic.column_id)
    FROM sys.index_columns ic WITH (NOLOCK)
    WHERE
    ic.key_ordinal = 0 AND
    ic.object_id = i.object_id AND
    ic.index_id = i.index_id
    ORDER BY
    COL_NAME(ic.object_id, ic.column_id)
    FOR XML PATH('')
    ), 1, 2, '')
    ) AS nonkey_cols (nonkey_cols)
    LEFT OUTER JOIN sys.dm_db_partition_stats dps WITH (NOLOCK) ON
    dps.object_id = i.object_id AND
    dps.index_id = i.index_id
    LEFT OUTER JOIN sys.dm_db_index_usage_stats ius WITH (NOLOCK) ON
    ius.database_id = DB_ID() AND
    ius.object_id = i.object_id AND
    ius.index_id = i.index_id
    LEFT OUTER JOIN (
    SELECT
    database_id, object_id, MAX(user_scans) AS user_scans,
    ROW_NUMBER() OVER (ORDER BY MAX(user_scans) DESC) AS row_num --user_scans|user_seeks+user_scans
    FROM sys.dm_db_index_usage_stats WITH (NOLOCK)
    WHERE
    database_id = DB_ID()
    --AND index_id > 0
    GROUP BY
    database_id, object_id
    ) AS ius2 ON
    ius2.database_id = DB_ID() AND
    ius2.object_id = i.object_id
    LEFT OUTER JOIN (
    SELECT
    referenced_object_id, COUNT(*) AS Reference_Count
    FROM sys.foreign_keys WITH (NOLOCK)
    WHERE
    is_disabled = 0
    GROUP BY
    referenced_object_id
    ) AS fk ON
    fk.referenced_object_id = i.object_id
    LEFT OUTER JOIN (
    SELECT *
    FROM sys.dm_db_index_operational_stats ( DB_ID(), NULL, NULL, NULL )
    ) AS dios ON
    dios.object_id = i.object_id AND
    dios.index_id = i.index_id
    OUTER APPLY (
    SELECT OBJECT_NAME(i.object_id/*, DB_ID()*/) AS table_name
    --, CASE WHEN dps.row_count >= 1000000 THEN 'M' WHEN dps.row_count >= 1000 THEN 'K' ELSE '' END AS row_count_suffix
    ) AS oa1
    OUTER APPLY (
    SELECT CASE WHEN dps.row_count >= 1000000 THEN 'M' WHEN dps.row_count >= 1000 THEN 'K' ELSE '' END AS row_count_suffix
    ) AS ca1
    /*
    OUTER APPLY (
    SELECT dps.row_count AS formatted_value
    ) AS fc_row_count
    OUTER APPLY (
    SELECT alloc_mb AS formatted_value
    ) AS fc_alloc_mb
    */WHERE
    --EXISTS(SELECT 1 FROM sys.indexes i2 WITH (NOLOCK) WHERE i2.object_id = i.object_id AND i2.index_id = 0) AND --$T
    (i.is_disabled = 0 OR @order_by IN (-1, 1)) AND
    i.is_hypothetical = 0 AND
    --i.type IN (0, 1, 2) AND
    o.type NOT IN ( 'IF', 'IT', 'TF', 'TT' ) AND
    (
    o.name LIKE @table_name_pattern AND
    o.name NOT LIKE 'dtprop%' AND
    o.name NOT LIKE 'filestream[_]' AND
    o.name NOT LIKE 'MSpeer%' AND
    o.name NOT LIKE 'MSpub%' AND
    --o.name NOT LIKE 'queue[_]%' AND
    --(DB_NAME() IN ('master', 'msdb') OR o.name NOT LIKE 'sys%')
    (@include_system_tables = 1 OR o.name NOT LIKE 'sys%')
    ) AND
    (@filegroup_name IS NULL OR FILEGROUP_NAME(i.data_space_id) LIKE @filegroup_name) AND
    (@max_compression IS NULL OR ispec.max_compression <= @max_compression)
    ORDER BY
    --cj2.has_clustered_index, ispec.size_rank, --heaps first, by size
    db_name,
    --i.index_id,
    --ius.user_seeks - ius.user_scans,
    CASE WHEN @order_by IN (-2, 2) THEN ispec.size_rank * -SIGN(@order_by) ELSE 0 END,
    --ius.user_scans DESC,
    --ius2.row_num, --user_scans&|user_seeks
    table_name,
    -- list clustered index first, if any, then other index(es)
    CASE WHEN i.index_id IN (0, 1) THEN 1 ELSE 2 END,
    key_cols
    OPTION ( RECOMPILE )

    SELECT *
    FROM #index_usage
    ORDER BY ident

    SELECT SUM(index_gb) AS Total_Size_GB
    FROM #index_usage
    --PRINT 'Total Size_GB = ' + CAST(@total AS varchar(30))

    IF @list_filegroup_and_drive_summary = 1
    SELECT
    LEFT(df.physical_name, 1) AS drive,
    FILEGROUP_NAME(au_totals.data_space_id) AS filegroup_name,
    au_totals.total_mb AS total_fg_mb,
    au_totals.used_mb AS used_fg_mb,
    au_totals.total_mb - au_totals.used_mb AS free_fg_mb,
    CAST(df.size / 128.0 AS decimal(9, 2)) AS file_size_mb
    FROM (
    SELECT
    au.data_space_id,
    CAST(SUM(au.total_pages) / 128.0 AS decimal(9, 2)) AS total_mb,
    CAST(SUM(au.used_pages) / 128.0 AS decimal(9, 2)) AS used_mb
    FROM sys.allocation_units au
    INNER JOIN sys.filegroups fg ON
    fg.data_space_id = au.data_space_id
    GROUP BY au.data_space_id WITH ROLLUP
    ) AS au_totals
    INNER JOIN sys.database_files df ON
    df.data_space_id = au_totals.data_space_id
    ORDER BY filegroup_name, drive

    PRINT 'Ended @ ' + CONVERT(varchar(30), GETDATE(), 120)

    SET DEADLOCK_PRIORITY LOW

     

    SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who give you the shirt off *someone else's* back.

  • Hi Scott, this is oversimplified but it reproduces the problem. The question becomes, what is missing so we do not have to wrap EA.IsArchived in a ISNULL?

     

    /*
    DROP TABLE Corporation2
    DROP TABLE EntityAttributes2
    DROP TABLE CorporationHistory2
    DROP TABLE EntityName2
    */

    CREATE TABLE Corporation2 (CorporationID CHAR(5) PRIMARY KEY NOT NULL)
    CREATE TABLE EntityAttributes2 (EntityID CHAR(5) PRIMARY KEY NOT NULL, IsArchived CHAR(1) NOT NULL)
    CREATE TABLE CorporationHistory2 (CorporationID CHAR(5) NOT NULL, EffectiveDate DATETIME NOT NULL, NameID CHAR(5) NOT NULL, INDEX IX_Primary (CorporationID, EffectiveDate))
    CREATE TABLE EntityName2(EntityNameID CHAR(5) PRIMARY KEY NOT NULL, Name VARCHAR(50) NOT NULL)

    DECLARE @I INT = 0
    WHILE @I < 10000 BEGIN
    INSERT INTO Corporation2 SELECT 'C' + FORMAT(@I, '0000')
    INSERT INTO EntityAttributes2 SELECT 'C' + FORMAT(@I, '0000'), IIF(@I % 10 = 0, 'T', 'F')
    INSERT INTO EntityAttributes2 SELECT 'P' + FORMAT(@I, '0000'), IIF(@I % 10 = 0, 'T', 'F')
    INSERT INTO CorporationHistory2 SELECT 'C' + FORMAT(@I, '0000'), '2001-01-01', 'N' + FORMAT(@I, '0000')
    INSERT INTO CorporationHistory2 SELECT 'C' + FORMAT(@I, '0000'), '2011-01-01', 'N' + FORMAT(@I, '0000')
    INSERT INTO CorporationHistory2 SELECT 'C' + FORMAT(@I, '0000'), '2021-01-01', 'N' + FORMAT(@I, '0000')
    INSERT INTO EntityName2 SELECT 'N' + FORMAT(@I, '0000'), 'AAA' + FORMAT(@I, '0000') + 'ZZZ'
    SET @I = @I + 1
    END

    SELECT * FROM Corporation2
    SELECT * FROM EntityAttributes2
    SELECT * FROM CorporationHistory2
    SELECT * FROM EntityName2

    SELECT
    C.CorporationID,
    EA.IsArchived,
    EN.Name
    FROM Corporation2 C
    LEFT JOIN EntityAttributes2 EA ON EA.EntityID = C.CorporationID
    OUTER APPLY (
    SELECT TOP 1 NameID FROM CorporationHistory2
    WHERE CorporationID = C.CorporationID AND EffectiveDate <= '2021-09-30'
    ORDER BY EffectiveDate DESC
    ) CH
    LEFT JOIN EntityName2 EN ON EN.EntityNameID = CH.NameID
    --WHERE EA.IsArchived = 'F' AND EN.Name IS NOT NULL
    WHERE ISNULL(EA.IsArchived, 'F') = 'F' AND EN.Name IS NOT NULL
  • cmartel 20772 wrote:

    Hi Scott, this is oversimplified but it reproduces the problem. The question becomes, what is missing so we do not have to wrap EA.IsArchived in a ISNULL?

    /*
    DROP TABLE Corporation2
    DROP TABLE EntityAttributes2
    DROP TABLE CorporationHistory2
    DROP TABLE EntityName2
    */

    CREATE TABLE Corporation2 (CorporationID CHAR(5) PRIMARY KEY NOT NULL)
    CREATE TABLE EntityAttributes2 (EntityID CHAR(5) PRIMARY KEY NOT NULL, IsArchived CHAR(1) NOT NULL)
    CREATE TABLE CorporationHistory2 (CorporationID CHAR(5) NOT NULL, EffectiveDate DATETIME NOT NULL, NameID CHAR(5) NOT NULL, INDEX IX_Primary (CorporationID, EffectiveDate))
    CREATE TABLE EntityName2(EntityNameID CHAR(5) PRIMARY KEY NOT NULL, Name VARCHAR(50) NOT NULL)

    DECLARE @I INT = 0
    WHILE @I < 10000 BEGIN
    INSERT INTO Corporation2 SELECT 'C' + FORMAT(@I, '0000')
    INSERT INTO EntityAttributes2 SELECT 'C' + FORMAT(@I, '0000'), IIF(@I % 10 = 0, 'T', 'F')
    INSERT INTO EntityAttributes2 SELECT 'P' + FORMAT(@I, '0000'), IIF(@I % 10 = 0, 'T', 'F')
    INSERT INTO CorporationHistory2 SELECT 'C' + FORMAT(@I, '0000'), '2001-01-01', 'N' + FORMAT(@I, '0000')
    INSERT INTO CorporationHistory2 SELECT 'C' + FORMAT(@I, '0000'), '2011-01-01', 'N' + FORMAT(@I, '0000')
    INSERT INTO CorporationHistory2 SELECT 'C' + FORMAT(@I, '0000'), '2021-01-01', 'N' + FORMAT(@I, '0000')
    INSERT INTO EntityName2 SELECT 'N' + FORMAT(@I, '0000'), 'AAA' + FORMAT(@I, '0000') + 'ZZZ'
    SET @I = @I + 1
    END

    SELECT * FROM Corporation2
    SELECT * FROM EntityAttributes2
    SELECT * FROM CorporationHistory2
    SELECT * FROM EntityName2

    SELECT
    C.CorporationID,
    EA.IsArchived,
    EN.Name
    FROM Corporation2 C
    LEFT JOIN EntityAttributes2 EA ON EA.EntityID = C.CorporationID
    OUTER APPLY (
    SELECT TOP 1 NameID FROM CorporationHistory2
    WHERE CorporationID = C.CorporationID AND EffectiveDate <= '2021-09-30'
    ORDER BY EffectiveDate DESC
    ) CH
    LEFT JOIN EntityName2 EN ON EN.EntityNameID = CH.NameID
    --WHERE EA.IsArchived = 'F' AND EN.Name IS NOT NULL
    WHERE ISNULL(EA.IsArchived, 'F') = 'F' AND EN.Name IS NOT NULL

    As I showed earlier, move the check for EA.IsArchived = 'F' into the LEFT JOIN itself, and remove it from the WHERE.  This will also be more efficient:

    LEFT JOIN EntityAttributes2 EA ON EA.EntityID = C.CorporationID AND EA.IsArchived = 'F' --<<--

    ...

    WHERE EN.Name IS NOT NULL

    SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who give you the shirt off *someone else's* back.

  • Hi Scott,

    like I mentioned earlier, the WHERE clause is added afterwards in my application (so the user can choose to see archived corporations only, active corporations only or both). Therefore, I wish to know if there is something I can do so the statement runs faster as is.

    Thanks

  • If the query apart from the WHERE and ORDER BY clauses is outside your control you could try:

    WHERE (EA.IsArchived = 'F' OR EA.IsArchived IS NULL) AND EN.Name IS NOT NULL

    If that does not work you may have to look at using a plan guide.

  • cmartel 20772 wrote:

    Hi Scott,

    like I mentioned earlier, the WHERE clause is added afterwards in my application (so the user can choose to see archived corporations only, active corporations only or both). Therefore, I wish to know if there is something I can do so the statement runs faster as is.

    Thanks

    No.  If you can't put the conditions directly into the LEFT OUTER JOINs, you will be stuck with worse performance -- possibly far, far worse performance, depending on the size of the tables and how many rows match the WHERE condition.

    The only other thing that could theoretically speed up the query would be adding / adjusting indexes to better support the query.

    SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who give you the shirt off *someone else's* back.

  • sql playing dumb on this case

    if your software can be set to do other types of joins/cross applies or even to, as you said, generate the isnull (which works on this case but may cause issues on other cases), then changing the outer apply to cross apply on the particular sample of data you supplied also generates a correct plan.

    Just based on your sample data adding different indexes does not seem to solve the issue.

Viewing 15 posts - 1 through 15 (of 22 total)

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