Sql-server – Suggestion on Missing Index Creation

indexsp-blitzindexsql server

I have been using sp_blitzindex and it is very very helpful(Thanks to Brent Ozar and Team). I executed this procedure against my database and below is the finding for one table falling in Indexaphobia group:

below is definition of underlying table:

CREATE TABLE [dbo].[table_name](
    [L] [int] IDENTITY(1,1) NOT NULL,
    [R] [varchar](15) NOT NULL,
    [A] [int] NOT NULL,
    [VAR32_01] [varchar](32) NULL,
    [VAR32_02] [varchar](32) NULL,
    [VAR32_03] [varchar](32) NULL,
    [VAR32_04] [varchar](32) NULL,
    [VAR32_05] [varchar](32) NULL,
    [VAR32_06] [varchar](32) NULL,
    [VAR32_07] [varchar](32) NULL,
    [VAR32_08] [varchar](32) NULL,
    [VAR32_09] [varchar](32) NULL,
    [VAR32_10] [varchar](32) NULL,
    [VAR32_11] [varchar](32) NULL,
    [VAR32_12] [varchar](32) NULL,
    [VAR32_13] [varchar](32) NULL,
    [VAR32_14] [varchar](32) NULL,
    [VAR32_15] [varchar](32) NULL,
    [VAR32_16] [varchar](32) NULL,
    [VAR32_17] [varchar](32) NULL,
    [VAR32_18] [varchar](32) NULL,
    [VAR32_19] [varchar](32) NULL,
    [VAR32_20] [varchar](32) NULL,
    [VAR32_21] [varchar](32) NULL,
    [VAR32_22] [varchar](32) NULL,
    [VAR32_23] [varchar](32) NULL,
    [VAR32_24] [varchar](32) NULL,
    [VAR32_25] [varchar](32) NULL,
    [VAR32_26] [varchar](32) NULL,
    [VAR32_27] [varchar](32) NULL,
    [VAR32_28] [varchar](32) NULL,
    [VAR32_29] [varchar](32) NULL,
    [VAR32_30] [varchar](32) NULL,
    [VAR32_31] [varchar](32) NULL,
    [VAR32_32] [varchar](32) NULL,
    [VAR32_33] [varchar](32) NULL,
    [VAR32_34] [varchar](32) NULL,
    [VAR32_35] [varchar](32) NULL,
    [VAR32_36] [varchar](32) NULL,
    [VAR32_37] [varchar](32) NULL,
    [VAR32_38] [varchar](32) NULL,
    [VAR32_39] [varchar](32) NULL,
    [VAR32_40] [varchar](32) NULL,
    [VAR32_41] [varchar](32) NULL,
    [VAR32_42] [varchar](32) NULL,
    [VAR32_43] [varchar](32) NULL,
    [VAR32_44] [varchar](32) NULL,
    [VAR32_45] [varchar](32) NULL,
    [VAR32_46] [varchar](32) NULL,
    [VAR32_47] [varchar](32) NULL,
    [VAR32_48] [varchar](32) NULL,
    [VAR32_49] [varchar](32) NULL,
    [VAR32_50] [varchar](32) NULL,
    [VAR32_51] [varchar](32) NULL,
    [VAR32_52] [varchar](32) NULL,
    [VAR32_53] [varchar](32) NULL,
    [VAR32_54] [varchar](32) NULL,
    [VAR32_55] [varchar](32) NULL,
    [VAR32_56] [varchar](32) NULL,
    [VAR32_57] [varchar](32) NULL,
    [VAR32_58] [varchar](32) NULL,
    [VAR32_59] [varchar](32) NULL,
    [VAR32_60] [varchar](32) NULL,
    [VAR32_61] [varchar](32) NULL,
    [VAR32_62] [varchar](32) NULL,
    [VAR32_63] [varchar](32) NULL,
    [VAR32_64] [varchar](32) NULL,
    [VAR64_01] [varchar](64) NULL,
    [VAR64_02] [varchar](64) NULL,
    [VAR64_03] [varchar](64) NULL,
    [VAR64_04] [varchar](64) NULL,
    [VAR64_05] [varchar](64) NULL,
    [VAR64_06] [varchar](64) NULL,
    [VAR64_07] [varchar](64) NULL,
    [VAR64_08] [varchar](64) NULL,
    [VAR64_09] [varchar](64) NULL,
    [VAR64_10] [varchar](64) NULL,
    [VAR64_11] [varchar](64) NULL,
    [VAR64_12] [varchar](64) NULL,
    [VAR64_13] [varchar](64) NULL,
    [VAR64_14] [varchar](64) NULL,
    [VAR64_15] [varchar](64) NULL,
    [VAR64_16] [varchar](64) NULL,
    [VAR64_17] [varchar](64) NULL,
    [VAR64_18] [varchar](64) NULL,
    [VAR64_19] [varchar](64) NULL,
    [VAR64_20] [varchar](64) NULL,
    [VAR64_21] [varchar](64) NULL,
    [VAR64_22] [varchar](64) NULL,
    [VAR64_23] [varchar](64) NULL,
    [VAR64_24] [varchar](64) NULL,
    [VAR64_25] [varchar](64) NULL,
    [VAR64_26] [varchar](64) NULL,
    [VAR64_27] [varchar](64) NULL,
    [VAR64_28] [varchar](64) NULL,
    [VAR64_29] [varchar](64) NULL,
    [VAR64_30] [varchar](64) NULL,
    [VAR64_31] [varchar](64) NULL,
    [VAR64_32] [varchar](64) NULL,
    [VAR128_01] [varchar](128) NULL,
    [VAR128_02] [varchar](128) NULL,
    [VAR128_03] [varchar](128) NULL,
    [VAR128_04] [varchar](128) NULL,
    [VAR128_05] [varchar](128) NULL,
    [VAR128_06] [varchar](128) NULL,
    [VAR128_07] [varchar](128) NULL,
    [VAR128_08] [varchar](128) NULL,
    [VAR128_09] [varchar](128) NULL,
    [VAR128_10] [varchar](128) NULL,
    [VAR128_11] [varchar](128) NULL,
    [VAR128_12] [varchar](128) NULL,
    [VAR128_13] [varchar](128) NULL,
    [VAR128_14] [varchar](128) NULL,
    [VAR128_15] [varchar](128) NULL,
    [VAR128_16] [varchar](128) NULL,
    [VAR256_01] [varchar](256) NULL,
    [VAR256_02] [varchar](256) NULL,
    [VAR256_03] [varchar](256) NULL,
    [VAR256_04] [varchar](256) NULL,
    [VAR256_05] [varchar](256) NULL,
    [VAR256_06] [varchar](256) NULL,
    [VAR256_07] [varchar](256) NULL,
    [VAR256_08] [varchar](256) NULL,
    [VAR512_01] [varchar](512) NULL,
    [VAR512_02] [varchar](512) NULL,
    [VAR512_03] [varchar](512) NULL,
    [VAR512_04] [varchar](512) NULL,
    [VAR1024_01] [varchar](1024) NULL,
    [VAR1024_02] [varchar](1024) NULL,
    [E] [varchar](20) NULL,
    [M] [varchar](40) NULL,
    [E] [varchar](50) NULL,
    [N] [varchar](40) NULL,
    [TN] [int] NULL,
    [T] [numeric](1, 0) NULL,
    [D] [numeric](1, 0) NULL,
 CONSTRAINT [XPKtable_name] PRIMARY KEY NONCLUSTERED 
(
    [L] ASC,
    [R] ASC
)

Following these missing index details, I am planning to create index with below definition:

create nonclustered index table_name_incl(A,VAR32_02) include(L,R,E,T,D,VAR32_10,VAR32_18,VAR32_19,VAR32_20,VAR64_11,VAR64_02,VAR32_42,VAR32_39,VAR32_38,VAR32_35,VAR32_39,VAR32_24,VAR32_25,VAR32_27)

I have arrived at above columns based on the number of occurrences of these columns in all 6 missing index stats.

As you can see from table definition, this is a heap and doesn't have clustered index on this table.

Appreciate your guidance or any assistance on this.

Version: Microsoft SQL Server 2014 (SP3) (KB4022619) - 12.0.6024.0 (X64) Sep 7 2018 01:37:51 Enterprise Edition: Core-based Licensing (64-bit) on Windows NT 6.3 (Build 9600: ) (Hypervisor)

Best Answer

From Comments

Clustered index poses many constraints like null, duplicates etc whereas non-clustered doesn't pose any risk for vendor and they agree on it easily.

This isn't true, clustered indexes don't disallow duplicates, NULLs, or even duplicate NULLs unless they're declared as UNIQUE.

These inserts will all succeed:

DROP TABLE IF EXISTS dbo.t;

CREATE TABLE dbo.t ( id INT NULL );

CREATE CLUSTERED INDEX c ON dbo.t ( id );


INSERT dbo.t ( id )
VALUES ( 1 );
INSERT dbo.t ( id )
VALUES ( 1 );
INSERT dbo.t ( id )
VALUES ( 1 );

INSERT dbo.t ( id )
VALUES ( NULL );
INSERT dbo.t ( id )
VALUES ( NULL );
INSERT dbo.t ( id )
VALUES ( NULL );

SELECT *
FROM   dbo.t AS t;

These inserts will fail after the first unique value:

DROP TABLE IF EXISTS dbo.t;

CREATE TABLE dbo.t ( id INT NULL );

CREATE UNIQUE CLUSTERED INDEX c ON dbo.t ( id );


INSERT dbo.t ( id )
VALUES ( 1 );
INSERT dbo.t ( id )
VALUES ( 1 );
INSERT dbo.t ( id )
VALUES ( 1 );

INSERT dbo.t ( id )
VALUES ( NULL );
INSERT dbo.t ( id )
VALUES ( NULL );
INSERT dbo.t ( id )
VALUES ( NULL );

SELECT *
FROM   dbo.t AS t;

Your suggestion did the trick, I created clustered index on column A in test environment and so far no complaint of missing index at all, just to add that there was missing index complaint in Test also. We will be promoting this change to Production very soon after confirming with vendor. Thanks a lot.

???

Related Solutions

Sql-server – Parent-Child Tree Hierarchical ORDER

OK, enough brain cells are dead.

SQL Fiddle

WITH cte AS
(
  SELECT 
    [ICFilterID], 
    [ParentID],
    [FilterDesc],
    [Active],
    CAST(0 AS varbinary(max)) AS Level
  FROM [dbo].[ICFilters]
  WHERE [ParentID] = 0
  UNION ALL
  SELECT 
    i.[ICFilterID], 
    i.[ParentID],
    i.[FilterDesc],
    i.[Active],  
    Level + CAST(i.[ICFilterID] AS varbinary(max)) AS Level
  FROM [dbo].[ICFilters] i
  INNER JOIN cte c
    ON c.[ICFilterID] = i.[ParentID]
)

SELECT 
  [ICFilterID], 
  [ParentID],
  [FilterDesc],
  [Active]
FROM cte
ORDER BY [Level];

Mysql – Adding index to large thesql tables

The activity of altering big tables are done in phases:

Create a new table with required fields and indexes say in test DB (just structure)
Dump the data from the existing table and load the same to the newly created table in test DB
Now announce your downtime :)
Swap the tables by renaming - RENAME table ur_db.table_name to test.temp, test.table_name to ur_db.table_name, test.temp to test.table_name; This is an atomic operation taking fraction of a second.
Load the extra records to the newly created table ( the records that came after dumping and then loading). This step can be done before Step: 3 also once to reduce your downtime.
And your system is back

Few notes:

You need not hit information schema directly like this, try using SHOW TABLE STATUS from db like 'table_name'
The speed of alter table is more or less linked with the I/O speed. Last time when we ran a direct alter table(without the above steps), we had 40GB+ table size it took around 4hours. If your 20GB data is taking years, you are working on some outdated machine.
Also drop of the unwanted indexes like index_bslt_ondate, index_base_schedule_line_items_loan_base_schedule as some other indexes has the left most column as the indexed column

Please let me know if you need any clarification on any of these steps.

Edit : A simple python script to automate the process https://github.com/georgecj11/hotswap_mysql_table.git

Best Answer

From Comments

Related Solutions

Sql-server – Parent-Child Tree Hierarchical ORDER

Mysql – Adding index to large thesql tables

Related Question