Best Index Choices

  • 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
  • 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.

  • 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) Prosecutor James Blackburn, in closing argument in the Fatal Vision murders trial: "If in the future, you should cry a tear, cry one for them [the murder victims]. If in the future, you should say a prayer, say one for them. And if in the future, you should light a candle, light one for them."

  • 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

  • ScottPletcher wrote:

    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.

    Attachments:
    You must be logged in to view attached files.
  • 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) Prosecutor James Blackburn, in closing argument in the Fatal Vision murders trial: "If in the future, you should cry a tear, cry one for them [the murder victims]. If in the future, you should say a prayer, say one for them. And if in the future, you should light a candle, light one for them."

  • 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

  • Results attached.

    Attachments:
    You must be logged in to view attached files.
  • 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?

    • This reply was modified 1 week ago by  Sergiy.

    _____________
    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) Prosecutor James Blackburn, in closing argument in the Fatal Vision murders trial: "If in the future, you should cry a tear, cry one for them [the murder victims]. If in the future, you should say a prayer, say one for them. And if in the future, you should light a candle, light one for them."

  • 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];

    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

  • Sergiy wrote:

    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?

    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
  • 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?

  • Jackie Lowery wrote:

    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) Prosecutor James Blackburn, in closing argument in the Fatal Vision murders trial: "If in the future, you should cry a tear, cry one for them [the murder victims]. If in the future, you should say a prayer, say one for them. And if in the future, you should light a candle, light one for them."

  • ScottPletcher wrote:

    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 18 total)

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