none
How do I know that Which Indexes are useful for that table so I can take out extra indexes RRS feed

  • Question

  • Hi I have a table with 4 Million (4 carore) records on it.Now I have a cluster index on it.So When I did

    Sp_spaceused it shows me that data size is almost 18 Gb and Cluster index is almost same.

    Is it really always be true that size of data=size of cluster index

    (2) I have create a couple of indexes on table I want to know that Do I need all those indexes so how can I know that my index is useful and useless so based on that If my cluster index is useless I want to take it out from the table

    Saturday, October 27, 2012 8:25 AM

All replies

  • I guess that you might have some confusion on the clustered index.

    When a table with a clustered index, the clustered index is the table (do not confuse with the clustered index keys). A table can be either a heap or clustered. the clustered index size should be equal to the table size ( without including the nonclustered indexes.)

    You can use the dynamic management view sys.dm_db_index_usage_stats to check if an nonclustered index is useful or not. for example, if for a particular nonclustered index, if the user_lookups + user_scans + user_lookups = 0, you can assume that the index might not be useful. However, please note that the DMV will be reinitiated very time the sql server instance is restarted, so to be sure that you have enough server running time to testify the index usage from the DMV I mentioned.

    As to clustered index, it is generally a good practice to have an clustered index, so you not normally drop a clustered index, but you might review your clustered index key and use the right strategy.


    View Steven Wang's profile on LinkedIn | Blog: MSBICOE.com | MCSE: Data Platform | MCITP: BI, SQL Developer & DBA

    Hate to mislead others, if I'm wrong slap me. Thanks!

    Saturday, October 27, 2012 8:49 AM
  • Post the important queries & DDL for quick assistance.

    Optimization article:

    http://www.sqlusa.com/articles/query-optimization/

    Determining factor for removing indexes: business critical queries should have index support.


    Kalman Toth SQL SERVER 2012 & BI TRAINING
    New Book: Beginner Database Design & SQL Programming Using Microsoft SQL Server 2012

    Saturday, October 27, 2012 1:40 PM
    Moderator

  • CREATE TABLE [dbo].[Library_Master](
        [LIBRARY] [char](8) NOT NULL,
        [TRDELT] [char](1) NULL,
        [TRFAM#] [nchar](9) NOT NULL,
        [TRMBR#] [nchar](2) NOT NULL,
        [TRSEQ#] [nchar](5) NOT NULL,
        [TRTYPE] [char](1) NOT NULL,
        [TRTDAT] [char](10) NOT NULL,
        [ZTMONTH] [numeric](18, 0) NULL,
        [ZTYEAR] [numeric](18, 0) NULL,
        [TRLDAT] [char](10) NULL,
        [TRPDAT] [char](10) NOT NULL,
        [ZPMONTH] [numeric](18, 0) NULL,
        [ZPYEAR] [numeric](18, 0) NULL,
        [ZPDAY] [numeric](18, 0) NULL,
        [TRSDAT] [char](10) NOT NULL,
        [TRIOEC] [char](1) NOT NULL,
        [TRLOC] [decimal](2, 0) NOT NULL,
        [TRCODE] [nchar](10) NOT NULL,
        [TRMODF] [char](2) NOT NULL,
        [TRMOD2] [char](2) NOT NULL,
        [TRMOD3] [char](2) NOT NULL,
        [TRAMT] [decimal](9, 2) NOT NULL,
        [TRQTY] [decimal](3, 0) NOT NULL,
        [TRADR#] [nchar](5) NOT NULL,
        [TRRDR#] [nchar](5) NOT NULL,
        [TRNORD] [nchar](5) NULL,
        [TRNPCP] [nchar](5) NOT NULL,
        [TRDIAG] [char](8) NOT NULL,
        [TRAUT#] [char](16) NULL,
        [TRREF#] [nchar](4) NOT NULL,
        [TRCHK#] [char](14) NOT NULL,
        [TRPTYP] [char](2) NOT NULL,
        [TRUSER] [char](10) NOT NULL,
        [TRBTCH] [decimal](5, 0) NOT NULL,
        [TRINSC] [decimal](5, 0) NOT NULL,
        [TRUACF] [decimal](9, 2) NULL,
        [TRNSTR] [decimal](4, 0) NULL,
        [TRNEND] [decimal](4, 0) NULL,
        [TIIDAT] [char](10) NOT NULL,
        [TIXMIT] [char](1) NOT NULL,
        [TIIN#1] [decimal](5, 0) NOT NULL,
        [TIIN#2] [decimal](5, 0) NOT NULL,
        [TIEOB] [char](3) NOT NULL,
        [TIRPDT] [char](1) NULL,
        [TIPMT$] [decimal](9, 2) NOT NULL,
        [TIADJ$] [decimal](9, 2) NOT NULL,
        [TIDAT2] [char](10) NOT NULL,
        [TIBIL2] [char](1) NULL,
        [TXEXAM] [char](1) NULL,
        [TXFIND] [char](1) NULL,
        [TXPRET] [char](8) NULL,
        [TXPSTT] [char](8) NULL,
        [TXAUX1] [char](4) NULL,
        [TXAUX2] [char](6) NULL,
        [TXAUX3] [char](8) NULL,
        [TXANSL] [decimal](5, 0) NULL,
        [TXANSQ] [char](1) NULL,
        [TXBASU] [decimal](5, 1) NOT NULL,
        [TXTIMU] [decimal](5, 1) NULL,
        [TXMODU] [decimal](5, 1) NULL,
        [TXTOTT] [decimal](5, 0) NULL,
        [TRDIG2] [char](8) NOT NULL,
        [TRDIG3] [char](8) NOT NULL,
        [TRDIG4] [char](8) NOT NULL,
        [TRDEPT] [char](5) NULL,
        [TRMCRI] [decimal](9, 0) NULL,
        [ID] [int] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,
     CONSTRAINT [PK__TRANS__145C0A3F] PRIMARY KEY NONCLUSTERED
    (
        [ID] ASC
    )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
    ) ON [PRIMARY]

    -------------------Indexes

    CREATE NONCLUSTERED INDEX [_dta_index_TRANS_7_932198371__K6_K1_K3_7] ON [dbo].[Library_Master]
    (
        [TRTYPE] ASC,
        [LIBRARY] ASC,
        [TRFAM#] ASC
    )
    INCLUDE ( [TRTDAT]) WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF,


    CREATE NONCLUSTERED INDEX [IDX_MOD_TRANS_ZLIBRARY_TRSEQ#_TRFAM#] ON [dbo].[Library_Master]
    (
        [LIBRARY] ASC,
        [TRSEQ#] ASC,
        [TRFAM#] ASC
    )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON,
     ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]

    CREATE CLUSTERED INDEX [idx_trans_comp_1] ON [dbo].[Library_Master]
    (
        [LIBRARY] ASC,
        [TRFAM#] ASC
    )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]

    CREATE NONCLUSTERED INDEX [idx_trans_comp_2] ON [dbo].[Library_Master]
    (
        [LIBRARY] ASC,
        [TRFAM#] ASC,
        [TRPDAT] ASC
    )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]

    CREATE NONCLUSTERED INDEX [idx_trans_tradr#] ON [dbo].[Library_Master]
    (
        [TRADR#] ASC
    )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]


    CREATE NONCLUSTERED INDEX [idx_trans_trcode] ON [dbo].[Library_Master]
    (
        [TRCODE] ASC
    )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]


    CREATE NONCLUSTERED INDEX [idx_trans_trtdat] ON [dbo].[Library_Master]
    (
        [TRTDAT] ASC
    )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]


    CREATE NONCLUSTERED INDEX [idx_trans_trtype] ON [dbo].[Library_Master]
    (
        [TRTYPE] ASC
    )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]

    CREATE NONCLUSTERED INDEX [idx_trans_zlibrary] ON [dbo].[Library_Master]
    (
        [LIBRARY] ASC
    )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]


    Create TABLE [dbo].[Library_Master] ADD  CONSTRAINT [PK__TRANS__145C0A3F] PRIMARY KEY NONCLUSTERED
    (
        [ID] ASC
    )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
    Monday, October 29, 2012 6:00 AM
  • Can you post the business critical queries as well? 

    An index is useful as long as it supports a business critical query.

    How about the other queries? They take a second-seat in optimization. Dominant focus must be on business critical queries which include very-frequent queries. Once you optimize business critical queries, you turn your attention to other queries.


    Kalman Toth SQL SERVER 2012 & BI TRAINING
    New Book: Beginner SQL Programming Using Microsoft SQL Server


    Monday, November 5, 2012 1:17 PM
    Moderator