Index with Include

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2008 » T-SQL (SS2K8) » Index with Include

Index with Include

Rate Topic

Display Mode

Topic Options

Author

Message

thava

Posted Wednesday, October 24, 2012 8:32 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, May 09, 2013 10:22 PM
Points: 103, Visits: 161

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

Post #1376516

GSquared

Posted Wednesday, October 24, 2012 9:35 AM

SSCoach

Group: General Forum Members
Last Login: Yesterday @ 1:55 PM
Points: 15,442, Visits: 9,571

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

Post #1376554

ScottPletcher

Posted Wednesday, October 24, 2012 9:40 AM

Ten Centuries

Group: General Forum Members
Last Login: Today @ 8:56 AM
Points: 1,320, Visits: 1,773

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)
One man with courage makes a majority. Andrew Jackson

Post #1376556

thava

Posted Wednesday, October 24, 2012 10:39 PM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, May 09, 2013 10:22 PM
Points: 103, Visits: 161

thanks for the reply

Post #1376767

Arjun Sivadasan

Posted Thursday, October 25, 2012 5:59 AM

Old Hand

Group: General Forum Members
Last Login: Thursday, May 09, 2013 3:29 AM
Points: 365, Visits: 697

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/

Post #1376942

« Prev Topic | Next Topic »

Permissions