SQLTeam.com | Weblogs | Forums

Database Design SQL server

Great thanks for the info.
Can we create cluster index on multiple attributes in a table ?

I think I will just create cluster index (instead of just Index) on the primary key, composite key and attributes with range or attributes we do WHERE, GROUP BY and ORDER BY clause on. I think that resolves Indexing.

Yes, you can create a clustered index on multiple columns.

CREATE CLUSTERED INDEX <index_name> ON dbo.<table_name> ( column1, column2, column3 ) ...

1 Like

Thanks, that is what I am going to do. Create cluster index instead.

ScottPletcher,

It seems like if we create cluster index on primary, foreign, and composite key and the attributes that are range and attributes where we do either GROUP by, ORDER by or Filters on. It seems like we are pretty much cluster the entire table especially if is not a huge table, wouldn’t you agree ?

That's the point of the clustering index, to provide the most common path of access.

If foreign keys point to this table, then you must index the column(s) being pointed to.

Don't worry too much about GROUP BY and especially about ORDER BY, unless they are also the WHERE conditions for the rows OR you list almost all rows in the table and GROUP BY the same thing almost every time.

Again, there's no one-size-fits-all answer for indexing, it depends on that specific table's requirements. If you want, after you load the data into the table and run some typical queries on it, I'll give you a script that will list missing indexes (as SQL itself identifies them) and existing indexes, and I can make some recommendations based off of that.

1 Like

ScottPletcher,

Sorry took me a bit to put it together. Because have to load then write the SQL statement. Below is one of the Query. Please advise !

SELECT *
FROM dbo.A1 a INNER JOIN dbo.H1 h ON a.d = h.d and a.Name = h.Name
INNER JOIN dbo.R1 r ON a.d = r.d and a.Name = r.Name
WHERE a.d = '2015-01-01' and a.name = 'Record' and r.prcd = 'am1'

The code itself looks fine. Indexing would be trickier to determine.

After the tables have been used for a while, run this code and post the results:

--Author: ScottPletcher,Index_Usage,Version1.4.
DECLARE @list_missing_indexes bit
DECLARE @table_name_pattern sysname

SET @table_name_pattern = '%' --<-- specify table name or table name pattern to search for
SET @list_missing_indexes = 1

SET NOCOUNT ON;
SET DEADLOCK_PRIORITY -8; /* make sure this task gets killed if somehow it gets in a deadlock */

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

DECLARE @include_schema_in_table_names bit
DECLARE @order_by smallint --1=table_name; 2=size; -2=size DESC;.
DECLARE @format_counts smallint --1=with commas, no decimals; 2/3=with K=1000s,M=1000000s, with 2=0 dec. or 3=1 dec. places;.
DECLARE @debug smallint

--NOTE: showing missing indexes can take some time; set to 0 if you don't want to wait.
SET @include_schema_in_table_names = 1
SET @order_by = -2
SET @format_counts = 3
SET @debug = 0

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%')
    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 '
        ISNULL(MIN(p.data_compression), 0) AS min_compression,
        ISNULL(MAX(p.data_compression), 0) 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
    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,
        CASE WHEN @format_counts = 1 THEN REPLACE(CONVERT(varchar(20), CAST(dps.row_count AS money), 1), '.00', '')
             WHEN @format_counts = 2 THEN CAST(CAST(dps.row_count * 1.0 / CASE ca1.row_count_suffix 
                 WHEN 'M' THEN 1000000 WHEN 'K' THEN 1000 ELSE 1 END AS int) AS varchar(20)) + ca1.row_count_suffix
             WHEN @format_counts = 3 THEN CAST(CAST(dps.row_count * 1.0 / CASE ca1.row_count_suffix 
                 WHEN 'M' THEN 1000000 WHEN 'K' THEN 1000 ELSE 1 END AS decimal(14, 1)) AS varchar(20)) + ca1.row_count_suffix
             ELSE CAST(dps.row_count AS varchar(20)) END AS 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)
    CROSS 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
END --IF

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

-- 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,    
    CASE WHEN @format_counts = 1 THEN REPLACE(CONVERT(varchar(20), CAST(dps.row_count AS money), 1), '.00', '')
         WHEN @format_counts = 2 THEN CAST(CAST(dps.row_count * 1.0 / CASE ca1.row_count_suffix 
             WHEN 'M' THEN 1000000 WHEN 'K' THEN 1000 ELSE 1 END AS int) AS varchar(20)) + ca1.row_count_suffix
         WHEN @format_counts = 3 THEN CAST(CAST(dps.row_count * 1.0 / CASE ca1.row_count_suffix 
             WHEN 'M' THEN 1000000 WHEN 'K' THEN 1000 ELSE 1 END AS decimal(14, 1)) AS varchar(20)) + ca1.row_count_suffix
         ELSE CAST(dps.row_count AS varchar(20)) END AS 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,
    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,
    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,
    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,
    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
CROSS 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

WHERE
    i.object_id > 100 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 
     o.name NOT LIKE 'sys%' 
    )
ORDER BY
    db_name,
    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

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

SET DEADLOCK_PRIORITY NORMAL
1 Like

ScottPletcher,

Thanks for the great code.
Below is the result after I run it.

Do you know if we can create a table in SQL server without any index to the attributes,
then we upload the data, after we upload and do all sorts of query and analysis do we
then create cluster Index or do we need to create cluster index as we create the table in SQL server ?
I preferred to alter the table to create the cluster index later.
Please advise !

Yes, you can do it later. You can initially load the table with no indexes at all.

1 Like

The results for now are showing 5 search on dbo.R1 table using d, Name and prcd. SQL is recommending an index on those columns [keep in mind, you need to thoroughly review SQL's index recommendations, they are really just a starting point].

Likewise, SQL is showing 10 seraches of dbo.H1 on d and Name, and recommending an index on that.

As a starting point, I would likely add a clustering on both two tables on (d, Name) to start with.

1 Like

alright, I just created cluster index on d, and Name for R1, and H1.
How come not to prcd ?

You're right, you could add that to the end of the R1 key. And you probably should, esp. if it's not a long column.

After adding the clustering indexes, look at the "Estimated Query Plan" for the query above. Hopefully you'll see a MERGE JOIN. You can post the query plan here, as a query plan (.xml or .sqlplan) and I can review that real quick too.

So I dropped the previous clustered index I created on d, Name for table R1 with code

DROP INDEX [indexName] on dbo.R1

then I recreated clustered Index on d, Name, prcd to R1 table.

then I run your code then click on Display Estimated Execution Plan which I believe is the same as Display Estimated Query plan and gotten the below to show up.


2

unfortunately it seems like I don't see Merge Join.

I can't upload the sqlplan or the xml file because is not an image.
Is there a way to on here ?

btw, what is the difference between Create Cluster Index VS Create Unique Clustered Index
I thought primary key is automatically Clustered Index or we specify Unique Clustered Index to it and create Clustered Index on non primary key such as foreign key and where we filtered and Unique Clustered Index on primary and composite primary key ? In your below code, why is primary key non clustered. Primary key constraint is to make sure is unique ?

CREATE CLUSTERED INDEX <index_name> ON dbo.<table_name> ( column1, column2, column3 )
CREATE UNIQUE CLUSTERED INDEX <index_name> ON <table_name> ( <col_name(s)> ) WITH ( <index_with_options> ) ON [<filegroup_name>];

ALTER TABLE <table_name> ADD CONSTRAINT <primary_key_constraint_name> PRIMARY KEY NONCLUSTERED / just for clarity and/or in case the pk ever gets build first / ( <col_name(s)> ) WITH ( <index_with_options> ) ON [<filegroup_name>];

Hi ScottPletcher,

I haven’t heard from you in a while. Hope you are doing well. Would love to discuss and get into the more details with you on database designed and indexing.

Hope to hear from you soon !

Sorry, I'm around :grinning:.

I need the xml for the plan. You can save it in a plain text file as long as it's valid xml, I can convert it back.

I can't upload anything unless is image (jpg, jpeg, png, gif).
%23err2
Can't upload the text file with the XML plan.
Please advise !

Hmm. I guess convert the xml to nvarchar(max) text and post the text?!

Yes how about I just post the xml script instead of the file ?

If that works, sure.

> <ShowPlanXML xmlns="http://schemas.microsoft.com/sqlserver/2004/07/showplan" Version="1.2" Build="12.0.2269.0"><BatchSequence><Batch><Statements><StmtSimple StatementText="-- Execute a query. &#xd;&#xa;SELECT *&#xd;&#xa;FROM dbo.A1 a INNER JOIN dbo.H1 h ON a.d = h.d and a.Name = h.Name&#xd;&#xa;INNER JOIN dbo.R1 r ON a.d = r.d and a.Name = r.Name&#xd;&#xa;WHERE a.d = &apos;2015-01-01&apos; and a.name = &apos;Record&apos; and r.prcd = &apos;am1&apos;" StatementId="1" StatementCompId="1" StatementType="SELECT" RetrievedFromCache="false" StatementSubTreeCost="0.389348" StatementEstRows="5974.29" StatementOptmLevel="FULL" QueryHash="0x89F1808A23F1CC54" QueryPlanHash="0x8C0A689EB0A2BE60" StatementOptmEarlyAbortReason="GoodEnoughPlanFound" CardinalityEstimationModelVersion="120"><StatementSetOptions QUOTED_IDENTIFIER="true" ARITHABORT="true" CONCAT_NULL_YIELDS_NULL="true" ANSI_NULLS="true" ANSI_PADDING="true" ANSI_WARNINGS="true" NUMERIC_ROUNDABORT="false"/><QueryPlan CachedPlanSize="32" CompileTime="4" CompileCPU="4" CompileMemory="520"><MissingIndexes><MissingIndexGroup Impact="84.4107"><MissingIndex Database="[fbH]" Schema="[dbo]" Table="[A1]"><ColumnGroup Usage="EQUALITY"><Column Name="[d]" ColumnId="1"/><Column Name="[name]" ColumnId="2"/></ColumnGroup></MissingIndex></MissingIndexGroup></MissingIndexes><MemoryGrantInfo SerialRequiredMemory="0" SerialDesiredMemory="0"/><OptimizerHardwareDependentProperties EstimatedAvailableMemoryGrant="416492" EstimatedPagesCached="104123" EstimatedAvailableDegreeOfParallelism="2"/><RelOp NodeId="0" PhysicalOp="Nested Loops" LogicalOp="Inner Join" EstimateRows="5974.29" EstimateIO="0" EstimateCPU="0.0249725" AvgRowSize="96" EstimatedTotalSubtreeCost="0.389348" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Database="[fbH]" Schema="[dbo]" Table="[A1]" Alias="[a]" Column="d"/><ColumnReference Database="[fbH]" Schema="[dbo]" Table="[A1]" Alias="[a]" Column="name"/><ColumnReference Database="[fbH]" Schema="[dbo]" Table="[A1]" Alias="[a]" Column="vol"/><ColumnReference Database="[fbH]" Schema="[dbo]" Table="[H1]" Alias="[h]" Column="d"/><ColumnReference Database="[fbH]" Schema="[dbo]" Table="[H1]" Alias="[h]" Column="Name"/><ColumnReference Database="[fbH]" Schema="[dbo]" Table="[H1]" Alias="[h]" Column="smallName"/><ColumnReference Database="[fbH]" Schema="[dbo]" Table="[H1]" Alias="[h]" Column="number"/><ColumnReference Database="[fbH]" Schema="[dbo]" Table="[R1]" Alias="[r]" Column="d"/><ColumnReference Database="[fbH]" Schema="[dbo]" Table="[R1]" Alias="[r]" Column="Name"/><ColumnReference Database="[fbH]" Schema="[dbo]" Table="[R1]" Alias="[r]" Column="prcd"/><ColumnReference Database="[fbH]" Schema="[dbo]" Table="[R1]" Alias="[r]" Column="p1"/><ColumnReference Database="[fbH]" Schema="[dbo]" Table="[R1]" Alias="[r]" Column="p2"/></OutputList><Warnings NoJoinPredicate="1"/><NestedLoops Optimized="0"><RelOp NodeId="1" PhysicalOp="Nested Loops" LogicalOp="Inner Join" EstimateRows="128.754" EstimateIO="0" EstimateCPU="0.000538192" AvgRowSize="53" EstimatedTotalSubtreeCost="0.337091" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Database="[fbH]" Schema="[dbo]" Table="[A1]" Alias="[a]" Column="d"/><ColumnReference Database="[fbH]" Schema="[dbo]" Table="[A1]" Alias="[a]" Column="name"/><ColumnReference Database="[fbH]" Schema="[dbo]" Table="[A1]" Alias="[a]" Column="vol"/><ColumnReference Database="[fbH]" Schema="[dbo]" Table="[R1]" Alias="[r]" Column="d"/><ColumnReference Database="[fbH]" Schema="[dbo]" Table="[R1]" Alias="[r]" Column="Name"/><ColumnReference Database="[fbH]" Schema="[dbo]" Table="[R1]" Alias="[r]" Column="prcd"/><ColumnReference Database="[fbH]" Schema="[dbo]" Table="[R1]" Alias="[r]" Column="p1"/><ColumnReference Database="[fbH]" Schema="[dbo]" Table="[R1]" Alias="[r]" Column="p2"/></OutputList><Warnings NoJoinPredicate="1"/><NestedLoops Optimized="0"><RelOp NodeId="2" PhysicalOp="Table Scan" LogicalOp="Table Scan" EstimateRows="4.89898" EstimateIO="0.203866" EstimateCPU="0.0715426" AvgRowSize="29" EstimatedTotalSubtreeCost="0.275408" TableCardinality="64896" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Database="[fbH]" Schema="[dbo]" Table="[A1]" Alias="[a]" Column="d"/><ColumnReference Database="[fbH]" Schema="[dbo]" Table="[A1]" Alias="[a]" Column="name"/><ColumnReference Database="[fbH]" Schema="[dbo]" Table="[A1]" Alias="[a]" Column="vol"/></OutputList><TableScan Ordered="0" ForcedIndex="0" ForceScan="0" NoExpandHint="0" Storage="RowStore"><DefinedValues><DefinedValue><ColumnReference Database="[fbH]" Schema="[dbo]" Table="[A1]" Alias="[a]" Column="d"/></DefinedValue><DefinedValue><ColumnReference Database="[fbH]" Schema="[dbo]" Table="[A1]" Alias="[a]" Column="name"/></DefinedValue><DefinedValue><ColumnReference Database="[fbH]" Schema="[dbo]" Table="[A1]" Alias="[a]" Column="vol"/></DefinedValue></DefinedValues><Object Database="[fbH]" Schema="[dbo]" Table="[A1]" Alias="[a]" IndexKind="Heap" Storage="RowStore"/><Predicate><ScalarOperator ScalarString="[fbH].[dbo].[A1].[d] as [a].[d]=&apos;2015-01-01&apos; AND [fbH].[dbo].[A1].[name] as [a].[name]=&apos;Record&apos;"><Logical Operation="AND"><ScalarOperator><Compare CompareOp="EQ"><ScalarOperator><Identifier><ColumnReference Database="[fbH]" Schema="[dbo]" Table="[A1]" Alias="[a]" Column="d"/></Identifier></ScalarOperator><ScalarOperator><Const ConstValue="&apos;2015-01-01&apos;"/></ScalarOperator></Compare></ScalarOperator><ScalarOperator><Compare CompareOp="EQ"><ScalarOperator><Identifier><ColumnReference Database="[fbH]" Schema="[dbo]" Table="[A1]" Alias="[a]" Column="name"/></Identifier></ScalarOperator><ScalarOperator><Const ConstValue="&apos;Record&apos;"/></ScalarOperator></Compare></ScalarOperator></Logical></ScalarOperator></Predicate></TableScan></RelOp><RelOp NodeId="3" PhysicalOp="Clustered Index Seek" LogicalOp="Clustered Index Seek" EstimateRows="26.2818" EstimateIO="0.003125" EstimateCPU="0.00018591" AvgRowSize="33" EstimatedTotalSubtreeCost="0.00403577" TableCardinality="519168" Parallel="0" EstimateRebinds="0" EstimateRewinds="3.89898" EstimatedExecutionMode="Row"><OutputList><ColumnReference Database="[fbH]" Schema="[dbo]" Table="[R1]" Alias="[r]" Column="d"/><ColumnReference Database="[fbH]" Schema="[dbo]" Table="[R1]" Alias="[r]" Column="Name"/><ColumnReference Database="[fbH]" Schema="[dbo]" Table="[R1]" Alias="[r]" Column="prcd"/><ColumnReference Database="[fbH]" Schema="[dbo]" Table="[R1]" Alias="[r]" Column="p1"/><ColumnReference Database="[fbH]" Schema="[dbo]" Table="[R1]" Alias="[r]" Column="p2"/></OutputList><IndexScan Ordered="1" ScanDirection="FORWARD" ForcedIndex="0" ForceSeek="0" ForceScan="0" NoExpandHint="0" Storage="RowStore"><DefinedValues><DefinedValue><ColumnReference Database="[fbH]" Schema="[dbo]" Table="[R1]" Alias="[r]" Column="d"/></DefinedValue><DefinedValue><ColumnReference Database="[fbH]" Schema="[dbo]" Table="[R1]" Alias="[r]" Column="Name"/></DefinedValue><DefinedValue><ColumnReference Database="[fbH]" Schema="[dbo]" Table="[R1]" Alias="[r]" Column="prcd"/></DefinedValue><DefinedValue><ColumnReference Database="[fbH]" Schema="[dbo]" Table="[R1]" Alias="[r]" Column="p1"/></DefinedValue><DefinedValue><ColumnReference Database="[fbH]" Schema="[dbo]" Table="[R1]" Alias="[r]" Column="p2"/></DefinedValue></DefinedValues><Object Database="[fbH]" Schema="[dbo]" Table="[R1]" Index="[rIDX]" Alias="[r]" IndexKind="Clustered" Storage="RowStore"/><SeekPredicates><SeekPredicateNew><SeekKeys><Prefix ScanType="EQ"><RangeColumns><ColumnReference Database="[fbH]" Schema="[dbo]" Table="[R1]" Alias="[r]" Column="d"/><ColumnReference Database="[fbH]" Schema="[dbo]" Table="[R1]" Alias="[r]" Column="Name"/><ColumnReference Database="[fbH]" Schema="[dbo]" Table="[R1]" Alias="[r]" Column="prcd"/></RangeColumns><RangeExpressions><ScalarOperator ScalarString="&apos;2015-01-01&apos;"><Const ConstValue="&apos;2015-01-01&apos;"/></ScalarOperator><ScalarOperator ScalarString="&apos;Record&apos;"><Const ConstValue="&apos;Record&apos;"/></ScalarOperator><ScalarOperator ScalarString="&apos;am1&apos;"><Const ConstValue="&apos;am1&apos;"/></ScalarOperator></RangeExpressions></Prefix></SeekKeys></SeekPredicateNew></SeekPredicates></IndexScan></RelOp></NestedLoops></RelOp><RelOp NodeId="4" PhysicalOp="Table Spool" LogicalOp="Lazy Spool" EstimateRows="46.4008" EstimateIO="0.01" EstimateCPU="0.000108752" AvgRowSize="51" EstimatedTotalSubtreeCost="0.0272842" Parallel="0" EstimateRebinds="0" EstimateRewinds="127.754" EstimatedExecutionMode="Row"><OutputList><ColumnReference Database="[fbH]" Schema="[dbo]" Table="[H1]" Alias="[h]" Column="d"/><ColumnReference Database="[fbH]" Schema="[dbo]" Table="[H1]" Alias="[h]" Column="Name"/><ColumnReference Database="[fbH]" Schema="[dbo]" Table="[H1]" Alias="[h]" Column="smallName"/><ColumnReference Database="[fbH]" Schema="[dbo]" Table="[H1]" Alias="[h]" Column="number"/></OutputList><Spool><RelOp NodeId="5" PhysicalOp="Clustered Index Seek" LogicalOp="Clustered Index Seek" EstimateRows="46.4008" EstimateIO="0.003125" EstimateCPU="0.000208041" AvgRowSize="51" EstimatedTotalSubtreeCost="0.00333304" TableCardinality="477744" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Database="[fbH]" Schema="[dbo]" Table="[H1]" Alias="[h]" Column="d"/><ColumnReference Database="[fbH]" Schema="[dbo]" Table="[H1]" Alias="[h]" Column="Name"/><ColumnReference Database="[fbH]" Schema="[dbo]" Table="[H1]" Alias="[h]" Column="smallName"/><ColumnReference Database="[fbH]" Schema="[dbo]" Table="[H1]" Alias="[h]" Column="number"/></OutputList><IndexScan Ordered="1" ScanDirection="FORWARD" ForcedIndex="0" ForceSeek="0" ForceScan="0" NoExpandHint="0" Storage="RowStore"><DefinedValues><DefinedValue><ColumnReference Database="[fbH]" Schema="[dbo]" Table="[H1]" Alias="[h]" Column="d"/></DefinedValue><DefinedValue><ColumnReference Database="[fbH]" Schema="[dbo]" Table="[H1]" Alias="[h]" Column="Name"/></DefinedValue><DefinedValue><ColumnReference Database="[fbH]" Schema="[dbo]" Table="[H1]" Alias="[h]" Column="smallName"/></DefinedValue><DefinedValue><ColumnReference Database="[fbH]" Schema="[dbo]" Table="[H1]" Alias="[h]" Column="number"/></DefinedValue></DefinedValues><Object Database="[fbH]" Schema="[dbo]" Table="[H1]" Index="[hIDX]" Alias="[h]" IndexKind="Clustered" Storage="RowStore"/><SeekPredicates><SeekPredicateNew><SeekKeys><Prefix ScanType="EQ"><RangeColumns><ColumnReference Database="[fbH]" Schema="[dbo]" Table="[H1]" Alias="[h]" Column="d"/><ColumnReference Database="[fbH]" Schema="[dbo]" Table="[H1]" Alias="[h]" Column="Name"/></RangeColumns><RangeExpressions><ScalarOperator ScalarString="&apos;2015-01-01&apos;"><Const ConstValue="&apos;2015-01-01&apos;"/></ScalarOperator><ScalarOperator ScalarString="&apos;Record&apos;"><Const ConstValue="&apos;Record&apos;"/></ScalarOperator></RangeExpressions></Prefix></SeekKeys></SeekPredicateNew></SeekPredicates></IndexScan></RelOp></Spool></RelOp></NestedLoops></RelOp></QueryPlan></StmtSimple></Statements></Batch></BatchSequence></ShowPlanXML>