Post reply

Creating an index including non-key columns

  • July 9, 2015 at 6:38 am

    #305674

    Does including non-key columns help much in the performance of an index?

  • July 9, 2015 at 6:42 am

    #1811273

    Maybe. Depends on the queries using the index.

    Should be some detail in http://www.sqlservercentral.com/articles/Indexing/68636/

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • July 9, 2015 at 7:11 am

    #1811292

    Pardon my ignorance but, if there are several indexes set up on a table, how does SSMS know which index to use?

  • July 9, 2015 at 7:21 am

    #1811297

    SSMS doesn't. SSMS is just a client tool. It sends queries to SQL Server and displays results.

    One component of the SQL Server engine is the query optimiser. It's job is to generate a good plan for a query and part of that is identifying what indexes to use.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • July 9, 2015 at 7:26 am

    #1811300

    This is what I got back from the Execution Plan. I already created this index, including these columns.

    /*

    Missing Index Details from ServiceData.sql - SH-SQL01.DataWarehouse (BH1NC00\pho.cams (157))

    The Query Processor estimates that implementing the following index could improve the query cost by 52.4628%.

    */

    /*

    USE [DataWarehouse]

    GO

    CREATE NONCLUSTERED INDEX [<Name of Missing Index, sysname,>]

    ON [dbo].[IDX_Service] ([PROC__2],[POST_PD])

    INCLUDE ([ORIG_FSC__1],[LOC__1],[MOD],[PROC__5__1],[PROV__33__1],[CHG_AMT],[UNITS_SERV_ANAL_ACT],[POST_MONTH],[POST_YEAR])

    GO

    */

  • July 9, 2015 at 7:49 am

    #1811313

    Yes, the missing index recomendations are broken and sometimes recommend indexes which exist, and besides you should never create from the missing index details without lots of testing.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • July 9, 2015 at 8:15 am

    #1811328

    Thanx.

  • July 10, 2015 at 11:07 am

    #1811694

    Also, the initial tuning must focus first on getting the best clustered index for every table. Only after that should you create or modify nonclustered indexes.

    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 10, 2015 at 12:08 pm

    #1811716

    Thanx.

Viewing 9 posts - 1 through 8 (of 8 total)

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