Can you guys give a recommendation on what the best indexes would be for this query? Please let me know if more info is needed.
SELECT IMLSMST_SQL.ser_lot_no AS 'Ser/Lot'
,CASE
WHEN IMLSMST_SQL.qty_on_hand - IMLSMST_SQL.qty_allocated - Isnull(Sum(wsPKGSL.Qty), 0) > 0
THEN IMLSMST_SQL.qty_on_hand - IMLSMST_SQL.qty_allocated - Isnull(Sum(wsPKGSL.Qty), 0)
ELSE 0
END AS 'Qty Avail'
FROM IMLSMST_SQL
LEFT JOIN (
wsPKG LEFT JOIN wsPKGLin ON wsPKG.PKG_ID = wsPKGLin.PKG_ID
LEFT JOIN wsPKGSL ON wsPKGLin.PKGLin_ID = wsPKGSL.PKGLin_ID
) ON wsPKG.Loc = IMLSMST_SQL.loc
AND wsPKG.Bin_no = IMLSMST_SQL.bin_no
AND wsPKGLin.Item_no = IMLSMST_SQL.item_no
AND wsPKGSL.ser_lot_no = IMLSMST_SQL.ser_lot_no
AND Isnull(wsPKG.STATUS, '') NOT IN (
'S'
,'C'
)
WHERE IMLSMST_SQL.loc = '@@LOC'
AND IMLSMST_SQL.bin_no = '@@BIN'
AND IMLSMST_SQL.item_no = '@@ITEMNO'
GROUP BY IMLSMST_SQL.ser_lot_no
,IMLSMST_SQL.qty_on_hand
,IMLSMST_SQL.qty_allocated
July 19, 2021 at 7:28 pm
For that particular query (experts correct me if I am wrong), but I see 4 indexes being needed - 1 per table. The query looks broken to me on that LEFT JOIN, but maybe it is just a syntax I am not familiar with.
Below is my recommendation for non-clustered indexes assuming none of these columns are in your clustered index:
on table IMLSMST_SQL, on loc, bin_no, item_no, and ser_lot_no
on table WSPKG, on PKG_ID, Loc, Bin_no, Status
on table wsPKGLin, on PKG_ID, PKGLin_ID, Item_no
on table wsPKGSL, on PKGLin_ID, ser_lot_no
Basically, my approach here is anything that would be helpful when searching through the table (WHERE clauses and JOIN conditions), gets put into an index.
Now, the above being said, your results may vary. For example, if your table is tiny, adding an index may not offer any performance benefit. Alternately, if indexes already exist that cover the query, adding more may not help and may hurt. If you are looking for an optimal index for that query, having a filtered index (since all of the WHERE syntax is hard-coded values and not parameters) may be even better performance wise. The fun part though - indexes that help that specific query may hurt other queries. Adding Indexes may improve the performance of the query you posted at the cost of hurting all other queries on the system.
I would first look at optimizing the query as much as possible before adding indexes myself. Review the execution plan and see where optimizations can be made. For example, changing your JOIN conditions for loc, bin_no, and Item_no to static values (like what you have in the WHERE clause) may help, or you may even be able to prune out some of the join conditions.
But if you are looking to tune the query (likely since you are trying to add indexes), I would evaluate the query (via the execution plan) and see where the problems exist prior to adding any indexes AND I would see if there is any room to improve the query prior to adding indexes. It may make more sense to use temp tables or table variables for your query or possibly even nested selects or cte's to filter some of the data prior to the JOINs.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
July 19, 2021 at 7:50 pm
I can't offer index recommendations without seeing the DDL for the tables, including all current index definitions. The most critical index is the clustered index, so that should be determined first.
However, one thing that you definitely need to do is to get rid of the ISNULL() in the query. It's not needed because NULL will never be IN or NOT IN any list. And it could prevent SQL's optimal use of any index(es) containing wsPKG.STATUS as a key:
AND wsPKG.STATUS NOT IN ('S','C') /*instead of "Isnull(wsPKG.STATUS, '') NOT ..."*/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".
July 19, 2021 at 8:10 pm
The query is obviously built by some automated tool and has to be fixed.
unless there is some special logic which is not apparent from the first glance.
_____________
Code for TallyGenerator
July 20, 2021 at 6:03 pm
I can't offer index recommendations without seeing the DDL for the tables, including all current index definitions. The most critical index is the clustered index, so that should be determined first.
However, one thing that you definitely need to do is to get rid of the ISNULL() in the query. It's not needed because NULL will never be IN or NOT IN any list. And it could prevent SQL's optimal use of any index(es) containing wsPKG.STATUS as a key:
AND wsPKG.STATUS NOT IN ('S','C') /*instead of "Isnull(wsPKG.STATUS, '') NOT ..."*/
Attaching DDL and Indexes.
July 20, 2021 at 7:03 pm
Hmm, interesting. Lots of indexes there, so we'll need to do a full analysis, if you're willing. Please run the attached query and post the results in some type of text file (i.e. not an image/picture).
In general, since PKG_No is unique, you might get better overall performance by using that as the link between tables rather than the guid column. But verifying that will take more analysis, and it might not be practical for you with your current data setup.
Be sure to leave "@list_missing_indexes = 1" for the run used to post the results. We'll need to consider what SQL has determined are missing index(es) when we redesign the indexes for these tables.
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 don''t 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 = '%'
--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,
mid.equality_columns, mid.inequality_columns, mid.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
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 NORMAL
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".
July 21, 2021 at 12:38 am
How many rows returned by
SELECT IMLSMST_SQL.ser_lot_no
FROM IMLSMST_SQL
WHERE IMLSMST_SQL.loc = '@@LOC'
AND IMLSMST_SQL.bin_no = '@@BIN'
AND IMLSMST_SQL.item_no = '@@ITEMNO'
?
Is column wsPKG.STATUS nullable?
_____________
Code for TallyGenerator
July 21, 2021 at 1:28 am
Can you please run this
SELECT TOP 10 item_no, loc, COUNT(*) NN
FROM IMLSMST_SQL
GROUP BY item_no, loc
HAVING COUNT(*) > 1
ORDER BY NN DESC
and post here the outcome?
_____________
Code for TallyGenerator
I suggest creating the following indexes to support the query. If you'd like to adjust other indexes on the tables also, just let me know.
CREATE UNIQUE NONCLUSTERED INDEX ncx_Loc_Bin_no ON dbo.wsPKG ( Loc, Bin_no, PKG_No ) INCLUDE ( PKG_ID, Status ) WITH ( FILLFACTOR = 95 ) ON [PRIMARY];
CREATE UNIQUE NONCLUSTERED INDEX ncx_Item_no ON dbo.wsPKGLin ( Item_no, ID ) INCLUDE ( PKG_ID, PKGLin_ID ) WITH ( FILLFACTOR = 95 ) ON [PRIMARY];
CREATE UNIQUE NONCLUSTERED INDEX ncx_ser_lot_no ON dbo.wsPKGSL ( ser_lot_no, ID ) INCLUDE ( PKGLin_ID, Qty ) WITH ( FILLFACTOR = 95 ) ON [PRIMARY];
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".
July 21, 2021 at 6:52 am
I suggest creating the following indexes to support the query. If you'd like to adjust other indexes on the tables also, just let me know.
CREATE UNIQUE NONCLUSTERED INDEX ncx_Loc_Bin_no ON dbo.wsPKG ( Loc, Bin_no, PKG_No ) INCLUDE ( PKG_ID, Status ) WITH ( FILLFACTOR = 95 ) ON [PRIMARY];
CREATE UNIQUE NONCLUSTERED INDEX ncx_Item_no ON dbo.wsPKGLin ( Item_no, ID ) INCLUDE ( PKG_ID, PKGLin_ID ) WITH ( FILLFACTOR = 95 ) ON [PRIMARY];
CREATE UNIQUE NONCLUSTERED INDEX ncx_ser_lot_no ON dbo.wsPKGSL ( ser_lot_no, ID ) INCLUDE ( PKGLin_ID, Qty ) WITH ( FILLFACTOR = 95 ) ON [PRIMARY];
There are already 4 indexes with the pair of columns item_no, loc at the beginning.
If my query returns max count(*) not more than 10 then 3 of those indexes should be dropped, only the shortest one should remain, it will do the job all right.
_____________
Code for TallyGenerator
July 21, 2021 at 2:14 pm
Can you please run this
SELECT TOP 10 item_no, loc, COUNT(*) NN
FROM IMLSMST_SQL
GROUP BY item_no, loc
HAVING COUNT(*) > 1
ORDER BY NN DESCand post here the outcome?
item_nolocNN
22AA4AF3-14BD-4825-9993-B7742A8758EDGAR104
1D554E5A-7D8F-4432-94F2-6DA21BB414F4GAR25
1D4AA196-589D-4854-8B8A-6AEA28B8F252GAR23
8B4672BE-3380-4110-BF8E-DA945DF0C28BGAR19
2AF54855-FDD6-43DB-AC58-91AFDC682ECBGAR16
2013F948-0E10-48C0-B6A7-B8DD9B0C2C9FGAR15
16C98EEA-8C61-4091-BAD5-C1985AA23FB1GAR13
EDFA3BDF-EF1C-4318-82A4-2F4338498E4FGAR13
905CBAC8-723F-4D44-8632-0AAABCC27322GAR7
4A5AE7E1-2E74-4AC5-81ED-C5738CA04D5CGAR6
July 21, 2021 at 2:19 pm
I suggest creating the following indexes to support the query. If you'd like to adjust other indexes on the tables also, just let me know.
CREATE UNIQUE NONCLUSTERED INDEX ncx_Loc_Bin_no ON dbo.wsPKG ( Loc, Bin_no, PKG_No ) INCLUDE ( PKG_ID, Status ) WITH ( FILLFACTOR = 95 ) ON [PRIMARY];
CREATE UNIQUE NONCLUSTERED INDEX ncx_Item_no ON dbo.wsPKGLin ( Item_no, ID ) INCLUDE ( PKG_ID, PKGLin_ID ) WITH ( FILLFACTOR = 95 ) ON [PRIMARY];
CREATE UNIQUE NONCLUSTERED INDEX ncx_ser_lot_no ON dbo.wsPKGSL ( ser_lot_no, ID ) INCLUDE ( PKGLin_ID, Qty ) WITH ( FILLFACTOR = 95 ) ON [PRIMARY];
Why do you think I need to include ID in the wsPKGLin and wsPKGSL indexes if ID is already in the clustered index on those tables?
July 21, 2021 at 3:49 pm
ScottPletcher wrote:I suggest creating the following indexes to support the query. If you'd like to adjust other indexes on the tables also, just let me know.
CREATE UNIQUE NONCLUSTERED INDEX ncx_Loc_Bin_no ON dbo.wsPKG ( Loc, Bin_no, PKG_No ) INCLUDE ( PKG_ID, Status ) WITH ( FILLFACTOR = 95 ) ON [PRIMARY];
CREATE UNIQUE NONCLUSTERED INDEX ncx_Item_no ON dbo.wsPKGLin ( Item_no, ID ) INCLUDE ( PKG_ID, PKGLin_ID ) WITH ( FILLFACTOR = 95 ) ON [PRIMARY];
CREATE UNIQUE NONCLUSTERED INDEX ncx_ser_lot_no ON dbo.wsPKGSL ( ser_lot_no, ID ) INCLUDE ( PKGLin_ID, Qty ) WITH ( FILLFACTOR = 95 ) ON [PRIMARY];Why do you think I need to include ID in the wsPKGLin and wsPKGSL indexes if ID is already in the clustered index on those tables?
The ID is included to make sure the key is unique. Unique indexes process better in SQL Server, otherwise SQL is forced to add a "uniquifier" which greatly complicates processing. All key columns from the clustered index are automatically included in every nonclustered index anyway, so adding ID adds zero overhead as well.
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".
July 21, 2021 at 3:52 pm
The ID is included to make sure the key is unique. Unique indexes process better in SQL Server. All key columns from the clustered index are automatically included in every nonclustered index anyway, so adding ID adds zero overhead as well.
I did not know that trick. Learn something new every day.
Viewing 15 posts - 1 through 15 (of 17 total)
You must be logged in to reply to this topic. Login to reply