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?
September 24, 2021 at 2:58 pm
... 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) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
#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) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
September 24, 2021 at 3:42 pm
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
September 24, 2021 at 5:16 pm
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.
September 24, 2021 at 5:23 pm
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) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
September 24, 2021 at 5:32 pm
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.
September 24, 2021 at 5:57 pm
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) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
September 24, 2021 at 6:03 pm
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) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
September 24, 2021 at 6:27 pm
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
September 24, 2021 at 6:32 pm
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) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
September 24, 2021 at 6:37 pm
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
September 24, 2021 at 7:30 pm
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.
September 24, 2021 at 7:39 pm
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) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
September 24, 2021 at 10:54 pm
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