Post reply

Index with Include

  • October 24, 2012 at 8:32 am

    #393452

    Hi

    hope this is the correct forum to this question if not please redirect to me the correct forum

    recently my company has upgraded the database to sqlserver2008 R2

    with help of this link[/url]

    i have did some minor change in it to avoid the duplication of the index name

    i can improve lot of query's performance, but the scenario is change now, in a single table i have more than 10 indexes, the table only has around 10 to 15 columns

    the script produce the index like this

    --Most Impacted time : 900.45

    CREATE INDEX IX_AdUserPrivilegeTrial_UserID_RoleID_SiteID ON [VG-3.0].[dbo].[AdUserPrivilegeTrial] ([UserID], [RoleID], [SiteID]) INCLUDE ([Version])

    Go

    --Most Impacted time : 854.84

    CREATE INDEX IX_AdUserPrivilegeTrial_UserID_RoleID_SiteID ON [VG-3.0].[dbo].[AdUserPrivilegeTrial] ([UserID], [RoleID], [SiteID]) INCLUDE ([UnitID], [NoAccess], [ViewAccess], [FullAccess], [userlevel])

    Go

    i am confused a lot please clarify me,

    now my question is

    if i need to keep the index as it is, explain me why i need to create a index on the same column

    or shall i change it like this

    CREATE INDEX IX_AdUserPrivilegeTrial_UserID_RoleID_SiteID ON [VG-3.0].[dbo].[AdUserPrivilegeTrial] ([UserID], [RoleID], [SiteID]) INCLUDE ([Version],[UnitID], [NoAccess], [ViewAccess], [FullAccess], [userlevel])

    sorry for my poor english

    Every rule in a world of bits and bytes, can be bend or eventually be broken
    MyBlog About Common dialog control
    A Visualizer for viewing SqlCommand object script [/url]

  • October 24, 2012 at 9:35 am

    #1552332

    The index suggestions do that kind of thing a lot.

    Go with the combined index, not two separate indexes that are so similar to each other.

    - Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
    Property of The Thread

    "Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

  • October 24, 2012 at 9:40 am

    #1552334

    Don't rely solely on the missing index recommendations from those tables. SQL typically suggests way too many indexes. You need to have someone review all available information before just creating the stated indexes, or you will end up with too many indexes and very slow table DELETEs, INSERTs and UPDATEs.

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

  • October 24, 2012 at 10:39 pm

    #1552503

    thanks for the reply

    Every rule in a world of bits and bytes, can be bend or eventually be broken
    MyBlog About Common dialog control
    A Visualizer for viewing SqlCommand object script [/url]

  • October 25, 2012 at 5:59 am

    #1552651

    Also, keep in mind that SQL Server stores statistics for left-based subsets of an index. You may want to look at all SPs or queries which refer to the table in question and redesign indexes.

    Read more about that here - http://sqlinthewild.co.za/index.php/2009/01/19/index-columns-selectivity-and-equality-predicates/

    https://sqlroadie.com/

Viewing 5 posts - 1 through 4 (of 4 total)

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